项目目录：
image.png

一、使用Mapper代理方式实现查询

首先创建一个Mapper目录下的EmpMapper接口，在resource目录下创建EmpMapper.xml映射文件，并在SqlSessionFactory.xml进行Mapper文件扫描

  1.    <!--添加对应的mapper映射文件-->
  2.     <mappers>
  3.         <mapper class="com.xiaohui.mapper.EmpMapper"></mapper>
  4.   </mappers>

EmpMapper.java

  1. package com.xiaohui.mapper;
  3. import com.xiaohui.entity.Emp;
  5. import java.util.List;
  7. public interface EmpMapper {
  8.     /**
  9.     * @功能描述:查询全部的员工信息
  10.     * @Param
  11.     * @return 全部员工信息封装的Emp对象的list集合
  12.     */
  13.     List<Emp> findAll();
  14. }

EmpMapper.xml

  1. <mapper namespace="com.xiaohui.mapper.EmpMapper">
  2.   <!--
  3.     1 接口的名字和Mapper映射为文件名字必须保持一致(不包含拓展名)
  4.     2 Mapper映射文件的namespace必须是接口的全路径名
  5.     3 sql语句的id必须是对应方法的名
  6.     4 DeptMapper映射文件应该和接口编译之后放在同一个目录下
  7.     -->
  8.   <!--List<Emp> findAll();-->
  9.   <select id="findAll" resultType="emp" >
  10.     select * from emp
  11.   </select>
  12. </mapper>

测试代码：

  1. //查询全部员工信息
  2. @Test
  3. public void findAllTest(){
  4.     EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
  5.     List<Emp> empMapperAll = empMapper.findAll();
  6.     empMapperAll.forEach(System.out::println);
  7. }

结果：

  1. DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@247310d0]
  2. DEBUG - ==>  Preparing: select * from emp 
  3. DEBUG - ==> Parameters: 
  4. DEBUG - <==      Total: 14
  5. Emp(empno=7369, ename=SMITH, job=CLERK, mgr=7902, hiredate=Wed Dec 17 00:00:00 CST 1980, sal=800.0, comm=null, deptno=20)
  6. Emp(empno=7499, ename=ALLEN, job=SALESMAN, mgr=7698, hiredate=Fri Feb 20 00:00:00 CST 1981, sal=1600.0, comm=300.0, deptno=30)
  7. Emp(empno=7521, ename=WARD, job=SALESMAN, mgr=7698, hiredate=Sun Feb 22 00:00:00 CST 1981, sal=1250.0, comm=500.0, deptno=30)
  8. Emp(empno=7566, ename=JONES, job=MANAGER, mgr=7839, hiredate=Thu Apr 02 00:00:00 CST 1981, sal=2975.0, comm=null, deptno=20)
  9. Emp(empno=7654, ename=MARTIN, job=SALESMAN, mgr=7698, hiredate=Mon Sep 28 00:00:00 CST 1981, sal=1250.0, comm=1400.0, deptno=30)
  10. DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@247310d0]
  11. DEBUG - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@247310d0]
  12. DEBUG - Returned connection 611520720 to pool.

二、接口代理下的参数问题

单个基本数据类型传入Mapper映射文件进行单条数据查询，只需要传入一个数据类型的数据进行查询就行了
当多个基本数据类型参数进行查询时，有以下几种方式进行参数传递
方式1 arg* arg0 arg1 arg2 数字是索引,从0开始

  1. <select id="findByDeptnoAndSal" resultType="emp">
  2.   select * from emp Where deptno = #{arg0} and sal &gt;= #{arg1}
  3. </select>

测试代码：

  1. @Test
  2. public void testFindByEmpnoAndSal(){
  3.     //获取接口
  4.     EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
  5.     List<Emp> byEmpnoAndSal = empMapper.findByDeptnoAndSal(20, 3000.0);
  6.     byEmpnoAndSal.forEach(System.out::println);
  7. }

结果：

  1. DEBUG - ==>  Preparing: select * from emp Where deptno = ? and sal >= ? 
  2. DEBUG - ==> Parameters: 20(Integer), 3000.0(Double)
  3. DEBUG - <==      Total: 2
  4. Emp(empno=7788, ename=SCOTT, job=ANALYST, mgr=7566, hiredate=Sun Apr 19 00:00:00 CDT 1987, sal=3000.0, comm=null, deptno=20)
  5. Emp(empno=7902, ename=FORD, job=ANALYST, mgr=7566, hiredate=Thu Dec 03 00:00:00 CST 1981, sal=3000.0, comm=null, deptno=20)
  6. DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@6be968ce]

方式2 param* param1 param2 param3 数字是编号,从1开始

  1. <select id="findByDeptnoAndSal" resultType="emp">
  2.   select * from emp Where deptno = #{param1} and sal &gt;= #{param2}
  3.     </select>

