mysql一对多mapper里怎么写,mybatis--表关系之一对多(示例代码)

mybatis中的多表查询

表关系分类：

一对一

多对一(一对多)

多对多

一对多

示例：一个用户有多个社会角色，

我们需要两张表，m_user和m_role表，需要在角色表上面添加用户表的外键

两个实体类，两个Mapper.xml文件

当我们查询账户时可以得到，对应的用户

当我们查询用户时可以得到，得到它账户集合

代码示例：

1.实体类

public class Role {

private Integer r_id;

private String r_name;

private String r_type;

}

public class User {

private Integer uId;

private String uName;

private String uLovel;

}

2.主配置文件，基本不变

/p>

PUBLIC "-//mybatis.org//DTD Config 3.0//EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

3.映射配置文件UserMapper.xml

select * from m_user;

select * from m_user where u_id=#{uId};

4.映射配置文件RoleMapper.xml

select * from m_role;

select * from m_role where r_id=#{rId};

5.测试

public class MybatisTest01 {

@Test

public void m1(){

SqlSession session = MybatisUtils.getSession();

UserMapper mapper = session.getMapper(UserMapper.class);

Listall = mapper.findAll();

for (User user : all) {

System.out.println(user);

}

session.commit();

session.close();

}

@Test

public void m2(){

SqlSession session = MybatisUtils.getSession();

RoleMapper mapper = session.getMapper(RoleMapper.class);

Listall = mapper.findAll();

for (Role role : all) {

System.out.println(role);

}

session.commit();

session.close();

}

}

输出结果正常

通过编写中间类，获取一对多数据

1.编写中间类

packagecom.bean;public class RoleUser extendsUser {privateString rName;publicString getrName() {returnrName;

}public voidsetrName(String rName) {this.rName =rName;

}

@OverridepublicString toString() {return super.toString()+ " RoleUser{" +

"rName=‘" + rName + ‘‘‘ +

‘}‘;

}

}

2.修改RoleMapper.xml配置文件

select u.*,r.r_name from m_user u,m_role r where u.u_r_id=r.r_id;

3.测试

@Testpublic voidm3(){

SqlSession session=MybatisUtils.getSession();

RoleMapper mapper= session.getMapper(RoleMapper.class);

List allUR =mapper.findAllUR();for(RoleUser roleUser : allUR) {

System.out.println(roleUser);

}

session.commit();

session.close();

}

输出结果 能取出数据

DEBUG [main] - ==> Preparing: select u.*,r.r_name from m_user u,m_role r where u.u_r_id=r.r_id;

DEBUG [main] - ==> Parameters:

DEBUG [main] -<== Total:5

User{uId=1,uName=‘金角‘, uLovel=‘111‘} RoleUser{rName=‘主公‘}

User{uId=2,uName=‘银角‘, uLovel=‘睡觉‘} RoleUser{rName=‘将军‘}

User{uId=3,uName=‘铁脚‘, uLovel=‘奥特曼‘} RoleUser{rName=‘士兵‘}

User{uId=4,uName=‘吕布‘, uLovel=‘孝顺‘} RoleUser{rName=‘主公‘}

User{uId=7,uName=‘周瑜‘, uLovel=‘谦虚‘} RoleUser{rName=‘将军‘}

通过编写添加对象关系，一的一方，获取一对多数据

1. 修改Role实体类

public classRole {privateInteger rId;privateString rName;privateString rType;//一对多，一的一方，一个角色至少对应一个类

private Listuser;

}

2.修改UserMapper配置文件,加入resultMap标签

select*from m_user;

select* from m_user where u_id=#{uId};

3.修改RoleMapper

select u.*,r.* from m_user u,m_role r where u.u_r_id=r.r_id;

select u.*,r.r_name from m_user u,m_role r where u.u_r_id=r.r_id;

select * from m_role where r_id=#{rId};

3.测试

@Testpublic voidm1(){

SqlSession session=MybatisUtils.getSession();

UserMapper mapper= session.getMapper(UserMapper.class);

List all =mapper.findAll();for(User user : all) {

System.out.println(user);

}

session.commit();

session.close();

}

输出结果

Role{rId=3, rName=‘主公‘, rType=‘A‘, user=[User{uId=1, uName=‘金角‘, uLovel=‘111‘}, User{uId=4, uName=‘吕布‘, uLovel=‘孝顺‘}]}

Role{rId=1, rName=‘将军‘, rType=‘B‘, user=[User{uId=2, uName=‘银角‘, uLovel=‘睡觉‘}, User{uId=7, uName=‘周瑜‘, uLovel=‘谦虚‘}]}

Role{rId=2, rName=‘士兵‘, rType=‘C‘, user=[User{uId=3, uName=‘铁脚‘, uLovel=‘奥特曼‘}]}

通过编写添加对象关系，多的一方，获取一对多数据

1.修改User实体类

public classUser {privateInteger uId;privateString uName;privateString uLovel;//一对多，多的一方，每个用户一个角色

privateRole role;

}

2.修改RoleMapper.xml文件

select u.*,r.* from m_user u,m_role r where u.u_r_id=r.r_id;

select * from m_user;

select * from m_user where u_id=#{uId};

3.修改UserMapper.xml文件

select u.*,r.* from m_user u,m_role r where u.u_r_id=r.r_id;

select * from m_user;

select * from m_user where u_id=#{uId};

4.测试

@Test

public void m1(){

SqlSession session = MybatisUtils.getSession();

UserMapper mapper = session.getMapper(UserMapper.class);

Listall = mapper.findAllUR();

for (User user : all) {

System.out.println(user+":"+user.getRole());

}

session.commit();

session.close();

}

输出结果

DEBUG [main] - ==> Preparing: select u.*,r.* from m_user u,m_role r where u.u_r_id=r.r_id;

DEBUG [main]- ==>Parameters:

DEBUG [main]- <== Total: 5User{uId=1, uName=‘金角‘, uLovel=‘111‘}:Role{rId=3, rName=‘主公‘, rType=‘A‘, user=null}

User{uId=2, uName=‘银角‘, uLovel=‘睡觉‘}:Role{rId=1, rName=‘将军‘, rType=‘B‘, user=null}

User{uId=3, uName=‘铁脚‘, uLovel=‘奥特曼‘}:Role{rId=2, rName=‘士兵‘, rType=‘C‘, user=null}

User{uId=4, uName=‘吕布‘, uLovel=‘孝顺‘}:Role{rId=3, rName=‘主公‘, rType=‘A‘, user=null}

User{uId=7, uName=‘周瑜‘, uLovel=‘谦虚‘}:Role{rId=1, rName=‘将军‘, rType=‘B‘, user=null}