MySQL BLOB类型简介:
向Customers表中插入一条记录,包含BLOB类型(图片)
删除和修改与插入一致,在这里不再演示(增删改大概一致)
package test_blob;import java.io.File;import java.io.FileInputStream;import java.sql.Connection;import java.sql.PreparedStatement;/** 向Customers表中插入BLOB类型的字段(例如图片)*/import org.junit.Test;import myjdbc.utils.JDBCUtil;public class TestInsertPhoto {@Testpublic void insertPhoto() throws Exception {//获取连接Connection conn = JDBCUtil.getConnection();//获取PreparedStatement实例String sql = "insert into customers(name,email,birth,photo) values(?,?,?,?)";PreparedStatement ps = conn.prepareStatement(sql);//填充占位符ps.setObject(1, "庄周");ps.setObject(2, "zhuanzhou@");ps.setObject(3, "1300-1-1");FileInputStream fis = new FileInputStream(new File("D:\\eclipse-workspace\\idea_test\\src\\photo.jpg"));ps.setBlob(4, fis);//执行ps.execute();//关闭资源JDBCUtil.closeResource(conn, ps);}}
查询图片保存到本地(第二种方式是通过元素的别名来获取)
详细代码如下:
package test_blob;import java.io.File;import java.io.FileOutputStream;import java.io.InputStream;import java.sql.Blob;import java.sql.Connection;import java.sql.Date;import java.sql.PreparedStatement;import java.sql.ResultSet;import org.junit.Test;import myjdbc.utils.JDBCUtil;import preparedstatement.curd.Customers;public class TestQueryBLOB {@Testpublic void queryBLOB() throws Exception {//获取连接Connection conn = JDBCUtil.getConnection();//获取PreparedStatement实例String sql = "select id,name,email,birth,photo from customers where name=?";PreparedStatement ps = conn.prepareStatement(sql);//填充占位符ps.setObject(1, "庄周");//获得结果集ResultSet rs = ps.executeQuery();//打印记录的基本信息,把BLOB对象(图片)使用流保存在本地if(rs.next()) {//int id = rs.getInt(1);//也可使用下标的方式去获得属性int id = rs.getInt("id");String name = rs.getString("name");String email = rs.getString("email");Date birth = rs.getDate("birth");Customers customers = new Customers(id,name,email,birth);System.out.println(customers);Blob photo = rs.getBlob("photo");InputStream is = photo.getBinaryStream();FileOutputStream fos = new FileOutputStream(new File("src/zhuangzhou.jpg"));int len;byte[] buffer = new byte[1024];while( (len = is.read( buffer ) ) !=-1) {fos.write(buffer, 0, len);System.out.println("*");}//关闭资源fos.close();is.close();JDBCUtil.closeResource(conn, ps,rs);}}}