Spring Boot连接SQLite数据库

本文记录Spring Boot连接单SQLite数据源和多SQLite数据源的一种配置方式。

单数据源

1.在pom.xml中添加相关依赖

        <!-- sqlite -->
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.21.0.1</version>
        </dependency>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>

2.在配置文件中配置数据库和mapper.xml文件信息

# sqlite数据库配置
# 一、sqlite文件在resources目录下（仅适合只读的场景，不推荐使用）
# 1.在IDEA中运行时，sqlite文件会被复制到target下的相对路径（target/classes/dbs/MySqlite.db），修改数据时，修改的是target下的sqlite文件;
# 2.用jar包运行时，sqlite文件会被复制到缓存目录，修改的也是缓存目录下的db文件（在window 10下的缓存路径是C:\Users\${用户名}\AppData\Local\Temp\sqlite-jdbc-tmp-xxxxxxxx.db）。
# spring.datasource.url=jdbc:sqlite::resource:dbs/MySqlite.db
# 二、sqlite文件为相对路径
# 1.在IDEA中运行时，相对路径基准目录为当前项目的目录（也就是跟src的同级目录）；
# 2.用jar包运行时，相对路径基准目录为jar包所在目录。
# spring.datasource.url=jdbc:sqlite:dbs/MySqlite.db
# 三、sqlite文件为绝对路径
spring.datasource.url=jdbc:sqlite:F:/dbs/MySqlite.db
# mapper.xml文件路径
mybatis.mapper-locations=classpath:mappers/*.xml
# spring的jdbc可以根据spring.datasource.url的前缀判断driver-class-name，所以spring.datasource.driver-class-name该配置可以不写
spring.datasource.driver-class-name=org.sqlite.JDBC
# sqlite默认不支持设置用户名和密码，一般是通过对sqlite文件加密进行数据保护，所以这两个配置先不配置
# spring.datasource.username=
# spring.datasource.password=

多数据源

1.在pom.xml中添加相关依赖

        <!-- sqlite -->
        <dependency>
            <groupId>org.xerial</groupId>
            <artifactId>sqlite-jdbc</artifactId>
            <version>3.21.0.1</version>
        </dependency>
        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!-- alibaba durid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>

2.在配置文件中配置数据库和mapper.xml文件信息

# 第一个数据库配置
spring.datasource.first.url=jdbc:sqlite:F:/dbs/MyFirstSqlite.db
spring.datasource.first.mapper-locations=classpath:mappers/first/*.xml
# spring的jdbc可以根据spring.datasource.url的前缀判断driver-class-name，所以该配置可以不写
spring.datasource.first.driver-class-name=org.sqlite.JDBC
# spring.datasource.first.username=
# spring.datasource.first.password=

# 第二个数据库配置
spring.datasource.second.url=jdbc:sqlite:F:/dbs/MySecondSqlite.db
spring.datasource.second.mapper-locations=classpath:mappers/second/*.xml
# spring的jdbc可以根据spring.datasource.url的前缀判断driver-class-name，所以该配置可以不写
spring.datasource.second.driver-class-name=org.sqlite.JDBC
# spring.datasource.second.username=
# spring.datasource.second.password=

3.Druid配置

第一个数据库的Druid配置

package com.example.study.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.example.study.dao.first", sqlSessionTemplateRef = "firstSqlSessionTemplate")
public class DruidFirstConfig {
    @Value("${spring.datasource.first.url:}")
    private String url;
    @Value("${spring.datasource.first.mapper-locations:}")
    private String mapperLocations;
    @Value("${spring.datasource.first.driver-class-name:}")
    private String driverClassName;
    @Value("${spring.datasource.first.username:}")
    private String username;
    @Value("${spring.datasource.first.password:}")
    private String password;

    @Bean
    public DataSource firstDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(url);
        // dataSource.setUsername(username);
        // dataSource.setPassword(password);
        // dataSource.setDriverClassName(driverClassName);
        return dataSource;
    }

    @Bean
    public SqlSessionFactory firstSqlSessionFactory(@Qualifier("firstDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
        return factoryBean.getObject();
    }

    @Bean
    public SqlSessionTemplate firstSqlSessionTemplate(@Qualifier("firstSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean
    public DataSourceTransactionManager firstDataSourceTransactionManager(@Qualifier("firstDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}

第二个数据库的Druid配置

package com.example.study.config;

import com.alibaba.druid.pool.DruidDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
@MapperScan(basePackages = "com.example.study.dao.second", sqlSessionTemplateRef = "secondSqlSessionTemplate")
public class DruidSecondConfig {
    @Value("${spring.datasource.second.url:}")
    private String url;
    @Value("${spring.datasource.second.mapper-locations:}")
    private String mapperLocations;
    @Value("${spring.datasource.second.driver-class-name:}")
    private String driverClassName;
    @Value("${spring.datasource.second.username:}")
    private String username;
    @Value("${spring.datasource.second.password:}")
    private String password;

    @Bean
    public DataSource secondDataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setUrl(url);
        // dataSource.setUsername(username);
        // dataSource.setPassword(password);
        // dataSource.setDriverClassName(driverClassName);
        return dataSource;
    }

    @Bean
    public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
        return factoryBean.getObject();
    }

    @Bean
    public SqlSessionTemplate secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }

    @Bean
    public DataSourceTransactionManager secondDataSourceTransactionManager(@Qualifier("secondDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }
}