mybatis关联关系 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();
}
} 
}