JPA操作

联表查询

//把需要的三个属性作为TBook1 的构造函数，并使用new函数
Query query = em.createQuery("SELECT new com.dmsdbj.library.entity.TBook1(c.id,c.searchNum,b.isbn,b.name) 
FROM TBookBasic b,TBook c WHERE b.id=c.basicId and c.isDelete=0 and b.isDelete=0");
————————————————
版权声明：本文为CSDN博主「奔跑的大白啊」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/zt15732625878/article/details/78383812

 

级联删除

    public void deleteChildrenCataloging(String id) {
        //todo 级联删除
      List<Cataloging> catalog= catalogingRepository.findAllByParentId(id);
      if(catalog.size()>0){
          catalog.forEach(es->{
              catalogingRepository.delete(es);
              catalogingMetaDataRepository.deleteCatalogingMetaDataByCatalogingId(Boolean.TRUE,es.getId());
              deleteChildrenCataloging(es.getId());
          });
      }
    }

 

分页1：

仓储层

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ResourceDockingRepository extends JpaRepository<ResourceDocking,String>  {

    Page<ResourceDocking> findAll(Specification<ResourceDocking> resourceDockingSpecification, Pageable pageable);
}

实现层

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;

import javax.persistence.criteria.*;
import java.util.ArrayList;
import java.util.List;


private Page<DataDirectoryInteriorAuditStep> findAllPage(AuditStepParam param){
        Pageable pageable = PageRequest.of(param.getPageNum(), param.getPageSize());
        return dataDirectoryInteriorAuditStepRepository.findAll((Specification<DataDirectoryInteriorAuditStep>) (root, criteriaQuery, criteriaBuilder) -> {

            List<Predicate> list = new ArrayList<>();

            if(StringUtils.isNotEmpty(param.getDeptId())){
                list.add(criteriaBuilder.like(root.get("auditDeptId"),param.getDeptId()));
            }

            Predicate[] arr = new Predicate[list.size()];
            return criteriaBuilder.and(list.toArray(arr));
        }, pageable);
    }

 

控制器

@PostMapping("/dockingList")
@RequiresPermissions("resourcedocking:list")
@RequestMapping(value = "/saveMateData",method = RequestMethod.GET)

@ApiOperation(value = "资源对接管理目录列表") public ResultData<DataCollectInit> dockingList(@Validated getResourceDockingListParams param){ Pageable pageable = PageRequest.of(param.getPageNum(), param.getPageSize()); Page<ResourceDocking> lie= resourceDockingService.getResourceDockingList(param,pageable ); return ResultData.success(lie); }

 

分页二： 

   @Query(value = "select * from datadirectory_interior_auth  auth\n" +
            "      left join data_metadata_view dmv on dmv.id =auth.table_id \n" +
            "      left join sys_users su on su.id=auth.user_ids \n" +
            "      where (auth.user_ids=?1" +
            "      or su.organization_id=?2\n" +
            "      or auth.is_whole_share =?3\n" +
            "      )\n" +
            "      and auth.start_time <=now()" +
            "      and auth.end_time >=now()" +
            "      and auth .state =?4" +
            "      and dmv.enable =?5" +
            "      and if(?6 is not null,dmv.meta_data_name like %?6%,1=1)  "+
            "      and dmv.is_delete =0",nativeQuery = true)
    Page<DataDirectoryInteriorAuth> getUserAuthPage(String userId, String userOrgId,
                                                    Boolean isWholeShare,
                                                    Boolean state, Boolean tableEnable, String title,Pageable pageable);

@Query(value = "select  new com.nongye.transmission.dto.responseParam.TransferSendDataDto(" +
        "trans.title,trans.remarks,trans.deptName,trans.createTime,trans.state,trans.auditMsg) from TransferApplyInfo trans " +
        "where trans.state<>'1' and trans.state<>'3'   and  (:state is null or trans.state=:state) and " +
        "(:keyWord is null or trans.title like %:keyWord%) ")
Page<TransferSendDataDto> getSendData(@Param("state") String state, @Param("keyWord") String keyWord, Pageable pageable);

@Query(value = "select mart.id,mart. create_time, mart.creator_id, mart.creator_name, mart.is_delete, mart.update_time, " +
        "mart.data_mart_id, mart.table_id,mart.creator_user_name" +
        " from data_manage_mart_table mart \n" +
        "      left join data_manage_originaltable dmo on dmo.id =mart.table_id \n" +
        "      where 1=1 \n" +
        "      and  if(:dataMartId is not null,mart.data_mart_id =:dataMartId,1=1)" +
        "      and if(:tableName is not null,dmo.table_name like %:tableName% or dmo.another_name like %:tableName%,1=1)"+
        "      and if(:deptId is not null,dmo.dept_id =:deptId,1=1)  ",
        countQuery =  "select count(1)" +
                " from data_manage_mart_table mart \n" +
                "      left join data_manage_originaltable dmo on dmo.id =mart.table_id \n" +
                "      where 1=1 \n" +
                "      and  if(:dataMartId is not null,mart.data_mart_id =:dataMartId,1=1)" +
                "      and if(:tableName is not null,dmo.table_name like %:tableName% or dmo.another_name like %:tableName%,1=1)"+
                "      and if(:deptId is not null,dmo.dept_id =:deptId,1=1)  ",nativeQuery = true)

Page<DataMartTable> findMartTablePage(@Param("dataMartId")String dataMartId, @Param("tableName")String tableName ,
                                      @Param("deptId")String deptId, Pageable page);

 

 

List动态查询

List<MetaData> findAll(Specification<MetaData> treeId);

@Override
    public List<MetaData> getMetaDataList(MetaDataParms param){
            Specification<MetaData> queryCondition = (Specification<MetaData>) (root, criteriaQuery, criteriaBuilder) -> {
                List<Predicate> predicateList = new ArrayList<>();
                if(StringUtils.isNotEmpty(param.getTreeId())){
                    if(!param.getTreeId().equals("1")){
                        predicateList.add(criteriaBuilder.equal(root.get("treeId"),param.getTreeId()));
                    }
                }
                if(StringUtils.isNotEmpty(param.getName())){
                    predicateList.add(criteriaBuilder.like(root.get("metaDataName"),param.getName()));
                }
                if(StringUtils.isNotEmpty(param.getNotId())){
                    predicateList.add(criteriaBuilder.notEqual(root.get("Id"),param.getNotId()));
                }
                return criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()]));
            };
            return metaDataRepository.findAll(queryCondition);

    }

  

