Mybatis学习（八）mybatis环境下以接口的方式进行的关联表查询

Mybatis学习（五）mybatis环境下以接口的方式进行的增删查改这一篇中用的表在这儿需要修改一下，因为查询时表名在mysql中属于关键字了，导致程序报错，在此重新更正下表user修改为t_user,表order修改为t_order。程序脚本会在下面更新。

sql文件如下：

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES ('2', 'hello1', '20');
INSERT INTO `t_user` VALUES ('3', 'hello2', '22');
INSERT INTO `t_user` VALUES ('4', 'huawei', '40');

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `t_order`
-- ----------------------------
DROP TABLE IF EXISTS `t_order`;
CREATE TABLE `t_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL,
  `orderName` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `userid` (`userid`),
  CONSTRAINT `userid` FOREIGN KEY (`userid`) REFERENCES `t_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_order
-- ----------------------------
INSERT INTO `t_order` VALUES ('1', '4', 'naafdfa');
INSERT INTO `t_order` VALUES ('2', '4', 'daerw');
INSERT INTO `t_order` VALUES ('3', '4', 'ewewe');

用户表订单表，一个用户对应着多个订单，由此建立建立一个关系查询。

程序结构：

Mybatis

  |--src

|---cn.bj.mybatis.model

|--IOrderOperation.java

|--IUserOperation.java

|--Order.java

|--User.java

|--User.xml

|--Order.xml

|---Configuration.xml

  |--test

|---cn.bj.mybatis.test

|--Mybatistest.java

User.java没有变化，其他的下面依次贴上代码：

IUserOperation.java

package cn.bj.mybatis.model;

import java.util.List;

public interface IUserOperation {
	
	 	public User selectUser(int id);
	    
	    public void addUser(User user);
	    
	    public void updateUser(User user);
	    
	    public void deleteUser(int id);
	    
	    public List<Order> getUserOrders(int id);
}

Order.java

package cn.bj.mybatis.model;

public class Order {

	private int id;
	private User user;
	private String orderName;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public User getUser() {
		return user;
	}
	public void setUser(User user) {
		this.user = user;
	}
	public String getOrderName() {
		return orderName;
	}
	public void setOrderName(String orderName) {
		this.orderName = orderName;
	}
	
}

Order.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="cn.bj.mybatis.model.IOrderOperation">
	
</mapper>

User.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="cn.bj.mybatis.models.UserMapper">
 -->
 <mapper namespace="cn.bj.mybatis.model.IUserOperation">
 <select id="getUserOrders" parameterType="int" resultMap="resultUserOrderList">
       select t_user.id,t_user.username,t_user.age,t_order.id o_id,t_order.orderName from t_user,t_order where t_user.id=t_order.userid and t_user.id=#{id}
    </select>
   <resultMap type="Order" id="resultUserOrderList">
        <id property="id" column="o_id"/>
        <result property="orderName" column="orderName"/>
         <association property="user" javaType="User">
            <id property="id" column="id" />
            <result property="name" column="name" />
            <result property="age" column="age" />
        </association>       
    </resultMap>
  <!-- 查询数据 -->
  <select id="selectUser"  resultType="User">
    select * from t_user where id = #{id}
  </select>
  <!-- 增加数据 -->
  <insert id="addUser" parameterType="User" useGeneratedKeys="true" keyProperty="id">
  	insert into t_user(username,age) values(#{username},#{age})
  </insert>
  <!-- 修改数据 -->
  <update id="updateUser" parameterType="User">
  	update t_user set username=#{username},age=#{age} where id=#{id}
  </update>
  <!-- 删除数据 -->
  <delete id="deleteUser" parameterType="int">
  	delete from t_user where id = #{id}
  </delete>
</mapper>

Configuration.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>
	<typeAliases> 
        <typeAlias alias="User" type="cn.bj.mybatis.model.User"/> 
        <typeAlias alias="Order" type="cn.bj.mybatis.model.Order"/> 
    </typeAliases> 
  <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://localhost:3306/mybatis"/>
        <property name="username" value="root"/>
        <property name="password" value="root"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="cn/bj/mybatis/model/User.xml"/>
    <mapper resource="cn/bj/mybatis/model/Order.xml"/>
  </mappers>
</configuration>

MybatisTest.java

package cn.bj.mybatis.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 cn.bj.mybatis.model.IUserOperation;
import cn.bj.mybatis.model.Order;

public class MybatisTest {
	public static void main(String[] args){
		SqlSessionFactory sqlSessionFactory = null;
		SqlSession session = null;
		String resource = "Configuration.xml";
		InputStream inputStream;
		try {
			inputStream = Resources.getResourceAsStream(resource);
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
			session = sqlSessionFactory.openSession();
			System.out.println(session);
			IUserOperation userOperation = (IUserOperation)session.getMapper(IUserOperation.class);
			
			List<Order> orders = userOperation.getUserOrders(4);
			for(Order o:orders){
				System.out.println(o.getOrderName());
			}
			
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			if(session != null){
				session.close();
			}
		}
		
	}

}

测试的结果将依次遍历集合Order。