前言

关注 B站 宝藏男孩 遇见狂神说

配置日志

日志可以跟踪信息，能够看到sql语句，便于查错

在mybatis-config.xml主配置文件，其中name&value是官网中的固定搭配 <!-- 配置日志log4j--> <settings> <setting name="logImpl" value="LOG4J"/> </settings> 在resources目录下创建log4j.properties文件

log4j.appender.file.File=日志输出位置 这里写进行Debug时，日志输出的位置

#将等级为DEBUG的日志信息输出到console和file这两个目的地，console和file的定义在下面的代码 log4j.rootLogger=DEBUG,console,file #控制台输出的相关设置 log4j.appender.console = org.apache.log4j.ConsoleAppender log4j.appender.console.Target = System.out log4j.appender.console.Threshold=DEBUG log4j.appender.console.layout = org.apache.log4j.PatternLayout log4j.appender.console.layout.ConversionPattern=[%c]-%m%n #文件输出的相关设置 log4j.appender.file = org.apache.log4j.RollingFileAppender log4j.appender.file.File=./log/mybatis.log log4j.appender.file.MaxFileSize=10mb log4j.appender.file.Threshold=DEBUG log4j.appender.file.layout=org.apache.log4j.PatternLayout log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n #日志输出级别 log4j.logger.org.mybatis=DEBUG log4j.logger.java.sql=DEBUG log4j.logger.java.sql.Statement=DEBUG log4j.logger.java.sql.ResultSet=DEBUG log4j.logger.java.sql.PreparedStatement=DEBUG

一对一（员工对应唯一的身份证号）

创建两个实体类

这里为了看的清晰些，我只展示属性

public class CardID { private int id; private String cardId; } public class Emp { private int id; private String name; private String address; private double salary; private char sex; private CardID cardID; }

在数据库中建立相应的表

