Mybatis（四）多表操作

数据库如下：

一、创建数据库所对应的bean类

public class User {

    private Integer uId;

    private String username;

    private String sex;

    private String address;

    public Integer getuId() {

        return uId;

    }

    public void setuId(Integer uId) {

        this.uId = uId;

    }

    public String getUsername() {

        return username;

    }

    public void setUsername(String username) {

        this.username = username;

    }

    public String getSex() {

        return sex;

    }

    public void setSex(String sex) {

        this.sex = sex;

    }

    public String getAddress() {

        return address;

    }

    public void setAddress(String address) {

        this.address = address;

    }

    @Override

    public String toString() {

        return "User{" +

                "uId=" + uId +

                ", username='" + username + '\'' +

                ", sex='" + sex + '\'' +

                ", address='" + address + '\'' +

                '}';

    }

}

public class Order {

    private Integer oId;

    private Integer number;

    private String orderName;

    private Integer userId;

    private User user;

    public Integer getoId() {

        return oId;

    }

    public void setoId(Integer oId) {

        this.oId = oId;

    }

    public Integer getNumber() {

        return number;

    }

    public void setNumber(Integer number) {

        this.number = number;

    }

    public String getOrderName() {

        return orderName;

    }

    public void setOrderName(String orderName) {

        this.orderName = orderName;

    }

    public Integer getUserId() {

        return userId;

    }

    public void setUserId(Integer userId) {

        this.userId = userId;

    }

    public User getUser() {

        return user;

    }

    public void setUser(User user) {

        this.user = user;

    }

    @Override

    public String toString() {

        return "Order{" +

                "oId=" + oId +

                ", number=" + number +

                ", orderName='" + orderName + '\'' +

                ", userId=" + userId +

                ", user=" + user +

                '}';

    }

}

二、核心配置文件sqlMapConfig.xml

　　（1）、jdbc.properties与sqlMapperConfig.xml文件

jdbc.driver=com.mysql.jdbc.Driver

jdbc.url=jdbc:mysql://localhost:3306/school?characterEncoding=utf-8

jdbc.username=root

jdbc.password=dyhroot

<?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>

    <!--properties配置文件的应用-->

    <properties resource="jdbc.properties"/>

    <!--别名的应用，一定要特别注意标签是有顺序的，不然会报异常-->

    <typeAliases>

        <!--这样写就是该文件中所有的"dyh.bean.User" 都用User代替了-->

        <!--这样写有一个弊端，就是除了用户之外还有其他实体类，比如Order、Customer…… 那就要写n别名了，所以不推荐使用-->

        <!--<typeAlias type="dyh.bean.User" alias="User"/>-->

        <!--推荐使用包的形式-->

        <package name="dyh.bean"/>

    </typeAliases>

    <environments default="development">

        <environment id="development">

            <!-- 使用jdbc事务管理 -->

            <transactionManager type="JDBC" />

            <!-- 数据库连接池 -->

            <dataSource type="POOLED">

                <property name="driver" value="${jdbc.driver}" />

                <property name="url" value="${jdbc.url}" />

                <property name="username" value="${jdbc.username}" />

                <property name="password" value="${jdbc.password}" />

            </dataSource>

        </environment>

    </environments>

    <!-- Mapper的位置  Mapper.xml 写Sql语句的文件的位置 -->

    <mappers>

        <mapper resource="orderMapper/OrderMapper.xml"/>

        <!--使用mapper接口类路径

        如：<mapper class="cn.itcast.mybatis.mapper.UserMapper"/>

        注意：此种方法要求mapper接口名称和mapper映射文件名称相同，且放在同一个目录中。-->

        <!--<package name="userMapper"/>-->

        <!--注册指定包下的所有mapper接口

        如：<package name="cn.itcast.mybatis.mapper"/>

        注意：此种方法要求mapper接口名称和mapper映射文件名称相同，且放在同一个目录中。-->

        <!--注意：但是这两种引入sql映射文件的办法，用IDEA的maven工程，这样写有问题，还没找到原因，有可能是不可以这么书写-->

    </mappers>

</configuration>

三、代理接口OrderMapper与对应的映射文件OrderMapper.xml

public interface OrderMapper {

    //一对一关联查询，以订单为中心关联用户

    public List<Order> selectOrders();

}

<?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="dyh.mapper.OrderMapper">

    <!--一对一关联查询，以订单为中心关联用户

    public List<Order> selectOrders();-->

    <resultMap id="order" type="Order">

        <!--orders表操作，数据库字段user_id 与 Order类中的属性userId 不一致,其他字段不可省略-->

        <result property="userId" column="user_id"/>

        <result property="orderName" column="orderName"/>

        <result property="oId" column="oId"/>

        <result property="number" column="number"/>

        <association property="user" javaType="User">

            <id property="uId" column="uId"/>

            <result property="username" column="username"/>

            <result property="sex" column="sex"/>

            <result property="address" column="address"/>

        </association>

    </resultMap>

    <select id="selectOrders" resultMap="order">

      SELECT

      o.orderName,

      o.number,

      o.user_id,

      o.oId,

      u.uId,

      u.username,

      u.sex,

      u.address

      FROM

      orders o

      LEFT JOIN

      users u

      ON

      o.user_id = u.uId;

    </select>

</mapper>

四、测试

package dyh.test;

import dyh.bean.Order;

import dyh.mapper.OrderMapper;

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.Test;

import java.io.InputStream;

import java.util.List;

public class MybatisTest {

    @Test

    public void testMapper() throws Exception {

        //加载核心配置文件

        String resource = "sqlMapConfig.xml";

        InputStream in = Resources.getResourceAsStream(resource);

        //创建SqlSessionFactory

        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);

        //创建SqlSession

        SqlSession sqlSession = sqlSessionFactory.openSession();

        //SqlSEssion帮我生成一个实现类  （给接口）

        OrderMapper orderMapper = sqlSession.getMapper(OrderMapper.class);

        List<Order> orders = orderMapper.selectOrders();

        for (Order order : orders) {

            System.out.println(order);

        }

    }

}

测试结果：

其他：

　　一对多关联（步骤同上）

public class User implements Serializable {

    private static final long serialVersionUID = 1L;

    private Integer id;

    private String username;

    private String sex;

    private Date birthday;

    private String address;

//一个用户对应多张订单

    private List<Orders> ordersList;

     //一对多关联

    public List<User> selectUserList(); -->

    <resultMap type="User" id="user">

        <id column="user_id" property="id"/>

        <result column="username" property="username"/>

        <!-- 一对多 -->

        <collection property="ordersList" ofType="Orders">

            <id column="id" property="id"/>

            <result column="number" property="number"/>

        </collection>

    </resultMap>

    <select id="selectUserList" resultMap="user">

        SELECT

         o.id,

        o.user_id,

        o.number,

         o.createtime,

         u.username

         FROM user u

         left join orders o

         on o.user_id = u.id

    </select>

巴特西

Mybatis（四）多表操作

最新文章

热门文章