1.版本
springboot 采用1.5.19
JPA 采用2.1
2.pom文件
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.whitefox</groupId>
<artifactId>sqlite3-test</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.19.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-cache</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>2.8.0</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.3.2</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-aop</artifactId>
</dependency>
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.27.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.hibernate/hibernate-core -->
<!--<dependency>-->
<!--<groupId>org.hibernate</groupId>-->
<!--<artifactId>hibernate-core</artifactId>-->
<!--<version>5.1.0.Final</version>-->
<!--</dependency>-->
<!--<!– https://mvnrepository.com/artifact/org.hibernate/hibernate-entitymanager –>-->
<!--<dependency>-->
<!--<groupId>org.hibernate</groupId>-->
<!--<artifactId>hibernate-entitymanager</artifactId>-->
<!--<version>5.1.0.Final</version>-->
<!--</dependency>-->
<!--<dependency>-->
<!--<groupId>org.hibernate.common</groupId>-->
<!--<artifactId>hibernate-commons-annotations</artifactId>-->
<!--<version>5.1.0.Final</version>-->
<!--</dependency>-->
<dependency>
<groupId>com.zsoltfabok</groupId>
<artifactId>sqlite-dialect</artifactId>
<version>1.0</version>
</dependency>
<!--<dependency>-->
<!--<groupId>org.hibernate.dialect</groupId>-->
<!--<artifactId>sqlite-dialect</artifactId>-->
<!--<version>0.1.0</version>-->
<!--</dependency>-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
</dependencies>
<build>
<plugins>
<!-- 指定jdk -->
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-surefire-plugin</artifactId>
<version>${maven-surefire-plugin.version}</version>
<configuration>
<skipTests>true</skipTests> <!--默认关掉单元测试 -->
</configuration>
</plugin>
</plugins>
</build>
</project>
以上文件中关键的依赖如下:
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.27.2</version>
</dependency>
<dependency>
<groupId>com.zsoltfabok</groupId>
<artifactId>sqlite-dialect</artifactId>
<version>1.0</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.1</version>
</dependency>
3.项目配置文件
application-dev.properties
server.port=8080
spring.application.name=sqlite-test
spring.datasource.druid.url=jdbc:sqlite::resource:db/sqlite.db
spring.datasource.druid.username=
spring.datasource.druid.password=
spring.datasource.druid.driver-class-name=org.sqlite.JDBC
# 连接池配置,说明请参考Druid Wiki,DruidDataSource配置属性列表
# 初始化大小,最小,最大
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-active=20
# 配置获取连接等待超时的时间
spring.datasource.druid.max-wait=60000
#是否缓存preparedStatement,也就是PSCache。PSCache对支持游标的数据库性能提升巨大,比如说oracle。在mysql下建议关闭。
spring.datasource.druid.pool-prepared-statements=false
#要启用PSCache,必须配置大于0,当大于0时,poolPreparedStatements自动触发修改为true。在Druid中,不会存在Oracle下PSCache占用内存过多的问题,可以把这个数值配置大一些,比如说100
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=-1
#等价于上面的max-pool-prepared-statement-per-connection-size
#spring.datasource.druid.max-open-prepared-statements=-1
#用来检测连接是否有效的sql,要求是一个查询语句,常用select 'x'。如果validationQuery为null,testOnBorrow、testOnReturn、testWhileIdle都不会起作用。
spring.datasource.druid.validation-query=SELECT '1' from sqlite_master
#单位:秒,检测连接是否有效的超时时间。底层调用jdbc Statement对象的void setQueryTimeout(int seconds)方法
spring.datasource.druid.validation-query-timeout=3
#申请连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。
spring.datasource.druid.test-on-borrow=true
#归还连接时执行validationQuery检测连接是否有效,做了这个配置会降低性能。
spring.datasource.druid.test-on-return=false
#建议配置为true,不影响性能,并且保证安全性。申请连接的时候检测,如果空闲时间大于timeBetweenEvictionRunsMillis,执行validationQuery检测连接是否有效。
spring.datasource.druid.test-while-idle=true
#有两个含义:
#1) Destroy线程会检测连接的间隔时间,如果连接空闲时间大于等于minEvictableIdleTimeMillis则关闭物理连接。
#2) testWhileIdle的判断依据,详细看testWhileIdle属性的说明
spring.datasource.druid.time-between-eviction-runs-millis=60000
spring.datasource.druid.min-evictable-idle-time-millis=300000
#不再使用
#spring.datasource.druid.max-evictable-idle-time-millis=
#默认值stat,配置多个英文逗号分隔
spring.datasource.druid.filters= stat,log4j
######Druid监控配置######
# WebStatFilter配置,说明请参考Druid Wiki,配置_配置WebStatFilter
spring.datasource.druid.web-stat-filter.enabled=false
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*
spring.datasource.druid.web-stat-filter.session-stat-enable=true
spring.datasource.druid.web-stat-filter.session-stat-max-count=1000
spring.datasource.druid.web-stat-filter.principal-session-name=
spring.datasource.druid.web-stat-filter.principal-cookie-name=
spring.datasource.druid.web-stat-filter.profile-enable=
# StatViewServlet配置,说明请参考Druid Wiki,配置_StatViewServlet配置
spring.datasource.druid.stat-view-servlet.enabled=false
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.reset-enable=false
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=123456
spring.datasource.druid.stat-view-servlet.allow=
spring.datasource.druid.stat-view-servlet.deny=
# Spring监控配置,说明请参考Druid Github Wiki,配置_Druid和Spring关联监控配置`
# Spring监控AOP切入点,如x.y.z.service.*,配置多个英文逗号分隔
spring.datasource.druid.aop-patterns= com.*.service.*
# 如果spring.datasource.druid.aop-patterns要代理的类没有定义interface请设置spring.aop.proxy-target-class=true
########################################################
### Java Persistence Api
########################################################
# Specify the DBMS
spring.jpa.database-platform=org.hibernate.dialect.SQLiteDialect
# Show or not log for each sql query
spring.jpa.show-sql=true
# Hibernate ddl auto (create, create-drop, update)
spring.jpa.hibernate.ddl-auto=none
# Naming strategy 命名策略
spring.jpa.hibernate.naming.strategy=org.hibernate.cfg.ImprovedNamingStrategy
#spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
# RDBMS 方言, SQLiteDialect
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLiteDialect
spring.jpa.properties.hibernate.event.merge.entity_copy_observer = allow
spring.aop.proxy-target-class=true
#url: jdbc:sqlite::resource:static/sqlite/xy.db
#\u70ED\u90E8\u7F72\u751F\u6548
#spring.devtools.restart.enabled=true
#\u8BBE\u7F6E\u91CD\u542F\u7684\u76EE\u5F55
#spring.devtools.restart.additional-paths: src/main/java
#classpath\u76EE\u5F55\u4E0B\u7684WEB-INF\u6587\u4EF6\u5939\u5185\u5BB9\u4FEE\u6539\u4E0D\u91CD\u542F
spring.devtools.restart.exclude: WEB-INF/**
以上内容中关键配置如下:
spring.datasource.druid.url=jdbc:sqlite::resource:db/sqlite.db
spring.datasource.druid.driver-class-name=org.sqlite.JDBC
spring.datasource.druid.pool-prepared-statements=false
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=-1
1.这里必须禁用PSCache,必须配置max-pool-prepared-statement-per-connection-size=-1,当大于0时,poolPreparedStatements自动触发修改为true。
2.url=jdbc:sqlite::resource:db/sqlite.db表示数据库文件sqlite.db存放在resources的db目录下
4.创建表
CREATE TABLE charger_map
(
charger_id VARCHAR(8),
station_id VARCHAR(30),
pile_id VARCHAR(30),
gun_count INT,
charge_type INT,
operator_id VARCHAR(30),
area_code VARCHAR(30),
secret_key VARCHAR(30),
server_url VARCHAR(100),
server_port INT,
qrcode_prefix VARCHAR(500)
);
5.创建domain、repository、service、controller
package com.whitefox.domain;
import javax.persistence.*;
@Entity
@Table(name = "charger_map", schema = "main", catalog = "")
public class ChargerMap {
private String chargerId;
private String stationId;
private String pileId;
private int gunCount;
private int chargeType;
private String operatorId;
private String areaCode;
private String secretKey;
private String serverUrl;
private int serverPort;
private String qrcodePrefix;
@Id
@Column(name = "charger_id", nullable = true, length = 8)
public String getChargerId() {
return chargerId;
}
public void setChargerId(String chargerId) {
this.chargerId = chargerId;
}
@Basic
@Column(name = "station_id", nullable = true, length = 30)
public String getStationId() {
return stationId;
}
public void setStationId(String stationId) {
this.stationId = stationId;
}
@Basic
@Column(name = "pile_id", nullable = true, length = 30)
public String getPileId() {
return pileId;
}
public void setPileId(String pileId) {
this.pileId = pileId;
}
@Basic
@Column(name = "gun_count", nullable = true)
public int getGunCount() {
return gunCount;
}
public void setGunCount(int gunCount) {
this.gunCount = gunCount;
}
@Basic
@Column(name = "charge_type", nullable = true)
public int getChargeType() {
return chargeType;
}
public void setChargeType(int chargeType) {
this.chargeType = chargeType;
}
@Basic
@Column(name = "operator_id", nullable = true, length = 30)
public String getOperatorId() {
return operatorId;
}
public void setOperatorId(String operatorId) {
this.operatorId = operatorId;
}
@Basic
@Column(name = "area_code", nullable = true, length = 30)
public String getAreaCode() {
return areaCode;
}
public void setAreaCode(String areaCode) {
this.areaCode = areaCode;
}
@Basic
@Column(name = "secret_key", nullable = true, length = 30)
public String getSecretKey() {
return secretKey;
}
public void setSecretKey(String secretKey) {
this.secretKey = secretKey;
}
@Basic
@Column(name = "server_url", nullable = true, length = 100)
public String getServerUrl() {
return serverUrl;
}
public void setServerUrl(String serverUrl) {
this.serverUrl = serverUrl;
}
@Basic
@Column(name = "server_port", nullable = true)
public int getServerPort() {
return serverPort;
}
public void setServerPort(int serverPort) {
this.serverPort = serverPort;
}
@Basic
@Column(name = "qrcode_prefix", nullable = true, length = 500)
public String getQrcodePrefix() {
return qrcodePrefix;
}
public void setQrcodePrefix(String qrcodePrefix) {
this.qrcodePrefix = qrcodePrefix;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
ChargerMap that = (ChargerMap) o;
if (chargerId != null ? !chargerId.equals(that.chargerId) : that.chargerId != null) return false;
if (stationId != null ? !stationId.equals(that.stationId) : that.stationId != null) return false;
if (pileId != null ? !pileId.equals(that.pileId) : that.pileId != null) return false;
if (gunCount!=that.gunCount) return false;
if (chargeType != that.chargeType ) return false;
if (operatorId != null ? !operatorId.equals(that.operatorId) : that.operatorId != null) return false;
if (areaCode != null ? !areaCode.equals(that.areaCode) : that.areaCode != null) return false;
if (secretKey != null ? !secretKey.equals(that.secretKey) : that.secretKey != null) return false;
if (serverUrl != null ? !serverUrl.equals(that.serverUrl) : that.serverUrl != null) return false;
if (chargeType != that.serverPort) return false;
if (qrcodePrefix != null ? !qrcodePrefix.equals(that.qrcodePrefix) : that.qrcodePrefix != null) return false;
return true;
}
@Override
public int hashCode() {
int result = chargerId != null ? chargerId.hashCode() : 0;
result = 31 * result + (stationId != null ? stationId.hashCode() : 0);
result = 31 * result + (pileId != null ? pileId.hashCode() : 0);
result = 31 * result + gunCount;
result = 31 * result + chargeType;
result = 31 * result + (operatorId != null ? operatorId.hashCode() : 0);
result = 31 * result + (areaCode != null ? areaCode.hashCode() : 0);
result = 31 * result + (secretKey != null ? secretKey.hashCode() : 0);
result = 31 * result + (serverUrl != null ? serverUrl.hashCode() : 0);
result = 31 * result + serverPort;
result = 31 * result + (qrcodePrefix != null ? qrcodePrefix.hashCode() : 0);
return result;
}
}
package com.whitefox.repository;
import com.whitefox.domain.ChargerMap;
import org.springframework.data.jpa.repository.JpaRepository;
import java.util.List;
public interface ChargerMapRepo extends JpaRepository<ChargerMap,String> {
/**
* 根据pileId返回实体
* @param pileId
* @return
*/
ChargerMap findFirstByPileId(String pileId);
List<ChargerMap> findAllByChargerIdNotNullOrderByChargerId();
}
package com.whitefox.service;
import com.whitefox.domain.ChargerMap;
import java.util.List;
public interface ChargerService {
List<ChargerMap> findChargerList();
}
package com.whitefox.service;
import com.whitefox.domain.ChargerMap;
import com.whitefox.repository.ChargerMapRepo;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class ChargerServiceImpl implements ChargerService {
@Autowired
private ChargerMapRepo chargerMapRepo;
@Override
public List<ChargerMap> findChargerList() {
return chargerMapRepo.findAllByChargerIdNotNullOrderByChargerId();
}
}
package com.whitefox.controller;
import com.whitefox.domain.ChargerMap;
import com.whitefox.service.ChargerService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
public class ChargerController {
@Autowired
private ChargerService chargerService;
@GetMapping(value = "test")
public List<ChargerMap> findAll(){
return chargerService.findChargerList();
}
}
6.总结
spring.datasource.druid.url=jdbc:sqlite::resource:db/sqlite.db 文件名及路径必须正确,否则报错找不到数据库。
PSCache 必须禁用。spring.datasource.druid.pool-prepared-statements=false
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=-1
sqlite方言依赖必须正确。com.zsoltfabok:sqlite-dialect-1.0.jar