springboot 和 mybatis整合:参数查询和动态sql
程序员文章站
2022-03-25 14:54:19
...
springboot 和 mybatis整合:
mapper定义的是数据库的操作方法:
@Mapper
public interface UserMapper {}
单参数的处理:
@Select("select * from t_user")
@Results({
@Result(property = "userId", column = "USER_ID"),
@Result(property = "username", column = "USERNAME"),
@Result(property = "password", column = "PASSWORD"),
@Result(property = "mobileNum", column = "PHONE_NUM")
})
List<User> list();
多参数的处理:
/**
* 延伸:无论什么方式,如果涉及多个参数,则必须加上@Param注解,否则无法使用EL表达式获取参数。
*/
@Select("select * from t_user where username like #{username} and password like #{password}")
@Results({
@Result(property = "userId", column = "USER_ID"),
@Result(property = "username", column = "USERNAME"),
@Result(property = "password", column = "PASSWORD"),
@Result(property = "mobileNum", column = "PHONE_NUM")
})
User get(@Param("username") String username, @Param("password") String password);
CRUD的高级注解:
@SelectProvider
@InsertProvider
@UpdateProvider
@DeleteProvider
先在mapper下定义Provider,所谓的Provider就是自定义的sql语句,规则可以自定义
public class UserSqlProvider {
/**
* 方式1:在工具类的方法里,可以自己手工编写SQL。
*/
public String listByUsername(String username) {
return "select * from t_user where username =#{username}";
}
}
然后定义使用这个sql语句的方法,就在对应的mapper里面操作:
/**
* 方式2:使用注解指定某个工具类的方法来动态编写SQL.
*/
@SelectProvider(type = UserSqlProvider.class, method = "listByUsername")
@Results({
@Result(property = "userId", column = "USER_ID"),
@Result(property = "username", column = "USERNAME"),
@Result(property = "password", column = "PASSWORD"),
@Result(property = "mobileNum", column = "PHONE_NUM")
})
List<User> listByUsername(String username);
controller里面使用:
@GetMapping("list/{username}")
public List<User> listByUsername(@PathVariable("username") String username) {
return userMapper.listByUsername(username);
}
another samples:
UserSqlProvider.java
/**
* 方式2:也可以根据官方提供的API来编写动态SQL。
*/
public String getBadUser(@Param("username") String username, @Param("password") String password) {
return new SQL() {{
SELECT("*");
FROM("t_user");
if (username != null && password != null) {
WHERE("username like #{username} and password like #{password}");
} else {
WHERE("1=2");
}
}}.toString();
}
UserMapper.java
@SelectProvider(type = UserSqlProvider.class, method = "getBadUser")
@Results({
@Result(property = "userId", column = "USER_ID"),
@Result(property = "username", column = "USERNAME"),
@Result(property = "password", column = "PASSWORD"),
@Result(property = "mobileNum", column = "PHONE_NUM")
})
User getBadUser(@Param("username") String username, @Param("password") String password);
UserController.java
@GetMapping("get/bad/{username}/{password}")
public User getBadUser(@PathVariable("username") String username, @PathVariable("password") String password) {
return userMapper.getBadUser(username, password);
}
推荐阅读
-
Mybatis下动态sql中##和$$的区别讲解
-
Mybatis模糊查询和动态sql语句的用法
-
mybatis_05动态SQL_if和where
-
SpringBoot 整合jdbc和mybatis
-
MyBatis入门(二)—— 输入映射和输出映射、动态sql、关联查询
-
MyBatis——动态SQL语句——if标签和where标签复合使用
-
springboot 整合mybatis和druid数据源连接池
-
springboot整合mybatis和分页插件pagehelper
-
FluentMybatis实现mybatis动态sql拼装和fluent api语法
-
Mybatis 不确定条件查询(动态语句where和if)借助实体类封装参数