oracle10对clob的处理简单化了

在oracle10g中对于大字段clob的处理简化了,可以像varchar那样的setString(str)和getString("colum_name)来插入和取值了.

不需要再像9i 那样用流进写入读出了.

但是需要使用PreparedStatement ,(用Statement和话会添不了值,我测试没有通过)

处理clob的完整方法方法如下,

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OracleDriver;
import java.io.IOException;
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.util.Properties;


public class ClobManipulationIn10g {
    private Connection conn = null;
    private String url = null;
    private String user = null;
    private String password = null;
    private Properties props = new Properties();
    private String fileName = null;

    public ClobManipulationIn10g(String fileName) {
        this.fileName = fileName;
    }


    public static void main(String[] args) throws SQLException {

        ClobManipulationIn10g clobManipulationIn10g = new ClobManipulationIn10g("C://工作交流单[规].html");

        DriverManager.registerDriver(new OracleDriver());

        String dbUrl = "jdbc:oracle:thin:@localhost:1521:xe";
        clobManipulationIn10g.url = dbUrl;

        clobManipulationIn10g.user = "shanxi";

        clobManipulationIn10g.password = "shanxi";
    
        clobManipulationIn10g.props.put("user", clobManipulationIn10g.user);
        clobManipulationIn10g.props.put("password", clobManipulationIn10g.password);
        clobManipulationIn10g.props.put("SetBigStringTryClob", "true");

        clobManipulationIn10g.selectClob();

    }


    private void insertClob() throws SQLException {

        PreparedStatement pstmt = null;
        PreparedStatement ps2=null;
        try {

            if ((conn == null) || conn.isClosed()) {

                conn = DriverManager.getConnection(this.url, this.props);
            }
            String str = this.readFile();

            String sql="insert into tbs(tid,tname,tclob) values(1,'aaaaa',empty_clob())";
            String sql2="update tbs set tclob=?";
           pstmt = conn.prepareStatement(sql);
            pstmt.executeUpdate();
            
            pstmt = conn.prepareStatement(sql2);
           pstmt.setString(1, str);
            pstmt.executeUpdate();
    
            
        } catch (SQLException sqlex) {
      
            System.out.println("SQLException while connecting and inserting into " +
                    "the database table: " + sqlex.toString());
        } catch (Exception ex) {
            System.out.println("Exception while connecting and inserting into the" +
                    " database table: " + ex.toString());
        } finally {
     
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }
    
      private void updateClob() throws SQLException {

        PreparedStatement pstmt = null;
        try { 
            if ((conn == null) || conn.isClosed()) { 
                conn = DriverManager.getConnection(this.url, this.props);
            }          
            String str = this.readFile(); 
            String sql = "update tbs set tname='bbbb', tclob=? where tid=1";    
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, str);
   
            pstmt.executeUpdate();
        } catch (SQLException sqlex) {
      
            System.out.println("SQLException while connecting and inserting into " +
                    "the database table: " + sqlex.toString());
        } catch (Exception ex) {
            System.out.println("Exception while connecting and inserting into the" +
                    " database table: " + ex.toString());
        } finally {
      
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }
    
    private void selectClob() throws SQLException { 
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        try {
            if ((conn == null) || conn.isClosed()) {     
                conn = DriverManager.getConnection(this.url, this.props);
            }

            String sqlCall = "SELECT * FROM tbs";
            pstmt = conn.prepareStatement(sqlCall);

            rset = pstmt.executeQuery();
   
            while (rset.next()) {
              String idval=rset.getString("tid");
              String nameval=rset.getString("tname");
              String clobVal = rset.getString("tclob");
                System.out.println("CLOB length: " + clobVal.length());
                System.out.println(idval);
                System.out.println(nameval);
                System.out.println(clobVal);
            }
        } catch (SQLException sqlex) {
                  
            System.out.println("SQLException while connecting and querying the " +
                    "database table: " + sqlex.toString());
        } catch (Exception ex) {
            System.out.println("Exception while connecting and querying the " +
                    "database table: " + ex.toString());
        } finally {
           
            if (rset != null) {
                rset.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }

    private void checkTables() {
        Statement stmt = null;
        ResultSet rset = null;
        try {
         
            if ((conn == null) || conn.isClosed()) {
              
                conn = DriverManager.getConnection(this.url, this.props);
            }
         
            stmt = conn.createStatement();
            rset = stmt.executeQuery(" SELECT table_name FROM user_tables " +
                    " WHERE table_name = 'aaa' ");
           
            if (!rset.next()) {
            
                stmt.executeUpdate(" CREATE TABLE aaa(clob_col CLOB)");
            }
        } catch (SQLException sqlEx) {
            System.out.println("Could not create table aaa : " + sqlEx.toString());
        } finally {
            try {
                if (rset != null) {
                    rset.close();
                }
                if (stmt != null) {
                    stmt.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException ex) {
                System.out.println("Could not close objects in checkTables method : " + ex.toString());
            }
        }
    }
    private String readFile() throws FileNotFoundException, IOException {
        
        BufferedReader br = new BufferedReader(new FileReader(fileName));
        String nextLine = "";
        StringBuffer sb = new StringBuffer();
        while ((nextLine = br.readLine()) != null) {
            sb.append(nextLine);
        }
     
        String clobData = sb.toString();
    
        return clobData;
    }
}