MyBatis

1、搭建框架

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" version="2.5">
  <display-name></display-name>
  
  	<welcome-file-list>
		<welcome-file>index.html</welcome-file>
	</welcome-file-list>
	
	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>classpath:spring/applicationContext-*.xml</param-value>
	</context-param>
	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>
  
  	<!-- 解决post乱码 -->
	<filter>
		<filter-name>CharacterEncodingFilter</filter-name>
		<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
		<init-param>
			<param-name>encoding</param-name>
			<param-value>utf-8</param-value>
		</init-param>
	</filter>
	<filter-mapping>
		<filter-name>CharacterEncodingFilter</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>
  
  
  <!-- springmvc的前端控制器 -->
	<servlet>
		<servlet-name>Springmvc</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<!-- contextConfigLocation不是必须的， 如果不配置contextConfigLocation， springmvc的配置文件默认在：WEB-INF/servlet的name+"-servlet.xml" -->
		<init-param>
			<param-name>contextConfigLocation</param-name>
			<param-value>classpath:spring/springmvc.xml</param-value>
		</init-param>
		<load-on-startup>1</load-on-startup>
	</servlet>
	<servlet-mapping>
		<servlet-name>Springmvc</servlet-name>
		<!-- 伪静态化 -->
		<url-pattern>*.html</url-pattern>
	</servlet-mapping>
  
   
   
  
  
</web-app>

Springmvc

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:context="http://www.springframework.org/schema/context"
	xmlns:dubbo="http://code.alibabatech.com/schema/dubbo"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd		
        http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.2.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd">
	
	<context:component-scan base-package="com.cb.web" />
	<mvc:annotation-driven />
	<bean
		class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="prefix" value="/WEB-INF/jsp/" />
		<property name="suffix" value=".jsp" />
	</bean>
	
</beans>

spring-service

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:dubbo="http://code.alibabatech.com/schema/dubbo"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd
	http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd
	http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.2.xsd">

	<!-- 配置包扫描器 -->
	<context:component-scan base-package="com.cb.service"/>
	 
</beans>

spring-transaction

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd
	http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd
	http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.2.xsd">
	<!-- 事务管理器 -->
	<bean id="transactionManager"
		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
		<!-- 数据源 -->
		<property name="dataSource" ref="dataSource" />
	</bean>
	<!-- 通知 -->
	<tx:advice id="txAdvice" transaction-manager="transactionManager">
		<tx:attributes>
			<!-- 传播行为 -->
			<tx:method name="save*" propagation="REQUIRED" />
			<tx:method name="insert*" propagation="REQUIRED" />
			<tx:method name="add*" propagation="REQUIRED" />
			<tx:method name="create*" propagation="REQUIRED" />
			<tx:method name="delete*" propagation="REQUIRED" />
			<tx:method name="update*" propagation="REQUIRED" />
			<tx:method name="find*" propagation="SUPPORTS" read-only="true" />
			<tx:method name="select*" propagation="SUPPORTS" read-only="true" />
			<tx:method name="get*" propagation="SUPPORTS" read-only="true" />
		</tx:attributes>
	</tx:advice>
	<!-- 切面 -->
	<aop:config>
		<aop:advisor advice-ref="txAdvice"
			pointcut="execution(* com.cb.service..*.*(..))" />
	</aop:config>
</beans>

spring-mapper

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
	xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.2.xsd
	http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.2.xsd
	http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.2.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.2.xsd
	http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-4.2.xsd">

	<!-- 数据库连接池 -->
	<!-- 加载配置文件 -->
	<context:property-placeholder location="classpath:conf/db.properties" />
	<!-- 数据库连接池 -->
	<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
		destroy-method="close">
		<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
		<property name="driverClassName" value="${jdbc.driver}" />
		<property name="maxActive" value="10" />
		<property name="minIdle" value="5" />
	</bean>
	<!-- 让spring管理sqlsessionfactory 使用mybatis和spring整合包中的 -->
	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<!-- 数据库连接池 -->
		<property name="dataSource" ref="dataSource" />
		<!-- 加载mybatis的全局配置文件 -->
		<property name="configLocation" value="classpath:mybatis/SqlMapConfig.xml" />
	</bean>
	<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
		<property name="basePackage" value="com.cb.mapper" />
	</bean>
