【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>
1 driver = com.mysql.jdbc.Driver 2 url = jdbc:mysql://localhost:3306/mytest 3 name = root 4 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>
<?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>
⑥向配置文件中注册 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()); } } } }
⑧结构图：
