MyBatis语法（一）总述

w_t_y_y

已于 2024-05-26 13:01:30 修改

阅读量701

点赞数

分类专栏： # ORM框架Mybatis 文章标签： xml java 开发语言

于 2017-12-28 11:14:30 首次发布

本文链接：https://blog.youkuaiyun.com/w_t_y_y/article/details/78919986

版权

ORM框架Mybatis 专栏收录该内容

38 篇文章

订阅专栏

一、关键字符：

1、大于小于

< ，<=，>，>=等符号，容易引起xml格式的错误，这样会导致后台将xml字符串转换为xml文档时报错，从而导致程序错误。这样的问题在iBatiS中或者自定义的xml处理sql的程序中需要做如下替换来处理：

2、#和$

2.1、#

#相当于对数据加上双引号；在使用 #{}时，MyBatis 会将 SQL 中的 #{}替换成“?”，配合 PreparedStatement 的 set 方法赋值，这样可以有效的防止 SQL 注入，保证程序的运行安全。

2.2、$

$相当于直接显示数据，不会加上引号。$有SQL注入的风险，如果程序中必须使用$，如order by xxx ${orderType}、select from ${tableName}等，注意对参数做校验防止SQL注入

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.xxx.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、mapper.java加注解

dao.java的接口上给参数加@Param注解，sql中不需要再写parameterType，直接使用即可。如：

public interface UserMapper{
 int insert(@Param("user") User user,@Param("userId") String userId);
}

<insert id="insert">
 insert into user values(#{user.userName},#{userId})
</insert>

2、xml指定类型

在dao.java的接口参数上不加@Param注解，sql中需要写parameterType指明类型。

三、基本操作：

增删改可以不返回结果，查询必须返回结果，也即必须有resultType或者resultMap。

1、增(insert)：

public interface BlogMapper {

	public void addBlog(Blog blog);
	
}

<?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.xxx.dao.BlogMapper">

<insert id="addBlog" parameterType="Blog">
insert into t_blog
  (id, title)
values
  (#{id}, #{title})
</insert>
</mapper>

测试：

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 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)：

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}),'%')

四、批量操作

1、批量插入：

注意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>

2、批量更新：

注意需要在数据库连接处开启批量更新

&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},
                age = #{user.age}
            </set>
            <where>
                user_id = #{user.userId}
            </where>
        </foreach>
    </update>

3、批量删除：

<delete id="batchDelete" parameterType="java.util.List">
    DELETE FROM table_name WHERE column_id IN
    <foreach collection="list" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</delete>

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>

五、嵌套查询：

1、语法

（1）association与collection：

xml映射Domain.java时， Object 一对一用association表示；List<Object>一对多用collection表示；

（2）collection去重问题

在使用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、demo

例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>

六、demo

动态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>