JDBC通用连接、增删改查类

JDBC通用连接、增删改查类

package com.xxx.yyy.dao;

import java.sql.*;

/**
 * 	>>>通用的  数据库  操作 方法 增删改查
 * 	引用范例:
 *	public static void main(String[] args) throws SQLException {
 *		ResultSet rs = executeQuery("SELECT * FROM user;", null);
 *		while (rs.next()) {
 *			System.out.println(rs.getObject(1));
 *		}
 *		Object [] s = {1234560};
 *		boolean flag = executeUpdate("UPDATE `user` SET password = ?;",s);
 *		System.out.println(flag);
 *	}
 */
public class DBUtil {
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
	static final String URL = "jdbc:mysql://localhost:3306/his";
	static final String USERNAME = "root";
	static final String PASSWORD = "root";
	
	public static Connection connection = null;
	public static PreparedStatement pstmt = null;
	public static ResultSet rs = null;
	/**
	 *	>>>获取连接  返回DriverManager.getConnection(URL,USERNAME,PASSWORD);
	 */
	public static Connection getConnection() throws SQLException, ClassNotFoundException {
		Class.forName(JDBC_DRIVER);
		return DriverManager.getConnection(URL,USERNAME,PASSWORD);
	}
	/**
	 *	>>>获取单例连接  返回DriverManager.getConnection(URL,USERNAME,PASSWORD);
	 */
	public static Connection getOneConnection() throws SQLException, ClassNotFoundException {
		if (DBUtil.connection == null) {
			return getConnection();
		} else {
			return DBUtil.connection;
		}
	}
	/**
	 * 	>>>给 String sql 语句中的 ? 赋值
	 */
	public static PreparedStatement createPreParedStatement(String sql,Object[] params) throws SQLException, ClassNotFoundException {
		connection = getOneConnection();
		pstmt = connection.prepareStatement(sql);
		if (params != null) {
			for (int i = 0; i < params.length; i++) {
				pstmt.setObject((i+1),params[i]);
			}
		}
		return pstmt;
	}
	/**
	 * 	(((通用的增删改)))
	 * 	>>>用法：String sql = "";  Object[] params = {int id,String name.....};
	 * 	>>>返回：   boolean值 t/f 
	 * 	>>>接收：  创建boolean flag = null; 
	 * 			使 flag = executeUpdate(String sql,Object[] params);
	 * 	>>>已经自动关闭 if (pstmt!=null) pstmt.close();
	 *  		if (rs!=null) rs.close();
	 *		   if (DBUtil.connection!=null) DBUtil.connection.close();
	 */
	public static boolean executeUpdate(String sql,Object[] params) {
		try {
			pstmt = createPreParedStatement(sql, params);
			int count = pstmt.executeUpdate();
			if (count > 0) {
				return true;
			} else {
				return false;
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			closeAll(connection, pstmt, rs);
		}
	}
	/**
	 * 	(((通用的查)))                                                
	 * 	>>>用法：String sql = "select ? from `user` where id =?";
	 * 		?对应值Object[] params = {String name,int id.....};可为空null
	 * 	>>>返回:  PreparedStatement pstmt = null;类型结果集rs
	 * 	>>>接收： 创建  PreparedStatement pstmt = null;
	 * 		使pstmt = executeQuery(String sql,Object[] params)
	 *	>>>需要调用关闭  DBUtil.closeAll(DBUtil.connection, pstmt, rs);
	 *			
	 */
	public static ResultSet executeQuery(String sql,Object[] params) {
		try {
			pstmt = createPreParedStatement( sql,params);
			rs = pstmt.executeQuery();
			return rs;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return null;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} 
	}
/**
 * 	(((关闭数据库连接)))
 * 			try {
 *				if (pstmt!=null) pstmt.close();
 *				if (rs!=null) rs.close();
 *				if (connection!=null) connection.close();
 *			} catch (SQLException e) {
 *				e.printStackTrace();
 *			}
 * 		
 */
	public static void closeAll(Connection connection,Statement pstmt,ResultSet rs) {
			try {
				if (pstmt!=null) pstmt.close();
				if (rs!=null) rs.close();
				if (connection!=null) connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}
	
}