PreparedStatement介绍
可以通过调用Connection对象的preparedStatement(String sql)方法获取 PreparedStatement对象
PreparedStatement接口是Statement的子接口，SQL语句被预编译并存储在此对象中，可以使用此对象多次高效地执行该语句。
PreparedStatement对象所代表的SQL语句中的参数用问号（?）来表示，调用 PreparedStatement对象的setXxx()方法来设置这些参数。setXxx()方法有两个参数，第一个参数是要设置的SQL语句中的参数的索引（从 1 开始），第二个是设置的SQL语句中的参数的值
Java与SQL对应数据类型转换表
| Java类型 | SQL类型 |
|---|---|
| boolean | BIT |
| byte | TINYINT |
| short | SMALLINT |
| int | INTEGER |
| long | BIGINT |
| String | CHAR，VARCHAR，LONGVARCHAR |
| byte array | BINARY , VARBINARY |
| java.sql.Date | DATE |
| java.sql.Time | TIME |
| java.sql.Timestamp | TIMESTAMP |
使用PreparedStatement实现增、删、改操作
测试需要使用的数据库，关注公众号程序员汪汪回复jdbc即可获取数据库脚本。使用该脚本需先在mysql中新建一个数据库，然后执行该脚本导入数据。
连接数据库的四个基本信息，放置在配置文件jdbc.properties中：
user=root
password=root
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.jdbc.Driver
向customers表中添加一条记录：
// 向customers表中添加一条记录
@Test
public void testInsert() {
Connection conn = null;
PreparedStatement ps = null;
try {
// 1. 读取配置文件中的4个基本信息
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
// 2. 加载驱动
Class.forName(driverClass);
// 3. 获取连接
conn = DriverManager.getConnection(url, user, password);
// System.out.println(conn);
// 4. 预编译sql语句，返回PreparedStatement的实例
String sql = "insert into customers(name, email, birth)values(?,?,?)"; // ?表示占位符
ps = conn.prepareStatement(sql);
// 5. 填充占位符
ps.setString(1, "哪吒");
ps.setString(2, "nezha@gmail.com");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = sdf.parse("1000-01-01");
ps.setDate(3, new java.sql.Date(date.getTime()));
// 6. 执行操作
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 7. 资源关闭
try {
if (ps != null) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
需要操作数据库，那么每次操作都需要获取连接，以及操作完毕需要关闭连接，对于这些重复操作，可以封装成一个工具类JDBCUtils：
public class JDBCUtils {
/**
* 获取数据库的连接
*/
public static Connection getConnection() throws Exception {
// 1. 读取配置文件中的4个基本信息
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
// 2. 加载驱动
Class.forName(driverClass);
// 3. 获取连接
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
/**
* 关闭连接和Statement的操作
*/
public static void closeResouse(Connection conn, Statement ps) {
// 资源关闭
try {
if (ps != null) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
使用JDBCUtils连接数据库，并修改customers表的一条记录：
@Test
public void testUpdate() {
Connection conn = null;
PreparedStatement ps = null;
try {
// 1. 获取数据库的链接
conn = JDBCUtils.getConnection();
// 2. 预编译sql语句，返回PreparedStatement实例
String sql = "update customers set name = ? where id = ?";
ps = conn.prepareStatement(sql);
// 3. 填充占位符 两种写法都可以
// ps.setString(1, "葫芦娃");
// ps.setInt(2, 19);
ps.setObject(1, "亚索");
ps.setObject(2, 19);
// 4. 执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 5. 关闭资源
JDBCUtils.closeResouse(conn, ps);
}
}
在进行多次编写增删改的操作中，不难发现，进行不同的操作时，变的只是sql以及sql中的需要填充的参数（占位符?）的个数，那么，把这两个变量提出来，由调用者传参，就可以封装成通用的增删改操作。
通用的增删改操作：
// 通用的增删改操作
public void update(String sql, Object ...args) { // sql中占位符的个数和args的长度一致
Connection conn = null;
PreparedStatement ps = null;
try {
// 1. 获取连接
conn = JDBCUtils.getConnection();
// 2. 预编译sql语句，返回PreparedStatement实例
ps = conn.prepareStatement(sql);
// 3. 填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
// 4. 执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 5. 关闭资源
JDBCUtils.closeResouse(conn, ps);
}
}
添加数据以及修改数据都已经写好了，还剩删除数据的操作没有，可以试试使用通用操作，把前面添加的数据哪吒删除：
// 测试通用的增删改
@Test
public void testCommonUpdate() {
String sql = "delete from customers where id = ?";
update(sql, 19);
}
使用PreparedStatement实现查询操作
查询操作得到的结果，如果就是一条结果，现阶段我们通常都会封装成一个对象，如果是多个对象，那么就封装成一个个对象，再放入集合中。要封装成对象，那么就要有相应的实体类（JavaBean），查询操作我们使用order表，那么就创建一个Order类：
public class Order {
private int orderId;
private String orderName;
private Date orderDate;
public Order() {
}
public Order(int orderId, String orderName, Date orderDate) {
this.orderId = orderId;
this.orderName = orderName;
this.orderDate = orderDate;
}
public int getOrderId() {
return orderId;
}
public void setOrderId(int orderId) {
this.orderId = orderId;
}
public String getOrderName() {
return orderName;
}
public void setOrderName(String orderName) {
this.orderName = orderName;
}
public Date getOrderDate() {
return orderDate;
}
public void setOrderDate(Date orderDate) {
this.orderDate = orderDate;
}
@Override
public String toString() {
return "Order{" +
"orderId=" + orderId +
", orderName='" + orderName + '\'' +
", orderDate=" + orderDate +
'}';
}
}
查询操作：
@Test
public void testQuery1() {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 1. 获取数据库连接
conn = JDBCUtils.getConnection();
String sql = "select order_id, order_name, order_date from `order` where order_id = ?";
// 2. 预编译sql语句，得到PreparedStatement对象
ps = conn.prepareStatement(sql);
// 3. 填充占位符
ps.setObject(1, 1);
// 4. 执行，得到结果集：ResultSet
rs = ps.executeQuery();
if (rs.next()) { // next():判断结果集的下一条是否有数据，如果有数据返回true，并指针下移，如果返回false，指针不会下移。如果是多条数据，if改为while即可。
// 5. 获取当前这条数据的各个字段值
int id = rs.getInt(1);
String name = rs.getString(2);
Date date = rs.getDate(3);
// 6. 封装数据
Order order = new Order(id, name, date);
System.out.println(order);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 7. 关闭资源
JDBCUtils.closeResouse(conn, ps, rs);
}
}
由于查询操作会得到一个结果集（ResultSet），所以在使用过后，也是需要进行手动关闭的，所以我们一并将关闭资源的操作放入JDBCUtils工具类中，对closeResouse()方法进行重载：
// 在之前的JDBCUtils类中添加如下代码
public static void closeResouse(Connection conn, Statement ps, ResultSet rs) {
// 资源关闭
try {
if (ps != null) {
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
对于一张表的查询，sql语句不同的部分只有查询的列和查询的条件。那么想要写一个针对order表的通用的查询操作，我们就必须将sql语句和查询条件作为形参传入。sql语句作为参数传入方法，我们就不知道它究竟要查几列，所以就不能像上面那样通过索引取出每列的数据，可是我们就是要知道查询了多少列，怎么办？这时就用到了一个修饰结果集的元数据（ResultSetMeteData），通过ResultSetMeteData我们可以知道结果集中有几列数据以及每列的列名，这时我们就可以取出每列的数据。数据有了，但是怎么封装？要封装就要知道这些数据对应的是Order类的哪几个属性，这时列名就派上用场了，通常一张表对应一个实体类，表的列名就对应实体类的属性，但是在封装数据时，我们不能直观的看到哪个数据对应哪个属性，怎么办？这时，反射机制就派上用场了。
order表的通用查询操作：
// 针对于Order表的通用查询操作
public Order orderForQuery(String sql, Object... args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
// 执行，获取结果集
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
// 获取结果集的列数
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
Order order = new Order();
for (int i = 0; i < columnCount; i++) {
// 通过resultSet获取每一列的值
Object columnValue = rs.getObject(i + 1);
// 通过ResultSetMeteData获取每个列的列名(表的字段名)
// 获取列的列名：getColumnName() --不推荐使用
// 获取列的别名：getColumnLabel() --推荐使用 使用该方法，传入的sql，查询的列需要起别名，并且别名要和实体类的属性名一致
// String columnName = rsmd.getColumnName(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
// 通过反射，将对象指定名columnName的属性赋值为指定的值columnValue
Field field = Order.class.getDeclaredField(columnLabel);
// 开启私有属性访问
field.setAccessible(true);
// 将order对象名为columnName的属性赋值为指定的值columnValue
field.set(order, columnValue);
}
return order;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResouse(conn, ps, rs);
}
return null;
}
注意：表的列名和实体类的属性名不一致时，比如表的列名是order_id，实体类对应的属性名是orderId，如果在查询时，sql语句中查询的列没有起别名，比如sql是select order_id from order...，此时得到的结果集中，如果使用getColumnName()或getColumnLabel()方法获取列名，得到的值都是order_id，那么此时封装数据，就不能使用反射，也没法封装。如果sql是select order_id orderId from order...，此时得到的结果集中，getColumnName()获得的值是order_id，getColumnLabel()获得的值是orderId，所以如果列名和属性名不一致时，在查询时需要给列起别名，并且别名要和属性名一致，这样才可以正确的封装数据，因此推荐使用getColumnLabel()方法获取列名。
任意表的通用查询操作
上面我们有了对order表的查询操作，现在来实现对任意表的通用查询操作。
对一张表的通用查询操作，我们把sql和查询条件当作形参，由调用者传入，由于传入的sql中就包含了需要操作的表，所以不需要考虑调用者操作的是哪个表，只需要考虑数据封装时使用哪个JavaBean，这个好办，让调用者告诉程序就行（作为参数传入）。查询操作封装成方法，那么就要将查询到的数据返回，由于不知道查询的是什么，不知道返回的是哪个实体类数据，所以就需要使用泛型。
不同表的通用查询操作，只查询表中的一条记录：
// 不同表的通用查询操作，返回表中的一条记录
public <T> T currencyQuery(Class<T> clazz, String sql, Object... args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
// 获取连接
conn = JDBCUtils.getConnection();
// 预编译
ps = conn.prepareStatement(sql);
// 填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
// 执行，返回结果集
rs = ps.executeQuery();
// 获取结果集元数据对象
ResultSetMetaData rsmd = rs.getMetaData();
// 获取结果集的列数
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
// 创建实体类对象
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
// 通过ResultSet获取每一列的值
Object columnValue = rs.getObject(i + 1);
// 通过ResultSetMeteData获取每个列的列名(表的字段名)
String columnLabel = rsmd.getColumnLabel(i + 1);
// 通过反射，将对象指定名columnName的属性赋值为指定的值columnValue
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResouse(conn, ps, rs);
}
return null;
}
不同表的通用查询操作，返回表中的多条记录：
// 不同表的通用查询操作，返回表中的多条记录
public <T> List<T> getForList(Class<T> clazz, String sql, Object... args) {
List<T> list = null;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
list = new ArrayList<>();
while (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object columnValue = rs.getObject(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResouse(conn, ps, rs);
}
return null;
}
测试上述的两个通用方法：
@Test
public void testCurrencyQuery() {
String sql = "select id, name, email from customers where id = ?";
Customer customer = currencyQuery(Customer.class, sql, 1);
System.out.println(customer);
String sql2 = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id = ?";
Order order = currencyQuery(Order.class, sql2, 1);
System.out.println(order);
}
@Test
public void testGetForList() {
String sql = "select id, name, email from customers where id < ?";
List<Customer> list = getForList(Customer.class, sql, 4);
System.out.println(list);
String sql2 = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id < ?";
List<Order> orderList = getForList(Order.class, sql2, 3);
orderList.forEach(System.out::println);
}
上面的测试代码，customers表的实体类，请自行创建！
批量插入
update、delete本身就具有批量操作的效果（没有where限定条件会影响所有数据）
此时的批量操作主要指的是批量插入。使用PreparedStatement如何实现更高效的批量插入？
题目：向goods表中插入20000条数据
方式一：
Connection conn = JDBCUtils.getConnection();
Statement st = conn.createStatement();
for (int i = 1; i <= 20000; i++) {
String sql = "insert into goods(name) values('name_ + " + i + "')";
st.execute(sql);
}
这个方式，每次循环就会创建一次sql，会浪费大量的内存，极其不推荐使用！！！
方式二：
// 批量插入的方式二：使用PreparedStatement
@Test
public void BatchInsertTest() {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into goods(name) values(?)";
ps = conn.prepareStatement(sql);
for (int i = 1; i <= 20000; i++) {
ps.setObject(1, "name_" + i);
ps.execute();
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResouse(conn, ps);
}
}
这个方式，算是对方式一的一个迭代，省去了创建大量的sql，可以节省内存空间。
方式三：
/* 批量插入的方式三：
* 1. addBatch()、executeBatch()、clearBatch()
* 2. mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持
* ?rewriteBatchedStatements=true 写在配置文件的url后面
* 3. 使用更新的驱动，比如使用版本：mysql-connector-java-5.1.37-bin.jar
*/
@Test
public void BatchInsertTest2() {
Connection conn = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
conn = JDBCUtils.getConnection();
String sql = "insert into goods(name) values(?)";
ps = conn.prepareStatement(sql);
for (int i = 1; i <= 2000000; i++) {
ps.setObject(1, "name_" + i);
// 1. “攒”sql
ps.addBatch();
if (i % 500 == 0) {
// 2. 执行batch
ps.executeBatch();
// 3. 清空batch
ps.clearBatch();
}
}
long end = System.currentTimeMillis();
System.out.println("花费的时间为：" + (end - start));
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResouse(conn, ps);
}
}
使用这个方法，需要修改一下连接数据库的URL，修改后的URL是：
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
方式四：
// 批量插入的方式四：设置链接不允许自动提交数据
@Test
public void BatchInsertTest3() {
Connection conn = null;
PreparedStatement ps = null;
try {
long start = System.currentTimeMillis();
conn = JDBCUtils.getConnection();
// 设置不允许自动提交数据
conn.setAutoCommit(false);
String sql = "insert into goods(name) values(?)";
ps = conn.prepareStatement(sql);
for (int i = 1; i <= 2000000; i++) {
ps.setObject(1, "name_" + i);
// 1. “攒”sql
ps.addBatch();
if (i % 500 == 0) {
// 2. 执行batch
ps.executeBatch();
// 3. 清空batch
ps.clearBatch();
}
}
// 提交数据
conn.commit();
long end = System.currentTimeMillis();
System.out.println("花费的时间为：" + (end - start));
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResouse(conn, ps);
}
}
写得可能不是很到位，欢迎讨论，各路大佬轻喷，谢谢！！
