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

Z时代
2024-01-10
分类：综合
java
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(大字符串格式)内容
  */
 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;
 }
 /*   执行 更新删除等语句
  @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;
  }
 }
}