0

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>
2
  • 2
    You may need to commit the session by calling session.commit(); before closing it. Commented Apr 12, 2021 at 20:16
  • @ave great! that was it! Commented Apr 12, 2021 at 21:58

1 Answer 1

0

As per user @ave in the comments:

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

Sign up to request clarification or add additional context in comments.

2 Comments

Marked as community wiki as I shouldn't get rep from this due to it being answered by someone else, but after 3+ years I don't think they are going to post it as an answer.
I cannot speak for others, but I am totally fine with someone else posting an answer based on my comment. 🙂 I usually just move on once the OP finds the answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.