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