Java反射根据实体类与数据对象生成插入、更新sql语句

╰半夏微凉° 2022-04-24 13:42 278阅读 0赞

优化后 ：[https://gitee.com/ren\_hq/codes/gyl8dteinv3x07b4hzfmu31][https_gitee.com_ren_hq_codes_gyl8dteinv3x07b4hzfmu31]

需要导入hutools的反射工具包：[https://gitee.com/ren\_hq/codes/gyl8dteinv3x07b4hzfmu31/raw?blob\_name=hutool-all-4.4.5.jar][https_gitee.com_ren_hq_codes_gyl8dteinv3x07b4hzfmu31_raw_blob_name_hutool-all-4.4.5.jar]

也可到上面第一个页面中下载

来源：[https://gitee.com/haust\_lyb/codes/kzjvldbp170q8emfsn2xa26][https_gitee.com_haust_lyb_codes_kzjvldbp170q8emfsn2xa26]

添加了更新语句中id小写时的情况，修改update语句只生成数据对象中不为空值得操作：

/**
         * 生成插入语句
         * @param tablename 表明
         * @param clazz 与数据库中字段一一对应的类
         * @param t 有数据的实体
         * @param <T> 数据实体类型 如 User
         */
        public static  <T> String getInsertSql(String tablename, Class<T> clazz, T t){
            //insert into table_name (column_name1,column_name2, ...) values (value1,value2, ...)
            String sql = "";
            Field[] fields = ReflectUtil.getFieldsDirectly(clazz, false);
            StringBuffer topHalf = new StringBuffer("insert into "+tablename+" (");
            StringBuffer afterAalf = new StringBuffer("values (");
            for (Field field : fields) {
                if ("ID".equals(field.getName()) || "id".equals(field.getName())){
                    continue;   //id 自动生成无需手动插入
                }
                topHalf.append(field.getName() + ",");
                if (ReflectUtil.getFieldValue(t, field.getName()) instanceof String) {
                    afterAalf.append("'" + ReflectUtil.getFieldValue(t, field.getName()) + "',");
                } else {
                    afterAalf.append(ReflectUtil.getFieldValue(t, field.getName()) + ",");
                }
            }
            topHalf = new StringBuffer(StrUtil.removeSuffix(topHalf.toString(), ","));
            afterAalf = new StringBuffer(StrUtil.removeSuffix(afterAalf.toString(), ","));
            topHalf.append(") ");
            afterAalf.append(") ");
            sql = topHalf.toString() + afterAalf.toString();
            return sql;
        }
    
        /**
         * 生成更新语句
         * 必须含有id
         * 数据实体中 null 与 空字段不参与更新
         * @param tablename 数据库中的表明
         * @param clazz 与数据库中字段一一对应的类
         * @param t 有数据的实体
         * @param <T> 数据实体类型,如 User
         */
        public static  <T> String getUpdateSql(String tablename, Class<T> clazz, T t){
            //UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... where ID=xxx
            //or
            //UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... where id=xxx
            String sql = "";
            String id = ""; //保存id名：ID or id
            Field[] fields = ReflectUtil.getFieldsDirectly(clazz, false);
            sql = "update "+tablename+" set ";
            for (Field field : fields) {
                StringBuffer tmp = new StringBuffer();
                if ("ID".equals(field.getName()) || "id".equals(field.getName())){
                    id = field.getName();
                    continue;//更新的时候无需set id=xxx
                }
                if (ReflectUtil.getFieldValue(t, field.getName()) != null && ReflectUtil.getFieldValue(t, field.getName()) != "") {
                    tmp.append( field.getName() + "=");
                    if (ReflectUtil.getFieldValue(t, field.getName()) instanceof String) {
                        tmp.append( "'" + ReflectUtil.getFieldValue(t, field.getName()) + "',");
                    } else {
                        tmp.append(ReflectUtil.getFieldValue(t, field.getName()) + ",");
                    }
                    sql += tmp;
                }
            }
            sql = StrUtil.removeSuffix(sql, ",") + " where " + id + "='" + ReflectUtil.getFieldValue(t, id)+"'";
            return sql;
        }
    
    
        /**
         * //根据数据库生成字段 例如 private Object a;
         * @param dbname 数据库名
         * @param tablename 表名称
         * @return 成员变量拼接后的字符串
         * @throws SQLException
         */
        public static String getPirvateObjectXxx(String dbname,String tablename) throws SQLException {
            Connection conn = /*获取你的数据库连接*/;
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("select COLUMN_NAME,COLUMN_TYPE from information_schema.COLUMNS where table_name = '"+tablename+"' and table_schema = '"+dbname+"'");
            StringBuffer sb=new StringBuffer();
            while (rs.next()) {
                sb.append("private Object "+rs.getObject(1)+";\n");
            }
            System.out.print(sb.toString());
            rs.close();
            stat.close();
            conn.close();
            return sb.toString();
        }

测试：

getInsertSql生成insert语句测试
    public static void main(String[] args) {
            User user = new User();
            user.setUsername("username"); user.setPassword("123"); user.setId(1L);
            String insertSql = getInsertSql("user", User.class, user);
            System.out.println(insertSql);
    
        }
    
    输出：
    insert into user (username,password) values ('username','123')

//getUpdateSql 生成update语句测试
    public static void main(String[] args) {
            User user = new User();
            user.setUsername("username"); user.setPassword("123"); user.setId(1L);
            String insertSql = getUpdateSql("user", User.class, user);
            System.out.println(insertSql);
    
        }
    
    输出：
    update user set username='username',password='123' where id='1'

getPirvateObjectXxx() 方法根据数据库表的字段生成属性变量的声明：

// 数据库名：hims  表名： user
    System.out.println(getPirvateObjectXxx("hims", "user"));
    
    输出：
    private Object id;
    private Object username;
    private Object password;
    ...
    private Object email;

[https_gitee.com_ren_hq_codes_gyl8dteinv3x07b4hzfmu31]: https://gitee.com/ren_hq/codes/gyl8dteinv3x07b4hzfmu31
[https_gitee.com_ren_hq_codes_gyl8dteinv3x07b4hzfmu31_raw_blob_name_hutool-all-4.4.5.jar]: https://gitee.com/ren_hq/codes/gyl8dteinv3x07b4hzfmu31/raw?blob_name=hutool-all-4.4.5.jar
[https_gitee.com_haust_lyb_codes_kzjvldbp170q8emfsn2xa26]: https://gitee.com/haust_lyb/codes/kzjvldbp170q8emfsn2xa26