详解jdbc实现对CLOB和BLOB数据类型的操作
详解jdbc实现对CLOB和BLOB数据类型的操作
1、 读取操作
CLOB
//获得数据库连接 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();
BLOB
//获得数据库连接 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.close(); } inStream.close(); con.commit(); con.close();
2、写入操作
CLOB
//获得数据库连接 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();
BLOB
//获得数据库连接 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();
3、读写CLOB/BLOB数据到文件
TNS:
# 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) ) )
Table:
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"); 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"); 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字段,简化写法:
//设置二进制CLOB参数 String xxx = "abcdefg"; ps.setCharacterStream(3, new StringReader(xxx), xxx.getBytes("GBK").length); ps.executeUpdate(); in.close();
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持,如有疑问请留言或者到本站社区交流讨论,感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!
赞 (0)