用到的依赖
示例中用到的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));
}
总结
- 字段属性默认用字段名称.如果有 @Column注解并且有name属性,则使用name属性作为字段名
- jpa实体类的boolean类型的字段在数据库里对应bit(1),再转换成sql的时候需要转换成1和0
- 需要忽略级联加载注解,例如@OneToOne,不然会把这个字段也插入到sql脚本中
- 需要递归获取父类的通用的字段
- 需要转义一些特殊字符,不然执行sql会报错,目前我过滤了( ’ 和 \ ) 这两个字符
其它问题目前还没遇到,如果有别的问题小伙伴们可以留言互相探讨一下。
本文标题:根据JPA实体类生成对应数据的sql文件
本文链接:https://blog.quwenai.cn/post/2277.html
版权声明:本文不使用任何协议授权,您可以任何形式自由转载或使用。




![[并发编程] - Executor框架#ThreadPoolExecutor源码解读02 [并发编程] - Executor框架#ThreadPoolExecutor源码解读02](https://blog.quwenai.cn/zb_users/upload/2022/03/20220327124158164835611866353.png)


还没有评论,来说两句吧...