简单的关系型数据库返回数据封装成类

这是将jdbc封装成一个只需要输入sql语句和Class对象，就能返回对象实例的工具类，提升非sql框架开发的效率。

首先如果你是j2ee项目，以下类放入resource文件。非j2ee则直接放在src文件夹下。因为要考虑到类加载器的扫描，类似于springboot项目的yml文件。这里面配置数据库数据

我用的是oracle数据库，如果你用的mysql，就改一下配置就好了。=左边的键名该不该无所谓，反正只是标识

db.properties

oracleurl = jdbc:oracle:thin:@localhost:1521:orcl
oracleuname = scott
oraclepwd = 1234

Properties是jdk提供的专门用来读取键值对文件的类，继承一下，自动读取配置文件的数据

DbProperties

public class DbProperties extends Properties {
 
    private static DbProperties dbProperties;
 
    private DbProperties(){
        //初始化db.properties文件
        InputStream iis = DbProperties.class.getClassLoader().getResourceAsStream("db.properties");
 
        try {
            this.load(iis);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
 
    public static DbProperties getInstance(){
        //懒汉单例
        if (dbProperties == null){
            dbProperties = new DbProperties();
        }
        return dbProperties;
    }
}

dbHelper

public class DbHelper {
    public Connection getConnection() throws Exception {
        DbProperties db = DbProperties.getInstance();
        Connection con = DriverManager.getConnection(db.getProperty("oracleurl"),db.getProperty("oracleuname"),db.getProperty("oraclepwd"));
        return con;
    }
 
    /**
     * 基于模板设计模式的查询方法
     * @param rowMapper 对一行结果集的除了，返回一个对应的对象
     * @param sql
     * @param params
     * @return
     * @param <T>
     */
    public <T> List<T> select(RowMapper<T> rowMapper,String sql,Object... params){
        List<T> list = new ArrayList<>();
        try (Connection con = getConnection();
        PreparedStatement pstmt = con.prepareStatement(sql)){
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i+1,params[i]);
            }
            ResultSet resultSet = pstmt.executeQuery();
            int num=0;
            while (resultSet.next()){
                T t = rowMapper.mapRow(resultSet,num); //结果集每一行的处理，由RowMapper接口的实现决定
                num++;
                list.add(t);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }
 
 
    /**
     * 更新方法
     * @param sql  sql语句
     * @param params  可变数组，统一用Object方便点
     * @return
     */
    public int update(String sql,Object... params){
        int result=0;
        DbProperties db = DbProperties.getInstance();
        try (Connection con = DriverManager.getConnection(db.getProperty("oracleurl"),db.getProperty("oracleuname"), db.getProperty("oraclepwd"));
             PreparedStatement pstmt = con.prepareStatement(sql)){
            //sql已经预编译
            //开始对?进行修改
 
            if (params != null && params.length>0){
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i+1,params[i]);
                }
            }
            result = pstmt.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
        return result;
    }
 
    /**
     * 查询方法
     * @param sql  sql语句
     * @param params  可变数组，统一用Object方便点
     * @return
     */
    public List<Map<String,Object>> select(String sql, Object... params){
        List<Map<String,Object>> list = new ArrayList<>();
        DbProperties db = DbProperties.getInstance();
        try (Connection con = DriverManager.getConnection(db.getProperty("oracleurl"),db.getProperty("oracleuname"), db.getProperty("oraclepwd"));
             PreparedStatement pstmt = con.prepareStatement(sql)){
            //sql已经预编译
            //开始对?进行修改
 
            //得到结果集元数据
            ResultSetMetaData metaData = pstmt.getMetaData();
 
            //有几条列
            //有?就改了
            int columnCount = metaData.getColumnCount();
            if (params != null && params.length>0){
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i+1,params[i]);
                }
            }
 
            //没有也要执行
            ResultSet rs = pstmt.executeQuery();
 
            //对rs进行优化
            while (rs.next()){
                //每行都按照一个map放入list集合
                Map<String,Object> map = new HashMap<>();
                //按列找名字，放进去
                for (int i = 0; i < columnCount; i++) {
                    map.put(metaData.getColumnName(i+1),rs.getObject(i+1));
                }
                list.add(map);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }
 
    public <T> List<T> select(Class<T> c,String sql,Object... params){
        List<T> list = new ArrayList<>();
 
        List<Map<String,Object>> maps = new ArrayList<>();
 
        try (Connection con = getConnection();
        PreparedStatement pstmt = con.prepareStatement(sql);){
            //设置参数
            for (int i = 0; i < params.length; i++) {
                pstmt.setObject(i+1,params[i]);
            }
            //查询
            ResultSet result = pstmt.executeQuery();
 
            //得到数据集元数据  一行
            ResultSetMetaData metaData = result.getMetaData();
 
            //一行多少列
            int columnCount = metaData.getColumnCount();
 
            //遍历元素集
            while (result.next()){
                //每个元数据都插入到map
                Map<String,Object> map = new HashMap<>();
                for (int i = 0; i < columnCount; i++) {
                    Object rs = result.getObject(i + 1);
                    //顺便把键改成首字母大写
                    map.put(metaData.getColumnName(i+1).substring(0,1).toUpperCase()+metaData.getColumnName(i+1).substring(1).toLowerCase(),rs);
                }
                maps.add(map);
            }
 
            //开始搞对象
            //遍历maps把所有的值都注入到set方法
            for (Map<String, Object> map : maps) {
                T t = c.newInstance();
 
                //所有的方法
                Method[] methods = c.getDeclaredMethods();
 
                //得到所有的set方法
                List<Method> sets = getSets(methods);
 
                Set<Map.Entry<String, Object>> set = map.entrySet();
                Iterator<Map.Entry<String, Object>> iterator = set.iterator();
                while (iterator.hasNext()){
                    Map.Entry<String, Object> entry = iterator.next();
                    String key = entry.getKey();
                    Object value = entry.getValue();
                    //找对应的方法
                    Method method = getSet(sets,key);
 
                    //该方法第0位一定是对应的属性
                    Class<?> parameterType = method.getParameterTypes()[0];
                    String name = parameterType.getTypeName();
                    if (name.equals("int") || name.equals("java.lang.Integer")){
                        method.invoke(t,Integer.parseInt(value.toString()));
                    } else if (name.equals("short") || name.equals("java.lang.Short")){
                        method.invoke(t,Short.parseShort(value.toString()));
                    }else if (name.equals("double") || name.equals("java.lang.Double")){
                        method.invoke(t,Double.parseDouble(value.toString()));
                    }else if (name.equals("float") || name.equals("java.lang.Float")){
                        method.invoke(t,Float.parseFloat(value.toString()));
                    }else if (name.equals("long") || name.equals("java.lang.Long")){
                        method.invoke(t,Integer.parseInt(value.toString()));
                    }else if (name.equals("byte") || name.equals("java.lang.Byte")){
                        method.invoke(t,Byte.parseByte(value.toString()));
                    }else if (name.equals("boolean") || name.equals("java.lang.Boolean")){
                        method.invoke(t,Boolean.parseBoolean(value.toString()));
                    }else if (name.equals("java.lang.String")){
                        method.invoke(t,value.toString());
                    }else {
                        method.invoke(t,value);
                    }
                }
                list.add(t);
            }
 
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return list;
    }
 
    private Method getSet(List<Method> sets, String key) {
        for (int i = 0; i < sets.size(); i++) {
            if (sets.get(i).getName().equals("set"+key)){
                return sets.get(i);
            }
        }
        return null;
    }
 
    private List<Method> getSets(Method[] methods) {
        List<Method> list = new ArrayList<>();
        for (int i = 0; i < methods.length; i++) {
            if (methods[i].getName().startsWith("set")){
                list.add(methods[i]);
            }
        }
        return list;
    }
}

测试代码

public class jdbcTest {
    public static void main(String[] args) throws Exception {
        DbHelper dbHelper = new DbHelper();
 
        List<Dept> select = dbHelper.select(Dept.class, "select * from dept");
        select.forEach(s-> System.out.println(s));
 
        /*//更新
        int result = dbHelper.update("update dept set dname=? where deptno=?", "你猜",54);
        System.out.println("影响"+result+"条数据");
        //查询
        List<Map<String, Object>> list = dbHelper.select("select * from dept");
        list.forEach(s-> System.out.println(s));
        // 转换键为小写
        for (Map<String, Object> map : list) {
            Map<String, Object> newMap = new HashMap<>();
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                newMap.put(entry.getKey().toLowerCase(), entry.getValue());
            }
            list.set(list.indexOf(map), newMap);
        }
        // 打印转换后的列表
        for (Map<String, Object> map : list) {
            System.out.println(map);
        }
        //用不了应该是键名是大写，导致找不到方法set方法
        List<Object> objects = GenerateObject.parseManyObjects(list, Dept.class);
        objects.forEach(s-> System.out.println(s));*/
    }
}

Dept类

public class Dept {
    private int deptno;
    private String dname;
    private String loc;
 
    public Dept() {
    }
 
    public Dept(int deptno, String dname, String loc) {
        this.deptno = deptno;
        this.dname = dname;
        this.loc = loc;
    }
 
    @Override
    public String toString() {
        return "Dept{" +
                "deptno='" + deptno + '\'' +
                ", dname='" + dname + '\'' +
                ", loc='" + loc + '\'' +
                '}';
    }
 
    public int getDeptno() {
        return deptno;
    }
 
    public void setDeptno(int deptno) {
        this.deptno = deptno;
    }
 
    public String getDname() {
        return dname;
    }
 
    public void setDname(String dname) {
        this.dname = dname;
    }
 
    public String getLoc() {
        return loc;
    }
 
    public void setLoc(String loc) {
        this.loc = loc;
    }
}