</beans>

MyBatis需要一个SqlMapConfig.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>
 

</configuration>

2、生成Mapper接口和Mapper配置文件（接口和配置文件中的方法名要一一对应），以及POJO

package com.cb.mapper;

import com.cb.pojo.Student;

public interface StudentMapper {
    int deleteByPrimaryKey(Integer stuid);

    int insert(Student record);

    int insertSelective(Student record);

    Student selectByPrimaryKey(Integer stuid);

    int updateByPrimaryKeySelective(Student record);

    int updateByPrimaryKey(Student record);
}

//===========================================
<?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.cb.mapper.StudentMapper" >
  <resultMap id="BaseResultMap" type="com.cb.pojo.Student" >
    <id column="stuID" property="stuid" jdbcType="INTEGER" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="age" property="age" jdbcType="INTEGER" />
  </resultMap>
  <sql id="Base_Column_List" >
    stuID, name, age
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from student
    where stuID = #{stuid,jdbcType=INTEGER}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from student
    where stuID = #{stuid,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.cb.pojo.Student" >
    insert into student (stuID, name, age
      )
    values (#{stuid,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}
      )
  </insert>
  <insert id="insertSelective" parameterType="com.cb.pojo.Student" >
    insert into student
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="stuid != null" >
        stuID,
      </if>
      <if test="name != null" >
        name,
      </if>
      <if test="age != null" >
        age,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="stuid != null" >
        #{stuid,jdbcType=INTEGER},
      </if>
      <if test="name != null" >
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="age != null" >
        #{age,jdbcType=INTEGER},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.cb.pojo.Student" >
    update student
    <set >
      <if test="name != null" >
        name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="age != null" >
        age = #{age,jdbcType=INTEGER},
      </if>
    </set>
    where stuID = #{stuid,jdbcType=INTEGER}
  </update>
  <update id="updateByPrimaryKey" parameterType="com.cb.pojo.Student" >
    update student
    set name = #{name,jdbcType=VARCHAR},
      age = #{age,jdbcType=INTEGER}
    where stuID = #{stuid,jdbcType=INTEGER}
  </update>
</mapper>

//===========================================
package com.cb.pojo;

public class Student {
    private Integer stuid;

    private String name;

    private Integer age;

    public Integer getStuid() {
        return stuid;
    }

    public void setStuid(Integer stuid) {
        this.stuid = stuid;
    }

   
	public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name == null ? null : name.trim();
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }
    
    @Override
   	public String toString() {
   		return "Student [stuid=" + stuid + ", name=" + name + ", age=" + age + "]";
   	}

}

3、MyBatis配置文件说明

a、用#表示占位符，如where stuID = #{stuid}

b、拼接符$，如  where username like '%${value}%'  定义输入到sql中的映射类型，${value}表示使用参数将${value}替换，做字符串的拼接。注意：如果是取简单数量类型的参数，括号中的值必须为value，POJO类型时参数可以是属性名

#{}和${}区别
#{}表示一个占位符号，通过#{}可以实现preparedStatement向占位符中设置值，自动进行java类型和jdbc类型转换，#{}可以有效防止sql注入。 #{}可以接收简单类型值或pojo属性值。 如果parameterType传输单个简单类型值，#{}括号中可以是value或其它名称。

${}表示拼接sql串，通过${}可以将parameterType 传入的内容拼接在sql中且不进行jdbc类型转换， ${}可以接收简单类型值或pojo属性值，如果parameterType传输单个简单类型值，${}括号中只能是value。

