mybatis实现一对多连接查询
问题：两个对象User和Score，它们之间的关系为一对多。
底层数据库为postgresql，ORM框架为mybatis。
关键代码如下：
mybatis配置文件如下：
mybatis.xml文件内容为：
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configurationPUBLIC "-//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>
package com.mybatis.domain;public class User {private Integer id;//用户idprivate 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;}} package com.mybatis.domain;public class Score {private Integer id ;//主键idprivate 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();}}<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>
<?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>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;}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);}}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