mybatis--关联关系
mybatis 关联关系
我这里有 用户表（id username）
家庭住址（id address userid）userid 是用户表的id
一.多对一关系：
1. entity实体类：
package cn.happy.entity;
import java.util.ArrayList;
import java.util.List;
public class DeptOne {
private Integer id;//用户编号
private String userName;//用户名
private Coder code;//用户住址表
public Coder getCode() {
return code;
}
public void setCode(Coder code) {
this.code = code;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
}
package cn.happy.entity;
import java.util.ArrayList;
import java.util.List;
public class Coder {
private Integer id;//用户住址编号
private Integer userid;//用户表id
private String address;//用户住址表
private List<DeptOne> dept=new ArrayList<DeptOne>();//用户表集合
public List<DeptOne> getDept() {
return dept;
}
public void setDept(List<DeptOne> dept) {
this.dept = dept;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUserid() {
return userid;
}
public void setUserid(Integer userid) {
this.userid = userid;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
2.dao层 和 他的 .xml 文件
先写用户表的dao层和 .xml文件
package cn.happy.dao;
import cn.happy.entity.Dept;
import cn.happy.entity.DeptOne;
import java.util.List;
public interface IDeptOneDAO {
public DeptOne getByno(int id);
}
<?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">
<!--映射文件的根节点
namespace
-->
<mapper namespace="cn.happy.dao.IDeptOneDAO">
<resultMap id="DeptMapper" type="DeptOne">
<id column="id" property="id"></id>
<result column="userName" property="userName"></result>
<association property="code" javaType="Coder">
<id column="id" property="id"></id>
<result column="address" property="address"></result>
</association>
</resultMap>
<select id="getByno" resultMap="DeptMapper">
SELECT easybuy_user_address.id,address,easyby_user.id,userName FROM easyby_user,easybuy_user_address
where easybuy_user_address.userid=easyby_user.id AND easyby_user.id=#{id}
</select>
</mapper>
他的测试方法：
@Test
public void onefind() throws IOException {
SqlSession session = sessionFactory.getSqlSession();
IDeptOneDAO mapper = session.getMapper(IDeptOneDAO.class);
DeptOne dept = mapper.getByno(1);
System.out.println(dept.getUserName());
System.out.println(dept.getCode().getAddress());
session.close();
}
二。一对多 关联关系：
dao层和xml文件：
package cn.happy.dao;
import cn.happy.entity.Coder;
import cn.happy.entity.DeptOne;
public interface ICoderOneDAO {
//这是测试单挑sql 的方法
public Coder getByCoderId(int id);
//这是测试多条sql的方法
public Coder getByMonyCoderId(int id);
}
<?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">
<!--映射文件的根节点
namespace
-->
<mapper namespace="cn.happy.dao.ICoderOneDAO">
<!--一对多 多条sql-->
<resultMap id="CoderMonyMapper" type="Coder">
<id column="id" property="id"></id>
<result column="address" property="address"></result>
<collection property="dept" ofType="DeptOne" select="MonyFindById" column="id">
</collection>
</resultMap>
<select id="MonyFindById" resultType="DeptOne">
SELECT id,userName FROM easyby_user
where id=#{id}
</select>
<select id="getByMonyCoderId" resultMap="CoderMonyMapper">
SELECT id,address FROM easybuy_user_address
where id=#{id}
</select>
<!--一对多 单条sql-->
<resultMap id="CoderMapper" type="Coder">
<id column="id" property="id"></id>
<result column="address" property="address"></result>
<collection property="dept" ofType="DeptOne">
<id column="id" property="id"></id>
<result column="userName" property="userName"></result>
</collection>
</resultMap>
<select id="getByCoderId" resultMap="CoderMapper">
SELECT easybuy_user_address.id,address,easyby_user.id,userName FROM easyby_user,easybuy_user_address
where easybuy_user_address.userid=easyby_user.id AND easybuy_user_address.id=#{id}
</select>
</mapper>
单侧方法：
//一对多 单个sql
@Test
public void monyfind() throws IOException {
SqlSession session = sessionFactory.getSqlSession();
ICoderOneDAO mapper = session.getMapper(ICoderOneDAO.class);
Coder code = mapper.getByCoderId(2);
System.out.println(code.getAddress());
for (DeptOne dept: code.getDept()) {
System.out.println(dept.getUserName());
}
session.close();
}
//一对多 多个sql
@Test
public void getByMonyCoderId() throws IOException {
SqlSession session = sessionFactory.getSqlSession();
ICoderOneDAO mapper = session.getMapper(ICoderOneDAO.class);
Coder code = mapper.getByMonyCoderId(1);
System.out.println(code.getAddress());
for (DeptOne dept: code.getDept()) {
System.out.println(dept.getUserName());
}
session.close();
}
这两个方法的最后结果一样，知识发送的sql 语句不同。
三。自关联
他的数据库如下：
CREATE TABLE `category` ( `cid` int(11) NOT NULL AUTO_INCREMENT, `cname` varchar(32) DEFAULT NULL, `pid` int(11) DEFAULT NULL, PRIMARY KEY (`cid`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8; /*Data for the table `category` */ insert into `category`(`cid`,`cname`,`pid`) values (1,'图书',0),(2,'服装',0),(3,'青年图书',1),(4,'少儿图书',1),(5,'期刊报纸',1),(6,'读者',3),(7,'12月份',6),(8,'12月份上半月',7),(9,'12月份下半月',7),(10,'11月份',6);
实体类如下：
package cn.happy.entity;
import java.util.ArrayList;
import java.util.List;
public class Category {
private Integer cid;//子级编号
private String cname;//名字
private Integer pid;//父级编号
private List<Category> cate=new ArrayList<Category>();//自关联（自己的一个集合）
@Override
public String toString() {
return "Category{" +
"cid=" + cid +
", cname='" + cname + '\'' +
", pid=" + pid +
", cate=" + cate +
'}';
}
public Integer getCid() {
return cid;
}
public void setCid(Integer cid) {
this.cid = cid;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public Integer getPid() {
return pid;
}
public void setPid(Integer pid) {
this.pid = pid;
}
public List<Category> getCate() {
return cate;
}
public void setCate(List<Category> cate) {
this.cate = cate;
}
}
dao层 和 xml 文件
package cn.happy.dao;
import cn.happy.entity.Category;
import cn.happy.entity.Coder;
import java.util.List;
public interface ICategoryDAO {
public List<Category> getByCategoryId(int pid);
}
<?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">
<!--映射文件的根节点
namespace
-->
<mapper namespace="cn.happy.dao.ICategoryDAO">
<resultMap id="categoryMapper" type="Category">
<id column="cid" property="cid"></id>
<result column="cname" property="cname"></result>
<collection property="cate" ofType="Category" select="getByCategoryId" column="cid">
</collection>
</resultMap>
<select id="getByCategoryId" resultMap="categoryMapper">
SELECT * FROM category where pid=#{pid}
</select>
</mapper>
测试方法：
//自关联
@Test
public void ziguanlian() throws IOException {
SqlSession session = sessionFactory.getSqlSession();
ICategoryDAO mapper = session.getMapper(ICategoryDAO.class);
List<Category> list = mapper.getByCategoryId(0);
for (Category item:list ) {
System.out.println(item.getCid()+"=="+item.getCname()+"=="+item.getPid()+":"+item.getCate());
System.out.println(item+"//////");
System.out.println("");
}
session.close();
}
