纯Java版本的JDBC基础操作,支持查询结果到泛型实体类的转换
闲来无事,纯手工撸了一把JDBC基础操作(从配置文件获取连接、CRUD、关闭等),基于MySQL,不依赖其它第三方库。代码如下。
package com.ldj.jdbc; /* * 纯Java代码版本的JDBC操作 * 支持查询结果到(泛型)实体类的映射 * 实体类为简单的JAVA Bean,即不包含复杂的对象属性 * * author: laideju itfky@foxmail.com * 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 int batchExecute(String sql, Object[][] params) { int ret = -1; Connection conn = null; PreparedStatement stat = null; try { conn = getConnection(); stat = conn.prepareStatement(sql); if(params!=null) { for (int i = 0, n = params.length; i < n; i++) { for(int j = 0, m = params[i].length; j < m; j++) { stat.setObject(j+1, params[i][j]); } stat.addBatch(); } } else { // 这里如果不执行 addBatch() 方法,则执行后不会有任何结果 stat.addBatch(); } int[] rowNumList = stat.executeBatch(); if(rowNumList != null && rowNumList.length > 0) { ret = 0; for(int num : rowNumList) { ret += num; } } } catch (Exception e) { e.printStackTrace(); } finally { close(null, stat, conn); } return ret; } /* * 单条记录的插入、删除、更新 */ public static int execute(String sql, Object[] params) { if(params == null) { return batchExecute(sql, null); } else { Object[][] wrapedParams = {params}; return batchExecute(sql, wrapedParams); } } /* * 获取实体对象 */ 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; } /* * 获取统计函数的查询结果 */ public static Number getSimpleResult(String sql, Object[] params) { Number ret =-1; Connection conn = null; PreparedStatement stat = null; ResultSet rs = null; try { conn = getConnection(); stat = conn.prepareStatement(sql); if(params!=null) { for (int i = 0, n = params.length; i < n; i++) { stat.setObject(i+1, params[i]); } } rs = stat.executeQuery(); if(rs.next()) { ret = (Number)rs.getObject(1); } } catch (Exception e) { e.printStackTrace(); } finally { close(rs, stat, conn); } return ret; } }