Java 對Oracle Clob（大字串）格式的操作 增改查

標籤：ar   io   os   sp   for   java   on   檔案   資料   

package com.study.db;

import java.io.FileInputStream;

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 java.sql.Statement;
import java.util.Properties;

/*
 * 串連資料庫和常用資料庫操作的類
 */
import oracle.sql.CLOB;

/**
 * 串連資料庫的類
 * 最基礎的類
 * 
 */
public class DBOracle {

 /**
  * 串連資料庫常用的屬性
  * 
  * 
  */
 public String sDBDriver = "oracle.jdbc.driver.OracleDriver";// 設定驅動
 public String url = "jdbc:oracle:thin:@192.168.1.0:1521:HPO";// 設定資料庫資訊
 public String sHint = "";
 public ResultSet rs = null;
 public Connection conn = null;
 public Statement stmt = null;
 public String user = "study";
 public String pwd = "ok";

 /**
  * 載入設定檔讀取資訊
  * 
  * 
  */
 public DBOracle() throws IOException {

  String userpath = System.getProperty("user.dir");
  String filesparator = System.getProperty("file.separator");
  String dbconfig = userpath + filesparator + "dbconfig.properties";
  FileInputStream in;
  in = new FileInputStream(dbconfig);
  Properties dbp = new Properties();
  dbp.load(in);
  in.close();
  this.url = dbp.getProperty("URL");
  this.user = dbp.getProperty("USER");
  this.pwd = dbp.getProperty("PASSWORD");
  System.out.println("地址=" + dbconfig);
  // System.out.println("真實="+this.url);

 }

 public String getSHint() {
  return sHint;
 }

 public void setSDBDriver(String dbDriver) {
  sDBDriver = dbDriver;
 }

 public String getSDBDriver() {
  return sDBDriver;
 }

 public String getUrl() {
  return url;
 }

 public void setUrl(String s) {
  url = s;
 }

 public ResultSet getResultSet() {
  return rs;
 }

 public boolean initialize(String url, String user, String pwd) {
  this.url = url;
  this.user = user;
  this.pwd = pwd;
  return initialize();
 }

 public boolean initialize() {// 預設構造方法
  try {
   Class.forName(sDBDriver);
   sHint = "Initialization sucessfully";
   return true;
  } catch (ClassNotFoundException e) {
   sHint = "Initialization Exception:" + e.getMessage();
   return false;
  }
 }

 /**
  * 關閉資料庫物件
  * 
  * 
  */
 public boolean close() {// 關閉資料庫連接
  try {
   if (rs != null)
   { rs.close();}
   if (stmt != null)
    {stmt.close();}
   if (conn != null)
    {conn.close();}
   return true;
  } catch (SQLException ex) {
   sHint = "Close Exception:" + ex.getMessage();
   return false;
  }
 }

 /**
  * 插入大欄位方法
  * 
  * 
  */
 public String insertClob(int userid, int courseware_Id, int Progress,
   String CourseClob) {// 插入資料庫clob欄位
  try {
   // int testid = 77;
   // System.out.println("113使用者ID" + userid + "課件ID" + courseware_Id+
   // "進度" + Progress + "value=" + CourseClob);
   Class.forName(this.sDBDriver);
   Connection conn = DriverManager.getConnection(this.url, this.user,
     this.pwd);
   conn.setAutoCommit(false);
   /* 以下表User_CourseWare中的Report欄位時CLOB類型的 */
   // 插入一條資料，注意CLOB欄位，需要先插入一個空的clob類型 empty_clob()，然後再單獨更新clob欄位
   // String sql = "insert into
   // User_CourseWare(User_Id,Courseware_Id,Progress,Report,Id)values(
   // ?,?,?,empty_clob(),?)";
   String sql = "insert into User_CourseWare(User_Id,Courseware_Id,Progress,Report ,id)values( ?,?,?,empty_clob(),  user_courseware_sq.nextval  )";
   PreparedStatement pstmt = conn.prepareStatement(sql);
   pstmt.setInt(1, userid);
   pstmt.setInt(2, courseware_Id);
   pstmt.setInt(3, Progress);
   // System.out.println("sql insert=" + sql);
   // pstmt.setInt(4, testid);
   int i1 = pstmt.executeUpdate();
   conn.commit();
   pstmt = null;
   if (i1 > 0) {
    // System.out.println("使用者ID" + userid + "插入" + courseware_Id+
    // "課件成功");
   }
   ResultSet rs = null;
   CLOB clob = null;
   String sql1 = "select Report from User_CourseWare where  User_Id=? and Courseware_Id=? for update";
   pstmt = conn.prepareStatement(sql1);
   /*
    * pstmt.setInt(1, testid); pstmt.setInt(2, userid); pstmt.setInt(3,
    * courseware_Id);
    */
   // System.out.println("sql1 select=" + sql1);
   pstmt.setInt(1, userid);
   pstmt.setInt(2, courseware_Id);

   rs = pstmt.executeQuery();
   if (rs.next()) {
    clob = (CLOB) rs.getClob(1);
   }
   Writer writer = clob.getCharacterOutputStream();
   writer.write(CourseClob);
   writer.flush();
   writer.close();
   rs.close();
   conn.commit();
   pstmt.close();
   conn.close();

  } catch (Exception e) {

   e.printStackTrace();
   return "error";
  }
  return "success";

 }

