MyBatis学习笔记(二)之关联关系

create table t_wife(
id int primary key auto_increment,
wife_name varchar(),
fk_husband_id int
);
create table t_husband(
id int primary key auto_increment,
husband_name varchar()
);
insert into t_husband values (null,'hello');
insert into t_wife values(null,'kitty',) 

package com.cy.mybatis.beans;
import java.io.serializable;
/**
* one to one
* @author acer
*
*/
public class husbandbean implements serializable{
private static final long serialversionuid = l;
private integer id;
private string name;
private wifebean wife;
public husbandbean() {
super();
}
public husbandbean(integer id, string name, wifebean wife) {
super();
this.id = id;
this.name = name;
this.wife = wife;
}
public integer getid() {
return id;
}
public void setid(integer id) {
this.id = id;
}
public string getname() {
return name;
}
public void setname(string name) {
this.name = name;
}
public wifebean getwife() {
return wife;
}
public void setwife(wifebean wife) {
this.wife = wife;
}
@override
public string tostring() {
return "husband [id=" + id + ", name=" + name + ", wife=" + wife + "]";
}
}

package com.cy.mybatis.beans;
import java.io.serializable;
/**
* one to one
* @author acer
*
*/
public class wifebean implements serializable{
private static final long serialversionuid = l;
private integer id;
private string name;
private husbandbean husband;
public wifebean() {
super();
}
public wifebean(integer id, string name, husbandbean husband) {
super();
this.id = id;
this.name = name;
this.husband = husband;
}
public integer getid() {
return id;
}
public void setid(integer id) {
this.id = id;
}
public string getname() {
return name;
}
public void setname(string name) {
this.name = name;
}
public husbandbean gethusband() {
return husband;
}
public void sethusband(husbandbean husband) {
this.husband = husband;
}
@override
public string tostring() {
return "wife [id=" + id + ", name=" + name + ", husband=" + husband
+ "]";
}
} 

package com.cy.mybatis.mapper;
import com.cy.mybatis.beans.husbandbean;
public interface husbandmapper {
/**
* 根据id查询丈夫信息
* @param id
* @return
* @throws exception
*/
public husbandbean selecthusbandbyid (int id) throws exception;
/**
* 根据id查询丈夫与妻子信息
* @param id
* @return
* @throws exception
*/
public husbandbean selecthusbandandwife(int id) throws exception;
} 

<?xml version="." encoding="utf-"?>
<!doctype mapper public "-//mybatis.org/dtd mapper ." "http://mybatis.org/dtd/mybatis--mapper.dtd">
<mapper namespace="com.cy.mybatis.mapper.husbandmapper">
<resultmap type="husbandbean" id="husbandandwife">
<id property="id" column="id" javatype="java.lang.integer"/>
<result property="name" column="name" javatype="java.lang.string"/>
<!-- association – 一个复杂的类型关联；许多结果将包成这种类型
嵌入结果映射 – 结果映射自身的关联，或者参考一个
column="id" 这里的id指的是在t_wife表来的主键id 
这个查询妻子，所以在妻子mapper里有个方法 -->
<association property="wife" column="id" javatype="wifebean" select="com.cy.mybatis.mapper.wifemapper.selectwifebyhusbandid" ></association>
</resultmap>
<!-- resulttype 返回类型 从这条语句中返回的期望类型的类的完全限定名或别名
。-->
<select id="selecthusbandbyid" resulttype="husbandbean">
select * from t_husband where id=#{id}
</select> 
<!-- resultmap 命名引用外部的 resultmap。返回的是一个集合。-->
<select id="selecthusbandandwife" resultmap="husbandandwife">
select * from t_husband where id=#{id}
</select>
</mapper> 

<?xml version="." encoding="utf-"?>
<!doctype mapper public "-//mybatis.org/dtd mapper ." "http://mybatis.org/dtd/mybatis--mapper.dtd">
<mapper namespace="com.cy.mybatis.mapper.wifemapper">
<select id="selectwifebyhusbandid" resulttype="wifebean">
select * from t_wife where fk_husband_id = #{id}
</select> 
</mapper> 

