how2j.cn

下载区
文件名 文件大小
springboot.rar 1m
步骤 1 : SQLite 介绍   
步骤 2 : 先运行,看到效果,再学习   
步骤 3 : 模仿和排错   
步骤 4 : 基于前面的知识点   
步骤 5 : SQLite 方言一堆   
步骤 6 : application.properties   
步骤 7 : pom.xml   
步骤 8 : 重启运行   

SQLite 是一种数据库,它是跑在 JVM里面的,所以不需要像 mysql 那样得独立安装配置,而是直接拿来就用。。。
本知识点就会把对mysql 的依赖,建立在 sqlite 上,这样大家跑起来就不用费神地安装配置 mysql 数据了啦
步骤 2 :

先运行,看到效果,再学习

edit
老规矩,先下载右上角的可运行项目,配置运行起来,确认可用之后,再学习做了哪些步骤以达到这样的效果。
如图所示,效果和使用 mysql 一样, CRUD和分页都有~
测试地址:

http://127.0.0.1:8080/listCategory

注: 启动方式是Springboot特有的,直接运行类:com.how2java.springboot.Application 的主方法。
先运行,看到效果,再学习
在确保可运行项目能够正确无误地运行之后,再严格照着教程的步骤,对代码模仿一遍。
模仿过程难免代码有出入,导致无法得到期望的运行结果,此时此刻通过比较正确答案 ( 可运行项目 ) 和自己的代码,来定位问题所在。
采用这种方式,学习有效果,排错有效率,可以较为明显地提升学习速度,跨过学习路上的各个槛。

推荐使用diffmerge软件,进行文件夹比较。把你自己做的项目文件夹,和我的可运行项目文件夹进行比较。
这个软件很牛逼的,可以知道文件夹里哪两个文件不对,并且很明显地标记出来
这里提供了绿色安装和使用教程:diffmerge 下载和使用教程
步骤 4 :

基于前面的知识点

edit
本知识点是建立在上一个知识点 Springboot使用JPA实现完整的增删改查 CRUD和分页 可运行项目的基础上进行的改进,所以最好把上个知识点理解和消化了.
步骤 5 :

SQLite 方言一堆

edit
因为是使用 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; }; }
步骤 6 :

application.properties

edit
对 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
sarawang

<dependency> <groupId>org.hibernate.orm</groupId> <artifactId>hibernate-community-dialects</artifactId> <version>6.1.7.Final</version> spring.jpa.database-platform=org.hibernate.community.dialect.SQLiteDialect 参考:https://www.baeldung.com/spring-boot-sqlite https://github.com/eugenp/tutorials/tree/master/persistence-modules/spring-data-rest







回答已经提交成功,正在审核。 请于 我的回答 处查看回答记录,谢谢
答案 或者 代码至少填写一项, 如果是自己有问题,请重新提问,否则站长有可能看不到




2021-11-21 【Hibernate5方言包】SpringBoot2版本SQLite方言包
萌森




SpringBoot2基本使用Hibernate5 由于Hibernate5,不能用现成的com.enigmabridge hibernate4-sqlite-dialect方言包(方言包与本站相同) 我解决是参考博客https://www.jianshu.com/p/ff56015f0e33
加载中

							

							





回答已经提交成功,正在审核。 请于 我的回答 处查看回答记录,谢谢
答案 或者 代码至少填写一项, 如果是自己有问题,请重新提问,否则站长有可能看不到





2021-04-02 引用父类报错
2020-11-03 这三类从哪里找到
2020-08-26 SQLiteDialect配置的三个类的作用是什么,因为有找到只配置SQLiteDialect就能工作的。


提问太多,页面渲染太慢,为了加快渲染速度,本页最多只显示几条提问。还有 6 条以前的提问,请 点击查看

提问之前请登陆
提问已经提交成功,正在审核。 请于 我的提问 处查看提问记录,谢谢
关于 JAVA 框架-SpringBoot-SQLite 的提问

尽量提供截图代码异常信息,有助于分析和解决问题。 也可进本站QQ群交流: 578362961
提问尽量提供完整的代码,环境描述,越是有利于问题的重现,您的问题越能更快得到解答。
对教程中代码有疑问,请提供是哪个步骤,哪一行有疑问,这样便于快速定位问题,提高问题得到解答的速度
在已经存在的几千个提问里,有相当大的比例,是因为使用了和站长不同版本的开发环境导致的,比如 jdk, eclpise, idea, mysql,tomcat 等等软件的版本不一致。
请使用和站长一样的版本,可以节约自己大量的学习时间。 站长把教学中用的软件版本整理了,都统一放在了这里, 方便大家下载: https://how2j.cn/k/helloworld/helloworld-version/1718.html

上传截图