MyBatis-Plus 條件查詢器的實現

@TableName("t_user")
public class User {
	private Long id;
	private String userName;
	private Integer age;
	private String email;
}

mybatis-plus:
 configuration:
  # 配置MyBatis日志
  log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
 global-config:
  db-config:
   # 配置MyBatis-Plus操作表的默認前綴
   table-prefix: t_

@TableName("t_user")
public class User {
	@TableId(value = "id", type = IdType.AUTO)
	private Long id;
	private String userName;
	private Integer age;
	private String email;
}

@Getter
public enum IdType {
    /**
     * 數據庫ID自增
     * <p>該類型請確保數據庫設置瞭 ID自增 否則無效</p>
     */
    AUTO(0),
    /**
     * 該類型為未設置主鍵類型(註解裡等於跟隨全局,全局裡約等於 INPUT)
     */
    NONE(1),
    /**
     * 用戶輸入ID
     * <p>該類型可以通過自己註冊自動填充插件進行填充</p>
     */
    INPUT(2),

    /* 以下3種類型、隻有當插入對象ID 為空，才自動填充。 */
    /**
     * 分配ID (主鍵類型為number或string）,
     * 默認實現類 {@link com.baomidou.mybatisplus.core.incrementer.DefaultIdentifierGenerator}(雪花算法)
     *
     * @since 3.3.0
     */
    ASSIGN_ID(3),
    /**
     * 分配UUID (主鍵類型為 string)
     * 默認實現類 {@link com.baomidou.mybatisplus.core.incrementer.DefaultIdentifierGenerator}(UUID.replace("-",""))
     */
    ASSIGN_UUID(4);

    private final int key;

    IdType(int key) {
        this.key = key;
    }
}

@TableName("t_user")
public class User {

	// 處理主鍵相關的字段名不一致 以及設置主鍵自增策略
	@TableId(value = "id", type = IdType.AUTO)
	private Long id;

	// 在mp種默認的設置可以將數據庫中的字段名xxx_xxx轉化為相應的駝峰命名
	@TableField(value = "user_name")
	private String userName;

	@TableField(value = "age")
	private Integer age;

	// exist=false 表示查詢時候不顯示
	@TableField(exist = false)
	private String email;
	
}

@TableName("t_user")
public class User {

	// 處理主鍵相關的字段名不一致 以及設置主鍵自增策略
	@TableId(value = "id", type = IdType.AUTO)
	private Long id;

	// 在mp種默認的設置可以將數據庫中的字段名xxx_xxx轉化為相應的駝峰命名
	@TableField(value = "user_name")
	private String userName;

	@TableField(value = "age")
	private Integer age;

	@TableField(value = "email")
	private String email;
	
	// 邏輯刪除 0 標識未刪除 1表示刪除瞭
	@TableLogic
	private Integer isDeleted;

}

UPDATE t_user SET is_deleted=1 WHERE id=? AND is_deleted=0

SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0

/**
	 * 測試wraper封裝查詢條件1
	 * 
	 * @throws Exception
	 */
	@Test
	public void testQuery5() {
		QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
		// sql: SELECT COUNT( * ) FROM t_user WHERE is_deleted=0 AND (age BETWEEN ? AND
		// ?)
		queryWrapper.between("age", 18, 23);
		Long list = userMapper.selectCount(queryWrapper);
		System.out.println(list);
	}

/**
	 * 測試wraper封裝查詢條件2
	 * 
	 * @throws Exception
	 */
	@Test
	public void testQuery9() {
		QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
		// SELECT id,user_name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND
		// (email IS NOT NULL AND email LIKE ?)
		queryWrapper.isNotNull("email").like("email", "163.com");
		List<User> list = userMapper.selectList(queryWrapper);
		list.forEach(System.out::println);
	}

/**
	 * 測試wraper封裝查詢條件3
	 * 
	 * @throws Exception
	 */
	@Test
	public void testQuery10() {
		QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
		// SELECT age,email,user_name FROM t_user WHERE is_deleted=0
		queryWrapper.select("age", "email", "user_name");
		List<Map<String, Object>> list = userMapper.selectMaps(queryWrapper);
		list.forEach(System.out::println);
	}

/**
	 * 測試wraper封裝查詢條件4
	 * 
	 * @throws Exception
	 */
	@Test
	public void testQuery11() {
		QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
		// SELECT age,email,user_name FROM t_user WHERE is_deleted=0 AND (user_name = ?)
		queryWrapper.select("age", "email", "user_name");
		queryWrapper.eq("user_name", "test");
		User list = userMapper.selectOne(queryWrapper);
		System.out.println(list);
	}

