MyBatis

发表于   |  

概述

一个基于Java的半自动化持久层框架。MyBatis不会为开发者自动生成SQL执行，而是需要开发人员自己写SQL,然后通过映射配置文件，将返回返回结果映射到指定的POJO。

核心配置文件mybatis-config.xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<configuration>
	<!-- 实体类的别名 -->
	<typeAliases>
		<typeAlias type="com.icss.mybatis.pojo.Student" alias="Student"/>
	</typeAliases>	
	<!-- 环境管理，可以配置多个环境，默认是default="development"的环境 -->
	<environments default="development">
		<environment id="development">
			<!-- 事务管理方式,JDBC事务 -->
			<transactionManager type="JDBC" />
			<!-- 数据源，type="POOLED"表示使用连接池 -->
			<dataSource type="POOLED">
				<property value="com.mysql.jdbc.Driver" name="driver" />
				<property value="jdbc:mysql://localhost/mybatis" name="url" />
				<property value="root" name="username" />
				<property value="123456" name="password" />
			</dataSource>
		</environment>
	</environments>

	<!--映射文件路径，可能会有多个mapper映射 -->
	<mappers>	
		<mapper resource="com/icss/mybatis/pojo/StudentMapper.xml"/>	
	</mappers>	
</configuration>

实现crud基本步骤

1.创建POJO

​ 创建数据库表对应的Java类，此处省略

2.创建配置Mapper映射文件

1
2
3
<mapper namespace="people" >

</mapper>

3.在Mapper映射文件中配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<mapper namespace="people" >
  <resultMap id="BaseResultMap" type="com.yonyou.sg.cpu.basedoc.domain.People" >
    <id column="pk_people" property="pkPeople" jdbcType="BIGINT" />
    <result column="modifier" property="modifier" jdbcType="VARCHAR" />
    <result column="code" property="code" jdbcType="VARCHAR" />
    <result column="creationtime" property="creationtime" jdbcType="VARCHAR" />
    <result column="age" property="age" jdbcType="VARCHAR" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="modifiedtime" property="modifiedtime" jdbcType="VARCHAR" />
    <result column="creator" property="creator" jdbcType="VARCHAR" />
    <result column="ts" property="ts" jdbcType="TIMESTAMP" />
    <result column="dr" property="dr" jdbcType="SMALLINT" />
  </resultMap>
  <select id="getPeopleByPK"  resultMap="BaseResultMap" parameterType="java.lang.Long">
    select * from people where  pk_people=#{pk}
  </select>
</mapper>

配置表列与类属性映射的结果集，type属性可以使用完整pojo类名，如果定义了类别名，也可以使用类别名
不需要把所有字段都映射，可以只映射一部分，主键字段必须用id元素指定

‘#{}‘语法用于插入动态数据，数据来源于调用此语句时传入的参数，参数类型由parameterType属性决定

4.核心配置文件中，引入Mapper

1
2
3
<mappers>	
		<mapper resource="com/icss/mybatis/pojo/PeopleMapper.xml"/>	
</mappers>

5.读取核心配置文件，调用CRUD功能

1
2
3
4
5
private SqlSessionFactory sessionFactory;
  
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");

sessionFactory = new SqlSessionFactoryBuilder().build(is);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
这里执行Sql有两种方式：
1.通过namespace+statementId
public void testInsert() {
		SqlSession session = sessionFactory.openSession();
		Student stu = new Student("李四",Date.valueOf("1987-6-10"),"119");
		session.insert("people.insert", stu);
		session.commit();
		session.close();
}

2.创建Dao接口,(Mapper的namespace路径必须与dao接口路径一致)
 //dao接口申明
package com.icss.mybatis.dao;
import java.util.List;
import com.icss.mybatis.pojo.Student;
public interface StudentMapper {
	void insert(Student stu);
	void udpate(Student stu);
	void delete(Integer stuId);
	Student queryById(Integer stuId);
	List<Student> query();
}
//执行sql
public void testInsert() {
	SqlSession session = sessionFactory.openSession();
	StudentMapper studentMapper = session.getMapper(StudentMapper.class);
	Student stu = new Student("钱七",Date.valueOf("1990-6-7"),"114");
	studentMapper.insert(stu);
	session.commit();
	session.close();
}

动态SQL元素

1
2
3
4
5
6
if
choose(when,otherwise)
trim
where
set
foreach
1
2
3
4
5
6
7
8
9
10
11
12
<select id="queryByCondition" parameterType="com.icss.mybatis.pojo.Student" resultMap="BaseResultMap">
select * from student where 1=1
<if test="stuName != null and stuName != ''">
	and stu_name=#{stuName}
</if>
<if test="stuBirthdate != null">
	and stu_birthdate=#{stuBirthdate}
</if>
<if test="stuPhone != null and stuPhone != ''">
	and stu_phone=#{stuPhone}
</if>
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<select id="queryByCondition2" parameterType="com.icss.mybatis.pojo.Student" resultMap="BaseResultMap">
select * from student where 1=1
<choose>
<when test="stuName != null and stuName != ''">
	and stu_name=#{stuName}
</when>
<when test="stuBirthdate != null">
	and stu_birthdate=#{stuBirthdate}
</when>
<otherwise>
	and stu_phone=#{stuPhone}
</otherwise>
</choose>
</select>

choose元素相当于java语句的if … else if …else语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<select id="queryByCondition3" parameterType="com.icss.mybatis.pojo.Student" resultMap="BaseResultMap">
select * from student
<where>
<if test="stuName != null and stuName != ''">
	and stu_name=#{stuName}
</if>
<if test="stuBirthdate != null">
	and stu_birthdate=#{stuBirthdate}
</if>
<if test="stuPhone != null and stuPhone != ''">
	and stu_phone=#{stuPhone}
</if>
</where>
</select>

使用where元素会自动根据条件的个数增删where语句and运算符，所以不需要写where 1=1之类的语句
1
2
3
4
5
6
7
8
9
10
<!-- 动态SQL：传入Array数组 -->
<select id="queryByInArray" resultMap="BaseResultMap">
   select * from student 
<if test="array.length>0">
where stu_id in 
<foreach collection="array" index="i" item="stuId" open="(" close=")" separator=",">
#{stuId}
</foreach>
</if>
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<!-- 动态SQL：trim标签 -->
<select id="queryByCondition4" parameterType="com.icss.mybatis.pojo.Student" resultMap="BaseResultMap">
select * from student
<trim prefix="where" prefixOverrides="and|or">
<if test="stuName != null and stuName != ''">
	and stu_name=#{stuName}
</if>
<if test="stuBirthdate != null">
	and stu_birthdate=#{stuBirthdate}
</if>
<if test="stuPhone != null and stuPhone != ''">
	or stu_phone=#{stuPhone}
</if>
</trim>
</select>
trim元素的主要功能是可以在自己包含的内容前加上某些前缀，也可以在其后加上某些后缀，与之对应的属性是prefix和suffix；可以把包含内容的首部某些内容覆盖，即忽略，也可以把尾部的某些内容覆盖，对应的属性是prefixOverrides和suffixOverrides
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<!-- 动态SQL：set更新 -->
<update id="updateByCondition" parameterType="com.icss.mybatis.pojo.Student">
update student
<set>
<if test="stuName!=null and stuName!=''">
stu_name=#{stuName},
</if>
<if test="stuBirthdate!=null">
stu_birthdate=#{stuBirthdate},
</if>
<if test="stuPhone!=null and stuPhone!=''">
stu_phone=#{stuPhone}
</if>
</set>
where stu_id=#{stuId}
</update>