利用反射和JDBC元数据实现更加通用的查询方法

package com.at221.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

import com.mysql.jdbc.Connection;

public class JDBCTools {
    public static void release(Statement sta,java.sql.Connection con,ResultSet rs){
        if(sta != null){
            try{
                sta.close();
            }catch(Exception e){
                e.getStackTrace();
            }
        }
        
        if(con != null){
            try{
                con.close();
            }catch(Exception e){
                e.getStackTrace();
            }
        }
        
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
            
    }
    
    public static void release(Statement sta,java.sql.Connection con){
        if(sta != null){
            try{
                sta.close();
            }catch(Exception e){
                e.getStackTrace();
            }
        }
        
        if(con != null){
            try{
                con.close();
            }catch(Exception e){
                e.getStackTrace();
            }
        }
            
    }
    
    public static Connection getConnec() throws SQLException, IOException, ClassNotFoundException, 
    InstantiationException, IllegalAccessException{
        String driverClass = null;
        String jdbcUrl = null;
        String user = null;
        String password = null;
        
        //利用配置文件，来更改数据库的配置
        InputStream is = JDBCTools.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties p = new Properties();
        p.load(is);
        driverClass = p.getProperty("driver");
//        System.out.println(driverClass);
        
        jdbcUrl = p.getProperty("jdbcUrl");
//        System.out.println(jdbcUrl);
        
        user = p.getProperty("user");
//        System.out.println(driverClass);
        
        password = p.getProperty("password");
//        System.out.println(password);
        
        Properties info = new Properties();
        info.put("user", user);
        info.put("password", password);
        DriverManager.registerDriver((Driver)Class.forName(driverClass).newInstance());
        
        Connection connec = (Connection)DriverManager.getConnection(jdbcUrl, user, password);
//        System.out.println(connec);
        return connec;
    }
}



package com.at221.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Map;

import org.junit.Test;

import com.mysql.jdbc.ResultSetMetaData;

public class TestReflection {
    @Test
    public void test(){
        String sql = "SELECT id Id,name Name,birth Birth,email Email from student where id = ?";
        Student s = (Student)getStudent(Student.class,sql,3);
        System.out.println(s);
    }
    public <T> Object getStudent(Class<T> clazz,String sql,Object ... args ){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        T entity = null;
        try {
            //1.SQL语句进行配置
            conn = JDBCTools.getConnec();
            ps = conn.prepareStatement(sql);
            //①给SQL语句中的通配符赋值
            
            for(int i = 0; i < args.length; i++){
                ps.setObject(i+1, args[i]);
            }
            //②利用SQL进行查询得到结果集
            rs = ps.executeQuery();
            //2.创建类的对象
            entity = clazz.newInstance();
            //3.利用jdbc元数据来获得列的别名,为map中的键值对进行赋值
            rsmd = (ResultSetMetaData)rs.getMetaData();
            
            Map<String, Object> value = new HashMap<String, Object>();
            if(rs.next()){
                for(int i = 0; i < rsmd.getColumnCount(); i++){
                    String key = rsmd.getColumnLabel(i+1);
                    Object obj = rs.getObject(i+1);
                    value.put(key, obj);
                }    
            }
            //4.利用得到的map进行对创建的对象进行赋值
            if(value.size() > 0){
                for(Map.Entry<String, Object> entry : value.entrySet()){
                    String column = entry.getKey();
                    Object columnValue = entry.getValue();
                    RecflectionUtils.setFieldValue(entity, column, columnValue);
                }
            }
            
            
        } catch(Exception e){
            e.printStackTrace();
        }finally {
            JDBCTools.release(ps, conn);
            
        }
        return entity;
    }
}



package com.at221.jdbc;

import java.sql.Date;

public class Student {
    private int id;
    private String name = null;
    private Date birth = null;
    private String email = null;
    
    public Student(int id, String name, Date birth, String email) {
        super();
        this.id = id;
        this.name = name;
        this.birth = birth;
        this.email = email;
    }

    public Student() {
        super();
    }
    
    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public Date getBirth() {
        return birth;
    }

    public void setBirth(Date birth) {
        this.birth = birth;
    }

    public String getEmail() {
        return email;
    }

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

    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name 
                + ", birth=" + birth + ", email=" + email + "]";
    }
    
    
}