 /**
  * 獲得大欄位XML
  * 獲得大字串格式
  * 
  * @param user_id
  *            使用者ID
  * @param courseware_id
  *            課件ID
  * @return 大字串
  * 
  */
 public String getCourseClob(int user_id, int courseware_id) {// 根據課件ID和人ID查詢課程ID
  String content = "null";
  try {
   Class.forName(this.sDBDriver);
   Connection conn = DriverManager.getConnection(this.url, this.user,
     this.pwd);
   conn.setAutoCommit(false);
   ResultSet rs = null;
   CLOB clob = null;
   String sql = "";
   sql = "select Report from User_CourseWare  where user_id=? and courseware_id=?  ";

   PreparedStatement pstmt = conn.prepareStatement(sql);
   pstmt.setInt(1, user_id);
   pstmt.setInt(2, courseware_id);
   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());
     content = this.Clob2String(clob);
    }

   }

   rs.close();
   conn.commit();
   pstmt.close();
   conn.close();

  } catch (ClassNotFoundException e) {

   e.printStackTrace();
   // return "null";
   content = "error";
  } catch (SQLException e) {
   e.printStackTrace();
   // return "null";
   content = "error";
  }
  return content;
 }

 /**
  * clob to string
  * 大字串格式轉換STRING
  * @param clob
  * @return 大字串
  * 
  */
 public String Clob2String(CLOB clob) {// Clob轉換成String 的方法
  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;
 }

 /**
  * 
  * 更新Clob(大字串格式)內容
  * @param userid
  *            使用者ID
  * @param courseware_Id
  *            課件ID
  * @param Progress
  *            課件進度
  * @param CourseClob
  *            XML 字串
  * @return boolean
  * 
  */

 public String updateClob(int userid, int courseware_Id, int Progress,
   String CourseClob) {
  this.updateUser_Course(userid, courseware_Id, Progress);// 調用更新進度
  try {
   Class.forName(this.sDBDriver);
   Connection conn = DriverManager.getConnection(this.url, this.user,
     this.pwd);
   String sql = "update User_CourseWare set  Report=empty_clob() , Progress=? where User_Id = ? and  Courseware_Id =? ";
   PreparedStatement pstmt = conn.prepareStatement(sql);
   pstmt.setInt(1, Progress);
   pstmt.setInt(2, userid);
   pstmt.setInt(3, courseware_Id);
   int i1 = pstmt.executeUpdate();
   conn.commit();
   // System.out.println("update sql="+sql);
   pstmt = null;
   if (i1 > 0) {

   }

   ResultSet rs = null;
   CLOB clob = null;
   String sql1 = "select Report from User_CourseWare where User_id=? and Courseware_id=?  for update";
   pstmt = conn.prepareStatement(sql1);
   // System.out.println("select sql="+sql1);
   pstmt.setInt(1, userid);
   pstmt.setInt(2, courseware_Id);
   rs = pstmt.executeQuery();
   if (rs.next()) {
    clob = (CLOB) rs.getClob(1);
   }

   Writer writer = clob.getCharacterOutputStream();
   writer.write(CourseClob);
   writer.flush();
   writer.close();
   rs.close();
   conn.commit();
   pstmt.close();
   conn.close();

  } catch (Exception e) {
   e.printStackTrace();
   return "error";
  }
  return "success";
 }

 /*
  * 參數：userid 使用者ID courseware_Id 課件ID Progress 課件進度 傳回值 課件進度不到100時不更新 到100時
  * 更新 狀態為2（已學）
  */
 public String updateUser_Course(int userid, int courseware_Id, int Progress) {
  String caseTest = "default";
  if (Progress == 100) {
   String sql = "update User_Course set  Status =2 where USER_ID =? and COURSE_ID =?   ";
   try {
    Class.forName(this.sDBDriver);
    Connection conn = DriverManager.getConnection(this.url,
      this.user, this.pwd);
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setInt(1, userid);
    pstmt.setInt(2, courseware_Id);

    int i1 = pstmt.executeUpdate();
    conn.commit();
    pstmt = null;
    if (i1 > 0) {
     // System.out.println("更新成功!");
    }
    caseTest = "success";

   } catch (Exception e) {
    caseTest = "error";
    e.printStackTrace();

   }

  }

  return caseTest;
 }

 /**
  * 
  * @param sql
  *            要執行的查詢語句
  * @return 結果集
  */
 public ResultSet executeQuery(String sql) {
  rs = null;
  try {

   Class.forName(this.sDBDriver);

   conn = DriverManager.getConnection(this.url, this.user, this.pwd);

   stmt = conn.createStatement();
   rs = stmt.executeQuery(sql);
  } catch (Exception ex) {
   sHint = "Query Exception:" + ex.getMessage();
  }

  return rs;
 }

 /**
  * 執行 更新刪除等語句
  * 
  * @param sql
  * @return 返回執行結果的boolean值
  */
 public boolean executeUpdate(String sql) {
  try {
   Class.forName(this.sDBDriver);
   conn = DriverManager.getConnection(this.url, this.user, this.pwd);
   stmt = conn.createStatement();
   stmt.executeUpdate(sql);
   conn.commit();
   return true;
  } catch (Exception ex) {
   sHint = "Update Exception :" + ex.getMessage();
   return false;
  }
 }

}

Java 對Oracle Clob（大字串）格式的操作 增改查