一、环境准备
表blog
实体类
(使用了Lombok)
packagecom.zy.pojo;
importlombok.Data;
importjava.util.Date;
@Datapublic classBlog {
privateString id;privateString title;privateString author;privateDate createTime;private intviews;
}
jdbc.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/testmybatis?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username=root
password=123456
mybatis-config.xml
/p>
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
二、< where >
当查询时,select * from xxx where id=#{id}
如果出现多条件时,比如 select * from xxx where id=#{id} and name=#{name}
但是当 id或者 name未知的情况下,就没有办法实现查询
所以使用动态SQL中的 < where >进行查询
< if >
BlogMapper
packagecom.zy.mapper;importcom.zy.pojo.Blog;importjava.util.List;importjava.util.Map;public interfaceBlogMapper {//查询博客
ListqueryBlogIF(Map map);
}
BlogMapper.xml:
select * from blog
title = #{title}
and author = #{author}
and views = #{views}
测试类
@Testpublic voidqueryBlogIF() {
SqlSession sqlSession=MybatisUtils.getSqlSession();
BlogMapper blogMapper= sqlSession.getMapper(BlogMapper.class);
HashMap map= newHashMap();
map.put("title","45325");
map.put("author","efg");
List blogs =blogMapper.queryBlogIF(map);
for(Blog blog : blogs) {
System.out.println(blog);
}
}
结果
如果不添加任何元素进入map
@Testpublic voidqueryBlogIF() {
SqlSession sqlSession=MybatisUtils.getSqlSession();
BlogMapper blogMapper= sqlSession.getMapper(BlogMapper.class);
HashMap map= newHashMap();
List blogs =blogMapper.queryBlogIF(map);
for(Blog blog : blogs) {
System.out.println(blog);
}
}
结果
< choose >
BlogMapper
packagecom.zy.mapper;
importcom.zy.pojo.Blog;
importjava.util.List;importjava.util.Map;
public interfaceBlogMapper {
ListqueryBlogChoose(Map map);
}
BlogMapper.xml
select*from blog
title=#{title}
and author=#{author}
and views=#{views}
测试类
@Testpublic voidqueryBlogChoose() {
SqlSession sqlSession=MybatisUtils.getSqlSession();
BlogMapper blogMapper= sqlSession.getMapper(BlogMapper.class);
HashMap map= newHashMap();
map.put("title","45325");
map.put("author","efg");
map.put("views","322");
List blogs =blogMapper.queryBlogChoose(map);for(Blog blog : blogs) {
System.out.println(blog);
}
}
结果
存在多个成立的条件
比如:这个sql中,不使用choose的话会查出3条记录
但只取choose中的第一个
使用 < trim >代替 < where > + SQL片段使用
BlogMapper.xml
title=#{title}
AND author=#{author}
AND views=#{views}
select*from blog
< set >
BlogMapper
packagecom.zy.mapper;
importcom.zy.pojo.Blog;
importjava.util.List;importjava.util.Map;
public interfaceBlogMapper {
//更新博客
intupdateBlog(Map map);
}
BlogMapper.xml
update blog
title=#{title},
author=#{author},
views=#{views},
where id=#{id}
测试类
@Testpublic voidqueryBlogSet() {
SqlSession sqlSession=MybatisUtils.getSqlSession();
BlogMapper blogMapper= sqlSession.getMapper(BlogMapper.class);
HashMap map= newHashMap();
map.put("title","6546");
map.put("author","fasfd");
map.put("views","6243");
map.put("id","d4303d0f-43f2-422e-8dbf-9a61ad872ff9");
blogMapper.updateBlog(map);
List blogs =blogMapper.queryBlogIF(map);for(Blog blog : blogs) {
System.out.println(blog);
}
}
原本的数据库
更新后
使用 < trim >代替 < set >
BlogMapper.xml
update blog
title=#{title},
author=#{author},
views=#{views},
where id=#{id}
SQL片段
有时候,可以将一些功能相同的部分抽出,方便使用
抽取例子:
抽取前
select * from blog
title = #{title}
and author = #{author}
and views = #{views}
抽取后
title = #{title}
and author = #{author}
and views = #{views}
select * from blog
测试
依旧能正常查出数据
注意点
1.最好基于单表定义SQL片段,尽量不要太复杂
2.不要存在 < where >
< ForEach >
对集合进行遍历使用 ForEach
对 blog表 的 id修改一下
BlogMapper
packagecom.zy.mapper;
importcom.zy.pojo.Blog;
importjava.util.List;importjava.util.Map;
public interfaceBlogMapper {
//查询1-2-3号记录的博客
ListqueryBlogForeach(Map map);
}
BlogMapper.xml
select * from blog
id = #{id}
测试类
@Testpublic voidqueryBlogForEach(){
SqlSession sqlSession=MybatisUtils.getSqlSession();
BlogMapper blogMapper= sqlSession.getMapper(BlogMapper.class);
HashMap map= newHashMap();
ArrayList ids = new ArrayList();
map.put("ids",ids);
List blogs =blogMapper.queryBlogForeach(map);
for(Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
结果
通过给ArrayList添加元素来实现想要的元素查询
比如想查 id为1,2,3的
@Testpublic voidqueryBlogForEach(){
SqlSession sqlSession=MybatisUtils.getSqlSession();
BlogMapper blogMapper= sqlSession.getMapper(BlogMapper.class);
HashMap map= newHashMap();
ArrayList ids = new ArrayList();
ids.add(1);
ids.add(2);
ids.add(3);
map.put("ids",ids);
List blogs =blogMapper.queryBlogForeach(map);
for(Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
结果