Mybatis

MyBatis 本是apache的一个开源项目iBatis 2010年这个项目由apache software foundation 迁移到了google code，并且改名为MyBatis 。2013年11月迁移到Github。iBATIS一词来源于“internet”和“abatis”的组合，是一个基于Java的持久层框架。iBATIS提供的持久层框架包括SQL Maps和Data Access Objects（DAOs）

MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。

官方地址

简介使用

<!--全局配置文件 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>  
<environments default="development">    
  <environment id="development">      
    <transactionManager type="JDBC"/>      
    <dataSource type="POOLED">        
      <property name="driver" value="${driver}"/>        
      <property name="url" value="${url}"/>        
      <property name="username" value="${username}"/>        
      <property name="password" value="${password}"/>      
    </dataSource>    
  </environment>  
</environments> 
  
<mappers>    
  <mapper resource="org/mybatis/example/BlogMapper.xml"/>  
  	<!-- 对应配置文件路径 * -->
</mappers> 
</configuration>

<!-- * org/mybatis/example/BlogMapper.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"> 
<mapper namespace="org.mybatis.example.dao.BlogMapper">  
<!-- 对应接口全类名位置 ## -->

  <!-- 对应接口方法 ### -->
  <select id="selectBlog" resultType="org.mybatis.example.bean.Blog">select * from Blog where id = #{id}</select> 

</mapper>

//## org.mybatis.example.dao.BlogMapper
public interface BlogMapper{
  	// 对应mapper中方法 ###
  	public Blog selectBlog(Integer id);
}

Mybatis底层与数据库交互是使用SqlSession完成的，SqlSession非线程安全，每次使用应当获取新对象，mapper接口的实现由mybatis生成一个代理对象。

public void test(){
  
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession session = sqlSessionFactory.openSession();
   try {  
           BlogMapper mapper = session.getMapper(BlogMapper.class);  
           Blog blog = mapper.selectBlog(1);  
   } finally {  session.close(); }    
}

全局配置文件

properties标签

<?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>  
  <properties resource="db.properties" url="网络路径/本地路径"></properties>
  <!--resource:类路径   url：网络路径/本地路径 -->
  <environments default="development">    
  <environment id="development">      
    <transactionManager type="JDBC"/>      
    <dataSource type="POOLED">        
      <property name="driver" value="${jdbc.driver}"/>        
      <property name="url" value="${jdbc.url}"/>        
      <property name="username" value="${jdbc.username}"/>        
      <property name="password" value="${jdbc.password}"/>      
    </dataSource>    
  </environment>  
</environments> 
</configuration>

#db.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mydql://localhost:3306/db
jdbc.username=username
jdbc.password=password

settings标签

<?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>  
  <settings>
    <setting name="mapUnderscoreToCamelCaseEnables " value="true" />
    <!-- 驼峰命名适配  -->
    <setting name="jdbcTypeForNull" value="null"/>
    <!-- 空值映射为null -->
    <setting name="lazyLoadingEnabled" value="true"/>
    <!-- 懒加载 -->
    <setting name="aggressiveLazyLoading" value="false"/>
    <!-- 按需加载 -->
  </settings>
</configuration>

typeAliases标签

<?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>
 	<typeAlias type="org.mybatis.example.bean.Blog" alias="blog"></typeAlias>
   <!-- mapper中就不用写全类名了，不写alias即默认为类名-->
  <package name="org.mybatis.example.bean"/>
   <!--当前包及其子包自动别名-->
  </typeAliases>
</configuration>

<?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="org.mybatis.example.dao.BlogMapper">  
  <!-- blog 别名使用-->
  <select id="selectBlog" resultType="blog">select * from Blog where id = #{id}</select> 
</mapper>

//通过@Alias注解使用别名
//批量扫描注解优先
@Alias("blog")
public class Blog{
  private Integer id;
  ...
}

