db.java

hibernate+spring的相片上传项目

      clean();
    }
  }

  /**
   * 执行SQL语句,并将相关信息写入到session中去
   * @param sql
   * @param session
   * @param url
   * @throws SQLException
   */
  public void execute(String sql, String yhdh, String yhlx, String ip,
                      String url) throws SQLException {
    try {
      OpenDbBean db = new OpenDbBean();
      conn = db.getConnection();
      ps = conn.createStatement();
      conn.setAutoCommit(false);
      ps.execute(sql);
      /**
       * 如果session不为空则进行记录
       */
      yhdh = StringUtil.escapeSQLTags(yhdh);
      yhlx = StringUtil.escapeSQLTags(yhlx);
      if (yhdh.length() > 0 && yhlx.length() > 0) {
        ip = StringUtil.escapeSQLTags(ip);
        url = StringUtil.escapeSQLTags(url);
        String logsql =
            "insert into nt_xtrz(xtrzxh,yhdh,czsj,yhlx,url,yjsql,ip) values(s_xtrzxh.nextval,'" +
            yhdh + "',sysdate,'" + yhlx + "','" + url + "','" +
            StringUtil.escapeSQLTags(sql) + "','" + ip + "')";
        ps.execute(logsql);

      }
      /**
       * 处理非法执行情况，主要用于调试程序,正常使用中将不会至此分支
       */
      else {

      }
      ps.close();
      conn.commit();
      //num++;
    }
    catch (SQLException e) {
      conn.rollback();
      throw new SQLException(e.getMessage() + "出错的sql为:" + sql);
    }
    finally {
      clean();
    }

  }

  /**
   *取得指定表内指定字段条件的CLOB字段信息
   * @param Table 表名称
   * @param ZD  字段名
   * @param Idname  表索引ID号
   * @param Id  指定的ID号
   * @return 返回字符型的CLOB字段信息
   * @throws java.lang.Exception
   */
  public String getClobField(String Table, String ZD, String Idname, String Id) throws
      java.sql.SQLException,Exception {
    String memo;
    long longLen;
    OpenDbBean db = new OpenDbBean();
    conn = db.getConnection();
    conn.setAutoCommit(false);
    String sql = "select * from " + Table + " where " + Idname + "=" + Id;
    ps = conn.createStatement();
    try {
      rs = ps.executeQuery(sql);
      if (rs.next()) {
        oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob(ZD);
        if (clob != null) {
          longLen = clob.length();
          memo = clob.getSubString(1L, (int) longLen);
          memo = StringUtil.replace(memo, "<br>", "\n");
          memo = StringUtil.replace(memo, "&nbsp;", " ");
        }
        else {
          memo = "";
        }
      }
      else {
        memo = "";
      }
      rs.close();
      conn.setAutoCommit(true);
      return memo;
    }
    catch (SQLException e) {
      System.out.println("getClobField方法出错(SQLException):"+e);
      return "";
    }
    catch(Exception ex){
        System.out.println("getClobField方法出错(Exception):"+ex);
        return "";
    }
    finally {
      clean();
    }
  }

  /**
   *取得指定表内指定字段条件的CLOB字段信息 blog专用
   * @param Table 表名称
   * @param ZD  字段名
   * @param Idname  表索引ID号
   * @param Id  指定的ID号
   * @return 返回字符型的CLOB字段信息
   * @throws java.lang.Exception
   */
  public String getClobField_blog(String Table, String ZD, String Idname, String Id) throws
      java.sql.SQLException,Exception {
    String memo;
    long longLen;
    OpenDbBean db = new OpenDbBean();
    conn = db.getConnection();
    conn.setAutoCommit(false);
    String sql = "select * from " + Table + " where " + Idname + "=" + Id;
    ps = conn.createStatement();
    try {
      rs = ps.executeQuery(sql);
      if (rs.next()) {
        oracle.sql.CLOB clob = (CLOB) rs.getClob(ZD);
        if (clob != null) {
          longLen = clob.length();
          memo = clob.getSubString(1L, (int) longLen);
          //memo = StringUtil.replace(memo, "<br>", "\n");
          memo = StringUtil.replace(memo, "&nbsp;", " ");
        }
        else {
          memo = "";
        }
      }
      else {
        memo = "";
      }
      rs.close();
      conn.setAutoCommit(true);
      return memo;
    }
    catch (SQLException e) {
      System.out.println("getClobField方法出错(SQLException)blog专用:"+e);
      return "";
    }
    catch(Exception ex){
        System.out.println("getClobField方法出错(Exception)blog专用:"+ex);
        return "";
    }
    finally {
      clean();
    }
  }

  /**
   *向user_article表插入clob字段信息
   * @param Table 表名称
   * @param ZD  字段名
   * @param Idname  表索引ID号
   * @param Id  指定的ID号
   *
   * @return 返回字符型的CLOB字段信息
   * @throws java.lang.Exception
   */
  public int insertClobFieldtt(String memo,String insertsql,
                              String mxid) throws java.sql.SQLException,
      java.io.IOException {

    try {
      OpenDbBean db = new OpenDbBean();
      conn = db.getConnection();
      conn.setAutoCommit(false);
      Statement stmt = conn.createStatement();
      stmt.executeUpdate(insertsql);
      //先将非CLOB的字段写入行
      String sql = "select * from user_article where id=" + mxid +
          " for update";
      //再次找开行插入的那行
      rs = stmt.executeQuery(sql);
      if (rs.next()) {
        oracle.sql.CLOB clob1 = (oracle.sql.CLOB) rs.getClob("MEMO");
        //将MEMO字段取出 放入clob1变量里
        String clobContent = StringUtil.toGb(StringUtil.notNull(memo));
        //将页面转过来的MEMO 放入 clobContent 变量
        Writer wr = clob1.getCharacterOutputStream();
        //建立输出到clob1的流柄 wr
        wr.write(clobContent);
        //将clobContent 变量 写入wr柄内
        wr.flush();
        wr.close();
        //关闭wr输出流

        conn.commit();
        //提交
        conn.setAutoCommit(true);
        //打开自动提交
        db.CleanConnection(conn);
        return 1;
      }else{
        return 0;
      }
    }
    catch(SQLException e){
      System.out.println("写入CLOB字段时出现SQL错误："+e);
      return 0;
    }
    catch (IOException ex) {
      System.out.println("写入CLOB字段时出现IO错误："+ex);
      return 0;
    }
  }

  /**
   *取得user_article表中指定ID号的CLOB字段信息
   * @param Id  指定的ID号
   * @return 返回字符型的CLOB字段信息
   * @throws java.lang.Exception
   */
  public String getClobFieldtt(String Id) throws
      java.sql.SQLException {
    String memo;

    long longLen;
    OpenDbBean db = new OpenDbBean();
    conn = db.getConnection();
    conn.setAutoCommit(false);


    String sql = "select memo from user_article where id=" + Id;
    ps = conn.createStatement();

    try {
      rs = ps.executeQuery(sql);

      if (rs.next()) {

        oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("memo");

        if (clob != null) {
          longLen = clob.length();
          memo = clob.getSubString(1L, (int) longLen);

        }
        else {
          memo = "";

        }
      }
      else {
        memo = "";

      }

      rs.close();

      conn.setAutoCommit(true);

      return memo;
    }
    catch (SQLException e) {
      System.out.println("getClobFieldtt方法出错："+e);
      return "";
    }
  }

  /**
   *取得指定表内指定字段条件的CLOB字段信息[根据两个条件定位到具体行]
   * @param Table 表名称
   * @param ZD  字段名
   * @param Idname  表索引ID号
   * @param Id  指定的ID号
   * @return 返回字符型的CLOB字段信息
   * @throws java.lang.Exception
   */
  public String getClobField2(String Table, String ZD, String Idname, String Id,String Idname2,String Id2) throws
      java.sql.SQLException {
    String memo;
    long longLen;
    OpenDbBean db = new OpenDbBean();
    conn = db.getConnection();
    conn.setAutoCommit(false);
    String sql = "select * from " + Table + " where " + Idname + "=" + Id+" and "+Idname2+"="+Id2 ;
    ps = conn.createStatement();
    try {
      rs = ps.executeQuery(sql);
      if (rs.next()) {
        oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob(ZD);
        if (clob != null) {
          longLen = clob.length();
          memo = clob.getSubString(1L, (int) longLen);
          memo = StringUtil.replace(memo, "<br>", "\n");
          memo = StringUtil.replace(memo, "&nbsp;", " ");
        }
        else {
          memo = "";
        }
      }
      else {
        memo = "";
      }
      rs.close();
      conn.setAutoCommit(true);
      return memo;
    }
    catch (SQLException e) {
      System.out.println("getClobField2方法出错："+e);
      return "";
    }
    finally {
      ps.close();
      db.CleanConnection(conn);
      clean();
    }
  }


  /**
   *向指定表插入clob字段信息
   * @param Table 表名称
   * @param ZD  字段名
   * @param Idname  表索引ID号
   * @param Id  指定的ID号
   *
   * @return 返回字符型的CLOB字段信息
   * @throws java.lang.Exception
   */
  public void insertClobField(String Table, String memo, String zd,String insertsql,
                              String mxid) throws java.sql.SQLException,
      java.io.IOException {

    try {
      OpenDbBean db = new OpenDbBean();
      conn = db.getConnection();
      conn.setAutoCommit(false);
      Statement stmt = conn.createStatement();
      stmt.executeUpdate(insertsql);
      //先将非CLOB的字段写入行
      String sql = "select * from " + Table + " where "+zd+"=" + mxid +
          " for update";
      //再次找开行插入的那行
      rs = stmt.executeQuery(sql);
      if (rs.next()) {
        oracle.sql.CLOB clob1 = (oracle.sql.CLOB) rs.getClob("memo");
        //将MEMO字段取出 放入clob1变量里
        String clobContent = StringUtil.toGb(StringUtil.notNull(memo));
        //将页面转过来的MEMO 放入 clobContent 变量
        clobContent = StringUtil.replace(clobContent, "\n", "<br>");
        clobContent = StringUtil.replace(clobContent, " ", "&nbsp;");
        Writer wr = clob1.getCharacterOutputStream();
        //建立输出到clob1的流柄 wr
        wr.write(clobContent);
        //将clobContent 变量 写入wr柄内
        wr.flush();
        wr.close();
        //关闭wr输出流

        conn.commit();
        //提交
        conn.setAutoCommit(true);
        //打开自动提交
        db.CleanConnection(conn);
      }
    }
    catch(SQLException e){
      System.out.println("写入CLOB字段时出现SQL错误："+e);
    }
    catch (IOException ex) {
      System.out.println("写入CLOB字段时出现IO错误："+ex);
    }
  }

  /**
 *向指定表插入clob字段信息 使用utf-8编码 BLOG专用
 * @param Table 表名称
 * @param ZD  字段名
 * @param Idname  表索引ID号
 * @param Id  指定的ID号
 *
 * @return 返回字符型的CLOB字段信息
 * @throws java.lang.Exception
 */
