mybatis 之三：多对多关联查询
MyBatis 本是apache的一个开源项目iBatis,它 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生信息，将接口和 Java 的 POJOs(Plain Old Java Objects,普通的 Java对象)映射成数据库中的记录。
学完hibernate关联关系再来学mybatis关联关系你会发现mybatis的关联关系比hibernate的容易很多，今天就给大家分享一些关于mybatis的关联查询。
1.首先resources下面建一个mybatis-config.xml配好需要连接的数据库,我的是mysql数据库
<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--错误日志-->
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
<typeAliases>
<!--批量别名定义 -->
<package name="com.zking.pojo"></package>
</typeAliases>
<!--jdbc数据连接池-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/t204?characterEncoding=utf-8"/>
<property name="username" value="root"/>
<property name="password" value="sasa"/>
</dataSource>
</environment>
</environments>
<!--添加mapper的依赖-->
<mappers>
<mapper resource="com/zking/mapper/PersonMapper.xml"></mapper>
</mappers>
</configuration>2.在数据库分别建person，city，card三张表，表自己可以随便建。
person人的类
| Create Table |
|
CREATE TABLE `person` ( `pid` varchar(50) NOT NULL, `pname` varchar(50) DEFAULT NULL, PRIMARY KEY (`pid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
city城市类
| Create Table |
|
CREATE TABLE `city` ( `cid` varchar(50) NOT NULL, `cname` varchar(50) DEFAULT NULL, `pid` varchar(50) DEFAULT NULL, PRIMARY KEY (`cid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
card中间表
| Create Table |
|
CREATE TABLE `card` ( `cid` varchar(50) DEFAULT NULL, `cno` varchar(50) DEFAULT NULL, `pid` varchar(50) NOT NULL, PRIMARY KEY (`pid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
3.在idea里面创建实体类
package com.zking.pojo;
import java.util.List;
/**
* 人的类
*/
public class person {
public person() {
}
//人对应城市
private List<city> cityList;
public List<city> getCityList() {
return cityList;
}
public void setCityList(List<city> cityList) {
this.cityList = cityList;
}
public person(String pid, String pname) {
this.pid = pid;
this.pname = pname;
}
private String pid;
private String pname;
public String getPid() {
return pid;
}
public void setPid(String pid) {
this.pid = pid;
}
public String getPname() {
return pname;
}
public void setPname(String pname) {
this.pname = pname;
}
}
package com.zking.pojo;
import java.util.List;
/**
* 城市的类
*/
public class city {
public city() {
}
//城市对应人
private List<person> personList;
public List<person> getPersonList() {
return personList;
}
public void setPersonList(List<person> personList) {
this.personList = personList;
}
public city(String cid, String cname, String pid) {
this.cid = cid;
this.cname = cname;
this.pid = pid;
}
private String cid;
private String cname;
private String pid;
public String getCid() {
return cid;
}
public void setCid(String cid) {
this.cid = cid;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public String getPid() {
return pid;
}
public void setPid(String pid) {
this.pid = pid;
}
}
package com.zking.pojo;
/**
* 中间表
*/
public class card {
public card() {
}
//人的对象
private person person;
//城市的对象
private city city;
public com.zking.pojo.person getPerson() {
return person;
}
public void setPerson(com.zking.pojo.person person) {
this.person = person;
}
public com.zking.pojo.city getCity() {
return city;
}
public void setCity(com.zking.pojo.city city) {
this.city = city;
}
public card(String cid, String cno, String pid) {
this.cid = cid;
this.cno = cno;
this.pid = pid;
}
private String cid;
private String cno;
private String pid;
public String getCid() {
return cid;
}
public void setCid(String cid) {
this.cid = cid;
}
public String getCno() {
return cno;
}
public void setCno(String cno) {
this.cno = cno;
}
public String getPid() {
return pid;
}
public void setPid(String pid) {
this.pid = pid;
}
}
4.创建查询接口
package com.zking.mapper;
import com.zking.pojo.person;
import java.util.List;
/**
*人的类的接口
*/
public interface IPerson {
/**
* 查询所有人及他们的选择城市的信息
*/
public List<person> selectPerson();
}
5.映射实体类对应关系，及语句
<?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.zking.mapper.IPerson">
<!-- resultMap:映射实体类和字段之间的一一对应的关系 -->
<resultMap id="selectPersonList" type="person">
<id column="pid" property="pid"></id>
<result column="pname" property="pname"></result>
<!-- 多对多关联映射：collection -->
<collection property="cityList" ofType="city">
<id column="cid" property="cid"></id>
<result column="cname" property="cname"></result>
</collection>
</resultMap>
<select id="selectPerson" resultMap="selectPersonList">
SELECT p.*,ci.*,ca.`cno` FROM person p, city ci, card ca
WHERE p.`pid`=ca.`pid` AND ci.`cid`=ca.`cid`
</select>
</mapper>6.测试类
package com.zking.temp;
import com.zking.mapper.IPerson;
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.Test;
/**
* 测试类
*/
public class PersonTemp {
@Test
public void selectPerson() throws Exception {
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
SqlSession sqlSession = sqlSessionFactory.openSession();
IPerson ipm = sqlSession.getMapper(IPerson.class);
ipm.selectPerson();
sqlSession.close();
}
}