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

首页 > 代码库 > Java 对Oracle Clob（大字符串）格式的操作增改查

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

2024-08-08 14:40:47 216人阅读

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="http://www.mamicode.com/+ 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（大字符串）格式的操作增改查

声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉：投诉/举报工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。

联系
我们

首页 > 代码库 > Java 对Oracle Clob（大字符串）格式的操作 增改查

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

看完仍有疑问？有类似问题直接问程序猿

首页 > 代码库 > Java 对Oracle Clob（大字符串）格式的操作增改查

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