public class UserMapperTest {
@Test
public void test(){
//第一步,获取SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//getMapper
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List userList = userMapper.getUserList();
for (Person person : userList) {
System.out.println(person);
}
//关闭SqlSession
sqlSession.close();
}
}
2.CRUD(增删改查)
1.namespace
namespace中的包名要和Mapper接口的包名一致
2.select、insert、update、delete
选择,查询语句;
id:就是对应namespace中的方法名
resultType:Sql语句执行的返回值!
parameterType:参数类型!
1.编写接口
public interface UserMapper {
//查询全部用户
List getUserList();
//根据Id查询用户
User getUserById(int id);
//insert一个用户
int addUser(User user);
//修改用户
int updateUser(User user);
//删除一个用户
int deleteUser(int id);
}
2.编写对应mapper中的sql语句
3.测试(增删改需要提交事务)
//我们可以在工具类创建的时候实现自动提交事务
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession(true);
}
public class Test01 {
//查询所有
@Test
public void test(){
//第一步,获取SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//getMapper
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
List userList = userMapper.getUserList();
for (User person : userList) {
System.out.println(person);
}
//关闭SqlSession
sqlSession.close();
}
//根据用户Id查询用户
@Test
public void getUserById(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User userById = mapper.getUserById(1);
System.out.println(userById);
sqlSession.close();
}
//增加用户
@Test
public void addUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.addUser(new User(5, "111", "123456"));
sqlSession.commit();
sqlSession.close();
}
//根据用户Id更新用户
@Test
public void updateUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.updateUser(new User(1,"王运发","88888888"));
sqlSession.commit();
sqlSession.close();
}
//根据Id删除用户
@Test
public void deleteUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.deleteUser(5);
sqlSession.commit();
sqlSession.close();
}
}
4.万能的Map
Map传递参数,直接在sql取出key即可
对象传递参数,直接在sql中取出对象属性即可
Mapper接口层中只有一个基本类型参数的情况下,可以直接在sql中取到
多个参数用Map,或者注解!
//通过Map形参类型添加用户(UserMapper.interface)
int addUser2(Map map);
//xml文件
insert into user (id,name,pwd) values (#{userId},#{userName},#{userPwd});
@Test
//通过Map方式添加用户
public void addMapUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map map = new HashMap();
map.put("userId",66);
//map.put("userName","集合");//可以不用给对象的所有属性赋值
map.put("userPwd",12121);
mapper.addUser2(map);
sqlSession.commit();
sqlSession.close();
}
5.模糊查询
select * from user where name like "%"#{value}"%";
@Test
//模糊查询
public void getUserLike(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List userLike = mapper.getUserLike("王");//List userLike = mapper.getUserLike("%王%"); like #{value};
for (User user : userLike) {
System.out.println(user);
}
sqlSession.close();
}
语法:SELECT * FROM `user` LIMIT 0,3;//从第一条记录开始查询三条数据
SELECt * FROM `user` LIMIT 4;//查询前四条记录
使用Mybatis实现分页,核心SQL
1.接口
//分页
List getUserByLimit(Map map);
2.Mapper.xml
select * from user limit #{startIndex},#{pageSize}
3.测试
@Test
public void test(){
//第一步,获取SqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
//getMapper
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
Map map = new HashMap();
map.put("startIndex",0);
map.put("pageSize",2);
List userByLimit = userMapper.getUserByLimit(map);
for (User user : userByLimit) {
System.out.println(user);
}
//关闭SqlSession
sqlSession.close();
}
RowBounds分页(不使用SQL实现分页)
1.接口
//RowBounds分页
List getUserByRowBounds(Map map);
2.mapper.xml
select * from user
3.测试
RowBounds rowBounds = new RowBounds(1, 2);
List list = sqlSession.selectList("mapper.UserMapper.getUserByRowBounds",null,rowBounds);
7.使用注解开发
1.注解在接口上实现
public interface UserMapper {
@Select("select * from user")
List getUsers();
@Select("select * from user where id = #{id}")
User getUserById(@Param("id") int id);
@Insert("insert into user(id,name,pwd) values (#{id},#{name},#{pwd})")
int insertUser(User user);
@Update("update user set name=#{name},pwd=#{password} where id = #{id}")
int updateUser(User user);
@Delete("delete from user where id = #{wid}")
int deleteUser(@Param("wid") int id);
}
2.需要在核心配置文件中绑定接口
3.测试
@Test
public void test01(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
//测试了删除
int i = mapper.deleteUser(8);
System.out.println(i);
}
本质:反射机制实现
底层:动态代理
关于@Param()注解
基本类型的参数或者String类型,需要加上
引用类型不需要加
如果只有一个基本类型的话,可以忽略,但是建议加上
我们在SQL中引用的就是我们在 @Param()中设定的属性名,例如下面的注解删除demo
@Delete(“delete from user where id = #{wid}”) int deleteUser(@Param(“wid”) int id);
8.多对一处理
比如:多个学生拥有一个老师
方式一: 子查询
//Srudent实体类中有了Teacher这个属性
public class Student {
private int id;
private String name;
private Teacher teacher;
}
SELECT * from student
//column为student表中与teacher表中关联的字段,通过student表中的tid查询对应的老师信息
select * from teacher where id = #{id}
方式二:连表查询(推荐(数据量小))
SELECT s.id,s.`name`,t.name AS tname FROM student s,teacher t WHERe s.tid = t.id
9.一对多处理
比如:一个老师对应多个学生
对于老师而言就是一对多的关系!
public class Teacher {
private int id;
private String name;
//一个老师拥有多个学生
private List students;
}
方式一:按照结果嵌套处理(推荐,写出一个复杂sql即可)
SELECT s.id sid,s.name sname, t.name tname,t.id tid FROM student s,teacher t WHERe s.tid = t.id and t.id = #{tid}
方式二:按照查询嵌套处理
select * from teacher where id = #{tid}
select * from student where tid = #{tid}
@Data
@AllArgsConstructor
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private String views;
}
4.编写Mapper接口和Mapper.xml文件
//插入数据
int addBlog(Blog blog);
insert into blog (id, title, author, create_time, views)
values (#{id},#{title},#{author},#{createTime},#{views});
If的使用
//根据title和author限制条件查询
List queryBlogIf(Map map);
select * from blog where 1=1
and title = #{title}
and author = #{author}
@Test
public void test(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
//可以同时限制两个查询条件
// map.put("title","3333");
map.put("author","333");
List blogs = mapper.queryBlogIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
choose(when,otherwise)
choose标签的使用,类似于Java关键字switch
select * from blog
title = #{title}
and author = #{author}
and views = #{views}
测试(原理跟switch类似,从上至下,有满足的就走满足的那个条件)
@Test
public void test(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
map.put("title","3333");
// map.put("author","333");
map.put("views","333");
List blogs = mapper.queryBlogChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
trim(where,set)
where标签的使用
//优化前 如果if中某个条件不成立会导致(where后面直接and)where and author=#{author}的报错情况
select * from blog where
and title = #{title}
and author = #{author}
//使用where标签就会避免这个问题
select * from blog
and title = #{title}
and author = #{author}
set标签的使用
update blog
title = #{title},
author = #{author}
where id = #{id}
总结:所谓的动态sql,本还是sql语句,只是我们在sql层面,去执行一个逻辑代码
sql片段
有的时候我们可以将一些功能部分抽取出来,方便复用
1.使用sql标签抽取公共的部分
title = #{title},
author = #{author}
2.再有需要的地方使用include标签引用即可
select * from blog
3.注意事项:
最好基于单表来定义sql片段
不要存在where标签
foreach
//查询第一二三号记录的博客
List queryBlogForeach(Map map);
select * from blog
id = #{id}
@Test
public void foreachTest(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
ArrayList ids = new ArrayList();
ids.add(1);
ids.add(2);
ids.add(3);
map.put("ids",ids);
List blogs = mapper.queryBlogForeach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
11.缓存(了解Mybatis缓存策略即可,目前会使用Redis作为缓存)
Mybatis系统中默认定义了两级缓存:一级缓存和二级缓存
默认情况下,只有一级缓存开启。(SqlSession级别的缓存,也称为本地缓存)
二级缓存需要手动开启和配置,他是基于namespace级别的缓存
Mybatis定义了缓存接口Cache,我们可以通过Cache接口来定义二级缓存
一级缓存:
一级缓存也叫本地缓存:Sqlsession
与数据库同一次会话期间查询到的数据会放在本地缓存中
如果再次获取相同的数据,直接从缓存中拿,不必在查询数据库
二级缓存
二级缓存也叫全局缓存,由于一级缓存作用域低,所以诞生了二级缓存
基于namespace级别的缓存,一个名称空间,对应一个二级缓存
工作机制
一个会话查询一条数据,这个数据就会被放在当前会话的一级缓存中
如果会话关闭了,这个会话对应的一级缓存就没了;会话关闭,一级缓存中的数据被保存到二级缓存中。
新的会话查询信息,就可以从二级缓存中获取内容。
小节:
只要开启了二级缓存,在同一个Mapper下就有效
所有的数据都会先放在一级缓存中
只有当会话关闭的时候,才会提交到二级缓存中 r" close=“)”> id = #{id}
@Test
public void foreachTest(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
ArrayList ids = new ArrayList();
ids.add(1);
ids.add(2);
ids.add(3);
map.put("ids",ids);
List blogs = mapper.queryBlogForeach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}