直接调用无需拼装sql
org.xerial
sqlite-jdbc
3.43.0.0
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();
}
}
}