注:本文是对本人的Mybatis入门(上)的补充,或者说进阶,是在程序跑起来的基础上进行。
关于Mybatis的入门教学
1.Mybatis的CRUD操作
Mapper文件的编写
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.it.dao.UserDao">
<!-- 查询单个对象 -->
<select id="findById" resultType="com.it.entity.User">
select * from t_user where id = #{id}
</select>
<insert id="add">
insert into t_user(name,password) values( #{name},#{password} )
</insert>
<update id="update">
update t_user set name = #{name} where id = #{id}
</update>
<delete id="delete">
delete from t_user where id = #{id}
</delete>
测试插入
注:当对数据库进行持久化操作时要进行提交,也就是commit.
//添加
@Test
public void testadd()throws Exception {
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(in);
SqlSession session = factory.openSession();
//---------------------------------------以上部分在以后会由spring完成
UserDao userDao = session.getMapper(UserDao.class);
User user = new User();
user.setName("张三");
user.setPassword("123");
userDao.add(user);
System.out.println(user.getId());
//---------------------------------------以下部分在以后会由spring完成
//提交
session.commit();
session.close();
in.close();
}
补充
在数据插入后,主键因为是自增,所以不需要手动添加,那么在插入数据后如果想使用该数据的主键应该怎么获得?可以使用selectkey来获取
<insert id="add">
<selectKey keyProperty="id" keyColumn="id" resultType="int" order="AFTER">
select last_insert_id();
</selectKey>
insert into t_user(name,password) values( #{name},#{password} )
</insert>
当插入数据后再输出该对象就可以看到对象里的主键id已被赋值。
2.ORM映射失效
如果实例化对象的属性名与数据库字段名不符,会出现ORM映射失效的现象,即对数据库进行操作时对应数据属性为NULL或默认值,对应的解决方法有两个。
方案一:列的别名
<mapper namespace="com.it.mybatis.part2.orm.ManagerDao">
<select id="selectManagerByIdAndPwd" resultType="com.it.mybatis.part2.orm.Manager">
SELECT mgr_id AS id , mgr_name AS username , mgr_pwd AS password
FROM t_managers
WHERE mgr_id = #{id} AND mgr_pwd = #{pwd}
</select>
</mapper>
方案二:结果映射(ResultMap - 查询结果的封装规则)
通过< resultMap id="" type="" >映射,匹配列名与属性名
<mapper namespace="com.it.mybatis.part2.orm.ManagerDao">
<!--定义resultMap标签-->
//type="实例化对象bean的地址"
<resultMap id="managerResultMap" type="com.it.mybatis.part2.orm.Manager">
<!--关联主键与列名-->
<id property="id" column="mgr_id" />
<!--关联属性与列名-->
<result property="username" column="mgr_name" />
<result property="password" column="mgr_pwd" />
</resultMap>
<!--使用resultMap作为ORM映射依据-->
<select id="selectAllManagers" resultMap="managerResultMap">
SELECT mgr_id , mgr_name , mgr_pwd
FROM t_managers
</select>
</mapper>
3.MyBatis处理关联关系-多表连接
实体间的关系:关联关系。
- OneToOne:一对一关系;
- OneToMany:一对多关系;
- ManyToMany:多对多关系;
3.1一对一关系
创建数据库
//旅客表
CREATE TABLE t_passenger(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50),
sex VARCHAR(1),
birthday DATE
)DEFAULT CHARSET =utf8;
INSERT INTO t_passenger VALUES(NULL,'zhansan','f','2020-11-11');
INSERT INTO t_passenger VALUES(NULL,'lucy','m','2020-12-12');
//护照表
CREATE TABLE t_passport(
id INT PRIMARY KEY AUTO_INCREMENT,
nationality VARCHAR(50),
expire DATE,
passenger_id INT UNIQUE
)DEFAULT CHARSET =utf8;
INSERT INTO t_passport VALUES(NULL,'China','2030-11-11',1);
INSERT INTO t_passport VALUES(NULL,'America','2030-12-12',2);
创建实体类
package com.it.pojo;
import lombok.Data;
import java.util.Date;
//旅客表
@Data
public class Passenger {
private Integer id;
private String name;
private String sex;
private Date birthday;
private Passport passport;//存储对应的护照信息,关系属性
}
---------------------------------------------------------
package com.it.pojo;
import lombok.Data;
import java.util.Date;
//护照表
@Data
public class Passport {
private Integer id;
private String nationality;
private Date expire;
}
创建Dao接口
package com.it.dao;
import com.it.pojo.Passenger;
public interface PassengerDao {
//通过旅客id查询旅客信息以及护照信息(关联查询)
public Passenger findById(Integer id);
}
创建PassengerDao.xml配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.it.dao.PassengerDao">
<resultMap id="passengerMap" type="Passenger">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="sex" property="sex"></result>
<result column="birthday" property="birthday"></result>
<!-- 描述 passportId nationality expire 和 passport 映射规则 -->
<association property="passport" javaType="Passport">
<id column="passportId" property="id"></id>
<result column="nationality" property="nationality"></result>
<result column="expire" property="expire"/>
</association>
</resultMap>
<!-- 查询旅客及其护照 -->
<select id="findById" resultMap="passengerMap">
SELECT p1.id,p1.name,p1.sex,p1.birthday,
p2.id passportId,p2.nationality,p2.expire
FROM t_passenger p1 INNER JOIN t_passport p2
ON p1.id = p2.passenger_id
WHERE p1.id=#{id};
</select>
</mapper>
注:指定“一方”关系时(对象),使用< association javaType="" >
测试
package com.it.demo;
import com.it.dao.PassengerDao;
import com.it.pojo.Passenger;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.InputStream;
public class MybatisTest {
@Test
public void testFindById()throws Exception{
//准备环境
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
//-------------------------------------------------------------
PassengerDao passengerDao = sqlSession.getMapper(PassengerDao.class);
Passenger passenger = passengerDao.findById(1);
System.out.println(passenger);
//-------------------------------------------------------------
sqlSession.close();
inputStream.close();
}
}
3.2一对多关系
一对多关系:在为一的一方为添加一个集合,在多的一方添加一个对象属性。
注:因为查询关系为一的一方时与一对一相同,所以不在写为一的一方。
首先创建表
CREATE TABLE t_department(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50),
location VARCHAR(100)
)DEFAULT CHARSET =utf8;
INSERT INTO t_department VALUES(1,"教学部","北京"),(2,"研发部","上海");
CREATE TABLE t_employee(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(50),
salary DOUBLE,
dept_id INT
)DEFAULT CHARSET =utf8;
INSERT INTO t_employee VALUES(1,"jack",1000.5,1);
INSERT INTO t_employee VALUES(2,"rose",2000.5,1);
INSERT INTO t_employee VALUES(3,"张三",3000.5,2);
INSERT INTO t_employee VALUES(4,"李四",4000.5,2);
编写实体类
package com.it.pojo;
import lombok.Data;
@Data
public class Employee {
private Integer id;
private String name;
private Double salary;
// 员工从属的部门信息
private Department department;
}
-----------------------------------------
package com.it.pojo;
import lombok.Data;
import java.util.List;
@Data
public class Department {
private Integer id;
private String name;
private String location;
// 存储部门中所有员工信息
private List<Employee> employees;
}
创建dao接口
package com.it.dao;
import com.it.pojo.Department;
import org.apache.ibatis.annotations.Param;
public interface DepartmentDao {
// 查询部门,及其所有员工信息
Department findDepartmentById(@Param("id") Integer id);
}
5.创建DepartmentDao.xml配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.it.dao.DepartmentDao">
<resultMap id="deptMap" type="Department">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="location" property="location"></result>
//指定“多方”关系时(集合),使用< collection ofType="" >
<collection property="employees" ofType="Employee">
<id column="emp_id" property="id"></id>
<result column="emp_name" property="name"></result>
<result column="salary" property="salary"></result>
</collection>
</resultMap>
<select id="findDepartmentById" resultMap="deptMap">
select d.id ,d.name,d.location,
e.id emp_id,e.name emp_name,e.salary
from t_department d join t_employee e
on d.id = e.dept_id
where d.id=#{id}
</select>
</mapper>
注:指定“多方”关系时(集合),使用< collection ofType="" >
测试
package com.it.demo;
import com.it.dao.DepartmentDao;
import com.it.dao.EmployeeDao;
import com.it.pojo.Department;
import com.it.pojo.Employee;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.InputStream;
public class MybatisTest {
@Test
public void findDeptById()throws Exception{
//准备环境
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
//-------------------------------------------------------------
DepartmentDao departmentDao = sqlSession.getMapper(DepartmentDao.class);
Department department = departmentDao.findDepartmentById(1);
System.out.println(department);
//-------------------------------------------------------------
sqlSession.close();
inputStream.close();
}
}
3.3 多对多关系
多对多关系可以看为两个一对多的关系,所以两边都使用< collection ofType="" >即可。
总结
1.ORM失效
方案一:使用别名;
方案二:使用resultMap;
2.关系处理
一方,添加集合;多方,添加对象。
双方均可建立关系属性,建立关系属性后,对应的Mapper文件中需使用< ResultMap >完成多表映射。
持有对象关系属性,使用< association property=“dept” javaType=“department” >
持有集合关系属性,使用< collection property=“emps” ofType=“employee” >
补充:Mybatis的分页查询
1.概念:
PageHelper是适用于MyBatis框架的一个分页插件,使用方式极为便捷,支持任何复杂的单表、多表分页查询操作。
2.下载:
官方网站:https://pagehelper.github.io/
下载地址:https://github.com/pagehelper/Mybatis-PageHelper
3.开发:
导入依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.11</version>
</dependency>
在MyBatis-config.xml中添加< plugins >
<configuration>
<typeAliases></typeAliases>
<plugins>
<!-- com.github.pagehelper为PageHelper类所在包名 -->
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<property name="reasonable" value="true"/>
<property name="supportMethodsArguments" value="true"/>
</plugin>
</plugins>
<environments>...</environments>
</configuration>
在测试类中添加方法进行测试
//查询分页
@Test
public void testfindByPage()throws Exception {
//1.读取配置文件
InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
//2.创建SqlSessionFactory工厂
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(in);
//3.使用工厂生产SqlSession对象
SqlSession session = factory.openSession();
//4.使用SqlSession创建Dao接口的代理对象
UserDao userDao = session.getMapper(UserDao.class);
//5.使用代理对象执行方法
PageHelper.startPage(1,2);//设置当前页和每页显示记录数
List<User> users = userDao.findAll();
PageInfo<User> userPageInfo = new PageInfo<>(users);//封装到PageInfo对象中
System.out.println(userPageInfo);
//6.释放资源
session.close();
in.close();
}
注:
- 只有在PageHelper.startPage()方法之后的第一个查询会有执行分页。
- 分页插件不支持带有“for update”的查询语句。
- 分页插件不支持“嵌套查询”,由于嵌套结果方式会导致结果集被折叠,所以无法保证分页结果数量正确。。