前言
关注 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}]}
共同成长,共同进步~