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

川长思鸟来 2021-08-13 18:02 516阅读 0赞

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