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 + "条数据");
            conn.commit();
            //清楚目前的参数值。
            statement.clearParameters();
        } 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）的打开和关闭，以及异常处理，以确保代码的健壮性。

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