一、四种查询方法(不算hql语句)
1.使用jpa内置的Criteria
适用于单表的简单查询,若需求需要按照数据库中某个存储utf-8的中文字段值的拼音来排序则实现不了
1).实现类的查询方法代码
@Override
public Page<BioColSampleInventory> sampleInventories(SamplingsInventoryDTO dto) {
Sort sort = Sort.by(Sort.Order.asc(BioColSubjectSample.Fields.subjectNo));
Pageable pageable = PageRequest.of(dto.getPage()-1, dto.getSize(), sort);PageRequest.of(dto.getPage()-1, dto.getSize(), sort);
Criteria criteria = Criteria.create();
//被试编号或者被试姓名
if(StringUtils.isNotBlank(dto.getValue())){
criteria.or(Criteria.create().like(BioColSampleInventory.Fields.subjectName, dto.getValue())
.equal(BioColSampleInventory.Fields.subjectNo, dto.getValue()));
}
//队列编号
if(StringUtils.isNotBlank(dto.getQueueId())){
criteria.equal(BioColSampleInventory.Fields.queueId, dto.getQueueId());
}
//入库标识
if(LocationStatusEnum.SITE_IN_THE_LIBRARY.getValue().equals(dto.getFlag())){
criteria.equal(BioColSampleInventory.Fields.locationStatus, dto.getFlag());
}else if(LocationStatusEnum.SITE_OUTBOUND.equals(dto.getFlag())){
//出库标识
criteria.equal(BioColSampleInventory.Fields.locationStatus, dto.getFlag());
}
return sampleInventoryRepository.search(criteria, pageable);
}
2).需要定义repository接口并实现
public interface SampleInventoryRepository extends BpJpaRepository<BioColSampleInventory, Long> {
}
3).数据库映射类
package com.iflytek.biims.biosample.entity;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.iflytek.bp.common.entity.AbstractAuditingEntity;
import lombok.Data;
import lombok.experimental.Accessors;
import lombok.experimental.FieldNameConstants;
import javax.persistence.*;
import java.util.Date;
/**
* @date 2021/8/27 11:12
*/
@Entity
@Table(name = "bio_col_sample_inventory")
@Data
@FieldNameConstants
@Accessors(chain = true)
public class BioColSampleInventory extends AbstractAuditingEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
/**
* 采集记录表主键
*/
private String clctId;
/**
* 被试编号
*/
private String subjectNo;
/**
* 被试姓名
*/
private String subjectName;
/**
* 样本编号
*/
private String sampleNo;
/**
* 样本类型
*/
private String typeNo;
/**
* 入库样本容量
*/
private Integer sampleCapacity;
/**
* 采集样本容量
*/
private Integer clctSampleCapacity;
/**
* 样本状态
*/
private Integer sampleStatus;
/**
* 收集时间
*/
@JsonFormat(pattern = "yyyy-MM-dd")
private Date collectDatetime;
/**
* 采样人员
*/
private String clctBy;
/**
* 入库时间
*/
@JsonFormat(pattern = "yyyy-MM-dd")
private Date intoTime;
/**
* 入库人员
*/
private String intoPepole;
/**
* 样本在库状态
*/
private Integer locationStatus;
/**
* 队列编号
*/
private String queueId;
}
2.自定义方法查询
适用于单表简单条件查询, 也是个人感觉JPA做的最好的地方,优点是灵活,无需编写sql,但是传参不能是date类型
1).实现类中的方法
@Override
public void intoAdd(String sampleNo, Integer locationStatus) {
//查询该试管编号是否已经入库
BioColSampleInventory whetherBean = sampleInventoryRepository.findBySampleNoAndLocationStatus(sampleNo, locationStatus);
}
2).自定义repository接口
package com.iflytek.biims.biosample.repository;
import com.iflytek.biims.biosample.entity.BioColSampleInventory;
import com.iflytek.bp.common.repository.BpJpaRepository;
import org.springframework.data.jpa.repository.Query;
import java.util.List;
/**
* @author
* @date 2021/8/30 10:24
*/
public interface SampleInventoryRepository extends BpJpaRepository<BioColSampleInventory, Long> {
//这个方法必须要写,需要注意BioColSampleInventory类中必须要有sampleNo和locationStatus属性
BioColSampleInventory findBySampleNoAndLocationStatus(String sampleNo, Integer locationStatus);
}
3.使用@query自定义sql查询
1.需要注意的点:
(1).nativeQuery的值为true,代表使用自定义sql查询,则不能使用hql语句
(2).查询SQL中的语句别名不能以驼峰式的命名
(3).定义的实体类中必须要有@Entity注解以及必须要有id的属性以及id属性上必须要有@Id注解
1).自定义Repository接口
public interface SampleInventoryStatisticRepository extends BpJpaRepository<SampleInventoryVO, Long> {
@Query(nativeQuery = true ,value = "SELECT a.id, count(DISTINCT a.subject_no) AS subject_count, IFNULL(sum(IF(a.type_no = 1, 1, 0 )),0) AS edta_one_count, "
+ "IFNULL(sum(IF(a.type_no = 2, 1, 0 )),0) AS edta_two_count FROM bio_col_sample_inventory a where a.queue_id = :queueId ")
SampleInventoryVO statistic(String queueId);
}
2).自定查询映射类
package com.iflytek.biims.biosample.entity.vo;
import lombok.Data;
import javax.persistence.Entity;
import javax.persistence.Id;
/**
* @author
* @date 2021/8/31 15:13
*/
@Data
@Entity
public class SampleInventoryVO {
@Id
private Long id;
/**
* 被试数量
*/
private Integer subjectCount;
/**
* EDTA-1 数量
*/
private Integer edtaOneCount;
/**
* EDTA-2 数量
*/
private Integer edtaTwoCount;
}
4.使用EntityManager自定义sql查询
需要注意的点
(1).查询SQL中的语句别名不能以驼峰式的命名
(2).定义的实体类中必须要有@Entity注解及必须要有id的属性以及id属性上必须要有@Id注解
1).实现类
package com.iflytek.biims.biosample.service.impl;
import com.iflytek.biims.biosample.entity.vo.MonitoringManagementVO;
import com.iflytek.biims.biosample.service.MonitoringManagementResourceService;
import com.iflytek.biims.biosample.service.dto.MonitoringManagementDTO;
import org.apache.commons.lang3.StringUtils;
import org.springframework.data.domain.*;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.ArrayList;
import java.util.List;
/**
* @author
* @date 2021/9/1 14:19
*/
@Service
public class MonitoringManagementServiceImpl implements MonitoringManagementResourceService {
@Resource
@PersistenceContext
private EntityManager entityManager;
@Override
public Page<MonitoringManagementVO> sampleInventoryWithSendRecords(MonitoringManagementDTO dto) {
StringBuilder commonSql=new StringBuilder();
StringBuilder querySql = new StringBuilder("SELECT ");
StringBuilder countSql = new StringBuilder("SELECT count(a.id) ");
querySql.append("a.id, a.subject_name, if(d.gender = 0,'男','女') as gender_name, a.subject_no, if(a.type_no = 1,'EDTA-1','EDTA-2') AS type_no_name, a.sample_no, ");
querySql.append("case a.location_status when 1 then '站点在库' when 2 then '站点出库' when 3 then '寄送途中' when 4 then '中心在库' ELSE '' end as 'location_status', ");
querySql.append("a.collect_datetime, a.clct_by, case a.sample_status when 0 then '正常' when 1 then '样品量不足' ");
querySql.append("when 2 then '样品缺失' ELSE '' end as 'sample_status', a.sample_capacity, ");
querySql.append("a.into_time, a.into_pepole, c.send_date, c.tracking_no, c.send_by ");
commonSql.append("FROM bio_col_sample_inventory a left join bio_col_send_inventory b on a.sample_no = b.sample_no ");
commonSql.append("left join bio_col_send_record c on b.package_no = c.package_no ");
commonSql.append("LEFT JOIN bio_col_subject_sampling d on a.clct_id = d.clct_id and d.subject_no = a.subject_no ");
commonSql.append("where a.queue_id ='").append(dto.getQueueId()).append("'");
if(StringUtils.isNotBlank(dto.getValue())){
commonSql.append(" and (a.subject_name like concat('%','").append(dto.getValue()).append("', '%') ");
commonSql.append("or a.subject_no = '").append(dto.getValue()).append("') ");
}
if(StringUtils.isNotBlank(dto.getSampleNo())){
commonSql.append("and a.sample_no like concat('%','").append(dto.getSampleNo()).append("','%') ");
}
if(dto.getLocationState() != null){
commonSql.append("and a.location_status = ").append(dto.getLocationState());
}
if(dto.getSampleState() != null){
commonSql.append(" and a.sample_status = ").append(dto.getSampleState());
}
querySql.append(commonSql);
countSql.append(commonSql);
querySql.append(" order by a.collect_datetime desc, CONVERT(a.subject_name USING gbk) desc ");
querySql.append("limit ").append((dto.getPage()-1) * (dto.getSize())).append(",")
.append(dto.getSize());
Query count = entityManager.createNativeQuery(countSql.toString());
List<MonitoringManagementVO> resultList = new ArrayList<>();
if(count.getResultList().size() > 0){
Query query = entityManager.createNativeQuery(querySql.toString(), MonitoringManagementVO.class);
resultList = query.getResultList();
}
Pageable pageable = PageRequest.of(dto.getPage()-1, dto.getSize(), Sort.unsorted());
if(CollectionUtils.isEmpty(resultList)){
return new PageImpl<>(new ArrayList<>(), pageable, 0);
}
return new PageImpl<>(resultList, pageable, count.getResultList().size());
}
}
2)自定义返回实体类
package com.iflytek.biims.biosample.entity.vo;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import javax.persistence.Entity;
import javax.persistence.Id;
import java.util.Date;
/**
* @author
* @date 2021/9/1 14:29
*/
@Data
@Entity
public class MonitoringManagementVO {
@Id
private int id;
/**
* 被试姓名
*/
private String subjectName;
/**
* 性别
*/
private String genderName;
/**
* 被试编号
*/
private String subjectNo;
/**
* 样本类型
*/
private String typeNoName;
/**
* 样本编号
*/
private String sampleNo;
/**
* 在库状态
*/
private String locationStatus;
/**
* 收集时间
*/
@JsonFormat(pattern = "yyyy-MM-dd")
private Date collectDatetime;
/**
* 收集人
*/
private String clctBy;
/**
* 样本状态
*/
private String sampleStatus;
/**
* 样本容量
*/
private Integer sampleCapacity;
/**
* 入库时间
*/
@JsonFormat(pattern = "yyyy-MM-dd")
private Date intoTime;
/**
* 入库人员
*/
private String intoPepole;
/**
* 寄送时间
*/
@JsonFormat(pattern = "yyyy-MM-dd")
private Date sendDate;
/**
* 快递单号
*/
private String trackingNo;
/**
* 寄送人
*/
private String sendBy;
}