JDBC结合反射实现查询封装,返回对应类型对象的集合

结合反射实现查询封装,返回对应类型对象的集合

现有表t_user:

先建JDBC工具类，JDBCUtils.java

public class JDBCUtils {
    private static Properties pro=new Properties();
    static{
        try {
            pro.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("db.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            Class.forName(pro.getProperty("driver"));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        Connection conn=null;
        conn=DriverManager.getConnection(
                pro.getProperty("url"),
                pro.getProperty("username"),
                pro.getProperty("password")
        );
        return conn;
    }
    public static void release(Connection conn,Statement state,ResultSet result){
        if(result!=null){
            try {
                result.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(state!=null){
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void release(Connection conn, Statement state){
        release(conn,state,null);
    }
}

User.java

public class User {
    private String name;
    private String password;

    public User() {
    }

    public User(String name, String password) {
        this.name = name;
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "name='" + name + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

QueryDao.java 获取连接数据库，并执行sql语句，用反射创建对应的对象

/*
结合反射实现查询封装,返回对应类型对象的集合
 */
public class QueryDao {

    //查询操作
    public List testUpdate(String sql,Class cls,Object ... args){
        Connection conn = null;
        PreparedStatement state = null;
        ResultSet resultSet=null;
        ResultSetMetaData data=null;
        List<Object> list=new ArrayList<>();

        //1.获取连接
        try {
            conn = JDBCUtils.getConnection();
            //2.封装预处理块
            state = conn.prepareStatement(sql);

            //3.赋值
            if(args!=null && args.length!=0){
                for(int i = 0;i<=args.length-1;i++){
                    state.setObject(i+1,args[i]);
                }
            }

            //4.执行
            resultSet = state.executeQuery();
            data = resultSet.getMetaData();
            int count = data.getColumnCount();
            data.getColumnLabel(1);
            while(resultSet.next()){
                Object o = cls.getConstructor().newInstance();
                for (int i = 1; i <=count ; i++) {
                    String label = data.getColumnLabel(i);
                    Field field = cls.getDeclaredField(label);
                    String str=field.getName();
                    String s = str.substring(0, 1).toUpperCase() + str.substring(1);
                    Method method = cls.getMethod("set" + s, field.getType());
                    method.invoke(o,resultSet.getObject(i));
/*                    field.setAccessible(true);
                    field.set(o,resultSet.getObject(i));*/
                }
                list.add(o);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }  finally {
            JDBCUtils.release(conn,state,resultSet);
        }

        //返回
        return list;
    }
}

AppTest.java 测试

public class AppTest {
    public static void main(String[] args) {
        QueryDao query = new QueryDao();
        User user=new User();
        List list = query.testUpdate("select username \"name\",password \"password\" from t_user ",User.class);
        list.forEach(System.out::println);
    }
}

本次实验需要用到ojdbc6.jar,记得导入包，用的数据库是Oracle.

运行截图：