title: How can I create a table using Mybatis and SQLite?
body: <p>I am trying to create a new database and new table using Mybatis and SQLite. I found from previous answers (<a href="https://stackoverflow.com/questions/46355690/is-it-possible-to-use-mybatis-to-issue-ddl">1</a>, <a href="https://stackoverflow.com/questions/18786560/does-mybatis-support-ddl/18849402#18849402">2</a>, <a href="https://stackoverflow.com/questions/32270897/creating-a-table-programmatically-using-mybatis-and-mysql">3</a>) that Mybatis does support using CREATE and ALTER statements, by marking them as &quot;UPDATE&quot; within Mybatis mapper syntax. However, those questions/answers were using Mapper XML whereas I'm using annotations, and also none were using SQLite.</p>
  <p>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 <a href="https://stackoverflow.com/a/4098026/191761">this answer</a>), 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?</p>
  <p>My Java code to test this scenario:</p>
  <pre class="lang-java prettyprint-override"><code>public class Example {
  
      public static void main(String[] args) {
          String userHomePath = System.getProperty(&quot;user.home&quot;);
          File exampleDb = new File(userHomePath, &quot;example.sqlite3&quot;);
          String jdbcConnectionString = &quot;jdbc:sqlite:&quot; + exampleDb.getAbsolutePath();
          DataSource dataSource = new PooledDataSource(&quot;org.sqlite.JDBC&quot;, jdbcConnectionString, null, null);
          Environment environment = new Environment(&quot;Main&quot;, 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();
          }
      }
  
  }
  </code></pre>
  <p>My mapper:</p>
  <pre class="lang-java prettyprint-override"><code>public interface GenericMapper {
      @Update(&quot;CREATE TABLE IF NOT EXISTS extbl (id INTEGER PRIMARY KEY AUTOINCREMENT)&quot;)
      void createExampleTableIfMissing();
  }
  </code></pre>
  <p>Checking the file after this code has run:</p>
  <pre class="lang-none prettyprint-override"><code>C:\Users\me&gt;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&gt;
  </code></pre>