db.java

hibernate+spring的相片上传项目

package org.lenovoAC.tools;

/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2005</p>
 * <p>Company: </p>
 * @author not attributable
 * @version 1.0
 */
import java.io.IOException;
import java.io.InputStream;
import java.io.Writer;
import java.sql.*;
import java.util.*;
import java.lang.*;
import oracle.sql.CLOB;
import sun.jdbc.rowset.CachedRowSet;

public class DB {
  private Connection conn = null;
  private Statement ps = null;
  private ResultSet rs = null;
  private PreparedStatement prs = null;
  //public static long num = 0; //统计数据库操作数量


  /**
   * 取得指定数据源的数据连接资源
   * @param odbc 要使用的数据源名称
   * @return  返回数据库连接
   * @throws java.lang.Exception
   */
  public Connection get_odbc_conn(String odbc) throws Exception{
    try{
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      Connection conn2 = DriverManager.getConnection("jdbc:odbc:"+odbc);
      return conn2;
    }
    catch(Exception e){
     throw e;
    }
  }

/*
  private void getConn() {
    try {
      if (conn == null) {
        conn = db.getConnection();
        //System.out.println("成功建立新的数据连接资源:" + conn);
      }
    }
    catch (Exception e) { //SQL
      System.out.println("执行SQL时出现建立新的数据连接时错误:" + e);
    }
  }
*/

//  private void init() { //throws SQLException
//    try {
//      if (conn == null) {
//        conn = db.getConnection();
//        //System.out.println("成功建立新的数据连接资源:" + conn);
//        ps = conn.createStatement();
//      }
//    }
//    catch (Exception e) { //SQL
//      System.out.println("执行SQL时出现建立新的数据连接时错误:" + e);
//    }
//  }

  /**
   *
   * @param sql
   * @return
   * @throws java.sql.SQLException
   */
  public CachedRowSet executeQuery(String sql) throws java.sql.SQLException{
    CachedRowSet cst = new CachedRowSet();
    try {
      OpenDbBean db = new OpenDbBean();
      conn = db.getConnection();
      ps = conn.createStatement();
      sql = escapeNull(sql);
      ps.setFetchSize(1000);
      rs = ps.executeQuery(sql);
      cst.populate(rs);
      //num++;
//      System.out.println(sql);
    }
    catch (SQLException e) {
        System.out.println("executeQuery方法出现SQLException错误:" + e + "出错SQL为：" + sql);
    }
    catch(Exception ex){
        System.out.println("executeQuery方法出现Exception错误:"+ex+"出错SQL为"+sql);
    }
    finally {
      this.clean();
    }
    return cst;
  }

  public ResultSet getRowSet(String sql) throws java.sql.SQLException {
    //CachedRowSet cst = new CachedRowSet();
    try {
      OpenDbBean db = new OpenDbBean();
      conn = db.getConnection();
      //getConn();
      sql = escapeNull(sql);
      //ps = conn.createStatement();
//      ps.setFetchSize(1000);
      rs = ps.executeQuery(sql);
      //num++;
    }
    catch (SQLException e)
    {
      throw new SQLException("数据查询出错" + e.getMessage() + "出错sql为：" + sql);
    }
    finally {
      this.clean();
    }
    return rs;
  }

  /**
   *
   * @param sql
   * @throws SQLException
   */
  public void execute(String sql) throws SQLException {
    try {
      sql = escapeNull(sql);
      OpenDbBean db = new OpenDbBean();
      conn = db.getConnection();
      ps = conn.createStatement();
      ps.execute(sql);
      ps.close();
      //num++;

    }
    catch (SQLException e) {
      throw new SQLException(e.getMessage() + "出错的sql为:" + sql);
    }
    finally {
      clean();
    }
//    System.out.println("调用DB");
  }

  private String escapeNull(String str) {
    str = StringUtil.replace(str, "'null'", "''");
    str = StringUtil.replace(str, "'%null%'", "'%%'");
    return str;
  }

