JDBC对CLOB的增修查

package com.yysoft.a.test;

import java.io.IOException;
import java.io.Reader;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.yysoft.util.Debug;
import com.yysoft.util.Library;

import oracle.jdbc.driver.OracleTypes;
import oracle.sql.CLOB;

public class Test {

 /**
  * @param args
  */
 public static void main(String[] args) {
  updateClob();
  getOtherClob();
 }
 
 public static void addClob(){
  try {
   String content="AAAAAAAAAAAAAAAAAAAAAAAAAAAAABBBBBBBBBBBBBBBBBBBBBCCCCCCCCCCCCCCCCCCCCCCCC";
   char[] cc=content.toCharArray();
   Class.forName("oracle.jdbc.driver.OracleDriver");
   Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@10.99.1.114:1521:orcl","e3baoan","e3baoan");
   //手动提交
   conn.setAutoCommit(false);
   /* 以下表cjhtest中的cjhcontent字段时CLOB类型的 */
   //插入一条数据，注意CLOB字段，需要先插入一个空的clob类型 empty_clob()，然后再单独更新clob字段
   String sql = "insert into cjhtest(cjhid,cjhcontent) values(?,empty_clob())";
   PreparedStatement pstmt=conn.prepareStatement(sql);
   pstmt.setInt(1, 1);
   int i1=pstmt.executeUpdate();
   conn.commit();
   pstmt=null;
   if(i1>0){
    System.out.println("插入成功");
   }
  
   ResultSet rs=null;
   CLOB clob=null;
   String sql1="select cjhcontent from cjhtest where cjhid=? for update";
   pstmt=conn.prepareStatement(sql1);
   pstmt.setInt(1, 1);
   rs=pstmt.executeQuery();
   if(rs.next()){
    //取得刚才的HCONTENT的内容，也就是刚才添加的empty_clob()
    clob=(CLOB) rs.getClob(1);
   }
   //需要用clob.getCharacterOutputStream()流方式输出
   Writer wirte=clob.getCharacterOutputStream();
   wirte.write(cc);  //向大字段定数据
   wirte.flush();    //强制写入
   wirte.close();    //关闭
   rs.close();
   conn.commit();
   pstmt.close();
   conn.close();
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 
 public static void updateClob(){
  try {
   String content="ABCDEFGHIJKLMNOPQRSTUVWSYZ";
   char[] cc=content.toCharArray();
   Class.forName("oracle.jdbc.driver.OracleDriver");
   Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@10.99.1.114:1521:orcl","e3baoan","e3baoan");
   conn.setAutoCommit(false);
   String sql="update cjhtest set cjhcontent=empty_clob() where cjhid=?";
   PreparedStatement pstmt=conn.prepareStatement(sql);
   pstmt.setInt(1, 1);
   int i1=pstmt.executeUpdate();
   conn.commit();
   pstmt=null;
   if(i1>0){
    System.out.println("清空成功");
   }
  
   ResultSet rs=null;
   CLOB clob=null;
   String sql1="select cjhcontent from cjhtest where cjhid=? for update";
   pstmt=conn.prepareStatement(sql1);
   pstmt.setInt(1, 1);
   rs=pstmt.executeQuery();
   if(rs.next()){
    clob=(CLOB) rs.getClob(1);
   }
   Writer writer=clob.getCharacterOutputStream();
   writer.write(cc);
   writer.flush();
   writer.close();
   rs.close();
   conn.commit();
   pstmt.close();
   conn.close();
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (IOException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 
 public static void getClob(){
  try {
   String content="";
   Class.forName("oracle.jdbc.driver.OracleDriver");
   Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@10.99.1.114:1521:orcl","e3baoan","e3baoan");
   conn.setAutoCommit(false);
   ResultSet rs=null;
   CLOB clob=null;
   String sql="select cjhcontent from cjhtest where cjhid=?";
   PreparedStatement pstmt=conn.prepareStatement(sql);
   pstmt.setInt(1, 1);
   rs=pstmt.executeQuery();
   if(rs.next()){
    clob=(CLOB) rs.getClob(1);
   }
   if(clob!=null && clob.length()!=0){
    content=clob.getSubString((long)1, (int)clob.length());
   }
   rs.close();
   conn.commit();
   pstmt.close();
   conn.close();
   System.out.println(content);
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 
 //取得的另一种方式
 public static void getOtherClob(){
  try {
   String content="";
   Class.forName("oracle.jdbc.driver.OracleDriver");
   Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@10.99.1.114:1521:orcl","e3baoan","e3baoan");
   conn.setAutoCommit(false);
   ResultSet rs=null;
   CLOB clob=null;
   String sql="select cjhcontent from cjhtest where cjhid=?";
   PreparedStatement pstmt=conn.prepareStatement(sql);
   pstmt.setInt(1, 1);
   rs=pstmt.executeQuery();
   if(rs.next()){
    clob=(CLOB) rs.getClob(1);
   }
   if(clob!=null && clob.length()!=0){
    content=Clob2String(clob);
   }
   rs.close();
   conn.commit();
   pstmt.close();
   conn.close();
   System.out.println(content);
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 
 /**
 * 从大字段对象中读取数据,并转换成字符
 * @return 大字段字符
 */
 public static String Clob2String(CLOB clob)
 {
  String content = null;
  StringBuffer stringBuf = new StringBuffer();

  try
  {
   int length   = 0;
   Reader inStream  = clob.getCharacterStream();  //取得大字侧段对象数据输出流
   char[] buffer  = new char[10];

   while ((length = inStream.read(buffer)) != -1)   //读取数据库 //每10个10个读取
   {
    for (int i=0; i<length; i++)
    {
     stringBuf.append(buffer[i]);
    }
   }
  
   inStream.close();
   content = stringBuf.toString();
  }
  catch(Exception ex)
  {
   System.out.println("ClobUtil.Clob2String:" + ex.getMessage());
  }

  return content;
 }
 
 
 /**
  * 把字符串存入大字段对象中
  * @param content  内容
  * @return
  */
 public static CLOB String2Clob(String content)
 {
  boolean flag = false;
  if(content==null)
   return null;
  CLOB clob=null;
  try
  {
   Writer outStream = clob.getCharacterOutputStream();  //取得大字段对象数据输入流
   char[] data = Library.toChinese(content).toCharArray(); //把源字符串转换成char数组

   outStream.write(data);         //向大字段定数据
   outStream.flush();          //强制写入
   outStream.close();          //关闭
   return clob;
  }
  catch(Exception ex)
  {
   System.out.println("ClobUtil.String2Clob:" + ex.getMessage());
   return null;
  }
 }
}