详解jdbc实现对CLOB和BLOB数据类型的操作

 //获得数据库连接 Connection con = ConnectionFactory.getConnection(); con.setAutoCommit(false); Statement st = con.createStatement(); //不需要“for update” ResultSet rs = st.executeQuery("select CLOBATTR from TESTCLOB where ID=1"); if (rs.next()) { java.sql.Clob clob = rs.getClob("CLOBATTR"); Reader inStream = clob.getCharacterStream(); char[] c = new char[(int) clob.length()]; inStream.read(c); //data是读出并需要返回的数据，类型是String data = new String(c); inStream.close(); } inStream.close(); con.commit(); con.close(); 

 //获得数据库连接 Connection con = ConnectionFactory.getConnection(); con.setAutoCommit(false); Statement st = con.createStatement(); //不需要“for update” ResultSet rs = st.executeQuery("select BLOBATTR from TESTBLOB where ID=1"); if (rs.next()) { java.sql.Blob blob = rs.getBlob("BLOBATTR"); InputStream inStream = blob.getBinaryStream(); //data是读出并需要返回的数据，类型是byte[] data = new byte[input.available()]; inStream.read(data); inStream<i style="color:transparent">来源gaodai$ma#com搞$代*码网</i>.close(); } inStream.close(); con.commit(); con.close(); 

 //获得数据库连接 Connection con = ConnectionFactory.getConnection(); con.setAutoCommit(false); Statement st = con.createStatement(); //插入一个空对象empty_clob() st.executeUpdate("insert into TESTCLOB (ID, NAME, CLOBATTR) values (1, "thename", empty_clob())"); //锁定数据行进行更新，注意“for update”语句 ResultSet rs = st.executeQuery("select CLOBATTR from TESTCLOB where ID=1 for update"); if (rs.next()) { //得到java.sql.Clob对象后强制转换为oracle.sql.CLOB oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("CLOBATTR"); Writer outStream = clob.getCharacterOutputStream(); //data是传入的字符串，定义：String data char[] c = data.toCharArray(); outStream.write(c, 0, c.length); } outStream.flush(); outStream.close(); con.commit(); con.close(); 

 //获得数据库连接 Connection con = ConnectionFactory.getConnection(); con.setAutoCommit(false); Statement st = con.createStatement(); //插入一个空对象empty_blob() st.executeUpdate("insert into TESTBLOB (ID, NAME, BLOBATTR) values (1, "thename", empty_blob())"); //锁定数据行进行更新，注意“for update”语句 ResultSet rs = st.executeQuery("select BLOBATTR from TESTBLOB where ID=1 for update"); if (rs.next()) { //得到java.sql.Blob对象后强制转换为oracle.sql.BLOB oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR"); OutputStream outStream = blob.getBinaryOutputStream(); //data是传入的byte数组，定义：byte[] data outStream.write(data, 0, data.length); } outStream.flush(); outStream.close(); con.commit(); con.close(); 

 # tnsnames.ora Network Configuration File: d:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora # Generated by Oracle configuration tools. 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) ) ) 

 create table TEST_ORALOB ( ID     VARCHAR2(20), TSBLOB BLOB not null, TSCLOB CLOB not null ) 

 package lavasoft.oralob.common; import oracle.sql.BLOB; import java.io.*; import java.sql.*; /** * JDBC读写Oracle10g的CLOB、BLOB * */ public class TestOraLob { public static void main(String[] args) { insertBlob(); queryBlob(); } public static void insertBlob() { Connection conn = DBToolkit.getConnection(); PreparedStatement ps = null; try { String sql = "insert into test_oralob (ID, TSBLOB, TSCLOB) values (?, ?, ?)"; ps = conn.prepareStatement(sql); ps.setString(1, "100"); //设置二进制BLOB参数 File file_blob = new File("C:\\a.jpg-600"); InputStream in = new BufferedInputStream(new FileInputStream(file_blob)); ps.setBinaryStream(2, in, (int) file_blob.length()); //设置二进制CLOB参数 File file_clob = new File("c:\\a.txt"); InputStreamReader reader = new InputStreamReader(new FileInputStream(file_clob)); ps.setCharacterStream(3, reader, (int) file_clob.length()); ps.executeUpdate(); in.close(); } catch (IOException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { DBToolkit.closeConnection(conn); } } public static void queryBlob() { Connection conn = DBToolkit.getConnection(); PreparedStatement ps = null; Statement stmt = null; ResultSet rs = null; try { String sql = "select TSBLOB from TEST_ORALOB where id ='100'"; stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if (rs.next()) { //读取Oracle的BLOB字段 InputStream in = rs.getBinaryStream(1); File file = new File("c:\\a1.jpg-600"); OutputStream out = new BufferedOutputStream(new FileOutputStream(file)); byte[] buff1 = new byte[1024]; for (int i = 0; (i = in.read(buff1)) > 0;) { out.write(buff1, 0, i); } out.flush(); out.close(); in.close(); //读取Oracle的CLOB字段 char[] buff2 = new char[1024]; File file_clob = new File("c:\\a1.txt"); OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(file_clob)); Reader reader = rs.getCharacterStream(1); for (int i = 0; (i = reader.read(buff2)) > 0;) { writer.write(buff2, 0, i); } writer.flush(); writer.close(); reader.close(); } rs.close(); stmt.close(); } catch (IOException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { DBToolkit.closeConnection(conn); } } } 

 //设置二进制CLOB参数 String xxx = "abcdefg"; ps.setCharacterStream(3, new StringReader(xxx), xxx.getBytes("GBK").length); ps.executeUpdate(); in.close();