springboot+JPA+druid+sqlite集成总结

最新推荐文章于 2023-06-27 15:19:26 发布

carcoon

最新推荐文章于 2023-06-27 15:19:26 发布

阅读量2k

点赞数

分类专栏： spring boot 文章标签： spring boot sqlite3

本文链接：https://blog.csdn.net/carcoon/article/details/106007153

版权

spring boot 专栏收录该内容

4 篇文章 1 订阅

订阅专栏

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>-->
        <!--&lt;!&ndash; https://mvnrepository.com/artifact/org.hibernate/hibernate-entitymanager &ndash;&gt;-->
        <!--<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

carcoon

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
springboot+JPA+druid+sqlite集成总结

1.版本springboot 采用1.5.19JPA 采用2.12.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.or
复制链接

扫一扫