03_MyBatis基本查询，mapper文件的定义，测试代码的编写，resultMap配置返回值，sql片段配置，select标签标签中的内容介绍，配置使用二级缓存，使用别名的数据类型，条件查询ma-阿里云开发者社区

1 PersonTestMapper.xml中的内容如下:

<?xmlversion="1.0"encoding="UTF-8"?>

<!DOCTYPEmapper

PUBLIC"-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--

namespace:命名空间，用来唯一标识一个映射文件,命名规范就是当前的文件的包加上文件名

-->

<mappernamespace="com.rl.mapper.PersonTestMapper">

<!--

根据id来查询一个Person的数据

sql语句接收参数的一个语法#{}，如果接收的是一个{}中的内容任意select * from person_test t where t.ID = ?,使用预编译方式生成sql

id:sql语句的唯一的标识不能重复

parameterType:sql要接收的数据类型

resultType：sql所返回的数据类型

-->

<!--

实际项目中数据库的表的字段一般由多个单词来构成由下划线来分隔多个单词 person_addr

在java的model的实体类中的属性多个单词的命名规范是驼峰模式personAddr

-->

<selectid="selectPersonById"parameterType="java.lang.Integer"resultType="com.rl.model.Person">

select * from person_test t where t.ID = #{id}

</select>

</mapper>

2 PersonMapper.xml的配置内容如下（resultMap配置返回值，sql片段配置，select标签标签中的内容介绍，配置使用二级缓存，使用别名的数据类型，条件查询map传递参数，模糊查询，插入，更新，删除，where条件查询，动态修改，in查询foreach迭代，批量插入foreach,批量删除，一对多查询，extends:resultMap的继承，多对多查询，延迟加载）：

<?xmlversion="1.0"encoding="UTF-8"?>

<!DOCTYPEmapper

PUBLIC"-//mybatis.org//DTD Mapper 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!--

namespace:命名空间，用来唯一标识一个映射文件,命名规范就是当前的文件的包名+mapper的xml文件名

-->

<mappernamespace="com.rl.mapper.PersonMapper">

<cachetype="org.mybatis.caches.ehcache.EhcacheCache"/>

<!--

id:resultMap的唯一标识

type:给哪个实体类做结果的映射

-->

<resultMaptype="person"id="BaseResultMap">

<!--

column:数据库中表的字段

property：数据库中表所有映射的实体类javaBean中的属性名

-->

<idcolumn="person_id"property="personId"/>

<resultcolumn="name"property="name"/>

<resultcolumn="gender"property="gender"/>

<resultcolumn="person_addr"property="personAddr"/>

<resultcolumn="birthday"property="birthday"/>

</resultMap>

<!--

公用的sql片段,也可以接收参数，动态sql，所有的sql可以使用

-->

<sqlid="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是否使用二级缓存

-->

<selectid="selectPersonById"parameterType="int"resultMap="BaseResultMap"useCache="true">

select * from person t where t.person_id = #{id}

</select>

<selectid="selectPersonCount"resultType="int">

select count(*) from person

</select>

<selectid="selectPersonAll"resultMap="BaseResultMap">

select <includerefid="columns"/> from person

</select>

<!--

可以使用map

map.put("gender",1);

map.put("birthday" new Date());

#{}中的内容使用Map的key来接收参数

-->

<selectid="selectPersonByParams"parameterType="map"resultMap="BaseResultMap">

<![CDATA[

select * from person t where t.gender = #{gender} and t.birthday < #{birthday}

]]>

</select>

<!--

使用查询对象的get方法来接收参数（也就是属性名）

-->

<selectid="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

-->

<selectid="selectPersonByLike"parameterType="qc"resultMap="BaseResultMap">

select * from person t where t.name like '%${name}%'

</select>

<insertid="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.主键返回

-->

<selectKeykeyProperty="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>

<updateid="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不能使用别名

-->

<deleteid="delete"parameterType="int">

delete from person where person_id = #{personId}

</delete>

<!--

map.put("name", "安");

map.put("gender", "0");

map.put("personAddr", "东京")

map.put("birthday", new Date());

<where>会自动处理and，第一个and可以不写，其他的and必须要写

-->

<selectid="selectPersonByCondition"parameterType="map"resultMap="BaseResultMap">

select * from person t

