5. MyBatis获取参数值的两种方式
MyBatis获取参数值的两种方式:${}
和#{}
${}
的本质就是字符串拼接,#{}
的本质就是占位符赋值
${}
使用字符串拼接的方式拼接sql,若为字符串类型或日期类型的字段进行赋值时,需要手动加单引号
#{}
使用占位符赋值的方式拼接sql,此时为字符串类型或日期类型的字段进行赋值时, 可以自动添加单引号
5.1 单个字面量类型的参数
若mapper接口中的方法参数为单个的字面量类型,此时可以使用${}
和#{}
以任意的名称获取参数的值,注意${}
需要手动加单引号。
在UserMapper
接口中,新增函数:
/**
* 根据用户名查询用户信息
* @param username
*/
User getUserByUsername(String username);
在UserMapper.xml
文件中,新增属性,使用${}
和#{}
获取参数的值:
<!--User getUserByUsername(String username);-->
<select id="getUserByUsername" resultType="User">
<!--select * from t_user where username = #{username}-->
select * from t_user where username = '${username}'
</select>
在ParameterTest
中,新增测试:
@Test
public void testGetUserByUsername(){
SqlSession sqlSession = SqlSessionUtil.getsqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.getUserByUsername("admin");
System.out.println(user);
}
测试结果:
DEBUG 10-20 11:30:41,247 ==> Preparing: select * from t_user where username = 'admin' (BaseJdbcLogger.java:137)
DEBUG 10-20 11:30:41,272 ==> Parameters: (BaseJdbcLogger.java:137)
DEBUG 10-20 11:30:41,289 <== Total: 1 (BaseJdbcLogger.java:137)
User{id=1, username='admin', password='123456', age=23, gender='男', email='123456@qq.com'}
5.2 多个字面量类型的参数
若mapper
接口中的方法参数为多个时,此时MyBatis会自动将这些参数放在一个map集合中,以arg0,arg1...
为键,以参数为值;以 param1,param2...
为键,以参数为值;因此只需要通过${}
和#{}
访问map集合的键就可以获取相对应的 值,注意${}
需要手动加单引号 。
在UserMapper
接口中,新增函数:
/**
* 验证登录
*/
User checkLogin(String username, String password);
在UserMapper.xml
文件中,新增属性,使用${}
和#{}
获取参数的值:
<!--User checkLogin(String username, String password);-->
<select id="checkLogin" resultType="User">
/*select * from t_user where username = #{username} and password = #{password}*/
/*select * from t_user where username = #{param1} and password = #{param2}*/
select * from t_user where username = '${param1}' and password = '${param2}'
</select>
在ParameterTest
中,新增测试:
@Test
public void testCheckLogin(){
SqlSession sqlSession = SqlSessionUtil.getsqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.checkLogin("admin", "123456");
System.out.println(user);
}
测试结果:
select * from t_user where username = #{username} and password = #{password}
若通过以上方式编写sql
语句,会报以下错误,我们需要根据给定的参数类型编写:
org.apache.ibatis.exceptions.PersistenceException:
### Error querying database. Cause: org.apache.ibatis.binding.BindingException: Parameter 'username' not found. Available parameters are [arg1, arg0, param1, param2]
### Cause: org.apache.ibatis.binding.BindingException: Parameter 'username' not found. Available parameters are [arg1, arg0, param1, param2]
如下是使用${}
和#{}
获取参数的值的测试结果:
DEBUG 10-20 11:34:15,273 ==> Preparing: select * from t_user where username = 'admin' and password = '123456' (BaseJdbcLogger.java:137)
DEBUG 10-20 11:34:15,298 ==> Parameters: (BaseJdbcLogger.java:137)
DEBUG 10-20 11:34:15,316 <== Total: 1 (BaseJdbcLogger.java:137)
User{id=1, username='admin', password='123456', age=23, gender='男', email='123456@qq.com'}
5.3 map集合类型的参数
若mapper
接口中的方法需要的参数为多个时,此时可以手动创建map
集合,将这些数据放在map
中 只需要通过${}
和#{}
访问map集合的键就可以获取相对应的值,注意${}
需要手动加单引号 。
在UserMapper
接口中,新增函数:
/**
* 验证登录(以map集合作为参数)
* @param map
* @return
*/
User checkLoginByMap(Map<String, Object> map);
在UserMapper.xml
文件中,新增属性,使用${}
和#{}
获取参数的值:
<!--User checkLoginByMap(Map<String, Object> map);-->
<select id="checkLoginByMap" resultType="User">
select * from t_user where username = #{username} and password = #{password}
</select>
在ParameterTest
中,新增测试:
@Test
public void testCheckLoginByMap(){
SqlSession sqlSession = SqlSessionUtil.getsqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Map<String, Object> map = new HashMap<>();
map.put("username", "admin");
map.put("password", "123456");
User user = mapper.checkLoginByMap(map);
System.out.println(user);
}
测试结果:
DEBUG 10-20 11:44:18,280 ==> Preparing: select * from t_user where username = ? and password = ? (BaseJdbcLogger.java:137)
DEBUG 10-20 11:44:18,309 ==> Parameters: admin(String), 123456(String) (BaseJdbcLogger.java:137)
DEBUG 10-20 11:44:18,333 <== Total: 1 (BaseJdbcLogger.java:137)
User{id=1, username='admin', password='123456', age=23, gender='男', email='123456@qq.com'}
5.4 实体类类型的参数
若mapper
接口中的方法参数为实体类对象时,此时可以使用${}
和#{}
,通过访问实体类对象中的属性名获取属性值,注意${}
需要手动加单引号 。
在UserMapper
接口中,新增函数:
/**
* 添加用户信息
* @param user
*/
void insertUser(User user);
在UserMapper.xml
文件中,新增属性,使用${}
和#{}
获取参数的值:
<!--void insertUser(User user);-->
<insert id="insertUser">
insert into t_user values(null,#{username},#{password},#{age},#{gender},#{email})
</insert>
在ParameterTest
中,新增测试:
@Test
public void testInsertUser(){
SqlSession sqlSession = SqlSessionUtil.getsqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = new User(null, "admi", "123456", 33, "女", "123@qq.com");
mapper.insertUser(user);
}
测试结果:
DEBUG 10-20 11:47:36,007 ==> Preparing: insert into t_user values(null,?,?,?,?,?) (BaseJdbcLogger.java:137)
DEBUG 10-20 11:47:36,033 ==> Parameters: admi(String), 123456(String), 33(Integer), 女(String), 123@qq.com(String) (BaseJdbcLogger.java:137)
DEBUG 10-20 11:47:36,044 <== Updates: 1 (BaseJdbcLogger.java:137)
<table ><tr> <td><img src="https://gitee.com/Lowell_37/picgoImg/raw/master/202310201148099.png" alt="image-20231020114808934" style="zoom:33%;" /></td> <td><img src="https://gitee.com/Lowell_37/picgoImg/raw/master/202310201148199.png" alt="image-20231020114844126" style="zoom:33%;" /></td> </tr></table>
5.5 使用@Param
标识参数
可以通过@Param
注解标识mapper
接口中的方法参数
此时,会将这些参数放在map
集合中,以@Param
注解的value
属性值为键,以参数为值;以param1,param2...
为键,以参数为值;只需要通过${}
和#{}
访问map
集合的键就可以获取相对应的值, 注意${}
需要手动加单引号。
在UserMapper
接口中,新增函数:
/**
* 验证登录(使用@Param)
* @param username
* @param password
* @return
*/
User checkLoginByParam(@Param("username") String username, @Param("password") String password);
在UserMapper.xml
文件中,新增属性,使用${}
和#{}
获取参数的值:
/* 可以在mapper接口方法的参数上设置@Param注解
* 此时MyBatis会将这些参数放在map中,以两种方式进行存储
* a>以@Param注解的value属性值为键,以参数为值
* b>以param1,param2...为键,以参数为值
* 只需要通过#{}和${}访问map集合的键,就可以获取相对应的值,一定要注意${}的单引号问题
*/
<!--User checkLoginByParam(@Param("username") String username, @Param("password") String password);-->
<select id="checkLoginByParam" resultType="User">
select * from t_user where username = #{username} and password = #{password}
</select>
在ParameterTest
中,新增测试:
@Test
public void testCheckLoginByParam(){
SqlSession sqlSession = SqlSessionUtil.getsqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
User user = mapper.checkLoginByParam("admin", "123456");
System.out.println(user);
}
测试结果:
DEBUG 10-20 11:52:32,498 ==> Preparing: select * from t_user where username = ? and password = ? (BaseJdbcLogger.java:137)
DEBUG 10-20 11:52:32,525 ==> Parameters: admin(String), 123456(String) (BaseJdbcLogger.java:137)
DEBUG 10-20 11:52:32,543 <== Total: 1 (BaseJdbcLogger.java:137)
User{id=1, username='admin', password='123456', age=23, gender='男', email='123456@qq.com'}
6. MyBatis的各种查询
6.1 查询一个实体类对象
/**
* 根据id查询用户信息
* @param id
* @return
*/
User getUserById(@Param("id") Integer id);
<!--User getUserById(@Param("id") Integer id);-->
<select id="getUserById" resultType="User">
select * from t_user where id = #{id}
</select>
@Test
public void testGetUserById() {
SqlSession sqlSession = SqlSessionUtil.getsqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
User user = mapper.getUserById(1);
System.out.println("user = " + user);
}
结果如下:
DEBUG 10-23 10:51:40,373 ==> Preparing: select * from t_user where id = ? (BaseJdbcLogger.java:137)
DEBUG 10-23 10:51:40,398 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:137)
DEBUG 10-23 10:51:40,428 <== Total: 1 (BaseJdbcLogger.java:137)
user = User{id=1, username='admin', password='123456', age=23, gender='男', email='123456@qq.com'}
6.2 查询一个list集合
/**
* 查询所有用户信息
* @return
*/
List<User> getAllUser();
<!--List<User> getAllUser();-->
<select id="getAllUser" resultType="User">
select * from t_user
</select>
@Test
public void testGetAllUser() {
SqlSession sqlSession = SqlSessionUtil.getsqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
List<User> list = mapper.getAllUser();
list.forEach(System.out::println);
}
若sql语句查询出的结果为多条时,一定不能以实体类类型作为方法的返回值
否则会抛出异常
TooManyResultException
若sql语句查询出的结果为1条时,此时可以使用实体类类型或者list集合类型作为方法的返回值
结果如下:
DEBUG 10-23 10:53:43,953 ==> Preparing: select * from t_user (BaseJdbcLogger.java:137)
DEBUG 10-23 10:53:43,977 ==> Parameters: (BaseJdbcLogger.java:137)
DEBUG 10-23 10:53:44,000 <== Total: 4 (BaseJdbcLogger.java:137)
User{id=1, username='admin', password='123456', age=23, gender='男', email='123456@qq.com'}
User{id=2, username='root', password='123', age=23, gender='?', email='123456@qq.com'}
User{id=3, username='root', password='123', age=23, gender='?', email='123456@qq.com'}
User{id=4, username='root', password='123', age=23, gender='?', email='123456@qq.com'}
6.3 查询单个数据
/**
* 查询用户的总数量
* @return
*/
Integer getCount();
<!-- Integer getCount();-->
<!--
MyBatis中为Java中常用的类型设置了类型别名
Integer:Integer, int
int: _int, _integer
Map: map
String: string
-->
<select id="getCount" resultType="Integer">
select count(*) from t_user
</select>
@Test
public void testGetCount() {
SqlSession sqlSession = SqlSessionUtil.getsqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
Integer count = mapper.getCount();
System.out.println("count = " + count);
}
结果如下:
DEBUG 10-23 10:57:30,198 ==> Preparing: select count(*) from t_user (BaseJdbcLogger.java:137)
DEBUG 10-23 10:57:30,225 ==> Parameters: (BaseJdbcLogger.java:137)
DEBUG 10-23 10:57:30,254 <== Total: 1 (BaseJdbcLogger.java:137)
count = 4
6.4、查询一条数据为map集合
/**
* 根据id查询用户信息为一个map集合
* @param id
* @return
*/
Map<String, Object> getUserByIdToMap(@Param("id") Integer id);
<!--Map<String, Object> getUserByIdToMap(@Param("id") Integer id);-->
<select id="getUserByIdToMap" resultType="map">
select * from t_user where id = #{id};
</select>
@Test
public void testGetUserByIdToMap() {
SqlSession sqlSession = SqlSessionUtil.getsqlSession();
SelectMapper mapper = sqlSession.getMapper(SelectMapper.class);
Map<String , Object> map = mapper.getUserByIdToMap(4);
System.out.println("map = " + map);
}
结果如下:
DEBUG 10-23 10:59:32,454 ==> Preparing: select * from t_user where id = ?; (BaseJdbcLogger.java:137)
DEBUG 10-23 10:59:32,481 ==> Parameters: 4(Integer) (BaseJdbcLogger.java:137)
DEBUG 10-23 10:59:32,497 <== Total: 1 (BaseJdbcLogger.java:137)
map = {password=123, gender=?, id=4, age=23, email=123456@qq.com, username=root}
6.5、查询多条数据为map集合
1. 方式一
/**
* 查询所有用户信息为map集合
* @return
* 将表中的数据以map集合的方式查询,一条数据对应一个map;若有多条数据,就会产生多个map集合,此
时可以将这些map放在一个list集合中获取
*/
List<Map<String, Object>> getAllUserToMap();
<!--Map<String, Object> getAllUserToMap();-->
<select id="getAllUserToMap" resultType="map">
select * from t_user
</select>
结果如下:
DEBUG 10-23 11:12:37,205 ==> Preparing: select * from t_user (BaseJdbcLogger.java:137)
DEBUG 10-23 11:12:37,231 ==> Parameters: (BaseJdbcLogger.java:137)
DEBUG 10-23 11:12:37,250 <== Total: 4 (BaseJdbcLogger.java:137)
mapList = [{password=123456, gender=男, id=1, age=23, email=123456@qq.com, username=admin}, {password=123, gender=?, id=2, age=23, email=123456@qq.com, username=root}, {password=123, gender=?, id=3, age=23, email=123456@qq.com, username=root}, {password=123, gender=?, id=4, age=23, email=123456@qq.com, username=root}]
2. 方式二
/**
* 查询所有用户信息为map集合
* @return
* 将表中的数据以map集合的方式查询,一条数据对应一个map;若有多条数据,就会产生多个map集合,并
且最终要以一个map的方式返回数据,此时需要通过@MapKey注解设置map集合的键,值是每条数据所对应的
map集合
*/
@MapKey("id")
Map<String, Object> getAllUserToMap();
Map<String, Object> map = mapper.getAllUserToMap();
System.out.println("map = " + map);
结果如下:
DEBUG 10-23 11:16:16,864 ==> Preparing: select * from t_user (BaseJdbcLogger.java:137)
DEBUG 10-23 11:16:16,890 ==> Parameters: (BaseJdbcLogger.java:137)
DEBUG 10-23 11:16:16,909 <== Total: 4 (BaseJdbcLogger.java:137)
map = {1={password=123456, gender=男, id=1, age=23, email=123456@qq.com, username=admin}, 2={password=123, gender=?, id=2, age=23, email=123456@qq.com, username=root}, 3={password=123, gender=?, id=3, age=23, email=123456@qq.com, username=root}, 4={password=123, gender=?, id=4, age=23, email=123456@qq.com, username=root}}
7. 特殊SQL的执行
7.1 模糊查询
/**
* 通过用户名模糊查询用户信息
* @param like
* @return
*/
List<User> getUserByLike(@Param("like") String like);
<!--List<User> getUserByLike(@Param("like") String like);-->
<select id="getUserByLike" resultType="User">
<!--select * from t_user where username like '%${like}%'-->
-- <!--select * from t_user where username like concat('%', #{like}, '%')-->
select * from t_user where username like "%"#{like}"%"
</select>
@Test
public void testGetUserByLike() {
SqlSession sqlSession = SqlSessionUtil.getsqlSession();
SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
List<User> list = mapper.getUserByLike("a");
list.forEach(System.out::println);
}
结果如下:
DEBUG 10-23 11:20:10,540 ==> Preparing: -- select * from t_user where username like "%"?"%" (BaseJdbcLogger.java:137)
DEBUG 10-23 11:20:10,565 ==> Parameters: a(String) (BaseJdbcLogger.java:137)
DEBUG 10-23 11:20:10,595 <== Total: 1 (BaseJdbcLogger.java:137)
User{id=1, username='admin', password='123456', age=23, gender='男', email='123456@qq.com'}
7.2 批量删除
/**
* 批量删除
* @param ids
*/
void deleteMoreUser(@Param("ids") String ids);
<!--void deleteMoreUser(@Param("ids") String ids);-->
<delete id="deleteMoreUser">
delete from t_user where id in(${ids})
</delete>
@Test
public void testDeleteMoreUser() {
SqlSession sqlSession = SqlSessionUtil.getsqlSession();
SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
mapper.deleteMoreUser("3, 4");
}
<table ><tr> <td><img src="https://gitee.com/Lowell_37/picgoImg/raw/master/202310231123854.png" alt="image-20231023112310742" style="zoom: 50%; align="left"" /></td> <td><img src="https://gitee.com/Lowell_37/picgoImg/raw/master/202310231124277.png" alt="image-20231023112403213" style="zoom:50%;" /></td> </tr></table>
7.3 动态设置表名
/**
* 动态设置表名,查询用户信息
* @param tableName
* @return
*/
List<User> getUserList(@Param("tableName") String tableName);
<!--List<User> getUserList(@Param("tableName") String tableName);-->
<select id="getUserList" resultType="User">
select * from ${tableName}
</select>
@Test
public void testGetUserList() {
SqlSession sqlSession = SqlSessionUtil.getsqlSession();
SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
List<User> userList = mapper.getUserList("t_user");
userList.forEach(System.out::println);
}
结果如下:
DEBUG 10-23 11:27:53,026 ==> Preparing: select * from t_user (BaseJdbcLogger.java:137)
DEBUG 10-23 11:27:53,051 ==> Parameters: (BaseJdbcLogger.java:137)
DEBUG 10-23 11:27:53,069 <== Total: 2 (BaseJdbcLogger.java:137)
User{id=1, username='admin', password='123456', age=23, gender='男', email='123456@qq.com'}
User{id=2, username='root', password='123', age=23, gender='?', email='123456@qq.com'}
7.4 添加功能获取自增的主键
/**
* 添加用户信息,并获取自增的主键
* @param user
*/
void insertUser(User user);
<!--void insertUser(User user);-->
<!--
useGeneratedKeys:表示当前添加功能使用自增的主键
keyProperty:将添加的数据的自增主键为实体类类型的参数的属性赋值
-->
<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
insert into t_user values(null, #{username}, #{password}, #{age}, #{gender}, #{email})
</insert>
@Test
public void testInsertUser() {
SqlSession sqlSession = SqlSessionUtil.getsqlSession();
SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
User user = new User(null, "xiaoming", "123456", 21, "男", "123456@qq.com");
mapper.insertUser(user);
System.out.println("user = " + user);
}
<img src="https://gitee.com/Lowell_37/picgoImg/raw/master/202312081053197.png" alt="image-20231023113048365" style="zoom:50%;" />