JDBCUtil牛X版

package com.jdbc;

import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class JDBCUtil {
	private static String driver;
	private static String url;
	private static String username;
	private static String password;
	
	protected static Connection conn;
	protected static PreparedStatement pstmt;
	protected static ResultSet rs;
	protected String sql;
	
	static{
		try {
			Properties properties = new Properties();
			properties.load(JDBCUtil.class.getClassLoader().getResourceAsStream("db.properties"));
			driver = properties.getProperty("driver");
			url = properties.getProperty("url");
			username = properties.getProperty("username");
			password = properties.getProperty("password");
			Class.forName(driver);
		} catch ( IOException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 获取连接
	 * @return
	 */
	public static Connection getConn(){
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 关闭连接
	 * @param rs
	 * @param pstmt
	 * @param conn
	 */
	public static void closeConn(ResultSet rs,PreparedStatement pstmt,Connection conn){
		try {
			if(conn!=null){
				conn.close();
			}
			if(pstmt!=null){
				pstmt.close();
			}
			if(rs!=null){
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 通用的查询
	 * 封装成对象
	 * @param clazz
	 * @param sql
	 * @param args
	 * @return 对象集合
	 */
	public static <T> List<T> getResultSetList(Class<T> clazz, String sql, Object... args) {
		T entity = null;
		List<T> list = new ArrayList<>();
		try {
			conn = JDBCUtil.getConn();
			pstmt = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				pstmt.setObject(i + 1, args[i]);
			}
			rs = pstmt.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();// 得到ResultSetMetaData对象

			String nameStr = null;
			Object value = null;
			Field f = null;
			List<Field> fields=new ArrayList<>();
			for (int i = 0; i < rsmd.getColumnCount(); i++) {
				nameStr = rsmd.getColumnLabel(i+1);//获取别名
				f=clazz.getDeclaredField(nameStr);
				f.setAccessible(true);
				fields.add(f);
			}
			
			while (rs.next()) {
				entity = clazz.newInstance();
				for (int i = 0; i < rsmd.getColumnCount(); i++) {
					value = rs.getObject(i + 1);
					f = fields.get(i);
					f.set(entity, value);
				}
				list.add(entity);
			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeConn(rs, pstmt, conn);
		}
		return list;
	}
	
	/**
	 * 添加、删除、修改
	 * @param sql
	 * @param parameters
	 * @return
	 */
	public static boolean executeUpdate(String sql,Object...parameters){
		boolean isFlag = false;
		try {
			conn = getConn();
			pstmt = conn.prepareStatement(sql);
			for (int i=0; i<parameters.length; i++) {
				pstmt.setObject(i+1, parameters[i]);
			}
			if(pstmt.executeUpdate()>0){
				isFlag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConn(rs, pstmt, conn);
		}
		return isFlag;
	}
	
	/**
	 * 计数
	 * @param sql
	 * @return
	 */
	public  int countRows(String sql,Object...parameters){
		int num = 0;
		try {
			conn = getConn();
			pstmt = conn.prepareStatement(sql);
			for (int i=0; i<parameters.length; i++) {
				pstmt.setObject(i+1, parameters[i]);
			}
			rs = pstmt.executeQuery();
			if(rs.next()){
				num=rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConn(rs, pstmt, conn);
		}
		return num;
	}
	
	/**
	 * 判断该条件数据是否存在
	 * @param sql
	 * @param parameters
	 * @return
	 */
	public  boolean isExist(String sql,Object...parameters){
		boolean isFlag = false;
		try {
			conn = getConn();
			pstmt = conn.prepareStatement(sql);
			for (int i=0; i<parameters.length; i++) {
				pstmt.setObject(i+1, parameters[i]);
			}
			rs = pstmt.executeQuery();
			if(rs.next()){
				isFlag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			closeConn(rs, pstmt, conn);
		}
		return isFlag;
	}
}