1.大文本数据:Clob
package com.hcx.jdbc;
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import com.hcx.utils.JdbcUtils;
//演示大文本数据存储及读取
//CREATE TABLE t2
//(
// id INT PRIMARY KEY ,
// txt TEXT
//)
public class ClobTest {
@Test
public void testSave() throws Exception{
//获得链接对象
Connection conn = JdbcUtils.getConnection() ;
//创建预处理命令对象
PreparedStatement pstmt = conn.prepareStatement("insert into t2 values(?,?)") ;
//指定?的值
pstmt.setInt(1, 1) ;
File file = new File("src/a.txt") ;
FileReader fr = new FileReader(file) ;
pstmt.setCharacterStream(2,fr,(int)file.length()) ;
//执行sql语句
pstmt.executeUpdate() ;
//释放资源
JdbcUtils.release(null, pstmt, conn) ;
}
@Test
public void testQuery() throws Exception{
//获得链接对象
Connection conn = JdbcUtils.getConnection() ;
//创建预处理命令对象
PreparedStatement pstmt = conn.prepareStatement("select * from t2") ;
//执行sql语句
ResultSet rs = pstmt.executeQuery() ;
if(rs.next()){
int id = rs.getInt("id") ;
Reader reader = rs.getCharacterStream("txt") ;
//需要再建一个文件
File f = new File("src/b.txt") ;
BufferedWriter bw = new BufferedWriter(new FileWriter(f)) ;
BufferedReader br = new BufferedReader(reader) ;
String s = "" ;
while((s = br.readLine()) != null){
bw.write(s) ;
bw.newLine() ;
}
br.close() ;
bw.close() ;
}
//释放资源
JdbcUtils.release(rs, pstmt, conn) ;
}
}
2、大二进制数据:Blob(图片、电影、声音、压缩包)
package com.hcx.jdbc;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;
import com.hcx.utils.JdbcUtils;
//演示二进制的数据存储及读取
//CREATE TABLE t1
//(
// id INT PRIMARY KEY ,
// image BLOB
//)
public class BlobTest {
@Test
public void testSave() throws Exception{
//获得链接对象
Connection conn = JdbcUtils.getConnection() ;
//创建预处理命令对象
PreparedStatement pstmt = conn.prepareStatement("insert into t1 values(?,?)") ;
//指定?的值
pstmt.setInt(1, 1) ;
File file = new File("src/2.gif") ;
InputStream in = new FileInputStream(file) ;
//由于file.length()返回的是long类型,mysql不支持,所以要强转成int
pstmt.setBinaryStream(2,in, (int)file.length()) ;
//执行sql语句
pstmt.executeUpdate() ;
//释放资源
JdbcUtils.release(null, pstmt, conn) ;
}
@Test
public void testQuery() throws Exception{
//获得链接对象
Connection conn = JdbcUtils.getConnection() ;
//创建预处理命令对象
PreparedStatement pstmt = conn.prepareStatement("select * from t1") ;
//执行sql语句
ResultSet rs = pstmt.executeQuery() ;
if(rs.next()){
int id = rs.getInt("id") ;
InputStream is = rs.getBinaryStream("image") ;
//需要再建一个文件
File f = new File("src/5.jpg") ;
OutputStream os = new FileOutputStream(f) ;
byte[] bs = new byte[1024] ;
int b = 0 ;
while((b = is.read(bs)) != -1){
os.write(bs,0,b) ;
}
is.close() ;
os.close() ;
}
//释放资源
JdbcUtils.release(rs, pstmt, conn) ;
}
}