#{}和${}的区别 ， 基于Mybatis的增删改查
接着上一篇的继续
这是项目结构
8.模糊查询，排序，#和$的区别
一．模糊查询
需求 : 根据博客的名字查询博客列表(模糊查询)
方式一: 使用#传参
<select id="selectBlogByTitle" parameterType="string" resultMap="blogResultMap">
select * from Blog where title like #{title}
</select>
接口:
List<Blog> selectBlogByTitle(String title);
方式二: 使用传参时,如果参数是单值(简单类型),并且只有一个参数,那么参数必须使用value占位,并且如果参数是字符串要使用引号
<select id="selectBlogByTitle2" parameterType="string" resultMap="blogResultMap">
select * from Blog where title like '%${value}%'
</select>
接口:
List<Blog> selectBlogByTitle2(String title);
测试:
@Test
public void selectBlogByTitle2(){
SqlSession session = MybatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
//List<Blog> blogList = blogMapper.selectBlogByTitle2("%o%"); 不想每次写% 可以写在BlogMapper.xml文件中
List<Blog> blogList = blogMapper.selectBlogByTitle2("o");
session.close();
System.out.println(blogList);
}
比较#和$的区别
#是占位符?
时,如果参数是字符串类型,那么要使用引号
尽量使用#而不是$
当参数表示表名或列名的时候,只能使用$
不区分大小写的查询:
<select id="selectBlogByTitle2" parameterType="string" resultMap="blogResultMap">
select * from Blog where lower(title) like lower('%${value}%')
</select>
二．排序
需求:按照某一列排序
Mapper:
<select id="selectBlogBySort" parameterType="string" resultMap="blogResultMap">
select * from Blog order by CONVERT(${value} USING gbk)
<!-- select * from Blog order by ${value} -->
</select>
接口:
List<Blog> selectBlogBySort(String column);
Test:
@Test
public void selectBlogBySort(){
SqlSession session = MybatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
List<Blog> blogList = blogMapper.selectBlogBySort("title");
//List<Blog> blogList = blogMapper.selectBlogBySort("CONVERT(title USING gbk)");
session.close();
System.out.println(blogList);
}
9.分页-多参数查询
需求: 查询分页数据
一: 方法一
按照参数的顺序,从0开始
Mapper:
<select id="selectBlogByPage" resultMap="blogResultMap">
select * from Blog limit #{0}, #{1}
</select>
接口:
参数名任意
List<Blog> selectBlogByPage(int offset, int pagesize);
Test:
@Test
public void selectBlogByPage(){
SqlSession session = MybatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
List<Blog> blogList = blogMapper.selectBlogByPage(2,2);//列0,1,2,3... 从第二个开始查 查两个
session.close();
System.out.println(blogList);
}
二: 方法二
注解的方式
Mapper:
<select id="selectBlogByPage2" resultMap="blogResultMap">
select * from Blog limit #{offset}, #{pagesize}
</select>
接口: 注解的value值要和mapper的占位符一致
List<Blog> selectBlogByPage2(
@Param(value="offset")int offset,
@Param(value="pagesize")int pagesize);
Test:
@Test
public void selectBlogByPage2(){
SqlSession session = MybatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
List<Blog> blogList = blogMapper.selectBlogByPage2(2,3);//列0,1,2,3... 从第二个开始查 查两个
session.close();
System.out.println(blogList);
}
三: 方法三
使用map
Mapper:mapper中的参数占位符要和map的key一一对应
<select id="selectBlogByPage3" resultMap="blogResultMap">
select * from Blog limit #{offset}, #{pagesize}
</select>
接口:
List<Blog> selectBlogByPage3(Map<String, Object> map);
Test:
@Test
public void selectBlogByPage3(){
SqlSession session = MybatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("offset", 2);
map.put("pagesize", 2);
List<Blog> blogList = blogMapper.selectBlogByPage3(map);//列0,1,2,3... 从第二个开始查 查两个
session.close();
System.out.println(blogList);
}
10.插入-获取刚刚插入的id( 自增id )
一: 插入记录
需求: 新增一个博客记录
Mapper:
<insert id="insertBlog" parameterType="Blog">
insert into blog(
title,
author_id,
state,
featured,
style
)values(
#{title},
#{authorId},
#{state},
#{featured},
#{style}
)
</insert>
二: 获取自增id
方式一: 在mapper中配置insert节点的属性
useGeneratedKeys=“true” keyProperty=“id”
Mapper:
<!-- useGeneratedKeys="true" keyProperty="id" 来自动给blog中的id赋数据库中自增的id -->
<insert id="insertBlog" parameterType="Blog" useGeneratedKeys="true" keyProperty="id">
insert into blog(
title,
author_id,
state,
featured,
style
)values(
#{title},
#{authorId},
#{state},
#{featured},
#{style}
)
</insert>
方式二:
在全局配置文件中配置setting选项
<settings>
<setting name="useGeneratedKeys" value="true"/>
</settings>
并且在mapper的insert节点配置属性keyProperty=“id”
方式三:
适用于没有自增主键的数据库 如(oracle)
11.修改
Mapper:
<update id="updateBlog" parameterType="Blog">
UPDATE blog set
title = #{title},
author_id = #{authorId},
state = #{state},
featured = #{featured},
style = #{style}
where id = #{id}
</update>
接口:
Test:
@Test
public void testUpdateBlog(){
SqlSession session = MybatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(16);
blog.setTitle("我的Blog");
blog.setStyle("black");
blog.setState("ACTIVE");
blog.setFeatured(true);
blog.setAuthorId(20);
int count = blogMapper.updateBlog(blog);
session.commit();//重要
session.close();
System.out.println(blog);
System.out.println("修改了"+ count + "条记录");
}
注意: 如果没有为对象设置所有的要修改的属性,那么未设置的属性会用默认值填充
解决方案一: 先查询 在修改(如果不的话,就只会改id为16的title和authorID然后其余属性就自动填充默认值)
@Test
public void testUpdateBlog2(){
SqlSession session = MybatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
Blog blog = blogMapper.selectBlog(1);//先查询 后修改
//Blog blog = new Blog();
blog.setId(16);
blog.setTitle("Blog");
blog.setAuthorId(20);
int count = blogMapper.updateBlog(blog);
session.commit();//重要
session.close();
System.out.println(blog);
System.out.println("修改了"+ count + "条记录");
}
解决方案二:动态sql后面再说
12.删 除
Mapper:
<delete id="deleteBlogById" parameterType="int">
delete from blog where id = #{id}
</delete>
接口:
int deleteBlogById(Integer id);
Test:
@Test
public void deleteBlogById(){
SqlSession session = MybatisUtil.getSqlSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
int count = blogMapper.deleteBlogById(18);
session.commit();//重要
session.close();
System.out.println("删除了"+ count + "条记录");
}