JPA实体 某些 字段 映射mysql 字段 JSON类型

[zhangzhenhuajack]

参考： https://github.com/vladmihalcea/hypersistence-utils

jsonType的实体如下：

@EqualsAndHashCode(callSuper = true)
@SuperBuilder
@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
@Entity(name = "WorkflowTemplateNodeEntity")
@Table(name = "workflow_template_node")
@org.hibernate.annotations.Table(comment = "模版node信息里面包含初始化节点", appliesTo = "workflow_template_node")
@TypeDefs({@TypeDef(name = "json", typeClass = JsonStringType.class), @TypeDef(name = "jsonList", typeClass = JsonType.class)})
public class WorkflowTemplateNode  extends BaseEntity implements Serializable {
    private static final long serialVersionUID = -8990713425639346750L;

    @Comment("节点名称")
    @ApiModelProperty("节点名称")
    @Convert(disableConversion = true)
    @Column(name = "node_name")
    @NotBlank(message = "节点名称不能为空")
    @Length(max = 30, message = "节点名称长度不能超过30")
    private String nodeName;

    @Comment("预计执行时间，单位是秒")
    @ApiModelProperty("预计执行时间，单位是秒")
    @Convert(disableConversion = true)
    @Column(name = "prediction_time")
//    @NotBlank(message = "预计执行时间不能为空")
    private String predictionTime;

    @Comment("节点完成类型(人工/机器/自动化)")
    @ApiModelProperty("节点完成类型(人工/机器/自动化)")
    @Convert(disableConversion = true)
    @Column(name = "node_complete_type")
    @Enumerated(EnumType.STRING)
    @NotNull(message = "节点完成类型不能为空")
    private NodeCompleteTypeEnum nodeCompleteTypeEnum;

    @Comment("节点的类型(初始化节点/实验节点/判断节点)枚举响应的code")
    @ApiModelProperty("节点的类型(初始化节点/实验节点/判断节点)枚举响应的code")
    @Convert(disableConversion = true)
    @Column(name = "node_type")
    @Enumerated(EnumType.STRING)
    @NotNull(message = "节点类型不能为空")
    private NodeTypeEnum nodeType;

    @Comment("到达此节点的表达式")
    @ApiModelProperty("到达此节点的表达式")
    @Convert(disableConversion = true)
    @Column(name = "node_spel")
    private String nodeSpel;

    @Comment("父级节点ID")
    @ApiModelProperty("父级节点ID")
    @Column(name = "parent_node_id")
    @Deprecated // 改用 parents代替
    private Long parentNodeId;

    @Comment("此节点最大被执行次数")
    @ApiModelProperty("此节点最大被执行次数")
    @Column(name = "exec_limit_count")
    private Integer execLimitCount;

    @Comment("节点水平维度的顺序(从1开始)")
    @ApiModelProperty("节点水平维度的顺序(从1开始)")
    @Column(name = "horizontal_seq")
    private Integer horizontalSeq;

    @Comment("垂直维度的顺序(开始节点是1)")
    @ApiModelProperty("垂直维度的顺序(开始节点是1)")
    @Column(name = "vertical_seq")
    private Integer verticalSeq;

    @Comment("工作流模版阶段ID")
    @ApiModelProperty("工作流模版阶段ID")
    @Column(name = "workflow_template_phase_id")
    @NotNull(message = "工作流模版阶段ID不能为空")
    private Long workflowTemplatePhaseId;

    @Comment("给前端用的一些扩展信息")
    @Column(name = "extended", columnDefinition = "json")
    @Type(type = "json", parameters = {@org.hibernate.annotations.Parameter(name = "class", value = "java.lang.String")})
    @JsonRawValue
    @Convert(disableConversion = true)
    @ApiModelProperty("给前端用的一些扩展信息JSONString的格式")
    private String extended;

    @Comment("工作流模版信息ID")
    @ApiModelProperty("工作流模版信息ID")
    @Column(name = "workflow_template_info_id")
    @NotNull(message = "工作流模版信息ID不能为空")
    private Long workflowTemplateInfoId;

    @Comment("节点的父节点List,后续按需扩展成表")
    @Column(name = "parents", columnDefinition = "json")
    @Type(type = "jsonList")
    @Convert(disableConversion = true)
    private List<WorkflowTemplateNodeRelations> parents;
}

[zhangzhenhuajack]

一： 根据json里面的某个字段进行查询的方法

    @Query(value = "select * from workflow_template_node n where n.workflow_template_info_id=:#{#workflowTemplateInfoId} and JSON_CONTAINS(JSON_EXTRACT(n.parents, '$[*].parentNodeId'),JSON_ARRAY(:#{#nodeId}))", nativeQuery = true)
    List<WorkflowTemplateNode> findByParentId(@Param("workflowTemplateInfoId") Long workflowTemplateInfoId, @Param("nodeId") Long nodeId);

json的一些函数参考：https://www.cnblogs.com/ivictor/p/16221712.html