Mybatis 一对多关联查询,批量修改

需求：同事给同事帮忙，顺便做个记录。千万不要手动去封装，很累很累。

代码地址：https://gitee.com/tcyj/mybatis-one2many.git
这里的一对多关联查询是指，在查询一方对象的时候，同时将其所关联的多方对象也都查询出来。
目录结构（springboot+mybatis）

下面以国家 Country 与部长 Minister 间的一对多关系进行演示。
1、pom.xml

<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.6.RELEASE</version>
		<relativePath />
	</parent>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.1</version>
				<configuration>
					<source>1.7</source>
					<target>1.7</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>
	<dependencies>

		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<version>1.2.0</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.6</version>
			<scope>runtime</scope>
		</dependency>
		<!-- https://mvnrepository.com/artifact/log4j/log4j -->
		<dependency>
			<groupId>log4j</groupId>
			<artifactId>log4j</artifactId>
			<version>1.2.17</version>
		</dependency>

	</dependencies>

2、实体：

public class Country {
	
	private Integer cid;
	
	private String cname;
	// 关联属性
	private Set<Minister> ministers;
	
	public Integer getCid() {
		return cid;
	}
	public void setCid(Integer cid) {
		this.cid = cid;
	}
	public String getCname() {
		return cname;
	}
	public void setCname(String cname) {
		this.cname = cname;
	}
	public Set<Minister> getMinisters() {
		return ministers;
	}
	public void setMinisters(Set<Minister> ministers) {
		this.ministers = ministers;
	}
	@Override
	public String toString() {
		return "Country [cid=" + cid + ", cname=" + cname + ", ministers="
				+ ministers + "]";
	}
}

public class Minister {
	
	private Integer mid;
	private String mname;
	
	//getter and setter
	//toString()
	
	public Integer getMid() {
		return mid;
	}
	public void setMid(Integer mid) {
		this.mid = mid;
	}
	public String getMname() {
		return mname;
	}
	public void setMname(String mname) {
		this.mname = mname;
	}
	@Override
	public String toString() {
		return "Minister [mid=" + mid + ", mname=" + mname + "]";
	}
}

3、定义数据库表
country表

CREATE TABLE `country` (
      `cid` INT(5) NOT NULL AUTO_INCREMENT,
      `cname` VARCHAR(20) DEFAULT NULL,
      PRIMARY KEY (`cid`)
    ) ENGINE=INNODB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
    
INSERT INTO `country` VALUES ('1', 'USA');
INSERT INTO `country` VALUES ('2', 'England');

minister表