  /**
   *
   * @param tableName
   * @return
   */
  public static ArrayList getColumnName(String tableName) {
    Connection conn = null;
    ResultSet rs = null;
    OpenDbBean db = new OpenDbBean();
    Statement ps = null;
    ArrayList list = new ArrayList();
    try {
      conn = db.getConnection();
      ps = conn.createStatement();
      rs = ps.executeQuery("select * from " + tableName);
      for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
        list.add(rs.getMetaData().getColumnName(i + 1));
      }
    }
    catch (Exception e) {
      System.out.println(e.getMessage());
    }
    finally {
      try {
        db.CleanConnection(conn, ps, rs);
      }
      catch (SQLException e1) {
        e1.printStackTrace();
      }
    }
    return list;
  }

  public static ArrayList getQueryColumnName(String sql) {
    Connection conn = null;
    ResultSet rs = null;
    OpenDbBean db = new OpenDbBean();
    Statement ps = null;
    ArrayList list = new ArrayList();
    try {
      conn = db.getConnection();
      ps = conn.createStatement();
      rs = ps.executeQuery(sql);
      for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
        list.add(rs.getMetaData().getColumnName(i + 1));
      }
    }
    catch (Exception e) {
      System.out.println(e.getMessage());
    }
    finally {
      try {
        db.CleanConnection(conn, ps, rs);
      }
      catch (SQLException e1) {
        e1.printStackTrace();
      }
    }
    return list;
  }

  /**
   *
   * @param tableName
   * @return
   */
  public static ArrayList getColumnTypeName(String tableName) {
    Connection conn = null;
    ResultSet rs = null;
    OpenDbBean db = new OpenDbBean();
    Statement ps = null;
    ArrayList list = new ArrayList();
    try {
      conn = db.getConnection();
      ps = conn.createStatement();
      rs = ps.executeQuery("select * from " + tableName + " where rownum<=1");
      for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
        list.add(rs.getMetaData().getColumnTypeName(i + 1));
      }
    }
    catch (Exception e) {
      System.out.println(e.getMessage());
    }
    finally {
      try {
        db.CleanConnection(conn, ps, rs);
      }
      catch (SQLException e1) {
        e1.printStackTrace();
      }
    }
    return list;
  }

  /**
   *
   * @param type
   * @return
   */
  public static String convertType(String type) {
    if (type.equals("datetime")) {
      return "java.sql.Date";
    }
    else
    if (type.equals("int")) {
      return "int";
    }
    else {
      return "String";
    }
  }

  public PreparedStatement getPs(String sql) throws SQLException {
    OpenDbBean db = new OpenDbBean();
    conn = db.getConnection();
    //num++;
    return conn.prepareStatement(sql);
  }

  /**
   *
   * @Transaction
   *
   */
  public void startTransaction() throws SQLException {
    if (conn != null) {
      conn.setAutoCommit(false);
    }
  }

  public void commit() {
    try {
      if (conn != null) {
        conn.commit();
      }
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void rollback() {
    try {
      if (conn != null) {
        conn.rollback();
      }
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public void clean() throws SQLException{
   if (this.prs != null) {
      try {

        this.prs.close();
      }
      catch (SQLException e) {
        e.printStackTrace();
        System.out.println("close prs error");
      }
      finally {
        this.prs = null;
      }
    }

    if (this.rs != null) {
      try {

        this.rs.close();
      }
      catch (SQLException e) {
        e.printStackTrace();
        System.out.println("close rs error" + e.getMessage());
      }
      finally {
        this.rs = null;
      }
    }
    if (this.ps != null) {
      try {
        this.ps.close();
      }
      catch (SQLException e) {
//        e.printStackTrace();
        System.out.println("close ps error" + e.getMessage());
      }
      finally {
        this.ps = null;
      }
    }

    if (this.conn != null) {
      try {

        conn.close();
        //this.conn.close();
      }
      catch (SQLException e) {
    //    e.printStackTrace();
        System.out.println("close conn error" + e.getMessage());
      }
      finally {
        this.conn = null;
      }
    }

    //db.CleanConnection(conn);
  }

  public CachedRowSet executeSql(Connection conn1, String sql) throws
      SQLException {
    PreparedStatement pst = conn1.prepareStatement(sql);
    ResultSet rst = pst.executeQuery();
    CachedRowSet cst = new CachedRowSet();
    cst.populate(rst);
    return cst;

  }

  /**
   * 批量执行List中的SQL语句
   * @param batchSql
   * @throws SQLException
   */
  public void executeBatch(List batchSql) throws SQLException {
    String sql = "";
    try {
      OpenDbBean db = new OpenDbBean();
      conn = db.getConnection();
      conn.setAutoCommit(false);
      ps = conn.createStatement();
      for (int i = 0; i < batchSql.size(); i++) {
        sql = (String) batchSql.get(i);
        sql = escapeNull(sql);
//        System.out.println("批量SQL:" + sql);
        ps.execute(sql);
      }
      ps.close();
      conn.commit();
      //num++;
    }
    catch (SQLException e) {
      conn.rollback();
      throw new SQLException(e.getMessage() + "出错的sql为:" + sql);
    }
    finally {
      if (batchSql != null) {
        batchSql = null;
      }
      clean();
    }
  }

  /**
   * 取得指定数据表内最大的信息编号值
   * @param Table　指定的表名
   * @param Id　编号字段名
   * @return　返回当前的最大编号加１的值
   * @throws java.lang.Exception
   */
  public String getMax(String Table, String Id) throws SQLException {
    String osql = "select max(" + Id + ") as maxid from " + Table;
    OpenDbBean db = new OpenDbBean();
    conn = db.getConnection();
    ps = conn.createStatement();
    //getConn();
    String maxid = "";
    try {
      ResultSet ors = ps.executeQuery(osql);
      if (ors == null) {
        maxid = Integer.toString(1);
      }
      else {
        ors.next();
        maxid = Integer.toString(ors.getInt("maxid") + 1);
      }
      ors.close();
      return maxid;
    }
    catch (SQLException e) {
      throw e;
    }
    finally {