Swagger返回值注释

@ApiOperation(value = "学生跟进",response =StudentLoseFollow.class)

逻辑删除操作

重点在于：@SQLDelete与@Where

其中，@SQLDelete重写了删除方法，@Where重写了查询方法。

@Entity
@Table(name="data_metadata_view")
@JsonInclude(JsonInclude.Include.NON_NULL)
@ApiModel(value="元数据表")
@SQLDelete(sql = "update data_metadata_view set is_delete = 1 where id = ?")
@Where(clause="is_delete = 0 ")
@Data
public class MetaData   extends BaseEntityColumn implements Serializable {

    @Id
    @GeneratedValue(generator = "system-uuid")
    @Column(length=36)
    @GenericGenerator(name = "system-uuid", strategy = "uuid")
    @ApiModelProperty(value = "ID",hidden = true)
    private String id;//主键

}

 

 ManToOne报错问题

添加注解：

@Column(insertable = false,updatable = false)

返回时序列化报错问题

Type definition error: [simple type, class org.hibernate.proxy.pojo.bytebuddy.ByteBuddyInterceptor]; nested exception is com.fasterxml.jackson.databind.exc.InvalidDefinitionException: No serializer found for class org.hibernate.proxy.pojo.bytebuddy.ByteBuddyInterceptor and no properties discovered to create BeanSerializer (to avoid exception, disable SerializationFeature.FAIL_ON_EMPTY_BEANS) (through reference chain: com.springbootjpa.jpatest.entity.City$HibernateProxy$gAXtgPDD["hibernateLazyInitializer"])

解决方案：

为了解决这个问题需要在 实体类上 加入一个注解，代码如下

@JsonIgnoreProperties({"hibernateLazyInitializer","handler"})

 

 日期查询

    public Date getEndTime(){
        if(endTime==null){
            return null;
        }
        return DateUtils.getDayEnd(endTime);
    }


public static java.util.Date getDayEnd(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        calendar.set(calendar.get(Calendar.YEAR), calendar.get(Calendar.MONTH), calendar.get(Calendar.DAY_OF_MONTH), 23, 59, 59);
        return calendar.getTime();
    }


   if(parms.getEndTime()!=null){
                list.add(criteriaBuilder.lessThan(root.get("createTime"), parms.getEndTime()));
            }

 

 个人比较推荐的控制器方法结构

 手写注释

    @ApiImplicitParams({
            @ApiImplicitParam(value = "Id",name = "id",type = "String")

    })

 返回的实体类注解

    @ApiOperation(value = "获取未处理的元数据",response = OriginalTable.class)

 

 

  @PostMapping("/getMetaDataOpearRecodeAnalyze")
    @RequiresPermissions("MetaDataManage:getMetaDataOpearRecodeAnalyze")
    @RequestMapping(value = "/getMetaDataOpearRecodeAnalyze",method = RequestMethod.GET)
    @ApiOperation(value = "获取元数据变更处统计")

    public ResultData getMetaDataOpearRecodeAnalyze(Date st,Date et){
        try{
            return ResultData.success(metadataOperationRecordService.getAnalyzeByOpera(st,et));
        }catch (RuntimeException e){
            LOGGER.error("获取元数据变更处统计-"+e.getMessage(),e);
            return ResultData.fail(e.getMessage(),ResultData.REFUSE_CODE);
        }catch (Exception e1){
            LOGGER.info("获取元数据变更处统计",e1);
            return ResultData.fail("系统内部错误",ResultData.ERROR_CODE);
        }
    }

 

