mybatis条件构造器(一)
1 准备工作
1.1 建表sql语句(Emp表)
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (`EMPNO` int NOT NULL AUTO_INCREMENT,`ENAME` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,`JOB` varchar(9) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL,`MGR` double NULL DEFAULT NULL,`HIREDATE` date NULL DEFAULT NULL,`SAL` double NULL DEFAULT NULL,`COMM` double NULL DEFAULT NULL,`DEPTNO` int NULL DEFAULT NULL,PRIMARY KEY (`EMPNO`) USING BTREE,INDEX `DEPTNO`(`DEPTNO`) USING BTREE,CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 8899 CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;
INSERT INTO `emp` VALUES (7369, '老张123', 'CLERK', 7902, '1980-12-30', 1200, 0, 20);
INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1700, 300, 30);
INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 3975, NULL, 20);
INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);
INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);
INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-07-13', 4000, NULL, 20);
INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);
INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-07-13', 2100, NULL, 20);
INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);
INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 4000, NULL, 20);
INSERT INTO `emp` VALUES (8896, '小明', 'MANAGER', 7698, '2023-01-02', 6666, 555, 10);
INSERT INTO `emp` VALUES (8897, '小红', 'SALESMAN', 7698, '2023-02-08', 5000, 1000, 10);
INSERT INTO `emp` VALUES (8898, 'zhang1', 'SALESMAN', 7698, '2023-01-31', 1243, 234, 10);
INSERT INTO `emp` VALUES (8899, '小小', 'SALESMAN', 7698, '2023-02-05', 123, 214, 10);
INSERT INTO `emp` VALUES (8900, '雄安', 'MANAGER', 7698, '2023-02-05', 13, 123, 10);SET FOREIGN_KEY_CHECKS = 1;
1.2 准备好测试环境
import com.zlz.ShiroStart2;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest(classes = ShiroStart2.class,webEnvironment = SpringBootTest.WebEnvironment.NONE)
public class DemoTest {}
2 多条件查询(全满足)
2.1 题目内容
查询员工姓名中包含a,薪水在1500-2000之间,津贴不为null的员工信息
2.2 对应的sql语句
SELECT EMPNO,ename,job,mgr,hiredate,sal,comm,deptno FROM emp WHERE (ename LIKE '%a%' AND sal BETWEEN 1500 AND 2000 AND comm IS NOT NULL)
2.3 示例代码
@Testpublic void a(){QueryWrapper<Emp> queryWrapper = new QueryWrapper<>();queryWrapper.like("ename","a").between("sal", 1500, 2000).isNotNull("comm");List<Emp> emps = empMapper.selectList(queryWrapper);emps.forEach(System.out::println);}
2.4 运行截图
2.4.1 实际执行的sql语句截图
2.4.2 数据显示截图
3 多排序字段查询
3.1 题目内容
#查询以部门编号升序,薪水降序的所有员工信息
3.2 对应的sql语句
SELECT EMPNO,ename,job,mgr,hiredate,sal,comm,deptno FROM emp ORDER BY deptno ASC,sal DESC
3.3 示例代码
@Testpublic void a2(){/查询以部门编号升序,薪水降序的所有员工信息QueryWrapper<Emp> queryWrapper = new QueryWrapper<>();queryWrapper.orderByAsc("deptno").orderByDesc("sal");List<Emp> emps = empMapper.selectList(queryWrapper);emps.forEach(System.out::println);}
3.4 运行截图
3.4.1 实际执行的sql语句截图
3.4.2 数据显示截图
4 按照条件删除
4.1 题目内容
#删除员工姓名为小小且日志日期为2023-02-05的员工
4.2 对应的sql语句
DELETE FROM emp WHERE (ename = '小小' AND hiredate = '2023-02-05')
4.3 示例代码
@Testpublic void a3(){QueryWrapper<Emp> queryWrapper = new QueryWrapper<>();queryWrapper.eq("ename", "小小").eq("hiredate", "2023-02-05");int result = empMapper.delete(queryWrapper);System.out.println("受影响行数: "+result);}
4.4 运行截图
4.4.1 实际执行的sql语句截图
4.4.2 数据显示截图
4.4.3 删除前数据库的数据
4.4.4 删除后数据库的数据
