JDBC 将数据库字段映射实体类中

实体类

public class Urls {
    private long id;
    private String province;
    private String city;
    private String url;

    @Override
    public String toString() {
        return "Urls{" +
                "id=" + id +
                ", province='" + province + '\'' +
                ", city='" + city + '\'' +
                ", url='" + url + '\'' +
                '}';
    }

    public Urls() {
    }

    public Urls(int id, String province, String city, String url) {
        this.id = id;
        this.province = province;
        this.city = city;
        this.url = url;
    }

    public long getId() {
        return id;
    }

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

    public String getProvince() {
        return province;
    }

    public void setProvince(String province) {
        this.province = province;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }
}

 BaseDao类

存在实体类，字段对应

通用的可对任意表进行实体类存储，返回list

    /**
     * 获取 查询的数据封装为集合
     * @param clazz 对应类的class对象
     * @param sql   要执行的sql
     * @param args  要传递的参数
     * @param <T>   具体的类型
     * @return
     */
    public <T> List<T> getAll(Class<T> clazz,String sql,Object... args) throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException, NoSuchFieldException {
        //创建存储所有数据的集合
        List<T> list=new ArrayList<>();
        //获取连接
        Connection connection = JDBCUtils.getConnection();
        //准备命令发送器
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //获取原数据
        ResultSetMetaData metaData = preparedStatement.getMetaData();
        //获取源数据共有多少列
        int columnCount = metaData.getColumnCount();

        //设置值
        if (args!=null&&args.length>0){
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
        }
        //执行命令 获取结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        //遍历结果集
        while (resultSet.next()){
            //使用反射创建对象
            T instance = clazz.newInstance();
            //将数据封装为对象
            for (int i = 0; i < columnCount; i++) {     //循环一次给一个属性赋值
                //获取字段名
                String columnLabel = metaData.getColumnLabel(i + 1);
                //获取对应的字段值
                Object value = resultSet.getObject(columnLabel);
                //获取对象对应的属性
                Field field = clazz.getDeclaredField(columnLabel);
                //设置私有可见
                field.setAccessible(true);
                //将数据库中的值变为对象的属性值
                field.set(instance, value);
            }
            list.add(instance);
            //将对象添加到集合内
        }
        //关闭资源
        JDBCUtils.closeResources(connection,preparedStatement,resultSet);
        return list;
    }

 不存在实体类属性对应，结果存在avg（）等函数查询的值

使用map存储每一行的值，放到list返回。

    public List<Map<String,Object>> mapToList(String sql,Object... args) throws SQLException, IOException, ClassNotFoundException {
        //创建集合
        List<Map<String,Object>> list=new ArrayList<>();
        //获取连接
        Connection connection = JDBCUtils.getConnection();
        //创建命令发送器
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //设置值
        if (args!=null&&args.length>0){
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
        }
        //获取原数据
        ResultSetMetaData metaData = preparedStatement.getMetaData();
        int columnCount = metaData.getColumnCount();
        //执行命令，获取数据集
        ResultSet resultSet = preparedStatement.executeQuery();
        //遍历结果集
        while (resultSet.next()){
            Map<String,Object> map=new TreeMap<>();
            for (int i = 0; i < columnCount; i++) {
                //获取列名
                String columnName = metaData.getColumnName(i + 1);
                //获取数据
                Object value = resultSet.getObject(columnName);
                //添加到map
                map.put(columnName,value);
            }
            //将map添加到list
            list.add(map);
        }
        //关闭资源
        JDBCUtils.closeResources(connection,preparedStatement,resultSet);
        //返回list
        return list;

    }

BaseDaoTest测试类

public class BaseDaoTest {
    @Test
    public void test02() throws SQLException, IOException, NoSuchFieldException, ClassNotFoundException, InstantiationException, IllegalAccessException {
        BaseDao baseDao=new BaseDao();
        String sql="select * from urls";
        List<Urls> all = baseDao.getAll(Urls.class, sql, null);
        all.forEach(System.out::println);
    }

}

踩坑日记

mysql中id为无符号整型，实体类使用int型会报参数错误，需要使用long型来接收，附mysql字段类型和java对应表：