Spring boot application not able to detect table defined in sqlite db file

Question

I have a Spring boot application which uses SQLite db to perform CRUD operations. When I try to insert a row into the database, I always get this error. I want to be able to perform CRUD operations with the sqlite db. My stack trace is given below

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: Chats)
    at org.sqlite.core.DB.newSQLException(DB.java:909) ~[sqlite-jdbc-3.15.1.jar:na]
    at org.sqlite.core.DB.newSQLException(DB.java:921) ~[sqlite-jdbc-3.15.1.jar:na]
    at org.sqlite.core.DB.throwex(DB.java:886) ~[sqlite-jdbc-3.15.1.jar:na]
    at org.sqlite.core.NativeDB.prepare_utf8(Native Method) ~[sqlite-jdbc-3.15.1.jar:na]
    at org.sqlite.core.NativeDB.prepare(NativeDB.java:127) ~[sqlite-jdbc-3.15.1.jar:na]
    at org.sqlite.core.DB.prepare(DB.java:227) ~[sqlite-jdbc-3.15.1.jar:na]
    at org.sqlite.core.CorePreparedStatement.<init>(CorePreparedStatement.java:41) ~[sqlite-jdbc-3.15.1.jar:na]
    at org.sqlite.jdbc3.JDBC3PreparedStatement.<init>(JDBC3PreparedStatement.java:30) ~[sqlite-jdbc-3.15.1.jar:na]
    at org.sqlite.jdbc4.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:19) ~[sqlite-jdbc-3.15.1.jar:na]
    at org.sqlite.jdbc4.JDBC4Connection.prepareStatement(JDBC4Connection.java:48) ~[sqlite-jdbc-3.15.1.jar:na]
    at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:254) ~[sqlite-jdbc-3.15.1.jar:na]
    at org.sqlite.jdbc3.JDBC3Connection.prepareStatement(JDBC3Connection.java:226) ~[sqlite-jdbc-3.15.1.jar:na]
    at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator.createPreparedStatement(JdbcTemplate.java:1524) ~[spring-jdbc-4.3.12.RELEASE.jar:4.3.12.RELEASE]
...

This is not correct because I am able to see the DDL of the table being created in SQLite client in the file.

The SQLite db file is located at src/main/resources/mydb.dbloction in the maven project. I tried the following configuration in my application properties.

spring.datasource.url=jdbc:sqlite:mydb.db
spring.datasource.driver-class-name=org.sqlite.JDBC
spring.jpa.database-platform=com.myproject.assignment.SqliteDialect
spring.datasource.username=
spring.datasource.password=
spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true

The configuration for the dialect has been taken from the following SO posts

Here is the method to insert code into the database.

public long insertChatRecord(final ChatRecord chatRecord) {
    if (LOGGER.isDebugEnabled()) {
      LOGGER.debug("Inserting chat record: " + chatRecord);
    }
    DateTime dateTime = DateTime.now().withZone(DateTimeZone.UTC);
    DateTime expirationTime = dateTime.plusSeconds(
        chatRecord.getTimeout() != null ? chatRecord.getTimeout().intValue(): 60);
    final String timestamp = expirationTime.toString(TIMESTAMP_FORMAT);
    long chatId = SequenceGenerator.getInstance().next();
    chatRecord.setChatId(chatId);
    chatRecord.setExpirationTimestamp(expirationTime);
    StringBuilder sb = new StringBuilder();
    sb.append("INSERT INTO Chats(chat_id, username, chat_text, expiration_date) ");
    sb.append("VALUES(?, ?, ?, ?)");
    jdbcTemplate.update(sb.toString(), new PreparedStatementSetter() {

      @Override
      public void setValues(PreparedStatement ps)
          throws SQLException, DataAccessException {
        // TODO Auto-generated method stub
        ps.setLong(1, chatRecord.getChatId());
        ps.setString(2,  chatRecord.getUsername());
        ps.setString(3,  chatRecord.getText());
        ps.setString(4, timestamp);
      }});
    return chatId;
  }

The exception is raised at this particular line.

jdbcTemplate.update(sb.toString(), new PreparedStatementSetter() {

      @Override
      public void setValues(PreparedStatement ps)
          throws SQLException, DataAccessException {
        // Setting values for prepared statement.
      }});

I don't know what I am doing wrong. I am running Spring Boot app in Eclipse. The main class is given below:

@SpringBootApplication
public  class Application {

  public static void main(String[] args) throws Exception {
    SpringApplication.run(Application.class, args);
  }

}

My pom.xml configuration as suggested by plugin documentation is given below.

<build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <addResources>true</addResources>
                </configuration>
            </plugin>
        </plugins>
    </build>

You should name your table as CHATS. It can't find your table because you using small letter — John Joe, Nov 04 '17 at 07:52
Hi, John, I can still reproduce the error even if I change the table name, — Kartik, Nov 04 '17 at 08:44
What about another table(s)? Can you see them or neither table can be accessed? — StanislavL, Nov 04 '17 at 08:56

varren · Answer 1 · 2017-11-04T09:17:59.593

It looks like you are trying to put your db in resources src/main/resources/mydb.db but according to spring.datasource.url=jdbc:sqlite:mydb.db spring is looking for db next to your jar/execution directory.

I think you should actually choose one

Use spring.datasource.url=jdbc:sqlite::resource:mydb.db syntax to access read-only DB files contained in JAR archives More info
Put your db outside of a jar. In IDE it is probably near src directory:
```
projectRoot/
   src/
   mydb.db
```
And keep spring.datasource.url=jdbc:sqlite:mydb.db.

score 1 · Accepted Answer · answered Nov 06 '17 at 06:57

The answer was in the initialisation of database. section https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html

I had to add a schema.sql that creates a table. The schema.sql was added to src/main/resources directory. Here is its content

DROP TABLE IF EXISTS Chats;CREATE TABLE IF NOT EXISTS Chats(username VARCHAR(20), chat_text VARCHAR(256), chat_id BIGINT, expiration_date TIMESTAMP, PRIMARY KEY(chat_id) )

Spring boot application not able to detect table defined in sqlite db file

2 Answers2