快速学习Mybatis之CRUD

Mybatis02-Mybatis之CRUD

上一篇博客，我们已经将Mybatis环境搭好了，下面，我们就开始用Mybatis框架对数据库进行增删改查！我们需要在对应的配置文件中添加配置

Mybatis框架对数据库进行增删改查有三种方式：

第一种方式：使用加载xml的方式去实现CRUD

第二种方式：基于接口代理实现CRUD

第三种方式：基于注解实现CRUD

1.Select标签（采用第一种方式查询）

1.mybatis.xml配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!-- mybatis 框架头文件声明，类似 spring 环境，均需要加入头文件 -->
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- 添加数据库连接相关配置信息 -->
<configuration>

    <!--引入外部配置文件-->
    <properties resource="jdbc.properties"/>

    <settings>
        <!--
            数据库字段(带有_字符的字段) 自动转换为驼峰命名
        -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--别名-->
    <typeAliases>
        <!--配置1：指定具体类取别名-->
        <!--<typeAlias type="com.mage.vo.User" alias="user"/>
        <typeAlias type="com.mage.query.UserQuery" alias="userquery"/>-->
        <!--配置2：指定某个包路径下的所有类取别名,别名默认是类名（推荐使用）-->
        <package name="com.mage.vo"/>
        <package name="com.mage.query"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <!-- 加入事务控制 -->
            <transactionManager type="jdbc" />
            <!-- 配置数据库连接信息 -->
            <dataSource type="pooled">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.user}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!-- mapper 配置文件指定 文件数量可配置多个-->
    <mappers>
        <!--resource:包路径-->
        <mapper resource="com/mage/mappers/UserMapper.xml" />
        <!--映射接口列表配置形式注解sql-->
        <!--<mapper class="com.mage.dao.AccountDao"/>-->
        <!--映射包下所有接口-->
        <!--<package name="com.mage.dao"/>-->

        <!--<mapper class="com.sxt.dao.IAccountDao"/>-->
    </mappers>
</configuration>

2.实体类（参数封装到javaBean中）

