JDBC操作SQLite的工具类

青旅半醒 2023-10-15 19:07 12阅读 0赞

> 直接调用无需拼装sql

### 注入依赖 ###

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

### 工具类 ###

import org.sqlite.SQLiteConnection;
    
    /**
     * @Author cpf
     * @Date 2023/9/8
     */
    import java.sql.*;
    
    public class SQLiteUtils {
        
        private static final String DB_FILE = "src/main/resources/database.db"; // SQLite数据库文件名
    
    
        public static void main(String[] args) throws SQLException {
        
            Connection conn = getConnection();
    
            //创建表
            String[] columns = new String[]{
        "id", "name", "age", "gender"};
            createTable(conn, "students", columns);
    
            //插入
            Object[] column = new Object[]{
        "name", "age", "gender"};
            Object[] values = new Object[]{
        "张三", "16", "汉族"};
            insertRecord(conn, "students",column, values);
    
            //查询所有记录
            ResultSet students = queryAll(conn, "students");
            while (students.next()){
        
                System.out.println(students.getString("name") + " | " + students.getString("age") + " | " + students.getString("gender"));
            }
    
            //查询指定字段的记录
            ResultSet resultSet = queryByColumn(conn, "students", "name");
            while (resultSet.next()){
        
                System.out.println("姓名: " + resultSet.getString("name"));
            }
    
            //查询指定条件的记录
            String[] column01 = new String[]{
        "name"};
            Object[] values01 = new Object[]{
        "张三"};
            ResultSet students1 = queryByCondition(conn, "students", column01, values01);
            while (students1.next()){
        
                System.out.println(students1.getString("name") + " | " + students1.getString("age") + " | " + students1.getString("gender"));
            }
    
            /**
             * 更新一条记录
             * @param conn 数据库连接
             * @param tableName 表名
             * @param conditionSet 更新字段的数组
             * @param conditionSetValue 更新字段的数组
             * @param conditions 条件的数组
             * @param conditionsValue 条件值的数组
             */
            String[] conditionSet = new String[]{
        "age"};
            Object[] conditionSetValue = new Object[]{
        "45"};
    
            String[] conditions = new String[]{
        "id"};
            Object[] conditionsValue = new Object[]{
        "5"};
            updateRecord(conn, "students", conditionSet, conditionSetValue, conditions, conditionsValue);
    
            //根据条件删除数据
            String[] conditions02 = new String[]{
        "id"};
            Object[] conditionsValue02 = new Object[]{
        "5"};
            deleteRecord(conn, "students", conditions02, conditionsValue02);
    
            //根据ID删除数据
            deleteRecordById(conn, "students", "4");
    
            closeConnection(conn);
        }
    
        /**
         * 创建SQLite数据库连接
         * @return 数据库连接
         */
        public static Connection getConnection() {
        
            Connection conn = null;
            try {
        
                Class.forName("org.sqlite.JDBC");
                conn = DriverManager.getConnection("jdbc:sqlite:" + DB_FILE);
            } catch (ClassNotFoundException | SQLException e) {
        
                e.printStackTrace();
            }
            return conn;
        }
    
        /**
         * 关闭数据库连接
         * @param conn 数据库连接
         */
        public static void closeConnection(Connection conn) {
        
            try {
        
                if (conn != null) {
        
                    conn.close();
                }
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
        }
    
        /**
         * 创建SQLite表
         * @param conn 数据库连接
         * @param tableName 表名
         * @param columns 列名和数据类型的数组
         */
        public static void createTable(Connection conn, String tableName, String[] columns) {
        
            StringBuilder sql = new StringBuilder();
            sql.append("CREATE TABLE IF NOT EXISTS ").append(tableName).append(" (");
            for (int i = 0; i < columns.length; i++) {
        
                sql.append(columns[i]).append(" ").append(getColumnType(columns[i]));
                if (i != columns.length - 1) {
        
                    sql.append(",");
                }
            }
            sql.append(")");
            try (Statement stmt = conn.createStatement()) {
        
                System.out.println("建表sql: " + sql.toString());
                stmt.executeUpdate(sql.toString());
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
        }
    
        /**
         * 获取列的数据类型
         * @param columnName 列名
         * @return 数据类型
         */
        private static String getColumnType(String columnName) {
        
            /*if (columnName.equalsIgnoreCase("id")) {
                return "INTEGER PRIMARY KEY";
            } else if (columnName.equalsIgnoreCase("name")) {
                return "TEXT";
            } else if (columnName.equalsIgnoreCase("age")) {
                return "INTEGER";
            } else if (columnName.equalsIgnoreCase("gender")) {
                return "TEXT";
            } else {
                return "TEXT";
            }*/
            if (columnName.equalsIgnoreCase("id")) {
        
                return "INTEGER PRIMARY KEY";
            } else {
        
                return "TEXT";
            }
        }
    
        /**
         * 插入一条记录
         * @param conn 数据库连接
         * @param tableName 表名
         * @param columns 字段的数组
         * @param values 值的数组
         */
        public static void insertRecord(Connection conn, String tableName, Object[] columns, Object[] values) {
        
            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO ").append(tableName).append(" (");
            for (int i = 0; i < columns.length; i++) {
        
                sql.append(columns[i].toString()).append(",");
            }
            sql.deleteCharAt(sql.length() - 1);
            sql.append(") VALUES (");
            for (int i = 0; i < columns.length; i++) {
        
                sql.append("?,");
            }
            sql.deleteCharAt(sql.length() - 1);
            sql.append(")");
            System.out.println("插入: " + sql.toString());
            try (PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
        
                for (int i = 0; i < values.length; i++) {
        
                    pstmt.setObject(i + 1, values[i]);
                }
                pstmt.executeUpdate();
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
        }
    
        /**
         * 查询所有记录
         * @param conn 数据库连接
         * @param tableName 表名
         * @return 查询结果
         */
        public static ResultSet queryAll(Connection conn, String tableName) {
        
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT * FROM ").append(tableName);
            System.out.println("查询所有记录: " + sql.toString());
            try {
        
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(sql.toString());
                return rs;
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
            return null;
        }
    
        /**
         * 查询指定字段的记录
         * @param conn 数据库连接
         * @param tableName 表名
         * @param columnName 列名
         * @return 查询结果
         */
        public static ResultSet queryByColumn(Connection conn, String tableName, String columnName) {
        
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT ").append(columnName).append(" FROM ").append(tableName);
            try{
        
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery(sql.toString());
                return rs;
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
            return null;
        }
    
        /**
         * 查询指定条件的记录
         * @param conn 数据库连接
         * @param tableName 表名
         * @param conditions 条件字段的数组
         * @param values 条件值的数组
         * @return 查询结果
         */
        public static ResultSet queryByCondition(Connection conn, String tableName, String[] conditions, Object[] values) {
        
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT * FROM ").append(tableName).append(" WHERE ");
            for (int i = 0; i < conditions.length; i++) {
        
                sql.append(conditions[i]).append("=?");
                if (i != conditions.length - 1) {
        
                    sql.append(" AND ");
                }
            }
            System.out.println("询指定条件的记录: " + sql.toString());
            try{
        
                PreparedStatement pstmt = conn.prepareStatement(sql.toString());
                for (int i = 0; i < values.length; i++) {
        
                    pstmt.setObject(i + 1, values[i]);
                }
                return pstmt.executeQuery();
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
            return null;
        }
    
        /**
         * 更新一条记录
         * @param conn 数据库连接
         * @param tableName 表名
         * @param conditionSet 更新字段的数组
         * @param conditionSetValue 更新字段的数组
         * @param conditions 条件的数组
         * @param conditionsValue 条件值的数组
         */
        public static void updateRecord(Connection conn, String tableName, String[] conditionSet, Object[] conditionSetValue, String[] conditions, Object[] conditionsValue) {
        
            StringBuilder sql = new StringBuilder();
            sql.append("UPDATE ").append(tableName).append(" SET ");
            for (int i = 0; i < conditionSet.length; i++) {
        
                sql.append(conditionSet[i]).append("=?");
                if (i != conditionSet.length - 1) {
        
                    sql.append(",");
                }
            }
            sql.append(" WHERE ");
            for (int i = 0; i < conditions.length; i++) {
        
                sql.append(conditions[i]).append("=?");
                if (i != conditions.length - 1) {
        
                    sql.append(" AND ");
                }
            }
            try{
        
                System.out.println("更新一条记录: " + sql.toString());
                PreparedStatement pstmt = conn.prepareStatement(sql.toString());
                for (int i = 0; i < conditionSetValue.length; i++) {
        
                    pstmt.setObject(i + 1, conditionSetValue[i]);
                }
                for (int i = 0; i < conditionsValue.length; i++) {
        
                    pstmt.setObject(i + conditionsValue.length + 1, conditionsValue[i]);
                }
                pstmt.executeUpdate();
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
        }
    
        /**
         * 删除一条记录
         * @param conn 数据库连接
         * @param tableName 表名
         * @param conditions 条件的数组
         * @param conditionsValue 条件值的数组
         */
        public static void deleteRecord(Connection conn, String tableName, String[] conditions, Object[] conditionsValue) {
        
            StringBuilder sql = new StringBuilder();
            sql.append("DELETE FROM ").append(tableName).append(" WHERE ");
            for (int i = 0; i < conditions.length; i++) {
        
                sql.append(conditions[i]).append("=?");
                if (i != conditions.length - 1) {
        
                    sql.append(" AND ");
                }
            }
            try{
        
                System.out.println("根据条件删除数据: " + sql.toString());
                PreparedStatement pstmt = conn.prepareStatement(sql.toString());
                for (int i = 0; i < conditionsValue.length; i++) {
        
                    pstmt.setObject(i + 1, conditionsValue[i]);
                }
                pstmt.executeUpdate();
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
        }
    
        /**
         * 根据ID删除一条记录
         * @param conn 数据库连接
         * @param tableName 表名
         * @param id ID值
         */
        public static void deleteRecordById(Connection conn, String tableName, String id) {
        
            StringBuilder sql = new StringBuilder();
            sql.append("DELETE FROM ").append(tableName).append(" WHERE id=?");
    
            try{
        
                System.out.println("根据条件删除数据: " + sql.toString());
                PreparedStatement pstmt = conn.prepareStatement(sql.toString());
                pstmt.setString(1, id);
                pstmt.executeUpdate();
            } catch (SQLException e) {
        
                e.printStackTrace();
            }
        }
    }