一、简介
MyBatis 是支持普通 SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis 消除了几乎所有的JDBC代码和参数的手工设置以及结果集的检索。
MyBatis 使用简单的 XML或注解用于配置和原始映射，将接口和 Java 的POJOs（Plain Old Java Objects，普通的 Java对象）映射成数据库中的记录。
Mybatis框架的组成架构图如下：
如图所见我们把Mybatis的功能架构分为四层：
1）API接口层：提供给外部使用的接口API，开发人员通过这些本地API来操纵数据库。接口层一接收到调用请求就会调用数据处理层来完成具体的数据处理。
2）数据处理层：负责具体的SQL查找、SQL解析、SQL执行和执行结果映射处理等。它主要的目的是根据调用的请求完成一次数据库操作。
3）基础支撑层：负责最基础的功能支撑，包括连接管理、事务管理、配置加载和缓存处理，这些都是共用的东西，将他们抽取出来作为最基础的组件。为上层的数据处理层提供最基础的支撑。
4）引导层：基于XML配置方式还是基于Java API方式。
二、关键字符：
1、我们需要通过xml格式处理sql语句时，经常会用到< ，<=，>，>=等符号，但是很容易引起xml格式的错误，这样会导致后台将xml字符串转换为xml文档时报错，从而导致程序错误。
这样的问题在iBatiS中或者自定义的xml处理sql的程序中经常需要我们来处理。其实很简单，我们只需作如下替换即可避免上述的错误：
2、#相当于对数据 加上 双引号；$相当于直接显示数据，不会加上引号：
public List<GroupUsersProfile> queryGroupUsers(QueryGroupUsersModel queryGroupUsersModel) {
//crowdId非空
if(StringUtils.isEmpty(queryGroupUsersModel.getCrowdId())==true) {
new ArrayList<GroupUsersProfile>();
}
//需要part
if(StringUtils.isEmpty(queryGroupUsersModel.getPart())==true) {
UserGroup userGroup = userGroupService.queryUserGroupById(queryGroupUsersModel.getCrowdId());
if(userGroup!=null) {
queryGroupUsersModel.setPart(userGroup.getPart());
}
}
//查询表名
String tableName = cmsCrowdRouteMapper.getCrowdTableByCrowdId(queryGroupUsersModel.getCrowdId());
if(tableName==null) {
logger.error("%s查看人群用户列表，获取tableName空,crowdId:%s",null,queryGroupUsersModel.getCrowdId());
return null;
}
//查询数目
if(queryGroupUsersModel.getStartIndex() == null) {
queryGroupUsersModel.setStartIndex(0);
}
if(queryGroupUsersModel.getPageSize() == null || queryGroupUsersModel.getPageSize().intValue() <=0) {
queryGroupUsersModel.setPageSize(10);
}
//mysql查询
return groupUsersMapper.getGroupUsersList(queryGroupUsersModel);
}<!--查询用户群用户-->
<select id="getGroupUsersList" resultMap="BaseResultMap" parameterType="com.iflytek.edmp.domain.vo.QueryGroupUsersModel">
select
<include refid="Base_Column_List" />
from ${tableName}
WHERE
1=1
and is_delete = 0
<if test=" crowdId != null and crowdId != ''">
and crowd_id = #{crowdId,jdbcType=VARCHAR}
</if>
<if test=" part != null and part != ''">
and part = #{part,jdbcType=VARCHAR}
</if>
<if test=" orderColumn != null and orderColumn != '' and orderDir != null and order !=''">
ORDER BY ${orderColumn} ${orderDir}
</if>
<if test=" startIndex != null and startIndex != '' and pageSize != null and pageSize != ''">
LIMIT #{startIndex},#{pageSize}
</if>
</select>三、传参：一般有两种方法：
1、在dao.java的接口上给参数加上@Param注解，sql中不需要再写parameterType，直接使用即可；如：
UserDao.java：
int insert(@Param("user") User user,@Param("userId") String userId);xml:
<insert id="insert">
insert into user values(#{user.userName},#{userId})
</insert>2、在dao.java的接口参数上不加@Param注解，sql中需要写parameterType指明类型。
四、基础持久化操作：增删改可以不返回结果，查询必须返回结果，也即必须有resultType或者resultMap。
1、增(insert)：
下面在com.iflytek.dao下创建两个文件来进行数据库操作：
<?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.iflytek.dao.BlogMapper">
<insert id="addBlog" parameterType="Blog">
insert into t_blog
(id, title)
values
(#{id}, #{title})
</insert>
</mapper>已定义的类型用parameterType。
package com.iflytek.dao;
import com.iflytek.domain.Blog;
public interface BlogMapper {
public void addBlog(Blog blog);
}测试：
package com.iflytek.test;
import java.io.IOException;
import java.io.InputStream;
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 com.iflytek.dao.BlogMapper;
import com.iflytek.domain.Blog;
import oracle.net.aso.b;
public class Main {
public static void main(String[] args) throws Exception {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
try {
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog=new Blog();
blog.setId(3);
blog.setTitle("第三篇");
//blog.setAuthor(1);
mapper.addBlog(blog);
session.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
}2、删(delete)：
<delete id="deleteBlog" parameterType="Blog">
delete from t_blog where title=#{title}
</delete>public void deleteBlog(Blog blog);测试：
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession session = sqlSessionFactory.openSession();
try {
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog=new Blog();
//blog.setId(3);
blog.setTitle("第三篇");
//blog.setAuthor(1);
mapper.deleteBlog(blog);
session.commit();
} catch (Exception e) {
e.printStackTrace();
}3、改(update)：
public int updateEmp(Emp emp);<update id="updateEmp" parameterType="Emp" >
update emp set money=#{money} where id=#{id}
</update>4、查(select)：
五、复杂操作：
1、嵌套查询：一对一用association，一对多用collection：
例1：
public Blog selectBlog(int id);<resultMap type="Blog" id="blogres">
<id property="id" column="bid" ></id>
<result property="title" column="title"/>
<association property="author" javaType="Author">
<id property="id" column="aid"></id>
<result property="name" column="aname"/>
</association>
<collection property="posts" ofType="Post">
<id property="id" column="pid"/>
<result property="name" column="name"/>
</collection>
</resultMap>
<select id="selectBlog" parameterType="int" resultMap="blogres">
select b.id as bid,
b.title,
a.id as aid,
a.name as aname,
p.id as pid,
p.name
from t_blog b
left join author a on b.authorid = a.id
left join post p on p.bid = b.id where b.id = #{id}
</select>引用自定义的用resultMap。
测试：
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog b = mapper.selectBlog(1);
System.out.println(b);例2：
public class UserInfoVO implements Serializable {
private String userId;
private String userAccount;
private String userName;
private RoleInfo roleInfo;
}
public class RoleInfo implements Serializable {
private Integer roleId;
private String roleName;
private String roleCode;
private Integer roleType;
private List<ProvinceSchoolInfo> provinceSchoolQInfos = new ArrayList<>();
}
public class ProvinceSchoolInfo implements Serializable {
private List<ProvinceInfo> provinceInfos = new ArrayList<>();
}
public class ProvinceInfo implements Serializable {
private Long provinceId;
private String provinceName;
private List<CityInfo> cityInfos = new ArrayList<>();
}
public class CityInfo implements Serializable {
private Long cityId;
private String cityName;
private List<DistrictInfo> districtInfos = new ArrayList<>();
}
public class DistrictInfo implements Serializable {
private Long districtId;
private String districtName;
}对应的map:
<resultMap id="UserInfoVOMap" type="com.demo.business.vo.dbo.UserInfoVO">
<result column="USER_ID" jdbcType="VARCHAR" property="userId" />
<association property="roleInfo" javaType="com.demo.business.permission.vo.RoleInfo">
<result column="ROLE_ID" jdbcType="INTEGER" property="roleId" />
<collection property="provinceSchoolQInfos" ofType="com.demo.business.permission.vo.ProvinceSchoolInfo">
<collection property="provinceInfos" ofType="com.demo.business.permission.vo.ProvinceInfo">
<result column="PROVINCE_ID" jdbcType="BIGINT" property="provinceId" />
<result column="province_name" jdbcType="VARCHAR" property="provinceName" />
<collection property="cityInfos" ofType="com.demo.business.permission.vo.CityInfo">
<result column="CITY_ID" jdbcType="BIGINT" property="cityId" />
<result column="city_name" jdbcType="INTEGER" property="cityName" />
<collection property="districtInfos" ofType="com.demo.business.permission.vo.DistrictInfo">
<result column="DISTRICT_ID" jdbcType="BIGINT" property="districtId" />
<result column="district_name" jdbcType="VARCHAR" property="districtName" />
</collection>
</collection>
</collection>
</collection>
</association>
</resultMap>注意：在使用mybatis时，集合标签<collection>可以帮我们实现聚合功能，但是会对聚合到的数据进行自动去重，也即对collection标签内的整体进行去重，所以如果不想去重，需要加一个唯一标志性的字段，如我查询学生考试列表，
<collection property="exmas" ofType="com.demo.Exam">
<result column="examName" jdbcType="VARCHAR" property="examName" />
</collection>这样结果会去重，如语文、数学、英语，如果我希望查询学生历史考试情况（不需要按照考试科目名称去重）可以加个主键，将聚合目标字段+主键id字段封装为一个对象UID
<collection property="exmas" ofType="com.demo.Exam">
<id property="id" column="id"/>
<result column="examName" jdbcType="VARCHAR" property="examName" />
</collection>2、批量插入：注意foreach语句不需要括号:
<!--批量插入用户权限-->
<insert id="updateUserRoles" >
insert into userrole (rid,uid)
values
<foreach collection="rlist" item="item" index="index" separator=",">(#{item},#{uid})</foreach>
</insert>int insertUserChannel(@Param("uid") String uid,@Param("list") List<HashMap<String,String>> channelMap);<!--批量插入-->
<insert id="insertUserChannel">
insert into user_channel_relation ( relation_id, user_group_id, channel_id, enable_flag)
values
<foreach collection="list" item="item" index="index" separator=",">
(#{item.id},#{uid},#{item.channelId},#{item.flag})</foreach>
</insert>3、批量更新：注意需要在数据库连接处开启批量更新
&allowMultiQueries=true语法同批量插入
int updateUserRoles(@Param("uid")String uid, @Param("rlist")List<String> ridList);void updateUserBatch(@Param(value = "userList") List<User> userList, @Param(value = "tableName") String tableName);
<update id="updateUserBatch">
<foreach collection="userList" item="user" separator=";" index="index">
update ${tableName}
<set>
user_name = #{user.userName}
</set>
<where>
user_id = #{user.userId}
</where>
</foreach>
</update>4、in查询：注意foreach语句需要括号：
List<DeliveryRule> selectListByPositionCodeAndTag(@Param("positionCode") String positionCode, @Param("tags")Set<String> tags);<select id="selectListByPositionCodeAndTag" resultMap="BaseResultMap">
select
<include refid="User_Column_List"/>
from cia_delivery_rule
where resource_position_code = #{positionCode,jdbcType=VARCHAR}
and tag_value IN
<foreach collection="tags" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
and `status` = 10
and start_time < NOW() and end_time > NOW()
</select>5、模糊查询：
（1）基础写法：用$，而不用#
<?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.iflytek.dao.GoodsMapper">
<select id="getGoods" resultType="Goodsinfo" parameterType="Goodcategory">
select * from goodsinfo i
left join goodcategory c
on i.cid=c.id where c.title like '%${title}%'
and c.pid!=0
</select>
</mapper>（2）安全写法：实际应用中，上述的模糊匹配是有SQL注入风险的，需要like CONCAT(CONCAT('%',#{title}),'%')；且一般为了命中索引使用后模糊：
user_account like CONCAT(CONCAT(#{query.userAccount}),'%')六、动态sql:mybatis提供了丰富的标签库支持动态SQL，可以像写java代码那样写sql语句：
例1：
<select id="select" parameterType="Blog" resultType="Blog">
select * from t_blog where 1=1
<if test="id!=null">
and id=#{id}
</if>
</select>public List<Blog> select(Blog blog);测试：
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog=new Blog();
List<Blog> b = mapper.select(blog);
System.out.println(b);
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog=new Blog();
blog.setId(1);
List<Blog> b = mapper.select(blog);
System.out.println(b);
这个例子需要注意一下，判断的条件是id==null，所以Blog的id字段设的是Integer而不是int，因为基本类型默认是有初始值的，如int初始值为0,可是数据库中却可以存在id为0的数据，而引用类型初始值为null，因为数据库中不可能存在为null的字段，这样就好判断一点。
例2：根据角色id、省市区id（空或-1则为全部）查询重复用户：
<!--根据角色、区域查询重复用户-->
<select id="getByAreaAndRole" resultMap="UserInfoVOMap">
select
t.USER_ID USER_ID,
t.ROLE_ID ROLE_ID,
t.PROVINCE_ID PROVINCE_ID,
t.CITY_ID CITY_ID,
t.DISTRICT_ID DISTRICT_ID,
p.province_name province_name,
c.city_name city_name,
d.district_name district_name
from T_USER_ROLE_AREA t
LEFT JOIN dw.dw_province_dim p ON t.PROVINCE_ID = p.province_id
LEFT JOIN dw.dw_city_dim c ON t.CITY_ID = c.city_id
LEFT JOIN dw.dw_district_dim d ON t.DISTRICT_ID = d.district_id
where t.is_delete = 0
and t.ROLE_ID = #{roleId}
and t.USER_ID!=#{userId}
<if test="provinceInfos.size > 0">
and
<foreach collection="provinceInfos" item="provinceInfo" open="(" close=")" separator="or">
<choose>
<when test="provinceInfo.provinceId != null ">
(t.PROVINCE_ID = #{provinceInfo.provinceId} or t.PROVINCE_ID = -1 )
</when>
<otherwise> 1 = 1 </otherwise>
</choose>
<if test="provinceInfo.cityInfos.size > 0">
and
<foreach collection="provinceInfo.cityInfos" item="cityInfo" open="(" close=")" separator="or">
<choose>
<when test="cityInfo.cityId != null">
(t.CITY_ID = #{cityInfo.cityId} or t.CITY_ID = -1 or t.CITY_ID is null)
</when>
<otherwise>1=1</otherwise>
</choose>
<if test="cityInfo.districtInfos.size > 0">
and
<foreach collection="cityInfo.districtInfos" item="districtInfo" open="(" close=")" separator="or">
<choose>
<when test="districtInfo.districtId !=null">
(t.DISTRICT_ID = #{districtInfo.districtId} or t.DISTRICT_ID = -1 or t.DISTRICT_ID is null)
</when>
<otherwise>1=1</otherwise>
</choose>
</foreach>
</if>
</foreach>
</if>
</foreach>
</if>
</select>本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
