JPA 关联表添加关联条件@Where、@WhereJoinTable()

左手的ㄟ右手 2023-06-16 15:55 1阅读 0赞

> JPA 关联表添加关联条件，当我其中一个表的数据状态发送改变，不是业务上所需要的，这时实体查询还是会带出关联数据，所以我们需要处理这部分数据；第一种方式：删除中间表对应数据；第二种添加关联条件，按条件带出有效数据

*  代码示例
    
     *  用户实体
    
        @Entity
         @Table(name = "pmi_t_user")
         @org.hibernate.annotations.Table(appliesTo = "pmi_t_user",comment="用户信息表")
         @Component
         @ApiModel(value = "用户实体")
         public class User implements Serializable { 
         
             private static final long serialVersionUID = -4618191047832700121L;
             /** * 主键id */
             @ApiModelProperty(value = "主键id")
             @Id
             @GeneratedValue(strategy = GenerationType.IDENTITY)
             private Long id;
         
         
             /** * 用户名 */
             @ApiModelProperty(value = "用户名", required = true)
             @NotBlank(message = "用户名不能为空")
             @Column(name = "user_name", nullable = false,columnDefinition="varchar(100) COMMENT '用户名'")
             private String userName;
         
         
             /** * 关联角色 db_status 是数据库角色表字段 * @WhereJoinTable(clause = "db_status = 1") */
             @ApiModelProperty(value = "关联角色")
             @ManyToMany(fetch = FetchType.EAGER,targetEntity = Role.class)
             @JsonIgnoreProperties(value = {  "users" })
             @Where(clause = "db_status = 1")
             @JoinTable(name = "pmi_t_user_role", joinColumns = @JoinColumn(name = "user_id"), inverseJoinColumns = @JoinColumn(name = "role_id"))
             @org.hibernate.annotations.ForeignKey(name = "none")
             private Set<Role> roles;
         }
    
     *  角色
    
        @Entity
         @Table(name = "pmi_t_role")
         @org.hibernate.annotations.Table(appliesTo = "pmi_t_role",comment="角色")
         @ApiModel(value = "角色")
         public class Role implements Serializable { 
         
             private static final long serialVersionUID = 2376963713493278377L;
             /** * 主键id */
             @ApiModelProperty(value = "主键id")
             @Id
             @GeneratedValue(strategy = GenerationType.IDENTITY)
             private Long id;
         
             /** * 角色名称 */
             @ApiModelProperty(value = "角色名称")
             @NotNull(message = "角色名称不能为空")
             @Length(max = 20,min=1, message = "角色名称长度大于1小于20")
             @Column(name = "role_name", nullable = false,columnDefinition="varchar(20) COMMENT '角色名称'")
             private String roleName;
         
         
             /** * 数据状态 1:正常 -1：删除 */
             @ApiModelProperty(value = "数据状态")
             @Column(name = "db_status", nullable = false,columnDefinition="tinyint(4) DEFAULT '1' COMMENT '数据状态 1:正常 -1：删除'")
             private int dbStatus;
         
         }
 *  查询效果：  
    ![在这里插入图片描述][2019112710190485.png]  
    ![在这里插入图片描述][20191127101917910.png]  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1_size_16_color_FFFFFF_t_70]
 *  sql打印  
    ![在这里插入图片描述][20191127102322768.png]

[2019112710190485.png]: https://img-blog.csdnimg.cn/2019112710190485.png
[20191127101917910.png]: https://img-blog.csdnimg.cn/20191127101917910.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191127101940294.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNDI0ODI1,size_16,color_FFFFFF,t_70
[20191127102322768.png]: https://img-blog.csdnimg.cn/20191127102322768.png