欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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);
    }