网站前台修改后台对接不上,微分销商城开发,四川网站营销seo什么价格,佛山企业网站建设平台这里写目录标题 一、简介二、使用1. Java项目中#xff08;1#xff09;引入驱动#xff08;2#xff09;工具类#xff08;3#xff09;调用举例 2. sqlite-devel in linuxsqlite-devel使用 三、更多应用1. 数据类型2. 如何存储日期和时间3. 备份 一、简介
非常轻量级1引入驱动2工具类3调用举例 2. sqlite-devel in linuxsqlite-devel使用 三、更多应用1. 数据类型2. 如何存储日期和时间3. 备份 一、简介
非常轻量级都没有服务器进程mysql必须要有mysqld.service 3306一个.db或.sqlite文件就是一个数据库, 非常方便备份和传输只要复制文件就可以
sqlite 是本地数据库不能远程。安全SQLite 在任何时刻只允许一个写入操作执行其他写入操作需要排队数据库就是一个文件这个文件可以在任意位置任意后缀名建议用.db 或者 .sqlite 作为后缀
二、使用
1. Java项目中
1引入驱动 dependencygroupIdorg.xerial/groupIdartifactIdsqlite-jdbc/artifactIdversion3.8.11.2/version/dependency2工具类
SqlLiteHelper
package sample.common.sqlLite;import sample.common.utils.LogUtil;import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.logging.Logger;/*** Author admin* Date 2023/4/4 11:20*/
public class SqlLiteHelper {private Connection connection;private Statement statement;private ResultSet resultSet;private String dbFilePath; // db文件的位置Logger logger LogUtil.installFormatter(Logger.getLogger(SqlLiteHelper.class.getSimpleName()));/*** 每次创建都将建立一个连接* param dbFilePath* throws ClassNotFoundException* throws SQLException*/public SqlLiteHelper(String dbFilePath) throws ClassNotFoundException, SQLException{this.dbFilePath dbFilePath;connection getConnection(dbFilePath);}public Connection getConnection(String dbFilePath) throws ClassNotFoundException, SQLException {Connection conn null;Class.forName(org.sqlite.JDBC);conn DriverManager.getConnection(jdbc:sqlite: dbFilePath);return conn;}private Connection getConnection() throws ClassNotFoundException, SQLException {if (null connection) connection getConnection(dbFilePath);return connection;}private Statement getStatement() throws SQLException, ClassNotFoundException {if (null statement) statement getConnection().createStatement();return statement;}/**返回对象**/public T T executeQuery(String sql, ResultSetExtractorT rse) throws SQLException, ClassNotFoundException {try {resultSet getStatement().executeQuery(sql);T rs rse.extractData(resultSet);return rs;} finally {destroyed();}}/**返回对象集合**/public T ListT executeQuery(String sql, RowMapperT rm) throws SQLException, ClassNotFoundException {ListT rsList new ArrayListT();try {resultSet getStatement().executeQuery(sql);while (resultSet.next()) {rsList.add(rm.mapRow(resultSet, resultSet.getRow()));}} finally {destroyed();}return rsList;}public T ListT executeQueryList(String sql, ClassT clazz) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {ListT rsList new ArrayListT();try {resultSet getStatement().executeQuery(sql);while (resultSet.next()) {T t clazz.newInstance();for (Field field : t.getClass().getDeclaredFields()) {field.setAccessible(true);field.set(t,resultSet.getObject(field.getName()));}rsList.add(t);}} finally {destroyed();}return rsList;}public T T executeQuery(String sql, ClassT clazz) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {try {resultSet getStatement().executeQuery(sql);T t clazz.newInstance();for (Field field : t.getClass().getDeclaredFields()) {field.setAccessible(true);// --- 连接断开了field.set(t,resultSet.getObject(field.getName()));}return t;} finally {destroyed();}}public int count(String sql) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {try {resultSet getStatement().executeQuery(sql);if(resultSet.next()){return resultSet.getInt(1);}} finally {destroyed();}return 0;}/**返回更新成功的条数**/public int executeUpdate(String sql) throws SQLException, ClassNotFoundException {try {int c getStatement().executeUpdate(sql);return c;} finally {destroyed();}}/**执行多个更新**/public void executeUpdate(String...sqls) throws SQLException, ClassNotFoundException {try {for (String sql : sqls) {getStatement().executeUpdate(sql);}} finally {destroyed();}}public void executeUpdate(ListString sqls) throws SQLException, ClassNotFoundException {try {for (String sql : sqls) {getStatement().executeUpdate(sql);}} finally {destroyed();}}/**数据插入更新**/public int executeInsert(String tableName, MapString,Object param) throws SQLException, ClassNotFoundException {try {StringBuffer sql new StringBuffer();sql.append(INSERT INTO );sql.append(tableName);sql.append( ( );for (String key : param.keySet()) {sql.append(key);sql.append(,);}sql.delete(sql.length()-1,sql.length());sql.append() VALUES ( );for (String key : param.keySet()) {sql.append();sql.append(param.get(key));sql.append(,);}sql.delete(sql.length()-1,sql.length());sql.append(););int c getStatement().executeUpdate(sql.toString());return c;} finally {destroyed();}}/**数据库资源关闭和释放**/public void destroyed() {// 每一次crud都关闭了所有的资源try {if (null ! statement) {statement.close();statement null;}if (null ! connection) {connection.close();connection null;}if (null ! resultSet) {resultSet.close();resultSet null;}} catch (SQLException e) {logger.info(Sqlite数据库关闭时异常e.getMessage());}}}
结果集实现
package sample.common.sqlLite;import java.sql.ResultSet;
import java.sql.SQLException;/*** Author admin* Date 2023/4/4 11:25*/
public interface RowMapperT {public abstract T mapRow(ResultSet rs, int index) throws SQLException;
}
package sample.common.sqlLite;import java.sql.ResultSet;/*** Author admin* Date 2023/4/4 11:24*/
public interface ResultSetExtractorT {public abstract T extractData(ResultSet resulltSet);
}
3调用举例
加载库和表
SqliteHelper sqlLiteHelper new SqlLiteHelper(dbFilePath); // 库
String createCard create table if not exists card(id integer primary key autoincrement,name text,lastReport text);
sqlLiteHelper.executeUpdate(createCard); // 建表2. sqlite-devel in linux
sqlite-devel
centos7.6 https://www.sqlite.org/download.html
yum install sqlite-devel使用
sqlite 连接不需要用户名和密码
[rootlocalhost trdp]# sqlite3 trdp.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite .database
# 输出显示了当前连接的数据库文件路径。
seq name file
--- --------------- ----------------------------------------------------------
0 main /usr/local/tynoo/trdp/trdp.dbsqlite .tables
msgs port
sqlite .schema port
CREATE TABLE port
(port String
);
sqlite select * from msgs;
sqlite insert into port (port) values (null);
sqlite .quit
三、更多应用
1. 数据类型
https://www.sqlite.net.cn/datatype3.html
2. 如何存储日期和时间 3. 备份
因为sqlite基于文件存储的特殊特性 它的备份本质上是这个sqlite_database.db这个文件的备份尤其是在单用户或低并发场景下SQLite 3 的数据备份可以通过简单的文件复制cp来实现
但是这里就存在几个问题 1文件 被锁定比如说正在写入数据 2事务不一致你复制的时候某个事务正在进行中
