Mybatis动态Sql

1.查询功能 (1)UserMapper.java

//动态sql=案例1 通过用户名和密码查询列表
    public List findByUserNameAndPwd(@Param("userName") String userName,@Param("userPassword") String userPassword);
    

(2)UserMapper.xml

        select * from smbms_user a where 1=1
        
            and userName like concat('%',#{userName},'%')
        
        
            and userPassword = #{userPassword}
        
    

(3)TestUserMapper.java

@Test
    public void testfindByUserNameAndPwd() {
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        String userName = "王";
        String userPassword = "";
        List list= sqlSession.getMapper(UserMapper.class).findByUserNameAndPwd(userName,userPassword);
       for (User u :list){
           System.out.println("姓名："+u.getUserName()+"   出生日期："+u.getBirthday());
       }
        sqlSession.close();
    }

(4)测试结果

 

2.添加功能 (1)UserMapper.java

 //添加功能动态sql
    public Integer addUserNew(User user);

(2)UserMapper.xml

 
        insert into smbms_user(
        
        
            userCode,
        
        
            userName,
        
        
            userPassword,
        
            
                gender,
            
            
                birthday,
            
            
                phone,
            
            
                address,
            
            
                userRole,
            
            
                createdBy,
            
            
                creationDate,
            
            
                userPassword,
            
            
                modifyDate,
            

        )
        values (
        
            
                #{userCode},
            
            
                #{userName},
            
            
                #{userPassword},
            
            
                #{gender},
            
            
                #{birthday},
            
            
                #{phone},
            
            
                #{address},
            
            
                #{userCode},
            
            
                #{createdBy},
            
            
                #{creationDate},
            
            
                #{modifyBy},
            
            
                #{modifyDate},
            
        
        )
    

(3)TestUserMapper.java

 @Test
    public void testaddUserNew() {
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        User user = new User();
        user.setUserName("wangwu");
        sqlSession.getMapper(UserMapper.class).addUserNew(user);
        sqlSession.commit();
        sqlSession.close();
    }

3.修改功能 (1)UserMapper.java

//修改功能动态sql
    public  Integer updateUserNew(User user);

(2)UserMapper.xml

 
        update  smbms_user
        
        
            userCode=#{userCode},
        
        
            userName= #{userName},
        
        
            userPassword= #{userPassword},
        
        
            gender= #{gender},
        
        
            birthday=#{birthday},
        
        
            phone= #{phone},
        
        
            address=#{address},
        
        
            userRole=#{userCode},
        
        
            createdBy= #{createdBy},
        
        
            creationDate= #{creationDate},
        
        
            modifyBy=#{modifyBy},
        
        
            modifyDate=#{modifyDate},
        
        

    

(3)TestUserMapper.java

 @Test
    public void testupdateUserNew() {
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        User user = new User();
        user.setId(18);
        user.setUserName("pgw");
        sqlSession.getMapper(UserMapper.class).updateUserNew(user);
        sqlSession.commit();
        sqlSession.close();
    }

4.功能-参数为集合list

(1)UserMapper.java

//参数list查询功能
    public  List findByList(List idList);

(2)UserMapper.xml

        select  * from smbms_user
        where id in
        
            #{idList}
    
     
(3)UserMapperTest.java
 
 @Test
    public void testfindByList() {
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        ArrayList list = new ArrayList<>();
        list.add(5);
        list.add(6);
        list.add(10);
        List list2=sqlSession.getMapper(UserMapper.class).findByList(list);
        for (User u:list2){
            System.out.println("userName:"+u.getUserName()+"  id:"+u.getId());
        }
        sqlSession.close();
    }
 
5.功能-参数为map 
(1)UserMapper.java 
 //参数map查询功能
    public List findByMapNew(Map map);
 
(2)UserMapper.xml 

        select  * from smbms_user
        where id in
        
            #{map}
        
    

(3)TestUserMapper.java

 @Test
    public void testfindByMapNew() {
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        HashMap map = new HashMap<>();
        ArrayList