最接地气的java数据库操作工具类

java工具类的开发内容

我们需在工具类中编写以下代码功能:
   1.获取数据库连接
   2.运行sql语句
   3.关闭资源
工具类包含以上功能，即可实现操作数据的效果

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JdbcUtils {
    // 数据库URL、用户名和密码
    private static final String URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String USER = "your_username";
    private static final String PASSWORD = "your_password";

    // 静态代码块加载数据库驱动
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     * @return Connection对象
     */
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 关闭资源
     * @param rs ResultSet对象
     * @param ps PreparedStatement对象
     * @param conn Connection对象
     */
    public static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 执行查询操作
     * @param sql SQL查询语句
     * @param params 参数数组
     * @return ResultSet对象
     */
    public static ResultSet executeQuery(String sql, Object[] params) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            rs = ps.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    /**
     * 执行更新操作（INSERT, UPDATE, DELETE）
     * @param sql SQL更新语句
     * @param params 参数数组
     * @return 影响的行数
     */
    public static int executeUpdate(String sql, Object[] params) {
        Connection conn = null;
        PreparedStatement ps = null;
        int affectedRows = 0;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    ps.setObject(i + 1, params[i]);
                }
            }
            affectedRows = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(null, ps, conn);
        }
        return affectedRows;
    }
}

工具类使用示例分享

查询操作

 
String sql = "SELECT * FROM your_table WHERE column1 = ?";
Object[] params = {"value1"};
ResultSet rs = JdbcUtils.executeQuery(sql, params);
try {
    while (rs.next()) {
        // 处理结果集
        System.out.println(rs.getString("column2"));
    }
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    JdbcUtils.close(rs, null, null);
}

更新操作

String sql = "UPDATE your_table SET column2 = ? WHERE column1 = ?";
Object[] params = {"new_value", "value1"};
int affectedRows = JdbcUtils.executeUpdate(sql, params);
System.out.println("受影响的行数: " + affectedRows);