本文主要是介绍四、操作BLOB类型字段,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!
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();}// 批量插入方式二:使用PreparedStatementpublic 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 blocke.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.“攒”sqlps.addBatch();if (i%500==0) {//2.执行Batchps.executeBatch();//3.清空Batchps.clearBatch();}}long end = System.currentTimeMillis();System.out.println("执行的时间为:" + (end - start));} catch (Exception e) {// TODO Auto-generated catch blocke.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.“攒”sqlps.addBatch();if (i%500==0) {//2.执行Batchps.executeBatch();//3.清空Batchps.clearBatch();}}//提交数据conn.commit();long end = System.currentTimeMillis();System.out.println("执行的时间为:" + (end - start));} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();} finally {JDBCUtils.closeResource(conn, ps);}}
}
这篇关于四、操作BLOB类型字段的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!