CREATE TABLE `minister` (
  `mid` INT(5) NOT NULL AUTO_INCREMENT,
  `mname` VARCHAR(20) DEFAULT NULL,
  `countryId` INT(5) DEFAULT NULL,
  PRIMARY KEY (`mid`)
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `minister` VALUES ('1', 'aaa', '1');
INSERT INTO `minister` VALUES ('2', 'bbb', '1');
INSERT INTO `minister` VALUES ('3', 'ccc', '1');
INSERT INTO `minister` VALUES ('4', 'ddd', '2');
INSERT INTO `minister` VALUES ('5', 'eee', '2');

4、 定义 Dao 层

public interface ICountryDao {
	Country selectCountryById(int cid);
	void updateList(List<Minister> ministerList);
}

5、定义映射文件

<mapper namespace="com.mybatis.mapper.ICountryDao">
	
	<resultMap type="Country" id="countryMapper">
		<id column="cid" property="cid"/>
		<result column="cname" property="cname"/>
		<!--关联属性的映射关系-->
		<collection property="ministers" ofType="Minister">
			<id column="mid" property="mid"/>
			<result column="mname" property="mname"/>
		</collection>
	</resultMap>
	
	<!--多表连查-->
	<select id="selectCountryById" resultMap="countryMapper">
		select cid,cname,mid,mname
		from country,minister
		where countryId=cid and cid=#{xxx} 
	</select>
	<select id="selectCountry" resultMap="countryMapper">
		select cid,cname,mid,mname
		from country,minister
		where countryId=cid 
	</select>
	<update id="updateList"  parameterType="java.util.List">  
	    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
	        update minister
	        <set>
	            mname=#{item.mname}
	        </set>
	        where mid = ${item.mid}
	    </foreach>      
	</update>
</mapper>

6、Controller层+启动类

@RestController
public class UserController {
	Logger log = Logger.getLogger("UserController");
	@Autowired
	private ICountryDao dao;
	
	@RequestMapping("/find")
	public Country find(){
		Country country = dao.selectCountryById(2);
		log.info(country);
		return country;
	}
	/**
	 * 批量更新
	 */
	@RequestMapping("/update")
	public void updateList(){
		
		List<Minister> ministerList=new ArrayList<Minister>();
		
		Minister minister = new Minister();
		minister.setMid(1);
		minister.setMname("aa");
		Minister minister2 = new Minister();
		minister2.setMid(2);
		minister2.setMname("ff");
		
		ministerList.add(minister);
		ministerList.add(minister2);
		dao.updateList(ministerList);
		
	}
}

@SpringBootApplication 
@MapperScan({"com.mybatis.mapper"})
public class Application {
	
	public static void main(String[] args){
		SpringApplication.run(Application.class,args);
	}
}

结果：Country [cid=2, cname=England, ministers=[Minister [mid=4, mname=ddd], Minister [mid=5, mname=eee]]]

查询请求：http://localhost:8001/find
批量更新：http://localhost:8001/update

---

Mybatis代码自动生成器

GeneratorSqlmap

public class GeneratorSqlmap {

	public void generator() throws Exception{

		List<String> warnings = new ArrayList<String>();
		boolean overwrite = true;
		//指定 ****配置文件
		String resource = getClass().getClassLoader().getResource("generatorConfig.xml").getPath();
		File configFile = new File(resource); 
	
		ConfigurationParser cp = new ConfigurationParser(warnings);
		Configuration config = cp.parseConfiguration(configFile);
		DefaultShellCallback callback = new DefaultShellCallback(overwrite);
		MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config,
				callback, warnings);
		myBatisGenerator.generate(null);

	} 
	public static void main(String[] args) throws Exception {
		try {
			GeneratorSqlmap generatorSqlmap = new GeneratorSqlmap();
			generatorSqlmap.generator();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}

}

pom.xml

        </dependency>
		        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.2.8</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.44</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.3.5</version>
        </dependency>

generatorConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
  PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
  "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>

	<context id="DB2Tables" targetRuntime="MyBatis3">
		<commentGenerator>
			<property name="suppressAllComments" value="true" />
		</commentGenerator>
		<!-- 配置数据库连接 -->
		<jdbcConnection driverClass="com.mysql.jdbc.Driver"
			connectionURL="jdbc:mysql://localhost:3306/girls" userId="root"
			password="123456">
		</jdbcConnection>

		<javaTypeResolver>
			<property name="forceBigDecimals" value="false" />
		</javaTypeResolver>

		<!-- 指定javaBean生成的位置 -->
		<javaModelGenerator targetPackage="com.mybatis.bean"
			targetProject=".\src\main\java">
			<property name="enableSubPackages" value="true" />
			<property name="trimStrings" value="true" />
		</javaModelGenerator>

		<!--指定sql映射文件生成的位置 -->
		<sqlMapGenerator targetPackage="mybatis" targetProject=".\src\main\resources">
			<property name="enableSubPackages" value="true" />
		</sqlMapGenerator>

		<!-- 指定dao接口生成的位置，mapper接口 -->
		<javaClientGenerator type="XMLMAPPER"
			targetPackage="com.mybatis.dao" targetProject=".\src\main\java">
			<property name="enableSubPackages" value="true" />
		</javaClientGenerator>


		<!-- table指定每个表的生成策略 -->
			<table tableName="admin" schema=""  domainObjectName="Admin" enableCountByExample="false"
				enableDeleteByExample="false" enableUpdateByExample="false"
				enableSelectByExample="false" selectByExampleQueryId="false">
		</table>
		<table tableName="beauty" schema="" domainObjectName="Beauty" enableCountByExample="false"
				enableDeleteByExample="false" enableUpdateByExample="false"
				enableSelectByExample="false" selectByExampleQueryId="false"></table>
		<table tableName="boys" schema="" domainObjectName="Borm" enableCountByExample="false"
				enableDeleteByExample="false" enableUpdateByExample="false"
				enableSelectByExample="false" selectByExampleQueryId="false"></table>

	</context>
</generatorConfiguration>