t_emp表 CREATE TABLE `t_emp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) DEFAULT NULL, `address` varchar(50) DEFAULT NULL, `salary` double DEFAULT NULL, `sex` varchar(2) DEFAULT NULL, `dept_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_dept_emp` (`dept_id`), CONSTRAINT `FK_dept_emp` FOREIGN KEY (`dept_id`) REFERENCES `t_dept` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8 INSERT INTO `t_emp` VALUES ('2', '张婧仪', '湖南怀化', '2345.8', 'f', '1'); INSERT INTO `t_emp` VALUES ('3', 'xiaoming', '山西太原', '6000', 'm', '2'); INSERT INTO `t_emp` VALUES ('4', 'sanli', '宁夏吴中', '10500', 'f', '1'); INSERT INTO `t_emp` VALUES ('5', 'wuzhi', '湖南张家界', '7600', 'f', '2'); INSERT INTO `t_emp` VALUES ('6', 'mmc', '陕西商洛', '2000', 'f', '1'); INSERT INTO `t_emp` VALUES ('7', '_huan', '湖南张家界', '8500', 'f', '2'); INSERT INTO `t_emp` VALUES ('15', 'liangzai', '广东佛山', '8500', 'm', '1'); INSERT INTO `t_emp` VALUES ('16', 'Tom', '湖南怀化', '3400', 'f', '2'); INSERT INTO `t_emp` VALUES ('18', 'ma', '湖南怀化', '10000', 'f', '2'); INSERT INTO `t_emp` VALUES ('19', 'ts', '广东佛山', '7800', 'm', '1'); INSERT INTO `t_emp` VALUES ('20', 'cat', '陕西西安', '9000', 'f', '3'); INSERT INTO `t_emp` VALUES ('21', 'Craim', '美国硅谷', '10500', 'm', '3'); INSERT INTO `t_emp` VALUES ('25', '花花', '广东中山', '7000', 'm', '3'); INSERT INTO `t_emp` VALUES ('26', 'Yao', '陕西榆林', '7800', 'f', '3'); t_card_id表 CREATE TABLE `t_card_id` ( `id` int(10) NOT NULL, `card_id` varchar(20) DEFAULT NULL, `money` double DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 INSERT INTO `t_card_id` VALUES ('2', '612526199704283094', '9000'); INSERT INTO `t_card_id` VALUES ('3', '413412198710036746', '1000'); INSERT INTO `t_card_id` VALUES ('4', '421246200809087865', '3000');

1.在接口中定义方法

@Param注解：传参时参数名必须为id，否则报错，这样写也便于纠错

public interface EmpMapper { // 方法1：结果查询 一对一 ,一个员工对应唯一的身份证号 Emp selectEmpOne(@Param("id") int id); // 方法2：子查询 一对一， 一个员工对应唯一的身份证号 Emp selectEmpOne2(@Param("id") int id); }

2.在EmpMapper.xml中写sql语句

查询方式 （1）.结果查询

这里的id要唯一，并且要和接口方法名一致

association标签：在实体类属性名为对象时使用，表示关联

复杂的属性需要单独处理 对象：association 集合：collection javaType是给 property设置对象类型

注意

property属性要和实体类属性对应 column属性和数据库字段名对应 否则，会报错

<!-- 一对一结果查询--> <select id="selectEmpOne" resultMap="selectOne"> select e.id,c.card_id,e.name,e.address,e.salary,e.sex from t_emp e,t_card_id c <where> <if test="id!=null"> e.id=c.id and e.id = #{id} </if> </where> </select> <resultMap id="selectOne" type="com.qst.pojo.Emp"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="address" column="address"/> <result property="salary" column="salary"/> <result property="sex" column="sex"/> <association property="cardID" javaType="com.qst.pojo.CardID"> <id property="id" column="id"/> <result property="cardId" column="card_id"/> </association> </resultMap>

（2）子查询（嵌套查询）

<select id="selectEmpOne2" resultMap="selectOne2"> select id,name,address,salary,sex from t_emp where id=#{id} </select> <resultMap id="selectOne2" type="com.qst.pojo.Emp"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="address" column="address"/> <result property="salary" column="salary"/> <result property="sex" column="sex"/> <association property="cardID" column="id" javaType="com.qst.pojo.CardID" select="selectEmpCardId"/> </resultMap> <select id="selectEmpCardId" resultType="com.qst.pojo.CardID"> select id,card_id from t_card_id where id = #{cardId} </select> 测试 @Test public void selectOne(){ Emp emp = empMapper.selectEmpOne(3); System.out.println(emp); }

结果输出（可以清晰地看到sql语句，以及结果）

[com.qst.mapper.emp.EmpMapper.selectEmpOne]-==> Preparing: select e.id,c.card_id,e.name,e.address,e.salary,e.sex from t_emp e,t_card_id c WHERE e.id=c.id and e.id = ? [com.qst.mapper.emp.EmpMapper.selectEmpOne]-==> Parameters: 3(Integer) [com.qst.mapper.emp.EmpMapper.selectEmpOne]-<== Total: 1 [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@3cc1435c] [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@3cc1435c] [org.apache.ibatis.datasource.pooled.PooledDataSource]-Returned connection 1019298652 to pool. Emp{id=3, name='xiaoming', address='山西太原', salary=6000.0, sex=m, cardID=CardID{id=3, cardId='413412198710036746'}}

一对多（一个部门有多名员工）

创建实体类 public class Dept { private int id; private String deptName; private List<Emp> emps; } public class Emp2 { private int id; private String name; private String address; private double salary; private char sex; private int dept_id; } t_dept表 CREATE TABLE `t_dept` ( `id` int(11) NOT NULL AUTO_INCREMENT, `deptname` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 INSERT INTO `t_dept` VALUES ('1', '财务部'); INSERT INTO `t_dept` VALUES ('2', '人事部'); INSERT INTO `t_dept` VALUES ('3', '销售部');

1.Mapper接口

// 一对多 子查询 一个部门有多个员工 List<Dept> selectListDept(@Param("id") int id); // 一对多 结果查询 一个部门有多个员工 List<Dept> selectListDept2(@Param("id") int id);

2.EmpMapper.xml

查询方式 （1）结果查询

collection : 集合,属性为集合时使用 ofType：是集合中的泛型类型 javaType:是集合的类型

<!-- 一对多 结果查询 一个部门有多个员工--> <select id="selectListDept2" resultMap="selectDept2"> select e.id,e.name,e.address,e.salary,e.sex,d.deptname from t_emp e,t_dept d where e.dept_id=d.id and d.id=#{id} </select> <resultMap id="selectDept2" type="com.qst.pojo.search2.Dept"> <id property="id" column="id"/> <result property="deptName" column="deptname"/> <collection property="emps" ofType="com.qst.pojo.search.Emp2" javaType="ArrayList"> <id property="id" column="id"/> <result property="name" column="name"/> <result property="address" column="address"/> <result property="salary" column="salary"/> <result property="sex" column="sex"/> <result property="dept_id" column="dept_id"/> </collection> </resultMap>

（2）子查询（嵌套查询）

<!-- 一对多 子查询 一个部门有多个员工--> <select id="selectListDept" resultMap="selectDept"> select id,deptname from t_dept where id = #{id} </select> <resultMap id="selectDept" type="com.qst.pojo.search.Dept"> <!-- column是一对多的外键，写的是一的一方主键的列名--> <id property="id" column="id"/> <result property="deptName" column="deptname"/> <collection property="emps" ofType="com.qst.pojo.search.Emp2" javaType="ArrayList" column="id" select="selectEmp2"/> </resultMap> <select id="selectEmp2" resultType="com.qst.pojo.search.Emp2"> select id,name,address,salary,sex,dept_id from t_emp where dept_id = #{dept_id} </select> 测试 // 一对多 子查询 @Test public void selectList(){ List<Dept> emps = empMapper.selectListDept(1); for (Dept emp : emps) { System.out.println(emp); } }

结果

[com.qst.mapper.emp.EmpMapper.selectListDept]-==> Preparing: select id,deptname from t_dept where id = ? [com.qst.mapper.emp.EmpMapper.selectListDept]-==> Parameters: 1(Integer) [com.qst.mapper.emp.EmpMapper.selectEmp2]-====> Preparing: select id,name,address,salary,sex,dept_id from t_emp where dept_id = ? [com.qst.mapper.emp.EmpMapper.selectEmp2]-====> Parameters: 1(Integer) [com.qst.mapper.emp.EmpMapper.selectEmp2]-<==== Total: 5 [com.qst.mapper.emp.EmpMapper.selectListDept]-<== Total: 1 [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@3c0be339] [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@3c0be339] [org.apache.ibatis.datasource.pooled.PooledDataSource]-Returned connection 1007412025 to pool. Dept{id=1, deptName='财务部', emps=[Emp2{id=2, name='张婧仪', address='湖南怀化', salary=2345.8, sex=f, dept_id=0}, Emp2{id=4, name='sanli', address='宁夏吴中', salary=10500.0, sex=f, dept_id=0}, Emp2{id=6, name='mmc', address='陕西商洛', salary=2000.0, sex=f, dept_id=0}, Emp2{id=15, name='liangzai', address='广东佛山', salary=8500.0, sex=m, dept_id=0}, Emp2{id=19, name='ts', address='广东佛山', salary=7800.0, sex=m, dept_id=0}]}

多对一（多个员工对应一个部门）

实体类 public class Dept { private int id; private String deptName; } public class Emp2 { private int id; private String name; private String address; private double salary; private char sex; private Dept dept; }

数据库和上面的一致

1.Mapper接口实现

// 多对一 多个员工对应一个部门 子查询 List<Emp2> selectListEmp1(@Param("id") int id); // 多对一 多个员工对应一个部门 结果查询 List<Emp2> selectListEmp2(@Param("id") int id);

2.EmpMapper.xml实现

查询方式 （1）结果查询 <!-- 多对一，结果查询--> <select id="selectListEmp2" resultMap="empToDept2"> select e.id eid,e.name,e.sex,e.salary,e.address,d.id did,d.deptname from t_emp e join t_dept d on e.dept_id=d.id and d.id=#{id} </select> <resultMap id="empToDept2" type="com.qst.pojo.search2.Emp2"> <id property="id" column="eid"/> <result property="name" column="name"/> <result property="sex" column="sex"/> <result property="salary" column="salary"/> <result property="address" column="address"/> <association property="dept" column="dept_id" javaType="com.qst.pojo.search2.Dept"> <id property="id" column="did"/> <result property="deptName" column="deptname"/> </association> </resultMap>

（2）子查询（嵌套查询）

<!-- 多对一 多个员工对应一个部门--> <select id="selectListEmp1" resultMap="empToDept"> select id,name,address,salary,sex,dept_id from t_emp where dept_id = #{dept_id} </select> <resultMap id="empToDept" type="com.qst.pojo.search2.Emp2"> <association property="dept" column="dept_id" javaType="com.qst.pojo.search2.Dept" select="deptInfo"> <id property="id" column="id"/> <result property="deptName" column="deptname"/> </association> </resultMap> <select id="deptInfo" resultType="com.qst.pojo.search2.Dept"> select id,deptname from t_dept where id = #{id} </select> <!-- 多对一，结果查询--> <select id="selectListEmp2" resultMap="empToDept2"> select d.id did,d.deptname,e.id eid,e.name,e.address,e.salary,e.sex from t_dept d,t_emp e where d.id=e.dept_id and e.dept_id=#{dept_id} </select> <resultMap id="empToDept2" type="com.qst.pojo.search2.Emp2"> <id property="id" column="eid"/> <result property="name" column="name"/> <result property="address" column="address"/> <result property="sex" column="sex"/> <association property="dept" column="dept_id" javaType="com.qst.pojo.search2.Dept"/> </resultMap> 测试 / 多对一 多个员工对应一个部门 子查询 @Test public void selectEmp(){ List<Emp2> emps = empMapper.selectListEmp1(3); for (Emp2 emp : emps) { System.out.println(emp); } }

结果

[com.qst.mapper.emp.EmpMapper.selectListEmp1]-==> Preparing: select id,name,address,salary,sex,dept_id from t_emp where dept_id = ? [com.qst.mapper.emp.EmpMapper.selectListEmp1]-==> Parameters: 3(Integer) [com.qst.mapper.emp.EmpMapper.deptInfo]-====> Preparing: select id,deptname from t_dept where id = ? [com.qst.mapper.emp.EmpMapper.deptInfo]-====> Parameters: 3(Integer) [com.qst.mapper.emp.EmpMapper.deptInfo]-<==== Total: 1 [com.qst.mapper.emp.EmpMapper.selectListEmp1]-<== Total: 4 [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@3c0be339] [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@3c0be339] [org.apache.ibatis.datasource.pooled.PooledDataSource]-Returned connection 1007412025 to pool. Emp2{id=20, name='cat', address='陕西西安', salary=9000.0, sex=f, dept=Dept{id=3, deptName='销售部'}} Emp2{id=21, name='Craim', address='美国硅谷', salary=10500.0, sex=m, dept=Dept{id=3, deptName='销售部'}} Emp2{id=25, name='花花', address='广东中山', salary=7000.0, sex=m, dept=Dept{id=3, deptName='销售部'}} Emp2{id=26, name='Yao', address='陕西榆林', salary=7800.0, sex=f, dept=Dept{id=3, deptName='销售部'}}

多对多(老师&学生)

1.多对多转换为一对多查询思想 * 2.生成一个中间表，只需要在数据库中创建，不需要添加实体类

创建实体表 public class Teacher { private int id; private String name; private List<Student> studentList; } public class Student { private int id; private String name; private List<Teacher> teacherList; } 学生&老师的中间表

包括学生ID和老师ID

CREATE TABLE `t_teacher_student` ( `id` int(5) NOT NULL COMMENT '中间表的id', `tid` int(5) NOT NULL COMMENT '老师的id', `sid` int(5) NOT NULL COMMENT '学生的id', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

注意：COMMENT 这个建议在创建表时加上，便于查看

Mapper接口 // 多对多查询 ,通过老师查询学生信息 List<Teacher> selectTeacherToStudent(@Param("id") int id); StudentMapper.xml实现

三表联合查询

<!-- 多对多查询： 转换为一对多查询 --> <select id="selectTeacherToStudent" resultMap="teacherWithStudent"> select t.id tid,t.name tname,s.id sid,s.name sname from t_teacher t,t_teacher_student ts,t_student s where ts.tid = t.id and ts.sid = s.id and t.id = #{id} </select> <resultMap id="teacherWithStudent" type="com.qst.pojo.Teacher"> <id property="id" column="tid"/> <result property="name" column="tname"/> <collection property="studentList" ofType="com.qst.pojo.Student"> <id property="id" column="sid"/> <result property="name" column="sname"/> </collection> </resultMap> 测试 @Test public void selectTeacherAndStudent(){ List<Teacher> lists = studentMapper.selectTeacherToStudent(1); for (Teacher list : lists) { System.out.println(list); } }

结果

[com.qst.mapper.student.StudentMapper.selectTeacherToStudent]-==> Preparing: select t.id tid,t.name tname,s.id sid,s.name sname from t_teacher t,t_teacher_student ts,t_student s where ts.tid = t.id and ts.sid = s.id and t.id = ? [com.qst.mapper.student.StudentMapper.selectTeacherToStudent]-==> Parameters: 1(Integer) [com.qst.mapper.student.StudentMapper.selectTeacherToStudent]-<== Total: 2 [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1ebd319f] [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1ebd319f] [org.apache.ibatis.datasource.pooled.PooledDataSource]-Returned connection 515715487 to pool. Teacher{id=1, name='杨过', studentList=[Student{id=2, name='吉吉', teacherList=null}, Student{id=3, name='红红', teacherList=null}]}

共同成长，共同进步~