业务背景:
公司跟品牌是一对一关系,根据品牌编号查询品牌信息,附带查询品牌所属的公司(一对一关联查询)
数据库结构:
品牌表: t_brand
公司表:t_corporation
CREATE TABLE t_brand (
brandid int(11) NOT NULL AUTO_INCREMENT,
brandname varchar(30) DEFAULT NULL,
corpid int(11) DEFAULT NULL,
PRIMARY KEY (brandid)
) DEFAULT CHARSET=utf8 COMMENT='品牌';
INSERT INTO t_brand VALUES ('1', 'Apple 苹果', '1');
INSERT INTO t_brand VALUES ('2', '华为', '2');
INSERT INTO t_brand VALUES ('3', 'OPPO', '3');
INSERT INTO t_brand VALUES ('4', '小米', '4');
CREATE TABLE t_corporation (
corpid int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) DEFAULT NULL,
PRIMARY KEY (corpid)
) DEFAULT CHARSET=utf8;
INSERT INTO t_corporation VALUES ('1', 'Apple Inc. ');
INSERT INTO t_corporation VALUES ('2', '华为技术有限公司');
INSERT INTO t_corporation VALUES ('3', '欧珀移动通信有限公司');
INSERT INTO t_corporation VALUES ('4', '小米科技有限责任公司');
实体类:
package com.cheese.pojo;
public class Corporation {
private int corpid;
private String name;
public int getCorpid() {
return corpid;
}
public void setCorpid(int corpid) {
this.corpid = corpid;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
package com.cheese.pojo;
public class Brand {
private int brandid;
private String brandname;
private Corporation corp;//Corporation对象作为品牌的一个属性
public int getBrandid() {
return brandid;
}
public void setBrandid(int brandid) {
this.brandid = brandid;
}
public String getBrandname() {
return brandname;
}
public void setBrandname(String brandname) {
this.brandname = brandname;
}
public Corporation getCorp() {
return corp;
}
public void setCorp(Corporation corp) {
this.corp = corp;
}
}
Mapper文件:brandMapper.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">
<!-- namespace的值设置成包名+sql映射文件名,保证namespace取值唯一 -->
<mapper namespace="com.cheese.mapping.brandMapper">
<!--
方式一:嵌套结果:使用嵌套结果来映射结果集
select b.brandid,b.brandname,b.corpid,c.`name` from t_brand b,t_corporation c
where b.corpid=c.corpid and b.brandid=1;
-->
<select id="getBrand" parameterType="int" resultMap="BrandResultMap">
select b.brandid,b.brandname,b.corpid,c.`name` from t_brand b,t_corporation c
where b.corpid=c.corpid and b.brandid=#{brandid}
</select>
<!-- resultMap映射实体类和字段之间的一一对应关系 -->
<resultMap type="com.cheese.pojo.Brand" id="BrandResultMap">
<id property="brandid" column="brandid"/>
<result property="brandname" column="brandname"/>
<association property="corp" javaType="com.cheese.pojo.Corporation"> <!--配置关联关系-->
<id property="corpid" column="corpid"/>
<result property="name" column="name"/>
</association>
</resultMap>
<!--
方式二:嵌套查询:通过单独的一个SQL语句来返回关联的对象
select b.brandid,b.brandname,b.corpid from t_brand b where b.brandid=1;
select c.corpid,c.`name` from t_corporation c where c.corpid=1;
-->
<select id="getBrand2" parameterType="int" resultMap="BrandResultMap2">
select b.brandid,b.brandname,b.corpid from t_brand b where b.brandid=#{brandid}
</select>
<select id="getCorp" parameterType="int" resultType="com.cheese.pojo.Corporation">
select c.corpid,c.`name` from t_corporation c where c.corpid=#{corpid};
</select>
<!-- resultMap映射实体类和字段之间的一一对应关系 -->
<resultMap type="com.cheese.pojo.Brand" id="BrandResultMap2">
<id property="brandid" column="brandid"/>
<result property="brandname" column="brandname"/>
<association property="corp" column="corpid" select="getCorp"/> <!--配置关联关系-->
</resultMap>
</mapper>
mybatis配置文件:config.xml
<?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>
<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/base6?useUnicode=true&characterEncoding=UTF8"/>
<property name="username" value="bts" />
<property name="password" value="b111" />
</dataSource>
</environment>
</environments>
<mappers>
<!-- 注册brandMapper.xml文件 -->
<mapper resource="com/cheese/mapping/brandMapper.xml"/>
</mappers>
</configuration>
测试程序:
package com.cheese.test;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;import com.cheese.pojo.Brand;
public class TestQuery {
@Test
public void getBrand(){
SqlSession sqlSession = SessionUtil.getSession();
/**
* com.cheese.mapping.brandMapper.getBrand
* 定位到brandMapper.xml 中 id 为 getBrand 的语句
*/
String getBrandStr = "com.cheese.mapping.brandMapper.getBrand";//
//执行查询,返回Brand对象
Brand brand = sqlSession.selectOne(getBrandStr,1);//查询t_brand表中brandid为1的记录
sqlSession.close();
System.out.println("品牌:" + brand.getBrandname() + " --> " + brand.getCorp().getName());
}
@Test
public void getBrand2(){
SqlSession sqlSession = SessionUtil.getSession();
/**
* com.cheese.mapping.brandMapper.getBrand2
* 定位到brandMapper.xml 中 id 为 getBrand2 的语句
*/
String getBrandStr = "com.cheese.mapping.brandMapper.getBrand2";//
//执行查询,返回Brand对象
Brand brand = sqlSession.selectOne(getBrandStr,1);//查询t_brand表中brandid为1的记录
sqlSession.close();
System.out.println("品牌:" + brand.getBrandname() + " ==> " + brand.getCorp().getName());
}
}