- PrepareStatement可以操作Blob类型
4.1MySQL BLOB类型
image.png
4.2像数据表中插入大数据类型
public void test1(){
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into customers(name,email,birth,photo)" +
"values(?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setObject(1, "张宇浩");
ps.setObject(2, "zhangyuhao@qq.com");
ps.setObject(3, "1992-09-08");
FileInputStream fis = new FileInputStream(new File(
"src", "demo.JPG"
));
//index，InputStream
ps.setBlob(4, fis);
ps.execute();
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps);
}
4.3修改数据表中的Blob类型字段
- 使用get（别名）方式比索引更好
@Test
public void queryBlob(){
Connection conn = null;
PreparedStatement ps = 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, 20);
ResultSet rs = ps.executeQuery();
if(rs.next())
{
// int id = rs.getInt(1);
// String name = rs.getString(2);
// String email = rs.getString(3);
// java.sql.Date photo = rs.getDate(4);
//方式2：
int id = rs.getInt("id");
String name = rs.getString("name");
String email = rs.getString("email");
Date birth = rs.getDate("birth");
}
FileInputStream fis = new FileInputStream(new File(
"src", "demo.JPG"
));
Blob photo = rs.getBlob("photo");
//下载
is = photo.getBinaryStream();
fos = new FileOutputStream("copyPhoto.jpg");
byte[] buffer = new byte[1024];
int len = 0;
while((len = is.read(buffer)) != -1)
{
fos.write(buffer, 0, len);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
is.close();
} catch (IOException e) {
e.printStackTrace();
}
try {
fos.close();
} catch (IOException e) {
e.printStackTrace();
}
JDBCUtils.closeResource(conn, ps);
}
4.4从数据表中读入(query)大数据类型
MediumBlob最大16M，改mysql的配置文件my.ini
