springboot+JPA+druid+sqlite集成总结

我会带着你远行 2022-12-23 13:25 157阅读 0赞

# 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/> 
        </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>
            
            
                
                
                
            
            
            
                
                
                
            
            
                
                
                
            
    
            <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>
            
           
        </dependencies>
        <build>
            <plugins>
                
                <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