1
PersonMapper.xml的配置内容如下(resultMap配置返回值,sql片段配置,select标签标签中的内容介绍,配置使用二级缓存,使用别名的数据类型,条件查询map传递参数,模糊查询,插入,更新,删除,where条件查询,动态修改,in查询foreach迭代,批量插入foreach,批量删除,一对多查询,extends:resultMap的继承,多对多查询,延迟加载):
<?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">
<!--
namespace:命名空间,用来唯一标识一个映射文件,命名规范就是当前的文件的包名+mapper的xml文件名
-->
<mapper
namespace="com.rl.mapper.PersonMapper">
<!--当前映射文件开启二级缓存-->
<cache
type="org.mybatis.caches.ehcache.EhcacheCache"/>
<!--
id:resultMap的唯一标识
type:给哪个实体类做结果的映射
-->
<resultMap
type="person"
id="BaseResultMap">
<!--
column:数据库中表的字段
property:数据库中表所有映射的实体类javaBean中的属性名
-->
<id
column="person_id"
property="personId"/>
<result
column="name"
property="name"/>
<result
column="gender"
property="gender"/>
<result
column="person_addr"
property="personAddr"/>
<result
column="birthday"
property="birthday"/>
</resultMap>
<!--
公用的sql片段,也可以接收参数,动态sql,所有的sql可以使用
-->
<sql
id="columns">
PERSON_ID, NAME, GENDER, PERSON_ADDR, BIRTHDAY
</sql>
<!--
根据id来查询一个Person的数据
sql语句接收参数的一个语法#{},如果接收的是一个{}中的内容任意select
* from person_test t where t.ID = ?,使用预编译方式生成sql
id:sql语句的唯一的标识不能重复
parameterType:sql要接收的数据类型
resultType:sql所返回的数据类型
-->
<!--
实际项目中数据库的表的字段一般由多个单词来构成
由下划线来分隔多个单词 person_addr
在java的model的实体类中的属性多个单词的命名规范是驼峰模式personAddr
-->
<!--
useCache:控制当前的这个sql是否使用二级缓存
-->
<select
id="selectPersonById"
parameterType="int"
resultMap="BaseResultMap"
useCache="true">
select * from person t where t.person_id = #{id}
</select>
<select
id="selectPersonCount"
resultType="int">
select count(*) from person
</select>
<!--
这里引用了上面的sql片段
-->
<select
id="selectPersonAll"
resultMap="BaseResultMap">
select <include
refid="columns"/> from person
</select>
<!--
可以使用map
map.put("gender",1);
map.put("birthday" new Date());
#{}中的内容使用Map的key来接收参数
-->
<select
id="selectPersonByParams"
parameterType="map"
resultMap="BaseResultMap">
<![CDATA[
select * from person t where t.gender = #{gender} and t.birthday < #{birthday}
]]>
</select>
<!--
使用查询对象的get方法来接收参数(也就是属性名)
-->
<select
id="selectPersonByParams1"
parameterType="qc"
resultMap="BaseResultMap">
<![CDATA[
select * from person t where t.gender = #{gender} and t.birthday < #{birthday}
]]>
</select>
<!--
模糊查询使用${} select * from person t
where t.name like '%安%'
parameterType:不能直接使用String,一定要用查询对象或者map
-->
<select
id="selectPersonByLike"
parameterType="qc"
resultMap="BaseResultMap">
select * from person t where t.name like '%${name}%'
</select>
<!--
库表变更 -->
<insert
id="insert"
parameterType="person">
<!--
keyProperty:实体类中主键属性,主键生成后把主键返回给这个属性
order:生成主键的sql和insert执行的顺序,
mysql是AFTER,
oracle是BEFORE
resultType: 主键返回的数据类型
sql:
在mysql中select
LAST_INSERT_ID()
在oracle中
select xxx.nextval from dual
selectKey做了两件事:1.主键自增
2.主键返回
-->
<selectKey
keyProperty="personId"
order="AFTER"
resultType="int">
select LAST_INSERT_ID()
</selectKey>
insert into person (person_id, name, gender, person_addr, birthday)
values(#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday})
</insert>
<update
id="update"
parameterType="person">
update person p set p.name = #{name},
p.gender = #{gender},
p.person_addr = #{personAddr},
p.birthday = #{birthday}
where p.person_id = #{personId}
</update>
<!--
删除的sql不能使用别名
-->
<delete
id="delete"
parameterType="int">
delete from person where person_id = #{personId}
</delete>
<!-- =============================动态sql==================================
-->
<!--
map.put("name", "安");
map.put("gender", "0");
map.put("personAddr", "东京")
map.put("birthday", new Date());
<where>会自动处理and,
第一个and可以不写,其他的and必须要写
-->
<select
id="selectPersonByCondition"
parameterType="map"
resultMap="BaseResultMap">
select * from person t
<where>
<if
test="name != null">
t.name like '%${name}%'
</if>
<if
test="gender != null">
and t.gender = #{gender}
</if>
<if
test="personAddr != null">
and t.person_addr like '%${personAddr}%'
</if>
<if
test="birthday != null">
<![CDATA[
and t.birthday < #{birthday}
]]>
</if>
</where>
</select>
<!--
动态修改
<set>标签可以去掉最后一个逗号
flushCache:二级缓存的刷新的配置:默认是true:会刷新,如果false就不刷新缓存
-->
<update
id="dynamicUpdate"
parameterType="person"
flushCache="false">
update person t
<set>
<if
test="name != null">
t.name = #{name},
</if>
<if
test="gender != null">
t.gender = #{gender},
</if>
<if
test="personAddr != null">
t.person_addr = #{personAddr},
</if>
<if
test="birthday != null">
t.birthday = #{birthday}
</if>
</set>
where t.person_id = #{personId}
</update>
<!--
select * from person t where t.person_id in (1,2,3)
map.put("ids", list);
-->
<select
id="selectPersonByIn"
parameterType="map"
resultMap="BaseResultMap">
select * from person t where t.person_id in
<foreach
collection="ids"
item="personId"
open="("
close=")"
separator=","
index="index">
#{personId}
</foreach>
</select>
<!--
map.put("pList", pList);
insert into person (person_id, name, gender, person_addr, birthday)
values
(#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday}),
(#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday}),
(#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday}),
(#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday}),
(#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday});
-->
<insert
id="insertBatch"
parameterType="map">
<selectKey
keyProperty="personId"
order="AFTER"
resultType="int">
select LAST_INSERT_ID()
</selectKey>
insert into person (person_id, name, gender, person_addr, birthday)
values
<foreach
collection="pList"
item="person"
separator=",">
(#{person.personId}, #{person.name}, #{person.gender}, #{person.personAddr}, #{person.birthday})
</foreach>
</insert>
<delete
id="deleteBatch"
parameterType="map">
delete from person where person_id in
<foreach
collection="ids"
item="personId"
open="("
close=")"
separator=","
index="index">
#{personId}
</foreach>
</delete>
<!-- ===============================关联查询==================
-->
<!--
一对多 -->
<resultMap
type="person"
id="selectPersonAndOrderByPIdRM">
<id
column="person_id"
property="personId"/>
<result
column="name"
property="name"/>
<result
column="gender"
property="gender"/>
<result
column="person_addr"
property="personAddr"/>
<result
column="birthday"
property="birthday"/>
<!--
collection:一对多的关联映射
property:一的端集合的属性名
ofType:集合中的泛型
-->
<collection
property="ordersList"
ofType="com.rl.model1.Orders">
<id
column="ORDER_ID"
property="orderId"
jdbcType="INTEGER"
/>
<result
column="PERSON_ID"
property="personId"
jdbcType="INTEGER"
/>
<result
column="TOTAL_PRICE"
property="totalPrice"
jdbcType="REAL"
/>
<result
column="ADDR"
property="addr"
jdbcType="VARCHAR"
/>
</collection>
</resultMap>
<!--
extends:resultMap的继承
-->
<resultMap
type="person"
id="selectPersonAndOrderByPIdRM1"
extends="BaseResultMap">
<collection
property="ordersList"
ofType="com.rl.model1.Orders">
<id
column="ORDER_ID"
property="orderId"
jdbcType="INTEGER"
/>
<result
column="PERSON_ID"
property="personId"
jdbcType="INTEGER"
/>
<result
column="TOTAL_PRICE"
property="totalPrice"
jdbcType="REAL"
/>
<result
column="ADDR"
property="addr"
jdbcType="VARCHAR"
/>
</collection>
</resultMap>
<resultMap
type="person"
id="selectPersonOrderAndDetailByPIdRM"
extends="BaseResultMap">
<collection
property="ordersList"
ofType="com.rl.model1.Orders">
<id
column="ORDER_ID"
property="orderId"
jdbcType="INTEGER"
/>
<result
column="PERSON_ID"
property="personId"
jdbcType="INTEGER"
/>
<result
column="TOTAL_PRICE"
property="totalPrice"
jdbcType="REAL"
/>
<result
column="ADDR"
property="addr"
jdbcType="VARCHAR"
/>
<collection
property="detailList"
ofType="com.rl.model1.OrderDetail">
<id
column="DETAIL_ID"
property="detailId"
jdbcType="INTEGER"
/>
<result
column="ORDER_ID"
property="orderId"
jdbcType="INTEGER"
/>
<result
column="PRICE"
property="price"
jdbcType="REAL"
/>
<result
column="QUANTITY"
property="quantity"
jdbcType="INTEGER"
/>
<result
column="ITEM_NAME"
property="itemName"
jdbcType="VARCHAR"
/>
</collection>
</collection>
</resultMap>
<resultMap
type="person"
id="selectPersonAndRoleByPIdRM"
extends="BaseResultMap">
<collection
property="roleList"
ofType="com.rl.model1.Role">
<id
column="ROLE_ID"
property="roleId"
jdbcType="INTEGER"
/>
<result
column="ROLE_NAME"
property="roleName"
jdbcType="VARCHAR"
/>
<result
column="DESCRIPT"
property="descript"
jdbcType="VARCHAR"
/>
</collection>
</resultMap>
<select
id="selectPersonAndOrderByPId"
parameterType="int"
resultMap="selectPersonAndOrderByPIdRM1">
select * from person p, orders o where p.PERSON_ID = o.PERSON_ID and p.PERSON_ID = #{personId}
</select>
<select
id="selectPersonOrderAndDetailByPId"
parameterType="int"
resultMap="selectPersonOrderAndDetailByPIdRM">
select * from person p,
orders o,
order_detail od where
p.PERSON_ID = o.PERSON_ID
and o.ORDER_ID = od.ORDER_ID
and p.PERSON_ID = #{personId}
</select>
<!--
多对多从Person一端看
-->
<select
id="selectPersonAndRoleByPId"
parameterType="int"
resultMap="selectPersonAndRoleByPIdRM">
SELECT p.*, r.* from person p,
person_role pr,
role r where
p.PERSON_ID = pr.PERSON_ID
and pr.ROLE_ID = r.ROLE_ID
and p.PERSON_ID = #{personId}
</select>
<!-- =========================延迟加载========================
-->
<resultMap
type="person"
id="selectPersonByIdLazyRM"
extends="BaseResultMap">
<!--
column:主sql的一列作为子sql的参数
select:指定子sql的位置
-->
<collection
property="ordersList"
column="person_id"
select="com.rl.mapper.OrdersMapper.selectOrderByPersonId">
</collection>
</resultMap>
<select
id="selectPersonByIdLazy"
parameterType="int"
resultMap="selectPersonByIdLazyRM">
select * from person t where t.person_id = #{personId}
</select>
</mapper>
OrdersMapper.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.rl.mapper.OrdersMapper"
>
<resultMap
id="BaseResultMap"
type="com.rl.model1.Orders"
>
<id
column="ORDER_ID"
property="orderId"
jdbcType="INTEGER"
/>
<result
column="PERSON_ID"
property="personId"
jdbcType="INTEGER"
/>
<result
column="TOTAL_PRICE"
property="totalPrice"
jdbcType="REAL"
/>
<result
column="ADDR"
property="addr"
jdbcType="VARCHAR"
/>
</resultMap>
<sql
id="Base_Column_List"
>
ORDER_ID, PERSON_ID, TOTAL_PRICE, ADDR
</sql>
<select
id="selectByPrimaryKey"
resultMap="BaseResultMap"
parameterType="java.lang.Integer"
>
select
<include
refid="Base_Column_List"
/>
from orders
where ORDER_ID = #{orderId,jdbcType=INTEGER}
</select>
<delete
id="deleteByPrimaryKey"
parameterType="java.lang.Integer"
>
delete from orders
where ORDER_ID = #{orderId,jdbcType=INTEGER}
</delete>
<insert
id="insert"
parameterType="com.rl.model1.Orders"
>
insert into orders (ORDER_ID, PERSON_ID, TOTAL_PRICE,
ADDR)
values (#{orderId,jdbcType=INTEGER}, #{personId,jdbcType=INTEGER}, #{totalPrice,jdbcType=REAL},
#{addr,jdbcType=VARCHAR})
</insert>
<insert
id="insertSelective"
parameterType="com.rl.model1.Orders"
>
insert into orders
<trim
prefix="("
suffix=")"
suffixOverrides=","
>
<if
test="orderId != null"
>
ORDER_ID,
</if>
<if
test="personId != null"
>
PERSON_ID,
</if>
<if
test="totalPrice != null"
>
TOTAL_PRICE,
</if>
<if
test="addr != null"
>
ADDR,
</if>
</trim>
<trim
prefix="values ("
suffix=")"
suffixOverrides=","
>
<if
test="orderId != null"
>
#{orderId,jdbcType=INTEGER},
</if>
<if
test="personId != null"
>
#{personId,jdbcType=INTEGER},
</if>
<if
test="totalPrice != null"
>
#{totalPrice,jdbcType=REAL},
</if>
<if
test="addr != null"
>
#{addr,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update
id="updateByPrimaryKeySelective"
parameterType="com.rl.model1.Orders"
>
update orders
<set
>
<if
test="personId != null"
>
PERSON_ID = #{personId,jdbcType=INTEGER},
</if>
<if
test="totalPrice != null"
>
TOTAL_PRICE = #{totalPrice,jdbcType=REAL},
</if>
<if
test="addr != null"
>
ADDR = #{addr,jdbcType=VARCHAR},
</if>
</set>
where ORDER_ID = #{orderId,jdbcType=INTEGER}
</update>
<update
id="updateByPrimaryKey"
parameterType="com.rl.model1.Orders"
>
update orders
set PERSON_ID = #{personId,jdbcType=INTEGER},
TOTAL_PRICE = #{totalPrice,jdbcType=REAL},
ADDR = #{addr,jdbcType=VARCHAR}
where ORDER_ID = #{orderId,jdbcType=INTEGER}
</update>
<resultMap
type="com.rl.model1.Orders"
id="selectPersonByOrderIdRM"
extends="BaseResultMap">
<!--
association:多对一的关联映射
property:多的一端所属的一的一端类的属性名
javaType:一的一端的数据类型
-->
<association
property="person"
javaType="person">
<id
column="person_id"
property="personId"/>
<result
column="name"
property="name"/>
<result
column="gender"
property="gender"/>
<result
column="person_addr"
property="personAddr"/>
<result
column="birthday"
property="birthday"/>
</association>
</resultMap>
<resultMap
type="com.rl.model1.Orders"
id="selectPersonAndDetailByOrderIdRM"
extends="selectPersonByOrderIdRM">
<collection
property="detailList"
ofType="com.rl.model1.OrderDetail">
<id
column="DETAIL_ID"
property="detailId"
jdbcType="INTEGER"
/>
<result
column="ORDER_ID"
property="orderId"
jdbcType="INTEGER"
/>
<result
column="PRICE"
property="price"
jdbcType="REAL"
/>
<result
column="QUANTITY"
property="quantity"
jdbcType="INTEGER"
/>
<result
column="ITEM_NAME"
property="itemName"
jdbcType="VARCHAR"
/>
</collection>
</resultMap>
<!--
多对一和一对多的混合查询的resultMap association要放在Collection的前面
-->
<resultMap
type="com.rl.model1.Orders"
id="selectPersonAndDetailByOrderIdRM1"
extends="BaseResultMap">
<association
property="person"
javaType="person">
<id
column="person_id"
property="personId"/>
<result
column="name"
property="name"/>
<result
column="gender"
property="gender"/>
<result
column="person_addr"
property="personAddr"/>
<result
column="birthday"
property="birthday"/>
</association>
<collection
property="detailList"
ofType="com.rl.model1.OrderDetail">
<id
column="DETAIL_ID"
property="detailId"
jdbcType="INTEGER"
/>
<result
column="ORDER_ID"
property="orderId"
jdbcType="INTEGER"
/>
<result
column="PRICE"
property="price"
jdbcType="REAL"
/>
<result
column="QUANTITY"
property="quantity"
jdbcType="INTEGER"
/>
<result
column="ITEM_NAME"
property="itemName"
jdbcType="VARCHAR"
/>
</collection>
</resultMap>
<select
id="selectPersonByOrderId"
parameterType="int"
resultMap="selectPersonByOrderIdRM">
select * from orders o, person p where o.PERSON_ID = p.PERSON_ID and o.ORDER_ID = #{orderId}
</select>
<select
id="selectPersonAndDetailByOrderId"
parameterType="int"
resultMap="selectPersonAndDetailByOrderIdRM">
select * from orders o, person p, order_detail
od
where o.PERSON_ID = p.PERSON_ID
and o.ORDER_ID = od.ORDER_ID
and o.ORDER_ID = #{orderId}
</select>
<!--
延迟加载的子sql,传递过来的参数是person_id
-->
<select
id="selectOrderByPersonId"
parameterType="int"
resultMap="BaseResultMap">
select * from orders t where t.person_id = #{personid}
</select>
<resultMap
type="com.rl.model1.Orders"
id="selectOrderByIdLazyRM"
extends="BaseResultMap">
<association
property="person"
column="person_id"
select="com.rl.mapper.PersonMapper.selectPersonById"></association>
</resultMap>
<resultMap
type="com.rl.model1.Orders"
id="selectOrderByIdLazy1RM"
extends="BaseResultMap">
<association
property="person"
column="person_id"
select="com.rl.mapper.PersonMapper.selectPersonById"></association>
<collection
property="detailList"
column="order_id"
select="com.rl.mapper.OrderDetailMapper.selectDetailByOrderId"></collection>
</resultMap>
<select
id="selectOrderByIdLazy"
parameterType="int"
resultMap="selectOrderByIdLazyRM">
select * from orders t where t.order_id = #{orderId}
</select>
<!--
查询订单的所属人和订单明细,延迟加载
-->
<select
id="selectOrderByIdLazy1"
parameterType="int"
resultMap="selectOrderByIdLazy1RM">
select * from orders t where t.order_id = #{orderId}
</select>
</mapper>
2
配置sqlMapConfig.xml中的<settings>配置:
<configuration>
<!--
开启延迟加载 -->
<settings>
<!--
全局的延迟加载的开关必须要开启 -->
<setting
name="lazyLoadingEnabled"
value="true"/>
<!--
积极加载设置成false -->
<setting
name="aggressiveLazyLoading"
value="false"/>
<!--
开启二级缓存,
缓存中只要是需要配置的针对的都是二级缓存
-->
<setting
name="cacheEnabled"
value="true"/>
</settings>
3
测试代码如下:
MybatisTest4.java
package com.rl.test;
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 org.junit.Before;
import org.junit.Test;
import com.rl.model1.Orders;
import com.rl.model1.Person;
/**
* mybatis的延迟加载
*/
public class MybatisTest4 {
SqlSessionFactory sessionFactory;
@Before
public void setUp() throws Exception {
InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");
sessionFactory = new SqlSessionFactoryBuilder().build(in);
}
/**
* 一对多关联查询
*/
@Test
public void selectPersonByIdLazy() {
//创建session对象
SqlSession session = sessionFactory.openSession();
try {
Person person = session.selectOne("com.rl.mapper.PersonMapper.selectPersonByIdLazy", 1);
System.out.println(person.getOrdersList());
} finally{
session.close();
}
}
/**
* 多对一的延迟加载
*/
@Test
public void selectOrderByIdLazy() {
//创建session对象
SqlSession session = sessionFactory.openSession();
try {
Orders order = session.selectOne("com.rl.mapper.OrdersMapper.selectOrderByIdLazy", 1);
System.out.println(order.getPerson());
} finally{
session.close();
}
}
/**
* 一对多和多对一的混合使用,延迟加载
*/
@Test
public void selectOrderByIdLazy1() {
//创建session对象
SqlSession session = sessionFactory.openSession();
try {
Orders order = session.selectOne("com.rl.mapper.OrdersMapper.selectOrderByIdLazy1", 1);
System.out.println(order.getPerson());
System.out.println(order.getDetailList());
} finally{
session.close();
}
}
}