【应用篇】MyBatis学习笔记

MyBatis学习笔记

---

一 环境配置

1 什么是MyBatis?

​ MyBatis 是支持普通 SQL 查询,存储过程和高级映射的优秀持久层框架。MyBatis 消除了几乎所有的 JDBC 代码和参数的手工设置以及结果集的检索。MyBatis 使用简单的 XML 或注解用于配置和原始映射,将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java 对象)映射成数据库中的记录。

​ 特点：使用MyBatis框架时，不需要编写实现类（原生的JDBC代码），只需要专注需要执行的sql命令。

2 相关jar包

3 MyBatis全局配置文件 MyBatis.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!-- dtd文档，包含命名空间-->
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--全局配置标签-->
<configuration>
    <!-- 设置LOG4J日志包生效-->
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>
    <typeAliases>
        <!--为整个包起别名,所有包下的类名为resultType中属性值-->
        <package name="cn.thetian.pojo"/>
    </typeAliases>
    <!--环境标签-->
    <environments default="default">
        <!--声明可以使用的环境mysql,oracle等-->
        <environment id="default">
            <!--使用原生JDBC事务-->
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/ajax?serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <!--映射标签，扫描mapper下的xml文件和mapper接口-->
    <mappers>
        <package name="cn.thetian.mapper"/>
    </mappers>
</configuration>

4 LOG4J日志配置文件

文件名：log4j.properties(必须用这个名字且需要处在src根目录下,用来调试程序以及记录日志)

log4j.rootCategory=INFO, CONSOLE, LOGFILE
#将mapper包设为DEBUG级别来查看sql语句
log4j.logger.cn.thetian.mapper=DEBUG

log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=%C %d{YYYY.MM.dd hh:mm:ss} %m %n

log4j.appender.LOGFILE=org.apache.log4j.FileAppender
log4j.appender.LOGFILE.file=D://my.log
#true 表示追加 false表示不追加
log4j.appender.LOGFILE.Append = false
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.Conversionattern=%m %n

二 一个简单的用例

1 设计数据库

就以一个简单的用户表为例子

2 编写pojo(例：User)

public class User {
    private int id;
    private String name;
    private String sex;
    private int age;
    private String hobby;
    
    //GetterAndSetter();
    //toString();
}