package com.cy.mybatis.service;
import org.apache.ibatis.session.sqlsession;
import com.cy.mybatis.beans.husbandbean;
import com.cy.mybatis.mapper.husbandmapper;
import com.cy.mybatis.tools.dbtools;
public class onetooneservice {
public static void main(string[] args) {
selecthusbandandwife();
}
private static void selecthusbandandwife() {
sqlsession session = dbtools.getsession();
husbandmapper hm = session.getmapper(husbandmapper.class);
try {
husbandbean husband = hm.selecthusbandandwife();
system.out.println(husband);
session.commit();
} catch (exception e) {
e.printstacktrace();
}
}
} 

create table t_key(
id int primary key auto_increment,
key_name varchar(),
fk_lock_id int 
);
create table t_lock(
id int primary key auto_increment,
lock_name varchar()
); 

package com.cy.mybatis.beans;
import java.io.serializable;
/**
* manytoone
* 
*
*/
public class keybean implements serializable {
private static final long serialversionuid = l;
private integer id;
private string key;
private lockbean lock;
public keybean() {
super();
}
public keybean(integer id, string key, lockbean lock) {
super();
this.id = id;
this.key = key;
this.lock = lock;
}
public integer getid() {
return id;
}
public void setid(integer id) {
this.id = id;
}
public string getkey() {
return key;
}
public void setkey(string key) {
this.key = key;
}
public lockbean getlock() {
return lock;
}
public void setlock(lockbean lock) {
this.lock = lock;
}
@override
public string tostring() {
return "keybean [id=" + id + ", key=" + key + ", lock=" + lock + "]";
}
} 

lockbean.java

package com.cy.mybatis.beans;
import java.io.serializable;
import java.util.list;
/**
* onetomany
* 
*
*/
public class lockbean implements serializable{
private static final long serialversionuid = l;
private integer id;
private string lock;
private list<keybean> keys;
public lockbean() {
super();
}
public lockbean(integer id, string lock, list<keybean> keys) {
super();
this.id = id;
this.lock = lock;
this.keys = keys;
}
public integer getid() {
return id;
}
public void setid(integer id) {
this.id = id;
}
public string getlock() {
return lock;
}
public void setlock(string lock) {
this.lock = lock;
}
public list<keybean> getkeys() {
return keys;
}
public void setkeys(list<keybean> keys) {
this.keys = keys;
}
@override
public string tostring() {
return "lockbean [id=" + id + ", keys=" + keys + ", lock=" + lock + "]";
}
} 

keymapper.java
package com.cy.mybatis.mapper;
import java.util.list;
import org.apache.ibatis.annotations.param;
import com.cy.mybatis.beans.keybean;
public interface keymapper {
/**
* 批量添加钥匙
* @return
* 提倡 这样使用 @param("keys")
*/
public int batchsavekeys(@param("keys")list<keybean> keys);
} 

lockmapper.java

package com.cy.mybatis.mapper;
import org.apache.ibatis.annotations.param;
import com.cy.mybatis.beans.lockbean;
public interface lockmapper {
/**
* 添加锁
* @param lock
* @return
*/
public int savelock(@param("lock")lockbean lock);
/**
* 根据id查询锁的资料
* @param id
* @return
*/
public lockbean findlockbyid(int id);
/**
* 根据id查询锁与钥匙的资料
* onemany
* @param id
* @return
*/
public lockbean findlockandkeys(int id);
} 

<?xml version="." encoding="utf-"?>
<!doctype mapper public "-//mybatis.org/dtd mapper ." "http://mybatis.org/dtd/mybatis--mapper.dtd">
<mapper namespace="com.cy.mybatis.mapper.keymapper">
<resultmap id="keymap" type="keybean">
<id property="id" column="id" javatype="java.lang.integer"/>
<result property="key" column="key_name" javatype="java.lang.string"/>
</resultmap>

