MyBatis的多表查询
一对一关系的实现
用例：人和它所住的地址是一对一关系，即一个人只能有一个地址。
第一步：在数据库新建两个表，第一个是address表
CREATE TABLE address ( id BIGINT(32) NOT NULL, NAME VARCHAR(35) DEFAULT NULL, PRIMARY KEY (id) )ENGINE=INNODB DEFAULT CHARSET=utf8;
表中数据为：
第二个是Person表
CREATE TABLE Person ( id BIGINT(32) NOT NULL AUTO_INCREMENT, -- 人的id NAME VARCHAR(25) DEFAULT NULL, -- 人的姓名 sex VARCHAR(4) DEFAULT NULL, -- 人的性别 address_id BIGINT(32) DEFAULT NULL, PRIMARY KEY (id), KEY FK_address_id (address_id), CONSTRAINT FK_address_id FOREIGN KEY (address_id) REFERENCES address (id) )ENGINE=INNODB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
表中数据为：
由于两个表是一对一的关系，外键在Person表中添加
第二步：建立实体类：
Address.java
package cn.zhongger.domain; import java.io.Serializable; /** * 用户地址实体 */ public class Address implements Serializable { private Integer id; private String name; public Address(Integer id, String name) { this.id = id; this.name = name; } public Address() { } 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; } @Override public String toString() { return "Address{" + "id=" + id + ", name='" + name + '\'' + '}'; } }
Person.java
package cn.zhongger.domain; import java.io.Serializable; /** * 用户实体 */ public class Person implements Serializable { private Integer id; private String name; private String sex; private Integer age; private Address address;//人和地址一一对应，即每个人只有一个地址 public Person() { } public Person(Integer id, String name, String sex, Integer age, Address address) { this.id = id; this.name = name; this.sex = sex; this.age = age; this.address = address; } 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 String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public Integer getAge() { return age; } public void setAge(Integer age) { this.age = age; } public Address getAddress() { return address; } public void setAddress(Address address) { this.address = address; } @Override public String toString() { return "Person{" + "id=" + id + ", name='" + name + '\'' + ", sex='" + sex + '\'' + ", age=" + age + ", address=" + address + '}'; } }
**第三步：**创建对应的DAO接口
IAddressDao.java
package cn.zhongger.dao; import cn.zhongger.domain.Address; public interface IAddressDao { /** * 根据id查找用户地址 * @param id * @return */ Address findByid(Integer id); }
IPersonDao.java
package cn.zhongger.dao; import cn.zhongger.domain.Person; public interface IPersonDao { /** * 根据id查找用户 * @param id * @return */ Person findById(Integer id); }
第四步:编写映射文件
AddressMap.xml
<?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="cn.zhongger.dao.IAddressDao"> <select id="findByid" parameterType="java.lang.Integer" resultType="cn.zhongger.domain.Address"> select * from address where id=#{id} </select> </mapper>
PersonMap.xml
<?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="cn.zhongger.dao.IPersonDao"> <resultMap id="PersonMap" type="cn.zhongger.domain.Person"> <id property="id" column="id"></id> <result property="name" column="name"/> <result property="sex" column="sex"/> <association property="address" column="address_id" select="cn.zhongger.dao.IAddressDao.findByid" javaType="cn.zhongger.domain.Address"></association> </resultMap> <select id="findById" parameterType="java.lang.Integer" resultMap="PersonMap"> select * from Person where id=#{id} </select> </mapper>
第五步：编写测试代码：
import cn.zhongger.dao.IAddressDao; import cn.zhongger.dao.IPersonDao; import cn.zhongger.domain.Address; import cn.zhongger.domain.Person; 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.After; import org.junit.Before; import org.junit.Test; import java.io.IOException; import java.io.InputStream; public class testMyBatis { private InputStream inputStream; private SqlSessionFactoryBuilder sqlSessionFactoryBuilder; private SqlSessionFactory sessionFactory; private SqlSession sqlSession; private IPersonDao mapper1; private IAddressDao mapper2; @Before public void init() throws IOException { inputStream = Resources.getResourceAsStream("SqlMapConfig.xml"); sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder(); sessionFactory = sqlSessionFactoryBuilder.build(inputStream); sqlSession = sessionFactory.openSession(); mapper1 = sqlSession.getMapper(IPersonDao.class); mapper2 = sqlSession.getMapper(IAddressDao.class); } @After public void destroy() throws IOException { sqlSession.commit(); sqlSession.close(); inputStream.close(); } @Test public void testfindById1(){ Person person1 = mapper1.findById(1); Person person2 = mapper1.findById(2); Person person3 = mapper1.findById(3); Person person4 = mapper1.findById(4); System.out.println(person1); System.out.println(person2); System.out.println(person3); System.out.println(person4); } @Test public void testfindById2(){ Address address1 = mapper2.findByid(1); Address address2 = mapper2.findByid(2); Address address3= mapper2.findByid(3); Address address4= mapper2.findByid(4); System.out.println(address1); System.out.println(address2); System.out.println(address3); System.out.println(address4); } }
输出结果如下：
总结：
要在MyBatis中实现一对一的多表查询，最重要的是SQL语句外键的设置和对应的DAO接口的映射文件的配置中的< association >属性
