一、搭建 Mybatis 环境
- maven 添加依赖
- db.properties
- mybatis-config.xml
- 实体类
- 实体类映射器 BookMapper
- 映射器配置文件 BookMapper.xml
- junit4 单元测试
1.maven
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.ze</groupId>
<artifactId>mybatis02</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.9</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
</dependency>
<!-- log4j日志依赖 https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
<build>
<resources>
<resource>
<directory>src/main/resources</directory>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
</build>
</project>
2.db.properties
db.username=root
db.password=123
db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql:///student?serverTimezone=Asia/Shanghai
3.mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<properties resource="db.properties"/>
<environments default="dev">
<environment id="dev">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="username" value="${db.username}"/>
<property name="password" value="${db.password}"/>
<property name="url" value="${db.url}"/>
<property name="driver" value="${db.driver}"/>
</dataSource>
</environment>
</environments>
<mappers>
<package name="com.ze.demo.mapper"/>
</mappers>
</configuration>
4.实体类
public class Book {
private String id;
private String name;
private String author;
private Double price;
@Override
public String toString() {
return "Book{" +
"id=" + id +
", name='" + name + '\'' +
", author='" + author + '\'' +
", price=" + price +
'}';
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
}
5.实体类映射器 BookMapper
public interface BookMapper {
List<Book> getAllBooks();
List<Book> getBooksByAuthor(String author);
/**
* 如果方法有多个参数,可以通过 @Param 注解来给每个参数取名称
* @param author
* @param price
* @return
*/
List<Book> getBooksByAuthorAndPrice(@Param("author") String author, @Param("price") Double price);
/**
* map 类型的参数,可以就当成一个普通对象来处理
* @param params
* @return
*/
List<Book> getBooksByAuthorAndPrice2(Map<String,Object> params);
/**
* @param params
* @return
*/
List<Book> getBooksByAuthorAndPrice3(@Param("p") Map<String, Object> params, @Param("id") Integer id);
List<Book> getBooksByAuthorName(String author);
List<Book> getBooksByAuthorName2(String author);
List<Book> getBooksByAuthorName3(String author);
List<Book> getAllBooksOrderBy(@Param("o") String o, @Param("d") String d);
Integer addBook(Book book);
Integer addBook2(Book book);
Integer addBook3(Book book);
}
6.映射器配置文件 BookMapper.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.ze.demo.mapper.BookMapper">
<select id="getAllBooks" resultType="com.ze.demo.model.Book">
select * from book;
</select>
<!--
默认情况下,#{author} 中的 author 就是方法参数的名字
-->
<select id="getBooksByAuthor" resultType="com.ze.demo.model.Book">
select * from book where author=#{author}
</select>
<!--
如果方法参数不止一个,有多个,那么默认情况不可以直接使用参数名去引用一个参数。
默认的参数名是 arg0,arg1。。。或者是 param1、param2.。。。
-->
<select id="getBooksByAuthorAndPrice" resultType="com.ze.demo.model.Book">
select * from book where author=#{author} and price>#{price};
</select>
<select id="getBooksByAuthorAndPrice2" resultType="com.ze.demo.model.Book">
select * from book where author=#{author} and price>#{price};
</select>
<select id="getBooksByAuthorAndPrice3" resultType="com.ze.demo.model.Book">
select * from book where author=#{p.author} and price>#{p.price} and id>#{id};
</select>
<select id="getBooksByAuthorName" resultType="com.ze.demo.model.Book">
select * from book where author like #{author};
</select>
<!--
concat 字符串拼接函数,参数任意多个
-->
<select id="getBooksByAuthorName2" resultType="com.ze.demo.model.Book">
select * from book where author like concat(#{author},'%');
</select>
<!--
# 和 $ 区别:
# 用的是 PreparedStatement,参数有占位符,后来再为占位符提供具体的参数,可以防止 SQL 注入
$ 用的是 Statement,参数直接通过字符串拼接加到 SQL 中
-->
<select id="getBooksByAuthorName3" resultType="com.ze.demo.model.Book">
select * from book where author like '${author}%';
</select>
<select id="getAllBooksOrderBy" resultType="com.ze.demo.model.Book">
select * from book order by ${o} ${d};
</select>
<!--
keyProperty 表示把查询的结果赋值给哪个属性
order 指的是 selectKey 的执行时机,after 表示在插入 sql 之后执行
这个要执行两条 SQL,先插入,再查询
-->
<insert id="addBook" parameterType="com.ze.demo.model.Book">
<selectKey resultType="java.lang.Integer" keyProperty="id" order="AFTER">
select last_insert_id();
</selectKey>
insert into book (name,author,price) values (#{name},#{author},#{price});
</insert>
<!--
下面这种主键回填,等价于我们之前在 JDBC 中用的主键回填,这种方式只需要执行一条 SQL
-->
<insert id="addBook2" parameterType="com.ze.demo.model.Book" useGeneratedKeys="true" keyProperty="id">
insert into book (name,author,price) values (#{name},#{author},#{price});
</insert>
<!--
先执行 selectKey,再执行插入
-->
<insert id="addBook3" parameterType="com.ze.demo.model.Book">
<selectKey keyProperty="id" order="BEFORE" resultType="java.lang.String">
select uuid();
</selectKey>
insert into book (id,name,author,price) values (#{id},#{name},#{author},#{price});
</insert>
</mapper>
7.测试类 单元测试 junit4
public class MainTest {
private SqlSession sqlSession;
private BookMapper bookMapper;
@Test
public void test09(){
Book book = new Book();
book.setAuthor("鲁迅");
book.setName("朝花夕拾");
book.setPrice(22.0);
bookMapper.addBook3(book);
System.out.println("book.getId() = " + book.getId());
}
@Test
public void test08(){
List<Book> list = bookMapper.getAllBooksOrderBy("id", "desc");
System.out.println("list = " + list);
}
@Test
public void test07(){
List<Book> list = bookMapper.getBooksByAuthorName3("鲁");
System.out.println("list = " + list);
}
@Test
public void test06(){
List<Book> list = bookMapper.getBooksByAuthorName("鲁%");
System.out.println("list = " + list);
}
@Test
public void test05(){
Map<String, Object> params = new HashMap<>();
params.put("author", "鲁迅");
params.put("price", 25);
List<Book> list = bookMapper.getBooksByAuthorAndPrice3(params, 6);
System.out.println("list = " + list);
}
@Test
public void test04(){
Map<String, Object> params = new HashMap<>();
params.put("author", "鲁迅");
params.put("price", 25);
List<Book> list = bookMapper.getBooksByAuthorAndPrice2(params);
System.out.println("list = " + list);
}
@Test
public void test03(){
List<Book> list = bookMapper.getBooksByAuthorAndPrice("鲁迅", 25.0);
System.out.println("list = " + list);
}
@Test
public void test02(){
List<Book> list = bookMapper.getBooksByAuthor("鲁迅");
System.out.println("list = " + list);
}
@Test
public void test01(){
List<Book> list = bookMapper.getAllBooks();
System.out.println("list = " + list);
}
@Before
public void before() throws IOException {
sqlSession = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-cfg.xml")).openSession();
bookMapper = sqlSession.getMapper(BookMapper.class);
gradeMapper = sqlSession.getMapper(GradeMapper.class);
userMapper = sqlSession.getMapper(UserMapper.class);
}
@After
public void after() {
sqlSession.commit();
}
}
二、MyBatis的CRUD操作【重点
】
CRUD:亦即增删改查操作
- 传参时,如果是对象,#{ } 括号中直接使用对象里的具体值,但要做到一一对应
- 如果是普通参数(基本数据类型),要加注解 @Param(“id”) 起别名,再用 #{id} 就可以了
- 模糊查询要用 mysql 中的 concat(“a”,“b”) 函数;或者用 Statement 的拼接方式,直接字符串拼接,不过会有 sql注入 的问题。
- Mybatis 底层是 jdbc,目前市面上大多都是 jdbc,是阻塞的,同步的
1. 查询
标签:< select id="" resultType="" >
1.1 序号参数绑定–使用原生参数绑定
一个参数时,直接#{xxx} 使用,多个参数时要 arg0… 或者 param0…
public interface UserDao {
//使用原生参数绑定
public User selectUserByIdAndPwd(Integer id , String pwd);
}
<select id="selectUserByIdAndPwd" resultType="user">
SELECT * FROM t_users
WHERE id = #{arg0} AND password = #{arg1} <!--arg0 arg1 arg2 ...-->
</select>
<select id="selectUserByIdAndPwd" resultType="user">
SELECT * FROM t_users
WHERE id = #{param1} AND password = #{param2} <!--param1 param2 param3 ...-->
</select>
1.2 注解参数绑定【推荐】
- @Param(“id”)
- #{id}
import org.apache.ibatis.annotations.Param; //引入注解
public interface UserDao {
//使用MyBatis提供的@Param进行参数绑定
public User selectUserByIdAndPwd(@Param("id") Integer id , @Param("pwd") String pwd);
}
<select id="selectUserByIdAndPwd" resultType="user">
SELECT * FROM t_users
WHERE id = #{id} AND password = #{pwd} <!-- 使用注解值 @Param("pwd") -->
</select>
1.3 Map参数绑定
传入 Map 要手动匹配里面的 key-value,能匹配上就可以
public interface UserDao {
//添加Map进行参数绑定
public User selectUserByIdAndPwd_map(Map values);
}
Map values = new HashMap(); //测试类创建Map
values.put("myId",1); //自定义key,绑定参数
values.put("myPwd","123456");
User user = userDao.selectUserByIdAndPwd_map(values);
<select id="selectUserByIdAndPwd_map" resultType="user">
SELECT * FROM t_users
WHERE id = #{myId} AND password = #{myPwd} <!-- 通过key获得value -->
</select>
1.4 对象参数绑定
需要属性名和 #{id} 一一对应,才可以使用
public interface UserDao {
//使用对象属性进行参数绑定
public User selectUserByUserInfo(User user);
}
<select id="selectUserByUserInfo" resultType="user">
SELECT * FROM t_users
WHERE id = #{id} AND password = #{password} <!-- #{id}取User对象的id属性值、#{password}同理 -->
</select>
1.5 模糊查询
public interface UserDao {
public List<User> selectUsersByKeyword(@Param("keyword") String keyword);
}
<mapper namespace="com.qf.mybatis.part1.different.UserDao">
<select id="selectUsersByKeyword" resultType="user">
SELECT * FROM t_users
WHERE name LIKE concat('%',#{keyword},'%') <!-- 拼接'%' -->
</select>
</mapper>
2. 删除
标签:< delete id="" parameterType="" >
<delete id="deleteUser" parameterType="int">
DELETE FROM t_users
WHERE id = #{id} <!--只有一个参数时,#{任意书写}-->
</delete>
3 修改
标签:< update id="" parameterType="" >
<update id="updateUser" parameterType="user">
UPDATE t_users SET name=#{name}, password=#{password}, sex=#{sex}, birthday=#{birthday}
WHERE id = #{id} <!--方法参数为对象时,可直接使用#{属性名}进行获取-->
</update>
4 添加
标签:< insert id="" parameterType="" >
<!--手动主键-->
<insert id="insertUser" parameterType="user">
INSERT INTO t_users VALUES(#{id},#{name},#{password},#{sex},#{birthday},NULL);
</insert>
<!--自动主键-->
<insert id="insertUser" parameterType="user">
<!-- 自动增长主键,以下两种方案均可 -->
INSERT INTO t_users VALUES(#{id},#{name},#{password},#{sex},#{birthday},NULL);
INSERT INTO t_users VALUES(NULL,#{name},#{password},#{sex},#{birthday},NULL);
</insert>
5 主键回填
标签:< selectKey id="" parameterType="" order=“AFTER|BEFORE”>
5.1 通过last_insert_id()查询主键
<!--
keyProperty 表示把查询的结果赋值给哪个属性
order 指的是 selectKey 的执行时机,after 表示在插入 sql 之后执行
这个要执行两条 SQL,先插入,再查询
-->
<insert id="addBook" parameterType="com.ze.demo.model.Book">
<selectKey resultType="java.lang.Integer" keyProperty="id" order="AFTER">
select last_insert_id();
</selectKey>
insert into book (name,author,price) values (#{name},#{author},#{price});
</insert>
5.2 通过uuid()查询主键
<!--
先执行 selectKey,再执行插入
-->
<insert id="addBook3" parameterType="com.ze.demo.model.Book">
<selectKey keyProperty="id" order="BEFORE" resultType="java.lang.String">
select uuid();
</selectKey>
insert into book (id,name,author,price) values (#{id},#{name},#{author},#{price});
</insert>
5.3 自动返回主键,类似于 JDBC 的 Statement.ReturnGeneratedKeys
<!--
下面这种主键回填,等价于我们之前在 JDBC 中用的主键回填,这种方式只需要执行一条 SQL
-->
<insert id="addBook2" parameterType="com.ze.demo.model.Book" useGeneratedKeys="true" keyProperty="id">
insert into book (name,author,price) values (#{name},#{author},#{price});
</insert>
二、SqlSession 各参数介绍
-
Resource:用于获得读取配置文件的IO对象,耗费资源,建议通过IO一次性读取所有所需要的数据。
-
SqlSessionFactory:SqlSession工厂类,内存占用多,耗费资源,建议每个应用只创建一个对象。
-
SqlSession:相当于Connection,可控制事务,应为线程私有,不被多线程共享。
-
将获得连接、关闭连接、提交事务、回滚事务、获得接口实现类等方法进行封装。
三、ORM映射【重点
】
1 MyBatis自动ORM失效
MyBatis只能自动维护库表”列名“与”属性名“相同时的一一对应关系,二者不同时,无法自动ORM。
自动ORM失效 |
---|
2 方案一:列的别名
在SQL中使用 as 为查询字段添加列别名,以匹配属性名。
<mapper namespace="com.qf.mybatis.part2.orm.ManagerDao">
<select id="selectManagerByIdAndPwd" resultType="com.qf.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>
3 方案二:结果映射(ResultMap - 查询结果的封装规则)
通过< resultMap id="" type="" >映射,匹配列名与属性名。
<mapper namespace="com.qf.mybatis.part2.orm.ManagerDao">
<!--定义resultMap标签-->
<resultMap id="managerResultMap" type="com.qf.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>
四、MyBatis处理关联关系-多表连接【重点
】
实体间的关系:关联关系(拥有 has、属于 belong)
OneToOne:一对一关系(Passenger— Passport)
OneToMany:一对多关系(Employee — Department)
ManyToMany:多对多关系(Student — Subject)
Table建立外键关系 |
---|
Entity添加关系属性 |
---|
Mapper中将属性与列名对应 |
---|
1. OneToOne
SQL参考OneToOneExample.sql
<mapper namespace="com.qf.mybatis.part2.one2one.PassengerDao">
<!-- 结果映射(查询结果的封装规则) -->
<resultMap id="passengerResultMap" type="com.qf.mybatis.part2.one2one.Passenger">
<id property="id" column="id"/>
<result property="name" column="name" />
<result property="sex" column="sex" />
<result property="birthday" column="birthday" />
<!-- 关系表中数据的封装规则 --> <!-- 指定关系表的实体类型 -->
<association property="passport" javaType="com.qf.mybatis.part2.one2one.Passport">
<id property="id" column="passport_id" />
<result property="nationality" column="nationality" />
<result property="expire" column="expire" />
<result property="passenger_id" column="passenger_id" />
</association>
</resultMap>
<!-- 多表连接查询 --> <!-- 结果映射(查询结果的封装规则)-->
<select id="selectPassengerById" resultMap="passengerResultMap">
<!-- 别名(避免与p1.id冲突) -->
SELECT p1.id , p1.name , p1.sex , p1.birthday , p2.id as passport_id , p2.nationality , p2.expire , p2.passenger_id
FROM t_passengers p1 LEFT JOIN t_passports p2
ON p1.id = p2.passenger_id
WHERE p1.id = #{id}
</select>
</mapper>
2. OneToMany
SQL参考OneToManyExample.sql
<mapper namespace="com.qf.mybatis.part2.one2many.DepartmentDao">
<!-- 封装规则 -->
<resultMap id="departmentResultMap" type="com.qf.mybatis.part2.one2many.Department">
<id property="id" column="id" />
<result property="name" column="name" />
<result property="location" column="location" />
<!-- 关系表中数据的封装规则 --> <!-- 指定关系表的实体类型 -->
<collection property="emps" ofType="com.qf.mybatis.part2.one2many.Employee">
<id property="id" column="emp_id" />
<result property="name" column="emp_name" />
<result property="salary" column="salary" />
<result property="dept_id" column="dept_id" />
</collection>
</resultMap>
<!-- 多表连接查询 --> <!-- 封装规则 -->
<select id="selectDepartmentById" resultMap="departmentResultMap" >
<!-- 别名(避免与d.id、d.name冲突)-->
SELECT d.id , d.name , d.location , e.id AS emp_id , e.name emp_name , e.salary , e.dept_id
FROM t_departments d LEFT JOIN t_employees e
ON d.id = e.dept_id
WHERE d.id = #{id}
</select>
</mapper>
3. 关系总结
一方,添加集合;多方,添加对象。
双方均可建立关系属性,建立关系属性后,对应的Mapper文件中需使用< ResultMap >完成多表映射。
持有对象关系属性,使用< association property=“dept” javaType=“department” >
持有集合关系属性,使用< collection property=“emps” ofType=“employee” >