MyBatis入门

MyBatis

1.概述

MyBatis是一款优秀的持久层框架，它支持自定义SQL，存储过程以及高级映射，MyBatis免去了几乎所有的JDBC代码 以及设置参数，获取结果集对象的工作。MyBatis可以通过简单的XML配置或注解来配置映射关系。

复习JDBC

//1.注册驱动
Class.forName("com.mysql.jdbc.Driver")
//2.获取连接
Connection connection = Drivermanager.getConnection("jdbc:mysql://localhost:3306/test","root","123456abcd");    
//3.定义sql
String sql = "selet * from student where id=?";
//4.预编译
PreparedStatement statment = connection.prepareStatement(sql);
//5.设置参数
statement.setInteger(1,100);
//6.执行sql,封装结果
ResultSet result = statement.executeQuery();

2.为什么使用MyBatis

• JDBC
  • SQL编写在代码中，耦合度比较高
  • 实际开发中SQL经常被更新，维护不易
• Hibernate
  • 内部自动生成SQL，不方便特殊优化
  • 长难复制的SQL，对Hibernate来说处理很不容易
  • 基于全映射的全自动框架，进行字段部分映射比较困难，并且会导致数据库性能下降。
• MyBatis
  • SQL和Java编码分离，功能划分清楚，一个专注数据，一个专注业务
  • 核心SQL可以自己编写，优化比较方便

3.MyBatis环境搭建

3.1环境搭建

步骤：

1. 创建Maven工程，并导入相关依赖
2. 创建实体类
3. 创建dao接口：UserDao
4. 创建MyBatis主配置文件：SqlMapConfig.xml
5. 创建映射配置文件：UserDao.xml

注意事项

1. 创建directory 和 package的区别
   • directory : com.wdzl.dao创建的是一级目录
   • package : com.wdzl.dao 创建了三级目录
2. MyBatis的映射配置文件所在目录层级要与对应的dao层接口目录层级相同
3. 映射配置文件mapper标签namespace属性的值必须对应接口的全限定类名
4. 映射配置文件select标签id属性必须与对应接口中的方法名一致，不能随便写
5. 只要遵循2，3，4的规定，我们就不用去写接口的实现类

3.2测试案例

步骤

1. 读取配置文件
2. 创建SqlSessionFactory —> 通过SqlSessionFactoryBuilder对象获取
3. 使用工厂对象生产SqlSession对象
4. 使用SqlSession对象获取接口的代理对象
5. 使用代理对象调用方法
6. 释放资源
   

4.自定义MyBatis

MyBatis在使用代理模式实现增删改查都做了什么

1. 创建代理对象
2. 在代理对象中创建方法

5.CRUD

public interface UserDao {
    /**
     * 查询所有用户
     */
    List<User> findAll();

    /**
     * 添加用户
     * @param user
     */
    void addUser(User user);

    /**
     * 修改用户信息
     */
    void updateUser(User user);

    /**
     * 删除用户
     * @param user
     */
    void delUser(User user);

    /**
     * 模糊查询 根据用户名模糊查询
     */
    List<User> findUserByName(String name);

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wdzl.dao.UserDao"> <!--对应dao层接口的全限定类名-->
    <!--查询所有用户信息-->
    <select id="findAll" resultType="com.wdzl.pojo.User">
        select * from user;
    </select>

