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