自动为类生成sql语句

朱雀 2024-04-23 21:18 62阅读 0赞

*  工作原因要用到原生sql，但是类里的属性太多了，写起来简直要命，所以写了个insert、update的代码生成器，具体代码如下：

package com.example.batis.Utils;
    
    import java.lang.reflect.Field;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.stream.Collectors;
    
    /**
     * @Project: hello-wolrd
     * @Package: com.example.batis.Utils
     * @Class: BreezeUtils
     * @Author: l22966
     * @Date: 2020/12/15 10:16
     * @Version:1.0
     * @Description:
     */
    public class BreezeUtils {
        
    
        /**
         * 根据全路径类名生成更新、插入sql语句
         * @throws ClassNotFoundException
         */
        private static void generateSql() throws ClassNotFoundException {
        
            //是否有父类
            Boolean isSon = false;
            //类名
            String className = "BaseSetting";
            //类路径名
            String load = "com.h3c.meetingmanagement.bean.entity";
            Class clazz = Class.forName(load + "." + className);
            Field[] declaredFields = clazz.getDeclaredFields();
            List<String> column = new ArrayList();
            for (Field declaredField : declaredFields) {
        
                column.add(declaredField.getName());
            }
    
            //获取父类的属性
            if(isSon){
        
                Class superClazz = clazz.getSuperclass();
                Field[] superDeclaredFields = superClazz.getDeclaredFields();
                for (Field superDeclaredField : superDeclaredFields) {
        
                    column.add(superDeclaredField.getName());
                }
            }
    
            //删掉不要的属性
            if(column.contains("idWorker")){
        
                column.remove("idWorker");
            }
    
            //形如#{mysqlColumn}的列表
            List<String> column1 = column.stream().map(u -> "#{" + u + "}").collect(Collectors.toList());
            //形如mysql_column的列表
            List<String> column2 = column.stream().map(BreezeUtils::changeToMysqlForm).collect(Collectors.toList());
            //类名转表名
            className = changeToMysqlForm(className.replaceFirst(className.charAt(0) + "", className.toLowerCase().charAt(0) + ""));
    
            //生成insert语句
            String insert = "insert into " + className + "(" + column2.toString() + ") values(" + column1.toString() + ")";
            insert = insert.replace("[", "").replace("]", "");
    
            //生成update语句
            String update = "update " + className + " set ";
            column1.remove("#{id}");
            column2.remove("id");
            for (int i = 0; i < column1.size(); i++) {
        
                update += column2.get(i) + "=" + column1.get(i) + ",";
            }
            update = update.substring(0, update.length() - 1) + " ";
            update += "where id = #{id}";
    
            System.out.println(insert);
            System.out.println(update);
        }
    
        /**
         * 驼峰转下划线
         * @param column
         * @return
         */
        private static String changeToMysqlForm(String column){
        
            StringBuilder stringBuilder = new StringBuilder(column);
            StringBuilder ret = new StringBuilder();
            for (int i = 0; i < stringBuilder.length(); i++) {
        
                if(stringBuilder.charAt(i)>='A'&&stringBuilder.charAt(i)<='Z'){
        
                    ret.append("_").append((char) (stringBuilder.charAt(i)+32));
                }else {
        
                    ret.append(stringBuilder.charAt(i));
                }
            }
            return ret.toString();
        }
        
    }

*  只要设定好类名和类所对应的路径名，就可以生成update和insert的sql语句，如果有父类的话需要把isSon字段设定成true(正经项目应该都会有一个用来存主键的父类)。如果属性中定义了一些数据库里不存的工具属性，模仿相应代码去掉就可以了。