传说中的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();
		}
	}
	

}