MyBatis--基于注解的动态SQL实例

POJO

package pers.zhang.pojo;

public class UserInfo {
    private int id;
    private String userName;
    private String password;
    private String realName;
    private String sex;
    private String address;
    private String emial;
    private String regDate;
    private int status;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getRealName() {
        return realName;
    }

    public void setRealName(String realName) {
        this.realName = realName;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getEmial() {
        return emial;
    }

    public void setEmial(String emial) {
        this.emial = emial;
    }

    public String getRegDate() {
        return regDate;
    }

    public void setRegDate(String regDate) {
        this.regDate = regDate;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "UserInfo{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                ", realName='" + realName + '\'' +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                ", emial='" + emial + '\'' +
                ", regDate='" + regDate + '\'' +
                ", status=" + status +
                '}';
    }
}

Mapper

package pers.zhang.mapper;

import org.apache.ibatis.annotations.*;
import pers.zhang.pojo.UserInfo;

import java.util.List;
import java.util.Map;

public interface UserInfoMapper {

    @SelectProvider(type = UserInfoDynaSqlProvider.class, method = "selectWithParam")
    List<UserInfo> find_userInfoByCond(Map<String, Object> param);

    @InsertProvider(type = UserInfoDynaSqlProvider.class, method = "insertUserInfo")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert_UserInfo(UserInfo userInfo);

    @UpdateProvider(type = UserInfoDynaSqlProvider.class, method = "updateUserInfo")
    int update_userInfo(UserInfo userInfo);

    @DeleteProvider(type = UserInfoDynaSqlProvider.class, method = "deleteUserInfo")
    void delete_UserInfo(Map<String, Object> param);
}

动态生成SQL类

package pers.zhang.mapper;

import jdk.nashorn.internal.objects.annotations.Where;
import org.apache.ibatis.jdbc.SQL;
import pers.zhang.pojo.UserInfo;

import java.util.Map;

/**
 * @author zhang
 * @date 2019/11/30 - 16:31
 */
public class UserInfoDynaSqlProvider {

    public String selectWithParam(Map<String, Object> param){
        return new SQL(){
            {
                SELECT("*");
                FROM("user_info");
                if(param.get("id") != null){
                    WHERE("id = #{id}");
                }
                if(param.get("userName") != null){
                    WHERE("userName = #{userName}");
                }if(param.get("password") != null){
                    WHERE("password = #{password}");
                }
            }
        }.toString();
    }


    public String insertUserInfo(UserInfo userInfo){
        return new SQL(){
            {
                INSERT_INTO("user_info");
                if(userInfo.getUserName() != null){
                    VALUES("userName", "#{userName}");
                }
                if(userInfo.getPassword() != null){
                    VALUES("password", "#{password}");
                }
            }
        }.toString();
    }


    public String updateUserInfo(UserInfo userInfo){
        return new SQL(){
            {
                UPDATE("user_info");
                if(userInfo.getUserName() != null){
                    SET("userName = #{userName}");
                }
                if(userInfo.getPassword() != null){
                    SET("password = #{password}");
                }
                WHERE("id = #{id}");
            }
        }.toString();
    }

    public String deleteUserInfo(Map<String, Object> param){
        return new SQL(){
            {
                DELETE_FROM("user_info");
                if(param.get("id") != null){
                    WHERE("id = #{id}");
                }
                if(param.get("userName") != null){
                    WHERE("userName = #{userName}");
                }
                if(param.get("password") != null){
                    WHERE("password = #{password}");
                }
            }
        }.toString();
    }
}

测试

//测试基于注解的动态SQL语句之@SelectProvider注解
    @Test
    public void testfindUserInfoByCond(){
        Map<String, Object> param = new HashMap<String, Object>();
        param.put("userName", "tom");
        param.put("password", "123456");
        List<UserInfo> list = sqlSession.getMapper(UserInfoMapper.class).find_userInfoByCond(param);
        for (UserInfo u : list) {
            System.out.println(u);
        }
    }

    //测试基于注解的动态SQL语句之@InsertProvider注解
    @Test
    public void testInsertUserInfo(){
        UserInfo userInfo = new UserInfo();
        userInfo.setUserName("mybatis2");
        userInfo.setPassword("123456");
        int result = sqlSession.getMapper(UserInfoMapper.class).insert_UserInfo(userInfo);
        System.out.println("插入的用户编号："  + userInfo.getId());
    }

    //测试基于注解的动态SQL语句之@UpdateProvider注解
    @Test
    public void testUpdateUser(){
        Map<String, Object> param = new HashMap<String, Object>();
        param.put("id", "1");
        UserInfoMapper mapper = sqlSession.getMapper(UserInfoMapper.class);
        UserInfo userInfo = mapper.find_userInfoByCond(param).get(0);
        userInfo.setPassword("666666");
        mapper.update_userInfo(userInfo);
    }

    //测试基于注解的动态SQL语句之@DeleteProvider注解
    @Test
    public void testDeleteUser(){
        Map<String, Object> param = new HashMap<String, Object>();
        param.put("userName", "mybatis2");
        param.put("password", "123456");
        sqlSession.getMapper(UserInfoMapper.class).delete_UserInfo(param);
    }