用JDBC写的CLOB存取类

表CLOB_TEST

ID NUMBER;

CONTENT CLOB;

 

import java.io.BufferedReader;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.sql.CLOB;

public class JDBCTest {

 /**
  * @param args
  */
 public static void main(String[] args) {
  insert();
  query();
  
 }
 
 public static void insert(){
  try {
   String driver = "oracle.jdbc.driver.OracleDriver";
   String url = "jdbc:oracle:thin:@192.168.1.253:1521:alphadb";
   String username = "alpha";
   String password = "alpha2012";
   
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   String sql = "insert into CLOB_TEST VALUES(?, empty_clob())";
   
    //禁用自动提交事务  
            conn.setAutoCommit(false);  
   PreparedStatement pstmt = conn.prepareStatement(sql);
   pstmt.setInt(1, 3);
   int ret = pstmt.executeUpdate();
   System.out.println(ret);
   pstmt.close();
   
   //查询并获得这个cursor,并且加锁  
            sql="SELECT CONTENT FROM CLOB_TEST WHERE ID=? for update";  
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, 3);
   ResultSet rs = pstmt.executeQuery();
   CLOB clob = null;
   while(rs.next()) {
//    System.out.println(rs.getInt(1));
    clob = (CLOB)rs.getClob(1);
   }
   
   PrintWriter out = new PrintWriter(clob.getCharacterOutputStream());
   out.println("HI CLOB");
   out.flush();
   out.close();
   
   pstmt.close();
   
   
   sql = "UPDATE CLOB_TEST SET CONTENT = ? WHERE ID = ?";
   pstmt = conn.prepareStatement(sql);
   pstmt.setClob(1, clob);
   pstmt.setInt(2, 2);
   ret = pstmt.executeUpdate();
   System.out.println(ret);
   conn.commit();
   pstmt.close();
   
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 
 public static void query(){
  try {
   String driver = "oracle.jdbc.driver.OracleDriver";
   String url = "jdbc:oracle:thin:@192.168.1.253:1521:alphadb";
   String username = "alpha";
   String password = "alpha2012";
   
   Class.forName(driver);
   Connection conn = DriverManager.getConnection(url, username, password);
   String sql = "SELECT * FROM CLOB_TEST WHERE ID = ?";
   
   PreparedStatement pstmt = conn.prepareStatement(sql);
   pstmt.setInt(1, 3);
   ResultSet rs = pstmt.executeQuery();
   CLOB clob = null;
   while(rs.next()) {
    System.out.println(rs.getInt("ID"));
    clob = (CLOB)rs.getClob("CONTENT");
   }
   BufferedReader in = new BufferedReader(clob.getCharacterStream());
   String line = "";
   while((line = in.readLine()) != null){
    System.out.println(line);
   }
   rs.close();
   pstmt.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();
  }
 }
}