JDBC学习日记--利用ResultSetMetaData +反射实现SQL通用查询

反射基础：

通过反射得到一个类对象

Class<T> clazz = Class.forName("类全名");
T element = clazz.newInstance();

得到反射类对象中的域（field）

Field field = clazz.getDeclareField("域名")；

给指定域名赋值

//改变对应域权限
field.setAccessible(true);
//写值
field.set(element,"值")

SQL方面

准备两个表（为后面的函数公用做准备）：

一个Cat表

一个Employee表

Java方面

准备两个类（对应cat表的cat类，对应employee表的employee类）

Cat类

public class Cat {
 	//编号
 	private int no;
 	
 	 //名字
 	private String catName;
 	
 	 //毛皮颜色
 	private String furColor;
 	
	public Cat() {
 	 	super();
	}
	
	public Cat(int no, String catName, String furColor) {
  		super();
  		this.no = no;
  		this.catName = catName;
  		this.furColor = furColor;
 	}
 	
 	 public int getNo() {
 		 return no;
 	}

 	public void setNo(int no) {
  		this.no = no;
	 }
	 
 	public String getCatName() {
 		 return catName;
	}
	
	public void setCatName(String catName) {
 	 	this.catName = catName;
 	}
 	
 	public String getFurColor() {
 		return furColor;
 	}
 	
 	public void setFurColor(String furColor) {
  		this.furColor = furColor;
	}
	
	@Override
 	public String toString() {
 	 	return "Cat [no=" + no + ", catName=" + catName + ", 	furColor=" + furColor + "]";
 	}
 }

employee类：

public class Employee {
 //编号
 private int no;
 
 //姓名
 private String name

 //身份证
 private String IDCard;
 
 //年度工资
 private double yearSalary;

 public Employee() {
 	super();
 }

 public Employee(int no, String name, String iDCard, double yearSalary) {
  	super();
 	this.no = no;
  	this.name = name;
 	IDCard = iDCard;
  	this.yearSalary = yearSalary;
 }

 public int getNo() {
 	 return no;
 }
 
 public void setNo(int no) {
  	this.no = no;
 }

 public String getName() {
  	return name;
 }

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

 public String getIDCard() {
  	return IDCard;
 }

 public void setIDCard(String iDCard) {
  	IDCard = iDCard;
 }

 public double getYearSalary() {
 	 return yearSalary;
 }

 public void setYearSalary(double yearSalary) {
  	this.yearSalary = yearSalary;
 }

 @Override
 public String toString() {
  	return "Employee [no=" + no + ", name=" + name + ", IDCard=" + IDCard + ", yearSalary=" + yearSalary + "]";
 }
 
}

得到需求类

 public static <T> T getTargett(Class<T> clazz,String sql, Object...args) {
 	 T entity = null;
 	 
  	Connection conn = null;
  	PreparedStatement ps = null;
  	ResultSet res = null;
	try {
  		conn = JDBCTools.getConnection();
   
   		//得到preparedStatement对象
   		ps = conn.prepareStatement(sql);
   
   		//给占位符赋值
   		for (int i = 0; i < args.length; i++) {
    			ps.setObject(i+1,args[i]);
  		 }
   
   		//执行SQL
  		 res = ps.executeQuery();
		
		//定义map集合
   		Map<String, Object> values = new HashMap<String, Object>();
   
   		//定义ResultSetMetaData对象
   		ResultSetMetaData rsmd = res.getMetaData();
		
		while(res.next()) {
    			//利用反射创建对象
    			entity = clazz.newInstance();
    
    			for (int i = 0; i < rsmd.getColumnCount(); i++) {
    			 //得到数据项标签
     			String columnLabel = rsmd.getColumnLabel(i+1);
     			
     			//得到数据项 的值
    			 Object columnValue = res.getObject(i+1);
     
     			//将键值对放入集合
    			 values.put(columnLabel, columnValue);
   			 }
  		 }
		
		//若value不为空，则用反射为clazz创建对象
   		if(values.size() > 0) {
    			entity = clazz.newInstance();
    
    			//遍历map，为对应属性列赋值
    			for (Map.Entry<String , Object> entry : values.entrySet()) {
     				//得到键
    				 String key = entry.getKey();

				//得到值
     				Object value = entry.getValue();
     
     				//给属性赋值
     				Field field = clazz.getDeclaredField(key);
     				field.setAccessible(true);
     				field.set(entity, value);
     				}
     			}
		} catch (Exception e) {
  		 e.printStackTrace();
  		} finally {
  		 JDBCTools.closeResource(conn, ps, res);
 		 }
		
		return entity;
	}


     			

主函数测试

public static void main(String[] args) throws Exception{
	String sql = "select no,name,ID_Card IDCard,year_Salary 	yearSalary "
   	 + "from employee where no = ?";
  
 	 Employee employee = JDBCTest.getObject(Employee.class, sql, 1);
  	System.out.println(employee);
  
  	String sql2 = "Select no,cat_Name catName,fur_Color furColor "
    + "from cat where no = ?";
  	Cat cat = JDBCTest.getObject(Cat.class, sql2, 1);
  	System.out.println(cat);
  }

得到输出结果如下：