@Query(value="select bs.* from " +
            "(select t.baseid,t.basesn,t.basecreatorfullname,t.basestatus,t.basesummary,'北京' faultprovince, '北京' faultcity, " +
            "replace(t.INC_ResponseLevel,'響應(yīng)','') 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,'否','未預(yù)處理','否','是') flagpretreatment,decode(t.flagPretreatment,null,'否','未預(yù)處理','否','是') 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 = '傳輸網(wǎng)絡(luò)故障處理工單' 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,'響應(yīng)','') 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,'否','未預(yù)處理','否','是') flagpretreatment,decode(t.flagPretreatment,null,'否','未預(yù)處理','否','是') 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;//部門(mén)ID
}

/**
 * 部門(mén)表
 */
@Data
@Entity
@Table(name = "T_DEP")
public class Dep {
    @Id
    private String id;//ID
    @Column
    private String depName;//部門(mén)名稱(chēng)
}

public void simpleSql(){
    QEmp emp = QEmp.emp;//員工表
    /**
     * 簡(jiǎn)單條件查詢(xún)、排序，相當(dāng)于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();

    /**
     * 分組查詢(xún)，相當(dāng)于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();

    /**
     * 分頁(yè)查詢(xún)，相當(dāng)于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;//部門(mén)表

    /**
     * 關(guān)聯(lián)查詢(xún)，相當(dāng)于sql：
     * select e.* from T_EMP e
     *  left join T_DEP d on e.dep_id = d.id
     *  where d.dep_name = '財(cái)務(wù)部'
     */
    List<Emp> empList = queryFactory.select(emp)
            .from(emp)
            .leftJoin(dep).on(emp.depId.eq(dep.id))
            .where(dep.depName.eq("財(cái)務(wù)部"))
            .fetch();

    /**
     * 嵌套查詢(xún)，相當(dāng)于sql：
     * select e.* from T_EMP e
     *  where e.dep_id = (
     *      select id form T_DEP where dep_name = '財(cái)務(wù)部'
     *  )
     */
    List<Emp> empList1 = queryFactory.select(emp)
            .from(emp)
            .where(emp.depId.eq(
                    queryFactory.select(dep.id).from(dep)
                            .where(dep.depName.eq("財(cái)務(wù)部"))
            ))
            .fetch();

    /**
     * 動(dòng)態(tài)條件、別名、獲取不同結(jié)果：

     */
    BooleanBuilder condition = new BooleanBuilder();
    condition.and(emp.age.goe(25));
    if (true) {//動(dòng)態(tài)條件
        condition.and(emp.sex.eq("男"));
    }
    queryFactory.select(emp.name.as("fullname"))
            .where(condition)
            .fetch();//查找多個(gè)結(jié)果，返回集合
          //.fetchOne();//查詢(xún)只返回一個(gè)結(jié)果，如果返回多個(gè)則拋異常
          //.fetchFirst();//返回多個(gè)結(jié)果時(shí)，只取第一個(gè)
}