1.blob数据的插入
@Test
//插入一条blog数据customers
public void testInsert() throws Exception{
//1.获取一连接
Connection conn = JDBCUtils.getConnection();
//2.获取prepared
String sql = "insert into customers(name,photo) values(?,?)";
PreparedStatement ps = conn.prepareStatement(sql);
//3.填充sql
ps.setString(1,"风景");
//单元测试模块默认路径在工程下
FileInputStream fs = new FileInputStream(new File("girl.jpg"));
ps.setBlob(2, fs);
//4.执行
ps.execute();
//5.关闭资源
JDBCUtils.closeResource(conn, ps);
fs.close();
}
@Test
//查询一条blog数据
public void testSelect() throws Exception{
//1.获取一连接
Connection conn = JDBCUtils.getConnection();
//2.获取prepared
String sql = "select name,photo from customers where name =?";
PreparedStatement ps = conn.prepareStatement(sql);
//3.填充sql
ps.setString(1,"风景");
//4.执行
ResultSet rs = ps.executeQuery();
//5.保存数据
if(rs.next()){
String name = rs.getString(1);
Blob blob = rs.getBlob(2);
InputStream is = blob.getBinaryStream();
FileOutputStream fs = new FileOutputStream("fengjing.jpg");
byte[] buffer = new byte[1024];
int len;
while((len=is.read(buffer))!=-1){
fs.write(buffer, 0, len);
}
fs.close();
is.close();
}
//6.关闭资源
JDBCUtils.closeResource(conn, ps);
rs.close();
}
//默认pack大小为1M 如果大于这个,尽管blob支持16m,但是仍然不能增加
//需要在init文件中 增加 max_allowed_packet=16M 此时可以支持16m
}
2.批量处理
//预编译含义:即preparedstatement是预编译的,当其将sql语句送入DBMS时,
//sql语句不需要经过编译校验,直接执行
//addBatch()即正常来说java送入一条语句进入DBMS,但是因为送的时间慢,但是DBMS处理速度很快
//故我们可以将打包一批语句送入DBMS一起执行
//commit()提交操作,正常来说(DML)sql语句进入DBMS时每执行一次自动commit一次,但是可以我们可以执行多次
//然后进行一次commit
public class manyInsert {
//1.批量插入方式:通过statement来执行insert语句的拼接操作
//2.使用preparedStatement批量操作
@Test
public void testInsert1() throws Exception{
Connection conn = JDBCUtils.getConnection();
String sql = "insert into goods(name) values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
long cus = System.currentTimeMillis();
for(int i = 0 ;i<20;i++){
ps.setObject(1, "lihua"+i);
ps.execute();
}
long cusm = System.currentTimeMillis();
System.out.println(cus-cusm);
JDBCUtils.closeResource(conn, ps);
}
//3.积攒sql的占位符一起(减少java和数据库的交互次数)
// 默认情况下 sql不支持batch(需要更改init以及更新驱动)
@Test
public void testInsert2() throws Exception{
Connection conn = JDBCUtils.getConnection();
System.out.println("连接获取成功");
String sql = "insert into goods(name) values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
long cus = System.currentTimeMillis();
for(int i = 0 ;i<20000;i++){
ps.setObject(1, "lihua"+i);
ps.addBatch();
if(i%500==0)
{
ps.execute();
ps.clearBatch();
}
}
long cusm = System.currentTimeMillis();
System.out.println(cus-cusm);
JDBCUtils.closeResource(conn, ps);
}
//4.DML执行一次会commit一次,我们可以延迟到最后再commit
@Test
public void testInsert3() throws Exception{
Connection conn = JDBCUtils.getConnection();
String sql = "insert into goods(name) values(?)";
PreparedStatement ps = conn.prepareStatement(sql);
long cus = System.currentTimeMillis();
conn.setAutoCommit(false);
for(int i = 0 ;i<20000;i++){
ps.setObject(1, "lihua"+i);
ps.addBatch();
if(i%500==0)
{
ps.execute();
ps.clearBatch();
}
}
conn.commit();
long cusm = System.currentTimeMillis();
System.out.println(cusm-cus);
JDBCUtils.closeResource(conn, ps);
}
}