JDBC的 PreparedStatement 的用法和解释

前言

• PreparedStatement是预编译的,对于批量处理可以大大提高效率. 也叫JDBC存储过程

---

1、封装数据库连接和关闭操作

package org.springblade.modules.data.util;import lombok.extern.slf4j.Slf4j;import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;/*** 数据库连接工具类*/
@Slf4j
public class JdbcOracleTemplatedct {private static String url;private static String username;private static String password;private static String driver;static {// 加载配置文件InputStream is = JdbcOracleTemplatedct.class.getClassLoader().getResourceAsStream("config.properties");Properties pro = new Properties();try {pro.load(is);url = pro.getProperty("jdbc3.url");username = pro.getProperty("jdbc3.username");password = pro.getProperty("jdbc3.password");driver = pro.getProperty("jdbc3.driverClassName");} catch (IOException e) {e.printStackTrace();log.info(e.getMessage(), e);log.info("加载配置文件失败!");}// 注册驱动try {Class.forName(driver);} catch (ClassNotFoundException e) {e.printStackTrace();log.info(e.getMessage(), e);}}public static Connection getConnection() {Connection conn = null;try {conn = DriverManager.getConnection(url, username, password);// 把jdbc 链接 设置成 非自动提交conn.setAutoCommit(false);} catch (SQLException e) {e.printStackTrace();log.info(e.getMessage(), e);log.info("获得链接失败!");}return conn;}public static void closeAll(Statement stmt, Connection conn) {if (stmt != null) {try {stmt.close();} catch (SQLException e) {e.printStackTrace();}}if (conn != null) {try {conn.close();// 关闭} catch (SQLException e) {e.printStackTrace();}}}public static void closeResAll(ResultSet rs, Statement stmt, Connection conn) {if (rs != null) {try {rs.close();// 关闭} catch (SQLException e) {e.printStackTrace();}}if (stmt != null) {try {stmt.close();} catch (SQLException e) {e.printStackTrace();}}if (conn != null) {try {conn.close();// 关闭} catch (SQLException e) {e.printStackTrace();}}}public static boolean createTable(String sqltemplet) {Connection conn = getConnection();long starttime = System.currentTimeMillis();Statement statement = null;boolean isok = false;try {try {statement = conn.createStatement();} catch (SQLException e) {e.printStackTrace();log.info(e.getMessage(), e);log.info("执行批处理时    创建预状态通道失败!");}statement.executeUpdate(sqltemplet);isok = true;log.info(" 执行成功一次,总使用时间："+ (System.currentTimeMillis() - starttime) + "ms");} catch (SQLException e) {e.printStackTrace();log.info(e.getMessage(), e);log.info("在 预 状 态 通 道 下 执 行 批 出 操 作 失 败!");} finally {closeAll(statement, conn);}return isok;}/*** 基于预状态通道执行批处理操作** @param sqltemplet* @param params* @return*/public static boolean batchData(String sqltemplet, List<List<Object>> params) {Connection conn = getConnection();long starttime = System.currentTimeMillis();PreparedStatement statement = null;boolean isok = false;try {try {statement = conn.prepareStatement(sqltemplet);} catch (SQLException e) {e.printStackTrace();log.info(e.getMessage(), e);log.info("执行批处理时    创建预状态通道失败!");}if (params != null && !params.isEmpty()) {for (List<Object> plist : params) {int size = plist.size();for (int i = 0; i < size; i++) {statement.setObject(i + 1, plist.get(i));}statement.addBatch();}statement.executeBatch();conn.commit();statement.clearParameters();isok = true;log.info(" 执行成功一次,总使用时间："+ (System.currentTimeMillis() - starttime) + "ms");}} catch (SQLException e) {e.printStackTrace();log.info(e.getMessage(), e);log.info("在 预 状 态 通 道 下 执 行 批 出 操 作 失 败!");try {conn.rollback();} catch (SQLException e1) {e1.printStackTrace();}} finally {closeAll(statement, conn);}return isok;}/*** 基于预状态通道执行批处理操作** @param sqltemplet* @param params* @return*/public synchronized  static boolean batchAllData(String sqltemplet,List<List<Object>> params) {try {Thread.sleep(500);} catch (InterruptedException e2) {e2.printStackTrace();}Connection conn = getConnection();long starttime = System.currentTimeMillis();PreparedStatement statement = null;boolean isok = false;try {try {statement = conn.prepareStatement(sqltemplet);} catch (SQLException e) {e.printStackTrace();log.info(e.getMessage(), e);log.info("执行批处理时    创建预状态通道失败!");}if (params != null && !params.isEmpty()) {int size2 = params.size();for (int j = 0; j < size2; j++) {List<Object> plist = params.get(j);int size = plist.size();for (int i = 0; i < size; i++) {statement.setObject(i + 1, plist.get(i));}statement.addBatch();if (j % 1000 == 0) {statement.executeBatch();conn.commit();statement.clearParameters();}}statement.executeBatch();conn.commit();statement.clearParameters();isok = true;log.info(" 执行成功一次,总使用时间："+ (System.currentTimeMillis() - starttime) + "ms");}} catch (SQLException e) {e.printStackTrace();log.info(e.getMessage(), e);log.info("在 预 状 态 通 道 下 执 行 批 出 操 作 失 败!");try {conn.rollback();} catch (SQLException e1) {e1.printStackTrace();}} finally {closeAll(statement, conn);}return isok;}public static List<Map<String, Object>> selectAll(String querysql) {List<Map<String, Object>> list = new ArrayList<>();long starttime = System.currentTimeMillis();Connection conn = getConnection();Statement statement = null;ResultSet rs = null;try {statement = conn.createStatement();rs = statement.executeQuery(querysql);ResultSetMetaData data = rs.getMetaData();int columnCount = data.getColumnCount();// System.out.println(columnCount);while (rs.next()) {int i = 1;Map<String, Object> map = new HashMap<String, Object>();while (i <= columnCount) {String name = data.getColumnName(i);// System.out.println(name + " : " + rs.getObject(i));if (rs.getObject(i) == null || "".equals(rs.getObject(i))) {map.put(name, "");} else {map.put(name, rs.getObject(i));}i++;}list.add(map);}log.info(" 执行成功一次,总使用时间："+ (System.currentTimeMillis() - starttime) + "ms");} catch (SQLException e) {e.printStackTrace();log.info(e.getMessage(), e);} finally {closeResAll(rs, statement, conn);}return list;}public static void batchUpdate(String sql) {Connection conn = null;Statement ps = null;try {conn = getConnection();ps = conn.createStatement();int executeUpdate = ps.executeUpdate(sql);conn.commit();} catch (SQLException e) {e.printStackTrace();log.error(e.getMessage(), e);try {conn.rollback();} catch (SQLException e1) {log.error(e1.getMessage(), e1);}} finally {try {ps.close();} catch (SQLException e) {log.error(e.getMessage(), e);}try {conn.close();} catch (SQLException e) {log.error(e.getMessage(), e);}}}
}

数据库配置文件 config.properties

jdbc3.driverClassName = oracle.jdbc.OracleDriver
jdbc3.url = jdbc:oracle:thin:@172.0.0.1:1521:rdt1
jdbc3.username = root
jdbc3.password = root

2、批量添加操作

public static void main(String[] args) {Connection conn = JdbcOracleTemplatedct.getConnection();PreparedStatement statement = null;String insertSql = "INSERT INTO your_table (column1, column2) VALUES (?, ?)";try {statement = conn.prepareStatement(insertSql);// 假设我们要插入10条数据for (int i = 1; i <= 10; i++) {statement.setString(1, "Value" + i + "Column1");statement.setInt(2, i);statement.addBatch(); // 将SQL语句添加到批处理中}// 执行批处理int[] count = statement.executeBatch();System.out.println("插入了" + count.length + "条数据");} catch (SQLException e) {e.printStackTrace();log.error("添加执行失败", e);} finally {if (statement != null) {try {statement.close();} catch (SQLException e) {e.printStackTrace();}}}}

3、查询操作

• 如果执行的是查询操作，使用executeQuery方法，并处理结果集。

ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {// 处理结果集
}

4、修改和删除操作

int rowsAffected = statement.executeUpdate();

---

总结

• 通过使用PreparedStatement和executeBatch，你可以提高Java应用程序与数据库交互的性能。特别是当你需要执行大量的增删改操作时，批处理可以显著减少与数据库的交互次数，从而提高效率。同时，注意正确处理资源（如Connection、PreparedStatement、ResultSet）的打开和关闭，以及异常处理，以确保代码的健壮性。

如果此篇文章有帮助到您, 希望打大佬们能关注、点赞、收藏、评论支持一波，非常感谢大家！
如果有不对的地方请指正!!!