package blob;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
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 java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import org.junit.Test;
import bean.Thesis;
import util.CRUD.JDBCUtils;
/*
* 测试使用PreparedStatement操作Blob类型的数据
*/
public class BlobTest {
//向thesis表中插入Blob类型的 字段
@Test
public void testInsert() {
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
String sql = "insert into thesis(t_name,t_author,t_create_time,t_magazine,t_number,is_del,photo)values(?,?,?,?,?,?,?)"; //？表示占位符
ps = conn.prepareStatement(sql );
ps.setString(1,"马博文");
ps.setString(2, "果");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = sdf.parse("2020-03-08");
ps.setDate(3, new Date(date.getTime()));
ps.setString(4, "保安队");
ps.setInt(5, 11);
ps.setInt(6, 0);
FileInputStream is = new FileInputStream(new File("e5ca507f9ab9d4d76d14dc3123d8043.jpg"));
ps.setBlob(7, is);
ps.execute();
}catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn, ps);
}
}
//查询数据表thesis中的blob类型的字段
@Test
public void testQuery() {
Connection conn=null;
PreparedStatement ps=null;
InputStream is=null;
FileOutputStream fos=null;
ResultSet rs=null;
try {
conn = JDBCUtils.getConnection();
String sql="select id,t_name,t_create_time,t_author,t_magazine,t_number,photo from thesis where id = ? ";
ps = conn.prepareStatement(sql);
ps.setObject(1, 53);
rs = ps.executeQuery();
if(rs.next()) {
// 方式一
// int id = rs.getInt(1);
// String t_name= rs.getString(2);
// Date t_create_time = rs.getDate(3);
// String t_author = rs.getString(4);
// String t_magazine = rs.getString(5);
// int t_number = rs.getInt(6);
//方式二:推荐使用这个
int id = rs.getInt("id");
String t_name = rs.getString("t_name");
Date t_create_time = rs.getDate("t_create_time");
String t_author = rs.getString("t_author");
String t_magazine = rs.getString("t_magazine");
int t_number = rs.getInt("t_number");
Thesis the = new Thesis(id, t_name, t_author, t_create_time, t_magazine, t_number);
System.out.println(the);
//将Blob类型的 字段下载下来，以文件的方式保存在本地
Blob photo = rs.getBlob("photo");
is = photo.getBinaryStream();
fos = new FileOutputStream("gg.jpg");
byte [] buffer = new byte[1024];
int len;
while((len = is.read(buffer))!= -1) {
fos.write(buffer,0,len);
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(is != null)
is.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(fos != null)
fos.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
JDBCUtils.closeResource(conn, ps, rs);
}
}
}