<where>

<iftest="name != null">

t.name like '%${name}%'

</if>

<iftest="gender != null">

and t.gender = #{gender}

</if>

<iftest="personAddr != null">

and t.person_addr like '%${personAddr}%'

</if>

<iftest="birthday != null">

<![CDATA[

and t.birthday < #{birthday}

]]>

</if>

</where>

</select>

<!--

动态修改

<set>标签可以去掉最后一个逗号

flushCache：二级缓存的刷新的配置：默认是true：会刷新，如果false就不刷新缓存

-->

<updateid="dynamicUpdate"parameterType="person"flushCache="false">

update person t

<set>

<iftest="name != null">

t.name = #{name},

</if>

<iftest="gender != null">

t.gender = #{gender},

</if>

<iftest="personAddr != null">

t.person_addr = #{personAddr},

</if>

<iftest="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);

-->

<selectid="selectPersonByIn"parameterType="map"resultMap="BaseResultMap">

select * from person t where t.person_id in

<foreachcollection="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});

-->

<insertid="insertBatch"parameterType="map">

<selectKeykeyProperty="personId"order="AFTER"resultType="int">

select LAST_INSERT_ID()

</selectKey>

insert into person (person_id, name, gender, person_addr, birthday)

values

<foreachcollection="pList"item="person"separator=",">

