直接運行demo
http://www.aygfsteel.com/Files/Good-Game/iba2.rar
數據說明：
/*
?ibatis?測試使用
?*?一對多；多對一；多對多?
*/
/*
?等級表?
?*??(外鍵)?colum?->?fid??
?
持久層類說明
? Level{
??? Integer id ;
??? String name ;
??? Level flevel ; //
維護與表level
多對一
關系屬性
(上一級level) ；
??? List<Level> clevel; //
維護與表level
一對多
關系屬性
(下一級孩子節點) ；
??? List<Luser> lusers ; //
維護與表luser 多對多 關系屬性
? }
?
*/
create
?
table
?
level
??(
????id?
int
?
not
?
null
,
????name?
varchar
(
80
)?
null
,
????fid?
int
?,
????
constraint
?pk_supplier_level?
primary
?
key
?(id),
????
constraint
?fk_item_1?
foreign
?
key
?(fid)
????????
references
?
level
?(id)
);
/*
?使用者
?? 持久層類說明
?? Luser {
???? int id ;
???? String name ;
???? List<Level> levels ;
//維護與表level 多對多 關系屬性
???? gget;sset()....
?? }
?
*/
create
?
table
?luser??(
????id?
int
?
not
?
null
,
????name?
varchar
(
80
)?
null
,
?????
constraint
?pk_supplier_luser?
primary
?
key
?(id)
);
/*
?維護?使用者?和?等級?多對多?關系
?*??????(外鍵)?colum?->?lid?;??uid
?
*/
create
?
table
?level_M2M_user??(
????id?
int
?
not
?
null
,
????lid?
int
?
not
?
null
?,
????uid?
int
?
not
?
null
?,
????
constraint
?pk_supplier_level_M2M_user?
primary
?
key
?(id),
????
constraint
?fk_item_2?
foreign
?
key
?(lid)
????????
references
?
level
?(id),
????
constraint
?fk_item_3?
foreign
?
key
?(uid)
????????
references
?luser?(id)
);
create
?
index
?levelName?
on
?
level
?(name);
insert
?
into
?
level
?(id,name,fid)?
values
(
1
,
'
root
'
,
null
);
insert
?
into
?
level
?(id,name,fid)?
values
(
2
,
'
level_1
'
,
1
);
insert
?
into
?
level
?(id,name,fid)?
values
(
3
,
'
root_2
'
,
1
);
insert
?
into
?
level
?(id,name,fid)?
values
(
4
,
'
root_1_1
'
,
2
);
insert
?
into
?
level
?(id,name,fid)?
values
(
5
,
'
root_1_2
'
,
2
);
insert
?
into
?
level
?(id,name,fid)?
values
(
6
,
'
root_1_1_1
'
,
4
);
insert
?
into
?luser?(id,name)?
values
?(
1
,
'
liukaiyi
'
?);
insert
?
into
?luser?(id,name)?
values
?(
2
,
'
good-game
'
?);
insert
?
into
?level_M2M_user?(id,uid,lid)?
values
(
1
,
1
,
1
);
insert
?
into
?level_M2M_user?(id,uid,lid)?
values
(
2
,
1
,
2
);
insert
?
into
?level_M2M_user?(id,uid,lid)?
values
(
3
,
1
,
3
);
insert
?
into
?level_M2M_user?(id,uid,lid)?
values
(
4
,
2
,
1
);
insert
?
into
?level_M2M_user?(id,uid,lid)?
values
(
5
,
2
,
5
);
insert
?
into
?level_M2M_user?(id,uid,lid)?
values
(
6
,
2
,
6
);
sql map
DaoConfig.getSqlMap() 參考最下面工具類；1.ddl 簡單些這就 使用ddl insert level <!--??
?? 測試代碼：
1.Level?level?=?new?Level();????
??? $$ Level?擴展屬性??
??? public?int?getIdentity()?{
????????synchronized?(Level.class)?{
????????????try?{
????????????????return?Integer.parseInt(?DaoConfig.getSqlMap().queryForObject("level.maxInsertId").toString()?);
????????????}?catch?(NumberFormatException?e)?{
????????????????e.printStackTrace();
????????????}?catch?(SQLException?e)?{
????????????????e.printStackTrace();
????????????}
????????}
????????return?0;
????}
2.level.setName("liukaiyi");
3.DaoConfig.getSqlMap().insert("level.addLevel",level);
4.DaoConfig.getSqlMap().insert("level.addLevel",level);
3；4行 利用 擴展屬性 id 自動增長！
-->
?????<select?id="maxInsertId"?resultClass="int"??>
?????????select?max(id)+1??from?level?
?????</select>
?????<insert?id="addLevel"?parameterClass="level"??>
?????????insert?into?level?(id,name,fid)?values(#identity#,#name#,#flevel.id#)
?????</insert>
??
2.動態組合查詢<!--
???運行測試代碼：
???Level?level?=?new?Level();?\\ map 也可以 ！
????????????level.setName("%_1%");
????????????level.setFlevel(new?Level(2));
????????????for(Level?ltmp?:?(List<Level>)DaoConfig.getSqlMap().queryForList("level.dynamicLevel",level)){
????????????????if(?ltmp.getFlevel()!=null?)
????????????????????System.out.println("f_level:"+ltmp.getFlevel().getId()+"="+ltmp.getFlevel().getName());
????????????????
????????????????System.out.println("->level:"+ltmp.getId()+"="+ltmp.getName());
????????????????
????????????????for(Level?lctmp?:?ltmp.getClevel()){
????????????????????System.out.println("????c_level:"+lctmp.getId()+"="+lctmp.getName());
????????????????}
????????????????System.out.println();
????????????}
????????????
-->
????<select?id="dynamicLevel"?parameterClass="level"?resultMap="getLevelByFid">??
?????? Select?id,name,fid?from?level??
?????? <dynamic?prepend="WHERE">??
?????????? <isNotEmpty?prepend="AND"?property="name">??
?????????? ? ? (name?like?#name#)??
??????? ?? </isNotEmpty>??
?????? ? ? <isNotEmpty?prepend="AND"?property="flevel">??
??????? ?? ??? (fid=#flevel.id#)??
????? ?? ? </isNotEmpty>??
???? ?? ?? order?by?id?desc?
?? ? ? </dynamic>??
???? </select>??
運行結果：
f_level:2=level_1
->level:5=root_1_2
f_level:2=level_1
->level:4=root_1_1
????c_level:6=root_1_1_1
3.查詢結果xml
<!--
??測試運行結果：
?????String?xmlData?=?DaoConfig.getSqlMap().
???????????????????? queryForObject("level.getLevelXmlById",new?Integer(3)).toString();
????? System.out.println(xmlData);
-->????
<select?id="getLevelXmlById"?resultClass="xml"?xmlResultName="level"??>??
?????????select?
?????????id?,
?????????name?,
?????????fid?
?????????from?level??
?????????where?id=#id#?
?????</select>
結果：
<?xml?version="1.0"?encoding="UTF-8"?>
<level>
? <ID>3</ID>
? <NAME>root_2</NAME>
? <FID>1</FID>
</level>
4.多對一（此方法可以 大概看成 hbm 中的 lazy='true' ）<!--
????運行測試代碼：
????Level?level?=?(Level)DaoConfig.getSqlMap().queryForObject("level.getLevelId",new?Integer(2));
????????????Level?fLevel?=?level.getFlevel();
????????????System.out.println(fLevel.getName());
結果：
root
-->
????<typeAlias?alias="level"?type="test.domain.Level"?/>
????<resultMap?class="level"?id="getLevelByFid">
????????<result?property="id"?column="id"?/>??
????????<result?property="name"?column="name"?/>
????????
????????<result?property="flevel"?column="fid"?select="level.getLevelId"/>
????????<result?property="clevel"?column="id"?select="level.getLevelByfId"/>
????????
????????<result?property="lusers"?column="id"?select="m2m.getLuserByLevelId"/>
????</resultMap>
????
????<select?id="getLevelId"?resultClass="level"?parameterClass="int"?resultMap="getLevelByFid"??>??
???????? select?
?????????id?,
?????????name?,
?????????fid?
?????????from?level???
?????????WHERE?id?=#id#??
?????</select>
????
4.2一對多 第2種方法（此方法可以 大概看成 hbm 中的 lazy='false' ）
<!--
????運行測試結果：
????????????????Level?level?=?(Level)DaoConfig.getSqlMap().queryForObject("level.getLevelId",new?Integer(2));
????????????Level?fLevel?=?level.getFlevel();
????????????System.out.println(fLevel.getName());
結果：
root
-->
????<select?id="getLevelIdAddFid"?resultClass="level"?parameterClass="int"??>??
?????????select?
?????????tl.id?as?id,
?????????tl.name?as?name?,
?????????tlf.id?as?"flevel.id",
?????????tlf.name?as?"flevel.name"?
?????????from?level?tl?inner?join?level?tlf?on?tl.fid=tlf.id
?????????WHERE?tl.id?=#id#??
?????</select>
5.多對一
<!--
????測試運行代碼：
??? Level?level?=?(Level)DaoConfig.getSqlMap().queryForObject("level.getLevelId",new?Integer(2));
????? for(?Level?ltmp?:?level.getClevel()?){
???????? System.out.println(ltmp.getName());
?????? }
結果：
root_1_1
root_1_2
-->
????<typeAlias?alias="level"?type="test.domain.Level"?/>
????<resultMap?class="level"?id="getLevelByFid">
????????<result?property="id"?column="id"?/>??
????????<result?property="name"?column="name"?/>
????????
????????<result?property="flevel"?column="fid"?select="level.getLevelId"/>
????????<result?property="clevel"?column="id"?select="level.getLevelByfId"/>
????????
????????<result?property="lusers"?column="id"?select="m2m.getLuserByLevelId"/>
????</resultMap>
????
????<select?id="getLevelByfId"?resultClass="level"?parameterClass="int"?resultMap="getLevelByFid"??>??
?????????select?
?????????id?,
?????????name?,
?????????fid?
?????????from?level???
?????????WHERE?fid?=#id#??
?????</select>
6.多對多
關系維護 m2m.xml
<?xml?version="1.0"?encoding="UTF-8"?>
<!DOCTYPE?sqlMap?PUBLIC?"-//ibatis.apache.org//DTD?SQL?Map?2.0//EN"
????"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap?namespace="m2m">
????<select?id="getLuserByLevelId"?resultMap="luser.getLuserByFid"?resultClass="luser"?parameterClass="int"?>??
?????????select?
?????????tlu.id?as?id,
?????????tlu.name?as?name
?????????from?
?????????????luser?tlu?inner?join?level_M2M_user?t2?on?tlu.id=t2.uid
??????????????inner?join?level?tl?on?tl.id=t2.lid
?????????where?tl.id=#id#
?????</select>?
?????
????<select?id="getLevelsByLuserId"?resultMap="level.getLevelByFid"?resultClass="level"??parameterClass="int"?>??
?????????select?
?????????tl.id?as?id,
?????????tl.name?as?name,
?????????tl.fid?as?fid
?????????from?
?????????????luser?tlu?inner?join?level_M2M_user?t2?on?tlu.id=t2.uid
??????????????inner?join?level?tl?on?tl.id=t2.lid
?????????where?tlu.id=#id#
?????</select>?
?????
?????
</sqlMap>
luser.xml
<?xml?version="1.0"?encoding="UTF-8"?>
<!DOCTYPE?sqlMap?PUBLIC?"-//ibatis.apache.org//DTD?SQL?Map?2.0//EN"
????"http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap?namespace="luser">
????<typeAlias?alias="luser"?type="test.domain.Luser"?/>
????
????<resultMap?class="luser"?id="getLuserByFid">
????????<result?property="id"?column="id"?/>??
????????<result?property="name"?column="name"?/>
????????<result?property="levels"?column="id"?select="m2m.getLevelsByLuserId"/>
????</resultMap>
????
????<select?id="getluserById"?resultClass="luser"?resultMap="getLuserByFid"?parameterClass="int"?>
????????select?id?,?name?from?luser?where?id=#id#
????</select>
</sqlMap>
level.xml
<!--
????測試運行代碼：
????????????????Luser?luser?=?(Luser)DaoConfig.getSqlMap().queryForObject("luser.getluserById",new?Integer(1));
????????????System.out.println("luser="+luser.getName());
????????????if(?luser!=null?)
????????????????for(Level?ltmp?:?luser.getLevels()?){
????????????????????System.out.println(ltmp.getName());
????????????????????for(Luser?lutmp?:?ltmp.getLusers()?){
????????????????????????System.out.println("?->"+lutmp.getName());
????????????????????}
????????????????}
結果：
luser=liukaiyi
root
?->liukaiyi
?->good-game
level_1
?->liukaiyi
root_2
?->liukaiyi
-->
????<typeAlias?alias="level"?type="test.domain.Level"?/>
????<resultMap?class="level"?id="getLevelByFid">
????????<result?property="id"?column="id"?/>??
????????<result?property="name"?column="name"?/>
????????
????????<result?property="flevel"?column="fid"?select="level.getLevelId"/>
????????<result?property="clevel"?column="id"?select="level.getLevelByfId"/>
????????
????????<result?property="lusers"?column="id"?select="m2m.getLuserByLevelId"/>
????</resultMap>