提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档
前言
mybatis学习记录
05. 特殊SQL语句
使用 ${} 的几种情况:
提示:以下是本篇文章正文内容,下面案例可供参考
一、模糊查询
like后面三种方式
“%”#{username}"%"(最常用)
‘%${username}%’
concat(’%’,#{username},’%’)
测试方法:
@Test
public void testGetUserByLike(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
List<User> list = mapper.getUserByLike("邓");
System.out.println(list);
}
mapper接口:
public interface SQLMapper {
List<User> getUserByLike(@Param("username") String username);
}
映射文件:
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="study.mapper.SQLMapper">
<!--List<User> getUserByLike(@Param("username") String username);-->
<select id="getUserByLike" resultType="User">
select * from t_user where username like "%"#{username}"%"
</select>
</mapper>
二、批量删除
测试方法:
@Test
public void testDeleteMore(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
int result = mapper.deleteMore("10,12,14");
System.out.println(result);
}
mapper接口:
int deleteMore(@Param("ids") String ids);
映射文件:
<!--int deleteMore(@Param("ids") String ids);-->
<delete id="deleteMore">
delete from t_user where id in (${ids})
</delete>
三、设置动态表名查询数据
测试方法:
@Test
public void testGetUserByTableName(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
List<User> list = mapper.getUserByTableName("t_user");
System.out.println(list);
}
mapper接口:
List<User> getUserByTableName(@Param("tableName") String tableName);
映射文件:
<!--List<User> getUserByTableName(@Param("tableName") String tableName);-->
<select id="getUserByTableName" resultType="User">
select * from ${tableName}
</select>
四、添加功能获取自增主键
void insertUser(User user);
useGeneratedKeys:设置当前标签中的sql使用了自增的主键
keyProperty:将自增的主键的值赋值给传输到映射文件中参数的某个属性
测试方法:
@Test
public void testInsertUser(){
SqlSession sqlSession = SqlSessionUtils.getSqlSession();
SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
User user = new User(null,"jack","36952",20,"女","123456481358");
mapper.insertUser(user);
System.out.println(user);
}
mapper接口:
void insertUser(User user);
映射文件:
<!--void insertUser(User user);-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into t_user values(null,#{username},#{password},#{age},#{sex},#{phone})
</insert>