MyBatis语法

秒速五厘米 2022-06-02 00:46 113阅读 0赞

一、简介

MyBatis 是支持普通 SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis 消除了几乎所有的JDBC代码和参数的手工设置以及结果集的检索。

MyBatis 使用简单的 XML或注解用于配置和原始映射，将接口和 Java 的POJOs（Plain Old Java Objects，普通的 Java对象）映射成数据库中的记录。

Mybatis框架的组成架构图如下：

![20171228105507854][]

如图所见我们把Mybatis的功能架构分为四层：  
1）API接口层：提供给外部使用的接口API，开发人员通过这些本地API来操纵数据库。接口层一接收到调用请求就会调用数据处理层来完成具体的数据处理。  
  
2）数据处理层：负责具体的SQL查找、SQL解析、SQL执行和执行结果映射处理等。它主要的目的是根据调用的请求完成一次数据库操作。  
  
3）基础支撑层：负责最基础的功能支撑，包括连接管理、事务管理、配置加载和缓存处理，这些都是共用的东西，将他们抽取出来作为最基础的组件。为上层的数据处理层提供最基础的支撑。  
  
4）引导层：基于XML配置方式还是基于Java API方式。

二、关键字符：

1、我们需要通过xml格式处理sql语句时，经常会用到< ，<=，>，>=等符号，但是很容易引起xml格式的错误，这样会导致后台将xml字符串转换为xml文档时报错，从而导致程序错误。

这样的问题在iBatiS中或者自定义的xml处理sql的程序中经常需要我们来处理。其实很简单，我们只需作如下替换即可避免上述的错误：

![SouthEast][]

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指明类型。

四、集成方法：以spring+mybatis实例，下面在maven下搭建一个java工程：

1、配置文件：

（1）pom.xml添加依赖

<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.iflytek</groupId>
    	<artifactId>mybatis</artifactId>
    	<version>0.0.1-SNAPSHOT</version>
    
    	<dependencies>
    		
    		<dependency>
    			<groupId>org.mybatis</groupId>
    			<artifactId>mybatis</artifactId>
    			<version>3.4.5</version>
    		</dependency>
    		
    		
    		<dependency>
    			<groupId>log4j</groupId>
    			<artifactId>log4j</artifactId>
    			<version>1.2.17</version>
    		</dependency>
    
    	</dependencies>
    
    </project>

（2）resources下创建两个配置文件：

log4j.properties：日志文件

# Global logging configuration
    log4j.rootLogger=ERROR, stdout
    # MyBatis logging configuration...
    log4j.logger.com.iflytek=TRACE
    # Console output...
    log4j.appender.stdout=org.apache.log4j.ConsoleAppender
    log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
    log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

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>
    <typeAliases>
    	
    		<package name="com.iflytek.domain"/>
    	</typeAliases>
      <environments default="development">
        <environment id="development">
          <transactionManager type="JDBC"/>
          <dataSource type="POOLED">
            <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
            <property name="url" value="jdbc:oracle:thin:@172.16.130.35:1521:orcl"/>
            <property name="username" value="wtyy1"/>
            <property name="password" value="wtyy1"/>
          </dataSource>
        </environment>
      </environments>
      <mappers>
        <package name="com.iflytek.dao"/>
      </mappers>
    </configuration>

2、实体类：（省略set和get方法）

package com.iflytek.domain;
    
    import java.util.List;
    
    public class Blog {
    
    	private Integer id;
    	
    	private String title;
    	private int authorid;
    	private Author author;
    	
    	private List<Post> posts;
    	}

package com.iflytek.domain;
    
    public class Author {
    	
    	private int id;
    	
    	private String name;
    }

package com.iflytek.domain;
    
    public class Post {
    private int bid;
    	private int id;
    	private String name;
    }

public class Emp implements Serializable{
    
    	private Integer id;
    	private String name;
    	private int age;
    	private int money;
    }

public class Goodcategory{
      private String id;
    private String title;
    private String pid;
    private String href;
    private String isnav;
    private String icon;
    private String remarks;
    private String del_flag;
    }

public class Goodsinfo{
      private String id;
    private String title;
    private String cid;
    private String issale;
    private String price;
    private String pic;
    private String descr;
    private String goodstag;
    private String storage;
    private String del_flag;
    }

五、基础持久化操作：

1、增(insert)：

下面在com.iflytek.dao下创建两个文件来进行数据库操作：

![20180103102446800][]

<?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:

注意：在使用mybatis时，集合标签`<collection>`可以帮我们实现聚合功能，但是会对聚合到的数据进行自动去重，也即对`collection标签内的整体进行去重，所以如果不想去重，需要加一个唯一标志性的字段，如我查询学生考试列表，`

这样结果会去重，如语文、数学、英语，如果我希望查询学生历史考试情况（不需要按照考试科目名称去重）可以加个主键，将聚合目标字段+主键id字段封装为一个对象`UID`

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、批量更新：同批量插入

int updateUserRoles(@Param("uid")String uid, @Param("rlist")List<String> ridList);

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

![20180103110717726][]

BlogMapper mapper = session.getMapper(BlogMapper.class);
    		Blog blog=new Blog();
    		blog.setId(1);
    		List<Blog> b = mapper.select(blog);
    		System.out.println(b);

![20180103110810752][]

这个例子需要注意一下，判断的条件是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>

[20171228105507854]: /images/20220602/f35de74444a04b28b542c1ca66dbc696.png
[SouthEast]: /images/20220602/08b53fe8bfa24390b4a690f64eee9f21.png
[20180103102446800]: /images/20220602/10827522d3f844c1bb059130a15e7508.png
[20180103110717726]: /images/20220602/f788fea8a7d14bff9ab199dc754edc74.png
[20180103110810752]: /images/20220602/864be337c2e54ccea6b9466563d9cc6d.png