Java Operation for Oracle Clob (large string) format modified

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;

/*
* Classes that connect databases and common database operations
*/
Import Oracle.sql.CLOB;

/**
* Classes that connect to the database
* The most basic class
*
*/
public class Dboracle {

/**
* Connection to database Common properties
*
*
*/
Public String sdbdriver = "oracle.jdbc.driver.OracleDriver";//Set Driver
Public String url = "JDBC:ORACLE:THIN:@192.168.1.0:1521:HPO";//Set Database information
Public String shint = "";
public ResultSet rs = null;
public Connection conn = null;
Public Statement stmt = null;
Public String user = "study";
Public String pwd = "OK";

/**
* Load profile Read information
*
*
*/
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 ("address =" + dbconfig);
System.out.println ("real =" +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 () {//Default construction method
  try {
   class.forname (sdbdriver);
   shint = "Initialization sucessfully";
   return true;
  } catch (ClassNotFoundException e) {
   shint = "Initialization Exception:" + E.getmessage ();
   return false;
  }
 }

/**
* Close Database objects
*
*
*/
public Boolean Close () {//Close database connection
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;
}
}

/**
* Insert large-font field method
*
*
*/
Public String insertclob (int userid, int courseware_id, int Progress,
String Courseclob) {//Insert Database Clob field
try {
int testid = 77;
SYSTEM.OUT.PRINTLN ("113 User ID" + userid + "Courseware ID" + courseware_id+
"Progress" + Progress + "value=" + Courseclob);
Class.forName (This.sdbdriver);
Connection conn = drivermanager.getconnection (This.url, This.user,
THIS.PWD);
Conn.setautocommit (FALSE);
/* The CLOB type of the report field in the following table User_courseware */
Insert a piece of data, note the Clob field, you need to first insert an empty CLOB type Empty_clob (), and then update the CLOB field separately
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 ("User Id" + userid + "Insert" + courseware_id+
"Courseware Success");
}
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";

}

 /**
  * Get large fields of XML
  * Get big string format
  * 
  * @param user_id
  * & nbsp;          User ID
  * @param courseware_id
  *             Courseware ID
  * @return Large string
  * 
  */
 public String getcourseclob (int user_id, int courseware_id) {//Query course ID based on courseware ID and Person 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
  * Large string format conversion string
  * @param clob
  * @return Large string
&nb Sp *&NBSP
  */
 public string clob2string (CLOB CLOB) {//CLOB Convert to String method
  string Content = NULL;
  stringbuffer stringbuf = new StringBuffer ();
  try {
   int length = 0;
   reader instream = Clob.getcharacterstream (); Gets the large font side segment object data output stream
   char[] buffer = new CHAR[10];
   while (length = instream.read (buffer))! =-1)//Read database//10 10 reads
   {
     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;
}

/**
*
* Update CLOB (large string format) content
* @param userid
* User ID
* @param courseware_id
* Courseware ID
* @param Progress
* Courseware Progress
* @param Courseclob
* XML String
* @return Boolean
*
*/

Public String updateclob (int userid, int courseware_id, int Progress,
String Courseclob) {
This.updateuser_course (userid, courseware_id, Progress);//Call Update 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";
}

 /*
  * Parameters: UserID User ID courseware_id Courseware ID Progress Courseware Progress return value courseware progress less than 100 when not updated to 100 when
  * Update status is 2 (learned)
  */
 public String updateuser_course (int userid, int courseware_id, int Progress) {
   String casetest = "Default";
  if (Progress = =) {
   string sql = "Update user_course set  Status =2 where use r_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 ("Update succeeded!");
}
Casetest = "Success";

} catch (Exception e) {
Casetest = "Error";
E.printstacktrace ();

}

}

return casetest;
}

/**
*
* @param sql
* Query statement to execute
* @return Result set
*/
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;
}

/**
* Perform update deletion and other statements
*
* @param sql
* @return Returns the boolean value of the execution result
*/
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 Operation for Oracle Clob (large string) format modified