public class UserQuery {
    private String userName;
    private String userPwd;

3.工具类，将相同代码进行简单封装，方便测试

public class Factory {
    public static SqlSessionFactory getsqlSessionFactory(String fileName){
        /*
         * 1.加载全局配置文件 构建sqlSessionFactory
         * 2.获取会话sqlSession
         * 3.调用方法执行查询
         * 4.关闭查询*/
        try {
            if (fileName != null) {
                //1.加载全局配置文件 构建sqlSessionFactory
                InputStream inputStream = Resources.getResourceAsStream(fileName);
                SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
                return sqlSessionFactory;
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
}

4.dao层接口UserDao

public interface UserDao {
    //根据用户id查询，返回用户信息
    public User queryUserById(Integer userId);
    //根据用户名查询，返回用户信息
    public User queryUserByUserName(String userName);
    //根据javaBean对象查询，返回用户信息
    public User queryUserByUserNameAndUserPwd(UserQuery userQuery);
    //根据用户名和用户密码查询，返回用户信息
    public User queryUserByUserNameAndUserPwdMap(String userName, String userPwd);
    //根据传入的ids,更新对应的用户密码
    public Integer updateUserPwdByIds(Integer[] ids);
    //统计用户表总记录
    public Integer countUser();
    //根据用户对应的id，查询用户的创建时间
    public Date queryUserCreateTimeByUserId(Integer userId);
    //根据参数输入的用户名，进行模糊查询，返回包含用户名的用户信息
    public List<User> queryUsersByUserNameLike(String userName);
    //根据javaBean对象，返回用Map集合包装的用户信息
    public Map queryUserNameAndUserPwdMap(UserQuery userQuery);
    //根据javaBean对象，返回用集合包装的用户信息
    public List<Map<String,Object>> queryUserByUserNameLikeMap(String userName);
    //根据用户id查询，返回用户信息,为了使用resultMap
    public User queryUserById(Integer userId);

5.dao层接口UserDao实现类

package com.mage.dao.impl;

import com.mage.dao.UserDao;
import com.mage.query.UserQuery;
import com.mage.utils.Factory;
import com.mage.vo.User;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import java.util.*;

/**
 * @author qzp
 * @create 2020-02-16 10:52
 */
public class UserDaoImpl implements UserDao {
    /**
     * @Description: 根据用户id查询，返回用户信息
     * @Param: [userId]
     * @return: com.mage.vo.User
     * @Author: qzp
     * @Date: 2020/5/2
     */
    @Override
    public User queryUserById(Integer userId) {
        SqlSession sqlSession = null;
        User user = null;
        try {
            //获取SqlSessionFactory对象
            SqlSessionFactory sqlSessionFactory = Factory.getsqlSessionFactory("mybatis.xml");
            if(sqlSessionFactory != null){
                //创建会话
                sqlSession = sqlSessionFactory.openSession();
                //查询单条记录
                user = sqlSession.selectOne("com.mage.mappers.UserMapper.queryUserById", userId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(sqlSession != null){
                sqlSession.close();
            }
        }
        return user;
    }
    /**
     * @Description: 根据用户名查询，返回用户信息
     * @Param: [userName]
     * @return: com.mage.vo.User
     * @Author: qzp
     * @Date: 2020/5/2
     */
    @Override
    public User queryUserByUserName(String userName) {
        SqlSession sqlSession = null;
        User user = null;
        try {
            //获取SqlSessionFactory对象
            SqlSessionFactory sqlSessionFactory = Factory.getsqlSessionFactory("mybatis.xml");
            if(sqlSessionFactory != null){
                //创建会话
                sqlSession = sqlSessionFactory.openSession();
                //查询单条记录
                user = sqlSession.selectOne("com.mage.mappers.UserMapper.queryUserByUserName", userName);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(sqlSession != null){
                sqlSession.close();
            }
        }
        return user;
    }
    /**
     * @Description: 根据javaBean对象查询，返回用户信息
     * @Param: [userQuery]
     * @return: com.mage.vo.User
     * @Author: qzp
     * @Date: 2020/5/2
     */
    @Override
    public User queryUserByUserNameAndUserPwd(UserQuery userQuery) {
        SqlSession sqlSession = null;
        User user = null;
        try {
            //获取SqlSessionFactory对象
            SqlSessionFactory sqlSessionFactory = Factory.getsqlSessionFactory("mybatis.xml");
            if(sqlSessionFactory != null){
                //创建会话
                sqlSession = sqlSessionFactory.openSession();
                //查询单条记录
                user = sqlSession.selectOne("com.mage.mappers.UserMapper.queryUserByUserNameAndUserPwd",userQuery);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(sqlSession != null){
                sqlSession.close();
            }
        }
        return user;
    }
    /**
     * @Description: 根据用户名和用户密码查询，返回用户信息
     * @Param: [userName, userPwd]
     * @return: com.mage.vo.User
     * @Author: qzp
     * @Date: 2020/5/2
     */
    @Override
    public User queryUserByUserNameAndUserPwdMap(String userName, String userPwd) {
        SqlSession sqlSession = null;
        User user = null;
        try {
            //获取SqlSessionFactory对象
            SqlSessionFactory sqlSessionFactory = Factory.getsqlSessionFactory("mybatis.xml");
            if(sqlSessionFactory != null){
                //创建会话
                sqlSession = sqlSessionFactory.openSession();
                //用Map代替数据库
                Map<String,Object> params = new HashMap<>();
                params.put("userName",userName);
                params.put("userPwd",userPwd);
                //查询单条记录
                user = sqlSession.selectOne("com.mage.mappers.UserMapper.queryUserByUserNameAndUserPwdMap",params);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(sqlSession != null){
                sqlSession.close();
            }
        }
        return user;
    }
    /**
     * @Description:  根据传入的ids,更新对应的用户密码
     * @Param: [ids]
     * @return: java.lang.Integer
     * @Author: qzp
     * @Date: 2020/5/2
     */
    @Override
    public Integer updateUserPwdByIds(Integer[] ids) {
        SqlSession sqlSession = null;
        int total = 0;
        try {
            //获取SqlSessionFactory对象
            SqlSessionFactory sqlSessionFactory = Factory.getsqlSessionFactory("mybatis.xml");
            if(sqlSessionFactory != null){
                //创建会话 注意：默认获取的SqlSession不会自动提交事务，需要显示指定事务自动提交
                sqlSession = sqlSessionFactory.openSession(true);
                //更新多条记录的密码
                total = sqlSession.update("com.mage.mappers.UserMapper.updateUserPwdByIds", ids);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(sqlSession != null){
                sqlSession.close();
            }
        }
        return total;
    }
    /**
     * @Description:统计用户表总记录  
     * @Param: []
     * @return: java.lang.Integer
     * @Author: qzp
     * @Date: 2020/5/2
     */
    @Override
    public Integer countUser() {
        SqlSession sqlSession = null;
        int total = 0;
        try {
            //获取SqlSessionFactory对象
            SqlSessionFactory sqlSessionFactory = Factory.getsqlSessionFactory("mybatis.xml");
            if(sqlSessionFactory != null){
                //创建会话
                sqlSession = sqlSessionFactory.openSession();
                //查询单条记录
                total = sqlSession.selectOne("com.mage.mappers.UserMapper.countUser");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(sqlSession != null){
                sqlSession.close();
            }
        }
        return total;
    }
    /**
     * @Description: 根据用户对应的id，查询用户的创建时间 
     * @Param: [userId]
     * @return: java.util.Date
     * @Author: qzp
     * @Date: 2020/5/2
     */
    @Override
    public Date queryUserCreateTimeByUserId(Integer userId) {
        SqlSession sqlSession = null;
        Date date = null;
        try {
            //获取SqlSessionFactory对象
            SqlSessionFactory sqlSessionFactory = Factory.getsqlSessionFactory("mybatis.xml");
            if(sqlSessionFactory != null){
                //创建会话
                sqlSession = sqlSessionFactory.openSession();
                //查询单条记录
                date = sqlSession.selectOne("com.mage.mappers.UserMapper.queryUserCreateTimeByUserId",userId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(sqlSession != null){
                sqlSession.close();
            }
        }
        return date;
    }
    /**
     * @Description: 根据参数输入的用户名，进行模糊查询，返回包含用户名的用户信息 
     * @Param: [userName]
     * @return: java.util.List<com.mage.vo.User>
     * @Author: qzp
     * @Date: 2020/5/2
     */
    @Override
    public List<User> queryUsersByUserNameLike(String userName) {
        SqlSession sqlSession = null;
        List<User> users = new ArrayList<>();
        try {
            //获取SqlSessionFactory对象
            SqlSessionFactory sqlSessionFactory = Factory.getsqlSessionFactory("mybatis.xml");
            if(sqlSessionFactory != null){
                //创建会话
                sqlSession = sqlSessionFactory.openSession();
                //查询单条记录
                users = sqlSession.selectList("com.mage.mappers.UserMapper.queryUsersByUserNameLike",userName);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(sqlSession != null){
                sqlSession.close();
            }
        }
        return users;
    }
    /**
     * @Description: 根据javaBean对象，返回用Map集合包装的用户信息 
     * @Param: [userQuery]
     * @return: java.util.Map
     * @Author: qzp
     * @Date: 2020/5/2
     */
    @Override
    public Map queryUserNameAndUserPwdMap(UserQuery userQuery) {
        SqlSession sqlSession = null;
        Map<String,Object> user = new HashMap<>();
        try {
            //获取SqlSessionFactory对象
            SqlSessionFactory sqlSessionFactory = Factory.getsqlSessionFactory("mybatis.xml");
            if(sqlSessionFactory != null){
                //创建会话
                sqlSession = sqlSessionFactory.openSession();
                //查询单条记录
                user= sqlSession.selectOne("com.mage.mappers.UserMapper.queryUserNameAndUserPwdMap",userQuery);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(sqlSession != null){
                sqlSession.close();
            }
        }
        return user;
    }
    /**
     * @Description: 根据javaBean对象，返回用集合包装的用户信息
     * @Param: [userName]
     * @return: java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
     * @Author: qzp
     * @Date: 2020/5/2
     */
    @Override
    public List<Map<String, Object>> queryUserByUserNameLikeMap(String userName) {
        SqlSession sqlSession = null;
        List<Map<String,Object>> results = new ArrayList<>();
        try {
            //获取SqlSessionFactory对象
            SqlSessionFactory sqlSessionFactory = Factory.getsqlSessionFactory("mybatis.xml");
            if(sqlSessionFactory != null){
                //创建会话
                sqlSession = sqlSessionFactory.openSession();
                //查询单条记录
                results = sqlSession.selectList("com.mage.mappers.UserMapper.queryUserByUserNameLikeMap", userName);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(sqlSession != null){
                sqlSession.close();
            }
        }
        return results;
    }
    /**
     * @Description: 根据用户id查询，返回用户信息,结果集采用resultMap
     * @Param: [userId]
     * @return: com.mage.vo.User
     * @Author: qzp
     * @Date: 2020/5/2
     */
    @Override
    public User queryUserById02(Integer userId) {
        SqlSession sqlSession = null;
        User user = null;
        try {
            //获取SqlSessionFactory对象
            SqlSessionFactory sqlSessionFactory = Factory.getsqlSessionFactory("mybatis.xml");
            if(sqlSessionFactory != null){
                //创建会话
                sqlSession = sqlSessionFactory.openSession();
                //查询单条记录
                user = sqlSession.selectOne("com.mage.mappers.UserMapper.queryUserById", userId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(sqlSession != null){
                sqlSession.close();
            }
        }
        return user;
    }
}

6.sql映射文件中的sql语句

<?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">
        <!--
        1.命名空间配置 namespace: 值全局唯一 包名+文件名（不包含后缀）
        2.配置 Mapped Statement
        3.查询标签：select 又称为statement statement 配置
        id 值声明 statement 唯一标识，同一命名空间(同一文件)下不可重复
        parameterType 输入参数即占位符的数据类型 类型可以是 基本数据类型,字
        符串,java bean ,map,list,数组 等
        resultType 输出结果 类型可以是基本数据类型,字符串,java bean,map
        等
        标签体：statement 描述 即待执行的 sql
        #{id} 占位符 变量名为 id/value 均可 ${value} 变量名必须为 value
        字符串拼接形式 无法避免 sql 注入
        -->
<mapper namespace="com.mage.mappers.UserMapper">
    <!--使用sql标签，将sql语句中频繁使用的字段统一管理和使用，直接通过id属性来调用-->
    <sql id="user_columns">
       id,user_name as userName,user_pwd as userPwd,flag,create_time as createTime
    </sql>
    
    <!--
        根据用户id查询，返回用户信息
    -->
    <select id="queryUserById" parameterType="int" resultType="User">
        select id,user_name as userName,user_pwd as userPwd,flag,create_time as createTime from user where id=#{id}
    </select>
    
    <!--
        根据用户名查询，返回用户信息
    -->
    <select id="queryUserByUserName" parameterType="string" resultType="User">
        select <include refid="user_columns"/> from user where user_name=#{userName}
    </select>
    <!--
        根据javaBean查询，返回用户信息
    -->
    <!--
               userName,userPwd  为 UserQuery 类成员变量名
           -->
    <select id="queryUserByUserNameAndUserPwd" parameterType="UserQuery" resultType="User">
        select <include refid="user_columns"/> from user where user_name=#{userName} and user_pwd = #{userPwd}
    </select>
    
    <!--
        根据用户名和用户密码查询，返回用户信息
    -->
    <!--
               user_name 对应map中key 的名称
               user_pwd  对应map中key 的名称
    -->
    <select id="queryUserByUserNameAndUserPwdMap" parameterType="map" resultType="User">
        select <include refid="user_columns"/> from user where user_name=#{userName} and user_pwd=#{userPwd}
    </select>
    
    <!--
		根据传入的ids,更新对应的用户密码
        更新,添加，删除默认返回受影响行数，没有resultType属性，数组可以省略parameterType
    -->
    <update id="updateUserPwdByIds">
        update user set user_pwd="123456" where id in
        <foreach collection="array" item="item" open="(" separator="," close=")">
        #{item}
        </foreach>
    </update>


    <!--
      		统计用户表总记录
    -->
    <select id="countUser" resultType="int">
        select count(1) from user
    </select>

     <!--
      		根据用户对应的id，查询用户的创建时间
      -->
    <select id="queryUserCreateTimeByUserId" parameterType="int" resultType="date">
        select create_time as createTime from user where id=#{userId}
    </select>
    
    <!--
      		根据参数输入的模糊用户名，进行模糊查询，返回包含用户名的用户信息
    -->
    <!--返回值类型是list ，返回值不写list，要写的是list集合中可以包含的类型-->
    <select id="queryUsersByUserNameLike" parameterType="string" resultType="User">
        select <include refid="user_columns"/> from user where user_name like concat('%',#{userName},'%')
    </select>

      <!--
      		根据javaBean对象，返回用Map集合包装的用户信息
      -->
    <select id="queryUserNameAndUserPwdMap" parameterType="UserQuery" resultType="map">
        select <include refid="user_columns"/> from user where user_name=#{userName} and user_pwd = #{userPwd}
    </select>
	<!--
      		根据参数输入的用户名，进行查询，返回包含用户名的用户信息
     -->
    <select id="queryUserByUserNameLikeMap" parameterType="string" resultType="map">
        select <include refid="user_columns"/> from user where user_name like concat('%',#{userName},'%')
    </select>
    
    <!--
        根据用户id查询，返回用户信息，为了测试sql映射中的resultMap
    -->
    <!--
       当前文件 id 唯一且不可重复
   	-->
    <resultMap id="user_map" type="User">
        <!--
           column:返回的列名
           property:User 对象成员变量
        -->
        <result column="id" property="id"></result>
        <result column="user_name" property="userName"></result>
        <result column="user_pwd" property="userPwd"></result>
        <result column="flag" property="flag"></result>
        <result column="create_time" property="createTime"></result>
    </resultMap>
    <select id="queryUserById02" parameterType="int" resultMap="user_map">
        select id, user_name, user_pwd, flag,create_time
        from user
        where id=#{userId}
    </select>
</mapper>

7.测试:

public class TestMybatis {
    private UserDao userDao = new UserDaoImpl();
    
    //之前正常测试，我们需要写如下代码
    @Test
    public void test01() throws IOException {
        /*
        * 1.加载全局配置文件 构建sqlSessionFactory
        * 2.获取会话sqlSession
        * 3.调用方法执行查询
        * 4.关闭查询
        * */
        InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = sqlSession.selectOne("com.mage.mappers.UserMapper.queryUserById", 83);
        System.out.println(user);
        sqlSession.close();
    }
}

优化测试代码，将所有的创建和关闭都交给程序，我们只调用方法，查看结果

public class TestMybatis {
    private UserDao userDao = new UserDaoImpl();
    //根据用户id查询，返回用户信息
    @Test
    public void test02(){
        System.out.println(userDao.queryUserById(83));
    }
    //根据用户名查询，返回用户信息
    @Test
    public void test03(){
        System.out.println(userDao.queryUserByUserName("admin"));
    }
    //根据javaBean对象查询，返回用户信息
    @Test
    public void test04(){
        UserQuery userQuery = new UserQuery("admin","111111");
        System.out.println(userDao.queryUserByUserNameAndUserPwd(userQuery));
    }
    //根据用户名和用户密码查询，返回用户信息
    @Test
    public void test05(){
        System.out.println(userDao.queryUserByUserNameAndUserPwdMap("admin","111111"));
    }
    //根据传入的ids,更新对应的用户密码
    @Test
    public void test06(){
        System.out.println(userDao.updateUserPwdByIds(new Integer[]{82,83,84,85}));
    }
    //统计用户表总记录
    @Test
    public void test07(){
        System.out.println(userDao.countUser());
    }
    //根据用户对应的id，查询用户的创建时间
    @Test
    public void test09(){
        System.out.println(new SimpleDateFormat("yyyy-MM-dd").format(userDao.queryUserCreateTimeByUserId(75)));
    }
    //根据参数输入的用户名，进行模糊查询，返回包含用户名的用户信息
    @Test
    public void test10(){
        List<User> users = userDao.queryUsersByUserNameLike("test");
        /*方式一*/
        for (int i = 0; i < users.size(); i++) {
            System.out.println(users.get(i));
        }
        System.out.println("===========");
        /*方式二*/
        for (User user : users) {
            System.out.println(user);
        }
        System.out.println("===========");
        /*方式三*/
        users.forEach(new Consumer<User>() {
            @Override
            public void accept(User user) {
                System.out.println(user);
            }
        });
        System.out.println("===========");
        /*方式四*/
        users.forEach(user -> {
            System.out.println(user);
        });
    }
    //根据javaBean对象，返回用Map集合包装的用户信息
    @Test
    public void test11(){
        UserQuery userQuery = new UserQuery("admin","111111");
        Map<String,Object> result = userDao.queryUserNameAndUserPwdMap(userQuery);
        /*方式一*/
        result.forEach(new BiConsumer<String, Object>() {
            @Override
            public void accept(String s, Object o) {
                System.out.println("key:"+s+"--"+"value:"+o);
            }
        });
        System.out.println("===========");
        /*方式二*/
        Set<Map.Entry<String,Object>> set = result.entrySet();
        for (Map.Entry<String, Object> entry : set) {
            System.out.println(entry.getKey()+"--"+entry.getValue());
        }
        System.out.println("===========");
        /*方式三*/
        Set<String> set1 = result.keySet();
        for (String key : set1) {
            System.out.println("key:"+key+"--"+"value:"+result.get(key));
        }
        System.out.println("===========");
        /*方式四*/
        result.forEach((k,v)->{
            System.out.println("key:"+k+"--"+"value:"+v);
        });
    }
    //根据javaBean对象，返回用集合包装的用户信息
    @Test
    public void test12(){
        List<Map<String, Object>> test = userDao.queryUserByUserNameLikeMap("test");
        /*方式一*/
        for (Map<String, Object> map : test) {
            for (Map.Entry<String,Object> entry:map.entrySet()) {
                System.out.println(entry.getKey()+":"+entry.getValue());
            }
            System.out.println("==============================");
        }
        /*方式二*/
        test.forEach(m->{
            m.forEach((k,v)->{
                System.out.println(k+":"+v);
            });
            System.out.println("=================================");
        });
    }
    //根据用户id查询，返回用户信息，为了测试sql映射中的resultMap
    @Test
    public void test13(){
        System.out.println(userDao.queryUserById02(75));
    }
}

基于接口代理实现CRUD规范

​ 1.sql映射文件namespace值 为接口的全限定名(全路径) 包名.接口名

​ 2.Insert 、Select、Update、Delete 标签Id 值 与接口方法名一致

​ 3.sql 映射文件输入参数类型与接口方法参数类型一致

​ 4.sql 映射文件输出结果类型与接口方法返回值类型一致

单框架环境下注意事项

​ 1.sql 映射文件与接口文件在同一个包中

​ 2.sql 映射文件文件名与接口名一致

2.insert标签(添加) / Update(更新) / Delete (删除)

1.mybatis.xml配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!-- mybatis 框架头文件声明，类似 spring 环境，均需要加入头文件 -->
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- 添加数据库连接相关配置信息 -->
<configuration>

    <!--引入外部配置文件-->
    <properties resource="jdbc.properties"/>

    <settings>
        <!--
            数据库字段(带有_字符的字段) 自动转换为驼峰命名
        -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--别名-->
    <typeAliases>
        <!--配置1：指定具体类取别名-->
        <!--<typeAlias type="com.mage.vo.User" alias="user"/>
        <typeAlias type="com.mage.query.UserQuery" alias="userquery"/>-->
        <!--配置2：指定某个包路径下的所有类取别名,别名默认是类名（推荐使用）-->
        <package name="com.mage.vo"/>
        <package name="com.mage.query"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <!-- 加入事务控制 -->
            <transactionManager type="jdbc" />
            <!-- 配置数据库连接信息 -->
            <dataSource type="pooled">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.user}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!-- mapper 配置文件指定 文件数量可配置多个-->
    <mappers>
        <!--resource:包路径-->
         <!--<mapper resource="com/mage/mappers/UserMapper.xml" />-->
        <!--映射接口列表配置形式注解sql-->
        <!--<mapper class="com.mage.dao.AccountDao"/>-->
        <!--映射包下所有接口-->
        <package name="com.sxt.dao"/>

        <!--<mapper class="com.sxt.dao.IAccountDao"/>-->
    </mappers>
</configuration>

2.实体类（参数封装到javaBean中，对应数据库表字段）

public class Account {
    private Integer id;
    private String aname;
    private String type;
    private BigDecimal money;
    private Date createTime;
    private Date updateTime;
    private String remark;
    private  Integer userId;

3.工具类，将相同代码进行简单封装，方便测试

public class MybatisUtils {
    public static SqlSessionFactory getsqlSessionFactory(String fileName){
        /*
         * 1.加载全局配置文件 构建sqlSessionFactory
         * 2.获取会话sqlSession
         * 3.调用方法执行查询
         * 4.关闭查询*/
        try {
            if (fileName != null) {
                //1.加载全局配置文件 构建sqlSessionFactory
                InputStream inputStream = Resources.getResourceAsStream(fileName);
                SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
                return sqlSessionFactory;
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
    
    public static SqlSession getOpenSession(SqlSessionFactory sqlSessionFactory){
        /*事务需要手动提交*/
         return sqlSessionFactory.openSession(true);
    }
}

4.dao层接口UserDao

public interface IAccountDao {
    //根据id查询，返回对应的表记录
    public Account quesyAccountById(Integer id);
    
    //添加单条记录，返回受影响行数
    public int saveAccount(Account account);
    
    //添加单条记录，返回受影响行数的主键，对应获取主键的sql实现方式01
    public Integer insertAccountHasPrimaryKey(Account account);
    //添加单条记录，返回受影响行数的主键，对应的获取主键的sql实现方式02
    public Integer insertAccountHasPrimaryKey02(Account account);

    //批量添加多条记录，返回受影响行数
    public Integer saveAccountBatch(List<Account> accounts); 
    
    //根据单条用户记录，更新用户记录，返回受影响行数
    public Integer updateAccount(Account account);
    
    //根据多条用户记录，更新多条用户记录，返回受影响行数
    public Integer updateAccountBatch(List<Account> accounts);
    
    //根据用户记录对应的id，删除对应的记录
    public Integer deleteAccountById(Integer id);
    
    //根据用户记录对应的id，删除对应的记录
    public Integer deleterAccountByIds(Map<String,Object> map);

5.sql映射文件中的sql语句

<!--
	根据id查询，返回对应的表记录
-->
<select id="quesyAccountById" parameterType="integer" resultType="com.sxt.vo.Account">
    select * from account where id =#{id};
</select>

<!--
	添加单条记录，返回受影响行数
-->
<insert id="saveAccount" parameterType="com.sxt.vo.Account">
    insert into account(aname, type, money, user_id, create_time, update_time, remark) VALUES(#{aname},#{type},#{money},#{userId},#{createTime},#{updateTime},#{remark})
</insert>
<!--
         添加单条记录返回主键方式01
            useGeneratedKeys:通知框架 执行添加时获取记录的主键
            keyProperty:设置接收主键的成员变量名
-->
<!--useGeneratedKeys="true":指确认返回主键，keyProperty="id":中的id是Account类接受返回值的具体属性-->
<insert id="insertAccountHasPrimaryKey" parameterType="com.sxt.vo.Account" useGeneratedKeys="true" keyProperty="id">
    insert into account(aname, type, money, user_id, create_time, update_time, remark) VALUES
    (#{aname},#{type},#{money},#{userId},#{createTime},#{updateTime},#{remark})
</insert>
<!--
         添加单条记录返回主键方式02
		order：声明添加的sql在执行前或者执行后获取主键
		keyProperty:设置接收主键的成员变量名
		resultType:返回主键的类型
 -->
<insert id="insertAccountHasPrimaryKey02" parameterType="com.sxt.vo.Account">
    <selectKey order="AFTER" resultType="int" keyProperty="id">
        select last_insert_id()
    </selectKey>
    insert into account(aname, type, money, user_id, create_time, update_time, remark) VALUES
    (#{aname},#{type},#{money},#{userId},#{createTime},#{updateTime},#{remark})
</insert>

<!--
	批量添加多条记录，返回受影响行数
-->
<!--注意sql语句,需要使用item.属性-->
<insert id="saveAccountBatch">
    insert into account(aname, type, money, user_id, create_time, update_time, remark) VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.aname},#{item.type},#{item.money},#{item.userId},#{item.createTime},#{item.updateTime},#{item.remark})
    </foreach>
</insert>


<!--
	根据单条用户记录，更新用户记录，返回受影响行数
-->
<update id="updateAccount" parameterType="com.sxt.vo.Account">
    update account set aname=#{aname},type=#{type},money=#{money},user_id=#{userId},create_time=#{createTime},update_time=#{updateTime},remark=#{remark} where id=#{id}
</update>


<!--
	根据多条用户记录，更新多条用户记录，返回受影响行数
-->
<update id="updateAccountBatch">
    <foreach collection="list" item="item" separator=";">
        update account set aname=#{item.aname},type=#{item.type},money=#{item.money},user_id=#{item.userId},create_time=#{item.createTime},update_time=#{item.updateTime},remark=#{item.remark} where id=#{item.id}
    </foreach>
</update>


<!--
	根据用户记录对应的id，删除对应的记录
-->
<delete id="deleteAccountById" parameterType="integer">
    delete from account where id =#{id}
</delete>

<!--
	根据多条用户记录对应的id，删除对应的记录
-->
<delete id="deleterAccountByIds" parameterType="map">
    delete  from  account where id in
    <foreach collection="ids" item="item" open="(" close=")" separator=",">
        #{item}
    </foreach>
</delete>

6.测试

public class TestIAccountDao {
    
    //添加单条记录，返回受影响行数
    @Test
    public void test01() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        /*事务需要手动提交*/
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        /**
         *  获取接口的代理对象  运行期动态为AccountDao 创建代理对象
         */
        IAccountDao iAccountDaoProxy = sqlSession.getMapper(IAccountDao.class);
        Account account = new Account("第一桶金","3", BigDecimal.valueOf(20000L),new Date(),new Date(),"工资",1);
        System.out.println(iAccountDaoProxy.saveAccount(account));
        sqlSession.close();
    }
    
    //根据id查询，返回对应的表记录
    @Test
    public void test02() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        IAccountDao iAccountDaoProxy = sqlSession.getMapper(IAccountDao.class);
        Account account = iAccountDaoProxy.quesyAccountById(167);
        System.out.println(account);
        sqlSession.close();
    }
    
    //添加单条记录，返回受影响行数的主键，对应获取主键的sql实现方式01
    @Test
    public void test03() throws IOException {
         SqlSessionFactory sqlSessionFactory = MybatisUtils.getsqlSessionFactory("mybatis.xml");
        /*调用方法，执行事务手动提交*/
        SqlSession sqlSession = MybatisUtils.getOpenSession(sqlSessionFactory);
        /**
         *  获取接口的代理对象  运行期动态为AccountDao 创建代理对象
         */
        IAccountDao iAccountDaoProxy = sqlSession.getMapper(IAccountDao.class);
        Account account = new Account("第二桶金","3", BigDecimal.valueOf(20000L),new Date(),new Date(),"工资",1);
        Integer integer = iAccountDaoProxy.insertAccountHasPrimaryKey(account);
        Integer id = account.getId();
        System.out.println(integer+":"+id);
        sqlSession.close();
    }
    
    //添加单条记录，返回受影响行数的主键，对应获取主键的sql实现方式01
    @Test
    public void test04() throws IOException {
         SqlSessionFactory sqlSessionFactory = MybatisUtils.getsqlSessionFactory("mybatis.xml");
        /*调用方法，执行事务手动提交*/
        SqlSession sqlSession = MybatisUtils.getOpenSession(sqlSessionFactory);
        /**
         *  获取接口的代理对象  运行期动态为AccountDao 创建代理对象
         */
        IAccountDao iAccountDaoProxy = sqlSession.getMapper(IAccountDao.class);
        Account account = new Account("第三桶金","3", BigDecimal.valueOf(20000L),new Date(),new Date(),"工资",1);
        Integer integer = iAccountDaoProxy.insertAccountHasPrimaryKey02(account);
        Integer id = account.getId();
        System.out.println(integer+":"+id);
        sqlSession.close();
    }
    
    //批量添加多条记录，返回受影响行数
    @Test
    public void test05() throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        IAccountDao iAccountDaoProxy = sqlSession.getMapper(IAccountDao.class);
        List<Account> accounts = new ArrayList<>();
        for (int i = 1; i <8 ; i++) {
            Account account = new Account("桶金"+i,"3", BigDecimal.valueOf(200000L),new Date(),new Date(),"年资",1);
            accounts.add(account);
        }
        /*List<Account> accounts = Arrays.asList(
                new Account("第四桶金","3", BigDecimal.valueOf(20000L),new Date(),new Date(),"工资",60),
                new Account("第五桶金","3", BigDecimal.valueOf(20000L),new Date(),new Date(),"工资",70),
                new Account("第六桶金","3", BigDecimal.valueOf(20000L),new Date(),new Date(),"工资",50));*/
        Integer integer = iAccountDaoProxy.saveAccountBatch(accounts);

        sqlSession.close();
    }
    
    //根据单条用户记录，更新用户记录，返回受影响行数
    @Test
    public void test06() throws IOException {
         SqlSessionFactory sqlSessionFactory = MybatisUtils.getsqlSessionFactory("mybatis.xml");
        /*调用方法，执行事务手动提交*/
        SqlSession sqlSession = MybatisUtils.getOpenSession(sqlSessionFactory);
        /**
         *  获取接口的代理对象  运行期动态为AccountDao 创建代理对象
         */
        IAccountDao iAccountDaoProxy = sqlSession.getMapper(IAccountDao.class);
        Account account = iAccountDaoProxy.quesyAccountById(203);
        account.setType("0");
        Integer integer = iAccountDaoProxy.updateAccount(account);
        System.out.println(integer);
        sqlSession.close();
    }
    
    //根据多条用户记录，更新多条用户记录，返回受影响行数
    @Test
    public void test07() throws IOException {
         SqlSessionFactory sqlSessionFactory = MybatisUtils.getsqlSessionFactory("mybatis.xml");
        /*调用方法，执行事务手动提交*/
        SqlSession sqlSession = MybatisUtils.getOpenSession(sqlSessionFactory);
        /**
         *  获取接口的代理对象  运行期动态为AccountDao 创建代理对象
         */
        IAccountDao iAccountDaoProxy = sqlSession.getMapper(IAccountDao.class);
        Account account = iAccountDaoProxy.quesyAccountById(200);
        account.setType("1");
        Account account1 = iAccountDaoProxy.quesyAccountById(201);
        account1.setType("1");
        Account account2 = iAccountDaoProxy.quesyAccountById(202);
        account2.setType("1");
        List<Account> accounts = Arrays.asList(account,account1,account2);
        System.out.println(iAccountDaoProxy.updateAccountBatch(accounts));
    }
    
    //根据用户记录对应的id，删除对应的记录
    @Test
    public void test08() throws IOException {
         SqlSessionFactory sqlSessionFactory = MybatisUtils.getsqlSessionFactory("mybatis.xml");
        /*调用方法，执行事务手动提交*/
        SqlSession sqlSession = MybatisUtils.getOpenSession(sqlSessionFactory);
        /**
         *  获取接口的代理对象  运行期动态为AccountDao 创建代理对象
         */
        IAccountDao iAccountDaoProxy = sqlSession.getMapper(IAccountDao.class);
        System.out.println(iAccountDaoProxy.deleteAccountById(179));
        sqlSession.close();
    }
    
    //根据多条用户记录对应的id，删除对应的记录
    @Test
    public void test09() throws IOException {
        SqlSessionFactory sqlSessionFactory = MybatisUtils.getsqlSessionFactory("mybatis.xml");
        /*调用方法，执行事务手动提交*/
        SqlSession sqlSession = MybatisUtils.getOpenSession(sqlSessionFactory);
        /**
         *  获取接口的代理对象  运行期动态为AccountDao 创建代理对象
         */
        IAccountDao iAccountDaoProxy = sqlSession.getMapper(IAccountDao.class);
        Map<String,Object> map = new HashMap<>();
        map.put("ids",new Integer[]{197,198,199,200,201,202,203});
        System.out.println(iAccountDaoProxy.deleterAccountByIds(map));
        sqlSession.close();
    }
}

3.注解实现CRUD

1.mybatis.xml配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!-- mybatis 框架头文件声明，类似 spring 环境，均需要加入头文件 -->
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- 添加数据库连接相关配置信息 -->
<configuration>

    <!--引入外部配置文件-->
    <properties resource="jdbc.properties"/>

    <settings>
        <!--
            数据库字段(带有_字符的字段) 自动转换为驼峰命名
        -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--别名-->
    <typeAliases>
        <!--配置1：指定具体类取别名-->
        <!--<typeAlias type="com.mage.vo.User" alias="user"/>
        <typeAlias type="com.mage.query.UserQuery" alias="userquery"/>-->
        <!--配置2：指定某个包路径下的所有类取别名,别名默认是类名（推荐使用）-->
        <package name="com.mage.vo"/>
        <package name="com.mage.query"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <!-- 加入事务控制 -->
            <transactionManager type="jdbc" />
            <!-- 配置数据库连接信息 -->
            <dataSource type="pooled">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.user}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <!-- mapper 配置文件指定 文件数量可配置多个-->
    <mappers>
        <!--resource:包路径-->
         <!--<mapper resource="com/mage/mappers/UserMapper.xml" />-->
        <!--映射接口列表配置形式注解sql-->
        <mapper class="com.mage.dao.AccountDao"/>

        <!--<mapper class="com.sxt.dao.IAccountDao"/>-->
    </mappers>
</configuration>

2.接口方法

public interface AccountDao {
    @Select("select id,aname,user_id as userId,money,remark,create_time as createTime,update_time as updateTime from  account where id=#{id} ")
    public Account queryAccountById(Integer id);
}

3.测试

  @Test
    public void test1() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session =factory.openSession();
        /**
         *  获取接口的代理对象  运行期动态为AccountDao 创建代理对象
         */
        AccountDao accountDaoProxy = session.getMapper(AccountDao.class);
        System.out.println(accountDaoProxy.queryAccountById(150));
        session.close();
    }