javasqlitemybatissqlitejdbc

How can I create a table using Mybatis and SQLite?


I am trying to create a new database and new table using Mybatis and SQLite. I found from previous answers (1, 2, 3) that Mybatis does support using CREATE and ALTER statements, by marking them as "UPDATE" within Mybatis mapper syntax. However, those questions/answers were using Mapper XML whereas I'm using annotations, and also none were using SQLite.

SQLite creates a new database as soon as you open a new connection to it, so it doesn't matter if the DB exists before or not. A new database is created with a size of zero bytes, which is fine (SQLite treats a 0 byte file as an empty database). But after the table creation I would expect the database size to be non-zero as it stores the table structure for that table. After running my code which I think should create the table (I'm checking my syntax against this answer), the database size still reads as 0 bytes, which says to me that the table has not actually been created. What am I doing wrong?

My Java code to test this scenario:

public class Example {

    public static void main(String[] args) {
        String userHomePath = System.getProperty("user.home");
        File exampleDb = new File(userHomePath, "example.sqlite3");
        String jdbcConnectionString = "jdbc:sqlite:" + exampleDb.getAbsolutePath();
        DataSource dataSource = new PooledDataSource("org.sqlite.JDBC", jdbcConnectionString, null, null);
        Environment environment = new Environment("Main", new JdbcTransactionFactory(), dataSource);
        Configuration configuration = new Configuration(environment);
        configuration.addMapper(GenericMapper.class);
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory sessionFactory = builder.build(configuration);
        try (SqlSession session = sessionFactory.openSession()) {
            GenericMapper genericMapper = session.getMapper(GenericMapper.class);
            genericMapper.createExampleTableIfMissing();
        }
    }

}

My mapper:

public interface GenericMapper {
    @Update("CREATE TABLE IF NOT EXISTS extbl (id INTEGER PRIMARY KEY AUTOINCREMENT)")
    void createExampleTableIfMissing();
}

Checking the file after this code has run:

C:\Users\me>dir example.sqlite3
 Volume in drive C is Windows
 Volume Serial Number is D4DE-B46A

 Directory of C:\Users\me

12/04/2021  18:14                 0 example.sqlite3
               1 File(s)              0 bytes
               0 Dir(s)  27,326,779,392 bytes free

C:\Users\me>

Solution

  • As per user @ave in the comments:

    Commit the session by calling session.commit() before closing it.