(#{person.personId}, #{person.name}, #{person.gender}, #{person.personAddr}, #{person.birthday})

</foreach>

</insert>

<deleteid="deleteBatch"parameterType="map">

delete from person where person_id in

<foreachcollection="ids"item="personId"open="("close=")"separator=","index="index">

#{personId}

</foreach>

</delete>

<resultMaptype="person"id="selectPersonAndOrderByPIdRM">

<idcolumn="person_id"property="personId"/>

<resultcolumn="name"property="name"/>

<resultcolumn="gender"property="gender"/>

<resultcolumn="person_addr"property="personAddr"/>

<resultcolumn="birthday"property="birthday"/>

<!--

collection:一对多的关联映射

property：一的端集合的属性名

ofType：集合中的泛型

-->

<collectionproperty="ordersList"ofType="com.rl.model1.Orders">

<idcolumn="ORDER_ID"property="orderId"jdbcType="INTEGER"/>

<resultcolumn="PERSON_ID"property="personId"jdbcType="INTEGER"/>

<resultcolumn="TOTAL_PRICE"property="totalPrice"jdbcType="REAL"/>

<resultcolumn="ADDR"property="addr"jdbcType="VARCHAR"/>

</collection>

</resultMap>

<!--

extends:resultMap的继承

-->

<resultMaptype="person"id="selectPersonAndOrderByPIdRM1"extends="BaseResultMap">

<collectionproperty="ordersList"ofType="com.rl.model1.Orders">

<idcolumn="ORDER_ID"property="orderId"jdbcType="INTEGER"/>

<resultcolumn="PERSON_ID"property="personId"jdbcType="INTEGER"/>

<resultcolumn="TOTAL_PRICE"property="totalPrice"jdbcType="REAL"/>

<resultcolumn="ADDR"property="addr"jdbcType="VARCHAR"/>

</collection>

</resultMap>

<resultMaptype="person"id="selectPersonOrderAndDetailByPIdRM"extends="BaseResultMap">

<collectionproperty="ordersList"ofType="com.rl.model1.Orders">

<idcolumn="ORDER_ID"property="orderId"jdbcType="INTEGER"/>

<resultcolumn="PERSON_ID"property="personId"jdbcType="INTEGER"/>

<resultcolumn="TOTAL_PRICE"property="totalPrice"jdbcType="REAL"/>

<resultcolumn="ADDR"property="addr"jdbcType="VARCHAR"/>

<collectionproperty="detailList"ofType="com.rl.model1.OrderDetail">

<idcolumn="DETAIL_ID"property="detailId"jdbcType="INTEGER"/>

<resultcolumn="ORDER_ID"property="orderId"jdbcType="INTEGER"/>

<resultcolumn="PRICE"property="price"jdbcType="REAL"/>

<resultcolumn="QUANTITY"property="quantity"jdbcType="INTEGER"/>

<resultcolumn="ITEM_NAME"property="itemName"jdbcType="VARCHAR"/>

</collection>

</resultMap>

<resultMaptype="person"id="selectPersonAndRoleByPIdRM"extends="BaseResultMap">

<collectionproperty="roleList"ofType="com.rl.model1.Role">

<idcolumn="ROLE_ID"property="roleId"jdbcType="INTEGER"/>

<resultcolumn="ROLE_NAME"property="roleName"jdbcType="VARCHAR"/>

<resultcolumn="DESCRIPT"property="descript"jdbcType="VARCHAR"/>

</collection>

</resultMap>

<selectid="selectPersonAndOrderByPId"parameterType="int"resultMap="selectPersonAndOrderByPIdRM1">

select * from person p, orders o where p.PERSON_ID = o.PERSON_ID and p.PERSON_ID = #{personId}

</select>

<selectid="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>

<selectid="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>

<resultMaptype="person"id="selectPersonByIdLazyRM"extends="BaseResultMap">

<!--

column：主sql的一列作为子sql的参数

select：指定子sql的位置

-->

<collectionproperty="ordersList"column="person_id"select="com.rl.mapper.OrdersMapper.selectOrderByPersonId">

</collection>

</resultMap>

<selectid="selectPersonByIdLazy"parameterType="int"resultMap="selectPersonByIdLazyRM">

select * from person t where t.person_id = #{personId}

</select>

</mapper>

RoleMapper.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" >

</resultMap>

ROLE_ID, ROLE_NAME, DESCRIPT

</sql>

select

from role

where ROLE_ID = #{roleId,jdbcType=INTEGER}

</select>

delete from role

where ROLE_ID = #{roleId,jdbcType=INTEGER}

</delete>

insert into role (ROLE_ID, ROLE_NAME, DESCRIPT

)

values (#{roleId,jdbcType=INTEGER}, #{roleName,jdbcType=VARCHAR}, #{descript,jdbcType=VARCHAR}

)

</insert>

insert into role

ROLE_ID,

</if>

ROLE_NAME,

</if>

DESCRIPT,

</if>

</trim>

#{roleId,jdbcType=INTEGER},

</if>

#{roleName,jdbcType=VARCHAR},

</if>

#{descript,jdbcType=VARCHAR},

</if>

</trim>

</insert>

update role

<set >

ROLE_NAME = #{roleName,jdbcType=VARCHAR},

</if>

DESCRIPT = #{descript,jdbcType=VARCHAR},

</if>

</set>

where ROLE_ID = #{roleId,jdbcType=INTEGER}

</update>

update role

set ROLE_NAME = #{roleName,jdbcType=VARCHAR},

DESCRIPT = #{descript,jdbcType=VARCHAR}

where ROLE_ID = #{roleId,jdbcType=INTEGER}

</update>

</collection>

</resultMap>

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 r.ROLE_ID = #{roleId}

</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" >

</resultMap>

ORDER_ID, PERSON_ID, TOTAL_PRICE, ADDR

</sql>

select

from orders

where ORDER_ID = #{orderId,jdbcType=INTEGER}

</select>

delete from orders

where ORDER_ID = #{orderId,jdbcType=INTEGER}

</delete>

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 into orders

ORDER_ID,

</if>

PERSON_ID,

</if>

TOTAL_PRICE,

</if>

ADDR,

</if>

</trim>

#{orderId,jdbcType=INTEGER},

</if>

#{personId,jdbcType=INTEGER},

</if>

#{totalPrice,jdbcType=REAL},

</if>

#{addr,jdbcType=VARCHAR},

</if>

</trim>

</insert>

update orders

<set >

PERSON_ID = #{personId,jdbcType=INTEGER},

</if>

TOTAL_PRICE = #{totalPrice,jdbcType=REAL},

</if>

ADDR = #{addr,jdbcType=VARCHAR},

</if>

</set>

where ORDER_ID = #{orderId,jdbcType=INTEGER}

</update>

update orders

set PERSON_ID = #{personId,jdbcType=INTEGER},

TOTAL_PRICE = #{totalPrice,jdbcType=REAL},

ADDR = #{addr,jdbcType=VARCHAR}

where ORDER_ID = #{orderId,jdbcType=INTEGER}

</update>

<!--

association:多对一的关联映射

property：多的一端所属的一的一端类的属性名

javaType：一的一端的数据类型

-->

</association>

</resultMap>

</collection>

</resultMap>

<!--

多对一和一对多的混合查询的resultMap association要放在Collection的前面

-->

</association>

</collection>

</resultMap>

select * from orders o, person p where o.PERSON_ID = p.PERSON_ID and o.ORDER_ID = #{orderId}

</select>

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 * from orders t where t.person_id = #{personid}

</select>

</resultMap>

</resultMap>

select * from orders t where t.order_id = #{orderId}

</select>

<!--

查询订单的所属人和订单明细，延迟加载

-->

select * from orders t where t.order_id = #{orderId}

</select>

</mapper>

3配置sqlMapConfig.xml中的<mappers>配置：

<mapperresource="com/rl/mapper/PersonTestMapper.xml"/>

<mapperresource="com/rl/mapper/PersonMapper.xml"/>

<mapperresource="com/rl/mapper/RoleMapper.xml"/>

<mapperresource="com/rl/mapper/OrdersMapper.xml"/>

<mapperresource="com/rl/mapper/OrderDetailMapper.xml"/>

</mappers>

4测试代码如下：

MybatisTest.java

package com.rl.test;

import java.io.InputStream;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

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.Person;

import com.rl.model1.QueryCondition;

/**

* mybatis的简单查询

public class MybatisTest {

SqlSessionFactory sessionFactory;

@Before

public void setUp() throws Exception {

InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");

sessionFactory = new SqlSessionFactoryBuilder().build(in);

}

/**

* 通过id查询数据

@Test

public void selectPersonById() {

//创建session对象

SqlSession session = sessionFactory.openSession();

try {

//第一个参数：指定要执行的sql语法是namespace.sql的id，第二个参数sql要接收的参数

com.rl.model.Person person = session.selectOne(

"com.rl.mapper.PersonTestMapper.selectPersonById", 1);

System.out.println(person);

} finally{

session.close();

}

/**

* 使用resultMap来查询

@Test

public void selectPersonById1() {

//创建session对象

SqlSession session = sessionFactory.openSession();

try {

//第一个参数：指定要执行的sql语法是namespace.sql的id，第二个参数sql要接收的参数

Person person = session.selectOne("com.rl.mapper.PersonMapper.selectPersonById", 1);

System.out.println(person);

} finally{

session.close();

}

/**

* 查询表的记录数

@Test

public void selectPersonCount() {

//创建session对象

SqlSession session = sessionFactory.openSession();

try {

Integer count = session.selectOne("com.rl.mapper.PersonMapper.selectPersonCount");

System.out.println(count);

} finally{

session.close();

}

/**

* 查询所有Person

@Test

public void selectPersonAll() {

//创建session对象

SqlSession session = sessionFactory.openSession();

try {

//查询集合的时候需要使用selectList

List<Person> pList = session.selectList("com.rl.mapper.PersonMapper.selectPersonAll");

for(Person p : pList){

System.out.println(p);

}

} finally{

session.close();

}

/**

* 使用多个参数用Map方式来查询

@Test

public void selectPersonByParams() {

//创建session对象

SqlSession session = sessionFactory.openSession();

try {

Map<String, Object> map = new HashMap<String, Object>();

map.put("gender",0);

map.put("birthday", new Date());

//查询集合的时候需要使用selectList

List<Person> pList = session.selectList("com.rl.mapper.PersonMapper.selectPersonByParams",map);

for(Person p : pList){

System.out.println(p);

}

} finally{

session.close();

}

/**

* 使用多个参数查询数据，使用查询对象的方式

@Test

public void selectPersonByParams1() {

//创建session对象

SqlSession session = sessionFactory.openSession();

try {

QueryCondition qc = new QueryCondition();

qc.setGender("0");

qc.setBirthday(new Date());

//查询集合的时候需要使用selectList

List<Person> pList = session.selectList("com.rl.mapper.PersonMapper.selectPersonByParams1",qc);

for(Person p : pList){

System.out.println(p);

}

} finally{

session.close();

}

/**

* 模糊查询

@Test

public void selectPersonByLike() {

//创建session对象

SqlSession session = sessionFactory.openSession();

try {

QueryCondition qc = new QueryCondition();

qc.setName("武");

//查询集合的时候需要使用selectList

List<Person> pList = session.selectList("com.rl.mapper.PersonMapper.selectPersonByLike",qc);

for(Person p : pList){

System.out.println(p);

}

} finally{

session.close();

}

MybatisTest1.java的内容如下：

package com.rl.test;

import java.io.InputStream;

import java.util.Date;

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.Person;

/**

* mybatis的数据库表的变更

public class MybatisTest1 {

SqlSessionFactory sessionFactory;

@Before

public void setUp() throws Exception {

InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");

sessionFactory = new SqlSessionFactoryBuilder().build(in);

}

/**

* 数据插入主键返回

* public void saveOrder(Orders order, List<OrderDetail> detailList){

orderDao.save(order);

for(OrderDetail detail : detailList){

detail.setOrderId(order.getOrderId());

detailDao.save(detail)

}

@Test

public void insert(){

SqlSession session = sessionFactory.openSession();

Person p = new Person();

//p.setPersonId(3);

p.setName("武松");

p.setGender("0");

p.setPersonAddr("阳谷县");

p.setBirthday(new Date());

try {

session.insert("com.rl.mapper.PersonMapper.insert", p);

//库表的变更都需要提交

session.commit();

} catch (Exception e) {

e.printStackTrace();

session.rollback();

}finally{

session.close();

}

/**

* 修改，将id是3的记录改成

@Test

public void update(){

SqlSession session = sessionFactory.openSession();

Person p = new Person();

p.setPersonId(3);

p.setName("陆虞候");

p.setGender("0");

p.setPersonAddr("阳谷县");

p.setBirthday(new Date());

try {

session.update("com.rl.mapper.PersonMapper.update", p);

//库表的变更都需要提交

session.commit();

} catch (Exception e) {

e.printStackTrace();

session.rollback();

}finally{

session.close();

}

/**

* 删除

@Test

public void delete(){

SqlSession session = sessionFactory.openSession();

try {

session.delete("com.rl.mapper.PersonMapper.delete", 4);

//库表的变更都需要提交

session.commit();

} catch (Exception e) {

e.printStackTrace();

session.rollback();

}finally{

session.close();

}

MybatisTest2.java

package com.rl.test;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.Date;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

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.Person;

/**

* mybatis的动态sql

public class MybatisTest2 {

SqlSessionFactory sessionFactory;

@Before

public void setUp() throws Exception {

InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");

sessionFactory = new SqlSessionFactoryBuilder().build(in);

}

@Test

public void selectPersonByCondition() {

//创建session对象

SqlSession session = sessionFactory.openSession();

try {

Map<String, Object> map = new HashMap<String, Object>();

map.put("name", "安");

map.put("gender", "0");

//map.put("personAddr", "东京");

//map.put("birthday", new Date());

//第一个参数：指定要执行的sql语法是namespace.sql的id，第二个参数sql要接收的参数

List<Person> pList = session.selectList("com.rl.mapper.PersonMapper.selectPersonByCondition", map);

for(Person p : pList){

System.out.println(p);

}

} finally{

session.close();

}

@Test

public void dynamicUpdate(){

SqlSession session = sessionFactory.openSession();

Person p = new Person();

p.setPersonId(3);

p.setName("陆虞候");

p.setGender("0");

//p.setPersonAddr("阳谷县");

//p.setBirthday(new Date());

try {

session.update("com.rl.mapper.PersonMapper.dynamicUpdate", p);

//库表的变更都需要提交

session.commit();

} catch (Exception e) {

e.printStackTrace();

session.rollback();

}finally{

session.close();

}

/**

* foreach的用法

@Test

public void selectPersonByIn() {

//创建session对象

SqlSession session = sessionFactory.openSession();

try {

Map<String, Object> map = new HashMap<String, Object>();

/*List list = new ArrayList();

list.add(1);

list.add(2);

list.add(3);*/

String [] list = {"1","2","3"};

map.put("ids", list);

List<Person> pList = session.selectList("com.rl.mapper.PersonMapper.selectPersonByIn", map);

for(Person p : pList){

System.out.println(p);

}

} finally{

session.close();

}

