827下午 maybatis的多表查询操作(完整源码可看) || 自动映射功能 || 把插入的最新信息的主键取出来

本文链接：https://blog.csdn.net/wudingan/article/details/100100718

一:在orders表的基础上查询两个表的信息

1.两个表orders表和user表(orders的uid外键为user 表的id)

2.常见错误,

如果没有在orders实体类上定义一个user变量,那么会报错

因为我们是多表查询,查询的结果不单单有orders的内容,还有user的内容,因此应该在orders实体类上加上如下代码

3.完整源码(请忽略为什么用order不用orders命名,因为我后面改了..)

ordermapper.java

ordermapper.xml

order.java 实体类

package com.bean;

public class Order {
	private int id;
	private String orderNum;
	private String orderName;
	private String orderDate;
	private User user;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getOrderNum() {
		return orderNum;
	}
	public void setOrderNum(String orderNum) {
		this.orderNum = orderNum;
	}
	public String getOrderName() {
		return orderName;
	}
	public void setOrderName(String orderName) {
		this.orderName = orderName;
	}
	public String getOrderDate() {
		return orderDate;
	}
	public void setOrderDate(String orderDate) {
		this.orderDate = orderDate;
	}
	public User getUser() {
		return user;
	}
	public void setUser(User user) {
		this.user = user;
	}
	@Override
	public String toString() {
		return "Order [id=" + id + ", orderNum=" + orderNum + ", orderName=" + orderName + ", orderDate=" + orderDate
				+",userName="+user.getUserName()+ ",password="+user.getPassword()+",gender="+user.getGender()+"]";
	}
	

	
	
}

OrderTest测试类:

package com.test;

import java.io.IOException;
import java.io.InputStream;
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.Order;
import com.mapper.OrderMapper;

public class OrderTest {

	SqlSessionFactory factory;
	@Before
	public void init() throws IOException {
		InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
		factory = new SqlSessionFactoryBuilder().build(is);
	}
	
	@Test
	public void testGetOrders() {
		SqlSession session = factory.openSession();
		try {
			OrderMapper mapper = session.getMapper(OrderMapper.class);
			List<Order> orders = mapper.getOrders();
			for (Order order : orders) {
				System.out.println(order);
			}
		} finally {
			session.close();
		}
	}

}

4.运行结果:

二:在users表的基础上查询两个表的信息(跟上面的类似)

1.user.java实体类

package com.bean;

import java.util.List;

import org.apache.ibatis.type.Alias;

@Alias(value="user")
public class User {
	private int id;
	private String userName;
	private String password;
	private int gender;
	private Order order;
	/*private List<Order> orders;
	public void setOrders(List<Order> orders) {
		this.orders = orders;
	}
	public List<Order> getOrders() {
		return orders;
	}*/
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public int getGender() {
		return gender;
	}
	public void setGender(int gender) {
		this.gender = gender;
	}
	
	public Order getOrder() {
		return order;
	}
	public void setOrder(Order order) {
		this.order = order;
	}

	@Override
	public String toString() {
		return "User表信息为:" + " userName=" + userName +
				"\n"+"Orders表信息为"+"ordernum:" + order.getOrderNum()+", ordername:"+order.getOrderName()+",订单时间 "+order.getOrderDate();
	}
	
		
}

2.usermapper.java

3.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="com.mapper.UserMapper">
	<resultMap type="com.bean.User" id="BaseResultMap">
		<!-- id表主键
			column:列名
			property:对应实体类的属性名
		 -->
		 <id column="id" property="id"/>
			<result column="user_name" property="userName"></result>
			<result column="password" property="password"></result>
			<result column="gender" property="gender"></result>
		
		<association property="order" javaType="com.bean.Order">
		<id column="id" property="id"/>
		<result column="o_num" property="orderNum"></result>
		<result column="o_name" property="orderName"></result>
		<result column="o_date" property="orderDate"></result>	
		</association>
	</resultMap>
	
	<select id="getUserById"  resultMap="BaseResultMap">
		<!-- select u.user_name,o.o_num,o.o_name,o.o_date from user u inner join
		order o on u.id=o.uid where id=#{id} -->
		SELECT u.`user_name`,o.`o_num`,o.`o_name` ,o.`o_date`FROM USER u INNER JOIN orders o ON u.id=o.uid WHERE u.id=#{id}
	</select>
	
</mapper>