MyBatis_普通注解方式与SQL构造器INNER_JOIN方式@Provider_结合@Results与@Result对关联查询结果集进行映射

MyBatis 注解方式的实现可以通过注解写SQL语句或者通过SQL构造器实现，并且对于多表查询的结果集进行映射，这里仅利用MyBatis的查询方式做一个示例

1、实体类

package com.mybatis.bean;
//商品信息类
public class Goods {
//	商品信息：编号，商品名，所属类别编号，产地，商品单价
	private long id;
	private String name;
	private String origin;
	private double price;
	private GoodsType type_id;
	
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public GoodsType getType_id() {
		return type_id;
	}
	public void setType_id(GoodsType type_id) {
		this.type_id = type_id;
	}
	public String getOrigin() {
		return origin;
	}
	public void setOrigin(String origin) {
		this.origin = origin;
	}
	public double getPrice() {
		return price;
	}
	public void setPrice(double price) {
		this.price = price;
	}
}

package com.mybatis.bean;

import java.util.List;

//商品类别类
public class GoodsType {
//	编号，商品类别名称
	private long id;
	private String type_name;
	private List<Goods> goods;
	
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	public String getType_name() {
		return type_name;
	}
	public void setType_name(String type_name) {
		this.type_name = type_name;
	}
	public List<Goods> getGoods() {
		return goods;
	}
	public void setGoods(List<Goods> goods) {
		this.goods = goods;
	}
}

这里的商品类别与商品是一对多的关系，这里我是在商品类中对商品类别进行的关联
2、dao接口

示例一:通过普通的注解实现

package com.mybatis.idao;

import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.FetchType;

import com.mybatis.bean.Goods;
import com.mybatis.bean.GoodsType;
//商品dao操作接口
public interface Goods_IDAO {
	//根据商品编号，查询商品名称和商品类别的操作
	@Select("select * from goods where id=#{id}")
	@Results(id="goodsTypeMap",
			 value = {
					@Result(column = "id",property = "id" ,id = true),
					@Result(column = "name",property = "name"),
					@Result(column = "origin",property = "origin"),
					@Result(column = "price",property = "price"),
					@Result(column = "type_id",property = "type_id",
					// lazy（延迟加载）和eager（立即加载）@One需要指定子查询的方法路径
					one = @One(select = "com.mybatis.idao.Goods_IDAO.findGoodsTypeById",fetchType = FetchType.EAGER))
			 }
	)
	public Goods findGoodsById(long id);
	//子查询
	@Select("select * from goods_type where id=#{id}")
	public GoodsType findGoodsTypeById(long id);
}

实例二:通过SQL构造器实现

package com.mybatis.idao;

import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import com.mybatis.bean.Goods;
import com.mybatis.bean.GoodsType;
import com.mybatis.sql.SqlProviderGoods;
//商品dao操作接口
public interface Goods_IDAO {
	//根据商品编号，查询商品名称和商品类别的操作
	@SelectProvider(type=SqlProviderGoods.class,method = "findGoodsByIdSQL")
	@Results(id="goodsTypeMap",
			value = {
					@Result(column = "id",property = "id" ,id = true),
					@Result(column = "name",property = "name"),
					@Result(column = "origin",property = "origin"),
					@Result(column = "price",property = "price"),
					//多表查询结果集映射时可以使用 属性类.属性 直接进行映射
					@Result(column = "t_id",property = "type_id.id", id = true),
					@Result(column = "type_name",property = "type_id.type_name")
			}
	)
	public Goods findGoodsById(long id);
}

package com.mybatis.sql;

import org.apache.ibatis.jdbc.SQL;
//SQL构造器
public class SqlProviderGoods {
	//根据商品编号，查询商品名称和商品类别的操作
	public String findGoodsByIdSQL() {
		return new SQL() {
			{
				SELECT("g.id,g.name,g.origin,g.price,t.id AS t_id,t.type_name");
				FROM("goods g");
				INNER_JOIN("goods_type t ON g.type_id=t.id");
				WHERE("g.id=#{id}");
			}
		}.toString();
	}
}

3、Mapper注册

<!-- Mapper 注册 -->
	<mappers>
		<!-- 商品dao接口 注册 注解方式实现注解指定dao接口类 -->
		<mapper class="com.mybatis.idao.Goods_IDAO"/>
	</mappers>

4、JUnit 测试
这里说明一下使用 JUnit 工具类来进行测试需要修改JDK版本为1.8以上才能测试，否则无法加载到测试的 Run

package com.mybatis.idao;

import java.io.IOException;
import java.io.InputStream;

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.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import com.mybatis.bean.Goods;
//JUnit 测试类
class Goods_IDAOTest {
	private SqlSessionFactory factory=null;
	//每个测试方法前加载一次 mybatis 配置
	@BeforeEach
	void init() throws IOException {
		InputStream in=Resources.getResourceAsStream("mybatis-config.xml");
		SqlSessionFactoryBuilder builder=new SqlSessionFactoryBuilder();
		this.factory=builder.build(in);
	}
	
	//根据商品编号，查询商品名称和商品类别的操作	
	@Test
	void testFindGoodsById() {
		SqlSession sqlSession=this.factory.openSession();
		Goods_IDAO dao=sqlSession.getMapper(Goods_IDAO.class);
		Goods goods=dao.findGoodsById(1l);
		System.out.println(goods.getName()+"\t"+goods.getType_id().getType_name());
	}

}

5、运行结果

附:数据表映射关系
goods 表的 type_id 字段 为外键关联 goods_type 表的 id 主键字段

转载请标明出处