详解jdbc实现对CLOB和BLOB数据类型的操作
详解jdbc实现对CLOB和BLOB数据类型的操作
1、读取操作
CLOB
//获得数据库连接
Connectioncon=ConnectionFactory.getConnection();
con.setAutoCommit(false);
Statementst=con.createStatement();
//不需要“forupdate”
ResultSetrs=st.executeQuery("selectCLOBATTRfromTESTCLOBwhereID=1");
if(rs.next())
{
java.sql.Clobclob=rs.getClob("CLOBATTR");
ReaderinStream=clob.getCharacterStream();
char[]c=newchar[(int)clob.length()];
inStream.read(c);
//data是读出并需要返回的数据，类型是String
data=newString(c);
inStream.close();
}
inStream.close();
con.commit();
con.close();
BLOB
//获得数据库连接
Connectioncon=ConnectionFactory.getConnection();
con.setAutoCommit(false);
Statementst=con.createStatement();
//不需要“forupdate”
ResultSetrs=st.executeQuery("selectBLOBATTRfromTESTBLOBwhereID=1");
if(rs.next())
{
java.sql.Blobblob=rs.getBlob("BLOBATTR");
InputStreaminStream=blob.getBinaryStream();
//data是读出并需要返回的数据，类型是byte[]
data=newbyte[input.available()];
inStream.read(data);
inStream.close();
}
inStream.close();
con.commit();
con.close();
2、写入操作
CLOB
//获得数据库连接
Connectioncon=ConnectionFactory.getConnection();
con.setAutoCommit(false);
Statementst=con.createStatement();
//插入一个空对象empty_clob()
st.executeUpdate("insertintoTESTCLOB(ID,NAME,CLOBATTR)values(1,"thename",empty_clob())");
//锁定数据行进行更新，注意“forupdate”语句
ResultSetrs=st.executeQuery("selectCLOBATTRfromTESTCLOBwhereID=1forupdate");
if(rs.next())
{
//得到java.sql.Clob对象后强制转换为oracle.sql.CLOB
oracle.sql.CLOBclob=(oracle.sql.CLOB)rs.getClob("CLOBATTR");
WriteroutStream=clob.getCharacterOutputStream();
//data是传入的字符串，定义：Stringdata
char[]c=data.toCharArray();
outStream.write(c,0,c.length);
}
outStream.flush();
outStream.close();
con.commit();
con.close();
BLOB
//获得数据库连接
Connectioncon=ConnectionFactory.getConnection();
con.setAutoCommit(false);
Statementst=con.createStatement();
//插入一个空对象empty_blob()
st.executeUpdate("insertintoTESTBLOB(ID,NAME,BLOBATTR)values(1,"thename",empty_blob())");
//锁定数据行进行更新，注意“forupdate”语句
ResultSetrs=st.executeQuery("selectBLOBATTRfromTESTBLOBwhereID=1forupdate");
if(rs.next())
{
//得到java.sql.Blob对象后强制转换为oracle.sql.BLOB
oracle.sql.BLOBblob=(oracle.sql.BLOB)rs.getBlob("BLOBATTR");
OutputStreamoutStream=blob.getBinaryOutputStream();
//data是传入的byte数组，定义：byte[]data
outStream.write(data,0,data.length);
}
outStream.flush();
outStream.close();
con.commit();
con.close();
3、读写CLOB/BLOB数据到文件
TNS：
#tnsnames.oraNetworkConfigurationFile:d:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora #GeneratedbyOracleconfigurationtools. ORADB= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)) ) (CONNECT_DATA= (SID=ORCL) ) ) MYORCL= (DESCRIPTION= (ADDRESS_LIST= (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)) ) (CONNECT_DATA= (SERVICE_NAME=myorcl) ) )
Table：
createtableTEST_ORALOB ( IDVARCHAR2(20), TSBLOBBLOBnotnull, TSCLOBCLOBnotnull )
测试代码：
packagelavasoft.oralob.common;
importoracle.sql.BLOB;
importjava.io.*;
importjava.sql.*;
/**
*JDBC读写Oracle10g的CLOB、BLOB
*
*/
publicclassTestOraLob{
publicstaticvoidmain(String[]args){
insertBlob();
queryBlob();
}
publicstaticvoidinsertBlob(){
Connectionconn=DBToolkit.getConnection();
PreparedStatementps=null;
try{
Stringsql="insertintotest_oralob(ID,TSBLOB,TSCLOB)values(?,?,?)";
ps=conn.prepareStatement(sql);
ps.setString(1,"100");
//设置二进制BLOB参数
Filefile_blob=newFile("C:\\a.jpg");
InputStreamin=newBufferedInputStream(newFileInputStream(file_blob));
ps.setBinaryStream(2,in,(int)file_blob.length());
//设置二进制CLOB参数
Filefile_clob=newFile("c:\\a.txt");
InputStreamReaderreader=newInputStreamReader(newFileInputStream(file_clob));
ps.setCharacterStream(3,reader,(int)file_clob.length());
ps.executeUpdate();
in.close();
}catch(IOExceptione){
e.printStackTrace();
}catch(SQLExceptione){
e.printStackTrace();
}finally{
DBToolkit.closeConnection(conn);
}
}
publicstaticvoidqueryBlob(){
Connectionconn=DBToolkit.getConnection();
PreparedStatementps=null;
Statementstmt=null;
ResultSetrs=null;
try{
Stringsql="selectTSBLOBfromTEST_ORALOBwhereid='100'";
stmt=conn.createStatement();
rs=stmt.executeQuery(sql);
if(rs.next()){
//读取Oracle的BLOB字段
InputStreamin=rs.getBinaryStream(1);
Filefile=newFile("c:\\a1.jpg");
OutputStreamout=newBufferedOutputStream(newFileOutputStream(file));
byte[]buff1=newbyte[1024];
for(inti=0;(i=in.read(buff1))>0;){
out.write(buff1,0,i);
}
out.flush();
out.close();
in.close();
//读取Oracle的CLOB字段
char[]buff2=newchar[1024];
Filefile_clob=newFile("c:\\a1.txt");
OutputStreamWriterwriter=newOutputStreamWriter(newFileOutputStream(file_clob));
Readerreader=rs.getCharacterStream(1);
for(inti=0;(i=reader.read(buff2))>0;){
writer.write(buff2,0,i);
}
writer.flush();
writer.close();
reader.close();
}
rs.close();
stmt.close();
}catch(IOExceptione){
e.printStackTrace();
}catch(SQLExceptione){
e.printStackTrace();
}finally{
DBToolkit.closeConnection(conn);
}
}
}
注：如果是具体的字符串写入CLOB字段，简化写法：
//设置二进制CLOB参数
Stringxxx="abcdefg";
ps.setCharacterStream(3,newStringReader(xxx),xxx.getBytes("GBK").length);
ps.executeUpdate();
in.close();
感谢阅读，希望能帮助到大家，谢谢大家对本站的支持，如有疑问请留言或者到本站社区交流讨论，感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！