简单封装一个JDBCUtil小工具

实现原理：

获取数据库连接，使用的是阿里巴巴的的druid数据库连接池(druid-1.1.10)，因此个人使用的时候需要一个配置文件。本工具默认自动扫描classpath路径下的src文件夹中的db.properties文件。另外使用到了反射来把查询到的结果集数据注入Java对象中。但仅仅支持全字段查询的结果集封装。

开发环境：

JDK1.8、ojdbc8.jar、druid-1.1.10.jar

实现的功能：

• 获取一个数据库连接对象
• 执行DDL，DML语句
• 执行DQL语句
• 根据表名逆向生成实体类
• 支持简单的分页查询操作

使用方法：

首先需要在src下创建配置文件db.properties，内容于druid的配置内容相同，因为本身就是给druid用的，配置内容参考如下：

driverClassName=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@127.0.0.1:1521:XE
username=briup
password=briup
initialSize=5
maxActive=10
maxWait=3000

方法概览：

• JDBCUtil.getConnection() 获取数据库连接对象
• JDBCUtil.executeUpdate(sql) 执行一条sql语句返回受影响的行数
• JDBCUtil.executeQueryAsList(sql, clazz) 执行一条sql语句，返回一个存放clazz类型对象的集合
• JDBCUtil.executeQueryAsObj(sql, clazz) 执行一条sql语句，返回一个clazz类型对象
• JDBCUtil.executeQueryHandleRS(sql, func) 执行一条sql语句，可自己实现函数接口处理结果集
• JDBCUtil.generateJavaFile(tableName, packageStr) 根据表名逆向生成对应的Java类
• JDBCUtil.close(autoCloseable) 关闭可自动关闭资源（conn、stmt、rs）
• 分页功能：需new一个JDBCUtil实例，先调用 initPageHelper()方法初始化，再用对象调用getPage()方法进行分页

全部源码如下：

package com.briup.sxau.jdbc.util;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.function.Function;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
/**
 * 
 * @author qiaoao
 *
 */
public class JDBCUtil {
	/**
	 * 封装一些类型名字的常量
	 */
	private static final String BYTE = byte.class.getSimpleName();
	private static final String SHORT = short.class.getSimpleName();
	private static final String INT = int.class.getSimpleName();
	private static final String LONG = long.class.getSimpleName();
	private static final String FLOAT = float.class.getSimpleName();
	private static final String DOUBLE = double.class.getSimpleName();
	private static final String BOOLEAN = boolean.class.getSimpleName();
	private static final String DATE = "Date";
	private static final String STRING = String.class.getSimpleName();

	private static final Map<String, String> map = new HashMap<>();

	static {
		map.put("NUMBER", INT);
		map.put("VARCHAR2", STRING);
	}

	private static DataSource dataSource = null;

	private static Properties prop = null;

