通过JDBC 操作 ORACLE BLOB,CLOB字段类型

 一、BLOB操作 1、入库 （1）JDBC方式 //通过JDBC获得数据库连接 Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:testdb", "test", "test"); 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(); （2）JNDI方式 //通过JNDI获得数据库连接 Context context = new InitialContext(); ds = (DataSource) context.lookup("ORA_JNDI"); Connection con = ds.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对象后强制转换为weblogic.jdbc.vendor.oracle.OracleThinBlob（不同的App Server对应的可能会不同） weblogic.jdbc.vendor.oracle.OracleThinBlob blob = (weblogic.jdbc.vendor.oracle.OracleThinBlob) 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(); 2、出库 //获得数据库连接 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(); 二、CLOB操作 1、入库 （1）JDBC方式 //通过JDBC获得数据库连接 Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:testdb", "test", "test"); 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(); （2）JNDI方式 //通过JNDI获得数据库连接 Context context = new InitialContext(); ds = (DataSource) context.lookup("ORA_JNDI"); Connection con = ds.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对象后强制转换为weblogic.jdbc.vendor.oracle.OracleThinClob（不同的App Server对应的可能会不同） weblogic.jdbc.vendor.oracle.OracleThinClob clob = (weblogic.jdbc.vendor.oracle.OracleThinClob) 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(); 2、出库 //获得数据库连接 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();