一只倔强的笨猪

【MyBatis】对一的关联映射查询

数据库关联外键

数据库先关联外键。几种外键的关联的意思:
cascade方式:
在父表上update/delete记录时,同步update/delete掉子表的匹配记录
set null方式:
在父表上update/delete记录时,将子表上匹配记录的列设为null
要注意子表的外键列不能为not null
No action方式:
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
Restrict方式:
同no action, 都是立即检查外键约束
Set default方式:
父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别
《【MyBatis】对一的关联映射查询》

1.创建实体类。

package com.bean;

public class Goods {
	private int goodsId;
	private String goodsName;
	private double goodsPrice;
	private int goodsNum;
	private GoodsType goodsType;
	
	public Goods() {
	}

	public Goods(int goodsId, String goodsName, double goodsPrice,
			int goodsNum, GoodsType goodsType) {
		super();
		this.goodsId = goodsId;
		this.goodsName = goodsName;
		this.goodsPrice = goodsPrice;
		this.goodsNum = goodsNum;
		this.goodsType = goodsType;
	}

	public int getGoodsId() {
		return goodsId;
	}

	public void setGoodsId(int goodsId) {
		this.goodsId = goodsId;
	}

	public String getGoodsName() {
		return goodsName;
	}

	public void setGoodsName(String goodsName) {
		this.goodsName = goodsName;
	}

	public double getGoodsPrice() {
		return goodsPrice;
	}

	public void setGoodsPrice(double goodsPrice) {
		this.goodsPrice = goodsPrice;
	}

	public int getGoodsNum() {
		return goodsNum;
	}

	public void setGoodsNum(int goodsNum) {
		this.goodsNum = goodsNum;
	}

	public GoodsType getGoodsType() {
		return goodsType;
	}

	public void setGoodsType(GoodsType goodsType) {
		this.goodsType = goodsType;
	}

	@Override
	public String toString() {
		return "Goods [goodsId=" + goodsId + ", goodsName=" + goodsName
				+ ", goodsPrice=" + goodsPrice + ", goodsNum=" + goodsNum
				+ ", goodsType=" + goodsType + "]";
	}
		
}
package com.bean;

public class GoodsType {
	private int typeId;
	private String typeName;
	public GoodsType(int typeId, String typeName) {
		super();
		this.typeId = typeId;
		this.typeName = typeName;
	}
	public GoodsType() {
	}
	public int getTypeId() {
		return typeId;
	}
	public void setTypeId(int typeId) {
		this.typeId = typeId;
	}
	public String getTypeName() {
		return typeName;
	}
	public void setTypeName(String typeName) {
		this.typeName = typeName;
	}
	@Override
	public String toString() {
		return "GoodsType [typeId=" + typeId + ", typeName=" + typeName + "]";
	}
	
}

2.编写Mapper接口。

package com.mapper;

import java.util.List;

import com.bean.Goods;

public interface GoodsMapper {
	//查询所有信息
	public List<Goods> findAll();
	
}

3.编写Mapper接口的XML文件。
三种方法:
(1)直接通过对象打点调用属性的方式做映射,要求: 查询语句要使用连接查询。

<?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.mapper.GoodsMapper">
	<select id="findAll" resultMap="resultGoods">
		select * from goods
		left join goods_type
		on goods_type.typeId = goods.goods_type
	</select>
	
	<resultMap type="com.bean.Goods" id="resultGoods">
		<id property="goodsId" column="goods_id" javaType="int" />
		<result property="goodsName" column="goods_name" javaType="java.lang.String" />
		<result property="goodsPrice" column="goods_price" javaType="double" />
		<result property="goodsNum" column="goods_num" javaType="int" />
		<!-- 第一种方案:直接使用打点调用属性的方式 -->
		<result property="goodsType.typeId" column="typeId" javaType="int" />
		<result property="goodsType.typeName" column="typeName" javaType="java.lang.String" />
	</resultMap>
</mapper>
<?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://127.0.0.1:3306/spring?useUnicode=true&amp;characterEncoding=utf8" />
				<property name="username" value="root" />
				<property name="password" value="123456" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="com/mapper/GoodsMapper.xml" />
	</mappers>
