JDBC利用工具集实现对数据库中的数据进行操作

        设置一个工具集类，可以进行对数据库中的数据进行增删改查操作，简化了主类中代码的书写量。

package lession0722A;

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Constructor;
import java.lang.reflect.Method;
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.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public final class JdbcUtils {

	public static final String MYSQL_DRIVER = "com.mysql.cj.jdbc.Driver";

	private JdbcUtils() {
		// ignore
	}

	/**
	 * 获取数据库连接
	 * 
	 * @return
	 */
	public static Connection getConnection(String driverName, String url, String user, String password) {
		try {
			Class.forName(driverName);
			return DriverManager.getConnection(url, user, password);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("获取数据库连接失败", e);
		}
	}

	public static Connection getConnection(String url, String user, String password) {
		return getConnection(MYSQL_DRIVER, url, user, password);
	}

	/**
	 * 数据库查询返回一个结果集
	 * 
	 * @return
	 */
	public static ResultSet executeQuery(Connection conn, String sql, Object... args) {
		try {
			PreparedStatement ps = conn.prepareStatement(sql);

			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}

			return ps.executeQuery();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("查询结果集失败", e);
		}
	}

	/**
	 * 查询表，并返回一个对象的集合
	 * 
	 * @return
	 */
	public static <T> List<T> query(Connection conn, ResultSetHandler<List<T>> handler, String sql, Object... args) {
		ResultSet rs = executeQuery(conn, sql, args);
		return handler.handle(rs);
	}

	/**
	 * 增删改
	 * 
	 * @param conn
	 * @param sql
	 * @param args
	 * @return
	 */
	public static int update(Connection conn, String sql, Object... args) {
		try {
			PreparedStatement ps = conn.prepareStatement(sql);

			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}

			return ps.executeUpdate();// 返回爱影响的行数

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("对数据库进行增删改操作失败", e);
		}
	}

	/**
	 * 关闭数据库连接
	 * 
	 * @param conn
	 */
	public static void closeConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 将一个下划线命名字符串转换成驼峰式
	 * 
	 * 强制约束：首字符不能是下划线
	 * 
	 * @param str
	 * @return
	 */
	public static String underlineToCamel(String str) {
		// a_student_name_is_low 2 aStudentNameIsLow
		StringBuilder sb = new StringBuilder();

		String[] tokens = str.split("_");
		sb.append(tokens[0]);

		for (int i = 1; i < tokens.length; i++) {
			String firstLetter = tokens[i].substring(0, 1);
			String remain = tokens[i].substring(1);
			sb.append(firstLetter.toUpperCase() + remain);
		}

		return sb.toString();
	}

	/**
	 * 将obj类转换成正确的类型
	 * 
	 * @param obj
	 * @param pd
	 * @return
	 */
	private static Object cast(Object obj, PropertyDescriptor pd) {
		if (pd.getPropertyType() == Integer.class) {
			return ((Number) obj).intValue();
		}

		if (pd.getPropertyType() == Long.class) {
			return ((Number) obj).longValue();
		}

		if (pd.getPropertyType() == Float.class) {
			return ((Number) obj).floatValue();
		}

		if (pd.getPropertyType() == Double.class) {
			return ((Number) obj).doubleValue();
		}

		if (pd.getPropertyType() == LocalDate.class) {
			Instant is = Instant.ofEpochMilli(((Date) obj).getTime());
			return LocalDate.ofInstant(is, ZoneId.systemDefault());
		}

		if (pd.getPropertyType() == LocalTime.class) {
			Instant is = Instant.ofEpochMilli(((Date) obj).getTime());
			return LocalTime.ofInstant(is, ZoneId.systemDefault());
		}

		if (pd.getPropertyType() == LocalDateTime.class) {
			Instant is = Instant.ofEpochMilli(((Date) obj).getTime());
			return LocalDateTime.ofInstant(is, ZoneId.systemDefault());
		}

		return obj;
	}

	/**
	 * 静态内部接口，可以省略static关键字。用于将结果集转换成目标类型
	 * 
	 * 结果集转换器
	 * 
	 * @author snow1k
	 * @date 2022/07/22
	 */
	public interface ResultSetHandler<T> {
		/**
		 * 就是把结果集转换成一个目标类型
		 * 
		 * @param rs
		 * @return
		 */
		public T handle(ResultSet rs);
	}

	/**
	 * 自动将结果集转换成Bean的转换器
	 * 
	 * @author snow1k
	 * @date 2022/07/22
	 */
	public static class BeanListHandler<T> implements ResultSetHandler<List<T>> {
		private final Constructor<T> constructor;// 构造函数
		private final List<String> columns = new ArrayList<>();// 存储结果集的所有列名（别名）
		// 存储所有属性描述符，以属性名为key
		private final Map<String, PropertyDescriptor> descriptors = new HashMap<>();

		public BeanListHandler(Class<T> clazz) {
			try {
				// 获取一个javabean的信息
				BeanInfo bi = Introspector.getBeanInfo(clazz);
				// 获取模型类（javabean）的所有属性描述符
				PropertyDescriptor[] pds = bi.getPropertyDescriptors();

				// 以属性名为key，存储所有的属性描述符
				for (PropertyDescriptor pd : pds) {
					descriptors.put(pd.getName(), pd);
				}

				// 强制要求模型类必须有一个公有的无参的构造函数
				this.constructor = clazz.getDeclaredConstructor();
			} catch (Exception e) {
				e.printStackTrace();
				throw new RuntimeException("无法调用模型类的无参的构造函数", e);
			}
		}

		@Override
		public List<T> handle(ResultSet rs) {
			List<T> result = new ArrayList<>();

			try {
				// 元数据
				ResultSetMetaData rsmd = rs.getMetaData();
				int count = rsmd.getColumnCount();
				for (int i = 0; i < count; i++) {
					columns.add(rsmd.getColumnLabel(i + 1));
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}

			// 从结果集取数据转换成目标集合
			try {
				while (rs.next()) {
					T t = this.constructor.newInstance();

					for (String column : columns) {
						Object obj = rs.getObject(column);
						// 把列名转换成模型类的属性名
						String property = underlineToCamel(column);
						// 获取描述某属性的属性描述符对象
						PropertyDescriptor pd = descriptors.get(property);
						Method method = pd.getWriteMethod();

						// 通过反射给目标对象设置值
						// 转换成正确的数据类型
						method.invoke(t, cast(obj, pd));

					}
					result.add(t);
				}
			} catch (Exception e) {
				e.printStackTrace();
			}

			return result;
		}
	}
}

