JPA的好搭档 - QueryDSL

@Query(value="select bs.* from " +
            "(select t.baseid,t.basesn,t.basecreatorfullname,t.basestatus,t.basesummary,'北京' faultprovince, '北京' faultcity, " +
            "replace(t.INC_ResponseLevel,'响应','') inc_responselevel,t.inc_happentime,t.basecreatedate,t.inc_equipmentmanufacturer, " +
            "t.inc_ne_name,t.inc_alarm_id,t.site_alerttype,t.alarmlevel,t.inc_alarm_desc,t.baseacceptouttime,t.basedealouttime, " +
            "decode(t.flagPretreatment,null,'否','未预处理','否','是') flagpretreatment,decode(t.flagPretreatment,null,'否','未预处理','否','是') flagpretreatment2, " +
            "decode(nvl(t.isImportantIncident, 0),1,'是','否') isimportantincident, decode(nvl(t.INC_IsEffectOP, 0),1,'是','否') inc_iseffectop, " +
            "t.operationdeal, t.t0_deal,t.faultdescription,t.dealguomodo,t.inc_alarm_cleartime,t.clearinctime, " +
            "t.renewtime,t.baseclosedate,nvl2(t.checkdealresult,'是','否') checkdealresult,g.jtitem1,g.jtitem2,g.jtitem3,t.reasontype " +
            "from T_DEMO t, T_DEMO_MAP g " +
            "where t.Sheet_type = '传输网络故障处理工单' and t.basestatus = '已归档' " +
            "and t.baseCloseDate >= :beginTime and t.baseCloseDate < :endTime " +
            "and (t.Withdraw_Desc = g.withdraw_desc or t.alarmname = g.alarmname) " +
            "union all " +
            "select t.baseid,t.basesn,t.basecreatorfullname,t.basestatus,t.basesummary,'北京' faultprovince, '北京' faultcity, " +
            "replace(t.INC_ResponseLevel,'响应','') inc_responselevel,t.inc_happentime,t.basecreatedate,t.inc_equipmentmanufacturer, " +
            "t.inc_ne_name,t.inc_alarm_id,t.site_alerttype,t.alarmlevel,t.inc_alarm_desc,t.baseacceptouttime,t.basedealouttime, " +
            "decode(t.flagPretreatment,null,'否','未预处理','否','是') flagpretreatment,decode(t.flagPretreatment,null,'否','未预处理','否','是') flagpretreatment2, " +
            "decode(nvl(t.isImportantIncident, 0),1,'是','否') isimportantincident, decode(nvl(t.INC_IsEffectOP, 0),1,'是','否') inc_iseffectop, " +
            "t.operationdeal,t.t0_deal,t.faultdescription,t.dealguomodo,t.inc_alarm_cleartime,t.clearinctime, " +
            "t.renewtime,t.baseclosedate,nvl2(t.checkdealresult,'是','否') checkdealresult,w.jtitem1,w.jtitem2,w.jtitem3,t.reasontype " +
            "from WF_BMCC_EOMS_ITDealFault t, (select distinct c.value,c.jtitem1,c.jtitem2,c.jtitem3 from WF_Config_EL_00_NetType c) w " +
            "where (t.Sheet_type = 'test0' or t.Sheet_type = 'test1' or t.Sheet_type = 'test2' " +
            "or t.Sheet_type = 'test3' or t.Sheet_type = 'test4') and t.basestatus = '已归档' " +
            "and t.baseCloseDate >= :beginTime and t.baseCloseDate < :endTime " +
            "and t.faultclass = w.value "+
            "  ) bs " +
            "where not exists (select dp.processbaseid from T_DEAL dp,T_GROUPS dg where dp.ProcessBaseSchema = " +
            "'DEALFAULT' and dp.processbaseid = bs.baseid and dp.groupid = dg.groupid) ",
            nativeQuery=true)
    List<DemoEntity> findOne(@Param("beginTime") long beginTime, @Param("endTime") long endTime);

implementation 'com.querydsl:querydsl-jpa'
annotationProcessor 'com.querydsl:querydsl-apt:5.0.0:jpa'
annotationProcessor 'jakarta.persistence:jakarta.persistence-api'

@Service
public class DemoQueryDSL {
    @Autowired
    private JPAQueryFactory queryFactory;
}

/**
 * 职员表
 */
@Data
@Entity
@Table(name = "T_EMP")
public class Emp {
    @Id
    private String id;//ID
    @Column
    private String name;//姓名
    @Column
    private Integer age;//年龄
    @Column
    private String sex;//性别
    @Column
    private String depId;//部门ID
}

/**
 * 部门表
 */
@Data
@Entity
@Table(name = "T_DEP")
public class Dep {
    @Id
    private String id;//ID
    @Column
    private String depName;//部门名称
}

public void simpleSql(){
    QEmp emp = QEmp.emp;//员工表
    /**
     * 简单条件查询、排序，相当于sql：
     * select * from T_EMP
     *  where name like '张%' and age > 25
     *  order by age desc;
     */
    List<Emp> empList = queryFactory.select(emp)
            .where(emp.name.startsWith("张").and(emp.age.gt(25)))
            .orderBy(emp.age.desc())
            .fetch();

    /**
     * 分组查询，相当于sql：
     * select e.dep_id, max(e.age) from T_EMP e
     *  grouping by e.depId;
     */
    List<Tuple> list = queryFactory.select(emp.depId, emp.age.max())
            .groupBy(emp.depId)
            .fetch();

    /**
     * 分页查询，相当于sql：
     * select * from T_EMP e
     *  where e.dep_id='123'
     *  limit 20 10;
     */
    List<Emp> pageList = queryFactory.select(emp)
            .where(emp.depId.eq("123"))
            .offset(20)
            .limit(10)
            .fetch();
}

public void complexSql(){
    QEmp emp = QEmp.emp;//员工表
    QDep dep = QDep.dep;//部门表

    /**
     * 关联查询，相当于sql：
     * select e.* from T_EMP e
     *  left join T_DEP d on e.dep_id = d.id
     *  where d.dep_name = '财务部'
     */
    List<Emp> empList = queryFactory.select(emp)
            .from(emp)
            .leftJoin(dep).on(emp.depId.eq(dep.id))
            .where(dep.depName.eq("财务部"))
            .fetch();

    /**
     * 嵌套查询，相当于sql：
     * select e.* from T_EMP e
     *  where e.dep_id = (
     *      select id form T_DEP where dep_name = '财务部'
     *  )
     */
    List<Emp> empList1 = queryFactory.select(emp)
            .from(emp)
            .where(emp.depId.eq(
                    queryFactory.select(dep.id).from(dep)
                            .where(dep.depName.eq("财务部"))
            ))
            .fetch();

    /**
     * 动态条件、别名、获取不同结果：

     */
    BooleanBuilder condition = new BooleanBuilder();
    condition.and(emp.age.goe(25));
    if (true) {//动态条件
        condition.and(emp.sex.eq("男"));
    }
    queryFactory.select(emp.name.as("fullname"))
            .where(condition)
            .fetch();//查找多个结果，返回集合
          //.fetchOne();//查询只返回一个结果，如果返回多个则抛异常
          //.fetchFirst();//返回多个结果时，只取第一个
}