</configuration>

(2)引用关联关系对象的Mapper映射。

<?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.mapper.GoodsMapper">
	<select id="findAll" resultMap="resultGoods">
		select * from goods
		left join goods_type
		on goods_type.typeId = goods.goods_type
	</select>
	
	<resultMap type="com.bean.Goods" id="resultGoods">
		<id column="goods_id" property="goodsId" jdbcType="INTEGER" />
		<result column="goods_name" property="goodsName" jdbcType="VARCHAR" />
		<result column="goods_price" property="goodsPrice" jdbcType="DOUBLE" />
		<result column="goods_num" property="goodsNum" jdbcType="INTEGER" />
		<association property="goodsType" javaType="com.bean.GoodsType"
			resultMap="com.mapper.GoodsTypeMapper.resultGoodsType" />
	</resultMap>
</mapper>
<?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.mapper.GoodsTypeMapper">
	<resultMap type="com.bean.GoodsType" id="resultGoodsType">
		<id property="typeId" column="typeId" javaType="int" />
		<result property="typeName" column="typeName" javaType="java.lang.String" />
	</resultMap>
</mapper>
<?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://127.0.0.1:3306/spring?useUnicode=true&amp;characterEncoding=utf8" />
				<property name="username" value="root" />
				<property name="password" value="123456" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="com/mapper/GoodsMapper.xml" />
		<mapper resource="com/mapper/GoodsTypeMapper.xml" />
	</mappers>
</configuration>

(3)调用关联关系对象的Mapper的查询方法。

package com.mapper;

import com.bean.GoodsType;

public interface GoodsTypeMapper {
	public GoodsType findById(int typeId);
}
<?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.mapper.GoodsTypeMapper">
	<select id="findById" parameterType="int" resultMap="resultGoodsType">
		select * from goods_type where typeId = #{id}
	</select>

	<resultMap type="com.bean.GoodsType" id="resultGoodsType">
		<id property="typeId" column="typeId" javaType="int" />
		<result property="typeName" column="typeName" javaType="java.lang.String" />
	</resultMap>
</mapper>
<?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.mapper.GoodsMapper">
	<select id="findAll" resultMap="resultGoods">
		select * from goods
		left join goods_type
		on goods_type.typeId = goods.goods_type
	</select>
	
	<resultMap type="com.bean.Goods" id="resultGoods">
		<id column="goods_id" property="goodsId" jdbcType="INTEGER" />
		<result column="goods_name" property="goodsName" jdbcType="VARCHAR" />
		<result column="goods_price" property="goodsPrice" jdbcType="DOUBLE" />
		<result column="goods_num" property="goodsNum" jdbcType="INTEGER" />
		<association property="goodsType" javaType="com.bean.GoodsType" 
				select="com.mapper.GoodsTypeMapper.findById" column="goods_type" />
	</resultMap>
</mapper>
<?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://127.0.0.1:3306/spring?useUnicode=true&amp;characterEncoding=utf8" />
				<property name="username" value="root" />
				<property name="password" value="123456" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="com/mapper/GoodsMapper.xml" />
		<mapper resource="com/mapper/GoodsTypeMapper.xml" />
	</mappers>
</configuration>

4.编写测试类。

package com.test;

import java.io.IOException;
import java.io.Reader;
import java.util.List;

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.Before;
import org.junit.Test;

import com.bean.Goods;
import com.mapper.GoodsMapper;

public class test {
	
	private SqlSession sqlSession;
	
	
	public void test01() {
		try {
			GoodsMapper goodsMapper = this.sqlSession.getMapper(GoodsMapper.class);
			List<Goods> goodsList = goodsMapper.findAll();
			for (Goods goods : goodsList) {
				System.out.println(goods);
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			this.sqlSession.close();
		}
	}
	
	
	
	@Test 
	public void testF(){
		test01();
	}
	
	@Before
	public void before() {
		try {
			String resource = "mybatis.xml";
			Reader reader = Resources.getResourceAsReader(resource);
			SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
			this.sqlSession = ssf.openSession();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}

数据库下载: 本地下载
项目代码: 本地下载

点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注