sqlHelper的编写

在进行jdbc 程序编写的时候，因为对数据库操作很多，所有都会把对数据库的各种操作，封装到一个类(SqlHelper/DBUtil)。

 注意： 如果访问数据库很频繁，则我们的Connection 、PreparedStatement,ResultSet..就不要搞成static

程序：

package com.linxiwd.utils;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class SqlHelper {

	// 定义需要的变量
	private static Connection ct = null;
	private static PreparedStatement ps = null;
	private static CallableStatement cs = null;
	private static ResultSet rs = null;

	private static Properties pp = null;
	private static FileInputStream fis = null;

	private static String url = "";
	private static String username = "";
	private static String password = "";
	private static String driver;

	// 加载驱动
	static {
		try {
			// 从properties问价获取配置信息
			pp = new Properties();
			fis = new FileInputStream("/imgs/dbinfo.properties");
			pp.load(fis);
			url = pp.getProperty("url");
			username = pp.getProperty("username");
			password = pp.getProperty("password");
			driver = pp.getProperty("driver");

			Class.forName("driver");

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (fis != null) {
				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 (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return ct;
	}

	// 分页问题？
	public static ResultSet executeQuery2() {
		return null;

	}

	// 调用存储过程
	// sql语句：{call 过程(?,?,?)}
	public static void callProl(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 (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}finally{
			close(rs, cs, ct);
		}
		
	}

	// 提供update/delete/insert方法
	// sql格式：update 表名 set 字段名-？where 字段=？
	// parameters应该是对应数目的参数
	public void excuteUpdate(String sql, String[] parameters) {
		ct = getConnection();
		try {
			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 void excuteUpdate(String[] sql, String[][] parameters) {
		try {
			ct = getConnection();
			ct.setAutoCommit(false);
			for (int i = 0; i < sql.length; i++) {
				ps = ct.prepareStatement(sql[i]);
				if (parameters[i] != null) {
					for (int j = 0; j < parameters[i].length; j++) {
						ps.setString(j + 1, parameters[i][j]);
					}
				}
				ps.executeUpdate();
			}

			ct.commit();

		} catch (Exception e) {
			// TODO: handle exception
			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);
		}
	}

	public ResultSet excuteQuery(String sql, String[] parameters) {
		ct = getConnection();
		try {
			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);
			// 这里由于要返回ResultSet，不能先关闭资源，需要在service部分关闭，不能忘了
			// 关闭的时候采用反向引用的方式，调用get方法获取该对象的ct/ps/rs；
		}

		return rs;

	}

	public ResutSet excuteQuery(String[] sql, String[][] parameters) {
		try {
			ct = getConnection();
			ct.setAutoCommit(false);
			for (int i = 0; i < sql.length; i++) {
				ps = ct.prepareStatement(sql[i]);
				if (parameters[i] != null) {
					for (int j = 0; j < parameters[i].length; j++) {
						ps.setString(j + 1, parameters[i][j]);
					}
				}
				rs=ps.executeQuery();
			}

			ct.commit();

		} catch (Exception e) {
			// TODO: handle exception
			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);
		}

		return rs;
	}

	// 关闭资源的函数
	public static void close(ResultSet rs, Statement sm, Connection ct) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			rs = null;
		}
		if (sm != null) {
			try {
				sm.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			sm = null;
		}
		if (ct != null) {
			try {
				ct.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			ct = null;
		}

	}

	public static Connection getCt() {
		return ct;
	}

	public static PreparedStatement getPs() {
		return ps;
	}

	public static ResultSet getRs() {
		return rs;
	}
}