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的繼承，多對多查詢，延遲加載）：
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
id:sql語句的唯一的辨別不能重複
parametertype:sql要接收的資料類型
resulttype：sql所傳回的資料類型
usecache:控制目前的這個sql是否使用二級緩存
<selectid="selectpersonbyid"parametertype="int"resultmap="baseresultmap"usecache="true">
select * from person t where t.person_id = #{id}
<selectid="selectpersoncount"resulttype="int">
select count(*) from person
<!--這裡引用了上面的sql片段
<selectid="selectpersonall"resultmap="baseresultmap">
select <includerefid="columns"/>
from person
可以使用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}
]]>
使用查詢對象的get方法來接收參數（也就是屬性名）
<selectid="selectpersonbyparams1"parametertype="qc"resultmap="baseresultmap">
模糊查詢使用${} 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
and t.gender = #{gender}
<iftest="personaddr
and t.person_addr like '%${personaddr}%'
<iftest="birthday
<![cdata[
and t.birthday < #{birthday}
]]>
</where>
動态修改
<set>标簽可以去掉最後一個逗号
flushcache：二級緩存的重新整理的配置：預設是true：會重新整理，如果false就不重新整理緩存
<updateid="dynamicupdate"parametertype="person"flushcache="false">
update person t
<set>
t.name = #{name},
t.gender = #{gender},
t.person_addr = #{personaddr},
t.birthday = #{birthday}
</set>
where t.person_id = #{personid}
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>
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">
<foreachcollection="plist"item="person"separator=",">
(#{person.personid}, #{person.name}, #{person.gender}, #{person.personaddr}, #{person.birthday})
<deleteid="deletebatch"parametertype="map">
delete from person where person_id in
<!-- ===============================關聯查詢==================
<!--一對多
<resultmaptype="person"id="selectpersonandorderbypidrm">
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>
extends:resultmap的繼承
<resultmaptype="person"id="selectpersonandorderbypidrm1"extends="baseresultmap">
<resultmaptype="person"id="selectpersonorderanddetailbypidrm"extends="baseresultmap">
<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>
<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"/>
<selectid="selectpersonandorderbypid"parametertype="int"resultmap="selectpersonandorderbypidrm1">
select * from person p, orders o where p.person_id = o.person_id and p.person_id = #{personid}
<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}
<!--多對多從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
<!-- =========================延遲加載========================
<resultmaptype="person"id="selectpersonbyidlazyrm"extends="baseresultmap">
column：主sql的一列作為子sql的參數
select：指定子sql的位置
<collectionproperty="orderslist"column="person_id"select="com.rl.mapper.ordersmapper.selectorderbypersonid">
<selectid="selectpersonbyidlazy"parametertype="int"resultmap="selectpersonbyidlazyrm">
select * from person t where t.person_id = #{personid}
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"
column="descript"
property="descript"
</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"
delete from role
</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>
id="insertselective"
insert into role
<trim
prefix="("
suffix=")"
suffixoverrides=","
<if
test="roleid != null"
role_id,
</if>
test="rolename != null"
role_name,
test="descript != null"
descript,
</trim>
prefix="values ("
#{roleid,jdbctype=integer},
#{rolename,jdbctype=varchar},
#{descript,jdbctype=varchar},
<update
id="updatebyprimarykeyselective"
update role
<set
role_name = #{rolename,jdbctype=varchar},
descript = #{descript,jdbctype=varchar},
</set>
</update>
id="updatebyprimarykey"
set role_name = #{rolename,jdbctype=varchar},
descript = #{descript,jdbctype=varchar}
id="selectroleandpersonbyridrm"
extends="baseresultmap">
<collection
property="personlist"
oftype="person">
<id
column="person_id"
property="personid"/>
column="name"
property="name"/>
column="gender"
property="gender"/>
column="person_addr"
property="personaddr"/>
column="birthday"
property="birthday"/>
</collection>
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}
ordersmapper.xml
namespace="com.rl.mapper.ordersmapper"
type="com.rl.model1.orders"
column="order_id"
property="orderid"
property="personid"
column="total_price"
property="totalprice"
jdbctype="real"
column="addr"
property="addr"
order_id, person_id, total_price, addr
from orders
where order_id = #{orderid,jdbctype=integer}
delete from orders
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 into orders
test="orderid != null"
order_id,
test="personid != null"
person_id,
test="totalprice != null"
total_price,
test="addr != null"
addr,
#{orderid,jdbctype=integer},
#{personid,jdbctype=integer},
#{totalprice,jdbctype=real},
#{addr,jdbctype=varchar},
update orders
person_id = #{personid,jdbctype=integer},
total_price = #{totalprice,jdbctype=real},
addr = #{addr,jdbctype=varchar},
set person_id = #{personid,jdbctype=integer},
addr = #{addr,jdbctype=varchar}
id="selectpersonbyorderidrm"
<!--
association:多對一的關聯映射
property：多的一端所屬的一的一端類的屬性名
javatype：一的一端的資料類型
-->
<association
property="person"
javatype="person">
</association>
id="selectpersonanddetailbyorderidrm"
extends="selectpersonbyorderidrm">
property="detaillist"
oftype="com.rl.model1.orderdetail">
column="detail_id"
property="detailid"
<result
column="price"
property="price"
column="quantity"
property="quantity"
column="item_name"
property="itemname"
多對一和一對多的混合查詢的resultmap association要放在collection的前面
id="selectpersonanddetailbyorderidrm1"
id="selectpersonbyorderid"
resultmap="selectpersonbyorderidrm">
select * from orders o, person p where o.person_id = p.person_id and o.order_id = #{orderid}
id="selectpersonanddetailbyorderid"
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}
延遲加載的子sql，傳遞過來的參數是person_id
id="selectorderbypersonid"
resultmap="baseresultmap">
select * from orders t where t.person_id = #{personid}
id="selectorderbyidlazyrm"
select="com.rl.mapper.personmapper.selectpersonbyid"></association>
id="selectorderbyidlazy1rm"
<collection
select="com.rl.mapper.orderdetailmapper.selectdetailbyorderid"></collection>
id="selectorderbyidlazy"
resultmap="selectorderbyidlazyrm">
select * from orders t where t.order_id = #{orderid}
</select>
查詢訂單的所屬人和訂單明細，延遲加載
id="selectorderbyidlazy1"
resultmap="selectorderbyidlazy1rm">
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來查詢
public void selectpersonbyid1() {
person person
= session.selectone("com.rl.mapper.personmapper.selectpersonbyid", 1);
查詢表的記錄數
public void selectpersoncount() {
integer count = session.selectone("com.rl.mapper.personmapper.selectpersoncount");
system.out.println(count);
}
查詢所有person
public void selectpersonall() {
//查詢集合的時候需要使用selectlist
list<person> plist = session.selectlist("com.rl.mapper.personmapper.selectpersonall");
for(person p : plist){
system.out.println(p);
}
使用多個參數用map方式來查詢
public void selectpersonbyparams() {
map<string, object> map = new hashmap<string, object>();
map.put("gender",0);
map.put("birthday", new date());
list<person> plist = session.selectlist("com.rl.mapper.personmapper.selectpersonbyparams",map);
使用多個參數查詢資料，使用查詢對象的方式
public void selectpersonbyparams1() {
querycondition qc = new querycondition();
qc.setgender("0");
qc.setbirthday(new date());
list<person> plist = session.selectlist("com.rl.mapper.personmapper.selectpersonbyparams1",qc);
模糊查詢
public void selectpersonbylike() {
qc.setname("武");
list<person> plist = session.selectlist("com.rl.mapper.personmapper.selectpersonbylike",qc);
}
mybatistest1.java的内容如下：
* mybatis的資料庫表的變更
public class mybatistest1 {
資料插入主鍵傳回
* public void saveorder(orders order, list<orderdetail> detaillist){
orderdao.save(order);
for(orderdetail detail : detaillist){
detail.setorderid(order.getorderid());
detaildao.save(detail)
public void insert(){
person p = new person();
//p.setpersonid(3);
p.setname("武松");
p.setgender("0");
p.setpersonaddr("陽谷縣");
p.setbirthday(new date());
session.insert("com.rl.mapper.personmapper.insert", p);
//庫表的變更都需要送出
session.commit();
} catch (exception e) {
e.printstacktrace();
session.rollback();
}finally{
修改，将id是3的記錄改成
public void update(){
p.setpersonid(3);
p.setname("陸虞候");
session.update("com.rl.mapper.personmapper.update", p);
删除
public void delete(){
session.delete("com.rl.mapper.personmapper.delete", 4);
mybatistest2.java
import java.util.arraylist;
* mybatis的動态sql
public class mybatistest2 {
public void selectpersonbycondition() {
map.put("name", "安");
map.put("gender", "0");
//map.put("personaddr", "東京");
//map.put("birthday", new date());
list<person> plist
= session.selectlist("com.rl.mapper.personmapper.selectpersonbycondition", map);
public void dynamicupdate(){
//p.setpersonaddr("陽谷縣");
//p.setbirthday(new date());
session.update("com.rl.mapper.personmapper.dynamicupdate", p);
* foreach的用法
public void selectpersonbyin() {
/*list list = new arraylist();
list.add(1);
list.add(2);
list.add(3);*/
string [] list = {"1","2","3"};
map.put("ids", list);
= session.selectlist("com.rl.mapper.personmapper.selectpersonbyin", map);
批量插入
public void insertbatch(){
map<string,object> map = new hashmap<string,object>();
list<person> plist= new arraylist<person>();
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);
public void deletebatch(){
list<integer> ids= new arraylist<integer>();
for(int i = 106; i < 1000115; i++){
ids.add(i);
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);
mybatistest3.java的内容如下：
import com.rl.model1.orders;
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
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下的所有訂單和訂單下的明細
selectpersonorderanddetailbypid() {
= session.selectone("com.rl.mapper.personmapper.selectpersonorderanddetailbypid",
多對多查詢從person端出發
selectpersonandrolebypid() {
= session.selectone("com.rl.mapper.personmapper.selectpersonandrolebypid",
多對多查詢從角色端來看
selectroleandpersonbyrid() {
role role
= session.selectone("com.rl.mapper.rolemapper.selectroleandpersonbyrid",
system.out.println(role);
多對一的關聯查詢
*這裡的mapper配置檔案在後續的博文中定義。
selectpersonbyorderid() {
orders order
= session.selectone("com.rl.mapper.ordersmapper.selectpersonbyorderid",
system.out.println(order);
多對一和一對多混合查詢
*這裡的mapper配置檔案在後續的博文中定義。
selectpersonanddetailbyorderid() {
= session.selectone("com.rl.mapper.ordersmapper.selectpersonanddetailbyorderid",
![mybatis_入門程式Mybatis入門[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)