要注意的是JavaBean中的属性名称要与数据库表中的名称几乎一致，数据库中的下划线_在Java中要写成驼峰式，如stu_id要写成stuId。

 

 JavaBean：员工类

package lession0722A;

import java.util.Date;

public class Student {

	private Integer id;
	private String stuId;
	private String name;
	private String sex;
	private Date birthday;
	private Float weight;
	private Integer height;
	private String email;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getStuId() {
		return stuId;
	}

	public void setStuId(String stuId) {
		this.stuId = stuId;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getSex() {
		return sex;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public Date getBirthday() {
		return birthday;
	}

	public void setBirthday(Date birthday) {
		this.birthday = birthday;
	}

	public Float getWeight() {
		return weight;
	}

	public void setWeight(Float weight) {
		this.weight = weight;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public Integer getHeight() {
		return height;
	}

	public void setHeight(Integer height) {
		this.height = height;
	}
}

主类：

package lession0722A;

import java.sql.Connection;
import java.util.List;

import lession0722A.JdbcUtils.BeanListHandler;

public class Test1 {

	public static void main(String[] args) {
		String url = "jdbc:mysql://127.0.0.1:3306/students?characterEncoding=UTF-8&useSSL=false&autoReconnect=true&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
		Connection conn = JdbcUtils.getConnection(url, "root", "123456");

		try {
			String sql = "select id,stu_id,name,sex,birthday,weight,email from t_student where id > ? and id < ?";

			BeanListHandler<Student> handler = new BeanListHandler<>(Student.class);

			List<Student> students = JdbcUtils.query(conn, handler, sql, 100, 130);
			System.out.println("id\t" + "stuId\t" + "name    \t" + "sex" + "\t" + "birthday\t" + "weight\t" + "height\t"
					+ "email");

			for (Student stu : students) {
				System.out.println(stu.getId() + "\t" + stu.getStuId() + "\t" + stu.getName() + "    \t" + stu.getSex()
						+ "\t" + stu.getBirthday() + "\t" + stu.getWeight() + "\t" + stu.getHeight() + "\t"
						+ stu.getEmail());
			}

		} finally {
			JdbcUtils.closeConnection(conn);
		}

	}
}

输出结果：