jooq多表查询_jooq使用示例

packagecom.transsnet.sims.business;importjava.util.ArrayList;importjava.util.HashMap;importjava.util.List;importjava.util.Map;importorg.apache.commons.lang3.StringUtils;importorg.jooq.Condition;importorg.jooq.DSLContext;importorg.jooq.Record;importorg.jooq.Record1;importorg.jooq.Record2;importorg.jooq.Result;importorg.jooq.SelectJoinStep;importorg.jooq.UpdateSetFirstStep;importorg.jooq.UpdateSetMoreStep;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.stereotype.Component;importorg.springframework.transaction.annotation.Transactional;importorg.springframework.util.CollectionUtils;importcom.transsnet.sims.common.dto.PageDto;importcom.transsnet.sims.common.dto.PosDto;importcom.transsnet.sims.common.dto.RetailerDto;importcom.transsnet.sims.common.dto.SaDto;importcom.transsnet.sims.storage.easybuy_aad.Tables;importcom.transsnet.sims.storage.easybuy_aad.tables.daos.AadSaConfigDao;importcom.transsnet.sims.storage.easybuy_aad.tables.pojos.AadSaConfig;importcom.transsnet.sims.storage.easybuy_aad.tables.records.AadSaConfigRecord;

@Componentpublic classDemoBis {

@AutowiredprivateAadSaConfigDao aadSaConfigDao;

@AutowiredprivateDSLContext dslContext;private com.transsnet.sims.storage.easybuy_aad.tables.AadSaConfig aadSaConfig =Tables.AAD_SA_CONFIG;private com.transsnet.sims.storage.easybuy_aad.tables.AadPosConfig aadPosConfig =Tables.AAD_POS_CONFIG;private com.transsnet.sims.storage.easybuy_aad.tables.AadRetailerConfig aadRetailerConfig =Tables.AAD_RETAILER_CONFIG;private com.transsnet.sims.storage.easybuy_aad.tables.AadIndexTable aadIndexTable =Tables.AAD_INDEX_TABLE;/*** 分页查询list

*@parampageNum

*@parampageSize

*@paramdto

*@return

*/

public PageDto pageList(int pageNum, intpageSize, SaDto dto) {

SelectJoinStep selectRecord =dslContext.select().from(aadSaConfig);

SelectJoinStep> selectCountRecord =dslContext.selectCount().from(aadSaConfig);//写一个一定成立的条件，这里写主键不为空

Condition eq =aadSaConfig.ID.isNotNull();//按需要拼接查询条件

if(StringUtils.isNotBlank(dto.getSaId())) {

eq= eq.and(aadSaConfig.SA_ID.like("%" + dto.getSaId() + "%"));

}if(StringUtils.isNotBlank(dto.getStatus())) {

eq=eq.and(aadSaConfig.STATUS.eq(dto.getStatus()));

}//查询总数

Integer count = selectCountRecord.where(eq).fetchOne().into(Integer.class);//构造分页组件

PageDto pageDto = newPageDto(pageNum, pageSize, count);//查询数据

List list =selectRecord.where(eq).orderBy(aadSaConfig.ID.desc())

.limit(pageSize)

.offset(pageSize* (pageNum - 1))

.fetchInto(AadSaConfig.class);

pageDto.setList(list);returnpageDto;

}/*** 查询list

*@paramdto

*@return

*/

public ListfetchList(SaDto dto) {

SelectJoinStep selectRecord =dslContext.select().from(aadSaConfig);//写一个一定成立的条件，这里写主键不为空

Condition eq =aadSaConfig.ID.isNotNull();//按需要拼接查询条件

if(StringUtils.isNotBlank(dto.getSaId())) {

eq= eq.and(aadSaConfig.SA_ID.like("%" + dto.getSaId() + "%"));

}if(StringUtils.isNotBlank(dto.getStatus())) {

eq=eq.and(aadSaConfig.STATUS.eq(dto.getStatus()));

}//查询数据

List list =selectRecord.where(eq).orderBy(aadSaConfig.ID.desc())

.fetchInto(AadSaConfig.class);returnlist;

}/*** 根据销售id查询详细

*@paramsaId

*@return

*/

publicAadSaConfig detail(String saId) {

List beans =aadSaConfigDao.fetchBySaId(saId);if(!CollectionUtils.isEmpty(beans)) {return beans.get(0);

}return null;

}/*** 新增记录*/@Transactionalpublic voidcreate(SaDto dto) {//查询销售序列值，行锁select for update

Integer index =dslContext.select(aadIndexTable.INDEX).from(aadIndexTable)

.where(aadIndexTable.TABLE.eq("aad_sa_config").and(aadIndexTable.FIELD.eq("sa_id")))

.forUpdate()

.fetchOneInto(Integer.class);//填充销售ID为定长字段，5位长度

String saId = "SA" + String.format("%05d", index);

dslContext.insertInto(aadSaConfig)

.set(aadSaConfig.SA_ID, saId)//销售Id按规则自增

.set(aadSaConfig.STATUS, dto.getStatus())

.execute();//添加记录成功后需要将index加1

dslContext.update(aadIndexTable).set(aadIndexTable.INDEX, ++index)

.where(aadIndexTable.TABLE.eq("aad_sa_config").and(aadIndexTable.FIELD.eq("sa_id")))

.execute();

}/*** 更新记录*/

public voidupdate(SaDto dto) {

UpdateSetFirstStep updateRecord =dslContext.update(aadSaConfig);

UpdateSetMoreStep setRecord = null;if(StringUtils.isNotBlank(dto.getSaName())) {

setRecord=updateRecord.set(aadSaConfig.SA_NAME, dto.getSaName().toUpperCase());

}if(StringUtils.isNotBlank(dto.getStatus())) {

setRecord=updateRecord.set(aadSaConfig.STATUS, dto.getStatus());

}if(setRecord == null) {return;

}

setRecord.where(aadSaConfig.SA_ID.eq(dto.getSaId())).execute();

}/*** 删除记录*/

public voiddelete(String saId) {

dslContext.delete(aadSaConfig).where(aadSaConfig.SA_ID.eq(saId)).execute();

}/*** 根据销售名称查询销售信息

*@paramsaName

*@return

*/

public List>fetchSaByName(String saName){//查询指定的字段

Result> resultRecord =dslContext.select(aadSaConfig.SA_ID, aadSaConfig.SA_NAME)

.from(aadSaConfig)

.where(aadSaConfig.SA_NAME.like("%" + saName + "%"))

.fetch();

List> list = new ArrayList>();//获取查询出来的字段值

for(Record2record : resultRecord) {

Map item = new HashMap();

item.put("label", record.getValue(aadSaConfig.SA_NAME));

item.put("value", record.getValue(aadSaConfig.SA_ID));

list.add(item);

}returnlist;

}/*** 联表更新数据

*@paramdto*/

public voidupdatePos(PosDto dto) {/** 最初是想使用insert into select，但是jooq中只能整表复制，但我们只需要某几个字段

* 尝试过先insert，然后再update，但这样比较消耗性能

* 试了几次后发现最好还是先把所需数据查出来，再进行插入*/

//获取商户相关信息，赋值到门店上

RetailerDto retailer = dslContext.selectFrom(aadRetailerConfig).where(aadRetailerConfig.RETAILER_ID.eq(dto.getRetailerId())).fetchOneInto(RetailerDto.class);//新增数据

dslContext.insertInto(aadPosConfig)

.set(aadPosConfig.POS_ID, dto.getPosId())

.set(aadPosConfig.POS_NAME, dto.getPosName())

.set(aadPosConfig.RETAILER_ID, retailer.getRetailerId())

.set(aadPosConfig.RETAILER_NAME, retailer.getRetailerName())

.execute();

}

}