从实体中获取id集合

        List<String> metaIdList=metaList.stream().map(MetaData::getId).collect(Collectors.toList());

 获取第一条符合条件的记录

                String columnId= fieldList.stream().filter(es->es.getColumnName().equals(entry.getKey())).findFirst().orElse(new MetaDataColumn()).getId();

 

代码模板

实体类

package com.nongye.datamanage.entity;

import com.fasterxml.jackson.annotation.JsonInclude;
import com.nongye.base.BaseEntityColumn;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.hibernate.annotations.GenericGenerator;
import org.hibernate.annotations.SQLDelete;
import org.hibernate.annotations.Where;

import javax.persistence.*;
import java.io.Serializable;

/**
 * zhangxu
 * 2022/9/13 14:11
 **/
@Entity
@Table(name="data_manage_rules_unique")
@JsonInclude(JsonInclude.Include.NON_NULL)
@ApiModel(value="唯一性检查")
@SQLDelete(sql = "update data_manage_rules_unique set is_delete = 1 where id = ?")
@Where(clause="is_delete = 0 ")
@Data
public class UniqueQualityRules extends BaseEntityColumn implements Serializable {

    @Id
    @GeneratedValue(generator = "system-uuid")
    @Column(length=36)
    @GenericGenerator(name = "system-uuid", strategy = "uuid")
    @ApiModelProperty(value = "ID",hidden = true)
    private String id;


    /**
     * 规则名称
     */
    @Column
    @ApiModelProperty(value = "规则名称")
    private String ruleName;

    /**
     * 组Id
     */
    @Column
    @ApiModelProperty(value = "组Id")
    private String groupId;

    /**
     * 组名称
     */
    @Column
    @ApiModelProperty(value = "组名称")
    private String groupName;

    /**
     * 表名
     */
    @Column
    @ApiModelProperty(value = "表名")
    private String tableName;

    /**
     * 字段
     */
    @Column
    @ApiModelProperty(value = "字段")
    private String columnName;

    /**
     * 描述
     */
    @Column
    @ApiModelProperty(value = "描述")
    private String description;


}

仓储类

package com.nongye.datadirectory.repository;

import com.nongye.datadirectory.entity.DataDirectoryOutsideApiUrl;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import java.util.List;


public interface OutsideApiUrlRepository extends JpaRepository<DataDirectoryOutsideApiUrl, String>,
        JpaSpecificationExecutor<DataDirectoryOutsideApiUrl> {


    public List<DataDirectoryOutsideApiUrl> findByOutsideApplyId(String applyId);
}

Service

 

Log静态类

    private static final Logger LOGGER = LoggerFactory.getLogger(UserServiceImpl.class);

 

JPA写sql动态查询

 @Query(value = " select opear_type, count(1) as num  from data_metadata_opera_recode " +
            "where create_time >?1  and create_time <?2" +
            " and if(?3 is not null ,meta_data_type =?3,1=1 )  group by opear_type ",nativeQuery = true)
    List<Map> getAnalyzeByOperaType(Date startTime,Date endTime,String searchType);

@Query(value = " select opear_type, count(1) as num  from data_metadata_opera_recode " +
        "where create_time >?1  and create_time <?2" +
        " and if(?3 is not null ,meta_data_type =?3,1=1 )  group by opear_type ",nativeQuery = true)

 

手动验证模型参数

实体类

    /**
     * 定义
     */
    @Column
    @Length(max = 100)
    @NotNull(message = "定义不能为空")
    @ApiModelProperty(value = "定义")
    private String definition;

验证方法

 private void validateColumn(@Valid MetaDataColumn columnList) {
        Set<ConstraintViolation<@Valid MetaDataColumn>> validateSet = Validation.buildDefaultValidatorFactory()
                .getValidator()
                .validate(columnList, new Class[0]);
        if (!CollectionUtils.isEmpty(validateSet)) {
            String messages = validateSet.stream()
                    .map(ConstraintViolation::getMessage)
                    .reduce((m1, m2) -> m1 + "；" + m2)
                    .orElse("参数输入有误！");
            throw new IllegalArgumentException(messages);

        }
    }

 

 强关联（ManytoOne、OneToOne）外键失效时，该怎么处理？

在many-to-one, one-to-one, 关系中，一方引入另一方的属性，如果引用属性值数据在数据库中不见，hibernate默认会抛出异常，解决此问题，加@NotFound注解即可.@notFound注解默认值为NotFoundAction.EXCEPTION


@ManyToOne
@JoinColumn(name="area_id")
@NotFound(action = NotFoundAction.IGNORE)
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public Area getArea() {
    return area;
}

 

 

 

搜索

复制