ssm复习之mybatis复习

    4.0.0
    org.example
    mybatis-study
    pom
    1.0-SNAPSHOT
    //子工程
    
        mybatis-01-hello
        mybatis-02-baseKnowledge
    

    

        
        
            mysql
            mysql-connector-java
            5.1.47
        


        
        
            org.mybatis
            mybatis
            3.5.2
        


        
        
            junit
            junit
            4.12
            test
        

        
        
            log4j
            log4j
            1.2.17
        
	 
        
            org.projectlombok
            lombok
            1.18.20
        
    

    
        
            
                src/main/java
                
                    ***.properties
                
                false
            
            
                src/main/resources
                
                    ***.properties
                
                false
            
        
    

package com.atshikai.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

//SqlSessionFactory ----》sqlSession
public class MyBatisUtils {
    private  static SqlSessionFactory sqlSessionFactory;
    static {
        try {
//            使用mybatis的第一步：获取sqlSessionFactory工厂
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
//    相当于getConnection注解的时候使用
    public static SqlSession getSqlSession(){
//        改为true是自动提交事务
        return sqlSessionFactory.openSession(true);
    }
}

1一旦创建SqlSessionFactory，SqlSessionFactoryBuilder就会被销毁—局部变量

2 SqlSessionFactory:可以想象为：数据库连接池，一旦创建在运行期间一直存在单例模式

3 SqlSession连接到连接池的一个请求，不能被共享，用完之后赶紧关闭

4一个mappe就相当于一个具体的业务！！！

    

    
        
        
        
		

    

    
        
    

    
        
            
            
                
                
                
                
            
        
    

    
        //直接绝对路径查找
        
        //下面两个需要在同一个路径下才可以
        

    

package com.atshikai.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data                              //get，set
@NoArgsConstructor                 //无参构造
@AllArgsConstructor                //有参构造
public class User {
    private int id;
    private String name;
    private String pwd;
}

package com.atshikai.dao;
import com.atshikai.pojo.User;
import java.util.List;
import java.util.Map;

public interface UserMapper {
    List getUserList();
//通过id查询
    User selectById(int id);
//    模糊查询
    List selectIgnore(String name);
//  增
    int insertUser(User user);
//   使用map进行添加User对象
    int addUser(Map map);
//    删
    int deleteUser(int id);
//    改
    int updateUser(User user);
}

    
    select * from mybatis.user
  

    
        select * from mybatis.user where id=#{id};
    

    
    
        select * from mybatis.user where name like #{value}
    

    
        insert into mybatis.user(id,name,pwd) values(#{id},#{name},#{pwd});
    


    
        insert into mybatis.user values(#{id},#{name},#{pwd});
    


    
        delete from mybatis.user where id=#{id};
    

    
        update mybatis.user set name=#{name},pwd=#{pwd} where id=#{id};
    


 
7:进行测试 
import com.atshikai.dao.UserMapper;
import com.atshikai.pojo.User;
import com.atshikai.utils.MyBatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MyTest {
    @Test
    public void testSelect(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List userList = mapper.getUserList();
        for (User user:userList){
            System.out.println(user);
        }
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void testInsert(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.insertUser(new User(4, "盖世凯5", "666"));
        System.out.println(i);
        sqlSession.commit();
        sqlSession.close();
    }

//    用map进行添加用户
    @Test
    public void testMapAdd(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        Map map=new HashMap();
        map.put("id",7);
        map.put("name","张建");
        map.put("pwd","888");
        mapper.addUser(map);
        sqlSession.commit();
        sqlSession.close();

    }

//    模糊查询
    @Test
    public void selectIgnore(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List users = mapper.selectIgnore("%盖%");
        for(User user:users){
            System.out.println(user);
        }
        sqlSession.commit();
        sqlSession.close();
    }
    @Test
    public void selectById(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = mapper.selectById(2);
        System.out.println(user);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void deleteUser(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.deleteUser(2);
        System.out.println(i);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void updateUser(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int i = mapper.updateUser(new User(1, "凯凯", "888"));
        System.out.println(i);
        sqlSession.commit();
        sqlSession.close();
    }
}

 
3:写sql#() 和${}的区别 
都可以使用但是#{} 可以防止sql注入，更安全
 
4resultType和resultMap 
resultType
 1：resultType可以把查询结果封装到pojo类型中，但必须pojo类的属性名和查询到的数据库表的字段名一致。
 2：如果sql查询到的字段与pojo的属性名不一致，则需要使用resultMap将字段名和属性名对应起来，进行手动配置封装，将结果映射到pojo中
 
resultMap
 resultMap可以实现将查询结果映射为复杂类型的pojo，比如在查询结果映射对象中包括pojo和list实现一对一查询和一对多查询。
 
5:多表查询(一对多,多对一) 
总结：注意点 
 
多对一(多个学生对应一个老师) 





    
        select s.id sid,s.name sname,t.id,t.name tname
        from student s,teacher t
        where s.tid=t.id;
    
    
        
        
        
        
            
            
        
    

    
    
        
        

        
    
    
        select * from student
    
    
        select * from teacher where id=#{Tid};
    

    
        select * from teacher t where t.id=#{Tid}
    
    
        
        
        
    
    
        select * from student where tid=#{Tid}
    


    
        select s.id sid,s.name sname, t.id tid,t.name tname
        from student s,teacher t
        where s.tid=tid and tid=#{Tid}
    
    
        
        
        
            
            
            
        
    


 
5:动态sql语句 
对于自动生成的id类
 
package com.atshikai.utils;

import java.util.UUID;

public class IdUtils {
    public static String getId(){
        return UUID.randomUUID().toString().replace("-","");
    }
}
 
 
 
所谓的动态sql，本质还是sql语句，只是在里面加了逻辑代码
 
 
sql片段：查询include(尽量include中不使用where) 
 
        
            and title=#{title}
        
        
            and author=#{author}
        
    

    select * from blog where 1=1
    

查询where,choose,when(where嵌套可以智能的去掉无用的and，如果where嵌套的没有东西，不影响代码执行)

    select * from blog
    
        
            
                and title=#{title}
            
            
                and author=#{author}
            
            
                views=#{views}
            
        
    


 
更新set(可以智能的去掉不需要的，如果set中没有东西，就会出错) 

    update blog
    
        
            title=#{title},
        
        
            author=#{author}
        
    
    where id=#{id}


 
查询forEach 

    select * from blog
    
        
            id=#{id}
        
    

详细看狂神27,28,29集

小结：
一级缓存:相当于一个map
缓存失效的情况：
1查询不同的东西；
2增删改操作，可能会改变之前的操作，所以必定刷新
3查询不同的Mapper.xml
4手动清理缓存(一级缓存默认是开启的，关不掉)sqlSession.clearCache();
二级缓存
就是我(一级缓存)死了，然后吧缓存遗传给你(二级缓存)