public void insertClobFieldB(String Table, String memo, String zd,String insertsql,
                            String mxid) throws java.sql.SQLException,
    java.io.IOException {

  try {
    OpenDbBean db = new OpenDbBean();
    conn = db.getConnection();
    conn.setAutoCommit(false);
    Statement stmt = conn.createStatement();
    stmt.executeUpdate(insertsql);
    //先将非CLOB的字段写入行
    String sql = "select * from " + Table + " where "+zd+"=" + mxid +
        " for update";
    //再次找开行插入的那行
    rs = stmt.executeQuery(sql);
    if (rs.next()) {
      oracle.sql.CLOB clob1 = (oracle.sql.CLOB) rs.getClob("memo");
      //将MEMO字段取出 放入clob1变量里
      String clobContent = StringUtil.toUtf8(StringUtil.notNull(memo));
      //将页面转过来的MEMO 放入 clobContent 变量
      clobContent = StringUtil.replace(clobContent, "\n", "<br>");
      //clobContent = StringUtil.replace(clobContent, " ", "&nbsp;");
      Writer wr = clob1.getCharacterOutputStream();
      //建立输出到clob1的流柄 wr
      wr.write(clobContent);
      //将clobContent 变量 写入wr柄内
      wr.flush();
      wr.close();
      //关闭wr输出流

      conn.commit();
      //提交
      conn.setAutoCommit(true);
      //打开自动提交
      db.CleanConnection(conn);
    }
  }
  catch(SQLException e){
    System.out.println("写入CLOB字段时出现SQL错误："+e);
  }
  catch (IOException ex) {
    System.out.println("写入CLOB字段时出现IO错误："+ex);
  }
}




  public void release() throws SQLException{
    this.clean();
    System.out.println("释放!");
  }

}