声明
本博客是基于相关黑马程序员的课程总结而来,感兴趣的可以去看视频教程,没什么废话,还是比较推荐的。 这篇博客其实就是对前面的mybatis的一次总结,用的是比较常用的方法————注解的形式。
计划本博客的内容有:
- 多表查询操作
- 缓存的配置
数据库的准备
在同一个库中创建两个表:
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL auto_increment,
`username` varchar(32) NOT NULL COMMENT '用户名称',
`birthday` datetime default NULL COMMENT '生日',
`sex` char(1) default NULL COMMENT '性别',
`address` varchar(256) default NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `user`(`id`,`username`,`birthday`,`sex`,`address`) values (41,'老王','2018-02-27 17:47:08','男','北京'),(42,'小二王','2018-03-02 15:09:37','女','北京金燕龙'),(43,'小二王','2018-03-04 11:34:34','女','北京金燕龙'),(45,'传智播客','2018-03-04 12:04:06','男','北京金燕龙'),(46,'老王','2018-03-07 17:37:26','男','北京'),(48,'小马宝莉','2018-03-08 11:44:00','女','北京修正');
DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
`ID` int(11) NOT NULL COMMENT '编号',
`UID` int(11) default NULL COMMENT '用户编号',
`MONEY` double default NULL COMMENT '金额',
PRIMARY KEY (`ID`),
KEY `FK_Reference_8` (`UID`),
CONSTRAINT `FK_Reference_8` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `account`(`ID`,`UID`,`MONEY`) values (1,41,1000),(2,45,1000),(3,41,2000);
环境搭建
新建一个maven工程。总体和上一篇博文的大差不大。
IUserDao
的改动部分:
package com.zhouman.dao;
import com.zhouman.domain.User;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import java.util.List;
public interface IUserDao {
/**
* 查询所有用户
* @return
*/
@Select("select * from user")
List<User> findAll();
/**
* 根据 id 查询用户
* @param userId
* @return
*/
@Select("select * from user where id = #{id}")
User findById(Integer userId);
/**
* 根据 用户名称 模糊查询
* @param username
* @return
*/
@Select("select * from user where username like #{username}")
List<User> findByName(String username);
}
User
的改动部分:
package com.zhouman.domain;
import java.io.Serializable;
import java.util.Date;
public class User implements Serializable {
private Integer userId;
private String userName;
private String userAddress;
private String userSex;
private Date userBirthday;
@Override
public String toString() {
return "User{" +
"userId=" + userId +
", userName='" + userName + '\'' +
", userAddress='" + userAddress + '\'' +
", userSex='" + userSex + '\'' +
", userBirthday=" + userBirthday +
'}';
}
public Integer getUserId() {
return userId;
}
public void setUserId(Integer userId) {
this.userId = userId;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public String getUserAddress() {
return userAddress;
}
public void setUserAddress(String userAddress) {
this.userAddress = userAddress;
}
public String getUserSex() {
return userSex;
}
public void setUserSex(String userSex) {
this.userSex = userSex;
}
public Date getUserBirthday() {
return userBirthday;
}
public void setUserBirthday(Date userBirthday) {
this.userBirthday = userBirthday;
}
}
AnnotationCRUDTest
的改动部分
package com.zhouman.test;
import com.zhouman.dao.IUserDao;
import com.zhouman.domain.User;
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 java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;
public class AnnotationCRUDTest {
private InputStream inputStream;
private SqlSessionFactory sessionFactory;
private SqlSession sqlSession;
private IUserDao userDao;
@Before
public void init() throws IOException {
//1. 获取字节输入流
inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
//2. 根据字节输入流创建 SqlSessionFactory
sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//3. 根据 SqlSessionFactory 生产一个 SqlSession
sqlSession = sessionFactory.openSession();
//4. 使用 SqlSession 获取 Dao 的代理对象
userDao = sqlSession.getMapper(IUserDao.class);
}
@After
public void destroy() throws IOException {
//6. 释放资源
sqlSession.commit();
sqlSession.close();
inputStream.close();
}
@Test
public void testFindAll(){
List<User> users = userDao.findAll();
for(User user : users){
System.out.println(user);
}
}
@Test
public void testFindById(){
System.out.println(userDao.findById(57));
System.out.println(userDao.findById(58));
}
@Test
public void testFindByName(){
List<User> users = userDao.findByName("%王%");
for (User user : users){
System.out.println(user);
}
}
}
注解的形式开发时 如何建立实体类属性和数据库列名的对应关系
在上面的测试方法类中AnnotationCRUDTest
运行findAll
方法,运行结果:
发现可以对User查询结果进行封装,但是除了username以外的属性都没有显示出来。
这是因为我们的User
类的属性名和数据库中表对应的列名并不一样。
那为何 userName 就能显示呢? 这是因为 Mybatis 不区分大小写。所以 username 和 userName 是一样的。
之前 采用dao接口.xml方式
的时候可以用 resultMap标签
来进行处理。
那注解方式开发该怎么办呢?
首先,说一种可行但是不推荐的方法:那就是在sql语句当中起别名。
推荐的做法是使用mybatis提供的 @Results
注释
在IUserDao接口中对应的方法体前面进行@Results
的使用:
/**
* 查询所有用户
* @return
*/
@Select("select * from user")
@Results(value = {
@Result(id = true,column = "id", property = "userId"),
@Result(column = "username", property = "userName"),
@Result(column = "address", property = "userAddress"),
@Result(column = "sex", property = "userSex"),
@Result(column = "birthday", property = "userBirthday"),
})
List<User> findAll();
执行结果:
但是上面的做法是有缺陷的,那就是其他方法要是也有问题也得要重新写相应的注释,
例如:findById
这无疑是非常麻烦且难看的。
改进方法:
package com.zhouman.dao;
import com.zhouman.domain.User;
import org.apache.ibatis.annotations.*;
import java.util.List;
public interface IUserDao {
/**
* 查询所有用户
* @return
*/
@Select("select * from user")
@Results( id = "userMap", value = {
@Result(id = true,column = "id", property = "userId"),
@Result(column = "username", property = "userName"),
@Result(column = "address", property = "userAddress"),
@Result(column = "sex", property = "userSex"),
@Result(column = "birthday", property = "userBirthday"),
})
List<User> findAll();
/**
* 根据 id 查询用户
* @param userId
* @return
*/
@Select("select * from user where id = #{id}")
@ResultMap("userMap")
User findById(Integer userId);
/**
* 根据 用户名称 模糊查询
* @param username
* @return
*/
@Select("select * from user where username like #{username}")
@ResultMap("userMap")
List<User> findByName(String username);
}
运行结果就不放上来了,占地方。
注释开发多表查询之一对一的查询配置
在domain包下创建一个 Account 实体类
package com.zhouman.domain;
import java.io.Serializable;
public class Account implements Serializable {
private Integer id;
private Integer uid;
private Double money;
//多对一对应关系的映射
private User user;
public User getUser() {
return user;
}
public void setUser(User user) {
this.user = user;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getUid() {
return uid;
}
public void setUid(Integer uid) {
this.uid = uid;
}
public Double getMoney() {
return money;
}
public void setMoney(Double money) {
this.money = money;
}
@Override
public String toString() {
return "Account{" +
"id=" + id +
", uid=" + uid +
", money=" + money +
'}';
}
}
在 dao 包下创建 IAccountDao 接口
package com.zhouman.dao;
import com.zhouman.domain.Account;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.FetchType;
import java.util.List;
public interface IAccountDao {
/**
* 查询所有账户,并且获取每个账户下所属的用户信息
* @return
*/
@Select("select * from account")
@Results(id = "accountMap",value = {
@Result(id = true, column = "id", property = "id"),
@Result(column = "uid", property = "uid"),
@Result(column = "money", property = "money"),
@Result(property = "user", column = "uid", one = @One(select = "com.zhouman.dao.IUserDao.findById",fetchType = FetchType.EAGER))
})
List<Account> findAll();
}
新建测试类来进行测试
package com.zhouman.test;
import com.zhouman.dao.IAccountDao;
import com.zhouman.dao.IUserDao;
import com.zhouman.domain.Account;
import com.zhouman.domain.User;
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 java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class AccountTest {
private InputStream inputStream;
private SqlSessionFactory sessionFactory;
private SqlSession sqlSession;
private IAccountDao accountDao;
@Before
public void init() throws IOException {
//1. 获取字节输入流
inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");
//2. 根据字节输入流创建 SqlSessionFactory
sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//3. 根据 SqlSessionFactory 生产一个 SqlSession
sqlSession = sessionFactory.openSession();
//4. 使用 SqlSession 获取 Dao 的代理对象
accountDao = sqlSession.getMapper(IAccountDao.class);
}
@After
public void destroy() throws IOException {
//6. 释放资源
sqlSession.commit();
sqlSession.close();
inputStream.close();
}
@Test
public void testFindAll(){
List<Account> accounts = accountDao.findAll();
for(Account account : accounts){
System.out.println("------------------------");
System.out.println(account);
System.out.println(account.getUser());
}
}
}
查询结果:
注释开发多表查询之一对多的查询配置
例如:一个用户对应多个账户
domain包下的 User 实体类添加下面部分内容:
//一对多关系映射:一个用户对应对个账户
private List<Account> accounts;
public List<Account> getAccounts() {
return accounts;
}
public void setAccounts(List<Account> accounts) {
this.accounts = accounts;
}
在IAccountDao
接口中添加一个方法:
/**
* 根据用户 id 查询 账户信息
* @param userId
* @return
*/
@Select("select * from account where uid = #{userId}")
List<Account> findAccountByUid(Integer userId);
在IUserDao
接口中改进findAll方法中的注释:
/**
* 查询所有用户
* @return
*/
@Select("select * from user")
@Results( id = "userMap", value = {
@Result(id = true,column = "id", property = "userId"),
@Result(column = "username", property = "userName"),
@Result(column = "address", property = "userAddress"),
@Result(column = "sex", property = "userSex"),
@Result(column = "birthday", property = "userBirthday"),
@Result(property = "accounts", column = "id",
many = @Many(select = "com.zhouman.dao.IAccountDao.findAccountByUid", fetchType = FetchType.LAZY))
})
List<User> findAll();
去测试类执行findAll方法
结果是:
结果分开是因为上一步中的注释为:
fetchType = FetchType.LAZY
开启了延迟加载
注解开发之使用二级缓存
累了,这一部分内容先不学了!