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; } } }
