mybatis实现一对多连接查询

问题：两个对象User和Score，它们之间的关系为一对多。

底层数据库为postgresql，ORM框架为mybatis。

关键代码如下：


mybatis配置文件如下：

mybatis.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>
	<settings>
		<setting name="cacheEnabled" value="true" />
		<setting name="lazyLoadingEnabled" value="true" />
		<setting name="multipleResultSetsEnabled" value="true" />
		<setting name="useColumnLabel" value="true" />
		<setting name="useGeneratedKeys" value="true" />
		<setting name="defaultExecutorType" value="SIMPLE" />
		<setting name="defaultStatementTimeout" value="25000" />
	</settings>
	<typeAliases>
		<typeAlias type="com.mybatis.domain.User" alias="User" />
		<typeAlias type="com.mybatis.domain.Score" alias="Score" />
	</typeAliases>
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="org.postgresql.Driver" />
				<property name="url" value="jdbc:postgresql://localhost:5432/mybatis" />
				<property name="username" value="postgres" />
				<property name="password" value="admin" />
			</dataSource>
		</environment>
	</environments>

	<mappers>
		<mapper resource="com/mybatis/domain/User.xml" />
		<mapper resource="com/mybatis/domain/Score.xml" />
	</mappers>
</configuration>

User.java代码为：

package com.mybatis.domain;

public class User {

	private Integer id;//用户id

	private String username;//用户名

	private String password;//密码

	private String address;//地址

	public User(){
		
	}
	
	public User(String username,String password,String address){
		this.username = username;
		this.password = password;
		this.address =address;
	}
	
	public User(Integer id,String username,String password,String address){
		this.id = id;
		this.username = username;
		this.password = password;
		this.address =address;
	}

	public int getId() {
		return id;
	}

	public void setId(Integer 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 String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}
	
	public String toString(){
		return "当前用户为：id = "+id+",username = "+username+",password = "+password+",address = "+address;
	}

}

Score.java代码如下：

 package com.mybatis.domain;

public class Score {
	
	private Integer id ;//主键id
	
	private User user;//所属用户
	
	private int math ;//数学成绩
	
	private int chinese ;//语文成绩
	
	private int english ;//英语成绩
	
	private int computer ;//计算机成绩
	
	public Score(){
		
	}
	
	public Score(User user, int math,int chinese,int english,int computer){
		this.user = user;
		this.math = math;
		this.chinese = chinese;
		this.english = english;
		this.computer = computer;
	}

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public User getUser() {
		return user;
	}

	public void setUser(User user) {
		this.user = user;
	}

	public int getMath() {
		return math;
	}

	public void setMath(int math) {
		this.math = math;
	}

	public int getChinese() {
		return chinese;
	}

	public void setChinese(int chinese) {
		this.chinese = chinese;
	}

	public int getEnglish() {
		return english;
	}

	public void setEnglish(int english) {
		this.english = english;
	}

	public int getComputer() {
		return computer;
	}

	public void setComputer(int computer) {
		this.computer = computer;
	}
	
	public String toString(){
		return "id = "+ this.id+",math = "+this.math+",chinese = "+this.chinese+",english = "+this.english+",computer = "+this.computer+
				", userid = "+this.user.getId()+",username = "+this.user.getUsername()+",password = "+this.user.getPassword()+
				",address = "+this.user.getAddress();
	}

}

user.xml中的关键代码为：

	
	<resultMap type="User" id="userResult">
	  <id property="id" column="userid"/>
	  <result property="username" column="username"/>
	  <result property="password" column="password"/>
	  <result property="address" column="address"/>
	</resultMap>

这里的对象的属性id对应的数据库表列名为userid，这是user对象为pg_score表中

的标示。

