mybaitis association 一对一， 一对多 collection，多对多 discrimination，以及根据list查询，更新，删除

目录

批量查询，根据list条件

批量更新，id不同，值不同

根据ids查询

association 一对一， 一对多  collection，多对多 discrimination

assiociation(一对一)

实现方式一：

实现方式三（association方式二）：

一对多collection

---

批量查询，根据list条件

<select id="queryStkbalseList" resultMap="BaseResultMapVO">
         select
        <include refid="Base_Column_List"/>
        from cc_stkbale stkbale
        <where>
            <foreach collection="stkbalseDTOS" item="record" separator=" or ">
                (stkbale.ORG_ID=#{record.orgId} and stkbale.MANAGER_NO=#{record.managerNo} and
                stkbale.BUNDLE_NO=#{record.bundleNo})
            </foreach>
        </where>
</select>

批量更新，id不同，值不同

<update id="updateXsSalesRemarkPriceByList" parameterType="java.util.List">
	<foreach collection="list" item="item" index="index" open="" close="" separator=";">
		UPDATE xs_sales_item
		<set>
			remark_price= #{item.remarkPrice,jdbcType=DECIMAL},
			price= #{item.price,jdbcType=DECIMAL}
		</set>
		<where>
			sales_item_no = #{item.salesItemNo,jdbcType=VARCHAR}
		</where>
	</foreach>
</update>

根据ids查询

<select id="querySaleItemDataByIds"
		resultType="com.mountslink.platform.components.sale.sale.vo.SalesExamRegisterVo">
	select
	price_tax as salePrice,
	cg_price as purchasePrice,
	sales_item_no as salesItemNo
	from xs_sales_item
	<where>
		id in
		<foreach collection="ids" item="id" separator="," open="(" close=")">
			#{id}
		</foreach>
		and status != '00'
	</where>
</select>

association 一对一， 一对多  collection，多对多 discrimination

assiociation(一对一)

级联操作的数据库表数据

 
USE `db_mybatis`;
 
/*Table structure for table `t_address` */
 
DROP TABLE IF EXISTS `t_address`;
 
CREATE TABLE `t_address` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sheng` varchar(20) DEFAULT NULL,
  `shi` varchar(20) DEFAULT NULL,
  `qu` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
 
/*Data for the table `t_address` */
 
insert  into `t_address`(`id`,`sheng`,`shi`,`qu`) values 
(1,'江苏省','苏州市','姑苏区'),
(2,'江苏省','南京市','鼓楼区');
 
/*Table structure for table `t_student` */
 
DROP TABLE IF EXISTS `t_student`;
 
CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `addressId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
 
/*Data for the table `t_student` */
 
insert  into `t_student`(`id`,`name`,`age`,`addressId`) values 
(1,'张一',10,1),
(2,'张二',11,2),
(3,'张三',11,2),
(4,'张四',11,2),
(5,'张五',11,2),
(6,'张六',11,2),
(7,'张七',11,2)
 

实现方式一：

<resultMap type="Student" id="StudentResult">
	<id property="id" column="id"/>
	<result property="name" column="name"/>
	<result property="age" column="age"/>
	
	<result property="address.id" column="addressId"/>
	<result property="address.sheng" column="sheng"/>
	<result property="address.shi" column="shi"/>
	<result property="address.qu" column="qu"/>
</resultMap>

说明

property="address.id" column="addressId"/>

为什么是address 因为student 类中的属性是address

<select id="findStudentWithAddress" resultMap="StudentResult" parameterType="Integer">
        select * from t_student t1,t_address t2 where t1.addressId=t2.id and t1.id=#{id}
</select>

@Test
	public void testFindStudentWithAddress() {
		logger.info("查询学生(带地址)");
		Student student=studentMapper.findStudentWithAddress(2);
		System.out.println(student);
	}

sqlSession 不用close 的原因是

@After
	public void tearDown() throws Exception {
		sqlSession.close();
	}

总结：这种方式采用的是对象方式的级联操作不太好，因为每一次查询都要把所有的属性列在那里，并且修改的时候要全部修改，所以很不方便，尽量是模块话
实现方式二（association方式一）：

<resultMap type="Address" id="AddressResult">
		<result property="id" column="id"/>
		<result property="sheng" column="sheng"/>
		<result property="shi" column="shi"/>
		<result property="qu" column="qu"/>
	</resultMap>
	
	<resultMap type="Student" id="StudentResult">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
		<result property="age" column="age"/>
		<association property="address" resultMap="AddressResult"/>
	</resultMap>

日志为：

2018-06-28 15:53:09,925 [main] INFO [com.java1234.service.StudentTest] - 查询学生(带地址)

2018-06-28 15:53:10,065 [main] DEBUG [com.java1234.mappers.StudentMapper.findStudentWithAddress] - ==> Preparing: select * from t_student t1,t_address t2 where t1.addressId=t2.id and t1.id=?

2018-06-28 15:53:10,095 [main] DEBUG [com.java1234.mappers.StudentMapper.findStudentWithAddress] - ==> Parameters: 2(Integer)

2018-06-28 15:53:10,113 [main] DEBUG [com.java1234.mappers.StudentMapper.findStudentWithAddress] - <== Total: 1

Student [id=2, name=李四, age=11, address=Address [id=2, sheng=江苏省, shi=南京市, qu=鼓楼区]]

实现方式三（association方式二）：

<resultMap type="Student" id="StudentResult">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
		<result property="age" column="age"/>
		<association property="address" javaType="Address">
			<result property="id" column="id"/>
			<result property="sheng" column="sheng"/>
			<result property="shi" column="shi"/>
			<result property="qu" column="qu"/>
		</association>
</resultMap>

实现方式四（association方式三）：

只需要有这个一个方法就可以了，因为在student的表中有一个Address 的外键

<resultMap type="Student" id="StudentResult">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
		<result property="age" column="age"/>
		<association property="address" column="addressId" select="com.java1234.mappers.AddressMapper.findById"></association>
</resultMap>

<association property="address" 

一对多collection

首先修改数据库

 USE `db_mybatis`;
 
/*Table structure for table `t_address` */
 
DROP TABLE IF EXISTS `t_address`;
 
CREATE TABLE `t_address` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sheng` varchar(20) DEFAULT NULL,
  `shi` varchar(20) DEFAULT NULL,
  `qu` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
 
/*Data for the table `t_address` */
 
insert  into `t_address`(`id`,`sheng`,`shi`,`qu`) values (1,'江苏省','苏州市','姑苏区'),(2,'江苏省','南京市','鼓楼区');
 
/*Table structure for table `t_grade` */
 
DROP TABLE IF EXISTS `t_grade`;
 
CREATE TABLE `t_grade` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gradeName` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
 
/*Data for the table `t_grade` */
 
insert  into `t_grade`(`id`,`gradeName`) values (1,'大学一年级'),(2,'大学二年级');
 
/*Table structure for table `t_student` */
 
DROP TABLE IF EXISTS `t_student`;
 
CREATE TABLE `t_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `addressId` int(11) DEFAULT NULL,
  `gradeId` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_t_student` (`gradeId`),
  KEY `FK_t_student2` (`addressId`),
  CONSTRAINT `FK_t_student2` FOREIGN KEY (`addressId`) REFERENCES `t_address` (`id`),
  CONSTRAINT `FK_t_student` FOREIGN KEY (`gradeId`) REFERENCES `t_grade` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
 
/*Data for the table `t_student` */
 
insert  into `t_student`(`id`,`name`,`age`,`addressId`,`gradeId`) values (1,'张

首先介绍表中的内容

<mapper namespace="com.java1234.mappers.GradeMapper">
 
	<resultMap type="Grade" id="GradeResult">
		<result property="id" column="id"/>
		<result property="gradeName" column="gradeName"/>
		<collection property="students" column="id" select="com.java1234.mappers.StudentMapper.findByGradeId"></collection>
	</resultMap>
	
	<select id="findById" parameterType="Integer" resultMap="GradeResult">
		select * from t_grade where id=#{id}
	</select>
</mapper>