MyBatis多表查询
一、基于xml文件的多表查询
1.一对一查询
实体类:
public class User {
private int id;
private String username;
private String password;
}
public class Order {
private int id;
private Date ordertime;
private double total;
}
Order接口:
public interface OrderMapper {
public List<Order> findAll();
}
OrderMapper.xml文件:
<resultMap id="orderMap" type="order">
<!--手动指定字段与实体属性的映射关系
column:数据表的字段名称
property:实体的属性名称-->
<id column="oid" property="id"/>
<result column="ordertime" property="ordertime"/>
<result column="total" property="total"/>
<!--property:当前实体(order)中的属性名称(private User user)
javaType:当前实体(order)中的属性的类型(User)-->
<association property="user" javaType="user">
<id column="uid" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="birthday" property="birthday"/>
</association>
</resultMap>
<select id="findAll" resultMap="orderMap">
SELECT * ,o.id oid FROM orders o,USER u WHERE o.uid=u.id
</select>
测试类:
@Test
public void test1() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
List<Order> orderList=mapper.findAll();
for (Order order:orderList){
System.out.println(order);
}
sqlSession.close();
}
2.一对多查询
User接口:
public interface UserMapper {
public List<User> findAll();
}
```xml
##### UserMapper.xml文件:
<select id="findAll" resultMap="userMap">
SELECT *,o.id oid FROM USER u,orders o WHERE u.id=o.uid
</select>
**测试:**
```java
@Test
public void test2() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> userList=mapper.findAll();
for (User user:userList){
System.out.println(user);
}
sqlSession.close();
}
3.多对多查询
UserMapper.xml文件:
<resultMap id="userRoleMap" type="user">
<!-- user的信息-->
<id column="uid" property="id"/>
<result column="username" property="username"/>
<result column="password" property="password"/>
<result column="birthday" property="birthday"/>
<!-- user内部的roleList信息-->
<collection property="roleList" ofType="role">
<id column="roleId" property="id"/>
<result column="roleName" property="roleName"/>
<result column="roleDesc" property="roleDesc"/>
</collection>
</resultMap>
<select id="findUserAndRoleAll" resultMap="userRoleMap">
SELECT * FROM USER u,sys_user_role ur,sys_role r WHERE u.id=ur.userId AND ur.roleId=r.id
</select>
测试:
@Test
public void test3() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper mapper=sqlSession.getMapper(UserMapper.class);
List<User> userAndRoleAll = mapper.findUserAndRoleAll();
for (User user:userAndRoleAll){
System.out.println(user);
}
sqlSession.close();
}
二、MyBatis的注解开发
基本注解 | 功能 |
---|---|
@Insert | 实现新增 |
@Update | 实现更新 |
@Delete | 实现删除 |
@Select | 实现查询 |
@Result | 实现结果集封装 |
@Results | 封装多个结果集 |
@One | 实现一对一结果集封装 |
@Many | 实现一对多结果集封装 |
1.注解基本使用:
核心配置文件sqlMapConfig.xml:
<!--加载映射关系-->
<mappers>
<!-- 指定接口所在的包-->
<package name="com.itzhuo.mapper"/>
</mappers>
UserMapper接口:
public interface UserMapper {
@Insert("insert into user values (#{id},#{username},#{password},#{birthday})")
public void save(User user);
@Update("update user set username=#{username},password=#{password} where id=#{id}")
public void update(User user);
@Delete("delete from user where id=#{id}")
public void delete(int id);
@Select("select * from user where id=#{id}")
public User findById(int id);
@Select("select * from user")
public List<User> findAll();
}
测试:
public class MyBatisTest {
private UserMapper mapper;
@Before
public void before() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession(true);
mapper = sqlSession.getMapper(UserMapper.class);
}
@Test
public void testSave(){
User user=new User();
user.setUsername("timo");
user.setPassword("369");
mapper.save(user);
}
@Test
public void testUpdate(){
User user=new User();
user.setId(11);
user.setUsername("jiansheng");
user.setPassword("123");
mapper.update(user);
}
@Test
public void testDelete(){
mapper.delete(11);
}
@Test
public void testFindAll(){
List<User> users=mapper.findAll();
for (User user:users){
System.out.println(user);
}
}
@Test
public void testFindById(){
User user=mapper.findById(1);
System.out.println(user);
}
}
2.MyBatis的注解实现复杂映射开发
@Results:代替的是标签。该注解中可以使用单个@Result注解,也可以使用@Result集合
@Result:代替了标签和标签
@Result中属性介绍:
- column:数据库的列名
- property:需要装配的属性名
- one:需要使用的@One注解
- many:需要使用的@Many注解
@One(一对一):代替了标签,是多表查询的关键,在注解中用来指定子查询返回单一对象
@One注解属性介绍:
select:指定用来多表查询的sqlmapper
@Many(多对一):代替了标签,是多表查询的关键,在注解中用来指定子查询返回对象集合
Ⅰ.一对一查询
OrderMapper接口:
@Select("select * from orders")
@Results({
@Result(column = "id",property = "id"),
@Result(column = "ordertime",property = "ordertime"),
@Result(column = "total",property = "total"),
@Result(
property = "user",//要封装的属性名称
column = "uid",//根据哪个字段去查询user表的数据
javaType = User.class,//要封装的实体类型
//select属性:代表查询哪个接口的方法获得数据
one=@One(select = "com.itzhuo.mapper.UserMapper.findById")
)
})
public List<Order> findAll();
测试:
@Test
public void testSave(){
List<Order> all = mapper.findAll();
for (Order order:all){
System.out.println(order);
}
}
Ⅱ.一对多查询
OrderMapper接口:
@Select("select * from orders where uid=#{uid}")
public List<Order> findByUid(int uid);
UserMapper接口:
@Select("select * from user")
@Results({
@Result(id = true, column = "id", property = "id"),
@Result(column = "username", property = "username"),
@Result(column = "password", property = "password"),
@Result(
property = "orderList",
column = "id",
many = @Many(select = "com.itzhuo.mapper.OrderMapper.findByUid")
)
})
public List<User> findUserAndOrderAll();
测试:
@Test
public void testSave(){
List<User> userAndOrderAll = mapper.findUserAndOrderAll();
for (User user : userAndOrderAll) {
System.out.println(user);
}
}
Ⅲ.多对多查询
RoleMapper接口:
public interface RoleMapper {
@Select("select * from sys_user_role ur,sys_role r where ur.roleId=r.id and ur.userId=#{uid}")
public List<Role> findByUid(int uid);
}
UserMapper接口:
@Select("select * from user")
@Results({
@Result(id=true,column = "id",property = "id"),
@Result(column = "username",property = "username"),
@Result(column = "password",property = "password"),
@Result(
property = "roleList",
column = "id",
javaType = List.class,
many = @Many(select="com.itzhuo.mapper.RoleMapper.findByUid")
)
})
public List<User> findUserAndRoleAll();
测试:
@Test
public void testSave(){
List<User> userAndRoleAll = mapper.findUserAndRoleAll();
for (User user : userAndRoleAll) {
System.out.println(user);
}
}