面向接口编程
java为连接不同的数据库提供了统一的接口,这样每个数据库厂商需要提供针对这个接口的具体实现,称之为数据库驱动。而由于接口是一种规范,因此就算对于不同的数据库,对他们的操作也是相同的,例如使用Driver driver = new 具体数据库驱动();就能得到特定数据库的驱动对象,然后通过driver.connect(参数1, 参数2)即可获取数据库连接。不管是什么数据库,对他们操作都是这些代码,大大提升了开发效率。
程序编写步骤
获取连接
在这之前,要获取mysql的驱动,你要把相应的jar包放在项目下:
方式一
import org.junit.Test;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.SQLException;
import java.util.Properties;
public class ConnectionTest {
@Test
public void test1() throws SQLException {
Driver driver = new com.mysql.jdbc.Driver();
// jdbc:mysql: 协议,相当于 http:
String url = "jdbc:mysql://localhost:3306/test";
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password", "root");
Connection connection = driver.connect(url, info);
System.out.println(connection);
}
}
com.mysql.jdbc.JDBC4Connection@21213b92
举个例子来理解上述操作,比如你要去旅游,这个Driver就相当于车,url就相当于目的地,properties就相当于你的车钥匙,只有这些都准备好了才能出发,这便是connection。
方式二 (基于方式一)
上述方式出现了第三方类:
- Driver driver = new com.mysql.jdbc.Driver();
为了有更好的可移植性,我们可以用反射的方式,这样就可以动态的创建不同数据库的对象:
// 方式2 对方式1的迭代
@Test
public void test2() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
String url = "jdbc:mysql://localhost:3306/test";
Properties info = new Properties();
info.setProperty("user", "root");
info.setProperty("password", "root");
Connection connection = driver.connect(url, info);
System.out.println(connection);
}
方式三 使用DriverManager
//方式3 使用DriverManager
@Test
public void test3() throws Exception {
Class clazz = Class.forName("com.mysql.jdbc.Driver");
Driver driver = (Driver) clazz.newInstance();
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "root";
DriverManager.registerDriver(driver);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
方式四 (基于方式三)
//方式4 基于方式3
@Test
public void test4() throws Exception {
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "root";
Class.forName("com.mysql.jdbc.Driver");
// Driver driver = (Driver) clazz.newInstance();
// DriverManager.registerDriver(driver);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
为什么注释掉那两行也能成功?看下图:
因为当反射把Driver类加载到内存中时,就会自动执行类中的静态代码块,自动的帮我们new了Driver对象。
方式五 (不以硬编码的方式写参数)
将连接数据库需要的配置信息(url,user啥的)写到文件中,通过读文件的方式连接。
@Test
public void test5() throws Exception {
// 默认识别路径是src下
InputStream is = ConnectionTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
}
操作和访问数据库
statement
这个方式存在如下弊端:
- 需要拼接字符串
- sql注入问题
https://www.bilibili.com/video/BV1eJ411c7rf?p=12
关于拼串:
关于sql注入:
PreparedStatement
增
package preparestatement.java;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;
import java.util.Properties;
public class PrepareStatementTest {
@Test
public void test1() throws Exception {
InputStream is = PrepareStatementTest.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(is);
// 读取参数
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
Class.forName(driverClass);
// 获取连接
Connection connection = DriverManager.getConnection(url, user, password);
System.out.println(connection);
// 预编译sql语句,返回preparedStatement的实例
String sql = "insert into customers(name,email,birth)value(?,?,?)";
PreparedStatement ps = connection.prepareStatement(sql);
// 填充占位符 注意这里索引是从1开始的
ps.setString(1, "秦岚");
ps.setString(2, "qinlan@gmail.com");
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = simpleDateFormat.parse("1979-7-17");
ps.setDate(3, new Date(date.getTime()));
// 执行sql操作
ps.execute();
// 关闭资源
ps.close();
connection.close();
}
}
我们可以将创建连接/关闭连接这种代码封装到一个工具类中,以便其他的类调用:
package utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
public class JDBCUtils {
public static Connection getConnection() throws Exception{
// InputStream stream = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
InputStream stream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(stream);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
Class.forName(driverClass);
Connection connection = DriverManager.getConnection(url, password, user);
return connection;
}
public static void closeResource(Connection conn, PreparedStatement ps) {
try {
if (conn!=null)
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
改
@Test
public void test2() {
Connection connection = null;
PreparedStatement ps = null;
try {
// 获取数据库连接
connection = JDBCUtils.getConnection();
// 预编译sql语句,返回prepareStatement实例
String sql = "update customers set name = ? where id = ?";
ps = connection.prepareStatement(sql);
// 填充占位符
ps.setString(1, "巴赫");
ps.setInt(2, 18);
// 执行
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
// 关闭资源
JDBCUtils.closeResource(connection, ps);
}
}
把更新功能抽象出来
我们可以把增、删、改封装到一个方法中:
public void update(String sql, Object ... args) {
Connection connection = null;
PreparedStatement ps = null;
try {
connection = JDBCUtils.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
System.out.println(args[i]);
ps.setObject(i+1, args[i]);
}
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps);
}
}
测试:
@Test
public void test3() {
// String sql = "delete from customers where id = ?";
// update(sql, "12");
String sql2 = "update `order` set order_name = ? where order_id = ?";
update(sql2, "QQ", "4");
}
这里要注意的是,order这个表的名字 “order” 是关键字,因此直接写order会报错,如果想让程序知道你想更新order是个表而不是关键字,就要向上述代码一样加个反引号
查
和增删改不同,查询是有结果返回的,因此获取连接、sql语句等步骤都是和增删改相同的,但要处理结果。
我们创建一个类来保存查到的数据(这里只写属性,方法略去了):
public class Customer {
private int id;
private String name;
private String email;
private Date birth;
}
@Test
public void test1() {
Connection connection = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
String sql = "select id,name,email,birth from customers where id = ?";
ps = connection.prepareStatement(sql);
ps.setObject(1, 20);
resultSet = ps.executeQuery();
System.out.println(resultSet);
if (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString(2);
String email = resultSet.getString(3);
Date birth = resultSet.getDate(4);
Customer customer = new Customer(id, name, email, birth);
System.out.println(customer);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps, resultSet);
}
}
next的作用是看看后面还有没有元素,如果有就指针下移,但不返回结果。
注意,resultSet也需要关闭资源,因此在JDBCUtils中添加一个重载的方法:
public static void closeResource(Connection conn, PreparedStatement ps, ResultSet result) {
try {
if (conn!=null)
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
result.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
把查询操作也弄成通用的
上面的代码只能查固定的属性,能不能写一个通用的方法,让我们想查几个属性就查几个属性呢?
// 查询的通用写法
public Customer query(String sql, Object... args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = JDBCUtils.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
// 获取元数据
ResultSetMetaData metaData = rs.getMetaData();
// 根据元数据获取查询结果的列数
int columnCount = metaData.getColumnCount();
if (rs.next()) {
Customer c = new Customer(); // 写在这里最好,如果写在if外面,那么当rs为空的时候仍然会创建对象
for (int i = 0; i < columnCount; i++) {
// 获取每列的值
Object columnValue = rs.getObject(i + 1);
// 获取每列的列名
String columnName = metaData.getColumnName(i + 1);
// 给Customer对象的columnName属性赋值为columnValue,利用反射
Field field = Customer.class.getDeclaredField(columnName);
field.setAccessible(true); // 属性是私有的,不要忘了设置权限
field.set(c, columnValue);
}
return c;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps, rs);
}
return null;
}
说明:
- 由于我们想查可变数量的属性,所以sql中的属性个数是不定的
- 我们还是先获取连接,把占位符设置好,然后执行查询
- 如何知道查询结果中有几列呢?因此要获取结果集的列数(通过元数据(就是描述结果信息的数据),已在代码中标明)
- 有了列数,就能通过循环来得到当前结果每一列的值,由于我们想把查询结果保存到对象中,因此还要知道当前列的属性名,同样通过元数据获得
- 现在知道了列名和值,要给对象赋值了,如何赋值?通过反射!(已标注)
调用:
@Test
public void test2() {
// String sql = "select id, name, birth, email from customers where id = ?";
String sql = "select id, name, email from customers where id = ?";
Customer customer = query(sql, "4");
System.out.println(customer);
}
结果:
Customer{id=4, name='汤唯', email='tangw@sina.com', birth=null}
当Java类中属性名和数据库中属性名不匹配的情况
上面的代码是Java类中的属性名和数据库中的字段名完全相同的情况,但有时这二者并不会完全相同,比如下面这个表:
我们可能在对应的类里起的名字是:
public class Order {
private int orderId;
private String orderName;
private Date orderDate;
}
如果还像前面那样查询,就会报错:
// Java类的属性名和数据库中属性名不相同,例如Order类
public Order orderQuery(String sql, Object... args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = JDBCUtils.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[0]);
}
rs = ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
if (rs.next()) {
Order order = new Order();
for (int i = 0; i < columnCount; i++) {
Object columnValue = rs.getObject(i+1);
String columnName = metaData.getColumnName(i + 1);
Field field = Order.class.getDeclaredField(columnName);
field.setAccessible(true);
field.set(order, columnValue);
}
return order;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps, rs);
}
return null;
}
@Test
public void orderTest() {
String sql = "select order_id, order_name, order_date from `order` where order_id = ?";
Order order = orderQuery(sql, 2);
System.out.println(order);
}
结果:
这是因为Java类中的属性和数据库的字段名不匹配。
解决方案是起别名,在sql查询的时候可以起别名,让这个别名和Java类中的属性相同即可:
@Test
public void orderTest() {
String sql = "select order_id orderId, order_name orderName, order_date orderDate from `order` where order_id = ?";
Order order = orderQuery(sql, 2);
System.out.println(order);
}
但是此时运行还是报错,因为metaData.getColumnName得到的是列名而不是别名,要使用getColumnLabel:
这个getColumnLabel是你起了别名他就返回别名,没起名字就返回列名。
小结
不同表的查询操作
上述代码可以针对一个表的若干属性进行查询,现在想对不同表的多个属性来进行查询。
// 多表不同属性的查询
public <T> T queryMulti(Class<T> clazz, String sql, Object ... args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = JDBCUtils.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
rs = ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
if (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object columnValue = rs.getObject(i + 1);
String columnLable = metaData.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnLable);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps, rs);
}
return null;
}
@Test
public void multiTable() {
String sql = "select name, birth, email from customers where id = ?";
Customer customer = queryMulti(Customer.class, sql, 3);
System.out.println(customer);
String sql2 = "select order_id orderId, order_name orderName from `order` where order_id = ?";
Order order = queryMulti(Order.class, sql2, 4);
System.out.println(order);
}
结果:
Customer{id=0, name='林志玲', email='linzl@gmail.com', birth=1984-06-12}
Order{orderId=4, orderName='QQ', orderDate=null}
说明:
- 这里的改动主要就是使用了泛型,在调用查询方法的时候指定要查询的表名
- 注意泛型方法的声明:public <T> T queryMulti(Class<T> clazz, String sql, Object … args),不能直接写成public T queryMulti(Class<T> clazz, String sql, Object … args),因为这么写编译器会认为这个T是一个类而不是泛型。
查询多条记录
现在我们想让查询结果中包含多条记录。
只需用集合即可:
// 查询结果包含多条记录
public <T> List<T> queryMultiItems(Class<T> clazz, String sql, Object ... args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = JDBCUtils.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
rs = ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
ArrayList<T> list = new ArrayList<>();
while (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object columnValue = rs.getObject(i + 1);
String columnLable = metaData.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnLable);
field.setAccessible(true);
field.set(t, columnValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps, rs);
}
return null;
}
@Test
public void multiItems() {
String sql = "select email, id, name from customers where id < ?";
List<Customer> customerList = queryMultiItems(Customer.class, sql, 10);
customerList.forEach(s -> System.out.println(s));
}
Customer{id=1, name='汪峰', email='wf@126.com', birth=null}
Customer{id=2, name='王菲', email='wangf@163.com', birth=null}
Customer{id=3, name='林志玲', email='linzl@gmail.com', birth=null}
Customer{id=4, name='汤唯', email='tangw@sina.com', birth=null}
Customer{id=5, name='成龙', email='Jackey@gmai.com', birth=null}
Customer{id=6, name='迪丽热巴', email='reba@163.com', birth=null}
Customer{id=7, name='刘亦菲', email='liuyifei@qq.com', birth=null}
Customer{id=8, name='陈道明', email='bdf@126.com', birth=null}
prepareStatement和statement
- prepareStatement不需要拼接sql语句
- prepareStatement可以解决sql注入问题
- prepareStatement可以操作图片、音频等数据
- prepareStatement可以更高效的实现批量操作
https://www.bilibili.com/video/BV1eJ411c7rf?p=23
小结
几道练习题
1
public class AddItem {
public static void main(String[] args) {
System.out.println("***");
Scanner scanner = new Scanner(System.in);
System.out.print("input user name: ");
String name = scanner.next();
String email = scanner.next();
String birth = scanner.next();
String sql = "insert into customers(name, email, birth) values(?,?,?)";
int i = new AddItem().update(sql, name, email, birth);
if (i > 0) {
System.out.println("update success!!");
}else {
System.out.println("update false!");
}
}
public int update(String sql, Object ... args) {
Connection connection = null;
PreparedStatement ps = null;
try {
connection = JDBCUtils.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
System.out.println(args[i]);
ps.setObject(i+1, args[i]);
}
// ps.execute();
return ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps);
}
return 0;
}
}
注意,之前都是用execute直接执行的,但他是有返回值的,如果是查询操作就返回true,如果是更新操作就返回false。而executeUpdate()方法是针对更新操作说的,返回0代表没有item受到影响,返回大于0的数代表受影响的行数
2
第一问和上一题很相似了,这里做一下第二问和第三问(只实现了按准考证查找,身份证类似):
package practice;
import bean.java.Student;
import utils.JDBCUtils;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Scanner;
public class task2 {
public static void main(String[] args) {
System.out.println("请选择查询方式:");
System.out.println("a 准考证号");
System.out.println("b 身份证号");
Scanner scanner = new Scanner(System.in);
String s = scanner.next();
if ("a".equalsIgnoreCase(s)) {
System.out.println("请输入准考证号");
String examCard = scanner.next();
String sql = "select FlowID flowID, Type type, IDCard idCard, ExamCard examCard, StudentName name, " +
"Location location, Grade grade from examstudent where examCard=?";
Student student = new task2().queryMulti(Student.class, sql, examCard);
System.out.println(student);
}else if ("b".equalsIgnoreCase(s)) {
}
}
public <T> T queryMulti(Class<T> clazz, String sql, Object ... args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
connection = JDBCUtils.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1, args[i]);
}
rs = ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
if (rs.next()) {
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object columnValue = rs.getObject(i + 1);
String columnLable = metaData.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnLable);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(connection, ps, rs);
}
return null;
}
}
第三问:
public static void main(String[] args) {
Scanner scanner = new Scanner(System.in);
System.out.println("input the examId: ");
String s = scanner.next();
String sql = "select FlowID flowID, Type type, IDCard idCard, ExamCard examCard, StudentName name, " +
"Location location, Grade grade from examstudent where examCard=?";
Student student = new task2().queryMulti(Student.class, sql, s);
if (student!=null) {
String sql1 = "delete from examstudent where examCard = ?";
int i = new task2().update(sql1, s);
if (i > 0) {
System.out.println("success!");
}
}
}
操作Blob类型的数据
插入Blob数据
public class InsertBlob {
@Test
public void test() throws Exception {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into customers(name, email, birth, photo) values(?,?,?,?)";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setObject(1, "路飞");
ps.setObject(2, "lf@gmail.com");
ps.setObject(3, "1995-4-13");
ps.setBlob(4, new FileInputStream(new File("1.jpg")));
ps.execute();
}
}
读取Blob数据
// 从数据库中读取Blob数据
@Test
public void test2() throws Exception {
InputStream is = null;
FileOutputStream os = null;
Connection connection = JDBCUtils.getConnection();
String sql = "select id, name, birth, email, photo from customers where id = ?";
PreparedStatement ps = connection.prepareStatement(sql);
ps.setObject(1, 22);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
Blob photo = rs.getBlob("photo");
is = photo.getBinaryStream();
os = new FileOutputStream("2.jpg");
byte[] bytes = new byte[1024];
int len;
while ((len = is.read(bytes)) != -1) {
os.write(bytes, 0, len);
}
}
JDBCUtils.closeResource(connection, ps, rs);
is.close();
os.close();
}
prepareStatement 实现高效的批量操作
prepareStatement正常插入
这里没有用try,只是表达个意思:
@Test
public void test() throws Exception {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into insert_test(name) values(?)";
PreparedStatement ps = connection.prepareStatement(sql);
long l1 = System.currentTimeMillis();
for (int i = 0; i < 20000; i++) {
ps.setObject(1, "name" + i);
ps.execute();
}
long l2 = System.currentTimeMillis();
System.out.println(l2-l1);
JDBCUtils.closeResource(connection, ps);
}
结果:
26479
这种方式每填充完一次占位符就执行一次,这样和磁盘交互次数较多导致效率不高,因此可以用如下方式:
使用Batch
// 使用batch插入
@Test
public void test2() throws Exception {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into insert_test(name) values(?)";
PreparedStatement ps = connection.prepareStatement(sql);
long l1 = System.currentTimeMillis();
for (int i = 0; i < 20000; i++) {
ps.setObject(1, "name" + i);
// 先存着,不执行
ps.addBatch();
if (i % 500 == 0) {
// 存到500条后再执行
ps.executeBatch();
// 清空batch
ps.clearBatch();
}
}
long l2 = System.currentTimeMillis();
System.out.println(l2-l1);
JDBCUtils.closeResource(connection, ps);
}
结果:
496
但使用Batch之前还要做几处配置:
设置自动提交
// 设置自动提交
@Test
public void test3() throws Exception {
Connection connection = JDBCUtils.getConnection();
String sql = "insert into insert_test(name) values(?)";
PreparedStatement ps = connection.prepareStatement(sql);
long l1 = System.currentTimeMillis();
// 关闭自动提交
connection.setAutoCommit(false);
for (int i = 0; i < 20000; i++) {
ps.setObject(1, "name" + i);
// 先存着,不执行
ps.addBatch();
if (i % 500 == 0) {
// 存到500条后再执行
ps.executeBatch();
// 清空batch
ps.clearBatch();
}
}
// 缓存结束再统一提交
connection.commit();
long l2 = System.currentTimeMillis();
System.out.println(l2-l1);
JDBCUtils.closeResource(connection, ps);
}
总结
PrepareStatement和Statement的对比 (面试)
https://www.bilibili.com/video/BV1eJ411c7rf?p=34
他们都是带着sql语句到数据库中执行操作的。
后者存在弊端:拼串和sql注入。
前者还能实现对Blob字段的操作。
前者批量插入数据时更高效:预编译
前者的诸多优点都是源于预编译: