纯Java版本的JDBC基础操作，支持查询结果到泛型实体类的转换

闲来无事，纯手工撸了一把JDBC基础操作（从配置文件获取连接、CRUD、关闭等），基于MySQL，不依赖其它第三方库。代码如下。

---

package com.ldj.jdbc;

/*
 * 纯Java代码版本的JDBC操作
 * 支持查询结果到(泛型)实体类的映射
 * 实体类为简单的JAVA Bean，即不包含复杂的对象属性
 *
 * author: laideju [email protected]
 * version: 1.0
 * date: 2018-10-25
 * 
 * */

import java.io.InputStream;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;


public class SimpleJdbcHelper {
	
	private static String driver = "";
	private static String url="";
	private static String userName="";
	private static String password="";
	
	static {
		Properties props = new Properties();
		try {
			InputStream is = SimpleJdbcHelper.class.getClassLoader().getResourceAsStream("db.properties");
			props.load(is);
			driver = props.getProperty("driver");
			url=props.getProperty("url");
			userName=props.getProperty("userName");
			password = props.getProperty("password");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static String getDriver() {
		return driver;
	}

	public static String getUrl() {
		return url;
	}

	public static String getUserName() {
		return userName;
	}

	public static String getPassword() {
		return password;
	}
	
	public static Connection getConnection() {
		Connection conn = null;		
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, userName, password);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return conn;
	}
	
	public static void close(ResultSet rs, Statement stat, Connection conn) {
		if(rs!=null) {
			try {
				rs.close();
				rs = null;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		if(stat != null) {
			try {
				stat.close();
				stat = null;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		
		if(conn != null) {
			try {
				conn.close();
				conn = null;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	/*
	 *	执行插入、更新、删除
	 */
	public static boolean execute(String sql, Object...args) {
		
		boolean ret = false;
		Connection conn = null;
		PreparedStatement stat = null;
		try {
			conn = getConnection();
			stat = conn.prepareStatement(sql);
			if(args!=null) {
				for (int i = 0, n = args.length; i < n; i++) {
					stat.setObject(i + 1, args[i]);
				}
			}
			stat.execute();
			ret = true;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(null, stat, conn);
		}
		
		return ret;
	}
	
	/*
	 *	获取实体对象
	 */
	public static<T> List<T> queryResult(String sql, Object[] args, Class<T> clazz){
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		List<T> list = new ArrayList<T>();
		try {
			conn=getConnection();
			stat =conn.prepareStatement(sql);
			if(args!=null) {
				for(int i=0, n=args.length;i<n;i++) {
					stat.setObject(i + 1, args[i]);
				}
			}
			
			rs = stat.executeQuery();
			
			ResultSetMetaData meta = rs.getMetaData();
			int totalProps = meta.getColumnCount();
			
			// 获取单列查询结果, 通常是 COUNT 等统计函数的结果
			if(totalProps == 1) {				
			
				rs.next();
				list.add((T)rs.getObject(1));						
				
			} else if(clazz != null) { // 获取多列查询结果，通常是获取实体类
				
				Method[] methods = clazz.getDeclaredMethods();
				Map<String, Method> methodNameMap = new HashMap<String, Method>();
				for(Method m:methods) {
					String methodName = m.getName().toLowerCase();
					if(!methodName.startsWith("set")) {
						continue;
					}				
					int mod = m.getModifiers();
					boolean isInstancePublicSetter = Modifier.isPublic(mod) && !Modifier.isStatic(mod) && !Modifier.isAbstract(mod);
					if(isInstancePublicSetter) {
						methodNameMap.put(methodName, m);
					}
				}			
				
				while(rs.next()) {
					Object obj = clazz.newInstance();
					for(int i=1; i <= totalProps; i++) {
						Object currentColumnVal = rs.getObject(i);
						String currentColumnName = meta.getColumnName(i);
						Method m = methodNameMap.get("set"+currentColumnName);
						if(m!=null) {
							m.invoke(obj, currentColumnVal);
						}
					}
					list.add((T)obj);
				}						
				
			} else { // 异常情形：既不是单列结果，也没有指明实体类的类型
				throw new Exception("queryResult(String sql, Object[] args, Class<T> clazz) 非单列结果且未指明clazz的值");
			}			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}	
}