ResultSet的所有值

说到ResultSet，对于有Java 开发经验的小伙伴自然是熟悉不过了，不过我相信对于大多数人来说也算是最熟悉的陌生人。从ResultSet 的取值操作大家都会，比如：

private static List<Member> select(String sql) {
	List<Member> result = new ArrayList<>();
	Connection con = null;
	PreparedStatement pstm = null;
	ResultSet rs = null;
	try {
		//1、加载驱动类
		Class.forName("com.mysql.jdbc.Driver");
		//2、建立连接
		con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/spring-db-demo","root","123456");
		//3、创建语句集
		pstm =  con.prepareStatement(sql);
		//4、执行语句集
		rs = pstm.executeQuery();
		while (rs.next()){
			Member instance = new Member();
			instance.setId(rs.getLong("id"));
			instance.setName(rs.getString("name"));
			instance.setAge(rs.getInt("age"));
			instance.setAddr(rs.getString("addr"));
			result.add(instance);
		}
		//5、获取结果集
	}catch (Exception e){
		e.printStackTrace();
	}
	//6、关闭结果集、关闭语句集、关闭连接
	finally {
		try {
			rs.close();
			pstm.close();
			con.close();
		}catch (Exception e){
			e.printStackTrace();
		}
	}
	return result;
}

这是我们在没有使用框架以前的常规操作。随着业务和开发量的增加，我们发现这样在数据持久层这样的重复代码出现频次非常高。因此，我们首先就想到将非功能性代码和业务代码分离。首先我就会想到将ResultSet 封装数据的代码逻辑分离，增加一个mapperRow()方法，专门处理对结果的封装，代码如下：

private static List<Member> select(String sql) {
	List<Member> result = new ArrayList<>();
	Connection con = null;
	PreparedStatement pstm = null;
	ResultSet rs = null;
	try {
		//1、加载驱动类
		Class.forName("com.mysql.jdbc.Driver");
		//2、建立连接
		con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/spring-db-demo","root","123456");
		//3、创建语句集
		pstm =  con.prepareStatement(sql);
		//4、执行语句集
		rs = pstm.executeQuery();
		while (rs.next()){
			Member instance = mapperRow(rs,rs.getRow());
			result.add(instance);
		}
		//5、获取结果集
	}catch (Exception e){
		e.printStackTrace();
	}
	//6、关闭结果集、关闭语句集、关闭连接
	finally {
		try {
			rs.close();
			pstm.close();
			con.close();
		}catch (Exception e){
			e.printStackTrace();
		}
	}
	return result;
}

但在真实的业务场景中，这样的代码逻辑重复率实在太高，上面的改造只能应用Member这个类，换一个实体类又要重新封装，聪明的程序猿肯定不会通过纯体力劳动给每一个实体类写一个mapperRow()方法，一定会想到代码复用方案。我们不妨来做这样一个改造，代码如下：
先创建Member 类：

@Entity
@Table(name="t_member")
@Data
public class Member implements Serializable {
    @Id private Long id;
    private String name;
    private String addr;
    private Integer age;

    @Override
    public String toString() {
        return "Member{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", addr='" + addr + '\'' +
                ", age=" + age +
                '}';
    }
}

对JDBC 操作优化：

public static void main(String[] args) {
	Member condition = new Member();
	condition.setName("Tom");
	condition.setAge(19);
	List<?> result =  select(condition);
	System.out.println(Arrays.toString(result.toArray()));
}

private static List<?> select(Object condition) {

	List<Object> result = new ArrayList<>();

	Class<?> entityClass = condition.getClass();

	Connection con = null;
	PreparedStatement pstm = null;
	ResultSet rs = null;
	try {
		//1、加载驱动类
		Class.forName("com.mysql.jdbc.Driver");
		//2、建立连接
		con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/gp-vip-spring-db-demo?characterEncoding=UTF-8&rewriteBatchedStatements=true","root","123456");

		//根据类名找属性名
		Map<String,String> columnMapper = new HashMap<String,String>();
		//根据属性名找字段名
		Map<String,String> fieldMapper = new HashMap<String,String>();
		Field[] fields =  entityClass.getDeclaredFields();
		for (Field field : fields) {
			field.setAccessible(true);
			String fieldName = field.getName();
			if(field.isAnnotationPresent(Column.class)){
				Column column = field.getAnnotation(Column.class);
				String columnName = column.name();
				columnMapper.put(columnName,fieldName);
				fieldMapper.put(fieldName,columnName);
			}else {
				//默认就是字段名属性名一致
				columnMapper.put(fieldName, fieldName);
				fieldMapper.put(fieldName,fieldName);
			}
		}

		//3、创建语句集
		Table table = entityClass.getAnnotation(Table.class);
		String sql = "select * from " + table.name();

		StringBuffer where = new StringBuffer(" where 1=1 ");
		for (Field field : fields) {
			Object value =field.get(condition);
			if(null != value){
				if(String.class == field.getType()) {
					where.append(" and " + fieldMapper.get(field.getName()) + " = '" + value + "'");
				}else{
					where.append(" and " + fieldMapper.get(field.getName()) + " = " + value + "");
				}
				//其他的，在这里就不一一列举，下半截我们手写ORM框架会完善
			}
		}
		System.out.println(sql + where.toString());
		pstm =  con.prepareStatement(sql + where.toString());

		//4、执行语句集
		rs = pstm.executeQuery();

		//元数据？
		//保存了处理真正数值以外的所有的附加信息
		int columnCounts = rs.getMetaData().getColumnCount();
		while (rs.next()){
			Object instance = entityClass.newInstance();
			for (int i = 1; i <= columnCounts; i++) {
				//实体类 属性名，对应数据库表的字段名
				//可以通过反射机制拿到实体类的说有的字段

				//从rs中取得当前这个游标下的类名
				String columnName = rs.getMetaData().getColumnName(i);
				//有可能是私有的
				Field field = entityClass.getDeclaredField(columnMapper.get(columnName));
				field.setAccessible(true);
				field.set(instance,rs.getObject(columnName));
			}

			result.add(instance);


		}

		//5、获取结果集
	}catch (Exception e){
		e.printStackTrace();
	}
	//6、关闭结果集、关闭语句集、关闭连接
	finally {
		try {
			rs.close();
			pstm.close();
			con.close();
		}catch (Exception e){
			e.printStackTrace();
		}
	}

	return result;
}

巧妙地利用反射机制，读取Class 信息和Annotation 信息，将数据库表中的列和类中的字段进行关联映射并赋值，以减少重复代码。