JDBC操作LOB

在Oracle中，lob类型主要是指：CLOB和BLOB，这两个类型都是用来存储大量数据而设计的。

Blob：是指二进制大对象也就是英文Binary Large Object的所写，是用来存储大量二进制数据。

Clob：是指大字符对象也就是英文Character Large Object的所写，用来存储大量文本数据。

一：操作CLOB

(1)数据库表结构如下：

 create table CLOB_TEST(

ID  VARCHAR2(5) not null,

CONTENT CLOB

)

(2)插入CLOB

方法一：第一步插入一个空值，第二步锁住此行，更新clob字段

public static void insertClob(Connection conn,String data) throws Exception{

//这句话如没有，9i的驱动下会报 java.sql.SQLException: ORA-01002: 读取违反顺序 的异常。

conn.setAutoCommit(false);

 

//插入一个空CLOB

String insertSql = "insert into clob_test(id,content) values('1',empty_clob())";

PreparedStatement  stmt = conn.prepareStatement(insertSql);

stmt.executeUpdate();

stmt.close();

   

// 查询插入的空CLOB 并 lock this line

String selectSql = "select content from clob_test where id = '1' for update";

PreparedStatement pstmt = conn.prepareStatement(selectSql);

ResultSet rs = pstmt.executeQuery();

if(rs.next()){

oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(1);

//为CLOB写信息

BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream()); 

BufferedReader in = new BufferedReader(new FileReader(data)); 

int c; 

while ((c=in.read())!=-1) { 

out.write(c); 

} 

in.close(); 

out.close(); 

}

conn.commit();

pstmt.close();

}

注：此方法在jdk1.4、jdk50、jdk6.0和Oracle9i、Oracle10g、Oracle11g驱动下测试通过！

 

方法二：通过setString方法

public static void insertClob(Connection conn,String data) throws Exception{

String insertSql = "insert into clob_test(id,content) values('1',?)";

PreparedStatement  stmt = conn.prepareStatement(insertSql);

stmt.setString(1, data);

stmt.executeUpdate();

stmt.close();

conn.close();

}

注：由于在Oracle9i的驱动下，setString 有2000字符长度的限制，故这个方法只适合Oracle10g以上的驱动（Oracle11g驱动+JDK6.0也测试通过）。

 

方法三：通过setClob方法

public static void insertClob(Connection conn,String filePath) throws Exception{

String insertSql = "insert into clob_test(id,content) values('1',?)";

PreparedStatement  stmt = conn.prepareStatement(insertSql);

stmt.setClob(1, new FileReader(filePath));

stmt.executeUpdate();

stmt.close();

conn.commit();

}

注：由于setClob(int parameterIndex, Reader reader)这个方法是JDBC4.0规范刚加的内容，是以流的方式为CLOB赋值的。并且Oracle9i驱动、Oracle10g驱动、JDK1.4、JDK1.5是基于JDBC3.0规范的，只有Oracle11g驱动+JDK6.0才是基于JDBC4.0规范的，所以目前这个方法只适合Oracle11g驱动（ojdbc6.jar）+JDK6.0！

 

 

 

(3)读取CLOB

方法一：

public static String readClob(Connection conn) throws Exception{

PreparedStatement  stmt = conn.prepareStatement("select * from clob_test where id = '1'");

ResultSet rs = stmt.executeQuery();

String str="";

StringBuffer sb = new StringBuffer("");

while(rs.next()){

Clob clob = rs.getClob("content");

Reader is = clob.getCharacterStream(); 

BufferedReader br = new BufferedReader(is); 

str = br.readLine(); 

while (str != null){ 

sb.append(str);

str = br.readLine(); 

} 

}

return sb.toString();

}

 

 

 

方法二：

public static String readClob(Connection conn) throws Exception{

PreparedStatement  stmt = conn.prepareStatement("select * from clob_test where id = '1'");

ResultSet rs = stmt.executeQuery();

String str="";

while(rs.next()){

str = rs.getString("content");

}

return str;

}

注：由于在Oracle9i的驱动下，rs.getString 返回为null，所以此方法只适合Oracle10g及其以上驱动。

 

二：操作BLOB

(1)数据库表结构如下：

create table BLOB_TEST(

ID VARCHAR2(5) not null,

CONTENT BLOB

)

 

(2)插入BLOB

方法一：第一步插入一个空值，第二步锁住此行，更新blob字段

public static void writeBlob(Connection con,String filePath) throws Exception{

FileInputStream fis = null;

PreparedStatement psm = null;

File file = new File(filePath);

psm = con.prepareStatement("insert into blob_test(id,content) values('2',empty_blob())");

psm.executeUpdate();

psm = con.prepareStatement("select content from blob_test where id ='2' for update");

ResultSet rs = psm.executeQuery();

if(rs.next()){

oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob(1);

FileInputStream fin = new FileInputStream(file);

OutputStream out = blob.getBinaryOutputStream();

int count = -1, total = 0; 

byte[] data = new byte[blob.getBufferSize()];

while ((count = fin.read(data)) != -1){ 

out.write(data, 0, count); 

} 

out.flush();

out.close();

 

}  

}

 

 

方法二：通过setBinaryStream方法

public static void writeBlob(Connection con,String filePath) throws Exception{

FileInputStream fis = null;

PreparedStatement psm = null;

File file = new File(filePath);

try {

fis = new FileInputStream(file);

psm = con.prepareStatement("insert into blob_test(id,content) values('2',?)");

psm.setBinaryStream(1, fis, fis.available());

psm.executeUpdate();

}finally{

if(fis != null) fis.close();

psm.close();

con.close();

}  

}

 

 

方法三：通过setBlob(int parameterIndex, InputStream inputStream)方法

public static void writeBlob(Connection con,String filePath) throws Exception{

FileInputStream fis = null;

PreparedStatement psm = null;

File file = new File(filePath);

try {

fis = new FileInputStream(file);

psm = con.prepareStatement("insert into blob_test(id,content) values('2',?)");

psm.setBlob(1, fis);

psm.executeUpdate();

}finally{

if(fis != null)

fis.close();

psm.close();

con.close();

}  

}

注：由于setBlob(int parameterIndex, InputStream inputStream)这个方法是JDBC4.0规范刚加的内容，是以流的方式为BLOB赋值的。并且Oracle9i驱动、Oracle10g驱动、JDK1.4、JDK1.5是基于JDBC3.0规范的，只有Oracle11g驱动+JDK6.0才是基于JDBC4.0规范的，所以目前这个方法只适合Oracle11g驱动（ojdbc6.jar）+JDK6.0！

 

 

(3)读取BLOB

public static void readBlob(Connection con,String outFilePath){

Statement sm = null;

ResultSet rs = null;

try {

sm = con.createStatement();

rs = sm.executeQuery("select * from blob_test where id = 2");

if(rs.next()){

Blob blob = rs.getBlob("content");

File file = new File(outFilePath);   

FileOutputStream sout = new FileOutputStream(file);   

InputStream in = blob.getBinaryStream();//获取BLOB数据的输入数据流   

//经BLOB输入数据流读取数据，并将其写入文件   

byte[] b = new byte[256];    

int off = 0;   

int len = b.length;   

for (int i = in.read(b); i != -1;) {    

sout.write(b);    

i = in.read(b);   

}

sout.close();

rs.close();

sm.close();

con.close();

}

} catch (Exception e) {

e.printStackTrace();

}

}