【Mybatis】多对多实例

①创建数据库和表，数据库为mytest，表为users、orders和users_orders

DROP TABLE IF EXISTS users;

CREATE TABLE users(

    id INT PRIMARY KEY auto_increment,

    name VARCHAR(20),

    age INT

);

INSERT INTO users(name, age) VALUES ('Jack', 40);

INSERT INTO users(name, age) VALUES ('Rose', 36);

INSERT INTO users(name, age) VALUES ('Tom', 18);

DROP TABLE IF EXISTS orders;

CREATE TABLE orders(

    order_id INT PRIMARY KEY auto_increment,

    order_name VARCHAR(20),

    order_price float

);

INSERT INTO orders(order_name, order_price) VALUES ('IPhone', 7777.77);

INSERT INTO orders(order_name, order_price) VALUES ('Haier', 8888.88);

INSERT INTO orders(order_name, order_price) VALUES ('Benz GLS', 9999);

DROP TABLE IF EXISTS users_orders;

CREATE TABLE users_orders(

    user_id INT,

    order_id INT

);

ALTER TABLE users_orders ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id);

ALTER TABLE users_orders ADD CONSTRAINT fk_order_id FOREIGN KEY (order_id) REFERENCES orders(order_id);

INSERT INTO users_orders VALUES (1,1);

INSERT INTO users_orders VALUES (1,2);

INSERT INTO users_orders VALUES (1,3);

INSERT INTO users_orders VALUES (2,1);

INSERT INTO users_orders VALUES (2,3);

INSERT INTO users_orders VALUES (3,1);

②创建Java工程，导入相应的jar包

③创建配置文件conf.xml和数据库配置文件db.properties

<?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 resource="db.properties"></properties>

    <environments default="development">

        <environment id="development">

            <transactionManager type="JDBC"></transactionManager>

            <dataSource type="POOLED">

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

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

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

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

            </dataSource>

        </environment>

    </environments>

    <mappers>

        <mapper resource="org/mybatis/mapping/userMapper.xml"/>

        <mapper resource="org/mybatis/mapping/orderMapper.xml"/>

    </mappers>

</configuration>

 driver = com.mysql.jdbc.Driver

 url = jdbc:mysql://localhost:3306/mytest

 name = root

 password = root

④创建实体类Users、Orders和Users_Orders

package org.mybatis.domain;

import java.util.Set;

public class Orders {

    private int order_id;

    private String order_name;

    private float order_price;

    private Set<Users> users;

    public int getOrder_id() {

        return order_id;

    }

    public void setOrder_id(int order_id) {

        this.order_id = order_id;

    }

    public String getOrder_name() {

        return order_name;

    }

    public void setOrder_name(String order_name) {

        this.order_name = order_name;

    }

    public float getOrder_price() {

        return order_price;

    }

    public void setOrder_price(float order_price) {

        this.order_price = order_price;

    }

    public Set<Users> getUsers() {

        return users;

    }

    public void setUsers(Set<Users> users) {

        this.users = users;

    }

    @Override

    public String toString() {

        return "Order [orderID:" + order_id + ", orderName:" +

                order_name + ", orderPrice:" + order_price + "]";

    }

}

package org.mybatis.domain;

import java.util.Set;

public class Users {

    private int id;

    private String name;

    private int age;

    private Set<Orders> orders;

    public Set<Orders> getOrders() {

        return orders;

    }

    public void setOrders(Set<Orders> orders) {

        this.orders = orders;

    }

    public int getId() {

        return id;

    }

    public void setId(int id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public int getAge() {

        return age;

    }

    public void setAge(int age) {

        this.age = age;

    }

    @Override

    public String toString() {

        return "User [userID:" + id + ", userName:" +

                name + ", userAge:" + age + "]";

    }

}

package org.mybatis.domain;

public class Users_Orders {

    private Users user;

    private Orders order;

    public Users getUser() {

        return user;

    }

    public void setUser(Users user) {

        this.user = user;

    }

    public Orders getOrder() {

        return order;

    }

    public void setOrder(Orders order) {

        this.order = order;

    }

}

⑤创建sql映射文件userMapper.xml和orderMapper.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="org.mybatis.mapping.userMapper">

    <select id="getUsers" resultMap="getUsersMap">

        select * from users u, orders o, users_orders uo

        where u.id=uo.user_id and o.order_id=uo.order_id

        and u.id=#{id}

    </select>

    <resultMap type="org.mybatis.domain.Users" id="getUsersMap">

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

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

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

        <collection property="orders" ofType="org.mybatis.domain.Orders">

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

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

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

        </collection>

    </resultMap>

</mapper>

userMapper.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="org.mybatis.mapping.orderMapper">

    <select id="getOrders" resultMap="getOrdersMap">

        select * from users u, orders o, users_orders uo

        where u.id=uo.user_id and o.order_id=uo.order_id

        and o.order_id=#{id}

    </select>

    <resultMap type="org.mybatis.domain.Orders" id="getOrdersMap">

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

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

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

        <collection property="users" ofType="org.mybatis.domain.Users">

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

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

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

        </collection>

    </resultMap>

</mapper>

orderMapper.xml

⑥向配置文件中注册 userMapper.xml和orderMapper.xml 文件【已添加，查看第③步】

⑦测试类

package org.mybatis.app;

import java.io.InputStream;

import java.util.List;

import java.util.Set;

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 org.mybatis.domain.Orders;

import org.mybatis.domain.Users;

public class TestManyToMany {

    SqlSession session;

    @Before

    public void beforeLoad() {

        InputStream inputStream =

                TestManyToMany.class.getClassLoader().getResourceAsStream("conf.xml");

        SqlSessionFactory sqlSessionFactory =

                new SqlSessionFactoryBuilder().build(inputStream);

        session = sqlSessionFactory.openSession();

    }

    @Test

    public void testGetUsers() {

        String statement = "org.mybatis.mapping.userMapper.getUsers";

        List<Users> usersList = session.selectList(statement, 2);

        for(Users user : usersList) {

            System.out.println(user.getName() + "买了" + user.getOrders().size() + "个产品, 它们是:");

            Set<Orders> orders = user.getOrders();

            for(Orders order : orders) {

                System.out.println(order.getOrder_name());

            }

        }

    }

    @Test

    public void testGetOrders() {

        String statement = "org.mybatis.mapping.orderMapper.getOrders";

        List<Orders> orderList = session.selectList(statement, 3);

        for(Orders order : orderList) {

            System.out.println(order.getOrder_name() + "被买了" + order.getUsers().size() + "次, 买它的人有:");

            Set<Users> users = order.getUsers();

            for(Users user : users) {

                System.out.println(user.getName());

            }

        }

    }

}

⑧结构图：

巴特西

【Mybatis】多对多实例

最新文章

热门文章