1、引入依赖:
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
</dependency>
2、加入插件,用于生成查询实例:
<plugin>
<groupId>com.mysema.maven</groupId>
<artifactId>apt-maven-plugin</artifactId>
<version>1.1.3</version>
<executions>
<execution>
<phase>generate-sources</phase>
<goals>
<goal>process</goal>
</goals>
<configuration>
<outputDirectory>target/generated-sources</outputDirectory>
<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
</configuration>
</execution>
</executions>
</plugin>
3、新建实体类,执行 clean、install,已下案例都需新增完实体类执行 clean、install,生成Q开头文件
4、jpa实现主键查询
(1)新建实体类
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "SYS2_ML_JGQX")
@IdClass(OrganizationAuthorityPK.class)
@JsonIgnoreProperties(ignoreUnknown = true)
public class OrganizationAuthorityDO implements Serializable {
private static final long serialVersionUID = 5264856275250983119L;
@Id
@ApiModelProperty("当前操作机构代码")
private String jgdm;
@Id
@ApiModelProperty("目标机构代码")
private String mbjgdm;
@Id
@ApiModelProperty("目录类别(0药品,1项目,2材料)")
private String mllb;
@ApiModelProperty("目录代码")
private String mldm;
}
@Data
@NoArgsConstructor
@AllArgsConstructor
public class OrganizationAuthorityPK implements Serializable {
private static final long serialVersionUID = 2644585821243983300L;
@ApiModelProperty("当前操作机构代码")
private String jgdm;
@ApiModelProperty("目标机构代码")
private String mbjgdm;
@ApiModelProperty("目录类别(1药品,2材料,3收费目录)")
private String mllb;
}
(2)、service
@Transactional(rollbackFor = Exception.class)
public void saveDrugContentDownloadInfo(String jgdm,String mbjgdm){
OrganizationAuthorityPK organizationAuthorityPK=new OrganizationAuthorityPK();
organizationAuthorityPK.setJgdm(jgdm);
organizationAuthorityPK.setMbjgdm(mbjgdm);
organizationAuthorityPK.setMllb("0");
Optional<OrganizationAuthorityDO>
optional=organizationAuthorityRepository.findById(organizationAuthorityPK);
OrganizationAuthorityDO organizationAuthorityDO=optional.get();
organizationAuthorityDO.setMbjgxzbz(1);
organizationAuthorityRepository.saveAndFlush(organizationAuthorityDO);
}
5、jpa实现多表动态查询
(1)、新建实体类
@Data
@NoArgsConstructor
@AllArgsConstructor
public class DrugContentVO implements Serializable {
private String jgdm;
private String mbjgdm;
private String mllb;
private String mldm;
private String czqx;
private String xzzd;
private String btzd;
private int mbjgxzbz;
private String mlmc;
}
@Data
@NoArgsConstructor
@AllArgsConstructor
@Entity
@Table(name = "SYS2_ML_JGQX")
@IdClass(OrganizationAuthorityPK.class)
@JsonIgnoreProperties(ignoreUnknown = true)
public class OrganizationAuthorityDO implements Serializable {
private static final long serialVersionUID = 5264856275250983119L;
@Id
@ApiModelProperty("当前操作机构代码")
private String jgdm;
@Id
@ApiModelProperty("目标机构代码")
private String mbjgdm;
@Id
@ApiModelProperty("目录类别(0药品,1项目,2材料)")
private String mllb;
@ApiModelProperty("目录代码")
private String mldm;
@ApiModelProperty("操作权限")
private String czqx;
@ApiModelProperty("限制字段")
private String xzzd;
@ApiModelProperty("必填字段")
private String btzd;
private int mbjgxzbz; //0:未下载 1:已下载
}
@ApiModel
@Entity
@Table(name="SYS2_MLFL")
@Data
public class Sys2MlflDTO implements Serializable {
@Id
@GeneratedValue(generator = "uuidGenerator")
@GenericGenerator(name = "uuidGenerator", strategy = "uuid")
@Column(nullable = false, length = 64)
private String mldm;
private String mlmc;
private String xmlb;
private String wssdm;
private String bz;
@JsonFormat(locale = "zh", timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
private String xt_djsj;
}
(2)、service
//场景一 查询结果为两个表的字段
public List<DrugContentVO> queryUseDrugContentInfo(String mbjgdm,int mbjgxzbz){
JPAQueryFactory jpaQueryFactory=new JPAQueryFactory(entityManager);
BooleanBuilder builder = new BooleanBuilder();
QOrganizationAuthorityDO qOrganizationAuthorityDO = QOrganizationAuthorityDO.organizationAuthorityDO;
QSys2MlflDTO qSys2MlflDTO= QSys2MlflDTO.sys2MlflDTO;
builder.and(qOrganizationAuthorityDO.mllb.eq("0"));
builder.and(qOrganizationAuthorityDO.mbjgdm.eq(mbjgdm));
if(mbjgxzbz==1){
builder.and(qOrganizationAuthorityDO.mbjgxzbz.eq(mbjgxzbz));
}
JPAQuery<DrugContentVO> jpaQuery=jpaQueryFactory.select(
Projections.bean(DrugContentVO.class,
qOrganizationAuthorityDO.mbjgdm,qOrganizationAuthorityDO.jgdm,qOrganizationAuthorityDO.mldm,
qOrganizationAuthorityDO.mllb,qOrganizationAuthorityDO.mbjgxzbz,qOrganizationAuthorityDO.btzd,
qOrganizationAuthorityDO.czqx,qOrganizationAuthorityDO.xzzd,qSys2MlflDTO.mlmc))
.from(qOrganizationAuthorityDO)
.innerJoin(qSys2MlflDTO)
.on(qOrganizationAuthorityDO.mbjgdm.eq(qSys2MlflDTO.wssdm),qOrganizationAuthorityDO.mldm.eq(qSys2MlflDTO.mldm))
.where(builder);
List<DrugContentVO> list=jpaQuery.fetch();
return list;
}
//场景二 查询结果为其中一个表的字段
public List<OrganizationAuthorityDO> queryUseDrugContentInfo(String mbjgdm,int mbjgxzbz){
JPAQueryFactory jpaQueryFactory=new JPAQueryFactory(entityManager);
BooleanBuilder builder = new BooleanBuilder();
QOrganizationAuthorityDO qOrganizationAuthorityDO = QOrganizationAuthorityDO.organizationAuthorityDO;
QSys2MlflDTO qSys2MlflDTO= QSys2MlflDTO.sys2MlflDTO;
builder.and(qOrganizationAuthorityDO.mllb.eq("0"));
builder.and(qOrganizationAuthorityDO.mbjgdm.eq(mbjgdm));
if(mbjgxzbz==1){
builder.and(qOrganizationAuthorityDO.mbjgxzbz.eq(mbjgxzbz));
}
JPAQuery<OrganizationAuthorityDO> jpaQuery=jpaQueryFactory.select(qOrganizationAuthorityDO)
.from(qOrganizationAuthorityDO)
.innerJoin(qSys2MlflDTO)
.on(qOrganizationAuthorityDO.mbjgdm.eq(qSys2MlflDTO.wssdm),qOrganizationAuthorityDO.mldm.eq(qSys2MlflDTO.mldm))
.where(builder);
List<OrganizationAuthorityDO> list=jpaQuery.fetch();
return list;
}
6、jpa实现多表动态分页查询
(1)、新建实体类,此处省略
(2)、service
@Resource
private EntityManager entityManager;
public Map<String,Object> queryPlatformUnitSettingsList(int pageNum, int pageSize, String jgbm, String keyWord){
pageNum=pageNum-1; //jpa 分页从第0开始
Map<String,Object> resultMap=new HashMap<>();
JPAQueryFactory jpaQueryFactory=new JPAQueryFactory(entityManager);
QYkYpkDO qYkYpkDO=QYkYpkDO.ykYpkDO;
QWmShYpdwszDTO qWmShYpdwszDTO=QWmShYpdwszDTO.wmShYpdwszDTO;
BooleanBuilder builder = new BooleanBuilder();
if(StringUtils.isNotEmpty(keyWord)){
builder.or(qYkYpkDO.pym.like("%".concat(keyWord).concat("%")));
builder.or(qYkYpkDO.wbm.like("%".concat(keyWord).concat("%")));
builder.or(qYkYpkDO.ypmc.like("%".concat(keyWord).concat("%")));
builder.or(qYkYpkDO.ypdm.eq(keyWord));
}
builder.and(qYkYpkDO.wssdm.eq(jgbm));
QYbYpkDTO qYbYpkDTO=QYbYpkDTO.ybYpkDTO;
builder.and(qYbYpkDTO.yblx.eq("03"));
QUvShybYpkDTO qUvShybYpkDTO=QUvShybYpkDTO.uvShybYpkDTO;
JPAQuery<PlatformUnitSettingsVO> jpaQuery=jpaQueryFactory.select(
Projections.bean(PlatformUnitSettingsVO.class,qYkYpkDO.wssdm,qYkYpkDO.ypdm,qYkYpkDO.ypmc,qYkYpkDO.ypgg,qYkYpkDO.dw.as("zxdw"),
qYkYpkDO.cydw,qYkYpkDO.cyzhxs,qYbYpkDTO.ybdm,qYbYpkDTO.ybmc,qUvShybYpkDTO.gg.as("ybgg"),qUvShybYpkDTO.bzdw,
qWmShYpdwszDTO.ptypxs,qYkYpkDO.pym,qYkYpkDO.wbm))
.from(qYkYpkDO)
.innerJoin(qYbYpkDTO).on(qYkYpkDO.wssdm.eq(qYbYpkDTO.wssdm),qYkYpkDO.ypdm.eq(qYbYpkDTO.ypdm))
.innerJoin(qUvShybYpkDTO).on(qYbYpkDTO.ybdm.eq(qUvShybYpkDTO.bzdm),qYbYpkDTO.wssdm.eq(qUvShybYpkDTO.jgdm))
.leftJoin(qWmShYpdwszDTO).on(qWmShYpdwszDTO.jgdm.eq(qYkYpkDO.wssdm),qYkYpkDO.ypdm.eq(qWmShYpdwszDTO.ypdm),qWmShYpdwszDTO.ptdm.eq(qYbYpkDTO.ybdm))
.where(builder).offset(pageNum*pageSize).limit(pageSize).orderBy(qYkYpkDO.ypdm.desc());
resultMap.put("rows",jpaQuery.fetch());
resultMap.put("total",jpaQuery.fetchCount());
entityManager.flush();
entityManager.clear();
return resultMap;
}
7、jpa实现单表动态查询
(1)、新建实体类,此处省略
(2)、service
public List<OrganizationAuthorityDO> queryUseDrugContentInfo(String mbjgdm,int mbjgxzbz){
BooleanBuilder builder = new BooleanBuilder();
QOrganizationAuthorityDO qOrganizationAuthorityDO = QOrganizationAuthorityDO.organizationAuthorityDO;
builder.and(qOrganizationAuthorityDO.mllb.eq("0"));
if(mbjgxzbz==1){
builder.and(qOrganizationAuthorityDO.mbjgxzbz.eq(mbjgxzbz));
}
builder.and(qOrganizationAuthorityDO.mbjgdm.eq(mbjgdm));
Iterable<OrganizationAuthorityDO> iterable = organizationAuthorityRepository.findAll(builder);
List<OrganizationAuthorityDO> list = IterableUtils.toList(iterable);
return list;
}
8、jpa单表动态分页查询
(1)、service
public Map<String,Object> findAllByDwdm(String jgbm, int pageNum,int pageSize,String keyWord){
JPAQueryFactory jpaQueryFactory=new JPAQueryFactory(entityManager);
BooleanBuilder builder = new BooleanBuilder();
QSys2FylbDTO qSys2FylbDTO = QSys2FylbDTO.sys2FylbDTO;
if(StringUtils.isNotEmpty(keyWord)){
builder.or(qSys2FylbDTO.pym.like("%".concat(keyWord).concat("%")));
builder.or(qSys2FylbDTO.wbm.like("%".concat(keyWord).concat("%")));
builder.or(qSys2FylbDTO.fbmc.like("%".concat(keyWord).concat("%")));
builder.or(qSys2FylbDTO.fbdm.eq(keyWord));
}
Map<String,Object> resultMap=new HashMap<>();
builder.and(qSys2FylbDTO.dwdm.eq(jgbm));
JPAQuery<Sys2FylbDTO> jpaQuery=jpaQueryFactory.select(qSys2FylbDTO)
.from(qSys2FylbDTO)
.where(builder).offset(pageNum*pageSize).limit(pageSize).orderBy(qSys2FylbDTO.fbdm.asc());
resultMap.put("rows",jpaQuery.fetch());
resultMap.put("total",jpaQuery.fetchCount());
return resultMap;
}