demo

table

CREATE TABLE `employee` (
	`id` VARCHAR(50) NOT NULL,
	`department_name` VARCHAR(50) NULL DEFAULT NULL COMMENT '部门名称',
	`name` VARCHAR(255) NULL DEFAULT NULL COMMENT '名称',
	`age` INT(11) UNSIGNED NULL DEFAULT NULL COMMENT '年龄',
	`height` SMALLINT(5) UNSIGNED NULL DEFAULT NULL COMMENT '身高',
	`nickname` VARCHAR(100) NULL DEFAULT NULL COMMENT '用户信息',
	`gender` TINYINT(3) UNSIGNED NULL DEFAULT NULL COMMENT '性别  1 男  2 女 0 未知',
	`is_active` BIT(1) NULL DEFAULT NULL COMMENT '是否有效  1 是 0 否',
	`lock_version` INT(10) UNSIGNED NULL DEFAULT '1',
	`birthday` DATE NULL DEFAULT NULL COMMENT '生日',
	`since_year` YEAR NULL DEFAULT NULL COMMENT '入职年份',
	`gmt_create` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	`gmt_modified` DATETIME NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
	`blobf` BLOB NULL,
	PRIMARY KEY (`id`)
)
COMMENT='员工表'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

示例

selectByPrimaryKey

根据主键查找返回Model

@Test
public void testSelectByPrimaryKey() {   
    Employee employee = employeeService.selectByPrimaryKey("employeeId");
    Assert.notNull(employee, "employee not exists");
}

SELECT * 
FROM employee 
WHERE id = 'employeeId' 

selectByPrimaryKeyWithColumns

根据主键查找返回包含指定字段数据的Model

@Test
public void testSelectByPrimaryKeyWithColumns() {
    Employee employee = employeeService.selectByPrimaryKeyWithColumns(
            "employeeId",
            true,
            EmployeeExample.C.AGE,
            EmployeeExample.C.DEPARTMENT_NAME,
            EmployeeExample.C.SINCE_YEAR,
            EmployeeExample.C.IS_ACTIVE);
    Assert.notNull(employee, "employee not exists");
}

SELECT age,department_name,since_year,is_active
FROM employee
WHERE id = 'employeeId'

selectByExample

根据条件查找返回List

@Test
public void testSelectByExampleBase() {
    EmployeeExample employeeExample = new EmployeeExample();
    employeeExample
            .or(cri -> cri
                    // 年龄> 30
                    .andAgeGreaterThan(30)
                    // 姓名 王姓人员
                    .andNameLike("{}%", "王"));
    List<Employee> list = employeeService.selectByExample(employeeExample);
    Assert.isTrue(list.size() > 0, "no data");
}

SELECT *
FROM employee
WHERE
(
age > 30 AND NAME LIKE '王%'
)

selectByExample复杂查询

根据条件查找返回List

@Test
public void testSelectByExample() {
    EmployeeExample employeeExample = new EmployeeExample();
    employeeExample
            .includeColumns(    // 包含以下列
                    EmployeeExample.C.NAME,
                    EmployeeExample.C.AGE,
                    EmployeeExample.C.BIRTHDAY)
            .ignoreNull()   // WHERE 条件拼接忽略空值
            .or(cri -> cri
                    .andAgeGreaterThan(30)  // 年龄> 30
                    .andNameLike("{}%", "王")
                    .andBirthdayEqualTo(null))// 忽略空值
            .or(cri -> cri

                    .ignoreNull(false)  // 内部条件忽略空值
                    .andAgeGreaterThanOrEqualTo(2) // 年龄>= 2
                    .addCriterions(ac ->
                            // 部门字段长度 > 2
                            ac.addCriterion("length(department_name) > ", 2)))
            .and(cri -> cri
                    .andIdIsNotNull() // id 不为空
                    .andActiveEqualTo(false))
            .orderBy(EmployeeExample.C.NAME);

    List<Employee> list = employeeService.selectByExample(employeeExample);
    Assert.isTrue(list.size() > 0, "no data");
}

SELECT name,age,birthday
FROM employee
WHERE
	(age > 30 AND NAME LIKE '王%')
	OR (age >= 2 AND LENGTH(department_name) > 2)
	AND (id IS NOT NULL OR is_active = 0)
ORDER BY name