根据JPA实体类生成对应数据的sql文件

梦里梦外; 2022-10-07 06:00 199阅读 0赞

### 文章目录 ###

*  用到的依赖
 *  测试用的model类
 *  生成sql的工具类
 *  测试
 *  总结

# 用到的依赖 #

示例中用到的lombok依赖自动生成get,set，如不需要也可自行手动生成get,set方法

<dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
            </dependency>
             <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jpa</artifactId>
            </dependency>

# 测试用的model类 #

下面用生成用户对应的sql脚本为例.

@Data
    @Inheritance(strategy=InheritanceType.TABLE_PER_CLASS)//选择继承策略
    @MappedSuperclass
    @EntityListeners(AuditingEntityListener.class)
    public  class BaseModel implements Serializable { 
    
        private static final long serialVersionUID = 2863256929817929825L;
    
        /** * id主键，需要手动set Id, 推荐使用雪花算法生成的id， uuid作为主键查询效率低 */
        @Id
        private Long id;
    
        /** * 记录创建时间,自动 */
        @CreatedDate
        private Date createTime;
    
        /** * 记录修改时间,自动 */
        @LastModifiedDate
        private Date lastmodifiedTime;
    
    }
    @Entity
    @Table(name = "sys_user")
    public class User extends BaseModel { { 
        @Id
        private Integer id;
        private String username;
        @Column(name = "pwd")
        private String password;
        private Integer roleId;
        private boolean deleted;
        @OneToOne(fetch = FetchType.LAZY)
        @JoinColumn(name="roleId",referencedColumnName="id",insertable = false, updatable = false)
        @JsonIgnore
        private Role role;
    }
    @Table(name = "sys_role")
    public class Role extends BaseModel {     
    }

# 生成sql的工具类 #

@Slf4j
    public class CreateInsertSqlUtils { 
    
        public static String createinsert(Object entity) { 
            StringBuilder rootBuilder = new StringBuilder("insert into ");
            StringBuilder columnBuilder = new StringBuilder();
            StringBuilder valueBuilder = new StringBuilder();
            List<SqlColumn> list = getFiledsInfo(entity);
            //获取@Table注解里的表名称 例如 @Table(name="sys_user"）
            Table annotation = entity.getClass().getAnnotation(Table.class);
            rootBuilder.append(annotation.name()).append(" ");
            for (SqlColumn sqlColumn : list) { 
                columnBuilder.append(sqlColumn.name).append(",");
                //如果类型是数值类型不用加单引号
                if (sqlColumn.clz.getSuperclass() == Number.class) { 
                    valueBuilder.append(sqlColumn.value).append(",");
                } else { 
                    valueBuilder.append("'").append(sqlColumn.value).append("',");
                }
            }
            columnBuilder.deleteCharAt(columnBuilder.length() - 1);
            valueBuilder.deleteCharAt(valueBuilder.length() - 1);
            rootBuilder.append("(").append(columnBuilder.toString()).append(") values (");
            rootBuilder.append(valueBuilder.toString()).append(");");
            return rootBuilder.toString();
        }
    
    
        /** * 获取类的字段名称和值 */
        @SuppressWarnings("unused")
        protected static List<SqlColumn> getFiledsInfo(Object object) { 
    
            Class tempClass = object.getClass();
            String obj_name = tempClass.getSimpleName().toString();
    
            LinkedList<Field> fieldList = new LinkedList<>();
            //当父类为null的时候说明到达了最上层的父类(Object类).
            boolean isSuper = false;
            while (tempClass != null && !tempClass.getName().toLowerCase().equals("java.lang.object")) { 
                if (isSuper) { 
                    Field[] fields = tempClass.getDeclaredFields();
                    //把父类的属性插入到前面,一般父类定义的都是id ,createTime等通用字段
                    for (int i = fields.length; i > 0; i--) { 
                        fieldList.addFirst(fields[i - 1]);
                    }
                } else { 
                    fieldList.addAll(Arrays.asList(tempClass.getDeclaredFields()));
                }
                isSuper = true;
                //得到父类,然后赋给自己
                tempClass = tempClass.getSuperclass();
            }
            List<SqlColumn> list = new ArrayList<>();
            String columnName;
            Object columnValue;
            Column column;
            SqlColumn sqlColumn;
            for (Field field : fieldList) { 
                if (field.isAnnotationPresent(OneToOne.class) || field.isAnnotationPresent(ManyToOne.class) || field.isAnnotationPresent(OneToMany.class)) { 
                    //忽略级有联加载注解的字段
                    log.info("ignore cascade column {}", field.getName());
                    continue;
                }
                columnName = field.getName();
                //如果字段包含Column注解并且name字段不为空的情况用column里的name属性作为列名
                if (field.isAnnotationPresent(Column.class)) { 
                    column = field.getAnnotation(Column.class);
                    if (!("").equals(column.name())) { 
                        columnName = column.name();
                    }
                }
                columnValue = getFieldValueByField(object, field);
                //如果值为null则跳过当前循环
                if (columnValue == null) { 
                    continue;
                }
                sqlColumn = SqlColumn.of(columnName, columnValue, field.getType());
                if (sqlColumn.clz == String.class) { 
                    //如果类型是字符串, 对 ' 和 \ 这两个符号添加转义符,其它的特殊符号目前还没遇到。
                    sqlColumn.value = sqlColumn.value.toString().replaceAll("\\\\", "\\\\\\\\");
                    sqlColumn.value = sqlColumn.value.toString().replaceAll("'", "\\\\'");
                }
                list.add(sqlColumn);
            }
            return list;
        }
    
        /** * 通过反射获取字段值 * 需要注意 boolean类型的值方法是is开头的 * * @param object 对象 * @param field 字段 * @return 获取的值 */
        protected static Object getFieldValueByField(Object object, Field field) { 
            try { 
                String fieldName = field.getName();
                String firstLetter = fieldName.substring(0, 1).toUpperCase();
                String prefix = "get";
                if (field.getGenericType() == Boolean.class || field.getGenericType() == boolean.class) { 
                    prefix = "is";
                }
                String getter = prefix + firstLetter + fieldName.substring(1);
                if (prefix.equals("get")) { 
                    Method method = object.getClass().getMethod(getter, new Class[]{ });
                    Object value = method.invoke(object, new Object[]{ });
                    return value;
                } else { 
                    Method m = object.getClass().getMethod(
                            getter);
                    Object obj = m.invoke(object);
                    return obj.equals(true) ? 1 : 0;
                }
            } catch (Exception e) { 
                return null;
            }
        }
    
    
        /** * 封装需要的字段 */
        private static class SqlColumn { 
            private String name;
            private Object value;
            private Class clz;
    
            public static SqlColumn of(String name, Object value, Class clz) { 
                SqlColumn sqlColumn = new SqlColumn();
                sqlColumn.name = name;
                sqlColumn.value = value;
                if (clz == boolean.class || clz == Boolean.class) { 
                    //jpa实体类boolean类型对应数据库的bit(1)类型
                    clz = Integer.class;
                }
                sqlColumn.clz = clz;
                return sqlColumn;
            }
    
        }
    
    }

# 测试 #

public static void main(String[] args) { 
            User user=new User();
            user.setId(1L);
            user.setPassword("123456");
            user.setUsername("test");
            user.setCreateTime(new Date());
            user.setDeleted(true);
            System.out.println(CreateInsertSqlUtils.createinsert(user));
        }

可以看到输出的结果信息和预期得到的是一样的  
![在这里插入图片描述][20210616164622748.png]

# 总结 #

*  字段属性默认用字段名称.如果有 @Column注解并且有name属性,则使用name属性作为字段名
 *  jpa实体类的boolean类型的字段在数据库里对应bit(1),再转换成sql的时候需要转换成1和0
 *  需要忽略级联加载注解,例如@OneToOne,不然会把这个字段也插入到sql脚本中
 *  需要递归获取父类的通用的字段
 *  需要转义一些特殊字符,不然执行sql会报错,目前我过滤了( ’ 和 \\ ) 这两个字符

其它问题目前还没遇到,如果有别的问题小伙伴们可以留言互相探讨一下。

[20210616164622748.png]: /images/20221005/329d61db45ce45c38b809f45e8a2c4be.png