详解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(); 

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持，如有疑问请留言或者到本站社区交流讨论，感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！