    <!--添加用户-->
    <insert id="addUser" parameterType="com.wdzl.pojo.User">
        insert into user(username,birthday,sex,address) values (#{username},#{birthday},#{sex},#{address})
    </insert>

    <update id="updateUser" parameterType="com.wdzl.pojo.User">
        update user set address = #{address} where id = #{id}
    </update>

    <delete id="delUser" parameterType="com.wdzl.pojo.User">
        delete from user where id=#{id}
    </delete>

    <select id="findUserByName" parameterType="String" resultType="com.wdzl.pojo.User">
        select * from user where username like #{name}
    </select>

</mapper>

package com.wdzl.dao;


import com.wdzl.pojo.User;
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 org.junit.Test;

import javax.xml.crypto.Data;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * UserDao测试类
 * MyBatis 入门案例测试实现步骤
 *      1.读取配置文件
 *      2.创建sqlSessionFactory工厂
 *      3.用工厂生产SqlSession
 *      4.使用SqlSession对象创建dao层代理对象
 *      5.使用代理对象执行方法
 *      6.释放资源
 */
public class UserDaoTest {
    @Test
    public void findAllTest() throws IOException {
        //1.读取配置文件
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.创建sqlSessionFactory工厂
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(resourceAsStream);
        //3.用工厂生产SqlSession
        SqlSession session = factory.openSession();
        //4.使用SqlSession对象创建dao层代理对象
        UserDao userDao = session.getMapper(UserDao.class);
        //5.使用代理对象执行方法
        List<User> users = userDao.findAll();
        for (User user : users) {
            System.out.println(user);
        }
        //6.释放资源
        session.close();
        resourceAsStream.close();

    }

    @Test
    public void addUserTest() throws IOException {
        /*      1.读取配置文件
         *      2.创建sqlSessionFactory工厂
         *      3.用工厂生产SqlSession
         *      4.使用SqlSession对象创建dao层代理对象
         *      5.使用代理对象执行方法
         *      6.释放资源*/
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");

        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(resourceAsStream);

        SqlSession sqlSession = factory.openSession();
        UserDao userDao = sqlSession.getMapper(UserDao.class);
        Date date = new Date();
        userDao.addUser(new User(0,"张三",date,"男","西安"));

        sqlSession.commit();

        sqlSession.close();
        resourceAsStream.close();

    }

    @Test
    public void updateUserTest() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");

        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(resourceAsStream);

        SqlSession sqlSession = factory.openSession();

        UserDao userDao = sqlSession.getMapper(UserDao.class);

        Date date = new Date();
        userDao.updateUser(new User(3,"1",date,"1","1"));

        sqlSession.commit();

        sqlSession.close();
        resourceAsStream.close();


    }

    @Test
    public void delUserTest() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");

        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(resourceAsStream);

        SqlSession sqlSession = factory.openSession();

        UserDao userDao = sqlSession.getMapper(UserDao.class);

        User user = new User();
        user.setId(1);
        userDao.delUser(user);

        sqlSession.commit();

        sqlSession.close();
        resourceAsStream.close();


    }

    @Test
    public void findUserByNameTest() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");

        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        SqlSessionFactory factory = builder.build(resourceAsStream);

        SqlSession sqlSession = factory.openSession();

        UserDao userDao = sqlSession.getMapper(UserDao.class);

        List<User> users = userDao.findUserByName("%南%");
        for (User user : users) {
            System.out.println(user);
        }



        sqlSession.commit();

        sqlSession.close();
        resourceAsStream.close();

    }
}

