/******************blob*********************/
public class Blob_Test {
//创建表
@Test
public void create(){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs = null;
JDBCUtils utils = JDBCUtils.getInstance();
String sql ="create table Blob_Test(id int not null auto_increment,image BLOB,PRIMARY KEY(ID))";
try {
conn=utils.getConnection();
ps = conn.prepareStatement(sql);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
utils.free(conn, ps, rs);
}
}
//插入Blob数据
@Test
public void insert() throws IOException{
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs = null;
JDBCUtils utils = JDBCUtils.getInstance();
//得到一个文件
File file = new File("src/2.jpg");
//创建字节输入流
InputStream in = new FileInputStream(file);
//创建sql语句
String sql ="insert into Blob_Test(image) values (?)";
try {
conn=utils.getConnection();
ps = conn.prepareStatement(sql);
//为?赋值 使用setBinaryStream方法
ps.setBinaryStream(1, in, file.length());
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
utils.free(conn, ps, rs);
}
}
//读取数据库blob数据
@Test
public void read() throws IOException{
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs = null;
JDBCUtils utils = JDBCUtils.getInstance();
//创建一个文件
File file = new File("BeautifulGirl.jpg");
//创建一个字节输出流
OutputStream out = new FileOutputStream(file);
//创建sql语句,从数据库查出blob数据
String sql ="select image from Blob_Test";
try {
conn=utils.getConnection();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
//创建一个数组
byte b[] = new byte[1024];
while(rs.next()){
//得到结果集中的blob数据
Blob blob = rs.getBlob("image");
//得到一个输入流
InputStream in = blob.getBinaryStream();
int len;
while((len=in.read(b))!=-1){
//将数据流的数据写到输出流
out.write(b, 0, len);
}
//关闭流
out.close();
in.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
utils.free(conn, ps, rs);
}
}
}
/******************clob*********************/
public class Clob_Test {
//创建表
@Test
public void create(){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs =null;
JDBCUtils utils = JDBCUtils.getInstance();
try {
String sql = "create table Clob_Test(id int not null auto_increment,text longtext,primary key(id))";
conn = utils.getConnection();
ps= conn.prepareStatement(sql);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
utils.free(conn, ps, rs);
}
}
//向数据库插入clob数据
@Test
public void insert() throws IOException{
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs =null;
JDBCUtils utils = JDBCUtils.getInstance();
//将一个文件插入到数据库
File file = new File("src/com/hdbc/day01/JDBCUtils.java");
//创建一个字符输入缓冲流
Reader reader = new BufferedReader(new FileReader(file));
try {
String sql = "insert into Clob_Test(text) values (?)";
conn = utils.getConnection();
ps= conn.prepareStatement(sql);
//以字符流的形式将文件存到数据库
ps.setCharacterStream(1, reader,file.length());
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
utils.free(conn, ps, rs);
}
}
//从数据库读取clob数据
@Test
public void read() throws IOException{
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs =null;
JDBCUtils utils = JDBCUtils.getInstance();
//将clob数据读到文件中
File file = new File("JDBCUtils_bak.java");
//创建一个字符输出缓冲流
Writer writer = new BufferedWriter(new FileWriter(file));
char ch[] = new char[1024];
try {
String sql = "select text from Clob_Test";
conn = utils.getConnection();
ps= conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
//两种方式:
//一种是先得到数据clob数据,
// Clob clob = rs.getClob("text");
//再得带字符输入流
// Reader reader = clob.getCharacterStream();
//第二种是可以直接得到clob数据的输入流
Reader reader = rs.getCharacterStream("text");
for(int i=0;(i=reader.read(ch))>0;){
//将数据写到目标文件中
writer.write(ch, 0, i);
}
writer.close();
reader.close();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
utils.free(conn, ps, rs);
}
}
}
插入和读取blob和clob类型数据
最新推荐文章于 2022-09-20 16:05:48 发布