Java中应用SqlLite数据库

一、pom.xml中添加配置<dependency><groupId>org.xerial</groupId><artifactId>sqlite-jdbc</artifactId><version>3.8.11.2</version></dependency>二、工具类SqliteHelper.jav

菲斯Face

4388人浏览 · 2021-12-20 10:53:44

菲斯Face · 2021-12-20 10:53:44 发布

一、pom.xml中添加配置

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

二、工具类

SqliteHelper.java

package cn.com.learn.common;
 
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
 
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
 
/**
 * sqlite帮助类，直接创建该类示例，并调用相应的借口即可对sqlite数据库进行操作
 *
 * 本类基于 sqlite jdbc v56
 *
 * @author haoqipeng
 */
public class SqliteHelper {
    final static Logger logger = LoggerFactory.getLogger(SqliteHelper.class);
 
    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;
    private String dbFilePath;
 
    /**
     * 构造函数
     * @param dbFilePath sqlite db 文件路径
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public SqliteHelper(String dbFilePath) throws ClassNotFoundException, SQLException {
        this.dbFilePath = dbFilePath;
        connection = getConnection(dbFilePath);
    }
 
    /**
     * 获取数据库连接
     * @param dbFilePath db文件路径
     * @return 数据库连接
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public Connection getConnection(String dbFilePath) throws ClassNotFoundException, SQLException {
        Connection conn = null;
        Class.forName("org.sqlite.JDBC");
        conn = DriverManager.getConnection("jdbc:sqlite:" + dbFilePath);
        return conn;
    }
 
    /**
     * 执行sql查询
     * @param sql sql select 语句
     * @param rse 结果集处理类对象
     * @return 查询结果
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    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();
        }
    }
 
    /**
     * 执行select查询，返回结果列表
     *
     * @param sql sql select 语句
     * @param rm 结果集的行数据处理类对象
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    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;
    }
 
    /**
     * 执行数据库更新sql语句
     * @param sql
     * @return 更新行数
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public int executeUpdate(String sql) throws SQLException, ClassNotFoundException {
        try {
            int c = getStatement().executeUpdate(sql);
            return c;
        } finally {
            destroyed();
        }
 
    }
 
    /**
     * 执行多个sql更新语句
     * @param sqls
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void executeUpdate(String...sqls) throws SQLException, ClassNotFoundException {
        try {
            for (String sql : sqls) {
                getStatement().executeUpdate(sql);
            }
        } finally {
            destroyed();
        }
    }
 
    /**
     * 执行数据库更新 sql List
     * @param sqls sql列表
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void executeUpdate(List<String> sqls) throws SQLException, ClassNotFoundException {
        try {
            for (String sql : sqls) {
                getStatement().executeUpdate(sql);
            }
        } finally {
            destroyed();
        }
    }
 
    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 void destroyed() {
        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.error("Sqlite数据库关闭时异常", e);
        }
    }
 
    /**
     * 执行select查询，返回结果列表
     *
     * @param sql sql select 语句
     * @param clazz 实体泛型
     * @return 实体集合
     * @throws SQLException 异常信息
     * @throws ClassNotFoundException 异常信息
     */
    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;
    }
 
    /**
     * 执行sql查询,适用单条结果集
     * @param sql sql select 语句
     * @param clazz 结果集处理类对象
     * @return 查询结果
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    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();
        }
    }
 
    /**
     * 执行数据库更新sql语句
     * @param tableName 表名
     * @param param key-value键值对,key:表中字段名,value:值
     * @return 更新行数
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    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();
        }
 
    }
}

ResultSetExtractor.java

package cn.com.learn.common;
 
import java.sql.ResultSet;
 
public interface ResultSetExtractor<T> {
 
    public abstract T extractData(ResultSet rs);
 
}

RowMapper.java

package cn.com.learn.common;
 
import java.sql.ResultSet;
import java.sql.SQLException;
 
public interface RowMapper<T> {
    public abstract T mapRow(ResultSet rs, int index) throws SQLException;
}

SqlLiteDemoResult.java

package cn.com.learn.common;
 
/**
 * Created by WANG on 2016/7/15.
 */
public class SqlLiteDemoResult {
 
    private String bizNo;
    private String content;
    private String createTime;
 
    public String getBizNo() {
        return bizNo;
    }
 
    public void setBizNo(String bizNo) {
        this.bizNo = bizNo;
    }
 
    public String getContent() {
        return content;
    }
 
    public void setContent(String content) {
        this.content = content;
    }
 
    public String getCreateTime() {
        return createTime;
    }
 
    public void setCreateTime(String createTime) {
        this.createTime = createTime;
    }
}

测试类（调用实例）

package cn.com.learn;
 
import cn.com.learn.common.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
/**
 * sqlite帮助类，直接创建该类示例，并调用相应的借口即可对sqlite数据库进行操作
 *
 * 本类基于 sqlite jdbc v56
 *
 * @author haoqipeng
 */
public class SqliteTest {
    public static void main(String args[]) {
        try {
            SqliteHelper h = new SqliteHelper("testHelper.db");
            // drop表栗子
            //h.executeUpdate("drop table if exists test;");
            // create表栗子
            //h.executeUpdate("create table test(name varchar(20));");
 
            // insert数据栗子
            String UUID = CommonUtil.getUUID();
            Map<String,Object> map = new HashMap<String,Object>();
            map.put("UUID",UUID);
            map.put("BIZ_NO",CommonUtil.Random(10));
            map.put("CONTENT","举个例子");
            map.put("CREATE_TIME",DateUtil.getNowTime());
            h.executeInsert("NOTE",map);
            // UPDATE栗子
            StringBuffer updSql = new StringBuffer();
            updSql.append("UPDATE ");
            updSql.append("NOTE");
            updSql.append(" SET ");
            updSql.append(" CONTENT = '");
            updSql.append("改个栗子");;
            updSql.append("'");
            h.executeUpdate(updSql.toString());
            // 取list栗子
            List<SqlLiteDemoResult> demoList = h.executeQueryList("select BIZ_NO as bizNo,CONTENT as content,CREATE_TIME as createTime from NOTE", SqlLiteDemoResult.class);
            System.out.println("打印列表********?");
            for (SqlLiteDemoResult result:demoList) {
                System.out.println(result.getBizNo());
                System.out.println(result.getContent());
                System.out.println(result.getCreateTime());
            }
            System.out.println("打印列表********?");
            // 取单条栗子
            SqlLiteDemoResult demoResult = h.executeQuery("select BIZ_NO as bizNo,CONTENT as content,CREATE_TIME as createTime from NOTE WHERE UUID = '" + UUID + "'", SqlLiteDemoResult.class);
 
            System.out.println("打印单条********?");
            System.out.println(demoResult.getBizNo());
            System.out.println(demoResult.getContent());
            System.out.println(demoResult.getCreateTime());
            System.out.println("打印单条********?");
 
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
 
}

三、sqllite可以使用Navicat直接连接访问，使用方便。