设为首页 收藏本站
查看: 518|回复: 0

[经验分享] mybatis实现一对多连接查询

[复制链接]

尚未签到

发表于 2016-11-25 09:17:31 | 显示全部楼层 |阅读模式
问题:两个对象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);
}*/
}


数据库表记录为:
DSC0000.jpg

运行结果如下:

第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

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-305297-1-1.html 上篇帖子: mybatis和spring整合——常用的两种 下篇帖子: MyBatis之配置文件
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表