当前位置：首页 > news >正文

免费网站建设报价南宁网站推广哪家好

news 2025/7/30 3:15:43

免费网站建设报价,南宁网站推广哪家好,国内优秀网站设计欣赏,邯郸双曜网络科技有限公司这里写目录标题一、简介二、使用1. Java项目中（1）引入驱动（2）工具类（3）调用举例 2. sqlite-devel in linuxsqlite-devel使用三、更多应用1. 数据类型2. 如何存储日期和时间3. 备份一、简介非常轻量级&…

这里写目录标题

一、简介
二、使用
- 1. Java项目中
- - （1）引入驱动
  - （2）工具类
  - （3）调用举例
- 2. sqlite-devel in linux
- - sqlite-devel
  - 使用
三、更多应用
- 1. 数据类型
- 2. 如何存储日期和时间
- 3. 备份

一、简介

非常轻量级，都没有服务器进程（mysql必须要有mysqld.service 3306）
一个.db或.sqlite文件就是一个数据库, 非常方便备份和传输，只要复制文件就可以

sqlite 是本地数据库，不能远程。安全！
SQLite 在任何时刻只允许一个写入操作执行，其他写入操作需要排队
数据库就是一个文件，这个文件可以在任意位置，任意后缀名，建议用.db 或者 .sqlite 作为后缀

二、使用

1. Java项目中

（1）引入驱动

       <dependency><groupId>org.xerial</groupId><artifactId>sqlite-jdbc</artifactId><version>3.8.11.2</version></dependency>

（2）工具类

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, ResultSetExtractor<T> rse) throws SQLException, ClassNotFoundException {try {resultSet = getStatement().executeQuery(sql);T rs = rse.extractData(resultSet);return rs;} finally {destroyed();}}/**返回对象集合**/public <T> List<T> executeQuery(String sql, RowMapper<T> rm) throws SQLException, ClassNotFoundException {List<T> rsList = new ArrayList<T>();try {resultSet = getStatement().executeQuery(sql);while (resultSet.next()) {rsList.add(rm.mapRow(resultSet, resultSet.getRow()));}} finally {destroyed();}return rsList;}public <T> List<T> executeQueryList(String sql, Class<T> clazz) throws SQLException, ClassNotFoundException, IllegalAccessException, InstantiationException {List<T> rsList = new ArrayList<T>();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, Class<T> 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(List<String> sqls) throws SQLException, ClassNotFoundException {try {for (String sql : sqls) {getStatement().executeUpdate(sql);}} finally {destroyed();}}/**数据插入更新**/public int executeInsert(String tableName, Map<String,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 RowMapper<T> {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 ResultSetExtractor<T> {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 连接不需要用户名和密码

[root@localhost 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