mybatis 一对多关系

User-Order：一对多

 

pojo：

public class User {

	private Integer uid;
	private String username;
	private String sex;
	private Date birthday;
	private String address;
	private String uuid2;
	
	private List<Order> orders;  //一个用户对应多个订单
	get/set()...

public class Order {
	private Integer oid;

	//private Integer userId;//用户ID

	private String number;
	private Date createtime;
	private String note;
	
	private User user;	//多个订单对应一个用户
	get/set()...

数据库建表语句

-- ----------------------------
-- Table structure for `orders`
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
  `oid` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL COMMENT '下单用户id',
  `number` varchar(32) NOT NULL COMMENT '订单号',
  `createtime` datetime NOT NULL COMMENT '创建订单时间',
  `note` varchar(100) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `FK_orders_1` (`user_id`),//外鍵
  CONSTRAINT `FK_order_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

 

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名称',
  `birthday` date DEFAULT NULL COMMENT '生日',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `address` varchar(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

 

Dao层

 

public interface UserMapper {
	/**
	 * 根据用户ID查询用户信息
	 * @param id
	 * @return
	 */
	User getUserById(Integer id);
	/**
	 * 根据用户名查找用户列表
	 * @param name
	 * @return
	 */
	List<User> getUserByUserName(String name);
	/**
	 * 添加用户
	 * @param user
	 */
	void inserUser(User user);

}

 

<?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">
<!-- namespace：命名空间，类似于java包，主要用于隔离sql语句的，后续有重要作用
	 #{}：点位符，相当于jdbc的?
	 ${}:字符串拼接指令，注意如果入参为普通数据类型时，括号里面只能写value
 -->
 <!-- 动态代理开发dao四个军规：
 	  1、namespace必需是接口的全路径名
 	  2、sql id 必需与接口的方法名称一致
 	  3、parameterType必需与接口方法的入参类型一致
 	  4、resultType必需与接口返回值类型一致
  -->
<mapper namespace="com.itheima.mybatis.mapper.UserMapper">
	<!-- id:sql id标识sql语句的唯一标识
		 parameterType:入参的数据类型
		 resultType:返回结果的数据类型
	 -->

	<select id="getUserById" parameterType="int" resultType="uSEr">
		SELECT`id`,`username`,`birthday`,`sex`,`address`
		FROM `user`
		WHERE id = #{id123}
	</select>
	
	
	<!-- resultType:如果返回结果是集合时，只需要设置为元素的数据类型就可 -->
	<select id="getUserByUserName" parameterType="string" resultType="com.itheima.mybatis.pojo.User">
		SELECT`id`, `username`,`birthday`,`sex`,`address`
		FROM `user`
		WHERE username LIKE '%${value}%'
	</select>
	
	<!-- 自增主键返回第二种配置方式：useGeneratedKeys使用自增，keyProperty与上一个属性配套使用 -->
	<insert id="inserUser" parameterType="com.itheima.mybatis.pojo.User" useGeneratedKeys="true" keyProperty="id">
	
		<!-- 通过selectKey配置自增主键返回
			 keyProperty:接收的主键属性
			 resultType:主键数据类型
			 order:指定selectKey执行顺序：BEFORE(在sql插入之前),AFTER(在sql插入之后)
		 -->
		<!-- <selectKey keyProperty="id" resultType="int" order="AFTER">
			SELECT LAST_INSERT_ID()
		</selectKey> -->
		INSERT INTO `user` (`username`, `birthday`, `sex`,`address`)
		VALUES (#{username},#{birthday},#{sex},#{address});
	</insert>
	
</mapper>

 

一对多关联查询

<!--用户和订单多对一-->
<resultMap type="User"id="USER_ORDER_MAP">
    <id property="uid"column="uid"/>
    <result property="username"column="username"/>
    <result property="age"column="age"/>
    <result property="birthday"column="birthday"/>
    <result property="address"column="address"/>

    <collection property="orders"ofType="order">
        <id column="oid"property="oid"></id>
        <result property="userid"column="userid"/>
        <result property="number"column="number"/>
        <result property="createtime"column="createtime"/>
        <result property="note"column="note"/>
    </collection>
</resultMap>

  <select id="searchEngine" resultMap="USER_ORDER_MAP" parameterType="String">
        SELECT o.`oid`,u.`uid`,u.`username` FROM user u INNER JOIN order o WHERE o.`user_Id` = u.`uid` AND u.`username` LIKE concat('%','${keyword}','%');
    </select>

 

一对多关联增加

一对多关联删除

一对多关联修改