Alias(默认别名)	Mapped Type
_long	long
_short	short
_int	int
_integer	int
_double	double
_float	float
_boolean	boolean
string	String
byte	Byte
long	Long
short	Short
int	Integer
integer	Integer
double	Double
float	Float
boolean	Boolean
date	Date
decimal	BigDecimal
bigdecimal	BigDecimal
map	Map

plugins插件

在四大对象执行前后进行拦截，原理为动态代理。

Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)
ParameterHandler (getParameterObject, setParameters)
ResultSetHandler (handleResultSets, handleOutputParameters)
StatementHandler (prepare, parameterize, batch, update, query)

environments标签

<?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>  
  <environments default="development"> <!--指定默认使用环境来快速切换环境-->
    
    	<environment id="env">
    	  <transactionManager type="JDBC"/>	<!-- 事务管理器  type: JDBC|MANAGED 自定义 -->
        <dataSource type="POOLED">	<!-- 数据源配置 type：UNPOOLED|POOLED|JNDI 自定义-->
          <property name="driver" value="${oracle.driver}"/>        
          <property name="url" value="${oracle.url}"/>        
          <property name="username" value="${oracle.username}"/>        
          <property name="password" value="${oracle.password}"/>  
        </dataSource>
    	</environment>
    
      <environment id="development">      
        <transactionManager type="JDBC"/>      
        <dataSource type="POOLED">        
          <property name="driver" value="${jdbc.driver}"/>        
          <property name="url" value="${jdbc.url}"/>        
          <property name="username" value="${jdbc.username}"/>        
          <property name="password" value="${jdbc.password}"/>      
        </dataSource>    
      </environment>  
    
</environments> 
</configuration>

databaseIdProvider标签

<?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>  
  <environments default="env">
	<databaseIdProvider type="DB_VENDOR">
  	<property name="MySQL" value="mysql"/>
    <property name="Oracle" value="oracle"/>
    <property name="SQL Server" value="sqlserver"/>
  </databaseIdProvider>
</configuration>


<?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="org.mybatis.example.dao.BlogMapper">  
  <!-- 发送带oracle的查询语句 -->
  <select id="selectBlog" resultType="org.mybatis.example.bean.Blog">
          select * from Blog where id = #{id}</select> 
  <select id="selectBlog" resultType="org.mybatis.example.bean.Blog" 
          databsesId="oracle">select * from Blog where id = #{id}</select> 
  <select id="selectBlog" resultType="org.mybatis.example.bean.Blog" 
          databsesId="mysql">select * from Blog where id = #{id}</select> 
</mapper>

mapper标签

<!--全局配置文件 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>  
  <mappers>    
    <mapper url=""></mapper>
    <!-- 磁盘路径 -->
    <mapper resource="org/mybatis/example/BlogMapper.xml"/>  
    <!-- 类路径 -->
    <mapper class="org.mybatis.example.dao.BlogMapperAnnotation"/>
    <!--1. mapper和接口 同名同路径-->
    <!--2. 添加注解方法的接口-->
    <package name="org.mybatis.example.dao"/>
    <!--  批量扫描 xml 接口 同包 -->
  </mappers> 
</configuration>

//org.mybatis.example.dao.BlogMapperAnnotation
public interface BlogMapperAnnotation{
  	// 对应mapper中方法 ###
  	@Select("select * from Blog where id = #{id}")
  	public Blog selectBlog(Integer id);
}

映射文件

CRUD

//## org.mybatis.example.dao.BlogMapper
public interface BlogMapper{
  	// 对应mapper中方法 ###
  	public Blog selectBlog(Integer id);
  	public boolean insertdBlog(Blog blog);
  	public Integer updateBlog(Blog blog);
  	public boolean deleteBlog(Integer id);
  //自动返回 Integer 、Long、Boolean 类型
}

