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>
1、foreach遍历:
java的dao接口
void batchInsertBloodGlucoseFeed(@Param("feedList")List<BloodGlucoseFeed> feedList);
mapper:collection字段值对应java中Param,如果没有设置Param,那么就以字段名为准
<insert id="batchInsertBloodGlucoseFeed"> INSERT INTO xxxx( `meal`, `busi_no`, `food_name`, `intake` ) VALUES <foreach collection="feedList" item="model" separator=","> (#{model.meal}, #{model.busiNo},#{model.foodName}, #{model.intake}) </foreach> </insert>