JAVA

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

数据库关联外键

数据库先关联外键。几种外键的关联的意思：

cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null 要注意子表的外键列不能为not null
No action方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
Restrict方式：同no action, 都是立即检查外键约束
Set default方式：父表有变更时,子表将外键列设置成一个默认的值但Innodb不能识别

数据库脚本下载：本地下载

项目案例代码：本地下载

1.创建实体类

com/bean/Goods.java

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 + "]";
    }

}

com/bean/GoodsType.java

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接口

com/mapper/GoodsMapper.java

package com.mapper;

import java.util.List;

import com.bean.Goods;

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

}

3.编写Mapper接口的XML文件

三种方法：

（1）直接通过对象打点调用属性的方式做映射，要求: 查询语句要使用连接查询。

com/mapper/GoodsMapper.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="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>

mybatis.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://127.0.0.1:3306/spring?useUnicode=true&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映射。

com/mapper/GoodsMapper.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="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>

com/mapper/GoodsTypeMapper.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="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>

mybatis.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://127.0.0.1:3306/spring?useUnicode=true&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的查询方法。

com/mapper/GoodsTypeMapper.java

package com.mapper;

import com.bean.GoodsType;

public interface GoodsTypeMapper {
    public GoodsType findById(int typeId);
}

com/mapper/GoodsTypeMapper.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="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>

com/mapper/GoodsTypeMapper.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="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>

mybatis.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://127.0.0.1:3306/spring?useUnicode=true&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.编写测试类

com/test/test.java

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();
        }
    }
}

如果觉得文章对你有用，请随意赞赏

MyBatis 数据库

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

https://www.benzhu.xyz/archives/mybatis006

作者

小猪

发布于

2019-03-29

更新于

2024-11-24

许可协议

CC BY 4.0

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

数据库关联外键

1.创建实体类

2.编写Mapper接口

3.编写Mapper接口的XML文件

4.编写测试类

作者

发布于

更新于

许可协议

评论