mybatis进阶操作

mybatis进阶操作

动态sql

 <select id="findUserByCondition" resultMap="userMap" parameterType="user">
        select * from user
        <where>
            <if test="userName != null">
                and username = #{userName}
            </if>
            <if test="userSex != null">
                and sex = #{userSex}
            </if>
        </where>
    </select>

    <!-- 根据queryvo中的Id集合实现查询用户列表 -->
    <select id="findUserInIds" resultMap="userMap" parameterType="queryvo">
        <include refid="defaultUser"></include>
        <where>
            <if test="ids != null and ids.size()>0">
                <foreach collection="ids" open="and id in (" close=")" item="uid" separator=",">
                    #{uid}
                </foreach>
            </if>
        </where>
    </select>

UNPOOLED

在sqlmapconfig.xml中,

每次新建一个连接

POOLED

从数据库连接池中取连接

Created connection 926370398.

Returned connection 926370398 to pool.

mybatis缓存

一级缓存

一级缓存是 SqlSession 范围的缓存，当调用 SqlSession 的修改，添加，删除，commit()，close()等方

法时，就会清空一级缓存。

User user1 = userDao.findById(41);
User user2 = userDao.findById(41);
//两次查询为同一个对象
User user3 = userDao.findById(41);
sqlSession.close();
sqlSession = factory.openSession();
User user4 = userDao.findById(41);
//两次查询不是同一个对象
sqlSession.clearCache();//也可以清空缓存

二级缓存

它指的是Mybatis中SqlSessionFactory对象的缓存。由同一个SqlSessionFactory对象创建的SqlSession共享其缓存

	sqlmapconfig.xml
	<settings>
        <setting name="cacheEnabled" value="true"/>
    </settings>
    
    UserDao.xml
    <!--开启user支持二级缓存-->
    <cache/>
    <select id="findById" parameterType="INT" resultType="user" useCache="true">
        select * from user where id = #{uid}
    </select>

	@Before//用于在测试方法执行之前执行
    public void init()throws Exception{
        //1.读取配置文件，生成字节输入流
        in = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.获取SqlSessionFactory
        factory = new SqlSessionFactoryBuilder().build(in);

    }
    @After//用于在测试方法执行之后执行
    public void destroy()throws Exception{
        in.close();
    }
    @Test
    public void testCache(){
        SqlSession sqlSession1 = factory.openSession();
        IUserDao dao1 = sqlSession1.getMapper(IUserDao.class);
        User user1 = dao1.findById(41);
        System.out.println(user1);
        sqlSession1.close();//一级缓存消失

        SqlSession sqlSession2 = factory.openSession();
        IUserDao dao2 = sqlSession2.getMapper(IUserDao.class);
        User user2 = dao2.findById(41);
        System.out.println(user2);
        sqlSession2.close();
        System.out.println(user1 == user2);//两次不想等,二级缓存存储的为数据而不是对象
    }

注解开发

实体类字段与数据库无法对应

	@Select("select * from user")
    @Results(id = "userMap",value={
            //数据库字段和实体类不一致时
            @Result(id = true,column = "id",property = "userId"),
            @Result(column = "username",property = "userName"),
            @Result(column = "address",property = "userAddress"),
            @Result(column = "sex",property = "userSex"),
            @Result(column = "birthday",property = "userBirthday"),
    })
    List<User> findAll();

    @Select("select * from user where id = #{id}")
    @ResultMap(value = {"userMap"})
    User finById(Integer id);

mybatis一对一查询

修改实体类

@Getter
@Setter
@ToString
public class Account implements Serializable {
    private Integer id;
    private Integer uid;
    private Double money;
    //一对一,一个账户属于一个用户
    private User user;
}

AccountDao

@Select("select * from account")
    @Results(id = "accountMap",value = {
            @Result(id = true,column = "id",property = "id"),
            @Result(column = "uid",property = "uid"),
            @Result(column = "money",property = "money"),
            //定义一对一 ,       封装对象         根据UID查                查询方法                                   立即加载
            @Result(property = "user",column = "uid",one = @One(select="com.cc.annotation.dao.UserDao.finById",fetchType=
                    FetchType.EAGER))
    })
    List<Account> findAll();

UserDao

 @Select("select * from user where id = #{id}")
    @ResultMap(value = {"userMap"})
    User finById(Integer id);

mybatis一对多

修改实体类

@Getter
@Setter
@ToString
public class User implements Serializable {
    private Integer userId;
    private String userName;
    private String userSex;
    private String userAddress;
    private Date userBirthday;
//一对多,一个用户对应多个账户
    private List<Account> accounts;

UserDao

@Select("select * from user")
@Results(id = "userMap",value={
//数据库字段和实体类不一致时
@Result(id = true,column = "id",property = "userId"),
@Result(column = "username",property = "userName"),
@Result(column = "address",property = "userAddress"),
@Result(column = "sex",property = "userSex"),
@Result(column = "birthday",property = "userBirthday"),
@Result(property = "accounts",column = "id",
many = @Many(select= "com.cc.annotation.dao.AccountDao.finById",
fetchType= FetchType.LAZY))
})
List<User> findAll();

AccountDao

    @Select("select * from account where uid = #{userId}")
    List<Account> finById(Integer userId);

二级缓存

在dao接口类上加注解

//@CacheNamespace(blocking = true)   开启二级缓存