3 编写pojoMapper.xml(例：userMapper.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">
<!-- namespace 为mapper起名，通过namespace.id调用不同的标签-->
<mapper namespace="cn.thetian.mapper.UserMapper">
    <!-- 
	id：标签id，一般以方法名命名 
	resultType：返回值类型，未起别名时要用包名加类名表示返回类型，自动使用AutoMapping特性，即一条记录对应一个对象，数据库中字段名要与类的属性名要保持一致。
	-->
    <select id="selAll" resultType="User">
        select * from user
    </select>
</mapper>

4 测试

/**
 * 测试session中的selectList
 * 1 获取mybatis.xml全局配置文件的字节流
 * 2 利用工厂模式和构建者模式获取工厂
 * 3 生产SqlSession
 * 4 利用SqlSession运行指定mapper的xml文件中的namespace+方法名
 * 5 返回List
 */
public class Test01 {
    public static void main(String[] args) throws IOException {
        //获取MyBatis.xml文件的字节流
        InputStream is = Resources.getResourceAsStream("mybatis.xml");
        //工厂设计模式
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        //生产SqlSession
        SqlSession session = factory.openSession();

        List<User> userList = session.selectList("cn.thetian.mapper.UserMapper.selAll");
        for (User user:userList){
            System.out.println(user.toString());
        }
        //关闭session
        session.close();
    }
}

result：

==> Preparing: select * from user
> Parameters:
< Total: 5
User{id=1, name=‘张三’, sex=‘男’, age=18, hobby=‘打游戏,看动漫’}
User{id=2, name=‘李四’, sex=‘男’, age=20, hobby=‘打张三,写代码’}
User{id=29, name=‘六六’, sex=‘女’, age=22, hobby=‘主播’}
User{id=30, name=‘张飞’, sex=‘男’, age=8000, hobby=‘俺也一样’}
User{id=31, name=‘王五’, sex=‘男’, age=666, hobby=‘helloworld’}

三 增删查改

1 增（insert标签）

<insert id="insUser" parameterType="User">
    <!-- #{key} 可以获取到参数值(value)（底层是将user转为map）-->
    insert into user values (default, #{name}, #{sex}, #{age}, #{hobby});
</insert>

demo：

try {
	User user = new User();
	user.setName("六六");
	user.setSex("女");
	user.setAge(22);
	user.setHobby("主播");
    
    //将一个user对象作为参数传递到userMapper.xml文件中
	int result = session.insert("cn.thetian.mapper.UserMapper.insUser", user);
	System.out.println(result>0?"添加成功":"添加失败");
	}catch (Exception e){
    	//出现异常回滚事务
		session.rollback();
	}
//增删改查需要手动提交事务（可以再MyBatis.xml文件中设置自动提交	）
session.commit();
session.close();

2 删（delete标签）

<delete id="delUser" parameterType="user">
	delete from user where id=#{id}
</delete>

demo:

try {
	User user = new User();
	user.setId(31);
	int delete = session.delete("cn.thetian.mapper.UserMapper.delUser", user);
	System.out.println(delete>0?"删除成功":"删除失败");
}catch (Exception e){
    //如果出现异常，回滚事务
	session.rollback();
}
session.commit();
session.close();

3 查

3.1 selectOne

**特点：**查询一个对象或变量，返回值为一个基本数据类型或者一个引用数据类型对象

<!-- 查询user表的行数-->
<select id="selCount" resultType="int">
    select count(*) from user
</select>

demo:

int num = session.selectOne("cn.thetian.mapper.UserMapper.selCount");
System.out.println(num);
session.close();

result:

==> Preparing: select count(*) from user
> Parameters:
< Total: 1
5

3.2 selectList

特点： 查询多条记录，返回集合，举例 标题二

3.3 selectMap

特点： 将数据库中某一字段当作map的key(取pojo中的字段) 返回->以参数为key,以返回结果为值的map

<select id="selMap" resultType="User">
	select * from user
</select>

Map<Object, Object> map = session.selectMap("cn.thetian.mapper.UserMapper.selMap","name");
System.out.println(map);
session.close();

result：

==> Preparing: select * from user
> Parameters:
< Total: 5
{李四=User{id=2, name=‘李四’, sex=‘男’, age=20, hobby=‘打张三,写代码’}, …

4 改（update）

<update id="updAge">
	update user set age=age+1
</update>

demo:

try {
	int index = session.update("cn.thetian.mapper.UserMapper.updAge");
	System.out.println(index);
}catch (Exception e){
	session.rollback();
}
session.commit();
session.close();

result:

==> Preparing: update user set age=age+1
> Parameters:
< Updates: 5
5

四 MyBatis接口绑定和注解

1 接口绑定方案

MyBatis接口绑定：创建一个接口来实现maaper.xml文件中的增删改查，外部通过调用接口来处理sql

UserMapper：

public interface UserMapper {
    List<User> selAll();
    List<User> selById(int id);
    List<User> selMap();
    int selCount();
    List<User> selByLimit(Map map);
    int insUser(User user);
    int delUser(User user);
    int updAge();
}

如果使用注解，可以不用编写UserMapper.xml配置文件

public interface UserMapper {
    @Select("select * from user")
    List<User> selAll();
    
    @Select("select * from user where id=#{param1}")
    List<User> selById(int id);
    
    @Select("select * from user")
    List<User> selMap();
    
    @Select("select count(*) from user")
    int selCount();
    
    @Select("select * from user limit #{pageNum},#{pageSize}")
    List<User> selByLimit(Map map);
    
    @Insert("insert into user values (default, #{name}, #{sex}, #{age}, #{hobby})")
    int insUser(User user);
    
    @Delete("delete from user where id=#{id}")
    int delUser(User user);
    
    @Update("update user set age=age+1")
    int updAge();
}

当使用接口绑定后可以通过一下方式调用：

InputStream is = Resources.getResourceAsStream("mybatis.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
SqlSession session = factory.openSession();
UserMapper userMapper = session.getMapper(UserMapper.class);

效果等同于使用session调用不同的 namespace+id

2 多参数传递

使用场景：

当需要一次传递多个参数（不使用map）时

使用方法：

在接口中利用注解 @Param 底层原理是将 参数转化为map

@Select("select * from account where account=#{param1} and name=#{param2}")
Account selAccNoName(@Param("account") String account, @Param ("name") String name);

demo:

Account account = accountMapper.selAccNoName("1", "刘备");
System.out.println(account);

result：

==> Preparing: select * from account where account=? and name=?
> Parameters: 1(String), 刘备(String)
< Total: 1
Account{id=1, account=‘1’, password=1, balance=4674.0, name=‘刘备’}

五 动态SQL

由于利用注解实现动态sql很繁琐，所以牵扯到动态sql的部分任然使用配置文件

1 where用法

需求： 查询账户，查询条件：无任何条件，按账户名查找，按名字查找（可是实现任意单个条件查找或者组合条件查找）

xml：

<select id="selWhereAccNoName" resultType="Account">
	<!--基本数据类型和String 应该用0,1,param1,param2等-->
	select * from account
    <!-- where标签会自动去掉第一个and-->
	<where>
        <!-- 如果传递的账户对象中含有有效 账户名-->
		<if test="account!=null and account!=''">
			and account=#{account}
		</if>
        <!-- 如果传递的账户对象中含有有效 名字-->
		<if test="name!=null and name!=''">
			and name=#{name}
		</if>
	</where>
</select>

demo：

Scanner scanner = new Scanner(System.in);
System.out.println("请输入账户名");
String account = scanner.nextLine();
System.out.println("请输入名字");
String name = scanner.nextLine();//直接回车跳过 相当于空串而不是null
List<Account> accountList = accountMapper.selChooseAccNoName(account, name);
System.out.println(accountList);
session.close();

result：

1 查找所有账户，即不输入账户名和名字

请输入账户名

请输入名字

==> Preparing: select * from account
> Parameters:
< Total: 3
[Account{id=1, account=‘1’, password=1, balance=4674.0, name=‘刘备’},…

2 按账户名查找账户

请输入账户名
1
请输入名字

==> Preparing: select * from account WHERE account=?
> Parameters: 1(String)
< Total: 1
[Account{id=1, account=‘1’, password=1, balance=4674.0, name=‘刘备’}]

3 按名字查找用户

请输入账户名

请输入名字
张飞
==> Preparing: select * from account WHERE name=?
> Parameters: 张飞(String)
< Total: 1
[Account{id=3, account=‘3’, password=1, balance=5100.0, name=‘张飞’}]

4 按账户名和名字查找用户

请输入账户名
1
请输入名字
刘备
==> Preparing: select * from account WHERE account=?
> Parameters: 1(String)
< Total: 1
[Account{id=1, account=‘1’, password=1, balance=4674.0, name=‘刘备’}]

2 choose when otherwise用法

xml：

<select id="selChooseAccNoName" resultType="Account">
    select * from account
	<where>
		<choose>
            <when test="account!=null and account!=''">
	            and account=#{account}
		    </when>
			<when test="name!=null and name!=''">
				and name=#{name}
	        </when>
        </choose>
	</where>
</select>

用法和使用 where+if 语句类似，可实现相同的需求

3 set用法

xml：

<!-- 使用set 去掉生成语句最后面的逗号,在最前方加set-->
<update id="updBalance" parameterType="Account">
	update account
	<set>
        <!-- 为了避免set语句为空需要写一个无关句-->
        id=#{id},
    	<if test="balance!=null">
			balance=#{balance},
    	</if>
	</set>
	where id=#{id}
</update>

demo：

Account newAccount = new Account();
newAccount.setId(1);
newAccount.setBalance(123456);
int i = accountMapper.updBalance(newAccount);
System.out.println(i>0?"修改成功":"修改失败");
session.close();

result：

==> Preparing: update account SET id=?, balance=? where id=?
> Parameters: 1(Integer), 123456.0(Double), 1(Integer)
< Updates: 1
修改成功

4 trim用法

使用trim代替set标签和where子句

xml：

<!-- 使用trim实现where和set
    prefixOverrides 去掉生成语句最前面的"and"
    prefix 在生成语句最前加添加"set(默认后面跟个空格)
    suffix 在生成语句最后放添加"参数"
    suffixOverrides 去掉生成语句最后面的"逗号"
    执行的顺序为>先去掉内容,后添加内容
-->
<update id="pudByTrim" parameterType="Account">
	update account
    <trim prefix="set" suffixOverrides=",">
		<if test="id!=null">
        	id=#{id},
		</if>
		<if test="balance!=null">
	        balance=#{balance},
        </if>
	</trim>
    <trim prefix="where" prefixOverrides="and">
		<if test="id!=null">
	        and id=#{id}
        </if>
	</trim>
</update>

结果与只是用set类似

5 bind用法

需求： 模糊查询

xml

<select id="selDIY" parameterType="Account" resultType="Account">
	<if test="name!=null and name!=''">
        <!-- 获取name后再前后各加一个%进行字符匹配-->
        <bind name="name" value="'%'+name+'%'"/>
	</if>
    	select * from account
	<trim prefix="where name like">
	    <if test="name!=null and name!=''">
			#{name}
		</if>
	</trim>
</select>

demo：

Account newAccount = new Account();
newAccount.setName("备");
List<Account> accountList = accountMapper.selDIY(newAccount);
System.out.println(accountList);
session.close();

result：

==> Preparing: select * from account where name like ?
> Parameters: %备%(String)
< Total: 1
[Account{id=1, account=‘1’, password=1, balance=4674.0, name=‘刘备’}]

6 foreach用法

需求1：in查询

xml：

<select id="selByIn" parameterType="list" resultType="Account">
	select * from account where id in
    <!-- 使用foreach操作集合-->
	<foreach collection="list" item="var" open="(" close=")" separator=",">
		#{var}
	</foreach>
</select>

demo：

List<Integer> list = new ArrayList<>();
list.add(1);
list.add(2);
list.add(3);
List<Account> accountList = accountMapper.selByIn(list);
System.out.println(accountList);
session.close();

result：

==> Preparing: select * from account where id in ( ? , ? , ? )
> Parameters: 1(Integer), 2(Integer), 3(Integer)
< Total: 3
[Account{id=1, account=‘1’, password=1, balance=4674.0, name=‘刘备’}, …

需求2：批量新增

xml：

<insert id="insBatch" parameterType="list">
	insert into account values
	<trim suffixOverrides=",">
		<foreach collection="list" item="var">
            <!--账户名密码都为var-->
	        (default ,#{var},#{var},0,'测试名'),
		</foreach>
	</trim>
</insert>

demo：

//需要SqlSession session = factory.openSession(ExecutorType.BATCH);否则会出异常ExecutorException: Executor was closed.
List<Integer> list = new ArrayList<>();
for (int i=10;i<20;i++){
	list.add(i);
}
accountMapper.insBatch(list);
session.commit();
session.close();

result：

==> Preparing: insert into account values (default ,?,?,0,‘测试名’), (default ,?,?,0,‘测试名’), …

==> Parameters: 10(Integer), 10(Integer), 11(Integer), 11(Integer), …

六 关联查询

数据库设计：

POJO设计（假设一个学生只有一个老师，但一个老师可以有多个学生）：

需求： 在查询学生的同时，也要查出他们所关联的老师

1 业务装配

顾名思义，就是把两个相关联的表单独查询出来，在业务层拼装成联合查询的结果

注解：

1：StudentMapper

@Select("select * from student")
List<Student> selAll();

2：TeacherMapper

@Select("select * from teacher where id=#{0}")
Teacher selById(int id);

demo：

public class Test04 {
    public static void main(String[] args) throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession(ExecutorType.BATCH);
        
        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        TeacherMapper teacherMapper = session.getMapper(TeacherMapper.class);
        
        List<Student> studentList = studentMapper.selAll();
        for (Student student:studentList){
            student.setTeacher(teacherMapper.selById(student.getTid()));
            System.out.println(student);
        }
        session.close();
    }
}

result：

==> Preparing: select * from student
> Parameters:
< Total: 6
==> Preparing: select * from teacher where id=?
> Parameters: 1(Integer)
< Total: 1
Student{id=1, name=‘ZT’, age=15, tid=1, teacher=Teacher{id=1, name=‘孔子’}}
==> Preparing: select * from teacher where id=?
> Parameters: 2(Integer)
< Total: 1
Student{id=2, name=‘万志’, age=17, tid=2, teacher=Teacher{id=2, name=‘释迦摩尼’}}
Student{id=3, name=‘张飞’, age=7999, tid=1, teacher=Teacher{id=1, name=‘孔子’}}
==> Preparing: select * from teacher where id=?
> Parameters: 3(Integer)
< Total: 1
Student{id=4, name=‘刘备’, age=8001, tid=3, teacher=Teacher{id=3, name=‘王阳明’}}
Student{id=5, name=‘关羽’, age=8000, tid=2, teacher=Teacher{id=2, name=‘释迦摩尼’}}
Student{id=6, name=‘鲁智深’, age=18, tid=3, teacher=Teacher{id=3, name=‘王阳明’}}

2 resultMap标签

使用resultMap实现关联单个对象（N+1）

xml：

<resultMap id="teacherMapping" type="student">
	<id column="id" property="id"></id>
	<result column="name" property="name"></result>
	<result column="age" property="age"></result>
	<result column="tid" property="tid"></result>
	<association property="teacher" select="cn.thetian.mapper.TeacherMapper.selById" column="tid"></association>
</resultMap>
<select id="selAll" resultMap="teacherMapping">
	select * from student
</select>

demo：

public class Test04 {
    public static void main(String[] args) throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession(ExecutorType.BATCH);
        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        List<Student> studentList = studentMapper.selAll();
        for (Student student:studentList){
            System.out.println(student);
        }
        session.close();
    }
}

result：

==> Preparing: select * from student
==> Parameters:
====> Preparing: select * from teacher where id=?
> Parameters: 1(Integer)
< Total: 1
====> Preparing: select * from teacher where id=?
> Parameters: 2(Integer)
< Total: 1
==> Preparing: select * from teacher where id=?
> Parameters: 3(Integer)
< Total: 1
< Total: 6
Student{id=1, name=‘ZT’, age=15, tid=1, teacher=Teacher{id=1, name=‘孔子’}}
Student{id=2, name=‘万志’, age=17, tid=2, teacher=Teacher{id=2, name=‘释迦摩尼’}}
Student{id=3, name=‘张飞’, age=7999, tid=1, teacher=Teacher{id=1, name=‘孔子’}}
Student{id=4, name=‘刘备’, age=8001, tid=3, teacher=Teacher{id=3, name=‘王阳明’}}
Student{id=5, name=‘关羽’, age=8000, tid=2, teacher=Teacher{id=2, name=‘释迦摩尼’}}
Student{id=6, name=‘鲁智深’, age=18, tid=3, teacher=Teacher{id=3, name=‘王阳明’}}

3 AutoMapping特性

xml：

<resultMap id="teacherMapping" type="student">
	<id column="id" property="id"></id>
	<result column="name" property="name"></result>
    <result column="age" property="age"></result>
	<result column="tid" property="tid"></result>
    <association property="teacher" javaType="teacher">
		<id column="tid" property="id"></id>
	    <result column="tname" property="name"></result>
    </association>
</resultMap>
<select id="selAll" resultMap="teacherMapping">
	select s.*,t.name tname from student s join teacher t on s.tid=t.id;
</select>

demo：

public class Test04 {
    public static void main(String[] args) throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession(ExecutorType.BATCH);
        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        List<Student> studentList = studentMapper.selAll();
        for (Student student:studentList){
            System.out.println(student);
        }
        session.close();
    }
}

result：

==> Preparing: select s.*,t.name tname from student s join teacher t on s.tid=t.id;
> Parameters:
< Total: 6
Student{id=1, name=‘ZT’, age=15, tid=1, teacher=Teacher{id=1, name=‘孔子’}}
Student{id=2, name=‘万志’, age=17, tid=2, teacher=Teacher{id=2, name=‘释迦摩尼’}}
Student{id=3, name=‘张飞’, age=7999, tid=1, teacher=Teacher{id=1, name=‘孔子’}}
Student{id=4, name=‘刘备’, age=8001, tid=3, teacher=Teacher{id=3, name=‘王阳明’}}
Student{id=5, name=‘关羽’, age=8000, tid=2, teacher=Teacher{id=2, name=‘释迦摩尼’}}
Student{id=6, name=‘鲁智深’, age=18, tid=3, teacher=Teacher{id=3, name=‘王阳明’}}

pper = session.getMapper(StudentMapper.class);
List studentList = studentMapper.selAll();
for (Student student:studentList){
System.out.println(student);
}
session.close();
}
}

**result：**

>==>  Preparing: select s.*,t.name tname from student s join teacher t on s.tid=t.id;  
>==> Parameters:  
><==      Total: 6 
>Student{id=1, name='ZT', age=15, tid=1, teacher=Teacher{id=1, name='孔子'}}
>Student{id=2, name='万志', age=17, tid=2, teacher=Teacher{id=2, name='释迦摩尼'}}
>Student{id=3, name='张飞', age=7999, tid=1, teacher=Teacher{id=1, name='孔子'}}
>Student{id=4, name='刘备', age=8001, tid=3, teacher=Teacher{id=3, name='王阳明'}}
>Student{id=5, name='关羽', age=8000, tid=2, teacher=Teacher{id=2, name='释迦摩尼'}}
>Student{id=6, name='鲁智深', age=18, tid=3, teacher=Teacher{id=3, name='王阳明'}}