CSDN博客

img shazhenhua

clob大字段jdbc直连(怎么连都可以,这里写的是直连的方法),新增和修改时不乱码的处理

发表于2008/9/29 18:57:00  539人阅读

分类: clob大字段

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.Reader;
import java.io.StringReader;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
/**
 *
 * @author 沙振华
 *2008年9月26日
 */
  public class Clobnono
{
    //ORACLE驱动程序
    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    //ORACLE连接用URL
    private static final String URL = "jdbc:oracle:thin:@10.62.1.12:1521:oracle";
    //用户名
    private static final String USER = "wtdpf";
    //密码
    private static final String PASSWORD = "wtdpf";
    //数据库连接
    private static Connection conn = null;
    //SQL语句对象
    private static Statement stmt = null;
 //空的构造方法
 public Clobnono(){}
 //建立测试用表格
    public static void createTables() throws Exception {
     try {
      stmt.executeUpdate("CREATE TABLE TEST_CLOB ( ID NUMBER(3), CLOBCOL CLOB)");
      //stmt.executeUpdate("CREATE TABLE TEST_BLOB ( ID NUMBER(3), BLOBCOL BLOB)");
       } catch (Exception ex) {}
     }


 /**
  *  * 描述:查出所有大字段的值
  * 沙振华
  * 2008-9-26
  * @param getclobpre 查询语句
  * @param clob  大字段clob的列名
  * @throws Exception
  */ 
 public static String getClobPre(String getclobpre,String clob) throws Exception{
     String clobbig="";
  PreparedStatement pstm = null;//预处理语句,用于查询数据
        ResultSet rs = null;
        pstm = conn.prepareStatement(getclobpre);
        rs = pstm.executeQuery();
        while(rs.next()){
        clobbig=getClobString(rs.getClob(clob));
            }
  return clobbig;
 }
    /*
     * 往数据库中插入一个新的CLOB对象
     * insertSQL  插入空clob占位
     * updateSQL  把空值修改
     * bigString  大字段clob数据
     * updateColumn  大字段clob列名
     * */
   public static void clobInsert(String insertSQL,String updateSQL,String bigString,String updateColumn) throws Exception {
    /* 设定不自动提交 */
      boolean defaultCommit = conn.getAutoCommit();
      conn.setAutoCommit(false);
      try {
       /* 插入一个空的CLOB对象 */
       stmt.executeUpdate(insertSQL);
       /* 查询此CLOB对象并锁定 */
       ResultSet rs = stmt.executeQuery(updateSQL);
       while (rs.next()) {
        //* 取出此CLOB对象
        oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob(updateColumn);//updateColumn是要处理的大字段名
        /* 向CLOB对象中写入数据 */
        BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
        BufferedReader in = new BufferedReader(new StringReader (bigString));//bigString是要处理的大字段值
        int c;
        while ((c=in.read())!=-1) {
         out.write(c);
        }
        in.close();
        out.close();
       }
       /* 正式提交 */
       conn.commit();
      } catch (Exception ex) {
       /* 出错回滚 */
       conn.rollback();
       throw ex;
      }
      //恢复原提交状态
      conn.setAutoCommit(defaultCommit);
   }
     //读取大字段clob
   public static String getClobString(Clob c) { 
    try {
     Reader reader=c.getCharacterStream();
     if (reader == null) {
      return null;
     }
     StringBuffer sb = new StringBuffer();
     char[] charbuf = new char[4096];
     for (int i = reader.read(charbuf); i > 0; i = reader.read(charbuf)) {
      sb.append(charbuf, 0, i);
     }
     return sb.toString();
    } catch (Exception e) {
     return "";
    }
   }
   //main方法
   public static void main(String[] args) throws Exception{
  /* 装载驱动,建立数据库连接 */
  Class.forName(DRIVER);
  conn = DriverManager.getConnection(URL,USER,PASSWORD);
  stmt = conn.createStatement();
  /* 建立测试表格 */
  createTables();
  /* CLOB对象插入 */
  clobInsert("insert into TEST_CLOB  (id,CLOBCOL) values ('789', empty_clob())", "select * from TEST_CLOB where id='789' for update", "大发发方法多发发生", "CLOBCOL");
  /* CLOB对象查询 */
   String clobpre=getClobPre("select * from TEST_CLOB where id='789' ","CLOBCOL");
         System.out.println("**2333332@@@*"+clobpre);
        /* CLOB对象修改 */
         clobUpdate("select * from TEST_CLOB where id='789' for update","小忒啊对方答复的沙发啊", "CLOBCOL" );
        /* CLOB对象修改后查询 */
         String clobuuuppp=getClobPre("select * from TEST_CLOB where id='789' ","CLOBCOL");
         System.out.println("**233333######32@@@*"+clobuuuppp);
   
  }
  /**
   *
   * 描述:
   * 沙振华
   * 2008-9-26
   * @param bigstring
   * @param updatesql
   * @param updatecolumn
   * @throws Exception
   */
   public static void clobUpdate( String updatesql,String bigstring, String updatecolumn) throws Exception {
         
       /* 设定不自动提交 */
       boolean defaultCommit = conn.getAutoCommit();
       conn.setAutoCommit(false);
       try {
           /*
            * 查询CLOB对象并锁定 SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR
            * UPDATE
            */
           ResultSet rs = stmt.executeQuery(updatesql);
           while (rs.next()) {
               /*
                * 获取此CLOB对象 CLOBCOL
                */
               oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob(updatecolumn);
               /* 进行覆盖式修改 */
               BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
               BufferedReader in = new BufferedReader(new StringReader (bigstring));
               int c;
               while ((c = in.read()) != -1) {
                   out.write(c);
               }
               in.close();
               out.close();
           }
           /* 正式提交 */
           conn.commit();
       }
       catch (Exception ex) {
           /* 出错回滚 */
           conn.rollback();
           throw ex;
       }
       /* 恢复原提交状态 */
       conn.setAutoCommit(defaultCommit);
   }
  
}  

0 0

相关博文

我的热门文章

img
取 消
img