/**

* 批量插入

@Test

public void insertBatch(){

SqlSession session = sessionFactory.openSession();

Map<String,Object> map = new HashMap<String,Object>();

List<Person> pList= new ArrayList<Person>();

try {

for(int i = 0; i < 1000009; i++){

Person p = new Person();

p.setName("武松"+i);

p.setGender("0");

p.setPersonAddr("阳谷县");

p.setBirthday(new Date());

pList.add(p);

if(i%100 == 0){

map.put("pList", pList);

session.insert("com.rl.mapper.PersonMapper.insertBatch", map);

pList.clear();

}

map.put("pList", pList);

session.insert("com.rl.mapper.PersonMapper.insertBatch", map);

//库表的变更都需要提交

session.commit();

} catch (Exception e) {

e.printStackTrace();

session.rollback();

}finally{

session.close();

}

@Test

public void deleteBatch(){

SqlSession session = sessionFactory.openSession();

Map<String,Object> map = new HashMap<String,Object>();

List<Integer> ids= new ArrayList<Integer>();

try {

for(int i = 106; i < 1000115; i++){

ids.add(i);

if(i%100 == 0){

map.put("ids", ids);

session.delete("com.rl.mapper.PersonMapper.deleteBatch", map);

ids.clear();

}

map.put("ids", ids);

session.insert("com.rl.mapper.PersonMapper.deleteBatch", map);

//库表的变更都需要提交

session.commit();

} catch (Exception e) {

e.printStackTrace();

session.rollback();

}finally{

session.close();

}

MybatisTest3.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;

import com.rl.model1.Role;

/**

*mybatis的关联查询

publicclass MybatisTest3 {

SqlSessionFactory sessionFactory;

@Before

publicvoid setUp()throws Exception {

InputStream in = Resources.getResourceAsStream("sqlMapConfig.xml");

sessionFactory =new SqlSessionFactoryBuilder().build(in);

}

/**

* 一对多关联查询

@Test

publicvoid selectPersonAndOrderByPId() {

//创建session对象

SqlSession session = sessionFactory.openSession();

try {

//第一个参数：指定要执行的sql语法是namespace.sql的id，第二个参数sql要接收的参数

Person person = session.selectOne("com.rl.mapper.PersonMapper.selectPersonAndOrderByPId", 1);

System.out.println(person);

} finally{

session.close();

}

/**

* 查询Person下的所有订单和订单下的明细

@Test

publicvoid selectPersonOrderAndDetailByPId() {

//创建session对象

SqlSession session = sessionFactory.openSession();

try {

//第一个参数：指定要执行的sql语法是namespace.sql的id，第二个参数sql要接收的参数

Person person = session.selectOne("com.rl.mapper.PersonMapper.selectPersonOrderAndDetailByPId", 1);

System.out.println(person);

} finally{

session.close();

}

/**

* 多对多查询从Person端出发

@Test

publicvoid selectPersonAndRoleByPId() {

//创建session对象

SqlSession session = sessionFactory.openSession();

try {

//第一个参数：指定要执行的sql语法是namespace.sql的id，第二个参数sql要接收的参数

Person person = session.selectOne("com.rl.mapper.PersonMapper.selectPersonAndRoleByPId", 1);

System.out.println(person);

} finally{

session.close();

}

/**

* 多对多查询从角色端来看

@Test

publicvoid selectRoleAndPersonByRId() {

//创建session对象

SqlSession session = sessionFactory.openSession();

try {

//第一个参数：指定要执行的sql语法是namespace.sql的id，第二个参数sql要接收的参数

Role role = session.selectOne("com.rl.mapper.RoleMapper.selectRoleAndPersonByRId", 1);

System.out.println(role);

} finally{

session.close();

}

/**

* 多对一的关联查询

*这里的mapper配置文件在后续的博文中定义。

@Test

publicvoid selectPersonByOrderId() {

//创建session对象

SqlSession session = sessionFactory.openSession();

try {

Orders order = session.selectOne("com.rl.mapper.OrdersMapper.selectPersonByOrderId", 1);

System.out.println(order);

} finally{

session.close();

}

/**

* 多对一和一对多混合查询

*这里的mapper配置文件在后续的博文中定义。

@Test

publicvoid selectPersonAndDetailByOrderId() {

//创建session对象

SqlSession session = sessionFactory.openSession();

try {

Orders order = session.selectOne("com.rl.mapper.OrdersMapper.selectPersonAndDetailByOrderId", 1);

System.out.println(order);

} finally{

session.close();

}