c、自增主键插入数据后，返回自增的id值，如果值自增的整数类型，是在插入之后才有值，所以主键值的获取阶段是after，order="AFTER"，插入成功后MyBatis会把自增的id值赋值给POJO

  <insert id="insert" parameterType="com.cb.pojo.Student" >
  	<!-- selectKey将主键返回，需要再返回 -->
		<selectKey keyProperty="stuid" order="AFTER" resultType="java.lang.Integer">
			select LAST_INSERT_ID()
		</selectKey>
    insert into student (stuID, name, age
      )
    values (#{stuid,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}
      )
  </insert>

添加selectKey实现将主键返回
keyProperty:返回的主键存储在pojo中的哪个属性
order：selectKey的执行顺序，是相对与insert语句来说，由于mysql的自增原理执行完insert语句之后才将主键生成，所以这里selectKey的执行顺序为after
resultType:返回的主键是什么类型
LAST_INSERT_ID():是mysql的函数，返回auto_increment自增列新记录id值。

第二种主键形式，UUID形式，这种形式的主键是需要在插入之前为POJO对象赋主键值，如果不想自己通过java代码来手动赋值，那么可以让MyBatis自动为pojo的主键属性赋值，这时就得在插入之前执行，MyBatis会自动把生成的UUID值赋值给pojo，如下：