<!-- * org/mybatis/example/BlogMapper.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"> 
<mapper namespace="org.mybatis.example.dao.BlogMapper">  

  <select id="selectBlog" resultType="org.mybatis.example.bean.Blog">
          select * from Blog where id = #{id}
    </select> 

  <insert id="insertdBlog" parameterType ="org.mybatis.example.bean.Blog">
      insert into Blog...values(#{属性名}，#{属性名})
  </insert>

  <update id="updateBlog" parameterType ="org.mybatis.example.bean.Blog">
      update Blog set ... 字段 = #{属性名} where 字段 = #{属性名}
    </update>

  <delete id="deleteBlog">
      delete from Blog where id = #{id}
    </delete>

</mapper>

自增主键

<!-- * org/mybatis/example/BlogMapper.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"> 
<mapper namespace="org.mybatis.example.dao.BlogMapper">  
  
    <!-- mysql 等支持 自增主键 数据库-->
  <insert id="insertdBlog" parameterType ="org.mybatis.example.bean.Blog"
          useGeneratedKeys="true" keyProperty="#{id}" databaseId="mysql">
    <!-- 使用自增主键策略 插入数据库操作完成后原插入对象的id属性将被赋值-->
      insert into Blog...values(#{属性名}，#{属性名})
  </insert>

    <!-- Oracle 从序列中获取 111-->
  <insert id="insertdBlog" parameterType ="org.mybatis.example.bean.Blog"
          databaseId="oracle">
    <selectKey KeyProperty="#{id}" order="BEFORE" resultTyoe"Integer">
      <!-- 使用查询序列 插入数据库操作完成后原插入对象的id属性将被赋值-->
      select Blog_sql.nextval from dual
    </selectKey>
      insert into Blog...values(#{id},#{属性名}，#{属性名})
  </insert>

      <!-- Oracle 从序列中获取 222 多条问题 -->
  <insert id="insertdBlog" parameterType ="org.mybatis.example.bean.Blog"
          databaseId="oracle">
      <selectKey KeyProperty="#{id}" order="AFTER" resultTyoe"Integer">
      <!-- 使用查询序列 插入数据库操作完成后原插入对象的id属性将被赋值-->
      select Blog_sql.currval from dual
    </selectKey>
      insert into Blog...values(Blog_sql.nextval,#{属性名}，#{属性名})
  </insert>
  
</mapper>

参数处理

多参数时，参数会封装成map，可以使用注解指定参数的关键字(key)。

//## org.mybatis.example.dao.BlogMapper
public interface BlogMapper{
  	// 对应mapper中方法 ###
  	public Blog selectBlogById(Integer id);
		public Blog selectBlogByIdAndName(@Param("id")Integer id,@Param("name")String name);
 		public Blog selectBlogByPojo(Blog blog);
 		public Blog selectBlogByMap(Map<String,Object> map);
		public Blog selectBlogByCollection(List<Integer> list);
}

Page{
  int index；
  int size;
  List<Blog> list;
}

<!-- * org/mybatis/example/BlogMapper.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"> 
<mapper namespace="org.mybatis.example.dao.BlogMapper">

  <!-- 单个参数   -->
  <!-- 当只有单个参数时，不会有任何处理 #{任意填写} 都可以取出参数-->
  <select id="selectBlogById" resultType="org.mybatis.example.bean.Blog">
          select * from Blog where id = #{id}
  </select> 

  <!-- 多个参数 -->
  <!-- 未指定参数名 -->
  <!-- 使用多个参数时，入参会被处理，参数会被封装为一个map-->
  <!-- 取值所用的key为 [ param1 ... paramN  0... n ]-->  
  <select id="selectBlogByIdAndName" resultType="org.mybatis.example.bean.Blog">
          select * from Blog where id = #{param1} and name = #{param2}
  </select> 
  <!-- 使用@Param（""） 注解指明接口中的参数后可以直接取用-->
  <!-- 原取参数规则仍旧适用 -->
  <select id="selectBlogByIdAndName" resultType="org.mybatis.example.bean.Blog">
          select * from Blog where id = #{id} and name = #{name}
  </select> 

   <!-- 传入pojo --> 
   <!-- 直接取用即可 -->
   <!-- 多参数包含对象时 param2.id 或注解后  blog.id -->
  <select id="selectBlogByPojo" resultType="org.mybatis.example.bean.Blog">
          select * from Blog where id = #{id} and name = #{name}
  </select> 

  <!-- 传入 map -->
  <!-- 直接取用 按照存入map时的key 即取用的 key 和 存入map时 key 相同 -->
  <select id="selectBlogByMap" resultType="org.mybatis.example.bean.Blog">
          select * from Blog where id = #{id} and name = #{name}
  </select> 

  <!--  传入集合类型  -->
  <!-- 取出第一个参数 集合类型取参数使用 collection[0] 数组类型取参数使用 list[0] -->
  <select id="selectBlogByMap" resultType="org.mybatis.example.bean.Blog">
          select * from Blog where id = #{list[0]} 
  </select> 
</mapper>

$与#区别

<?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="org.mybatis.example.dao.BlogMapper">  

  <select id="selectBlog" resultType="org.mybatis.example.bean.Blog">
    select * from Blog where id = #{id} and name = ${name}</select>
    <!-- 执行Sql：select * from Blog where id = ? and name = blog_01 -->
    <!-- #{} 采用预编译形式 可以防止sql注入发生 ${} 采用字符串拼串方式 可能被注入 -->
    select * from ${year}_Blog where id =#{id}	//分表
    select * from Blog order by ${name} ${order} where id = #{id} //排序
</mapper>

#{} 取值时可以对参数进行限定javaType、jdbcType、mode、numbericScale、resultMap、typeHandler、jdbcTypeName

<!-- 部分数据库在字段为null 可能无法识别mybatis传入的null值 -->
<insert id="insertdBlog" parameterType ="org.mybatis.example.bean.Blog">
  	insert into Blog values(#{id}，#{name,jdbcType=Null})
  <!-- name 为空 Oracle 数据库正常 默认映射为 jdbcType.OTHER -->
</insert>

返回值处理

public interface BlogMapper{
  	public List<Blog> selectBlogByName(String name);
		public Map<Stirng,Object> selectBlogMap(Integer id);
 		@MapKey("id")
		public Map<Stirng,Blog> selectBlogMaps(String name);
}

<?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="org.mybatis.example.dao.BlogMapper">  

  <select id="selectBlogByName" resultType="org.mybatis.example.bean.Blog">
    <!--  返回为列表 返回类型为集合元素类型 Blog-->
    select * from Blog where name like #{name}
  </select>

  <select id="selectBlogMap" resultType="map">
    <!--  返回值为map 返回类型为 map -->
    select * from Blog where id = #{id}
  </select>

  <select id="selectBlogByName" resultType="org.mybatis.example.bean.Blog">
    <!--  返回为map<String,Blog> 返回类型为集合元素类型 Blog-->
    <!-- 接口使用注解修饰 @MapKey("id") 标注map的key使用哪个字段 -->
    select * from Blog where name like #{name}
  </select>

</mapper>

resultMap

自定义结果集

<!-- * org/mybatis/example/BlogMapper.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"> 
<mapper namespace="org.mybatis.example.dao.BlogMapper">  

  <!--自定义结果规则 resultMap -->
  <resultMap type="org.mybatis.example.bean.Blog" id="CustomizeBlog">
  	<id column="id" property="id"/>
    <result column="name" property="name"/>
    <result colume="regist_time" property="reistTime"/>
  </resultMap>
  
  <select id="selectBlog" resultMap="CustomizeBlog">
          select * from Blog where id = #{id}
  </select> 
</mapper>

级联查询

<!-- * org/mybatis/example/BlogMapper.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"> 
<mapper namespace="org.mybatis.example.dao.BlogMapper">  

  <!--级联查 属性 定义结果规则 resultMap -->
  <resultMap type="org.mybatis.example.bean.Blog" id="CustomizeBlog">
  	<id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="group_id" property="group.id">
    <result column="group_name" property="group.name">
  </resultMap>
  
  <select id="selectBlog" resultMap="CustomizeBlog">
          select * from Blog b,Group g where b.id = g.id and b.id = #{id}
  </select> 
</mapper>

association

<!-- * org/mybatis/example/BlogMapper.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"> 
<mapper namespace="org.mybatis.example.dao.BlogMapper">  

  <!--级联查 联合 定义结果规则 resultMap -->
  <resultMap type="org.mybatis.example.bean.Blog" id="CustomizeBlog">
  	<id column="id" property="id"/>
    <result column="name" property="name"/>
      <association property="org.mybatis.example.bean.Group">
        <id column "group_id" property="id"/>
        <result column="group_name" property="name">
      </association>
  </resultMap>
  
  <select id="selectBlog" resultMap="CustomizeBlog">
          select (distinct)* from Blog b,Group g where b.id = g.id and b.id = #{id}
  </select> 
</mapper>

分步查询

<!-- * org/mybatis/example/BlogMapper.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"> 
<mapper namespace="org.mybatis.example.dao.BlogMapper">  

  <!-- 联合 分步查询 定义结果规则 resultMap -->
  <resultMap type="org.mybatis.example.bean.Blog" id="CustomizeBlog">
  	<id column="id" property="id"/>
    <result column="name" property="name"/>
      <association property="group" 
            select="org.mybatis.example.dao.GroupMapper.getGroupById"
            column="group_id">
      </association>
  </resultMap>
  
  <select id="selectBlog" resultMap="CustomizeBlog">
          select * from Blog
  </select> 
</mapper>

懒加载

<!-- 分步查询的条件下 在全局配置文件中开启懒加载 按需加载等功能 即可-->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 懒加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
<!-- 按需加载 -->

collection

<?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="org.mybatis.example.dao.GroupMapper">  

    <resultMap type="org.mybatis.example.bean.Group" id="CustomizeGroup">
  	<id column="group_id" property="id"/>
    <result column="group_name" property="name"/>
      <collection property="blogs" ofType="org.mybatis.example.bean.Blog">
      	<id column="id" property="id"/>
        <result column="name" property="name"/>
      </collection>
  </resultMap>
<!-- 一个组中包含多个Blog -->
  <select id="selectBlog" resultMap="CustomizeGroup">
          select * from Group g left join Blog b on g.id = b.id where g.id = #{id} 
  </select> 
</mapper>

分步查询

<?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="org.mybatis.example.dao.GroupMapper">  

    <resultMap type="org.mybatis.example.bean.Group" id="CustomizeGroup">
  	<id column="group_id" property="id"/>
    <result column="group_name" property="name"/>
      <collection property="blogs" ofType="org.mybatis.example.bean.Blog"
                  select="org.mybatis.example.dao.GroupMapper.selectBlogByGroupId"
                  column="id">
      </collection>
  </resultMap>
<!-- 一个组中包含多个Blog -->
  <select id="selectBlog" resultMap="CustomizeGroup">
          select * from Group
  </select> 
</mapper>

懒加载

<!-- 同上一个示例 -->
<!-- 分步查询的条件下 在全局配置文件中开启懒加载 按需加载等功能 即可-->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 懒加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
<!-- 按需加载 -->

传递多参手动懒加载

<collection property="blogs" ofType="org.mybatis.example.bean.Blog"
            select="org.mybatis.example.dao.GroupMapper.selectBlogByGroupId"
            column="{group_id = id}" fetchType="lazy">
  <!-- column="{key1=column1,key2=column2} fetchType="lazy|eager"-->
</collection>

discriminator

<!-- * org/mybatis/example/BlogMapper.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"> 
<mapper namespace="org.mybatis.example.dao.BlogMapper">  

  <!-- 联合 分步查询 定义结果规则 resultMap -->
  <resultMap type="org.mybatis.example.bean.Blog" id="CustomizeBlog">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    <result column="sex" property="sex"/>
    <discriminator javaType="sex" column="string">
        <case value="0" resultType="">
                <association property="group" 
                      select="org.mybatis.example.dao.GroupMapper.getGroupById"
                      column="group_id">
               </association>
        </case>
      
        <case value="1" resultType="org.mybatis.example.bean.Blog">
           <result column="sex" property="name"/>
        </case>
    </discriminator>

  </resultMap>
  
  <select id="selectBlog" resultMap="CustomizeBlog">
          select * from Blog
  </select> 
</mapper>

动态SQL

IF标签

public class User{
  private int id;
  private String name;
  private String sex;
  private String email;
  private Dept dept;
}

public class UserMapper{
  public List<User> getUsersbyIf(User user);
}

<!-- * org/mybatis/example/BlogMapper.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"> 
<mapper namespace="com.wang.test.dao.BlogMapper">  

 <select id="getUsersbyIf" resultMap="com.wang.test.bean.User">
          select * from user where<!--  1=1 -->
   		<if test="id!=null">
   				id=#{id}<!--  and id=#{id} -->
   		</if>
			<if test="sex != null and sex != '' ">
   				and sex=#{sex}
			</if>
   		<if test="email!=null and email.trim()!='' ">
   				and email=#{email}
			</if>
  </select> 
</mapper>
<!--  当没有id时 sql语句会出现问题 -->

WHERE标签

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

 <select id="getUsersbyIf" resultMap="com.wang.test.bean.User">
          select * from user
           <where>
              <if test="id!=null">
                  id=#{id}	<!-- id=#{id} and -->
              </if>
              <if test="sex != null and sex != '' ">
                  and sex=#{sex}	<!-- id=#{id} and-->
              </if>
              <if test="email!=null and email.trim()!='' ">
                  and email=#{email}	<!-- email=#{email} -->
              </if>
           </where>
  </select> 
</mapper>
  <!--  and后置不好使  -->

TRIM标签

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

 <select id="getUsersbyIf" resultMap="com.wang.test.bean.User">
          select * from user
           <trim prefix="where" prefixOverrides="xxx" suffix="" suffixOverrides="and">
             <!-- 添加 where 前缀 覆盖xxx 添加后缀 覆盖and -->
              <if test="id!=null">
                 xxx id=#{id} and
              </if>
              <if test="sex != null and sex != '' ">
                   sex=#{sex} and
              </if>
              <if test="email!=null and email.trim()!='' ">
                  email=#{email}
              </if>
           </trim>
  </select> 
</mapper>

posted @ 2018-09-15 13:54 图图突tu 阅读(202) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

图图突tu

🎬You know some birds are not meant to be caged
  🎈Their feathers are just too bright.
  💌dkni9ht@163.com
  🖥️https://github.com/wan9huan

Mybatis

Mybatis

简介使用

全局配置文件

properties标签

settings标签

typeAliases标签

plugins插件

environments标签

databaseIdProvider标签

mapper标签

映射文件

CRUD

自增主键

参数处理

$与#区别

返回值处理

resultMap

association

collection

discriminator

动态SQL

IF标签

WHERE标签

TRIM标签

公告

图图突tu

🎬You know some birds are not meant to be caged 🎈Their feathers are just too bright. 💌dkni9ht@163.com 🖥️https://github.com/wan9huan

Mybatis

Mybatis

简介使用

全局配置文件

properties标签

settings标签

typeAliases标签

plugins插件

environments标签

databaseIdProvider标签

mapper标签

映射文件

CRUD

自增主键

参数处理

$与#区别

返回值处理

resultMap

association

collection

discriminator

动态SQL

IF标签

WHERE标签

TRIM标签

公告

🎬You know some birds are not meant to be caged
🎈Their feathers are just too bright.
💌dkni9ht@163.com
🖥️https://github.com/wan9huan