2021-08-22XML中select语句⽰范：
标题当我们使⽤⽤户名称模糊查⽤户，或者使⽤⽤户昵称模糊查⽤户时
众所周知,SQL语句中的like模糊查询 select * from table where name like‘%张全蛋%’,
但实际开发中经常⽤到 select * from table where name like concat(#{name},’%’)
⽐如根据⽤户名或⼿机号模糊查⽤户
(us.nick_name LIKE CONCAT(’%’,#{keyWord},’%’) OR us.phone LIKE CONCAT(’%’,#{keyWord},’%’)) XML⽅式：
1. 创建⼀个DAO⽅法：
List<UserDO> query(@Param("keyWord")String keyWord);
2. XML中写代码
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN"
"/dtd/mybatis-3-mapper.dtd">
<mapper namespace="ukedament.dao.UserDAO">
<resultMap id="userResultMap"type="ukedament.dataobject.UserDO">
<id column="id"property="id"/>
<result column="user_name"property="userName"/>
<result column="pwd"property="pwd"/>
<result column="nick_name"property="nickName"/>
张全蛋<result column="avatar"property="avatar"/>
<result column="gmt_created"property="gmtCreated"/>
<result column="gmt_modified"property="gmtModified"/>
</resultMap>
<insert id="add"parameterType="ukedament.dataobject.UserDO"useGeneratedKeys="true"keyProperty="id">
INSERT INTO user (user_name, pwd, nick_name,avatar,gmt_created,gmt_modified)
VALUES(#{userName}, #{pwd}, #{nickName}, #{avatar},now(),now())
</insert>
<update id="update"parameterType="ukedament.dataobject.UserDO">
update user set nick_name=#{nickName},gmt_modified=now() where id=#{id}
</update>
<delete id="delete">
delete from user where id=#{id}
</delete>
<select id="findAll"resultMap="userResultMap">
select * from user
</select>
<select id="findByUserName"resultMap="userResultMap">
select * from user where user_name=#{userName} limit 1
</select>
<select id="query"resultMap="userResultMap">
select * from user where user_name like CONCAT('%',#{keyWord},'%')
or nick_name like CONCAT('%',#{keyWord},'%')
</select>
</mapper>
3. 控制层写代码
package;
import UserDAO;
import UserDO;
import Autowired;
import Controller;
import*;
import List;
@Controller
public class UserController {
@Autowired
private UserDAO userDAO;
@GetMapping("/users")
@ResponseBody
public List<UserDO>getAll(){
return userDAO.findAll();
}
@PostMapping("/user")
@ResponseBody
public UserDO save(@RequestBody UserDO userDO){
userDAO.add(userDO);
return userDO;
}
@PostMapping("/user/update")
@ResponseBody
public UserDO update(@RequestBody UserDO userDO){
userDAO.update(userDO);
return userDO;
}
@GetMapping("/user/del")
@ResponseBody
public boolean delete(@RequestParam("id") Long id){
return userDAO.delete(id)>0;
}
@GetMapping("/user/findByUserName")
@ResponseBody
public UserDO findByUserName(@RequestParam("userName") String userName){ return userDAO.findByUserName(userName);
}
@GetMapping("/user/query")
@ResponseBody
public List<UserDO>query(@RequestParam("keyWord") String keyWord){
return userDAO.query(keyWord);
}
}