传说中的SqlHelper

dbinfo.properties:

#oracle url=jdbc:oracle:thin:@127.0.0.1:1521:orcl username=scott driver=oracle.jdbc.driver.OracleDriver password=Oracle11g

SqlHelper.java:

/**
 * 功能：实现SqlHelper工具类
 * 日期：2012年3月29日
 */
package com.cz.util;

import java.io.*;
import java.sql.*;
import java.util.*;

public class SqlHelper {
  //定义需要的变量
  private static Connection ct=null;
  private static PreparedStatement ps=null;
  private static ResultSet rs=null;
  private static CallableStatement cs=null;
  
  public static CallableStatement getCs() {
    return cs;
  }

  private static String url="";
  private static String username="";
  private static String driver="";
  private static String password="";
  //读取配置文件
  private static Properties pp=null;
  private static InputStream fis=null;
  
  //加载驱动，只需要一次
  static{
    try {
      //从dbinfo.properties文件中读取配置信息
      pp=new Properties();
      //fis=new FileInputStream("dbinfo.properties");
      //当我们使用java web的时候，读取文件要使用类加载器
      fis=SqlHelper.class.getClassLoader().getResourceAsStream("dbinfo.properties");
      pp.load(fis);
      url=pp.getProperty("url");
      username=pp.getProperty("username");
      driver=pp.getProperty("driver");
      password=pp.getProperty("password");
      
      Class.forName(driver);
    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }finally{
      try {
        fis.close();
      } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
      }
      fis=null;
    }
  }
  //得到连接
  public static Connection getConnection(){
    try {
      ct=DriverManager.getConnection(url, username, password);
    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
    }
    return ct;
  }
  
  //分页问题
  public static ResultSet executeQuery(){
    return null;
  }
  
  //调用存储过程(无返回值)
  public static void callPro1(String sql,String [] parameters){
    try {
      ct=getConnection();
      cs=ct.prepareCall(sql);
      //给?赋值
      if(parameters!=null){
         for(int i=0;i<parameters.length;i++){
           cs.setObject(i+1, parameters[i]);
         }
      }
      //执行
      cs.execute();
    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      //抛出运行时异常
      throw new RuntimeException(e.getMessage());
    }finally{
      close(rs,cs,ct);
    }
  }
  
  //调用存储过程(有返回值)
  public static CallableStatement callPro2(String sql,String [] inparameters,Integer [] outparameters){
    try {
      ct=getConnection();
      cs=ct.prepareCall(sql);
      //给?赋值
      if(inparameters!=null){
         for(int i=0;i<inparameters.length;i++){
           cs.setObject(i+1, inparameters[i]);
         }
      }
      if(outparameters!=null){
         for(int i=0;i<outparameters.length;i++){
           cs.registerOutParameter(inparameters.length+1+i, outparameters[i]);
         }
      }
      
      //执行
      cs.execute();
    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      //抛出运行时异常
      throw new RuntimeException(e.getMessage());
    }finally{
      //close(rs,cs,ct);
    }
    return cs;
  }
  
  //统一的select语句
  public static ResultSet executeQuery(String sql,String [] parameters){
    try {
      ct=getConnection();
      ps=ct.prepareStatement(sql);
      //给?赋值
      if(parameters!=null){
         for(int i=0;i<parameters.length;i++){
           ps.setString(i+1, parameters[i]);
         }
      }
      //执行
      rs=ps.executeQuery();
    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      //抛出运行时异常
      throw new RuntimeException(e.getMessage());
    }finally{
      //close(rs,ps,ct);
    }
    return rs;
  }
  
  //对查询语句升级
  //这样可以满足：哪里使用资源哪里关闭资源
  public static ArrayList executeQuery3(String sql,String[] parms)
  {
    PreparedStatement pstmt=null;
    Connection conn=null;
    ResultSet rs=null;
    try{
      conn=getConnection();
      pstmt=conn.prepareStatement(sql);
      //对问号赋值
      if(parms!=null){
         for(int i=0;i<parms.length;i++){
           pstmt.setString(i+1, parms[i]);
         }
      }
      rs=pstmt.executeQuery();
      ArrayList al=new ArrayList();
      ResultSetMetaData rsmd=rs.getMetaData();
      int column=rsmd.getColumnCount();
      while (rs.next())
      {
        Object[] ob=new Object[column];
        for (int i=1;i<=column ;i++ )
        {
          ob[i-1]=rs.getObject(i);

        }
        al.add(ob);

      }
      
      return al;
    }catch(Exception e){
      e.printStackTrace();
      throw new RuntimeException("executeSqlResultSet方法出错："+e.getMessage());
    }finally{
      close(rs, pstmt, conn);
    }
  }


  
  public static Connection getCt() {
    return ct;
  }

  public static PreparedStatement getPs() {
    return ps;
  }

  public static ResultSet getRs() {
    return rs;
  }

  //如果有多个update/delete/insert，需要考虑事务
  public static void executeUpdate2(String []sql,String [][]parameters){
    try {
      ct=getConnection();
      //因为这时用户传入的可能是多个sql语句
      ct.setAutoCommit(false);
      for(int i=0;i<sql.length;i++){
        if(parameters[i]!=null){
          ps=ct.prepareStatement(sql[i]);
          for(int j=0;j<parameters[i].length ;j++){
            ps.setString(j+1,parameters[i][j]);
          }
          ps.executeUpdate();
        }
      }
      //int i=9/0;
      ct.commit();
      
      
    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      //回滚
      try {
        ct.rollback();
      } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
      }
      //抛出运行时异常
      throw new RuntimeException(e.getMessage());
    }finally{
      close(rs,ps,ct);
    }
  }
  
  
  //先写一个update/delete/insert
  public static void executeUpdate(String sql,String [] parameters){
    
    try {
      ct=getConnection();
      ps=ct.prepareStatement(sql);
      //给?赋值
      if(parameters!=null){
         for(int i=0;i<parameters.length;i++){
           ps.setString(i+1, parameters[i]);
         }
      }
      //执行
      ps.executeUpdate();
    } catch (Exception e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
      //抛出运行时异常
      throw new RuntimeException(e.getMessage());
    }finally{
      close(rs,ps,ct);
    }
    
  }
  
  //关闭资源函数
  public static void close(ResultSet rs,Statement ps,Connection ct){
    try {

      if (rs != null) {
        rs.close();
      }
      rs = null;
      if (ps != null) {
        ps.close();
      }
      ps = null;
      if (ct != null) {
        ct.close();
      }
      ct = null;
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
  

}