<!--collection 为用于遍历的元素（必选），支持数组、list、set -->
<!-- item 表示集合中每一个元素进行迭代时的别名. -->
<!--separator表示在每次进行迭代之间以什么符号作为分隔 符. -->
<insert id="batchsavekeys">
insert into t_key values 
<foreach collection="keys" item="key" separator=",">
(null,#{key.key},#{key.lock.id})
</foreach>
</insert>
<select id="findkeysbylockid" resultmap="keymap">
select * from t_key where fk_lock_id = #{id}
</select>
</mapper> 


lockmapper.xml

<?xml version="." encoding="utf-"?>
<!doctype mapper public "-//mybatis.org/dtd mapper ." "http://mybatis.org/dtd/mybatis--mapper.dtd">
<mapper namespace="com.cy.mybatis.mapper.lockmapper">
<!--自定义返回类型 -->
<resultmap id="lockmap" type="lockbean">
<id property="id" column="id" javatype="java.lang.integer"/>
<result property="lock" column="lock_name" javatype="java.lang.string"/>
</resultmap>
<!--自定义返回类型 -->
<resultmap id="lockandkeysmap" type="lockbean">
<id property="id" column="id" javatype="java.lang.integer"/>
<result property="lock" column="lock_name" javatype="java.lang.string"/>
<collection property="keys" column="id" select="com.cy.mybatis.mapper.keymapper.findkeysbylockid"></collection>
</resultmap>
<insert id="savelock">
insert into t_lock values (null,#{lock.lock}) 
</insert>
<select id="findlockbyid" resultmap="lockmap">
select * from t_lock where id= #{id}
</select>
<select id="findlockandkeys" resultmap="lockandkeysmap">
select * from t_lock where id= #{id}
</select>
</mapper> 

package com.cy.mybatis.service;
import java.util.arraylist;
import java.util.list;
import org.apache.ibatis.session.sqlsession;
import com.cy.mybatis.beans.keybean;
import com.cy.mybatis.beans.lockbean;
import com.cy.mybatis.mapper.keymapper;
import com.cy.mybatis.mapper.lockmapper;
import com.cy.mybatis.tools.dbtools;
public class onetomanyservice {
public static void main(string[] args) {
// savelock();
// batchsavekeys();
findlockandkeys();
}
private static void findlockandkeys() {
sqlsession session = dbtools.getsession();
lockmapper lm = session.getmapper(lockmapper.class);
lockbean lock = lm.findlockandkeys();
system.out.println(lock);
}
private static void batchsavekeys() {
sqlsession session = dbtools.getsession();
lockmapper lm = session.getmapper(lockmapper.class);
keymapper km = session.getmapper(keymapper.class);
lockbean lock = lm.findlockbyid();
list<keybean> keys = new arraylist<keybean>();
for(int i = ; i < ; i++){
keybean key = new keybean(null, "钥匙"+i, lock);
keys.add(key);
}
km.batchsavekeys(keys);
session.commit();
}
private static void savelock() {
sqlsession session = dbtools.getsession();
lockmapper lm = session.getmapper(lockmapper.class);
lockbean lock = new lockbean(null, "锁", null);
lm.savelock(lock);
session.commit();
}
} 

package com.cy.mybatis.beans;
import java.util.list;
/**
* 定义一个分页对象
* 
* @author
* 
*/
public class pager {
private int pageno;// 当前页码
private int pagetotal;// 总页码
private int rowstotal;// 总条数
private int pagesize;// 每页显示条数
private list<object> list;// 返回的数据集合
public int getpageno() {
return pageno;
}
public void setpageno(int pageno) {
this.pageno = pageno;
}
public int getpagetotal() {
return pagetotal;
}
public void setpagetotal(int pagetotal) {
this.pagetotal = pagetotal;
}
public int getrowstotal() {
return rowstotal;
}
public void setrowstotal(int rowstotal) {
this.rowstotal = rowstotal;
pagetotal = rowstotal % pagesize == ? rowstotal / pagesize : rowstotal / pagesize + ;
}
public int getpagesize() {
return pagesize;
}
public void setpagesize(int pagesize) {
this.pagesize = pagesize;
}
public list<?> getlist() {
return list;
}
public void setlist(list<object> list) {
this.list = list;
}
@override
public string tostring() {
return "pager [pageno=" + pageno + ", pagetotal=" + pagetotal
+ ", rowstotal=" + rowstotal + ", pagesize=" + pagesize
+ ", list=" + list + "]";
}
} 


usermapper.java接口。

package com.cy.mybatis.mapper;
import java.util.list;
import java.util.map;
import org.apache.ibatis.annotations.param;
import com.cy.mybatis.beans.userbean;
public interface usermapper {
/**
* 新增用戶
* @param user
* @return
* @throws exception
*/
public int insertuser(@param("user")userbean user) throws exception;
/**
* 修改用戶
* @param user
* @param id
* @return
* @throws exception
*/
public int updateuser (@param("u")userbean user,@param("id")int id) throws exception;
/**
* 刪除用戶
* @param id
* @return
* @throws exception
*/
public int deleteuser(int id) throws exception;
/**
* 根据id查询用户信息
* @param id
* @return
* @throws exception
*/
public userbean selectuserbyid(int id) throws exception;
/**
* 查询所有的用户信息
* @return
* @throws exception
*/
public list<userbean> selectalluser() throws exception;
/**
* 批量增加
* @param user
* @return
* @throws exception
*/
public int batchinsertuser(@param("users")list<userbean> user) throws exception;
/**
* 批量删除
* @param list
* @return
* @throws exception
*/
public int batchdeleteuser(@param("list")list<integer> list) throws exception;
/**
* 分页查询数据
* @param parma
* @return
* @throws exception
*/
public list<userbean> pageruser(map<string, object> parmas) throws exception;
/**
* 
* 分页统计数据
* @param parma
* @return
* @throws exception
*/
public int countuser(map<string, object> parmas) throws exception;
} 

xml文件

<?xml version="." encoding="utf-"?>
<!doctype mapper public "-//mybatis.org/dtd mapper ." "http://mybatis.org/dtd/mybatis--mapper.dtd">
<mapper namespace="com.cy.mybatis.mapper.usermapper">
<!-- 自定义返回结果集 -->
<resultmap id="usermap" type="userbean">
<id property="id" column="id" javatype="java.lang.integer"></id>
<result property="username" column="username" javatype="java.lang.string"></result>
<result property="password" column="password" javatype="java.lang.string"></result>
<result property="account" column="account" javatype="java.lang.double"></result>
</resultmap>
<!-- 在各种标签中的id属性必须和接口中的方法名相同 ， id属性值必须是唯一的，不能够重复使用。parametertype属性指明查询时使用的参数类型，resulttype属性指明查询返回的结果集类型--> 
<!-- usegeneratedkeys：（ 仅 对 insert 有 用 ） 这 会 告 诉 mybatis 使 用 jdbc 的getgeneratedkeys 
方法来取出由数据（比如：像 mysql 和 sqlserver 这样的数据库管理系统的自动递增字段）内部生成的主键。默认值： false。 --> 
<!--keyproperty： （仅对 insert有用）标记一个属性， mybatis 会通过 getgeneratedkeys或者通过 insert 语句的 selectkey 子元素设置它的值。默认：不设置。 -->
<!--#{}中的内容，为占位符，当参数为某个javabean时，表示放置该bean对象的属性值 -->
<insert id="insertuser" usegeneratedkeys="true" keyproperty="user.id">
insert into t_user (username,password,account) values (#{user.username},#{user.password},#{user.account})
</insert>
<update id="updateuser">
update t_user set username=#{u.username},password=#{u.password},account=#{u.account} where id=#{id}
</update>
<delete id="deleteuser" parametertype="int">
delete from t_user where id=#{id} 
</delete>
<select id="selectuserbyid" parametertype="int" resultmap="usermap">
select * from t_user where id=#{id}
</select>
<select id="selectalluser" resultmap="usermap">
select * from t_user
</select>
<!-- 批量操作和foreach标签 -->
<insert id="batchinsertuser" parametertype="java.util.list">
insert into t_user values 
<foreach collection="users" item="users" separator=",">
(null,#{users.username},#{users.password},#{users.account})
</foreach>
</insert>
<delete id="batchdeleteuser">
delete from t_user where id in (
<foreach collection="list" item="list" separator=",">
#{id}
</foreach>
)
</delete>
<!--collection 为用于遍历的元素（必选），支持数组、list、set -->
<!-- item 表示集合中每一个元素进行迭代时的别名. -->
<!--separator表示在每次进行迭代之间以什么符号作为分隔 符. -->
<select id="pageruser" parametertype="java.util.map" resultmap="usermap">
select * from t_user where =
<if test="username!=null">
and username like '%${username}%'
</if>
limit ${index},${pagesize} 
</select>
<select id="countuser" parametertype="java.util.map" resulttype="int">
select count(*) from t_user where = 
<if test="username != null">
and username like '%${username}%' 
</if>
</select>
</mapper> 

#在生成sql时，对于字符类型参数，会拼装引号
$在生成sql时，不会拼装引号，可用于order by之类的参数拼装

测试类

package com.cy.mybatis.service;
import java.util.arraylist;
import java.util.hashmap;
import java.util.list;
import java.util.map;
import org.apache.ibatis.session.sqlsession;
import com.cy.mybatis.beans.userbean;
import com.cy.mybatis.tools.dbtools;
import com.cy.mybatis.mapper.usermapper;
public class userservice {
/**
* @param args
*/
public static void main(string[] args) {
// insertuser();
// deleteuser();
// updateuser();
// selectuserbyid();
// selectalluser();
// batchinsertuser();
// batchdeleteuser();
// countuser();
pageruser();
}
private static void countuser() {
sqlsession session = dbtools.getsession();
usermapper mapper = session.getmapper(usermapper.class);
map<string,object> params = new hashmap<string,object>();
params.put("username", "kitty");
int index = ;
params.put("index", index);//从第几页开始。mysql是从开始的
params.put("pagesize", );//每页显示的数据条数
int count;
try {
count = mapper.countuser(params);
system.out.println(count);
} catch (exception e) {
e.printstacktrace();
}
}
private static void pageruser() {
sqlsession session = dbtools.getsession();
usermapper mapper = session.getmapper(usermapper.class);
map<string,object> params = new hashmap<string,object>();
params.put("username", "kitty");
params.put("index", );//从第几页开始。mysql是从开始的
params.put("pagesize", );//每页显示的数据条数
try {
list<userbean> u = mapper.pageruser(params);
for (userbean userbean : u) {
system.out.println("--------"+userbean);
}
} catch (exception e) {
e.printstacktrace();
}
}
private static void batchdeleteuser() {
sqlsession session = dbtools.getsession();
usermapper mapper = session.getmapper(usermapper.class);
list<integer> ids = new arraylist<integer>();
for(int i = ; i < ; i ++){
ids.add(i);
}
try {
mapper.batchdeleteuser(ids);
session.commit();
} catch (exception e) {
e.printstacktrace();
}
}
private static void batchinsertuser() {
sqlsession session = dbtools.getsession();
usermapper mapper = session.getmapper(usermapper.class);
list<userbean> users = new arraylist<userbean>();
for(int i = ; i < ; i ++){
userbean user = new userbean("kitty"+i, "", .);
users.add(user);
}
try {
mapper.batchinsertuser(users);
session.commit();
} catch (exception e) {
e.printstacktrace();
}
}
/**
* 新增用户
*/
private static void insertuser() {
sqlsession session = dbtools.getsession();
usermapper mapper = session.getmapper(usermapper.class);
userbean user = new userbean("懿", "", .);
try {
mapper.insertuser(user);
system.out.println(user.tostring());
session.commit();
} catch (exception e) {
e.printstacktrace();
session.rollback();
}
}
/**
* 删除用户
*/
private static void deleteuser(){
sqlsession session=dbtools.getsession();
usermapper mapper=session.getmapper(usermapper.class);
try {
mapper.deleteuser();
session.commit();
} catch (exception e) {
e.printstacktrace();
session.rollback();
}
}
/**
* 修改用户数据
*/
private static void updateuser(){
sqlsession session=dbtools.getsession();
usermapper mapper=session.getmapper(usermapper.class);
userbean user =new userbean("小明", "",.);
try {
mapper.updateuser(user, );
session.commit();
} catch (exception e) {
e.printstacktrace();
session.rollback();
}
}
/**
* 根据id查询用户
*/
private static void selectuserbyid(){
sqlsession session=dbtools.getsession();
usermapper mapper=session.getmapper(usermapper.class);
try {
userbean user= mapper.selectuserbyid();
system.out.println(user.tostring());
session.commit();
} catch (exception e) {
e.printstacktrace();
session.rollback();
}
}
/**
* 查询所有的用户
*/
private static void selectalluser(){
sqlsession session=dbtools.getsession();
usermapper mapper=session.getmapper(usermapper.class);
try {
list<userbean> user=mapper.selectalluser();
system.out.println(user.tostring());
session.commit();
} catch (exception e) {
e.printstacktrace();
session.rollback();
}
} 
}