结果：

  1. DEBUG - ==>  Preparing: select * from emp Where deptno = ? and sal >= ? 
  2. DEBUG - ==> Parameters: 20(Integer), 3000.0(Double)
  3. DEBUG - <==      Total: 2
  4. Emp(empno=7788, ename=SCOTT, job=ANALYST, mgr=7566, hiredate=Sun Apr 19 00:00:00 CDT 1987, sal=3000.0, comm=null, deptno=20)
  5. Emp(empno=7902, ename=FORD, job=ANALYST, mgr=7566, hiredate=Thu Dec 03 00:00:00 CST 1981, sal=3000.0, comm=null, deptno=20)
  6. DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@609e8838]

方式3 使用@Param注解（别名）来作为方法参数传递


通过@Param注解使用别名之后,就不能再使用arg 但是可以继续使用param（方式2）

  1. List<Emp> findByDeptnoAndSal(@Param("deptno") int deptno,@Param("sal") double sal);
  1. <select id="findByDeptnoAndSal" resultType="emp">
  2.   select * from emp Where deptno = #{deptno} and sal &gt;= #{sal}
  3.     </select>

测试结果和以上是一样

使用Map集合作为方法参数传递

使用Map集合的方式进行参数的传递时，在mapper映射文件中指定接受数据类型是map，并使用对应的键作为参数占位进行sql操作

  1.     List<Emp> findByDeptnoAndSalMap(Map<String,Object> map);
  1.     <select id="findByDeptnoAndSalMap" resultType="emp" parameterType="map">
  2.         select * from emp Where deptno = #{deptno} and sal &gt;= #{sal}
  3.     </select>

测试代码：

  1.     //Map形式进行查询某个员工信息
  2.     @Test
  3.     public void testFindByEmpnoAndSalMap(){
  4.         //获取接口
  5.         EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
  6.         Map<String,Object> map = new HashMap<>();
  7.         map.put("deptno",20);
  8.         map.put("sal",3000.0);
  9.         List<Emp> byEmpnoAndSal = empMapper.findByDeptnoAndSalMap(map);
  10.         byEmpnoAndSal.forEach(System.out::println);
  11.     }

结果同上。

使用引用类型作为方法参数传递

使用对象其中的属性作为参数传递时，在Mapper映射文件中指定接受类型是emp对象，并使用emp对象中的属性名作为参数占位

单个引用类型作为方法参数传递

  1.    List<Emp> findByDeptnoAndSalEmp(Emp emp);
  1.     <select id="findByDeptnoAndSalEmp" resultType="emp" parameterType="emp">
  2.         select * from emp Where deptno = #{deptno} and sal &gt;= #{sal}
  3.     </select>

测试代码：

  1.     //对象形式进行查询某个员工信息
  2.     @Test
  3.     public void testFindByEmpnoAndSalEmp(){
  4.         //获取接口
  5.         EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
  6.         Emp emp = new Emp();
  7.         emp.setDeptno(20);
  8.         emp.setSal(3000.0);
  9.         List<Emp> byEmpnoAndSal = empMapper.findByDeptnoAndSalEmp(emp);
  10.         byEmpnoAndSal.forEach(System.out::println);
  11.     }

结果同上。

多个引用类型作为方法参数传递

当使用多个引用类型时，Mapper映射文件中可以使用arg.xxx， param.xxx 、@Param注解（别名）.xxx来进行参数占位

  1.    //List<Emp> findByDeptnoAndSalEmp(Emp emp1,Emp emp2);
  2.    List<Emp> findByDeptnoAndSalEmpList(@Param("emp1") Emp emp1,@Param("emp2") Emp emp2);
  1.     <select id="findByDeptnoAndSalEmpList" resultType="emp">
  2.         <!--select * from emp Where deptno = #{arg0.deptno} and sal &gt;= #{arg1.sal}-->
  3.         <!--select * from emp Where deptno = #{param1.deptno} and sal &gt;= #{param2.sal}-->
  4.         select * from emp Where deptno = #{emp1.deptno} and sal &gt;= #{emp2.sal}
  5.     </select>

测试代码：

  1.     @Test
  2.     public void testFindByEmpList(){
  3.         //获取接口
  4.         EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
  5.         Emp emp1 = new Emp();
  6.         emp1.setDeptno(20);
  7.         Emp emp2 = new Emp();
  8.         emp2.setSal(3000.0);
  9.         List<Emp> byEmpnoAndSal = empMapper.findByDeptnoAndSalEmpList(emp1,emp2);
  10.         byEmpnoAndSal.forEach(System.out::println);
  11.     }

结果同上。

模糊查询方式

  1.    List<Emp> findByEname( String name);
  1.     <select id="findByEname" resultType="emp">
  2.         select * from emp where ename like concat('%',#{ename},'%')
  3.     </select>

测试代码：

  1.     @Test
  2.     public void testFindByEname(){
  3.         //获取接口
  4.         EmpMapper empMapper = sqlSession.getMapper(EmpMapper.class);
  5.         List<Emp> byEname = empMapper.findByEname("a");
  6.         byEname.forEach(System.out::println);
  7.     }

结果：

  1. DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7ae0a9ec]
  2. DEBUG - ==>  Preparing: select * from emp where ename like ? 
  3. DEBUG - ==> Parameters: %a%(String)
  4. DEBUG - <==      Total: 7
  5. DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7ae0a9ec]

