MyBatis 代码自动生成 where 条件 - sql - blog - 计算机学科 | D & PersonalBlog = D の Java = 别怕路长梦远

自定义动态 sql

	public static String getSumSql(String caseField, List caseList, String valueColumn, boolean isRound) {
	List<String> sumSqlList = new ArrayList<>();
	for (Object groupValue : caseList) {
	String sumSql = " sum(CASE WHEN {} = '{}' THEN {} ELSE 0 END) AS \"{}\" ";
	if (isRound) {
	sumSql = " round(sum(CASE WHEN {} = '{}' THEN {} ELSE 0 END),2) AS \"{}\" ";
	}
	sumSqlList.add(StrUtil.format(sumSql, caseField, groupValue, valueColumn, groupValue));
	}
	return StrUtil.join(",", sumSqlList);
	}

这个代码会动态的返回 sql 如下：

sum(CASE WHEN xxx = 2024 THEN xxx ELSE 0 END) AS "2024",sum(CASE WHEN xxx = 2023 THEN xxx ELSE 0 END) AS "2023",sum(CASE WHEN xxx = 2025 THEN xxx ELSE 0 END) AS "2025"

编写 warpper

	private List<Map<String, Object>> getTotalResultsByGasType(String analysisId, String codeList, String name, String manBody
	, List<Integer> groups, String totalType, String showType) {
	MPJLambdaWrapper<YearLngGasSupply> wrapper = new MPJLambdaWrapper();
	String sumSql = JsonToSqlUtil.getSumSql("year", groups, "supply".equals(showType) ? "plan_supply_num" : "load_rate", true);
	wrapper.eq(YearLngGasSupply::getAnalysisId, analysisId);
	if (ObjUtil.isNotEmpty(codeList)) {
	wrapper.in(YearLngGasSupply::getAdministrativeDivisionCode, codeList.split(","));
	}
	if (ObjUtil.isNotEmpty(manBody)) {
	wrapper.in(YearLngGasSupply::getMainBody, manBody.split(","));
	}
	wrapper.like(ObjUtil.isNotEmpty(name), YearLngGasSupply::getResourceName, name);
	wrapper.eq(YearLngGasSupply::getIsDeleted, FieldState.DELETE_NO);
	return yearLngGasSupplyMapper.getTotalResultsByGasType(wrapper, sumSql, totalType, analysisId);
	}

编写 sql

	@Select(" with base_table as (\n" +
	" select ${sumSql}," +
	" '${mainBody}' as main_body,'' as resource_name,'' as resource_code,\n" +
	" '' as administrative_division_name,'' as administrative_division_code, '0' as can_edit" +
	" ,'' as gas_import_capacity" +
	" ,'' as design_accept_scale" +
	" ,'' as use_time" +
	" FROM pro_sup_year_lng_gas_supply t " +
	" ${ew.customSqlSegment}\n" +
	" and t.resource_code not in (select resource_code from pro_sup_resource_config t" +
	" where t.analysis_id=#{analysisId} and is_deleted = 0 and is_join_supply=1)" +
	")\n" +
	" SELECT a.main_body, '0' AS disabled, t.* FROM ( SELECT '${mainBody}' as main_body) a LEFT JOIN base_table t ON 1=1 ")
	List<Map<String, Object>> getTotalResultsByGasType(@Param(Constants.WRAPPER) Wrapper<YearLngGasSupply> queryWrapper
	, @Param("sumSql") String sumSql, @Param("mainBody") String mainBody, @Param("analysisId") String analysisId);

# 介绍

# Mybatis-plus:${ew.custcomSqlSegment} 用法

# entity

	package com.poilearn.entity;

	import com.baomidou.mybatisplus.annotation.IdType;
	import com.baomidou.mybatisplus.annotation.TableField;
	import com.baomidou.mybatisplus.annotation.TableId;
	import com.baomidou.mybatisplus.annotation.TableName;
	import java.io.Serializable;
	import java.math.BigDecimal;

	import lombok.AllArgsConstructor;
	import lombok.Getter;
	import lombok.NoArgsConstructor;
	import lombok.Setter;

	/**
	* <p>
	* 商品表
	* </p>
	*
	* @author dkx
	* @since 2024-09-08
	*/
	@Getter
	@Setter
	@AllArgsConstructor
	@NoArgsConstructor
	@TableName("tab_shop")
	public class Shop implements Serializable {

	private static final long serialVersionUID = 1L;

	/**
	* 主键
	*/
	@TableId(value = "id", type = IdType.ASSIGN_UUID)
	private Long id;

	/**
	* 商品名称
	*/
	@TableField("name")
	private String name;

	/**
	* 商品描述
	*/
	@TableField("description")
	private String description;

	/**
	* 商品类型
	*/
	@TableField("type")
	private String type;

	/**
	* 商品价格
	*/
	@TableField("price")
	private BigDecimal price;
	}

# mapper

	@Mapper
	public interface ShopMapper extends BaseMapper<Shop> {

	@Select(
	"SELECT * FROM tab_shop\n" +
	"${ew.customSqlSegment}"
	)
	List<Shop> queryList(@Param(Constants.WRAPPER) Wrapper<Shop> wrapper);
	}

# serviceImpl

	@Service
	public class ShopServiceImpl extends ServiceImpl<ShopMapper, Shop> implements IShopService {
	@Autowired
	private ShopMapper shopMapper;

	@Override
	public List<Shop> queryList(String name) {
	return shopMapper.queryList(Wrappers.<Shop>lambdaQuery().like(!StringUtil.isNullOrEmpty(name), Shop::getName, name));
	}
	}

# controller

	@RestController
	@RequestMapping("/poilearn/shop")
	public class ShopController {
	@Autowired
	private IShopService iShopService;

	@GetMapping("/all")
	public List<Shop> query(@RequestParam(value = "name", required = false) String name) {
	return iShopService.queryList(name);
	}
	}

结果：

不模糊查询

模糊查询

# Mybatis-plus:${ew.sqlSelect} 用法

# entity

跟上面一样公用

# mapper

	@Mapper
	public interface ShopMapper extends BaseMapper<Shop> {
	@Select(
	"SELECT ${ew.sqlSelect} FROM tab_shop\n" +
	"${ew.customSqlSegment}"
	)
	List<Shop> selectOrList(@Param(Constants.WRAPPER) Wrapper<Shop> wrapper);
	}

# serviceImpl

	@Service
	public class ShopServiceImpl extends ServiceImpl<ShopMapper, Shop> implements IShopService {

	@Override
	public List<Shop> selectOrList(String name) {
	return shopMapper.selectOrList(Wrappers.<Shop>lambdaQuery().select(Shop::getName, Shop::getDescription).like(!StringUtil.isNullOrEmpty(name), Shop::getName, name));
	}
	}

# controller

	@RestController
	@RequestMapping("/poilearn/shop")
	public class ShopController {
	@Autowired
	private IShopService iShopService;

	@GetMapping("/selectOrList")
	public List<Shop> selectOrList(@RequestParam(value = "name", required = false) String name) {
	return iShopService.selectOrList(name);
	}
	}