|
|
|
步骤 2 : 先运行，看到效果，再学习 步骤 3 : 模仿和排错 步骤 4 : 基于前面的知识点 步骤 5 : SQLite 方言一堆 步骤 6 : application.properties 步骤 7 : pom.xml 步骤 8 : 重启运行
SQLite 是一种数据库，它是跑在 JVM里面的，所以不需要像 mysql 那样得独立安装配置，而是直接拿来就用。。。
本知识点就会把对mysql 的依赖，建立在 sqlite 上，这样大家跑起来就不用费神地安装配置 mysql 数据了啦
老规矩，先下载右上角的可运行项目，配置运行起来，确认可用之后，再学习做了哪些步骤以达到这样的效果。
如图所示，效果和使用 mysql 一样， CRUD和分页都有~ 测试地址： http://127.0.0.1:8080/listCategory 注： 启动方式是Springboot特有的，直接运行类：com.how2java.springboot.Application 的主方法。
在确保可运行项目能够正确无误地运行之后，再严格照着教程的步骤，对代码模仿一遍。
模仿过程难免代码有出入，导致无法得到期望的运行结果，此时此刻通过比较正确答案 ( 可运行项目 ) 和自己的代码，来定位问题所在。 采用这种方式，学习有效果，排错有效率，可以较为明显地提升学习速度，跨过学习路上的各个槛。 推荐使用diffmerge软件，进行文件夹比较。把你自己做的项目文件夹，和我的可运行项目文件夹进行比较。 这个软件很牛逼的，可以知道文件夹里哪两个文件不对，并且很明显地标记出来 这里提供了绿色安装和使用教程：diffmerge 下载和使用教程
本知识点是建立在上一个知识点 Springboot使用JPA实现完整的增删改查 CRUD和分页 可运行项目的基础上进行的改进,所以最好把上个知识点理解和消化了.
因为是使用 JPA 来链接 SQlite, 而 JPA 默认用的是 Hibernate，所以要为 Hibernate 配置专门的方言。
方言是什么意思呢？ 为了更好地和四川人打交道，最好说四川方言。 为了更好地和浙江人打交道，最好说浙江方言。 为了更好地和 sqlite 打交道，最好说 sqlite 方言。 方言就是这么个意思啦，方便 Hibernate和 Sqlite打交道。 方言的英文是 Dialect， 所以就有了SQLiteDialect 这个类了。 除此之外，还需要其他两个类配合。 至于他们是怎么工作的。。。就不用关心啦，反正能用就行啦。。。 package com.how2java.sqlite;
import java.sql.SQLException;
import java.sql.Types;
import org.hibernate.JDBCException;
import org.hibernate.ScrollMode;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.function.AbstractAnsiTrimEmulationFunction;
import org.hibernate.dialect.function.NoArgSQLFunction;
import org.hibernate.dialect.function.SQLFunction;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.dialect.function.VarArgsSQLFunction;
import org.hibernate.dialect.identity.IdentityColumnSupport;
import org.hibernate.dialect.pagination.AbstractLimitHandler;
import org.hibernate.dialect.pagination.LimitHandler;
import org.hibernate.dialect.pagination.LimitHelper;
import org.hibernate.dialect.unique.DefaultUniqueDelegate;
import org.hibernate.dialect.unique.UniqueDelegate;
import org.hibernate.engine.spi.RowSelection;
import org.hibernate.exception.DataException;
import org.hibernate.exception.JDBCConnectionException;
import org.hibernate.exception.LockAcquisitionException;
import org.hibernate.exception.spi.SQLExceptionConversionDelegate;
import org.hibernate.exception.spi.TemplatedViolatedConstraintNameExtracter;
import org.hibernate.exception.spi.ViolatedConstraintNameExtracter;
import org.hibernate.internal.util.JdbcExceptionHelper;
import org.hibernate.mapping.Column;
import org.hibernate.type.StandardBasicTypes;
public class SQLiteDialect extends Dialect {
private final UniqueDelegate uniqueDelegate;
public SQLiteDialect() {
registerColumnType(Types.BIT, "boolean");
registerColumnType(Types.DECIMAL, "decimal");
registerColumnType(Types.CHAR, "char");
registerColumnType(Types.LONGVARCHAR, "longvarchar");
registerColumnType(Types.TIMESTAMP, "datetime");
registerColumnType(Types.BINARY, "blob");
registerColumnType(Types.VARBINARY, "blob");
registerColumnType(Types.LONGVARBINARY, "blob");
registerFunction("concat", new VarArgsSQLFunction(StandardBasicTypes.STRING, "", "||", ""));
registerFunction("mod", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "?1 % ?2"));
registerFunction("quote", new StandardSQLFunction("quote", StandardBasicTypes.STRING));
registerFunction("random", new NoArgSQLFunction("random", StandardBasicTypes.INTEGER));
registerFunction("round", new StandardSQLFunction("round"));
registerFunction("substr", new StandardSQLFunction("substr", StandardBasicTypes.STRING));
registerFunction("trim", new AbstractAnsiTrimEmulationFunction() {
@Override
protected SQLFunction resolveBothSpaceTrimFunction() {
return new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(?1)");
}
@Override
protected SQLFunction resolveBothSpaceTrimFromFunction() {
return new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(?2)");
}
@Override
protected SQLFunction resolveLeadingSpaceTrimFunction() {
return new SQLFunctionTemplate(StandardBasicTypes.STRING, "ltrim(?1)");
}
@Override
protected SQLFunction resolveTrailingSpaceTrimFunction() {
return new SQLFunctionTemplate(StandardBasicTypes.STRING, "rtrim(?1)");
}
@Override
protected SQLFunction resolveBothTrimFunction() {
return new SQLFunctionTemplate(StandardBasicTypes.STRING, "trim(?1, ?2)");
}
@Override
protected SQLFunction resolveLeadingTrimFunction() {
return new SQLFunctionTemplate(StandardBasicTypes.STRING, "ltrim(?1, ?2)");
}
@Override
protected SQLFunction resolveTrailingTrimFunction() {
return new SQLFunctionTemplate(StandardBasicTypes.STRING, "rtrim(?1, ?2)");
}
});
uniqueDelegate = new SQLiteUniqueDelegate(this);
}
private static final SQLiteDialectIdentityColumnSupport IDENTITY_COLUMN_SUPPORT = new
SQLiteDialectIdentityColumnSupport(new SQLiteDialect());
@Override
public IdentityColumnSupport getIdentityColumnSupport() {
return IDENTITY_COLUMN_SUPPORT;
}
// limit/offset support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
private static final AbstractLimitHandler LIMIT_HANDLER = new AbstractLimitHandler() {
@Override
public String processSql(String sql, RowSelection selection) {
final boolean hasOffset = LimitHelper.hasFirstRow(selection);
return sql + (hasOffset ? " limit ? offset ?" : " limit ?");
}
@Override
public boolean supportsLimit() {
return true;
}
@Override
public boolean bindLimitParametersInReverseOrder() {
return true;
}
};
@Override
public LimitHandler getLimitHandler() {
return LIMIT_HANDLER;
}
// lock acquisition support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
@Override
public boolean supportsLockTimeouts() {
// may be http://sqlite.org/c3ref/db_mutex.html ?
return false;
}
@Override
public String getForUpdateString() {
return "";
}
@Override
public boolean supportsOuterJoinForUpdate() {
return false;
}
// current timestamp support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
@Override
public boolean supportsCurrentTimestampSelection() {
return true;
}
@Override
public boolean isCurrentTimestampSelectStringCallable() {
return false;
}
@Override
public String getCurrentTimestampSelectString() {
return "select current_timestamp";
}
// SQLException support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
private static final int SQLITE_BUSY = 5;
private static final int SQLITE_LOCKED = 6;
private static final int SQLITE_IOERR = 10;
private static final int SQLITE_CORRUPT = 11;
private static final int SQLITE_NOTFOUND = 12;
private static final int SQLITE_FULL = 13;
private static final int SQLITE_CANTOPEN = 14;
private static final int SQLITE_PROTOCOL = 15;
private static final int SQLITE_TOOBIG = 18;
private static final int SQLITE_CONSTRAINT = 19;
private static final int SQLITE_MISMATCH = 20;
private static final int SQLITE_NOTADB = 26;
@Override
public SQLExceptionConversionDelegate buildSQLExceptionConversionDelegate() {
return new SQLExceptionConversionDelegate() {
@Override
public JDBCException convert(SQLException sqlException, String message, String sql) {
final int errorCode = JdbcExceptionHelper.extractErrorCode(sqlException) & 0xFF;
if (errorCode == SQLITE_TOOBIG || errorCode == SQLITE_MISMATCH) {
return new DataException(message, sqlException, sql);
} else if (errorCode == SQLITE_BUSY || errorCode == SQLITE_LOCKED) {
return new LockAcquisitionException(message, sqlException, sql);
} else if ((errorCode >= SQLITE_IOERR && errorCode <= SQLITE_PROTOCOL) || errorCode == SQLITE_NOTADB) {
return new JDBCConnectionException(message, sqlException, sql);
}
// returning null allows other delegates to operate
return null;
}
};
}
@Override
public ViolatedConstraintNameExtracter getViolatedConstraintNameExtracter() {
return EXTRACTER;
}
private static final ViolatedConstraintNameExtracter EXTRACTER = new TemplatedViolatedConstraintNameExtracter() {
@Override
protected String doExtractConstraintName(SQLException sqle) throws NumberFormatException {
final int errorCode = JdbcExceptionHelper.extractErrorCode(sqle) & 0xFF;
if (errorCode == SQLITE_CONSTRAINT) {
return extractUsingTemplate("constraint ", " failed", sqle.getMessage());
}
return null;
}
};
// union subclass support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
@Override
public boolean supportsUnionAll() {
return true;
}
// DDL support ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
@Override
public boolean canCreateSchema() {
return false;
}
@Override
public boolean hasAlterTable() {
// As specified in NHibernate dialect
return false;
}
@Override
public boolean dropConstraints() {
return false;
}
@Override
public boolean qualifyIndexName() {
return false;
}
@Override
public String getAddColumnString() {
return "add column";
}
@Override
public String getDropForeignKeyString() {
throw new UnsupportedOperationException("No drop foreign key syntax supported by SQLiteDialect");
}
@Override
public String getAddForeignKeyConstraintString(String constraintName,
String[] foreignKey, String referencedTable, String[] primaryKey,
boolean referencesPrimaryKey) {
throw new UnsupportedOperationException("No add foreign key syntax supported by SQLiteDialect");
}
@Override
public String getAddPrimaryKeyConstraintString(String constraintName) {
throw new UnsupportedOperationException("No add primary key syntax supported by SQLiteDialect");
}
@Override
public boolean supportsCommentOn() {
return true;
}
@Override
public boolean supportsIfExistsBeforeTableName() {
return true;
}
@Override
public boolean doesReadCommittedCauseWritersToBlockReaders() {
// TODO Validate (WAL mode...)
return true;
}
@Override
public boolean doesRepeatableReadCauseReadersToBlockWriters() {
return true;
}
@Override
public boolean supportsTupleDistinctCounts() {
return false;
}
@Override
public int getInExpressionCountLimit() {
return 1000;
}
@Override
public UniqueDelegate getUniqueDelegate() {
return uniqueDelegate;
}
private static class SQLiteUniqueDelegate extends DefaultUniqueDelegate {
public SQLiteUniqueDelegate(Dialect dialect) {
super(dialect);
}
@Override
public String getColumnDefinitionUniquenessFragment(Column column) {
return " unique";
}
}
@Override
public String getSelectGUIDString() {
return "select hex(randomblob(16))";
}
@Override
public ScrollMode defaultScrollMode() {
return ScrollMode.FORWARD_ONLY;
}
}
package com.how2java.sqlite;
import org.hibernate.dialect.Dialect;
import org.hibernate.dialect.identity.IdentityColumnSupportImpl;
public class SQLiteDialectIdentityColumnSupport extends IdentityColumnSupportImpl {
public SQLiteDialectIdentityColumnSupport(Dialect dialect) {
super(dialect);
}
@Override
public boolean supportsIdentityColumns() {
return true;
}
@Override
public boolean hasDataTypeInIdentityColumn() {
return false;
}
@Override
public String getIdentitySelectString(String table, String column, int type) {
return "select last_insert_rowid()";
}
@Override
public String getIdentityColumnString(int type) {
return "integer";
}
}
package com.how2java.sqlite;
import org.hibernate.boot.MetadataBuilder;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.spi.MetadataBuilderInitializer;
import org.hibernate.engine.jdbc.dialect.internal.DialectResolverSet;
import org.hibernate.engine.jdbc.dialect.spi.DialectResolver;
import org.jboss.logging.Logger;
public class SQLiteMetadataBuilderInitializer implements MetadataBuilderInitializer {
private final static Logger logger = Logger.getLogger(SQLiteMetadataBuilderInitializer.class);
@Override
public void contribute(MetadataBuilder metadataBuilder, StandardServiceRegistry serviceRegistry) {
DialectResolver dialectResolver = serviceRegistry.getService(DialectResolver.class);
if (!(dialectResolver instanceof DialectResolverSet)) {
logger.warnf("DialectResolver '%s' is not an instance of DialectResolverSet, not registering SQLiteDialect",
dialectResolver);
return;
}
((DialectResolverSet) dialectResolver).addResolver(resolver);
}
static private final SQLiteDialect dialect = new SQLiteDialect();
static private final DialectResolver resolver = (DialectResolver) info -> {
if (info.getDatabaseName().equals("SQLite")) {
return dialect;
}
return null;
};
}
对 mysql 的配置，修改到了对 sqlite 的配置，都做了注释，很简单明了
spring.mvc.view.prefix=/WEB-INF/jsp/
spring.mvc.view.suffix=.jsp
#spring.datasource.url=jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8
#spring.datasource.username=root
#spring.datasource.password=admin
#spring.datasource.driver-class-name=com.mysql.jdbc.Driver
#spring.jpa.properties.hibernate.hbm2ddl.auto=update
spring.jpa.database-platform=com.how2java.sqlite.SQLiteDialect
#表结构由hibernate根据实体类来帮你创建
spring.jpa.generate-ddl=true
#自动根据Entity配置创建表
spring.jpa.hibernate.ddl-auto=update
#数据库文件位置
spring.datasource.url=jdbc:sqlite:how2j.db
#驱动名称
spring.datasource.driver-class-name=org.sqlite.JDBC
spring.mvc.view.prefix=/WEB-INF/jsp/ spring.mvc.view.suffix=.jsp #spring.datasource.url=jdbc:mysql://127.0.0.1:3306/how2java?characterEncoding=UTF-8 #spring.datasource.username=root #spring.datasource.password=admin #spring.datasource.driver-class-name=com.mysql.jdbc.Driver #spring.jpa.properties.hibernate.hbm2ddl.auto=update spring.jpa.database-platform=com.how2java.sqlite.SQLiteDialect #表结构由hibernate根据实体类来帮你创建 spring.jpa.generate-ddl=true #自动根据Entity配置创建表 spring.jpa.hibernate.ddl-auto=update #数据库文件位置 spring.datasource.url=jdbc:sqlite:how2j.db #驱动名称 spring.datasource.driver-class-name=org.sqlite.JDBC
增加 sqlite jar 包
<?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.how2java</groupId>
<artifactId>springboot</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>springboot</name>
<description>springboot</description>
<packaging>war</packaging>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.9.RELEASE</version>
</parent>
<dependencies>
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<!-- servlet依赖. -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
</dependency>
<!-- tomcat的支持.-->
<dependency>
<groupId>org.apache.tomcat.embed</groupId>
<artifactId>tomcat-embed-jasper</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional> <!-- 这个需要为 true 热部署才有效 -->
</dependency>
<!-- mysql-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.21</version>
</dependency>
<!-- jpa-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
</dependencies>
<properties>
<java.version>1.8</java.version>
</properties>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
重启运行，然后访问如下地址就可以看到效果了啦，注： 刚开始没数据，要自己加
http://127.0.0.1:8080/listCategory
HOW2J公众号，关注后实时获知最新的教程和优惠活动，谢谢。
问答区域
2023-11-14
Hibernate 6 支持SQLite dialect
回答已经提交成功，正在审核。 请于 我的回答 处查看回答记录，谢谢
2021-11-21
【Hibernate5方言包】SpringBoot2版本SQLite方言包
回答已经提交成功，正在审核。 请于 我的回答 处查看回答记录，谢谢
2021-04-02
引用父类报错
2020-11-03
这三类从哪里找到
2020-08-26
SQLiteDialect配置的三个类的作用是什么，因为有找到只配置SQLiteDialect就能工作的。
提问太多，页面渲染太慢，为了加快渲染速度，本页最多只显示几条提问。还有 6 条以前的提问，请 点击查看
提问之前请登陆
提问已经提交成功，正在审核。 请于 我的提问 处查看提问记录，谢谢
|
|||||||||||||
