Add, delete, modify, and query of Oracle big text clob Data Types

Add, delete, modify, and query package common of Oracle big text clob data type; 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; www.2cto.com/** database connection and Common Database Operations */import oracle. SQL. CLOB; /*** Database connection class * Basic class **/public class text {/*** common database connection attributes ***/public String sDBDriver = "oracle. jdbc. driver. oracleDriver "; // set the driver public String url =" jdbc: oracle: thin: @ localhost: spe "; // set the 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 the configuration file to read information * www.2c To.com **/public text () 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. getPrope Rty ("USER"); this. pwd = dbp. getProperty ("PASSWORD"); System. out. println ("address =" + dbconfig); // System. out. println ("true =" + 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 getResul TSet () {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 constructor try {Class. forName (sDBDriver); sHint = "Initialization sucessfully"; return true;} catch (ClassNotFoundException e) {sHint = "Initialization Exception:" + e. getMessage (); return false ;}}/*** close count Database Object * www.2cto.com **/public boolean close () {// close the 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 field method ***/public String insertClob (int userid, int courseware_Id, int Progress, String CourseClob) {// Insert the database clob field try {// int testid = 77; // System. out. println ("113 user ID" + userid + "courseware ID" + courseware_Id + // "Progress" + Progress + "value =" + CourseClob); Class. fo RName (this. sDBDriver); Connection conn = DriverManager. getConnection (this. url, this. user, this. pwd); conn. setAutoCommit (false);/* when the Report field in the following Table User_CourseWare is in the CLOB type * // insert a data record. Pay attention to the CLOB field, insert an empty clob-type empty_clob (), and then update the clob field // String SQL = "insert into // User_CourseWare (User_Id, Courseware_Id, Progress, Report, Id) separately) 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.exe cuteUpdate (); conn. commit (); www.2cto.com pstmt = null; if (i1> 0) {// System. out. println ("User ID" + userid + "insert" + courseware _ Id + // "courseware succeeded");} 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.exe cuteQuery (); if (rs. next () {clob = (CLOB) rs. getClob (1);} Writer writer = clob. getCharacterOutputStream (); writer. write (CourseClob); write R. flush (); writer. close (); rs. close (); conn. commit (); pstmt. close (); conn. close ();} catch (Exception e) {e. printStackTrace (); return "error";} return "success ";} /*** get big field XML * Get big String format ** @ param user_id * User ID * @ param courseware_id * courseware ID * @ return large String **/public String getCourseClob (int user_id, int courseware_id) {// query the course ID www.2cto.com String content = "null"; try {Class. f OrName (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.exe cuteQuery (); 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 (); www.2cto.com} 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 **/public String Clob2String (CLOB clob) {// Method for converting Clob to String STRING String content = null; stringBuffer stringBuf = new StringBuffer (); try {www.2cto.com int length = 0; Reader inStream = clob. getCharacterStream (); // get the object data output stream char [] buffer = new char [10]; while (length = inStream. read (buffer ))! =-1) // read the database // every 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 boo Lean * www.2cto.com */public String updateClob (int userid, int courseware_Id, int Progress, String CourseClob) {this. updateUser_Course (userid, courseware_Id, Progress); // call the 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.exe cuteUpdate (); conn. commit (); // System. out. println ("update SQL =" + SQL); pstmt = null; www.2cto.com 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.exe cuteQuery (); if (rs. next () {clob = (CLOB) rs. getClob (1);} www.2cto.com 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";} www.2cto.com/** parameters: userid user ID courseware_Id courseware ID Progress courseware Progress returned value courseware Progress less than 100 not updated to 100 * updated to 2 (learned) */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); www.2cto.com int i1 = pstmt.exe cuteUpdate (); conn. commit (); pstmt = null; if (i1> 0) {// System. out. println ("Update successful! ");} CaseTest =" success ";} catch (Exception e) {caseTest =" error "; e. printStackTrace () ;}} return caseTest;} www.2cto.com/***** @ param SQL * query statement to be executed * @ 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.exe cuteQuery (SQL);} catch (Exception ex) {sHint = "Query Exception:" + ex. getMessage () ;}www.2cto.com return rs ;} /*** run the update/delete statement ** @ param SQL * @ return: return 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.exe cuteUpdate (SQL); conn. commit (); return true; www.2cto.com} catch (Exception ex) {sHint = "Update Exception:" + ex. getMessage (); return false ;}}}