	static {
		prop = new Properties();
		InputStream inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties");
		try {
			prop.load(inputStream);
			dataSource = DruidDataSourceFactory.createDataSource(prop);
		} catch (IOException e1) {
			e1.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	// 总记录
	private int totalInfo;
	// 总页数
	private int totalPage;
	// 每页显示条数
	private int pageSize;
	// 当前操作的表名
	private String tableName;

	public void initPageHelper(String tableName, int pageSize) {
		if (tableName == null || pageSize <= 0) {
			throw new IllegalArgumentException("表名不可为空 且 分页条数必须大于0");
		}
		this.tableName = tableName;
		this.totalInfo = getTotalInfo();
		this.pageSize = pageSize;
		this.totalPage = totalInfo / pageSize + (totalInfo % pageSize == 0 ? 0 : 1);
	}

	/**
	 * 准备分页的sql语句
	 * 
	 * @param start 开始的记录编号
	 * @param end   结束的记录编号
	 * @return
	 */
	private String prepareSql(int start, int end) {
		List<TypeAndName> list = getTableInfo(tableName);
		StringBuilder sb = new StringBuilder("select ");
		for (int i = 0; i < list.size(); i++) {
			sb.append("t." + list.get(i).name);
			if (i != list.size() - 1) {
				sb.append(",");
			}
		}
		sb.append(" \n");
		sb.append("from (select rownum rn,");
		for (int i = 0; i < list.size(); i++) {
			sb.append(list.get(i).name);
			if (i != list.size() - 1) {
				sb.append(",");
			}
		}
		sb.append(" \n");
		sb.append("from " + tableName + " where rownum<=" + end + ")");
		sb.append("\n");
		sb.append("t where t.rn>=" + start);
		return sb.toString();
	}

	public <T> List<T> getPage(int pageNum, Class<T> clazz) {
		if (tableName == null) {
			throw new RuntimeException("未初始化，需先调用initPageHelper()方法");
		}
		if (pageNum > totalPage) {
			return null;
		}
		int end = pageNum * pageSize;
		int start = end - pageSize + 1;
		String sql = prepareSql(start, end);
		return executeQueryAsList(sql, clazz);
	}

	// 获取表中总记录条数
	public int getTotalInfo() {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String str = null;
		try {
			conn = dataSource.getConnection();
			stmt = conn.createStatement();
			String sql = "select count(*) from " + tableName;
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				str = rs.getString(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, stmt, conn);
		}
		return Integer.parseInt(str);
	}

	/**
	 * 获取一个数据库连接对象
	 * 
	 * @return Connection
	 * @throws SQLException
	 */
	public static Connection getConnection() throws SQLException {
		return dataSource.getConnection();
	}

	/**
	 * 执行DML，DDL语句
	 * 
	 * @return 受影响的行数
	 */
	public static int executeUpdate(String sql) {
		Connection conn = null;
		Statement stmt = null;
		int rows = 0;
		try {
			conn = dataSource.getConnection();
			stmt = conn.createStatement();
			rows = stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(stmt, conn);
		}
		return rows;
	}

	/**
	 * 自定义处理结果集
	 * 
	 * @param <T>
	 * @param sql  要执行的sql语句
	 * @param func 对结果集进行操作的函数
	 * @return
	 */
	public static <T> T executeQueryHandleRS(String sql, Function<ResultSet, T> func) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		T t = null;
		try {
			conn = dataSource.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			t = func.apply(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, stmt, conn);
		}
		return t;
	}

	/**
	 * 执行查询
	 * 
	 * @return 对象
	 */
	public static <T> T executeQueryAsObj(String sql, Class<T> clazz) {
		return executeQueryAsList(sql, clazz).get(0);
	}

	/**
	 * 执行查询
	 * 
	 * @return list集合
	 */
	public static <T> List<T> executeQueryAsList(String sql, Class<T> clazz) {
		List<T> result = new ArrayList<>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = dataSource.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			List<TypeAndName> list = parseClass(clazz);
			T obj = null;
			while (rs.next()) {
				// 通过反射创建对象
				obj = clazz.newInstance();
				for (int i = 0; i < list.size(); i++) {
					TypeAndName tan = list.get(i);
					if (INT.equals(tan.type)) {
						int v = rs.getInt(tan.name);
						// 拿到值以后，调用set方法给对象赋值
						tan.invokeSet(obj, int.class, v);
					} else if (STRING.equals(tan.type)) {
						String v = rs.getString(tan.name);
						tan.invokeSet(obj, String.class, v);
					} else if (DATE.equals(tan.type)) {
						java.sql.Date v = rs.getDate(tan.name);
						tan.invokeSet(obj, java.sql.Date.class, v);
					} else if (BYTE.equals(tan.type)) {
						byte v = rs.getByte(tan.name);
						tan.invokeSet(obj, byte.class, v);
					} else if (SHORT.equals(tan.type)) {
						short v = rs.getShort(tan.name);
						tan.invokeSet(obj, short.class, v);
					} else if (LONG.equals(tan.type)) {
						long v = rs.getLong(tan.name);
						tan.invokeSet(obj, long.class, v);
					} else if (FLOAT.equals(tan.type)) {
						float v = rs.getFloat(tan.name);
						tan.invokeSet(obj, float.class, v);
					} else if (DOUBLE.equals(tan.type)) {
						double v = rs.getDouble(tan.name);
						tan.invokeSet(obj, double.class, v);
					} else if (BOOLEAN.equals(tan.type)) {
						boolean v = rs.getBoolean(tan.name);
						tan.invokeSet(obj, boolean.class, v);
					}
				}
				result.add(obj);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(rs, stmt, conn);
		}
		return result;
	}

	/**
	 * 解析Class，获取类中所有的属性名以及属性类型
	 * 
	 * @param clazz
	 * @return
	 */
	private static List<TypeAndName> parseClass(Class<?> clazz) {
		Field[] fields = clazz.getDeclaredFields();
		List<TypeAndName> list = new ArrayList<>();
		for (int i = 0; i < fields.length; i++) {
			String fieldType = fields[i].getType().getSimpleName();
			String fieldName = fields[i].getName();
			list.add(new TypeAndName(fieldType, fieldName));
		}
		return list;
	}

	// 将字符串的首字母大写，其他字母小写
	public static String initCap(String str) {
		return str.substring(0, 1).toUpperCase().concat(str.substring(1).toLowerCase());
	}

	/**
	 * 声明一个静态内部类 用来封装(数据库字段或java类)的属性类型和属性名称
	 */
	private static class TypeAndName {
		String type;
		String name;

		public TypeAndName(String type, String name) {
			this.type = type;
			this.name = name;
		}

		/**
		 * 利用反射调用对象的set方法给对象属性赋值
		 * 
		 * @param obj   要赋值的对象
		 * @param c     方法参数的class
		 * @param value 方法参数的值
		 */
		public <T> void invokeSet(Object obj, Class<T> c, Object value) {
			try {
				Method setMethod = obj.getClass().getDeclaredMethod("set".concat(initCap(this.name)), c);
				setMethod.invoke(obj, value);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		@Override
		public String toString() {
			return "TypeAndName [type=" + type + ", name=" + name + "]";
		}
	}

	public static void generateJavaFile(String tableName, String packageStr) {
		// package com.briup.demo;
		String packageDeclare = "package ".concat(packageStr).concat(";");
		// src/com/briup/demo
		String classLocation = "src/".concat(packageStr.replace(".", "/")).concat("/");
		File file = new File(classLocation);
		if (!file.exists()) {
			file.mkdirs();
		}
		String content = getContent(tableName, packageDeclare);
		String javaFileName = initCap(tableName).concat(".java");
		classLocation = classLocation.concat(javaFileName);
		contentToJavaFile(content, classLocation);
	}

	private static String getContent(String tableName, String packageDeclare) {
		List<TypeAndName> list = getTableInfo(tableName);
		String className = initCap(tableName);
		StringBuilder sb = new StringBuilder(packageDeclare).append("\n\n");
		if (map.containsKey("DATE")) {
			sb.append("import java.sql.Date;\n\n");
		}
		sb.append("public class " + className + " {\n\n");
		// 属性
		for (int i = 0; i < list.size(); i++) {
			TypeAndName tan = list.get(i);
			String fieldType = map.get(tan.type);
			String fieldName = tan.name.toLowerCase();
			sb.append("\tprivate " + fieldType + " " + fieldName + ";\n\n");
		}
		// 构造方法
		sb.append("\tpublic " + className + "() {}\n\n");
		sb.append("\tpublic " + className + "(");
		for (int i = 0; i < list.size(); i++) {
			TypeAndName tan = list.get(i);
			String fieldType = map.get(tan.type);
			String fieldName = tan.name.toLowerCase();
			sb.append(fieldType + " " + fieldName);
			if (i != list.size() - 1) {
				sb.append(",");
			}
		}
		sb.append(") {\n");
		for (int i = 0; i < list.size(); i++) {
			TypeAndName tan = list.get(i);
			String fieldName = tan.name.toLowerCase();
			sb.append("\t\tthis." + fieldName + " = " + fieldName + ";\n");
		}
		sb.append("\t}\n\n");
		// get set...
		for (int i = 0; i < list.size(); i++) {
			TypeAndName tan = list.get(i);
			String fieldType = map.get(tan.type);
			String fieldName = tan.name.toLowerCase();
			String prepFieldName = initCap(fieldName);
			String setMethodName = "set".concat(prepFieldName);
			String getMethodName = "get".concat(prepFieldName);
			sb.append("\tpublic void " + setMethodName + "(" + fieldType + " " + fieldName + ") {\n");
			sb.append("\t\tthis." + fieldName + " = " + fieldName + ";\n");
			sb.append("\t}\n\n");
			sb.append("\tpublic " + fieldType + " " + getMethodName + "() {\n");
			sb.append("\t\treturn this." + fieldName + ";\n");
			sb.append("\t}\n\n");
		}
		// toString()方法
		sb.append("\t@Override\n\tpublic String toString() {\n");
		sb.append("\t\treturn \"" + className + "=[");
		for (int i = 0; i < list.size(); i++) {
			TypeAndName tan = list.get(i);
			String fieldName = tan.name.toLowerCase();
			sb.append(fieldName + "=\" + ");
			if (i != 0 && i % 3 == 0) {
				sb.append("\n\t\t");
			}
			sb.append(fieldName + " + ");
			if (i != list.size() - 1) {
				sb.append("\",");
			}
		}
		sb.append("\"]\";\n");
		sb.append("\t}");
		sb.append("\n");
		sb.append("}");
		return sb.toString();
	}

	private static void contentToJavaFile(String content, String javaFilePath) {
		FileWriter fw = null;
		try {
			fw = new FileWriter(javaFilePath);
			fw.write(content);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (fw != null) {
				try {
					fw.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	private static List<TypeAndName> getTableInfo(String tableName) {
		List<TypeAndName> list = new ArrayList<>();
		Connection conn = null;
		ResultSet rs = null;
		try {
			conn = dataSource.getConnection();
			DatabaseMetaData metaData = conn.getMetaData();
			rs = metaData.getColumns(null, prop.getProperty("username").toUpperCase(), tableName.toUpperCase(), null);
			while (rs.next()) {
				String colName = rs.getString("COLUMN_NAME");
				String colType = rs.getString("TYPE_NAME");
				if (colType.equalsIgnoreCase("DATE")) {
					map.put("DATE", DATE);
				}
				list.add(new TypeAndName(colType, colName));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(rs, conn);
		}
		return list;
	}

	public static void close(AutoCloseable... autoCloseable) {
		for (AutoCloseable closeable : autoCloseable) {
			try {
				if (closeable != null)
					closeable.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
}