JDBC操作SQLite的工具类

直接调用无需拼装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();}}
}