Java 对Oracle Clob（大字符串）格式的操作增改查详解数据库

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

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(),  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获得大字符串格式

 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;

 }

//大字符串格式转换STRING

 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

 }

//更新

 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;

 }

//要执行的查询语句

 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;

 }

//执行 更新删除等语句

 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;

  }

 }

}

转载请注明来源网站:blog.ytso.com谢谢！

4536.html