主键自增回填

当使用增加数据时，如果主键没有设置自增并且作为其他表的外链接进行数据操作时会有错误发生，解决办法是在inser标签中增加以 下面两个就可以解决
useGeneratedKeys=”true” 返回数据库帮我们生成的主键
keyProperty=”deptno” 生成的主键值用我们dept对象那个属性存储

  1. <mapper namespace="com.xiaohui.mapper.DeptMapper">
  2.     <insert id="addDept" parameterType="dept" useGeneratedKeys="true" keyProperty="deptno">
  3.         insert into dept values (DEFAULT ,#{dname},#{loc} )
  4.     </insert>
  5. </mapper>

测试代码：

  1.     @Test
  2.     public void testAddDept(){
  3.         DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
  4.         Dept dept = new Dept(null, "JAva", "BOSTON");
  5.         System.out.println(dept.getDeptno());
  6.         mapper.addDept(dept);
  7.         sqlSession.commit();
  8.         System.out.println(dept.getDeptno());
  9.     }

结果

  1. DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@514646ef]
  2. DEBUG - ==>  Preparing: insert into dept values (DEFAULT ,?,? ) 
  3. DEBUG - ==> Parameters: JAva(String), BOSTON(String)
  4. DEBUG - <==    Updates: 1
  5. DEBUG - Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@514646ef]
  6. null
  7. 42
  8. DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@514646ef]

第二种方式进行主键自增回填

  1. <mapper namespace="com.xiaohui.mapper.DeptMapper">
  2.     <insert id="addDept2" parameterType="dept">
  3.         <selectKey order="AFTER" keyProperty="deptno"  resultType="int">
  4.             select @@identity
  5.         </selectKey>
  6.         insert into dept values(null,#{dname},#{loc})
  7.     </insert>
  8. </mapper>

结果

  1. DEBUG - Checking to see if class com.xiaohui.mapper.EmpMapper matches criteria [is assignable to Object]
  2. null
  3. DEBUG - Opening JDBC Connection
  4. DEBUG - Created connection 611520720.
  5. DEBUG - Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@247310d0]
  6. DEBUG - ==>  Preparing: insert into dept values(null,?,?) 
  7. DEBUG - ==> Parameters: JAva(String), BOSTON(String)
  8. DEBUG - <==    Updates: 1
  9. DEBUG - ==>  Preparing: select @@identity 
  10. DEBUG - ==> Parameters: 
  11. DEBUG - <==      Total: 1
  12. DEBUG - Committing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@247310d0]
  13. 43
  14. DEBUG - Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@247310d0]
  15. DEBUG - Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@247310d0]
  16. DEBUG - Returned connection 611520720 to pool.
  18. Process finished with exit code 0

三、接口代理下实现CRUD操作

  1. public interface EmpMapper {
  3.     /**
  4.      * 增加员工信息
  5.      */
  6.     int addEmp(Emp emp);
  7.     /**
  8.      * 根据员工编号修改员工姓名的方法
  9.      */
  10.     int updateEnameByEmpno(@Param("empno") int empno,@Param("ename") String ename);
  11.     /**
  12.      * 根据员工编号删除员工信息
  13.      */
  14.     int deleteByEmpno(int empno);
  16. }
  1.     <!--int addEmp(Emp emp);-->
  2.     <insert id="addEmp" parameterType="emp">
  3.         insert into emp values(DEFAULT ,#{ename},#{job},#{mgr},#{hiredate},#{sal},#{comm},#{deptno})
  4.     </insert>
  5.     <!--int updateEnameByEmpno(@Param("empno") int empno,@Param("ename") String ename);-->
  6.     <update id="updateEnameByEmpno">
  7.         update emp set ename =#{ename} where empno =#{empno}
  8.     </update>
  9.     <!--int deleteByEmpno(int empno);-->
  10.     <delete id="deleteByEmpno">
  11.         delete from emp where empno =#{empno}
  12.     </delete>

测试代码

  1.     @Test
  2.     public void testAddEmp(){
  3.         EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
  4.         mapper.addEmp(new Emp(null, "TOM", "SALESMAN", 7521, new Date(), 2314.0, 100.0, 10));
  5.         sqlSession.commit();
  6.     }
  7.     @Test
  8.     public void testUpdateEnameByEmpno(){
  9.         EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
  10.         mapper.updateEnameByEmpno(7935, "TOM");
  11.         sqlSession.commit();
  12.     }
  13.     @Test
  14.     public void testDeletByEmpno(){
  15.         EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
  16.         mapper.deleteByEmpno(7935);
  17.         sqlSession.commit();
  18.     }

结果就是对该数据进行增删改的操作