分析
mybatis中的多表操作 < 掌握使用>
一对一
一对多< ? >
多对多
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
1 、mybatis中的多表查询
(1 )表之间的关系有几种:
一对多、多对一、一对一、多对多
(2 )举例:
用户和订单就是一对多
订单和用户就是多对一:一个用户可以下多个订单、多个订单属于同一个用户
人和身份证号就是一对一
老师和学生就是多对多
(3 )特例:
如果拿出每一个订单,它都能属于一个用户。所以mybatis就把多对一看成了一对一。
2 、mybatis中的多表查询
(1 )示例:用户和账户
一个用户可以有多个账户
一个账户只能属于一个用户(多个账户也可以属于同一个用户)
(2 )步骤:
1 、建立两张表:用户表,账户表
让用户表和账户表之间具备一对多的关系:需要使用外键在账户表中添加
2 、建立两个实体类:用户实体类和账户实体类
让用户和账户的实体类能体现出一对多的关系
3 、建立两个配置文件
用户的配置文件
账户的配置文件
4 、实现配置
当我们查询用户时,可以同时得到用户下所包含的账户信息。
当我们查询账户时,可以同时得到账户的所属用户信息
一、一对一查询one2one
方式一:通过写account子类方式查询
1、大致步骤
要求:select a. * , u. username, u. address from account a, user u where u. id= a. uid;
说明:通过两表user account连接查询,用user. id与account. uid作为桥梁
————查询有账户的用户的信息
大致步骤:
( 1 ) extends Account
( 2 ) 修改:return super. toString ( ) + " AccountUser [username=" + username + ", address=" + address + "]" ;
( 3 ) < ! -- 查询所有账户同时包含用户名和地址信息 -- >
< select id= "findAllAccount" resultType= "com.song.domain.AccountUser" >
select a. * , u. username, u. address from account a, user u where u. id= a. uid;
< / select>
( 4 ) 测试类书写方式
@Test
public void testfindAllAccountUser ( ) {
List< AccountUser> aus = accountDao. findAllAccount ( ) ;
for ( AccountUser au: aus) {
System. out. println ( au) ;
}
}
2、编写User实体类
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
}
public class Account implements Serializable {
private Integer id;
private Integer uid;
private double money;
}
public class AccountUser extends Account {
private String username;
private String address;
public String getUsername ( ) {
return username;
}
public void setUsername ( String username) {
this . username = username;
}
public String getAddress ( ) {
return address;
}
public void setAddress ( String address) {
this . address = address;
}
@Override
public String toString ( ) {
return super . toString ( ) + " AccountUser [username=" + username + ", address=" + address + "]" ;
}
}
3、编写持久层接口
public interface IAccountDao {
List< AccountUser> findAllAccount ( ) ;
}
4、编写IAccountDao.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 = " com.song.dao.IAccountDao" >
< select id = " findAllAccount" resultType = " com.song.domain.AccountUser" >
select a.*,u.username,u.address from account a,user u where u.id=a.uid;
</ select>
</ mapper>
5、编写测试类
public class AccountTest {
private InputStream in;
private SqlSessionFactory factory;
private SqlSession sqlSession;
private IAccountDao accountDao;
@Before
public void init ( ) throws IOException {
in = Resources. getResourceAsStream ( "SqlMapConfig.xml" ) ;
factory= new SqlSessionFactoryBuilder ( ) . build ( in) ;
sqlSession = factory. openSession ( true ) ;
accountDao = sqlSession. getMapper ( IAccountDao. class ) ;
}
@After
public void destory ( ) throws Exception {
sqlSession. close ( ) ;
in. close ( ) ;
}
@Test
public void testfindAllAccountUser ( ) {
List< AccountUser> aus = accountDao. findAllAccount ( ) ;
for ( AccountUser au: aus) {
System. out. println ( au) ;
}
}
}
6、查询结果
方式二:建立修改配置实体类方式
1、大致步骤
一、要求:select a. * , u. username, u. address from account a, user u where u. id= a. uid;
二、说明:通过两表user account连接查询,用user. id与account. uid作为桥梁 ————查询有账户的用户的信息
三、大致步骤
1 、修改Account实体类
添加User实体类为Account实体类的属性并且建立set get 方法
代码如下:
public User user;
public void setUser ( User user) {
this. user = user;
}
public User getUser ( ) {
return user;
}
注意:
User实体类是Account实体类的属性。
2 、修改映射文件
添加响应的配置属性
代码如下:
< ! -- 定义封装account和user的resultMap -- >
< resultMap id= "accountUserMap" type= "account" >
< id property= "id" column= "aid" > < / id>
< result property= "uid" column= "uid" > < / result>
< result property= "money" column= "money" > < / result>
< ! -- 一对一的关系映射: 配置封装user的内容 -- >
< association property= "user" column= "uid" javaType= "com.song.domain.User" >
< id property= "id" column= "id" > < / id>
< result property= "username" column= "username" > < / result>
< result property= "address" column= "address" > < / result>
< result property= "sex" column= "sex" > < / result>
< result property= "birthday" column= "birthday" > < / result>
< / association>
< / resultMap>
3 、查询语句如下
< ! -- 查询所有 -- >
< select id= "findAll" resultMap= "accountUserMap" >
select a. * , u. username, u. address from account a, user u where u. id= a. uid;
< / select>
4 、测试方法如下
@Test
public void testfindAllcount ( ) {
List< Account> accounts = accountDao. findAll ( ) ;
for ( Account account: accounts) {
System. out. print ( "这是一条记录:" ) ;
System. out. print ( account) ;
System. out. println ( account. getUser ( ) ) ;
}
}
2、编写实体类
public class User implements Serializable {
private Integer id;
private String username;
private Date birthday;
private String sex;
private String address;
. . .
}
public class Account implements Serializable {
private Integer id;
private Integer uid;
private double money;
public User user;
. . .
}
3、编写持久层接口
public interface IAccountDao {
List< Account> findAll ( ) ;
}
4、编写IAccountDao.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 = " com.song.dao.IAccountDao" >
< resultMap id = " accountUserMap" type = " account" >
< id property = " id" column = " aid" > </ id>
< result property = " uid" column = " uid" > </ result>
< result property = " money" column = " money" > </ result>
< association property = " user" column = " uid" javaType = " User" >
< id property = " id" column = " id" > </ id>
< result property = " username" column = " username" > </ result>
< result property = " address" column = " address" > </ result>
< result property = " sex" column = " sex" > </ result>
< result property = " birthday" column = " birthday" > </ result>
</ association>
</ resultMap>
< sql id = " defaultAccount" > select * from account</ sql>
< select id = " findAll" resultMap = " accountUserMap" >
select u.*,a.money,a.uid,a.id as aid from user u,account a where u.id=a.uid
</ select>
</ mapper>
5、编写测试类
package com. song. test;
import java. io. IOException;
import java. io. InputStream;
import java. util. List;
import org. apache. ibatis. io. Resources;
import org. apache. ibatis. session. SqlSession;
import org. apache. ibatis. session. SqlSessionFactory;
import org. apache. ibatis. session. SqlSessionFactoryBuilder;
import org. junit. After;
import org. junit. Before;
import org. junit. Test;
import com. song. dao. IAccountDao;
import com. song. domain. Account;
public class AccountTest {
private InputStream in;
private SqlSessionFactory factory;
private SqlSession sqlSession;
private IAccountDao accountDao;
@Before
public void init ( ) throws IOException {
in = Resources. getResourceAsStream ( "SqlMapConfig.xml" ) ;
factory= new SqlSessionFactoryBuilder ( ) . build ( in) ;
sqlSession = factory. openSession ( true ) ;
accountDao = sqlSession. getMapper ( IAccountDao. class ) ;
}
@After
public void destory ( ) throws Exception {
sqlSession. close ( ) ;
in. close ( ) ;
}
@Test
public void testfindAllcount ( ) {
List< Account> accounts = accountDao. findAll ( ) ;
for ( Account account: accounts) {
System. out. print ( "这是一条记录:" ) ;
System. out. print ( account) ;
System. out. println ( account. getUser ( ) ) ;
}
}
}
6、查询结果