<!--添加用户-->
<insert id="addUser" parameterType="com.wdzl.pojo.User">
   		<!--keyProperty : 返回的值 order : sql语句执行之后或者之前-->
        <selectKey keyProperty="id" order="BEFORE" resultType="int"> 
            select last_insert_id()
        </selectKey>
        insert into user(username,birthday,sex,address) values (#{username},#{birthday},#{sex},#{address})
</insert>

6.当属性名与字段名不同时

7.自建impl

package com.wdzl.dao.impl;

import com.wdzl.dao.UserDao;
import com.wdzl.pojo.User;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import java.util.List;

public class UserDaoImpl implements UserDao {
    private SqlSessionFactory factory;

    public UserDaoImpl() {
    }

    public UserDaoImpl(SqlSessionFactory factory) {
        this.factory = factory;
    }

    @Override
    public List<User> findAll() {
        //获取sqlSession对象
        SqlSession sqlSession = factory.openSession();
        //
        List<User> users = sqlSession.selectList("com.wdzl.dao.UserDao.findAll");

        return users;
    }

    @Override
    public void addUser(User user) {
        SqlSession sqlSession = factory.openSession();
        sqlSession.insert("com.wdzl.dao.UserDao.addUser",user);

        sqlSession.commit();
        sqlSession.close();
    }

    @Override
    public void updateUser(User user) {
        SqlSession sqlSession = factory.openSession();
        sqlSession.update("com.wdzl.dao.UserDao.updateUser", user);

        sqlSession.commit();
        sqlSession.close();
    }

    @Override
    public void delUser(User user) {
        SqlSession sqlSession = factory.openSession();
        sqlSession.delete("com.wdzl.dao.UserDao.delUser", user);

        sqlSession.commit();
        sqlSession.close();

    }

    @Override
    public User findUserByName(String name) {
        SqlSession sqlSession = factory.openSession();
        User user = sqlSession.selectOne("com.wdzl.dao.UserDao.findUserByName", name);

        return user;
    }

    @Override
    public int findTotal() {
        SqlSession sqlSession = factory.openSession();
        int i = sqlSession.selectOne("com.wdzl.dao.UserDao.findTotal");
        return i;
    }



}

package com.wdzl.dao;

import com.wdzl.dao.impl.UserDaoImpl;
import com.wdzl.pojo.User;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import javax.xml.crypto.Data;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;

public class UserDaoTest {
    private InputStream resourceAsStream;
    private SqlSessionFactoryBuilder builder;
    private SqlSessionFactory factory;

    @Before
    public void first() throws IOException {
        //1.读取配置文件
         resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.创建sqlSessionFactory工厂
         builder = new SqlSessionFactoryBuilder();
         factory = builder.build(resourceAsStream);
    }

    @After
    public void last() throws IOException {
        resourceAsStream.close();

    }

    @Test
    public void findAllTest() throws IOException {
        UserDao userDao = new UserDaoImpl(factory);

        List<User> users = userDao.findAll();
        for (User user : users) {
            System.out.println(user);
        }

    }

    @Test
    public void addUserTest() {
        Date date = new Date();

        User user = new User();
        user.setUsername("1");
        user.setAddress("1");
        user.setBirthday(date);
        user.setSex("1");

        UserDao userDao = new UserDaoImpl(factory);
        userDao.addUser(user);

    }

    @Test
    public void updateUserTest() {


        User user = new User();
        user.setAddress("111111111");
        user.setId(3);

        UserDao userDao = new UserDaoImpl(factory);
        userDao.updateUser(user);
    }

    @Test
    public void delUserTest() {
        User user = new User();
        user.setId(7);
        UserDao userDao = new UserDaoImpl(factory);
        userDao.delUser(user);
    }

    @Test
    public void findUserByName() {
        UserDao userDao = new UserDaoImpl(factory);
        User user = userDao.findUserByName("%三%");
        System.out.println(user);

    }

    @Test
    public void findTotalTest() {
        UserDao userDao = new UserDaoImpl(factory);
        int i = userDao.findTotal();
        System.out.println(i);
    }

}

8.配置别名

全部与大小写无关

9.自动提交

在创建SqlSession sqlSession = factory.openSession();向 openSession(true)

10.复合条件查询

/**
     * 根据给定的条件对满足条件的用户进行复合条件查询
     *
     * 查询满足姓名 张三 性别 男
     */
    List<User> findUserByCondition(User user);

    <!--根据条件 复合条件查询-->
    <select id="findUserByCondition" parameterType="user" resultType="user">
        select * from user where 
        <where>
        <if test="username != null">
            and username=#{username}
        </if>
        <if test="sex != null">
            and sex=#{sex}
        </if>
        </where>
    </select>

11.查询id为，1，3的 用户

/**
     * 查询id为，1，3的 用户
     */
    List<User> findUserByIds(QueryVo queryVo);

<!--查询id为1，3-->
    <select id="findUserByIds" parameterType="queryVo" resultType="user">
        select * from user
        <where>
            <if test="ids != null and ids.size()>0">
                <foreach collection="ids" open="and id in(" close=")" item="id" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

12.从外部连接

13.sql语句提取

14.多表查询

在MySQL中，表与表有三种关系

1. 一对一
2. 一对多
3. 多对多

在MyBatis中把多对一的关系按一对一的情况处理

多对一 和 一对多

需求：查询所有账户信息的同时，显示所属用户的信息
确定SQL语句

select a.id,a.money,u.id,u.username,u.sex,u.address from account a,user u where u.id=a.uid

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wdzl.dao.AccountDao"> <!--对应dao层接口的全限定类名-->

    <!--定义封装account 和 user的resultMap-->
    <resultMap id="accountUserMap" type="account">
        <id property="id" column="id"></id>
        <result property="uid" column="uid"></result>
        <result property="money" column="money"></result>
        <!--配置一对一关系映射，配置user的内容-->
        <association property="user" column="uid" javaType="user">
            <id property="id" column="id"></id>
            <result property="username" column="username"></result>
            <result property="birthday" column="birthday"></result>
            <result property="sex" column="sex"></result>
            <result property="address" column="address"></result>
        </association>

    </resultMap>

    <select id="findAccount" resultMap="accountUserMap">
        select a.id,a.money,u.id,u.username,u.sex,u.address from account a,user u where u.id=a.uid
    </select>
</mapper>

一对多

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wdzl.dao.UserDao"> <!--对应dao层接口的全限定类名-->

    <resultMap id="userAccountMap" type="user">
        <id property="uid" column="uid"></id>
        <result property="username" column="username"></result>
        <result property="birthday" column="birthday"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
        <collection property="accounts" ofType="account" column="id">
            <id property="aid" column="aid"></id>
            <result property="uid" column="uid"></result>
            <result property="money" column="money"></result>
        </collection>
    </resultMap>

    <!--查询用户列表-->
    <select id="findAll" resultMap="userAccountMap">
        select * from user u left outer join account a on u.uid=a.uid
    </select>
</mapper>

多对多

<mapper namespace="com.wdzl.dao.RoleDao"> <!--对应dao层接口的全限定类名-->

    <resultMap id="roleMap" type="role">
        <id property="rid" column="rid"></id>
        <result property="role_name" column="role_name"></result>
        <result property="role_desc" column="role_desc"></result>
        <collection property="users" ofType="user" column="uid">
            <id property="uid" column="uid"></id>
            <result property="username" column="username"></result>
            <result property="birthday" column="birthday"></result>
            <result property="sex" column="sex"></result>
            <result property="address" column="address"></result>
        </collection>
    </resultMap>

    <!--查询所有角色-->
    <select id="findAll" resultMap="roleMap">
        select r.*,u.* from role r left outer join user_role ur on r.rid = ur.rid left outer join user u on u.uid=ur.uid
    </select>
</mapper>

<mapper namespace="com.wdzl.dao.UserDao"> <!--对应dao层接口的全限定类名-->

    <resultMap id="userAccountMap" type="user">
        <id property="uid" column="uid"></id>
        <result property="username" column="username"></result>
        <result property="birthday" column="birthday"></result>
        <result property="sex" column="sex"></result>
        <result property="address" column="address"></result>
        <collection property="roles" ofType="role" column="uid">
            <id property="rid" column="rid"></id>
            <result property="role_name" column="role_name"></result>
            <result property="role_desc" column="role_desc"></result>
        </collection>
    </resultMap>

    <!--查询用户列表-->
    <select id="findAll" resultMap="userAccountMap">
        SELECT u.*,r.* FROM USER u LEFT OUTER JOIN user_role ur ON ur.`UID`=u.`uid` LEFT OUTER JOIN role r ON r.`rid`=ur.`RID`
    </select>
</mapper>

15.懒加载

16.缓存

一级缓存

二级缓存

二级缓存中只存储数据，不存储对象

17.注解方式

package com.wdzl.dao;

import com.wdzl.pojo.User;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

public interface UserDao {

    @Select("select * from user")
    User findAll();

    /**
     * 添加用户
     * @param user
     */
    @Insert("insert into user (username,birthday,sex,address) values (#{username},#{birthday},#{sex},#{address})")
    void addUser(User user);

    /**
     * 修改
     * @param user
     */
    @Update("update user set address=#{address} where uid=#{uid}")
    void updateUser(User user);

    @Delete("delete from user where uid=#{uid}")
    void delUser(int id);

    /**
     * 根据id查
     * @param uid
     * @return
     */
    @Select("select * from user where uid=#{uid}")
    User findUserById(Integer uid);

    /**
     * 模糊查询
     * @param name
     * @return
     */
    @Select("select * from user where username like #{username}")
    List<User> findUserByName(String name);

    /**
     * 查询总记录数
     * @return
     */
    @Select("select count(*) from user")
    int findTotal();
}

18.当表名与属性名不相同时

引用

19.多表查询