1.MySQL BLOB类型
●MySQL中,BLOB是-一个二进制大型对象,是-一个可以存储大量数据的容器,它能容纳不同大小的数据。
●插入BLOB类型的数据必须使用PreparedStatement,因为BLOB类型的数据无法使用字符串拼接写的。
●MySQL的四种BLOB类型(除了在存储的最大信息量上不同外,他们是等同的)
2.插入读取大数据类型
插入步骤:
String sql = "INSERT INTO customer(name, email, birth, photo) VALUES(?, ?, ?, ?)";
conn = JDBCUtil.getConnection();
ps = conn.preparedStatement(sql);
ps.setString(1, "LDH");
ps.setString(2, "LDH@163.com");
ps.setDate(3, new Date(new java.util.Date().getTime()));
//填充 Blob 类型的数据
ps.setBlob(4, new FileInputStream("abcd.jpg"));
ps.executeUpdate();
读取步骤:
String sql = "SELECT id, name, email, birth, photo FROM customer WHERE id = ?";
conn = getConnection();
ps = conn.prepareStatement(sql);
ps.setInt(1, 8);
rs = ps.executeQuery();
if (rs.next()) {
Integer id = rs.getInt(1);
String name = rs.getString(2);
String email = rs.getString(3);
Date birth = rs.getDate(4);
Customer cust = new Customer(id, name, email, birth);
System.out.println(cust);
Blob photo = rs.getBlob(5);
InputStream is = photo.getBinaryStream();
OutputStream os = new FileOutputStream("c.jpg");
byte[] buffer = new byte[1024];
int len = 0;
while ((len = is.read(buffer)) != -1) {
os.write(buffer, 0, len);
}
}
3.使用PreparedStatement实现批量插入
批量处理JDBC语句提高处理速度
- 当需要成批插入或者更新记录时。可以采用Java的批量更新机制,这
一机制允许多条语句一次性提交给数据库批量处理。通常情况下比单
独提交处理更有效率 - JDBC的批量处理语句包括下面两个方法:
1.addBatch(String):添加需要批量处理的SQL语句或是参数;
2.executeBatch():执行批量处理语句;
3.clearBatch():清空缓存的数据 - 通常我们会遇到两种批量执行SQL语句的情况
1.多条SQL语句的批量处理;
2.一个SQL语句的批量传参;
一个SQL语句的批量传参
优化1:
- 使用PreparedStatement替代Statement
优化2:
- 使用 addBatch() / executeBatch() / clearBatch()
- ?rewriteBatchedStatements=true&useServerPrepStmts=false
- 使用更新的mysql 驱动:
mysql-connector-java-5.1.37-bin.jar
优化3:
- Connection 的 setAutoCommit(false) / commit()
代码演示:
import java.sql.Connection;
import java.sql.PreparedStatement;
import com.atguigu3.util.JDBCUtils;
/*
* 使用PerparedStatement实现批量数据的操作
*
* update、delete本身就具有批量操作的 效果。
* 此时的批量操作,主要指的是批量插入。使用PerparedStatement如何实现更高效的批量插入
*
* 方式一:
* Statement
*/
public class InsertTest {
public static void main(String[] args) {
InsertTest insertTest = new InsertTest();
insertTest.testInsert3();
}
// 批量插入方式二:使用PreparedStatement
public void testInsert1() {
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 <= 20000; i++) {
ps.setObject(1, "name_" + i);
ps.execute();
}
long end = System.currentTimeMillis();
System.out.println("执行的时间为:" + (end - start));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps);
}
}
/*
* 批量插入的方式三:
* 1.addBatch、executeBatch()、clearBatch
*
*/
public void testInsert2() {
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 <= 1000000; 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) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps);
}
}
//方式四:设置不允许自动提交数据
public void testInsert3() {
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 <= 1000000; 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) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps);
}
}
}