score.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="ScoreDaoMapping">
	<resultMap type="Score" id="score">
		<constructor>
			<idArg column="id" javaType="int" />
			<arg column="userid" javaType="int" />
			<arg column="math" javaType="int" />
			<arg column="chinese" javaType="int" />
			<arg column="english" javaType="int" />
			<arg column="computer" javaType="int" />
		</constructor>
	</resultMap>
	
	<resultMap id="joinSelectScore" type="Score" >
	  <id property="id" column="id"/>
	  <result property="math" column="math"/>
	  <result property="chinese" column="chinese"/>
	  <result property="english" column="english"/>
	  <result property="computer" column="computer"/>
	  <association property="user" column="userid" javaType="User" resultMap="UserDaoMapping.userResult"/>
	</resultMap>
	
	<insert id="insertScore" parameterType="Score">
	   insert into pg_score(math,chinese,english,computer,userid) values(#{math},#{chinese},#{english},#{computer},#{user.id})
	</insert>
	
	<select id="findScoreByUser" resultMap="joinSelectScore" resultType="list" parameterType="map">
	     select 
	            s.id as id,
	            s.math as math,
	            s.chinese as chinese,
	            s.english as english,
	            s.computer as computer,
	            u.id as userid,
	            u.username as username,
	            u.password as password,
	            u.address as address
	     from pg_score s left outer join pg_userinfo u on s.userid = u.id where u.id=#{userid} 
	</select>
   
</mapper>

ScoreDao.java中的关键代码为：

private  String resource = "com/mybatis/configuration/mybatis.xml";
	public List<Score> selectScoreByUser(User user) throws IOException{
		Reader reader = Resources.getResourceAsReader(resource);
		SqlSessionFactory ssf = new SqlSessionFactoryBuilder().build(reader);
		SqlSession session = ssf.openSession();
		reader.close();
		Map<String,Integer> params = new HashMap<String,Integer>();
		params.put("userid", user.getId());
		List<Score> scoreList = session.selectList("ScoreDaoMapping.findScoreByUser", params);
		session.commit();
		session.close();
		return scoreList;
	}

ScoreService.java代码如下：

package com.mybatis.service;

import java.io.IOException;
import java.util.List;

import com.mybatis.dao.ScoreDao;
import com.mybatis.domain.Score;
import com.mybatis.domain.User;

public class ScoreService {

	private ScoreDao scoreDao = new ScoreDao();

	public ScoreDao getScoreDao() {
		return scoreDao;
	}

	public void setScoreDao(ScoreDao scoreDao) {
		this.scoreDao = scoreDao;
	}
	
	public List<Score> getScoreByUser(User user) throws IOException{
		return scoreDao.selectScoreByUser(user);
	}
	
	public void insertScore(Score score) throws IOException{
		scoreDao.insert(score);
	}
}

Test.java代码如下：

package com.mybatis.test;

import java.util.List;

import com.mybatis.domain.Score;
import com.mybatis.domain.User;
import com.mybatis.service.ScoreService;
import com.mybatis.service.UserService;

public class Test {

	private UserService userService = new UserService();
	
	private ScoreService scoreSerice = new ScoreService();
	
	public static void main(String[] args) throws Exception{
	    
		Test test = new Test();
		//test.insertScore();
		List<Score> scoreList = test.getScore();
		Score score = null;
        for(int i=0;i<scoreList.size();i++){
        	System.out.println("第"+(i+1)+"个score对象为：");
        	score = scoreList.get(i);
        	System.out.println(score);
        }
		
	}
	
	public void insertScore() throws Exception{
		List<User> userList = userService.getPageUsers(10, 0);
		User user = userList.get(2);
		
		Score score = new Score();
		score.setUser(user);
		score.setChinese(80);
		score.setComputer(90);
		score.setEnglish(91);
		score.setMath(98);
		scoreSerice.insertScore(score);
	}
	
	public List<Score> getScore() throws Exception{
		List<User> userList = userService.getPageUsers(10, 0);
		User user = userList.get(0);
		
		List<Score> scoreList = scoreSerice.getScoreByUser(user);
		return scoreList;
	}
	
/*	public User getUserById(int id) throws Exception{
		return userService.getUserById(id);
	}*/
}

数据库表记录为：




运行结果如下：

第1个score对象为：
id = 1,math = 98,chinese = 80,english = 91,computer = 90, userid = 1,username = yangjianzhou:0,password = password:0,address = password:0
第2个score对象为：
id = 2,math = 98,chinese = 80,english = 91,computer = 90, userid = 1,username = yangjianzhou:0,password = password:0,address = password:0
第3个score对象为：
id = 4,math = 98,chinese = 80,english = 91,computer = 90, userid = 1,username = yangjianzhou:0,password = password:0,address = password:0