<insert  id="insertUser" parameterType="com.cb.po.User">
<selectKey resultType="java.lang.String" order="BEFORE" 
keyProperty="id">
select uuid()
</selectKey>
insert into user(id,username,birthday,sex,address) 
		 values(#{id},#{username},#{birthday},#{sex},#{address})
</insert>
注意这里使用的order是“BEFORE”

4、一对一映射：由于MyBatis是半自动化的映射，所以不会自动加载对应关联属性，需要自己进行查询结果映射，步骤如下：

在POJO中添加关联属性的get，set

  private User user;    
 
   public User getUser() {
    return user;
}
 
public void setUser(User user) {
    this.user = user;
}

在mapper.xml中配置返回属性映射，因为这种非字段属性MyBatis不会自动组装，一对一用association节点进行包裹关联属性，关联属性和查询的结果字段中，如果和主表栏目字段名冲突了得起别名，如左连接结果id改为uid别名进行关联

<!-- 一对一:手动映射 -->
	<!-- 
	id:resultMap的唯一标识
	type:将查询出的数据放入这个指定的对象中
	注意:手动映射需要指定数据库中表的字段名与java中pojo类的属性名称的对应关系
	 -->
	<resultMap type="cn.cb.pojo.Orders" id="orderAndUserResultMap">
		<!-- id标签指定主键字段对应关系
		column:列,数据库中的字段名称
		property:属性,java中pojo中的属性名称
		 -->
		<id column="id" property="id"/>
		
		<!-- result:标签指定非主键字段的对应关系 -->
		<result column="user_id" property="userId"/>
		<result column="number" property="number"/>
		<result column="createtime" property="createtime"/>
		<result column="note" property="note"/>
		
		<!-- 这个标签指定单个对象的对应关系 
		property:指定将数据放入Orders中的user属性中
		javaType:user属性的类型
		-->
		<association property="user" javaType="cn.cb.pojo.User">
			<id column="uid" property="id"/>
			<result column="username" property="username"/>
			<result column="birthday" property="birthday"/>
			<result column="sex" property="sex"/>
			<result column="address" property="address"/>
		</association>
	</resultMap>
	<select id="findOrdersAndUser2" resultMap="orderAndUserResultMap">
		select a.*, b.id uid, username, birthday, sex, address 
		from orders a, user b 
		where a.user_id = b.id
	</select>

5、一对多映射：

pojo中添加集合属性

	private List<Orders> ordersList;
	public List<Orders> getOrdersList() {
		return ordersList;
	}
	public void setOrdersList(List<Orders> ordersList) {
		this.ordersList = ordersList;
	}

mapper中配置属性映射，集合属性包裹在collection节点中

	<resultMap type="cn.cb.pojo.User" id="userAndOrdersResultMap">
		<id column="id" property="id"/>
		<result column="username" property="username"/>
		<result column="birthday" property="birthday"/>
		<result column="sex" property="sex"/>
		<result column="address" property="address"/>
		
		<!-- 指定对应的集合对象关系映射
		property:将数据放入User对象中的ordersList属性中
		ofType:指定ordersList属性的泛型类型
		 -->
		<collection property="ordersList" ofType="cn.cb.pojo.Orders">
			<id column="oid" property="id"/>
			<result column="user_id" property="userId"/>
			<result column="number" property="number"/>
			<result column="createtime" property="createtime"/>
		</collection>
	</resultMap>

xml中一对多的sql语句一般多是外键连接，如left join ，inner join的写法

select  
       ib.INVOICE_AMOUNT,
        ri.REAL_INVOICE_ID,
        ri.REAL_INVOICE_SN,
        ri.TAX_CODE,
        ri.AMOUNT,
        ri.INVOICE_TAX_AMOUNT
 FROM invoice_bill ib INNER JOIN real_invoice ri ON ib.INVOICE_ID = ri.INVOICE_ID

6、批量查询：sql中的in(1,2,3,4)在MyBatis中也可以通过内置的循环标签属性foreach进行，如查询ids，这时用in就比较快一些，foreach标签支持传入list和map进行遍历，但是map用的不多，因为多个属性时一般传入POJO，但是list用的地方还挺多，其可以遍历pojo下的list类型属性，也支持直接传入list集合进行遍历，如下示例直接传入list集合：

  <select id="selectByPrimaryKeys" resultMap="BaseResultMap" parameterType="java.util.List" >
    select * 
    from sales s left join `order` o on s.salesid=o.salesid 
    where s.salesid in 
         <foreach collection="list" index="index" item="item" open="(" separator="," close=")">  
            #{item}  
        </foreach>
  </select>

//======================================
其中<foreach>这个标签是用来循环传入的集合的，collection="list"这个参数中有list，map两种，还有pojo中集合属性， parameterType="java.util.List"这个传入的参数类型必须这么写，不能简写成List，参数的返回类型也需要明确。

//=========================================
调用如下
		List ids=new ArrayList();
		ids.add(1);
		ids.add(2);
		ids.add(3);		
	    List<Sales> list = salesService.selectByPrimaryKeys(ids);

  批量操作如果传入的是POJO，那么collection指定为pojo属性名，如下　

<if test="goodsIdList != null">and bgoods.goods_id not in
                <foreach collection="goodsIdList" item="goodsId"  index="index"
                         open="(" close=")" separator=",">
                    #{goodsId}
                </foreach>
            </if>

 开启mysql批处理：默认是不支持批处理的，开启批处理需要如下设置：

数据库连接url后加上rewriteBatchedStatements=true这个参数即可，如：jdbc:mysql://localhost:3306/mydb?rewriteBatchedStatements=true

批量插入：

Integer insertSelectiveByList(List<OiSysRlatRoleMenu> list);

  <select id="insertSelectiveByList" >
   	insert into oi_sys_rlat_role_menu(role_id, menu_id, oper_id) values
   	<foreach collection="list" item="roleMenu" separator=",">
   	(
   		#{roleMenu.roleId},
   		#{roleMenu.menuId},
   		#{roleMenu.operId}
   	)
   	</foreach>
  </select>

　　参数list时，先判断是否为空，否则会报错。

　　

1.查询

<if test="ids != null and ids.size() > 0">
and c.grade_id in
<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>

</if>

<if test="ids != null and list.size() > 0">
and
<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
c.grade_id like '${item}' + '%'
</foreach>

</if>

2.新增

<insert id="insertList" parameterType="list">
insert into test(
id,name
) VALUES
<foreach collection="list" item="item" separator="," >

(#{item.id},#{item.name})

</foreach>

</insert>