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>
<!--这里引用了上面的sql片段 --> <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>
<!-- =============================动态sql================================== --> <!-- 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}), (#{personId}, #{name}, #{gender}, #{personAddr}, #{birthday}), (#{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> </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>
<!--多对多从Person一端看 --> <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" >
<mapper namespace="com.rl.mapper.RoleMapper" >
<resultMap id="BaseResultMap" type="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" />
</resultMap>
<sql id="Base_Column_List" >
ROLE_ID, ROLE_NAME, DESCRIPT
</sql>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from role
where ROLE_ID = #{roleId,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from role
where ROLE_ID = #{roleId,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.rl.model1.Role" >
insert into role (ROLE_ID, ROLE_NAME, DESCRIPT
)
values (#{roleId,jdbcType=INTEGER}, #{roleName,jdbcType=VARCHAR}, #{descript,jdbcType=VARCHAR}
)
</insert>
<insert id="insertSelective" parameterType="com.rl.model1.Role" >
insert into role
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="roleId != null" >
ROLE_ID,
</if>
<if test="roleName != null" >
ROLE_NAME,
</if>
<if test="descript != null" >
DESCRIPT,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="roleId != null" >
#{roleId,jdbcType=INTEGER},
</if>
<if test="roleName != null" >
#{roleName,jdbcType=VARCHAR},
</if>
<if test="descript != null" >
#{descript,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.rl.model1.Role" >
update role
<set >
<if test="roleName != null" >
ROLE_NAME = #{roleName,jdbcType=VARCHAR},
</if>
<if test="descript != null" >
DESCRIPT = #{descript,jdbcType=VARCHAR},
</if>
</set>
where ROLE_ID = #{roleId,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.rl.model1.Role" >
update role
set ROLE_NAME = #{roleName,jdbcType=VARCHAR},
DESCRIPT = #{descript,jdbcType=VARCHAR}
where ROLE_ID = #{roleId,jdbcType=INTEGER}
</update>
<resultMap type="com.rl.model1.Role" id="selectRoleAndPersonByRIdRM" extends="BaseResultMap">
<collection property="personList" ofType="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"/>
</collection>
</resultMap>
<select id="selectRoleAndPersonByRId" parameterType="int" resultMap="selectRoleAndPersonByRIdRM">
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" >
<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>
3配置sqlMapConfig.xml中的<mappers>配置：
| <!--集中管理表的映射文件 --> <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(); } } } |
