MyBatis 实现部门和员工表查询 (动态 SQL)

首先，需要搭建 MyBatis 框架环境，包括配置 MyBatis 的 XML 文件和相关的 Java 类。\n\n1. 创建数据库表\n创建员工表和部门表，表结构如下：\n\nCREATE TABLE emp (\n empno INT PRIMARY KEY,\n ename VARCHAR(50),\n hiredate DATE,\n sal DECIMAL(10, 2),\n deptno INT\n);\n\nCREATE TABLE dept (\n deptno INT PRIMARY KEY,\n dname VARCHAR(50),\n loc VARCHAR(50)\n);\n\n\n2. 创建实体类\n创建 Emp 和 Dept 两个实体类，分别对应员工表和部门表。\n\nEmp.java：\njava\npublic class Emp {\n private Integer empno;\n private String ename;\n private Date hiredate;\n private BigDecimal sal;\n private Integer deptno;\n \n // 省略 getter 和 setter 方法\n}\n\n\n\nDept.java：\njava\npublic class Dept {\n private Integer deptno;\n private String dname;\n private String loc;\n \n // 省略 getter 和 setter 方法\n}\n\n\n3. 创建 Mapper 接口\n创建 EmpMapper 和 DeptMapper 两个 Mapper 接口，用于定义查询方法。\n\nEmpMapper.java：\njava\npublic interface EmpMapper {\n List<Emp> findByEmpnoOrEname(@Param("empno") Integer empno, @Param("ename") String ename);\n}\n\n\n\nDeptMapper.java：\njava\npublic interface DeptMapper {\n List<Dept> findByDeptnoOrDname(@Param("deptno") Integer deptno, @Param("dname") String dname);\n}\n\n\n4. 创建 Mapper XML 文件\n创建 EmpMapper.xml 和 DeptMapper.xml 两个 Mapper XML 文件，配置查询语句。\n\nEmpMapper.xml：\nxml\n<mapper namespace="com.example.mapper.EmpMapper">\n <select id="findByEmpnoOrEname" resultType="com.example.entity.Emp">\n SELECT * FROM emp\n <where>\n <if test="empno != null">\n empno = #{empno}\n </if>\n <if test="ename != null and ename != ''">\n ename like CONCAT('%', #{ename}, '%')\n </if>\n </where>\n </select>\n</mapper>\n\n\nDeptMapper.xml：\nxml\n<mapper namespace="com.example.mapper.DeptMapper">\n <select id="findByDeptnoOrDname" resultType="com.example.entity.Dept">\n SELECT * FROM dept\n <where>\n <if test="deptno != null">\n deptno = #{deptno}\n </if>\n <if test="dname != null and dname != ''">\n dname like CONCAT('%', #{dname}, '%')\n </if>\n </where>\n </select>\n</mapper>\n\n\n5. 配置 MyBatis\n在 MyBatis 的配置文件（如 mybatis-config.xml）中添加 Mapper XML 文件的引用。\n\nxml\n<configuration>\n \n \n <mappers>\n <mapper resource="com/example/mapper/EmpMapper.xml"/>\n <mapper resource="com/example/mapper/DeptMapper.xml"/>\n </mappers>\n</configuration>\n\n\n6. 使用 MyBatis 进行查询\n在 Java 代码中使用 MyBatis 进行查询操作。\n\njava\npublic class Main {\n public static void main(String[] args) {\n // 创建 SqlSessionFactory\n SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));\n \n // 创建 SqlSession\n try (SqlSession sqlSession = sqlSessionFactory.openSession()) {\n // 获取 Mapper 接口实例\n EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);\n DeptMapper deptMapper = sqlSession.getMapper(DeptMapper.class);\n \n // 查询员工信息\n List<Emp> empList = empMapper.findByEmpnoOrEname(1001, "John");\n for (Emp emp : empList) {\n System.out.println(emp.getEmpno() + " " + emp.getEname() + " " + emp.getHiredate() + " " + emp.getSal());\n \n // 查询关联的部门信息\n Dept dept = deptMapper.findByDeptnoOrDname(emp.getDeptno(), null).get(0);\n System.out.println(dept.getDeptno() + " " + dept.getDname() + " " + dept.getLoc());\n }\n \n // 查询部门信息\n List<Dept> deptList = deptMapper.findByDeptnoOrDname(10, "Sales");\n for (Dept dept : deptList) {\n System.out.println(dept.getDeptno() + " " + dept.getDname() + " " + dept.getLoc());\n \n // 查询关联的员工信息\n List<Emp> empList = empMapper.findByEmpnoOrEname(null, null);\n for (Emp emp : empList) {\n if (emp.getDeptno().equals(dept.getDeptno())) {\n System.out.println(emp.getEmpno() + " " + emp.getEname() + " " + emp.getHiredate() + " " + emp.getSal());\n }\n }\n }\n }\n }\n}\n\n\n以上就是使用 MyBatis 实现部门和员工表查询的示例代码。通过动态 SQL 的方式，可以根据条件查询员工信息和部门信息，并查询关联的数据。