@Test
	public void testQuery7() throws Exception {
		User user = new User();
		// 將用戶名中包含有a並且（年齡大於20或郵箱為null）的用戶信息修改
		user.setEmail("[email protected]");
		QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
		// UPDATE t_user SET age=?, email=? WHERE (user_name LIKE ? AND (age > ? OR email IS NULL))
		queryWrapper.like("user_name", "a")
					.and(
							i -> i.ge("age", 20)
								  .or()
								  .isNull("email"));
		List<User> list = userMapper.selectList(queryWrapper);

		list.forEach(System.out::println);
	}

@GetMapping("/queryZI")
	public List<User> queryZI() {
		// SELECT id,user_name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (id IN ( SELECT id FROM t_user WHERE id <=100))
		QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
		queryWrapper.inSql("id", " SELECT id FROM t_user WHERE id <=100");
		List<User> maps = userMapper.selectList(queryWrapper);
		return maps;
	}

@Test
	public void testUpdate2() throws Exception {
		User user = new User();
		user.setEmail("[email protected]");
		UpdateWrapper<User> updateWrapper = new UpdateWrapper<User>();
		updateWrapper.ge("age", 20).like("user_name", "a").or().isNull("email");
		/**
		 * 根據 whereEntity 條件，更新記錄
		 *
		 * @param entity        實體對象 (set 條件值,可以為 null) 修改的參數
		 * @param updateWrapper 實體對象封裝操作類（可以為 null,裡面的 entity 用於生成 where 語句）條件參數
		 */
		int row = userMapper.update(user, updateWrapper);

		log.info("刪除數據={}條", row);
	}

/**
 * MybatisPlus的分頁插件配置類
 * @author wangruoxian
 *
 */
@Configuration
@MapperScan("com.wei.mapper")
public class MyBatisPlusPageConfig {
	@Bean
	public MybatisPlusInterceptor mybatisPlusInterceptor() {
		MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
		interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
		return interceptor;
	}
}

@GetMapping("/page")
	public IPage<User> pageList(){
		Page<User> page = new Page<User>(1,3);
//		QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
//		// 當前頁碼
//		page.setCurrent(1);
//		// 每頁顯示的條數
//		page.setSize(5);
		IPage<User> selectPage = userMapper.selectPage(page, null);
		log.info("當前頁數={}",selectPage.getCurrent());
		log.info("總頁數={}",selectPage.getPages());
		selectPage.getRecords().forEach(System.out::println);
		log.info("每頁大小={}",selectPage.getSize());
		log.info("總數據條數={}",selectPage.getTotal());
		return selectPage;
	}

# 配置掃描通用枚舉
type-enums-package: com.wei.enums

@Getter
public enum SexEnum {
	MALE(0,"男"),
	FEMALE(1,"女");
	
	@EnumValue
	private Integer sexCode;
	
	private String sexValue;
	
	private SexEnum(Integer sexCode, String sexValue) {
		this.sexCode = sexCode;
		this.sexValue = sexValue;
	}
}

CREATE DATABASE `mybatis_plus_slave` 
use `mybatis_plus_slave`;

CREATE TABLE t_product
(
id BIGINT(20) NOT NULL COMMENT '主鍵ID',
name VARCHAR(30) NULL DEFAULT NULL COMMENT '商品名稱',
price INT(11) DEFAULT 0 COMMENT '價格',
version INT(11) DEFAULT 0 COMMENT '樂觀鎖版本號',
PRIMARY KEY (id)
);

INSERT INTO product (id, NAME, price) VALUES (1, '外星人筆記本', 100);

<!-- 引入依賴 多數據源 -->
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
			<version>3.3.1</version>
		</dependency>

spring:
 # 配置數據源信息
 datasource:
  dynamic:
   # 設置默認的數據源或者數據源組,默認值即為master
   primary: master
   # 嚴格匹配數據源,默認false.true未匹配到指定數據源時拋異常,false使用默認數據源
   strict: false
   datasource:
    master:
     url: jdbc:mysql://localhost:3306/mybatis_plus?characterEncoding=utf-8&useSSL=false
     driver-class-name: com.mysql.cj.jdbc.Driver
     username: root
     password: 123456
    slave_1:
     url: jdbc:mysql://localhost:3306/mybatis_plus_1?characterEncoding=utf-8&useSSL=false
     driver-class-name: com.mysql.cj.jdbc.Driver
     username: root
     password: 123456

@RestController
@RequestMapping("/product")
@Api(value = "測試 ProductController 的接口", tags = "產品管理相關的接口", description = "product產品測試接口")
public class ProductController {
	
	@Autowired
	private IProductService IProductService;

	@GetMapping("/list")
//	@ApiOperation("查詢所有商品的接口")
	public List<Product> queryAllProduct(){
		return IProductService.list();
	}
}