1.表中插入BLOB字段的数据
获取文件的绝对路径
Thread.currentThread().getContextClassLoader().getResource("bg.png").getPath()
涉及BLOB字段的数据添加(增删改)
// 涉及BLOB字段的数据添加(增删改)
public void testInsert() throws Exception {
Connection conn=JDBCUtils.getConnection();
String sql="insert into customers(name,email,birth,photo) values(?,?,?,?)";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setObject(1,"ddd");
ps.setObject(2,"aaa.com");
ps.setObject(3,"2020-10-23");
// InputStream is=Test1.class.getClassLoader().getResourceAsStream("bg.png");
FileInputStream fis=new FileInputStream(Thread.currentThread().getContextClassLoader().getResource("bg.png").getPath());
ps.setBlob(4,fis);
ps.execute();
JDBCUtils.closeResource(conn,ps);
}
2.表中查询BLOB字段的数据
普通数据使用对象来封装,blob类型数据使用流存放到本地
// 涉及BLOB字段的数据查询
public void testQuery(){
Connection conn= null;
PreparedStatement ps= null;
ResultSet rs= null;
InputStream is=null;
FileOutputStream fos=null;
try {
conn = JDBCUtils.getConnection();
String sql="select id,name,email,birth,photo from customers where id=?";
ps = conn.prepareStatement(sql);
ps.setObject(1,"16");
rs = ps.executeQuery();
if(rs.next()){
int id=rs.getInt("id");
String name=rs.getString("name");
String email=rs.getString("email");
Date birth=rs.getDate("birth");
Blob photo=rs.getBlob("photo");
Customers c=new Customers(id,name,email,birth);
System.out.println(c);
// 将读取的blob文件转换为流,存储到本地
is=photo.getBinaryStream();
fos=new FileOutputStream("fb.jpg");
int len;
byte[] bytes=new byte[1024];
while((len=is.read(bytes))!=-1){
fos.write(bytes,0,len);
}
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,ps,rs);
try {
if(is!=null)
is.close();
} catch (IOException e) {
e.printStackTrace();
}
try {
if(fos!=null)
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
3.数据批处理操作
3.1 方式一(Statement)
// sql语句会编译多次,并且和数据库交互多次
public void testInsert1() throws Exception {
Connection conn=JDBCUtils.getConnection();
Statement s=conn.createStatement();
for(int i=1;i<20000;i++) {
String sql = "insert into goods(name) values(name_" + i + ")";
s.execute(sql);
}
}
3.2 方式二(preparedStatement)
// sql语句会编译一次,和数据库交互多次
public void testInsert2() {
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.setString(1,"name_"+i);
ps.execute();
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,ps);
}
}
3.3 方式三(preparedStatement,批处理)
// 使用batch,默认mysql不支持,需要更改配置条件
// sql语句会编译一次,和数据库交互次数较少,多次提交,ps.execute提交
public void testInsert3(){
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.setString(1,"name_"+i);
// 1.攒sql语句
ps.addBatch();
if(i%500==0){
// 2.执行语句
ps.executeBatch();
// 3.清空batch
ps.clearBatch();
}
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,ps);
}
}
3.4 方式四(preparedStatement,一次提交)
public void testInsert4(){
Connection conn= null;
PreparedStatement ps= null;
try {
conn = JDBCUtils.getConnection();
// 禁止自动提交
conn.setAutoCommit(false);
String sql="insert into goods(name) values(?)";
ps = conn.prepareStatement(sql);
for(int i=1;i<=20000;i++){
ps.setString(1,"name_"+i);
// 1.攒sql语句
ps.addBatch();
if(i%500==0){
// 2.执行语句
ps.executeBatch();
// 3.清空batch
ps.clearBatch();
}
}
// 统一提交数据
conn.commit();
} catch (Exception e) {
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,ps);
}
}
结果
Test1 t=new Test1();
long b=System.currentTimeMillis();
// t.testInsert2();//13607
// t.testInsert3();//525
t.testInsert4();//487
long e=System.currentTimeMillis();
System.out.println(e-b);