首页 计算机学科 blog sql

# MyBatisPlus3-Provider 的增删改查使用

MyBatis 3.x 版本提供了以下 4 个 CRUD 的高级注解。

@SelectProvider：用于构建动态查询 SQL。

@InsertProvider：用于构建动态新增 SQL。

@UpdateProvider：用于构建动态更新 SQL。

@DeleteProvider：用于构建动态删除 SQL。

动态 SQL 注解主要用于编写动态 SQL。这里以 @SelectProvider 为例，它主要包含两个注解属性，其中，type 表示工具类，method 表示工具类的某个方法（用于返回具体的 SQL 语句）。

以下代码可以构建动态 SQL，实现查询功能：

创建表：

CREATE TABLE `tab_shop` (
  `id` bigint(20) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '商品名称',
  `description` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '商品描述',
  `type` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '商品类型',
  `price` decimal(10,2) DEFAULT NULL COMMENT '商品价格',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='商品表';

# 一，查询

Mapper

@Mapper
public interface ShopMapper extends BaseMapper<Shop> {
    @SelectProvider(type = Provider.class, method = "selectShop")
    List<Shop> selectShop(String id);
    @SelectProvider(type = Provider.class, method = "selectShopWrapper")
    List<Shop> selectShopWrapper(@Param(Constants.WRAPPER) Wrapper<Shop> wrapper);
}
provider 类：在项目中创建该类里面定义自定义动态 sql
public class Provider { 
	public String selectShop(String id) {
        return new SQL() {
            {
                SELECT("*");
                FROM("tab_shop");
                if (!StringUtil.isNullOrEmpty(id)) {
                    WHERE("id = #{id}");
                }
            }
        }.toString();
    }
    public String selectShopWrapper(@Param(Constants.WRAPPER) Wrapper<Shop> wrapper) {
        return new SQL() <!--swig0-->.toString();
    }
}
serviceImpl
@Service
@Slf4j
public class ShopServiceImpl extends ServiceImpl<ShopMapper, Shop> implements IShopService {  
    @Autowired
    private ShopMapper shopMapper;
	public List<Shop> selectShop(String id) {
        return shopMapper.selectShop(id);
    }
    @Override
    public List<Shop> selectShopWrapper(String id) {
        return shopMapper.selectShopWrapper(
                Wrappers.<Shop>lambdaQuery()
                        .eq(!StringUtil.isNullOrEmpty(id), Shop::getId, id)
        );
    }
}
controller
@RestController
@RequestMapping("/poilearn/shop")
public class ShopController {
    @Autowired
    private IShopService iShopService;
	@GetMapping("/getShop")
    public List<Shop> getShop(@RequestParam("id") String id) {
        return iShopService.selectShop(id);
    }
    @GetMapping("/getShopWrapper")
    public List<Shop> getShopWrapper(@RequestParam("id") String id) {
        return iShopService.selectShopWrapper(id);
    }
}
查询结果：
image-20240913174226917
# 二，新增
mapper
@Mapper
public interface ShopMapper extends BaseMapper<Shop> {
	@InsertProvider(type = Provider.class, method = "saveShop")/
    // 自动生成主键
    @Options(useGeneratedKeys = true, keyColumn = "id", keyProperty = "id")
    void insertShop(Shop shop);
}
provider 类：在项目中创建该类里面定义自定义动态 sql
public class Provider { 
	public String saveShop(Shop shop) {
        String values = String.format("'%s', '%s', '%s'", shop.getName(), shop.getDescription(), shop.getType());
        return new SQL() <!--swig1-->.toString();
    }
}
serviceImpl
@Service
@Slf4j
public class ShopServiceImpl extends ServiceImpl<ShopMapper, Shop> implements IShopService {
    @Autowired
    private ShopMapper shopMapper;
	@Override
    public void saveShop(Shop shop) {
        shopMapper.insertShop(shop);
    }
}
controller
@RestController
@RequestMapping("/poilearn/shop")
public class ShopController {
    @Autowired
    private IShopService iShopService;
	@PostMapping("/insShop")
    public void insShop(@RequestBody Shop shop) {
        iShopService.saveShop(shop);
    }
}
结果：
image-20240913174713094
# 三，更新
mapper
@Mapper
public interface ShopMapper extends BaseMapper<Shop> {
	@UpdateProvider(type = Provider.class, method = "editShop")
    int editShop(@Param(Constants.WRAPPER) Wrapper<Shop> wrapper, Shop shop);
}
provider 类：在项目中创建该类里面定义自定义动态 sql
public class Provider {
	public String editShop(@Param(Constants.WRAPPER) Wrapper<Shop> wrapper, Shop shop) {
        String sets = String.format("%s, %s", "name = " + shop.getName(), "description = " + shop.getDescription());
        return new SQL() <!--swig2-->.toString();
    }
 }
serviceImpl
@Service
@Slf4j
public class ShopServiceImpl extends ServiceImpl<ShopMapper, Shop> implements IShopService {
    @Autowired
    private ShopMapper shopMapper;
	@Override
    public int editShop(Shop shop) {
        return shopMapper.editShop(
                Wrappers.<Shop>lambdaUpdate()
                        .eq(Shop::getId, shop.getId()), shop
        );
    }
}
controller
@RestController
@RequestMapping("/poilearn/shop")
public class ShopController {
    @Autowired
    private IShopService iShopService;
	@PostMapping("/editShop")
    public boolean editShop(@RequestBody Shop shop) {
        return iShopService.editShop(shop) > 0;
    }
}
结果：
image-20240913174934390
# 四，删除
mapper
@Mapper
public interface ShopMapper extends BaseMapper<Shop> {
	@DeleteProvider(type = Provider.class, method = "delShop")
    int delShop(String id);
}
provider 类：在项目中创建该类里面定义自定义动态 sql
public class Provider { 
	public String delShop(String id) {
        return new SQL() <!--swig3-->.toString();
    }
}
serviceImpl
@Service
@Slf4j
public class ShopServiceImpl extends ServiceImpl<ShopMapper, Shop> implements IShopService {
    @Autowired
    private ShopMapper shopMapper;
	@Override
    public int delShop(String id) {
        return shopMapper.delShop(id);
    }
}
controller
@RestController
@RequestMapping("/poilearn/shop")
public class ShopController {
    @Autowired
    private IShopService iShopService;
	@GetMapping("/delShop")
    public boolean delShop(@RequestParam("id") String id) {
        return iShopService.delShop(id) > 0;
    }
}
结果：
image-20240913175146834