In memory SQLITE in a Spring Boot application not working

Question

I am trying to setup SQLITE as an in-memory database in my spring-boot application. But when i try to query the database then it gives me an error "No such table"

Can someone please recommend what am i doing wrong? I need to have SQLITE as a in memory only and we only use jdbc in our project.

Here is my code:

application.properties

spring.datasource.url=jdbc:sqlite:memory
spring.datasource.username=
spring.datasource.password= 

spring.datasource.platform=sqlite
spring.datasource.driver-class-name=org.sqlite.JDBC

MyRepo.java

@Repository
public class MyRepo{

    @Autowired
    private NamedParameterJdbcTemplate namedJdbc;
    
    
    public String getUserName() throws Exception{
        String userName = null;
        String sql = "SELECT username FROM emp WHERE username=:name";
        MapSqlParameterSource paramSource = new MapSqlParameterSource();
        paramSource.addValue("name", "tuser");
        userName = this.namedJdbc.query(sql, paramSource, (rs) -> {
                String name = null;
                while (rs.next()) {
                    name = rs.getString("username").trim();
                    return name;
                }
                return null;
            });
        
        return userName;
    }
}

UserDaoTest.java

@SpringBootTest
public class UserDaoTest {
    
    @Autowired
    private MyRepo rep;

    
    @Test
    public void testFindByname() throws Exception{
        rep.getUserName();
        
    }

}

I also have schema.sql and data.sql files under src/main/resources

schema.sql

DROP TABLE IF EXISTS emp;CREATE TABLE IF NOT EXISTS emp(username VARCHAR(20), empId BIGINT, PRIMARY KEY(empId) )

data.sql

INSERT INTO emp(username,empId) VALUES ('tuser',1001);

Exception that i am getting:

PreparedStatementCallback; uncategorized SQLException for SQL [SELECT username FROM Chats WHERE username=?]; SQL state [null]; error code [1]; [SQLITE_ERROR] SQL error or missing database (no such table: Chats)

also add the property in your application.properties file `spring.jpa.hibernate.ddl-auto=update`. For more about read this answer: https://stackoverflow.com/a/66760630/9457633 — Md Kawser Habib, Mar 26 '21 at 01:00
Note that Spring Data JDBC (or JPA) may do everything you need without having to write a DAO yourself. — chrylis -cautiouslyoptimistic-, Mar 26 '21 at 01:22
@Md. Kawser Habib : i am not using hibernate in my project. i need to make it work as jdbc itself — user1945064, Mar 26 '21 at 04:35
@chrylis -cautiouslyoptimistic : i am not sure what you are saying, but i need access to the data and for some reason the query throws an exception? — user1945064, Mar 26 '21 at 04:36

score 0 · Answer 1 · answered Jun 18 '21 at 14:23

0

well, I am shooting in the dark but looks like you need to add the schema for 'Chats' table as well to your schema.sql

answered Jun 18 '21 at 14:23

Suhel Khan

55
2
7

score 0 · Answer 2 · answered Dec 22 '21 at 15:39

https://sqlite.org/inmemorydb.html

The database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases.

Your issue might be with spring boot opening multiple connections due to its connection pool configuration. If you're using hikari connection pool (default in newer spring boot versions), try adding these properties

spring.datasource.hikari.maximum-pool-size=1
spring.datasource.hikari.max-lifetime=0

In memory SQLITE in a Spring Boot application not working

2 Answers2