Springboot内嵌SQLite配置使用

版本号

MacOS Apple M1 | Jdk17 | Maven 3.8.5 | SpringBoot 2.6.9 | SQLite 3.42.0.0

pom.xml

<dependencies><dependency><groupId>org.xerial</groupId><artifactId>sqlite-jdbc</artifactId><version>3.42.0.0</version></dependency><dependency><groupId>com.github.gwenn</groupId><artifactId>sqlite-dialect</artifactId><version>0.1.4</version></dependency>
</dependencies>

基础配置

application.properties

# data source
spring.datasource.url=jdbc:sqlite:tutorial.db
spring.datasource.driver-class-name=org.sqlite.JDBC
# spring.datasource.journal_mode=WAL
spring.datasource.hikari.maximum-pool-size=1
spring.jpa.properties.hibernate.dialect=org.sqlite.hibernate.dialect.SQLiteDialect
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update

Configuration

import java.util.Properties;
import javax.annotation.Resource;
import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;@Configuration
@EnableJpaRepositories(basePackages = "com.dipeak.diengine.backend.dao")
public class DataSourceConfig {@Resource private Environment environment;@Beanpublic DataSource dataSource() {final DriverManagerDataSource dataSource = new DriverManagerDataSource();dataSource.setDriverClassName(environment.getProperty("spring.datasource.driver-class-name"));dataSource.setUrl(environment.getProperty("spring.datasource.url"));return dataSource;}@Beanpublic LocalContainerEntityManagerFactoryBean entityManagerFactory() {final LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();em.setDataSource(dataSource());em.setPackagesToScan(new String[] {"com.dipeak.diengine.backend.model.entity"});em.setJpaVendorAdapter(new HibernateJpaVendorAdapter());em.setJpaProperties(additionalProperties());return em;}final Properties additionalProperties() {final Properties hibernateProperties = new Properties();if (environment.getProperty("spring.jpa.hibernate.ddl-auto") != null) {hibernateProperties.setProperty("hibernate.hbm2ddl.auto", environment.getProperty("spring.jpa.hibernate.ddl-auto"));}if (environment.getProperty("spring.jpa.properties.hibernate.dialect") != null) {hibernateProperties.setProperty("hibernate.dialect", environment.getProperty("spring.jpa.properties.hibernate.dialect"));}if (environment.getProperty("hibernate.show_sql") != null) {hibernateProperties.setProperty("hibernate.show_sql", environment.getProperty("hibernate.show_sql"));}return hibernateProperties;}
}

Entity 定义

@Entity
@Table(name = "users")
public class User {@Id@GeneratedValue(strategy = GenerationType.IDENTITY)private Long id;private String name;}

Repository 定义

@Repository
public interface UserRepository extends JpaRepository<User, Long> {@Query("select u from User u where u.id <= ?1")Page<User> findMore(Long maxId, Pageable pageable);@Modifying@Transactional@Query("update User u set u.name = ?1 where u.id = ?2")int updateById(String name, Long id);}

Unit Test

public class SqliteTest {@Resource private UserRepository userRepository;@Testpublic void insert() {User user = new User();user.setId(3l);user.setName("wang da fang");userRepository.save(user);}
}