springboot 连接sqlite

参考：https://www.baeldung.com/spring-boot-sqlite

1.方言问题

报错：Caused by: org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set

See, Hibernate doesn’t ship with a Dialect for SQLite. We need to create one ourselves.

或者参考另外一篇文章：

https://stackoverflow.com/questions/46990525/problems-with-dialect-sqlite-3-with-hibernate-5

有写好的方言（我用的这种）

<!-- https://mvnrepository.com/artifact/com.zsoltfabok/sqlite-dialect -->
<dependency>
    <groupId>com.zsoltfabok</groupId>
    <artifactId>sqlite-dialect</artifactId>
    <version>1.0</version>
</dependency>

 

2.路径 问题：

读取对应目录文件：  jdbc:sqlite:F:/test1.db

3.加密的问题：

没解决。改为不加密了。。

 

4.动态修改数据源的问题。使用的是spring boot ,spring data jpa 

sqlite的路径动态改变，通过接口传入，去解析不同sqlite文件里的数据

@Configuration
public class DataSourceAutoConfiguration {

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.sqlite.JDBC");
        dataSource.setUrl("jdbc:sqlite:d:/xxx.db");
        dataSource.setUsername("");
        dataSource.setPassword("");

        return dataSource;
    }
}

 

控制器类中：
@Autowired
private DriverManagerDataSource dataSource;

控制器方法中动态改变
dataSource.setUrl("jdbc:sqlite:d:/xxx.db");

 

5.sqlite插入guid

lower(hex(randomblob(16)))

 

6.sqlite boolean类型的问题

我存了默认值 true,修改其中两个值为1，然后又修改默认值为0  结果发现SQLite Expert Professional 和Database .NET 17.0.5798_3@87104 这两个工具查看结果不一样，

用原始的sqlite3 exe 查看发现里面有的数据是0,有的是true.

个人推测：SQLite Expert  把true当做了 1 就是真。而 database .net 把true当做字符串 "true" 作为了假
所以显示不一样。
 

所以直接存0,1 就可以了，两边工具查看都是一致的。不要用true这种可能被当做字符串

 

 

原文如下：

 

I just announced the new Learn Spring course, focused on the fundamentals of Spring 5 and Spring Boot 2:

>> CHECK OUT THE COURSE

1. Overview

In this quick tutorial, we’ll go through steps to use an SQLite database in a JPA-enabled Spring Boot application.

Spring Boot supports a few well known in-memory databases out of the box, but SQLite requires a bit more from us.

Let’s have a look at what it takes.

2. Project Setup

For our illustration, we’ll start with a Spring Data Rest app we’ve used in past tutorials.

In the pom, we need to add the sqllite-jdbc dependency:

1 2 3 4 5

<dependency>     <groupId>org.xerial</groupId>     <artifactId>sqlite-jdbc</artifactId>     <version>3.25.2</version> </dependency>

This dependency gives us what we need to use JDBC to communicate with SQLite. But, if we are going to use an ORM, it’s not enough.

3. SQLite Dialect

See, Hibernate doesn’t ship with a Dialect for SQLite. We need to create one ourselves.

3.1. Extending Dialect

Our first step is to extend org.hibernate.dialect.Dialect class to register the data types provided by SQLite:

1 2 3 4 5 6 7 8 9 10

public class SQLiteDialect extends Dialect {       public SQLiteDialect() {         registerColumnType(Types.BIT, "integer");         registerColumnType(Types.TINYINT, "tinyint");         registerColumnType(Types.SMALLINT, "smallint");         registerColumnType(Types.INTEGER, "integer");         // other data types     } }

There are several, so definitely check out the sample code for the rest.

Next, we’ll need to override some default Dialect behaviors.

3.2. Identity Column Support

For example, we need to tell Hibernate how SQLite handles @Id columns, which we can do with a custom IdentityColumnSupport implementation:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

public class SQLiteIdentityColumnSupport extends IdentityColumnSupportImpl {       @Override     public boolean supportsIdentityColumns() {         return true;     }       @Override     public String getIdentitySelectString(String table, String column, int type)       throws MappingException {         return "select last_insert_rowid()";     }       @Override     public String getIdentityColumnString(int type) throws MappingException {         return "integer";     } }

To keep things simple here, let’s keep the identity column type to Integer only. And to get the next available identity value, we’ll specify the appropriate mechanism.

Then, we simply override the corresponding method in our growing SQLiteDialect class:

1 2 3 4

@Override public IdentityColumnSupport getIdentityColumnSupport() {     return new SQLiteIdentityColumnSupport(); }

3.3. Disable Constraints Handling

And, SQLite doesn’t have support for the database constraints, so we’ll need to disable those by again overriding the appropriate methods for both primary and foreign keys:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

@Override public boolean hasAlterTable() {     return false; }   @Override public boolean dropConstraints() {     return false; }   @Override public String getDropForeignKeyString() {     return ""; }   @Override public String getAddForeignKeyConstraintString(String cn,   String[] fk, String t, String[] pk, boolean rpk) {     return ""; }   @Override public String getAddPrimaryKeyConstraintString(String constraintName) {     return ""; }

And, in just a moment, we’ll be able to reference this new dialect in our Spring Boot configuration.

4. DataSource Configuration

Also, since Spring Boot doesn’t provide configuration support for SQLite database out of the box, we also need to expose our own DataSource bean:

1 2 3 4 5 6 7 8 9 10 11

@Autowired Environment env;   @Bean public DataSource dataSource() {     final DriverManagerDataSource dataSource = new DriverManagerDataSource();     dataSource.setDriverClassName(env.getProperty("driverClassName"));     dataSource.setUrl(env.getProperty("url"));     dataSource.setUsername(env.getProperty("user"));     dataSource.setPassword(env.getProperty("password"));     return dataSource; }

And finally, we’ll configure the following properties in our persistence.properties file:

1 2 3 4 5 6 7

driverClassName=org.sqlite.JDBC url=jdbc:sqlite:memory:myDb?cache=shared username=sa password=sa hibernate.dialect=com.baeldung.dialect.SQLiteDialect hibernate.hbm2ddl.auto=create-drop hibernate.show_sql=true

Note that we need to keep the cache as shared in order to keep the database updates visible across multiple database connections.

So, with the above configurations, the app will start and will launch an in-memory database called myDb, which the remaining Spring Data Rest configuration can take up.

5. Conclusion

In this article, we took a sample Spring Data Rest application and pointed it at an SQLite database. However, to do so, we had to create a custom Hibernate dialect.

Make sure to check out the application over on Github. Just run with mvn -Dspring.profiles.active=sqlite spring-boot:run and browse to http://localhost:8080.