MyBatis知识总结和梳理 （3）Mybatis动态Sql

本文为上课时的实例总结，篇幅较长，耐心阅览

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

public List findByUserC(User user);

(2)UserMapper.xml

      select * from smbms_user where 1 = 1
      
                and userName = #{userName}
      
      
                and userPassword = #{userPassword}
      
    

(3)TestUserMapper.java

@Test
    public void testfindByUserC() throws IOException {
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        User user = new User();
        user.setUserName("张华");
//        user.setUserPassword("0000000");
        List list = sqlSession.getMapper(UserMapper.class).findByUserC(user);
        for(User u:list){
            System.out.println(u.getGender());
        }
        sqlSession.close();
    }

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

public Integer addUserC(User user);

(2)UserMapper.xml

        insert into smbms_user(
        
            
                userName,
            
            
                userPassword,
            
        
        )
        values(
        
            
                #{userName},
            
            
                #{userPassword},
            
        
        )
    

(3)TestUserMapper.java

@Test
    public void testaddUserC() throws IOException {
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        User user = new User();
        user.setUserName("张华3333333");
//        user.setUserPassword("0000000");
        int flag = sqlSession.getMapper(UserMapper.class).addUserC(user);
        if (flag>0){
            System.out.println("添加成功");
        }else{
            System.out.println("添加失败");
        }
        sqlSession.commit();
        sqlSession.close();
    }

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

public Integer updateUserC(User user);

(2)UserMapper.xml

            update smbms_user
            
                userCode = #{userName},
                userPassword = #{userPassword },
            
    

4.功能-参数为集合list (1)UserMapper.java

public List findByIds(List idList);

(2)UserMapper.xml

 
        select * from smbms_user
        where id in
        
            #{idList}
        
    
 
(3)UserMapperTest.java 
@Test
    public void testfindByIds() throws IOException {
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        List idList = new ArrayList<>();
        idList.add(7);
        idList.add(10);
        idList.add(12);
        List byIds = sqlSession.getMapper(UserMapper.class).findByIds(idList);
        for(User user :byIds){
            System.out.println(user.getUserName());
        }
        sqlSession.close();
    }
 
5.功能-参数为map 
(1)UserMapper.java 
public List findByMap(Map idmap);
 
(2)UserMapper.xml 

        select * from smbms_user
        where id in
        
            #{idmap}
        
    

(3)TestUserMapper.java

@Test
    public void demofindByMap(){
        SqlSession sqlSession = MyBatisUtil.createSqlSession();
        Map map = new HashMap();
        List list = new ArrayList();
        list.add(1);
        list.add(5);
        map.put("idKeys",list);

        List lists = sqlSession.getMapper(UserMapper.class).findByMap(map);
        for (int i = 0; i