使用springboot操作sqlite数据库

悠悠 2023-09-24 17:57 109阅读 0赞

### 一 pom文件导入依赖 ###

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

### 二 添加SqLiteHelper.java 类 ###

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 org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    /**
     * sqlite帮助类，直接创建该类示例，并调用相应的借口即可对sqlite数据库进行操作
     * 
     * 本类基于 sqlite jdbc v56
     * 
     * @author haoqipeng
     */
    public class SqLiteHelper{
        
         public static final Logger log = LoggerFactory.getLogger(SqliteHelper.class);
    
        private static String Drivde="org.sqlite.JDBC";
        static {
        
            try {
        
                Class.forName(Drivde);
            } catch (ClassNotFoundException e) {
        
                e.printStackTrace();
            }
        }
    
        private  static Statement statement;
    
        private static Connection connection;
    
        private static ResultSet  resultSet;
    
        private static String  toPath;
        /**
         * 构造函数
         * @param dbFilePath sqlite db 文件路径
         * @throws ClassNotFoundException
         * @throws SQLException
         */
        public SqliteHelper(String path){
        
            toPath = path;
        }
    
        //检查确认连接状态是否可用
        public static Statement getStatement(){
        
            if (statement==null){
        
                try {
        
                    connection=  DriverManager.getConnection("jdbc:sqlite:"+toPath);//连接数据库,不存在则创建
                    statement=connection.createStatement();
                } catch (SQLException e) {
        
                    e.printStackTrace();
                }
            }
            return statement;
        }
        
        /**
         * 执行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();
            }
        }
        
      
        
        
        /**
         * 数据库资源关闭和释放
         */
        public void destroyed() {
        
            try {
        
                if (null != connection) {
        
                    connection.close();
                    connection = null;
                }
                
                if (null != statement) {
        
                    statement.close();
                    statement = null;
                }
                
                if (null != resultSet) {
        
                    resultSet.close();
                    resultSet = null;
                }
            } catch (SQLException e) {
        
                logger.error("Sqlite数据库关闭时异常", e);
            }
        }
    }

### 三 ResltSetExtractor.java 结果集处理类 ###

import java.sql.ResultSet;
    
    public interface ResultSetExtractor<T> {
        
        
         T extractData(ResultSet rs);
    
    }

### 四 RowMapper.java 结果集行数据处理类 ###

package com.xjjc.sqlite;
    
    /**
     * <h3>basicapis_whm_mongodb</h3>
     * <p>结果集行数据处理类</p>
     *
     * @author : dkl
     * @date : 2023-02-13 10:15
     **/
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public interface RowMapper<T> {
        
        T mapRow(ResultSet rs, int index) throws SQLException;
    }

### 五 测试查询数据 ###

SqliteHelper sqliteHelper = new SqliteHelper("E:\\xijiaojince\\WH\\sqlite\\sqlitedata\\2022-12-05\\2022_12_11_12_09_04_无定位.db");
            try {
        
                List<PcmsDatas> sList = sqliteHelper.executeQuery("SELECT  d.id,d.name,d.ip,d.tcmsId,d.typeStr,d.state,d.dateTime,d.lastDate,d.name from Devices d ", (ResultSet rs, int index)->{
        
                        PcmsDatas pcmsDatas = new PcmsDatas();
                        pcmsDatas.setId(rs.getString("id"));
                        pcmsDatas.setMikm(rs.getString("ip"));
                        return pcmsDatas;
                });
                System.out.println(sList);
            } catch (Exception throwables) {
        
                throwables.printStackTrace();
            }