Java EE 查询 将数据库中对应的表逐行转换成DAO对应的实体类。

4/14/2017 3:20:05 PM

Java EE 查询 将数据库中对应的表逐行转换成DAO实体类。List方式返回。自动赋值时忽略大小写的名称进行匹配，自动赋值到指定的实体类中。

需要导入mysql-connector-java-xxxx-bin.jar包

直接上代码:

package com.hbbc.test;

import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;


public class DBUtil {

public Connection openConnection(String propertiesName){
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Properties prop=new Properties();
        prop.load(DBUtil.class.getClassLoader().getResourceAsStream(propertiesName!=""?propertiesName:"main.properties"));
        String url=prop.getProperty("url");
        String username=prop.getProperty("username");
        String password=prop.getProperty("password");
        return DriverManager.getConnection(url, username, password);
    } catch (ClassNotFoundException | IOException | SQLException e) {
        e.printStackTrace();
    }
    return null;
}


@SuppressWarnings("unchecked")
public static void main(String[] args) {
    String sql="SELECT * FROM testdb";
    ArrayList<UserBean> beanList=(ArrayList<UserBean>) DBUtil.QueryDB(sql, UserBean.class);

    for (int i = 0; i < beanList.size(); i++) {
        System.out.println("id:"+beanList.get(i).getId()+",age:"+beanList.get(i).getAge()+",username:"+beanList.get(i).getUserName()+",sex:"+beanList.get(i).isSex()+",addTime:"+beanList.get(i).getAddTime());
    }
}


/**
 * 
 * @param sql 查询语句
 * @param clazz 实体类型
 * @return  返回的list类型，需强制转换
 */
public static List<?> QueryDB(String sql,Class<?> clazz){
    DBUtil dbu=new DBUtil();
    Connection conn=dbu.openConnection("");
    ArrayList<Object> resultlist=new ArrayList<>();
    try {
            java.sql.PreparedStatement pst=conn.prepareStatement(sql);
            Statement stmt=conn.createStatement();
            ResultSet rs=stmt.executeQuery(sql); 
            ResultSetMetaData rsd=pst.executeQuery().getMetaData();

            pst=conn.prepareStatement(sql);

            //获取反射对象中的属性集合
            Field[] field=clazz.getDeclaredFields();


            while (rs.next()) {
                Object obj=clazz.newInstance();//反射对对象实例化

                //数据表中列数,获取列名称下标从第1个开始遍历
                for (int k = 1; k <= rsd.getColumnCount(); k++) {

                    //当前类中属性的数量
                for (int j = 0; j < field.length; j++) {
                        //获取数据表中当前列明
                        String columnName=rsd.getColumnName(k);
                        //如果当前数据表列中的名称和类中属性名称相同，就执行属性赋值方法
                        if (columnName.equalsIgnoreCase(field[j].getName())) {
                            //System.out.println("field.length:"+field.length);
                            //属性的类型
                            String typeName=field[j].getGenericType().toString();

                            //以下是常用的类型
                            if (("class java.lang.String").equals(typeName)) {
                                //属性赋值
                                Method m=clazz.getMethod("set"+columnName, String.class);
                                m.invoke(obj,rs.getString(columnName));
                            }else if (("int").equals(typeName)) {
                                Method m=clazz.getMethod("set"+columnName, int.class);
                                m.invoke(obj,rs.getInt(columnName));
                            }else if(("boolean").equals(typeName)){
                                Method m=clazz.getMethod("set"+columnName, boolean.class);
                                m.invoke(obj,rs.getBoolean(columnName));
                            }else if ("class java.util.Date".equals(typeName)) {
                                Method m=clazz.getMethod("set"+columnName, Date.class);
                                m.invoke(obj,rs.getDate(columnName));
                            }

//                              System.out.print("typeName:"+typeName+",Name:"+field[j].getName()+",ColumnName:"+columnName);
//                              System.out.println("---------------执行完");
                        }
                    }
                }
                resultlist.add(obj);
            }
            return resultlist;

    } catch (SQLException e) {
        System.out.println("数据库连接失败");
        e.printStackTrace();
    } catch (NoSuchMethodException e) {
        System.out.println("没有这个方法");
        e.printStackTrace();
    } catch (SecurityException e) {
        e.printStackTrace();
    } catch (IllegalAccessException e) {
        System.out.println("获取不到数据表中当前的值");
        e.printStackTrace();
    } catch (IllegalArgumentException e) {
        System.out.println("反射，传递参数错误");
        e.printStackTrace();
    } catch (InvocationTargetException e) {
        e.printStackTrace();
    } catch (InstantiationException e) {
        System.out.println("无法对该类newInstance");
        e.printStackTrace();
    }
    return null;
}

}

---

如下是实体类:

package com.hbbc.test;

import java.util.Date;

public class UserBean {


private String userName;

private int id;

private int age;

private boolean sex;

private Date addTime;

public String getUserName() {
    return userName;
}

public void setUserName(String userName) {
    this.userName = userName;
}

public int getId() {
    return id;
}

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

public int getAge() {
    return age;
}

public void setAge(int age) {
    this.age = age;
}

public boolean isSex() {
    return sex;
}

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

public Date getAddTime() {
    return addTime;
}

public void setAddTime(Date addTime) {
    this.addTime = addTime;
}

}

Demo查看：

Demo下载