Mybatis学习

Day01Mybatis笔记

一、Mybatis框架概念和特点

1.Mybatis特点

1）属于持久层框架 持久化（将内存中的对象数据转移到数据库的过程称为持久化）

​ 持久化框架：Mybatis Hibernate Spring-Date-Jpa

2)半自动化 ORM框架

​ ORM：对象关系映射思想

面向对象OOP	关系型数据库
类（User）	t_user
成员变量	字段
类对象	记录

3）Mybatis-半自动化：

​ 1.表需要手动进行设计

​ 主表 从表

2.应用程序提供sql-基本功（以查询为主）

​ 单表查询-(条件过滤 排序 分组 子查询 聚合查询-聚合函数)

​ 多表连接查询

​ 3.依赖数据库平台

优点：上手简单（基于原生的JDBC封装）,优化比较灵活，适合互联网项目

4)Hibernate-自动化ORM框架

​ 1.表可以通过框架自动化创建‘

​ 2.省掉基本的sql（增删查改）

​ 3.不依赖数据库平台

缺点：:学习成本较高 优化难度较大 适用于传统的软件( OA | 图书馆管理系统 | ERP。。 ) 不适合大型的互联网项目(电商 金融项目)

2.什么是MyBatis?

​ MyBatis 是一款优秀的持久层框架，它支持定制化 SQL、存储过程以及高级映射。MyBatis 避免了几乎所有的 JDBC 代码和手动设置参数以及获取结果集。MyBatis 可以使用简单的 XML 或注解来配置和映射原生类型、接口和 Java 的 POJO（Plain Old Java Objects，普通老式 Java 对象）为数据库中的记录。

​ 1.支持定制化sql（程序员编写sql）

​ 2.支持存储过程调用（数据库端脚本）

​ 3.映射处理（结果映射）

屏蔽原生的jdbc 代码( Connection PS ResultSet 资源关闭 )

XML 配置 | 注解配置

二、Mybatis环境搭建

1.构建Maven普通工程-quick-start工程

2.添加坐标

<dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>


    <!-- mybatis jar 包依赖 -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.1</version>
    </dependency>
    <!-- 数据库驱动 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.39</version>
    </dependency>

    <!-- log4j 日志打印 -->
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.16</version>
    </dependency>

<build>
    <!--
          Maven 项目
             如果源代码(src/main/java)存在xml  properties  tld 等文件  maven 默认不会自动编译该文件到输出目录
             如果要编译源代码中xml properties tld 等文件  需要显式配置resources 标签
        -->
    <resources>
      <resource>
        <directory>src/main/resources</directory>
      </resource>
      <resource>
        <directory>src/main/java</directory>
        <includes>
          <include>**/*.xml</include>
          <include>**/*.properties</include>
          <include>**/*.tld</include>
        </includes>
        <filtering>false</filtering>
      </resource>
    </resources>
  </build>

3.添加日志文件log4j.properties

src/main/resources目录下

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

4.添加全局配置文件mybatis.xml

src/main/resources

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <!--
        映射文件加载配置
    -->
    <mappers>
        <!--
           resource:包路径  com/shsxt/xxx/xxxMapper.xml
        -->
        <mapper resource="com/shsxt/mappers/UserMapper.xml"/>
    </mappers>
</configuration>

5.添加User对象

public class User {
    private  Integer id;
    private String userName;
    private String userPwd;
    private String flag;
    private Date createTime;

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", userPwd='" + userPwd + '\'' +
                ", flag='" + flag + '\'' +
                ", createTime=" + createTime +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserPwd() {
        return userPwd;
    }

    public void setUserPwd(String userPwd) {
        this.userPwd = userPwd;
    }

    public String getFlag() {
        return flag;
    }

    public void setFlag(String flag) {
        this.flag = flag;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
}

6.添加Sql映射文件

src/main/java/com.shsxt.mappers

<?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.shsxt.mappers.UserMapper">
    <!--
       mapper:namespace 命名空间
             值全局唯一 推荐使用  包+文件名(不包含后缀): com.shsxt.mappers.UserMapper
    -->


    <!--
       查询标签 select 又称为 Statement
         标签基本属性配置
           id:Statement 唯一标识  当前文件内值唯一
           parameterType:入参类型   基本类型  String Date JavaBean  Map  数组  List
           resultType:结果类型  基本类型 String Date  JavaBean  Map  List
         标签体:sql 串
    -->
    <select id="queryUserById" parameterType="int" resultType="com.shsxt.vo.User">
       select id,user_name as userName,user_pwd as userPwd,flag,create_time as createTime
       from user
       where id=#{userId}
  </select>
</mapper>

7.添加测试代码和启动测试

​ src/test/java

 @Test
   public void test() throws IOException {
        /**
         * 1.加载全局配置文件 构建sqlSessionFactory
         * 2.获取会话SqlSession
         * 3.调用方法执行查询
         * 4.关闭会话
         */
        //得到流加载全局配置文件
        InputStream is=Resources.getResourceAsStream("mybatis.xml");
        // 构建sqlSessionFactory
        SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(is);
        //获取会话SqlSession
        SqlSession session=factory.openSession();
        //调用方法执行查询
        User user=session.selectOne("com.shsxt.mappers.UserMapper.queryUserById",75);
        System.out.println(user);
        //关闭会话
        session.close();
    }

三、Mybatis sql 入参配置

​ 基本类型(包装类型)、String、Date、JavaBean、Map、List、数组

1.基本类型

 <!--
       查询标签 select 又称为 Statement
         标签基本属性配置
           id:Statement 唯一标识  当前文件内值唯一
           parameterType:入参类型   基本类型  String Date JavaBean  Map  数组  List
           resultType:结果类型  基本类型 String Date  JavaBean  Map  List
         标签体:sql 串
    -->
    <select id="queryUserById" parameterType="int" resultType="com.shsxt.vo.User">
       select id,user_name as userName,user_pwd as userPwd,flag,create_time as createTime
       from user
       where id=#{userId}
  </select>

//接口

public interface UserDao {
    //根据id查询
    public User queryUserByUserId(Integer userId);
}

//实现接口

public class UserDaoImpl implements UserDao{
//加载全局配置文件 构建sqlSessionFactory
    private SqlSessionFactory factory;

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

    @Override
    public User queryUserByUserId(Integer userId) {
        //获取会话SqlSession
        SqlSession session=factory.openSession();
        //调用方法执行查询
        User user=session.selectOne("com.shsxt.mappers.UserMapper.queryUserById",75);
        //关闭会话
        session.close();
        return user;
    }
}

//测试

public class UserTest {

    private UserDao userDao;

    @Before
    public void init() throws IOException {
        //得到流
        InputStream is=Resources.getResourceAsStream("mybatis.xml");
        //加载全局配置文件 构建sqlSessionFactory
        SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(is);
        //调用方法
        userDao=new UserDaoImpl(factory);
    }
   @Test
    public void test01(){
        System.out.println(userDao.queryUserByUserId(75));
    }
}

2.String 类型

 <sql id="user_columns">
        id,user_name as userName,user_pwd as userPwd,flag,create_time as createTime
  </sql>

    <select id="queryUserByUserName" parameterType="String" resultType="com.shsxt.vo.User">
        select <include refid="user_columns"/>
        from user
        where user_name=#{userName}
    </select>

 @Override
    public User queryUserByUserName(String userName) {
        //获取会话SqlSession
        SqlSession session=factory.openSession();
        //调用方法执行查询
        User user=session.selectOne("com.shsxt.mappers.UserMapper.queryUserByUserName","admin");
       session.close();
        return user;
    }

//测试
 @Test
    public void test02(){
        System.out.println(userDao.queryUserByUserName("admin"));
    }

3.JavaBean 类型

<select id="queryUserByUserNameAndUserPwd" parameterType="UserQuery" resultType="user">
        /*userName userPwd 为UserQueery类成员变量名*/
        select <include refid="user_columns"/>
        from user
        where user_name=#{userName} and user_pwd=#{userPwd}
    </select>

 @Override
    public User queryUserByUserNameAndUserPwd(UserQuery userQuery) {
        //获取会话SqlSession
        SqlSession session=factory.openSession();
        //调用方法执行查询
        User user=session.selectOne("com.shsxt.mappers.UserMapper.queryUserByUserNameAndUserPwd",userQuery);
        session.close();
        return user;
    }

//测试
  @Test
    public void test03(){
        UserQuery userQuery=new UserQuery();
        userQuery.setUserName("admin");
        userQuery.setUserPwd("111111");
        System.out.println(userDao.queryUserByUserNameAndUserPwd(userQuery));
    }

4.Map 类型

 <select id="queryUserByUserNameAndUserPwdMap" parameterType="map" resultType="user">

        select <include refid="user_columns"/>
        from user
        where user_name=#{userName} and user_pwd=#{userPwd}
    </select>

 @Override
    public User queryUserByUserNameAndUserPwdMap(String userName, String userPwd) {
        //获取会话SqlSession
        SqlSession session=factory.openSession();
        Map<String,Object> params=new HashMap<>();
        params.put("userName",userName);
        params.put("userPwd",userPwd);
        User user=session.selectOne("com.shsxt.mappers.UserMapper.queryUserByUserNameAndUserPwdMap",params);
        session.close();
        return user;
    }

//测试
  @Test
    public void test04(){    System.out.println(userDao.queryUserByUserNameAndUserPwdMap("admin","111111"));
    }

5.数组类型

 <update id="updateUserPasswordByIds">
        update user set user_pwd="111111" where id in
        <foreach collection="array" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </update>

@Override
    public Integer updateUserPasswordByIds(Integer[] ids) {
        //默认获取的sqlSession不会自动提交事务，需要显示指定事务自动提交
       SqlSession session=factory.openSession(true);
       int total=session.update("com.shsxt.mappers.UserMapper.updateUserPasswordByIds",ids);
       session.close();
       return total;
    }

//测试
 @Test
    public void test05(){
        System.out.println(userDao.updateUserPasswordByIds(new Integer[]{82,83,84,85}));
    }

四、Mybatis sql 结果参数配置

结果参数：基本类型（4类8种）String Date JavaBean List Map List<Map>
resultType:String Date JavaBean Map
resultMap:属于一个标签id值，为另一个resultMap 标签的id

1.基本类型

 <select id="countUser" resultType="int">
        select count(1) from user
 </select>

 @Override
    public Integer countUser() {
        SqlSession session = factory.openSession();
        Integer total = session.selectOne("com.shsxt.mappers.UserMapper.countUser");
        session.close();
        return total;
    }

//测试
@Test
    public void test06(){
        System.out.println(userDao.countUser());
    }

2.日期类型

<select id="queryUserCreateTimeByUserId" parameterType="int" resultType="date">
        select create_time from user where id=#{userId}
</select>

 @Override
    public Date queryUserCreateTimeByUserId(Integer userId) {
        SqlSession session = factory.openSession();
        Date date = session.selectOne("com.shsxt.mappers.UserMapper.queryUserCreateTimeByUserId",75);
        session.close();
        return date;
    }

//测试
@Test
    public void test07(){
        System.out.println(new SimpleDateFormat("yyyy-MM-dd").format(userDao.queryUserCreateTimeByUserId(75)));
    }

3.JavaBean 类型

 <select id="queryUsersByUserNameLike" parameterType="String" resultType="User">
        select <include refid="user_columns"/>
        from user
        where user_name like concat('%',#{userName},'%')
    </select>

@override
public List<User> queryUsersByUserNameLike(String userName) {
        SqlSession session = factory.openSession();
        List<User> users = session.selectList("com.shsxt.mappers.UserMapper.queryUsersByUserNameLike",userName);
        session.close();
        return users;
    }

//测试
@Test
    public void test08(){
      List<User> users=userDao.queryUsersByUserNameLike("test");
      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);
            }
        );
    }

4.Map 类型

 <select id="queryUserByUserNameAndUserPwdMap2" parameterType="UserQuery" resultType="map">
        select <include refid="user_columns"/>
        from user
        where user_name=#{userName} and user_pwd=#{userPwd}
    </select>

 @Override
    public Map queryUserByUserNameAndUserPwdMap2(UserQuery userQuery) {
        SqlSession session = factory.openSession();
        Map<String,Object> result=session.selectOne("com.shsxt.mappers.UserMapper.queryUserByUserNameAndUserPwdMap2",userQuery);
        return result;
    }

//测试
 @Test
    public void test09(){
        UserQuery userQuery=new UserQuery();
        userQuery.setUserName("admin");
        userQuery.setUserPwd("111111");
        Map<String,Object> result=userDao.queryUserByUserNameAndUserPwdMap2(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("=============================");
        result.forEach((k,v)->{
            System.out.println(k+","+v);
        });
        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> set2=result.keySet();
        for (String key:set2){
            System.out.println(key+","+result.get(key));
        }
    }

5.List 类型

 <select id="queryUsersByUserNameLikeMap" parameterType="string" resultType="map">
        select <include refid="user_columns"/> from user where user_name like concat('%',#{userName},'%')
    </select>

@Override
    public List<Map<String, Object>> queryUsersByUserNameLikeMap(String userName) {
        SqlSession session = factory.openSession();
        List<Map<String,Object>> results = session.selectList("com.shsxt.mappers.UserMapper.queryUsersByUserNameLikeMap",userName);
        session.close();
        return results;
    }

//测试
 @Test
    public void test10(){
      List<Map<String,Object>>  test=userDao.queryUsersByUserNameLikeMap("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("++++++++++++++++++++++++++");
        }
        System.out.println("-----------------------------");
      test.forEach(new Consumer<Map<String, Object>>() {
          @Override
          public void accept(Map<String, Object> stringObjectMap) {
              stringObjectMap.forEach(new BiConsumer<String, Object>() {
                  @Override
                  public void accept(String s, Object o) {
                      System.out.println(s+","+o);
                  }
              });
          }
      });
        System.out.println("---=================----------");
        test.forEach(m->{
            m.forEach((k,v)->{
                System.out.println(k+","+v);
            });
        });
    }

6.ResultMap 接收结果

 <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>

五、Mybatis 常见元素

​ 注意元素的配置顺序 在src/resources/mybatis.xml

（properties?, settings?, typeAliases?, typeHandlers?, objectFactory?, objectWrapperFactory?, reflectorFactory?, plugins?, environments?, databaseIdProvider?, mappers?)

1.properties 属性

jdbc.properties文件

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/mybatis
jdbc.uname=root
jdbc.password=123456

src/resources/mybatis.xml

<!--连接数据库配置文件-->
    <properties resource="jdbc.properties"></properties>

2.Settings 属性

src/resources/mybatis.xml

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

3.typeAilases 属性（别名包）

src/resources/mybatis.xml

<typeAliases>
        <!--&lt;!&ndash;配置01&ndash;&gt;
        <typeAlias type="com.shsxt.vo.User" alias="User"></typeAlias>
        <typeAlias type="com.shsxt.query.UserQuery" alias="UserQuery"></typeAlias>-->
        <!--配置02
            指定包的路径 该包下所有javaBean 均起别名 默认 类名（常用）
        -->
        <package name="com.shsxt.vo"/>
        <package name="com.shsxt.query"/>
    </typeAliases>

4.mappers 属性

 <!--
        映射文件加载配置
    -->
    <mappers>
        <!--
           resource:包路径  com/shsxt/xxx/xxxMapper.xml
        -->
        <mapper resource="com/shsxt/mappers/UserMapper.xml"/>
      <!--  <mapper class="com.shsxt.dao.AccountDao"></mapper>-->
      <package name="com.shsxt.dao"/>
    </mappers>

实现

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);
}


public class AccountTest {

    @Test
    public void test() throws IOException {
        //得到流
        InputStream is= Resources.getResourceAsStream("mybatis.xml");
        //加载全局配置文件 构建sqlSessionFactory
        SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(is);
        //获取会话SqlSession
        SqlSession session=factory.openSession();
        /**
         * 获取接口的代理对象  运行期动态为AccountDao 创建代理对象
         */
        AccountDao accountDaoProxy=session.getMapper(AccountDao.class);
        //调用方法执行查询
        System.out.println(accountDaoProxy.queryAccountById(150));
        //关闭会话
        session.close();
    }
}

4.plugins插件配置（分页插件pageHelper）

Day02Mybatis笔记

一、Mybatis核心标签

基于接口代理实现CRUD规范

1.sql映射文件namespace值 为接口的权限定名(全路径)包名+接口名
2.insert、Select、Update、Delete、标签id值与接口方法名一致
3.sql映射文件输入参数类型与接口方法参数类型一致
4.sql映射文件输出结果类型与接口方法返回值类型一致

单框架环境下

1.sql映射文件与接口文件在同一个包
2.sql映射文件文件名与接口名一致

1.select

​ 参考sql输入 输出 xml配置 parameterType设置添加的参数类型（JavaBean Map)

2.Insert

默认返回影响行数 做添加操作

1）添加记录返回影响行数

定义接口方法

com.shsxt.dao—>IAccountDao.java

  public int insertAccount(Account account);

添加xml配置

<insert id="insertAccount" 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>

添加测试

@Test
    public void test() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession(true);
        /**
         * 获取接口的代理对象  运行期动态为IAccountDao 创建代理对象
         */
        IAccountDao accountDaoProxy = session.getMapper(IAccountDao.class);
        Account account = new Account("第一桶金", "1", 1000.0, 30, new Date(), new Date(), "我的第一桶金");
        System.out.println(accountDaoProxy.insertAccount(account));
        session.close();
    }

2)添加记录返回主键

第一种方法：

  public Integer insertAccountHasPrimaryKey(Account account);

<!--
        添加记录返回主键01
           useGeneratedKeys:通知框架 执行添加时获取记录的主键
           keyProperty:设置接收主键的成员变量名
   -->
    <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>

添加测试

@Test
    public void test2() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession(true);
        /**
         * 获取接口的代理对象  运行期动态为IAccountDao 创建代理对象
         */
        IAccountDao accountDaoProxy = session.getMapper(IAccountDao.class);
        Account account = new Account("第一桶金", "1", 1000.0, 20, new Date(), new Date(), "我的第一桶金");
        System.out.println(accountDaoProxy.insertAccountHasPrimaryKey(account)+","+account.getId());
        session.close();
    }

第二种方法：

public Integer insertAccountHasPrimaryKey2(Account account);

 <!--
         添加记录返回主键02
            selectKey
             标签属性:
               order:声明添加的sql在执行前或者执行后获取主键
               resultType:主键结果类型
               keyProperty:设置接收主键的成员变量名
             标签体:
                 mysql: select last_insert_id()
                 oracle:select xxxSequense.nextVal() from dual
    -->
    <insert id="insertAccountHasPrimaryKey2" 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>

3)批量添加

 public Integer saveAccountBath(List<Account> accounts);

<insert id="saveAccountBath">
         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>

添加测试

 @Test
    public void test4() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession(true);
        /**
         * 获取接口的代理对象  运行期动态为IAccountDao 创建代理对象
         */
        IAccountDao accountDaoProxy = session.getMapper(IAccountDao.class);
        Account account = new Account("hhh", "1", 1000.0, 20, new Date(), new Date(), "hhh");
        List<Account> accounts= Arrays.asList(
                new Account("hhh", "1", 1000.0, 20, new Date(), new Date(), "hhh"),
                new Account("sss", "1", 1000.0, 20, new Date(), new Date(), "hhh"),
                new Account("qqq", "1", 1000.0, 20, new Date(), new Date(), "hhh")
        );
        System.out.println(accountDaoProxy.saveAccountBath(accounts));
        session.close();
    }

3.Update

默认返回影响行数 做更新操作

1）根据主键更新记录

定义接口方法

 public Integer updateAccount(Account account);

xml配置

 <update id="updateAccount" parameterType="com.sxt.vo.Account" >
        update account set aname=#{aname},type=#{type},money=#{money},remark=#{remark}
        where id=#{id}
    </update>

添加测试

 @Test
    public void test5() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession(true);
        /**
         * 获取接口的代理对象  运行期动态为IAccountDao 创建代理对象
         */
        IAccountDao accountDaoProxy = session.getMapper(IAccountDao.class);
        Account account=accountDaoProxy.queryAccountById(174);
        account.setAname("test");
        account.setType("2");
        System.out.println(accountDaoProxy.updateAccount(account));
        session.close();
    }

2)批量更新

## dev开发环境
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
jdbc.uname=root
jdbc.password=123456

public Integer updateAccountBatch(List<Account> accounts);

<update id="updateAccountBatch" >
        <foreach collection="list" item="item" separator=";">
            update account set aname=#{item.aname},type=#{item.type},money=#{item.money},remark=#{item.remark}
            where id=#{item.id}
        </foreach>
    </update>

测试

@Test
    public void test6() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession(true);
        /**
         * 获取接口的代理对象  运行期动态为IAccountDao 创建代理对象
         */
        IAccountDao accountDaoProxy = session.getMapper(IAccountDao.class);
        Account account01=accountDaoProxy.queryAccountById(174);
        account01.setType("4");
        Account account02=accountDaoProxy.queryAccountById(175);
        //account02.setAname("test");
        account02.setType("2");
        System.out.println(accountDaoProxy.updateAccountBatch(Arrays.asList(account01,account02)));
        session.close();
    }

4.Delete

1)单记录删除

  public Integer deleteAccountById(Integer id);

 <delete id="deleteAccountByIds" parameterType="map">
        delete from account where id in
        <foreach collection="ids" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </delete>

测试

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

2)批量删除

public  Integer deleteAccountByIds(Map<String,Object> map);

 <delete id="deleteAccountByIds" parameterType="map">
        delete from account where id in
        <foreach collection="ids" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </delete>

测试

  @Test
public void test7() throws IOException {
        InputStream is = Resources.getResourceAsStream("mybatis.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
        SqlSession session = factory.openSession(true);
        /**
         * 获取接口的代理对象  运行期动态为IAccountDao 创建代理对象
         */
        IAccountDao accountDaoProxy = session.getMapper(IAccountDao.class);
Map<String,Object> map=new HashMap<>();
        map.put("ids",new Integer[]{169,170,171});
        System.out.println(accountDaoProxy.deleteAccountByIds(map));
    session.close();
}

二、Mybatis动态sql

1.基于XML标签配置

if标签

<!--
       动态sql-if 条件判断标签
    -->
    <select id="queryByParams" parameterType="com.shsxt.taojin.query.AccountQuery" resultType="com.shsxt.taojin.vo.Account">
        select <include refid="account_columns"/> from account
        where 1=1
        <if test="null !=userId">
          and  user_id=#{userId}
        </if>
        <if test="null !=aname and aname !=''">
            and aname like concat('%',#{aname},'%')
        </if>
        <if test="null !=type and type !=''">
            and type=#{type}
        </if>
        <if test="null !=time and time !=''">
            and create_time &gt;= #{time}
        </if>
    </select>

where记录过滤标签

<!--
       动态sql-where 标签 结果过滤
           如果where 后第一个过滤条件出现 and | or mybatis 自动忽略and
    -->
    <select id="queryByParams" parameterType="com.shsxt.taojin.query.AccountQuery" resultType="com.shsxt.taojin.vo.Account">
        select <include refid="account_columns"/> from account
       <where>
           <if test="null !=userId">
              and   user_id=#{userId}
           </if>
           <if test="null !=aname and aname !=''">
               and aname like concat('%',#{aname},'%')
           </if>
           <if test="null !=type and type !=''">
               and type=#{type}
           </if>
           <if test="null !=time and time !=''">
               and create_time &gt;= #{time}
           </if>
       </where>
    </select>

choose when otherwise类似if

 <!--
       动态sql03-choose when otherwise  条件切换  切换字段返回
    -->
    <select id="queryByParams" parameterType="com.shsxt.taojin.query.AccountQuery" resultType="com.shsxt.taojin.vo.Account">
        select <include refid="account_columns"/> from account
        <where>
            <choose>
                <when test="null !=userId">
                    user_id=#{userId} and type ='0'
                </when>
                <when test="null !=aname">
                     and aname like concat('%',#{aname},'%')
                </when>
                <otherwise>
                    type='1'
                </otherwise>
            </choose>
        </where>
    </select>

<!--<select id="queryByParams" parameterType="com.shsxt.taojin.query.AccountQuery" resultType="com.shsxt.taojin.vo.Account">
        select
            id, aname, user_id, create_time, update_time,type,
              <choose>
                  <when test="null !=type and type !=''">
                      remark
                  </when>
                  <otherwise>
                      money
                  </otherwise>
              </choose>
        from account
        <where>
            <choose>
                <when test="null !=userId">
                    user_id=#{userId} and type ='0'
                </when>
                <when test="null !=aname">
                    and aname like concat('%',#{aname},'%')
                </when>
                <otherwise>
                    type='1'
                </otherwise>
            </choose>
        </where>
    </select>-->

trim标签

 <!--
        动态sql04-trim  可以替换where  set
    -->
     <select id="queryByParams" parameterType="com.shsxt.taojin.query.AccountQuery" resultType="com.shsxt.taojin.vo.Account">
        select <include refid="account_columns"/>
        from account
        <trim prefix="where" prefixOverrides="and |or" >
            <if test="null !=userId">
                and   user_id=#{userId}
            </if>
            <if test="null !=aname and aname !=''">
                and aname like concat('%',#{aname},'%')
            </if>
            <if test="null !=type and type !=''">
                and type=#{type}
            </if>
            <if test="null !=time and time !=''">
                and create_time &gt;= #{time}
            </if>
        </trim>
    </select>


 <update id="update" parameterType="com.shsxt.taojin.vo.Account">
        update account
        <trim prefix="set" suffixOverrides="," >
            <if test="aname !=null and aname !=''">
                aname =#{aname},
            </if>
            <if test="type !=null and type !=''">
                type=#{type},
            </if>
            <if test="remark !=null and remark !=''">
                remark=#{remark},
            </if>
        </trim>
        where id=#{id}
    </update>

set标签 更新标签

 <!--
       动态sql05-set 标签
    -->
  <update id="update" parameterType="com.shsxt.taojin.vo.Account">
        update account
        <set>
            <if test="aname !=null and aname !=''">
                aname =#{aname},
            </if>
            <if test="type !=null and type !=''">
                type=#{type},
            </if>
            <if test="remark !=null and remark !=''">
                remark=#{remark},
            </if>
        </set>
        where id=#{id}
    </update>

foreach批量操作标签

   <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>

2.基于注解

  @Insert   @Select @Update @Delete
  @InsertProvider @SelectProvider 	@UpdateProvider  @DeleteProvider

添加

 /*第一种注解配置*/

    @Insert("insert into account(aname, type, money, user_id, create_time, update_time, remark) values" +
            "(#{aname},#{type},#{money},#{userId},#{createTime},#{updateTime},#{remark})")
public Integer saveAccount(Account account);

/*第二种注解配置*/

@InsertProvider(type= AccountProvider.class,method="getInsertAccountSql")
    public Integer saveAccount2(Account account);

//定义方法getInsertAccountSql

 public String getInsertAccountSql(Account account){
        return "insert into account(aname, type, money, user_id, create_time, update_time, remark) values" +
                "(#{aname},#{type},#{money},#{userId},#{createTime},#{updateTime},#{remark})";
    }

 /*第三种注解配置*/

@InsertProvider(type= AccountProvider.class,method="getInsertAccountSql2")
    public Integer saveAccount3(Account account);

//定义方法getInsertAccountSql2

 public String getInsertAccountSql2(Account account){
        return new SQL() {
            {
                INSERT_INTO("account");
                if(StringUtils.isNoneBlank(account.getAname())){
                    VALUES("aname","#{aname}");
                }
                if(StringUtils.isNoneBlank(account.getType())){
                    VALUES("type","#{type}");
                }
            }
        }.toString();
    }

测试类

 @Test
    public  void test() throws IOException {
        //加载全局配置
        InputStream is= Resources.getResourceAsStream("mybatis.xml");
        //创建SqlFactory
        SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(is);
        //获取一次会话
        SqlSession session=factory.openSession(true);
        /**
         * 获取接口的代理对象 运行动态为IAccountDao 创建代理对象
         */
        IAccountDao accountDaoProxy=session.getMapper(IAccountDao.class);
        Account account=new Account("xiao","1", 1000.0,10,new Date(),new Date(),"test01");
        System.out.println(accountDaoProxy.saveAccount3(account));
        //关闭会话
        session.close();
    }

查询

//第一种注解配置
@Select("select id,aname,type,user_id,money,remark,create_time,update_time from account where id=#{id}")
    public Account queryAccountById(Integer id);

//第二种注解配置
@SelectProvider(type=AccountProvider.class,method = "getQueryAccountById2Sql")
    public Account queryAccountById2(Integer id);

//定义方法

  public String getQueryAccountById2Sql(){
        return "select id,aname,type,user_id,money,remark,create_time,update_time from account where id=#{id}";
    }


 //第三种注解配置  
@SelectProvider(type=AccountProvider.class,method = "getQueryAccountById3Sql")
    public Account queryAccountById3(Integer id);

//定义方法
 public String getQueryAccountById3Sql(){
        return new SQL(){
            {
            SELECT(" id,aname,type,money,user_id,remark,create_time,update_time");
            FROM("account");
            WHERE("id=#{id}");
            }
        }.toString();
    }

//查询是一个集合

    @SelectProvider(type = AccountProvider.class,method = "getQueryAccountsByParams")
    public List<Account> queryAccountsByParams(AccountQuery accountQuery);

//定义方法

 public String getQueryAccountsByParams(AccountQuery accountQuery){
        return new SQL(){{
            SELECT("id,aname,type,user_id,money,remark,create_time,update_time");
            FROM("account");
            WHERE("1=1");
            if(null !=accountQuery.getUserId()){
                WHERE("user_id=#{userId}");
            }
            if(StringUtils.isNoneBlank(accountQuery.getAname())){
                WHERE("aname like concat('%',#{aname},'%')");
            }
            if(StringUtils.isNoneBlank(accountQuery.getType())){
                WHERE("type=#{type}");
            }
            if(StringUtils.isNoneBlank(accountQuery.getTime())){
                WHERE("create_time=#{time}");
            }

        }}.toString();
    }

测试类

 @Test
    public  void test3() throws IOException {
        //加载全局配置
        InputStream is= Resources.getResourceAsStream("mybatis.xml");
        //创建SqlFactory
        SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(is);
        //获取一次会话
        SqlSession session=factory.openSession(true);
        /**
         * 获取接口的代理对象 运行动态为IAccountDao 创建代理对象
         */
        IAccountDao accountDaoProxy=session.getMapper(IAccountDao.class);
        System.out.println(accountDaoProxy.deleteAccount3(179));
        //关闭会话
        session.close();
    }

//集合测试

@Test
    public  void test4() throws IOException {
        //加载全局配置
        InputStream is= Resources.getResourceAsStream("mybatis.xml");
        //创建SqlFactory
        SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(is);
        //获取一次会话
        SqlSession session=factory.openSession(true);
        /**
         * 获取接口的代理对象 运行动态为IAccountDao 创建代理对象
         */
        IAccountDao accountDaoProxy=session.getMapper(IAccountDao.class);
        AccountQuery accountQuery=new AccountQuery();
        accountQuery.setUserId(20);
        accountQuery.setType("4");
        accountDaoProxy.queryAccountsByParams(accountQuery).forEach(a->{
            System.out.println(a);
        });
        //关闭会话
        session.close();
    }

更新

类比添加注解配置差不多

删除

//第一种注解
@Delete("delete from account where id=#{id}")
    public Integer deleteAccount(Integer id);

//第二种注解
@DeleteProvider(type=AccountProvider.class,method = "deleteAccount2Sql")
    public Integer deleteAccount2(Integer id);

//定义方法
 public String deleteAccount2Sql(){
        return "delete from account where id=#{id}";
    }

//第三种注解  
@DeleteProvider(type=AccountProvider.class,method = "deleteAccount3Sql")
    public Integer deleteAccount3(Integer id);

//定义方法

    public String deleteAccount3Sql(){
        return new SQL(){{
            DELETE_FROM("account");
            WHERE("id=#{id}");
        }}.toString();
    }

测试类

 @Test
    public  void test3() throws IOException {
        //加载全局配置
        InputStream is= Resources.getResourceAsStream("mybatis.xml");
        //创建SqlFactory
        SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(is);
        //获取一次会话
        SqlSession session=factory.openSession(true);
        /**
         * 获取接口的代理对象 运行动态为IAccountDao 创建代理对象
         */
        IAccountDao accountDaoProxy=session.getMapper(IAccountDao.class);
        System.out.println(accountDaoProxy.deleteAccount3(179));
        //关闭会话
        session.close();
    }

三、Mybatis 整合Spring

1.创建mybatis-spring web工程

2.添加坐标依赖

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-context</artifactId>
      <version>4.3.2.RELEASE</version>
    </dependency>
    <!-- spring 测试 jar -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-test</artifactId>
      <version>4.3.2.RELEASE</version>
    </dependency>
    <!-- spring jdbc -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>4.3.2.RELEASE</version>
    </dependency>
    <!-- spring 事务 -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-tx</artifactId>
      <version>4.3.2.RELEASE</version>
    </dependency>
    <!-- aspectj 切面编程的 jar -->
    <dependency>
      <groupId>org.aspectj</groupId>
      <artifactId>aspectjweaver</artifactId>
      <version>1.8.9</version>
    </dependency>
    <!-- c3p0 连接池 -->
    <dependency>
      <groupId>c3p0</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.1.2</version>
    </dependency>
    <!-- mybatis -->
    <dependency><groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.1</version>
    </dependency>
    <!-- 添加 mybatis 与 Spring 整合的核心包 -->
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis-spring</artifactId>
      <version>1.3.0</version>
    </dependency>
    <!-- mysql 驱动包 -->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.39</version>
    </dependency>
    <!-- 日志打印相关的 jar -->
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-log4j12</artifactId>
      <version>1.7.2</version>
    </dependency>
    <dependency>
      <groupId>org.slf4j</groupId>
      <artifactId>slf4j-api</artifactId>
      <version>1.7.2</version>
    </dependency>
  </dependencies>

  <build>
    <finalName>ssm</finalName>
      <!--
                Maven 项目
                   如果源代码(src/main/java)存在xml  properties  tld 等文件  maven 默认不会自动编译该文件到输出目录
                   如果要编译源代码中xml properties tld 等文件  需要显式配置resources 标签
              -->
      <resources>
          <resource>
              <directory>src/main/resources</directory>
          </resource>
          <resource>
              <directory>src/main/java</directory>
              <includes>
                  <include>**/*.xml</include>
                  <include>**/*.properties</include>
                  <include>**/*.tld</include>
              </includes>
              <filtering>false</filtering>
          </resource>
      </resources>
  </build>

3.添加配置文件

log4j.properties

# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

jdbc.properties

## dev开发环境
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
jdbc.uname=root
jdbc.password=123456

mybatis.xml

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE configuration        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"        "http://mybatis.org/dtd/mybatis-3-config.dtd"><configuration>
    <settings>      
        <!--            数据库字段(带有_字符的字段) 自动转换为驼峰命名        -->      
        <setting name="mapUnderscoreToCamelCase" value="true"/>   
    
   </settings>
   <typeAliases>      
    	<package name="com.shsxt.vo"/>       		    <package name="com.shsxt.query"/>    </typeAliases></configuration>

spring.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx.xsd">

    <!--
       配置扫描器
    -->
    <context:component-scan base-package="com.shsxt"/>

    <!--
       properties 文件加载配置
    -->
    <context:property-placeholder location="classpath:jdbc.properties"/>

    <!--
       c3p0 数据源配置
    -->
    <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
        <property name="driverClass" value="${jdbc.driver}"></property>
        <property name="jdbcUrl" value="${jdbc.url}"></property>
        <property name="user" value="${jdbc.uname}"></property>
        <property name="password" value="${jdbc.password}"></property>
    </bean>

    <aop:aspectj-autoproxy/>

    <!--
       事物管理器
    -->
    <bean id="txManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <tx:advice id="txAdvice" transaction-manager="txManager">
        <tx:attributes>
            <tx:method name="save*" propagation="REQUIRED"/>
            <tx:method name="update*" propagation="REQUIRED"></tx:method>
            <tx:method name="del*" propagation="REQUIRED"></tx:method>
        </tx:attributes>
    </tx:advice>

    <aop:config>
        <aop:pointcut id="cut" expression="execution(* com.shsxt.service..*.*(..))"/>
        <aop:advisor pointcut-ref="cut" advice-ref="txAdvice"></aop:advisor>
    </aop:config>


    <!--
       Mybatis 整合Spring
    -->
    <bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"></property>
        <!--
            mybatis 全局配置文件
        -->
        <property name="configLocation" value="classpath:mybatis.xml"></property>
        <!--
            sql 映射文件
        -->
        <property name="mapperLocations" value="classpath:com/shsxt/mappers/*.xml"></property>
    </bean>

    <bean id="mapperScanner" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.shsxt.dao">
        </property>
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactoryBean">
        </property>
    </bean>
    
</beans>

4.源代码

UserDao(com.shsxt.dao.userDao)

package com.shsxt.dao;

import com.shsxt.vo.User;

public interface UserDao {
    public User queryUserByUserId(Integer userId);
}

User.java

package com.shsxt.vo;

import java.util.Date;

public class User {
    private Integer id;
    private String userName;
    private String userPwd;
    private String flag;
    private Date createTime;

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", userPwd='" + userPwd + '\'' +
                ", flag='" + flag + '\'' +
                ", createTime=" + createTime +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getUserPwd() {
        return userPwd;
    }

    public void setUserPwd(String userPwd) {
        this.userPwd = userPwd;
    }

    public String getFlag() {
        return flag;
    }

    public void setFlag(String flag) {
        this.flag = flag;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
}

UserMapper.xml(com.shsxt.mappers.userMapper.xml)

<?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.shsxt.dao.UserDao">


    <select id="queryUserByUserId" parameterType="int" resultType="User">
        select id, user_name, user_pwd, flag, create_time from user where id=#{userId}
    </select>
</mapper>

添加单元测试

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:spring.xml"})
public class TestSSM {

    @Autowired
    private UserDao userDao;
    @Test
    public  void test(){
        System.out.println(userDao.queryUserByUserId(75));
    }
}

Day03Mybatis笔记

一、Mybatis分页插件的配置

1.添加分页插件依赖坐标

 <!--分页插件-->
    <!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper -->
    <dependency>
      <groupId>com.github.pagehelper</groupId>
      <artifactId>pagehelper</artifactId>
      <version>5.1.11</version>
    </dependency>

2.添加mybatis拦截器

在全局文件mybatis.xml添加

<!--mybatis 拦截器-->
    <plugins>
        <!-- com.github.pagehelper为PageHelper类所在包名 -->
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin>
    </plugins>

3.定义接口方法

public interface AccountDao {
    
    public List<Account> queryByParams(AccountQuery accountQuery);
}

AccountQuery类

public class AccountQuery {
    private Integer pageNum=1;
    private Integer pageSize=10;
    private Integer userId;
    private String aname;
    private String type;
    private String time;

    public Integer getPageNum() {
        return pageNum;
    }

    public void setPageNum(Integer pageNum) {
        this.pageNum = pageNum;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getAname() {
        return aname;
    }

    public void setAname(String aname) {
        this.aname = aname;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }

    public String getTime() {
        return time;
    }

    public void setTime(String time) {
        this.time = time;
    }
}

4.实现接口方法

<?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.shsxt.dao.AccountDao">

    <sql id="account_columns">
        id, aname, type, money, user_id, create_time, update_time, remark
    </sql>
    <!--
       动态sql-where 标签 结果过滤
           如果where 后第一个过滤条件出现 and | or mybatis 自动忽略and
    -->
    <select id="queryByParams" parameterType="AccountQuery" resultType="com.shsxt.vo.Account">
        select <include refid="account_columns"/> from account
       <where>
           <if test="null !=userId">
              and   user_id=#{userId}
           </if>
           <if test="null !=aname and aname !=''">
               and aname like concat('%',#{aname},'%')
           </if>
           <if test="null !=type and type !=''">
               and type=#{type}
           </if>
           <if test="null !=time and time !=''">
               and create_time &gt;= #{time}
           </if>
       </where>
    </select>
</mapper>

service层

@Service
public class AccountService {
    @Resource
    private AccountDao accountDao;

  //返回集合信息分页查询
    public List<Account> queryAccountsByParams(AccountQuery accountQuery){
        PageHelper.startPage(accountQuery.getPageNum(),accountQuery.getPageSize());
        return accountDao.queryByParams(accountQuery);
    }

   //分页信息
    
    public PageInfo<Account> queryAccountsByParamsPageInfo(AccountQuery accountQuery){
        PageHelper.startPage(accountQuery.getPageNum(),accountQuery.getPageSize());
        List<Account> accounts=accountDao.queryByParams(accountQuery);
        // 不会执行分页处理
        //PageHelper.startPage(accountQuery.getPageNum(),accountQuery.getPageSize());
        //List<Account> accounts1 =  accountDao.queryAbc();
        return new PageInfo<>(accounts,10);
    }
}

5.测试类

public class TestAccount extends TestBase{
    @Resource
    private AccountService accountService;
    @Test
    public void test(){
        AccountQuery accountQuery=new AccountQuery();
        accountQuery.setUserId(75);
        accountQuery.setPageNum(2);
        accountService.queryAccountsByParams(accountQuery).forEach(a->{
            System.out.println(a);
        });
    }

    @Test
    public void test02(){
        AccountQuery accountQuery=new AccountQuery();

        PageInfo<Account> pageInfo=accountService.queryAccountsByParamsPageInfo(accountQuery);
      System.out.println("总记录："+pageInfo.getTotal()+"总页数："+pageInfo.getPages()+"页数导航："+pageInfo.getNavigatePages());
        System.out.println("===============");
        int[] navigatepageNums = pageInfo.getNavigatepageNums();
        for(int navigatepageNum:navigatepageNums)		{
            System.out.println(navigatepageNum);
        }
        System.out.println("====================");
        pageInfo.getList().forEach(a->{
            System.out.println(a);
        });
    }
}

二、Mybatis代码自动化生成&Base类封装

1.代码自动化生成配置和测试

1.1添加插件配置（pom.xml）

<build>

    <!--自动生成代码-->
    <plugins>
      <plugin>
        <groupId>org.mybatis.generator</groupId>
        <artifactId>mybatis-generator-maven-plugin</artifactId>
        <version>1.3.2</version>
        <configuration>
          <configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
          <verbose>true</verbose>
          <overwrite>true</overwrite>
        </configuration>
      </plugin>
    </plugins>
    
    
</build>

1.2添加generatorConfig.xml

(src/main/resources)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    
    <!-- mysqljar包绝对路径-->
    <classPathEntry location="E:\m2\repository\mysql\mysql-connector-java\5.1.39\mysql-connector-java-5.1.39.jar" />
    <context id="DB2Tables" targetRuntime="MyBatis3">
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://127.0.0.1:3306/mybatis"
                        userId="root"
                        password="123456">
        </jdbcConnection>

        <javaTypeResolver >
            <property name="forceBigDecimals" value="false" />
        </javaTypeResolver>


        <!--
           设置实体类存放路径
        -->
      <javaModelGenerator targetPackage="com.shsxt.vo" targetProject="D:\IdeaProjects\mybatis-spring\src\main\java">
            <property name="enableSubPackages" value="true" />
            <property name="trimStrings" value="true" />
        </javaModelGenerator>

        <!--
           sql 映射文件路径
        -->
        <sqlMapGenerator targetPackage="com.shsxt.mappers"  targetProject="D:\IdeaProjects\mybatis-spring\src\main\java">
            <property name="enableSubPackages" value="true" />
        </sqlMapGenerator>

        <!--
           接口文件存放路径
        -->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.shsxt.dao"  targetProject="D:\IdeaProjects\mybatis-spring\src\main\java">
            <property name="enableSubPackages" value="true" />
        </javaClientGenerator>
        <table tableName="id_card" domainObjectName="IdCar"
               enableCountByExample="false" enableUpdateByExample="false"
               enableDeleteByExample="false" enableSelectByExample="false"
               selectByExampleQueryId="false"></table>
    </context>
</generatorConfiguration>

1.3配置运行命令参数

mybatis-generator:generate

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vMYpfCN2-1597568040461)(F:\A20200102\高级资源\Mybatis 框架\code.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RRhNqJJd-1597568040466)(F:\A20200102\高级资源\Mybatis 框架\code2.png)]

2.Base类封装

2.1Dao层BaseMapper定义方法

package com.shsxt.base;

import org.springframework.dao.DataAccessException;
import java.util.List;

/**
 * BaseMapper  基本方法定义
 */
public interface BaseMapper<T,ID> {
    /**
     * 添加记录返回行数
     * @param entity
     * @return
     */
    public Integer insertSelective(T entity) throws DataAccessException;
    /**
     * 添加记录返回主键
     * @param entity
     * @return
     */
    public Integer insertHasKey(T entity) throws DataAccessException;
    /**
     * 批量添加
     * @param entities
     * @return
     */
    public Integer insertBatch(List<T> entities) throws DataAccessException;

    /**
     * 根据id 查询详情
     * @param id
     * @return
     */
    public T selectByPrimaryKey(ID id) throws DataAccessException;

    /**
     * 多条件查询
     * @param baseQuery
     * @return
     */
    public List<T> selectByParams(BaseQuery baseQuery) throws DataAccessException;

    /**
     * 更新单条记录
     * @param entity
     * @return
     */
    public Integer updateByPrimaryKeySelective(T entity) throws DataAccessException;

    /**
     * 批量更新
     * @param entities
     * @return
     */
    public Integer updateBatch(List<T> entities) throws DataAccessException;

    /**
     * 删除单条记录
     * @param id
     * @return
     */
    public Integer deleteByPrimaryKey(ID id) throws DataAccessException;

    /**
     * 批量删除
     * @param ids
     * @return
     */
    public Integer deleteBatch(ID[] ids) throws DataAccessException;

}

2.2 service层BaseService定义与实现

public abstract class BaseService<T,ID> {

    @Autowired
    private BaseMapper<T,ID> baseMapper;

    /**
     * 添加记录返回行数
     * @param entity
     * @return
     */
    public Integer insertSelective(T entity) throws DataAccessException{
        return baseMapper.insertSelective(entity);
    }

    /**
     * 添加记录返回主键
     * @param entity
     * @return
     */
    public ID insertHasKey(T entity) throws DataAccessException{
        baseMapper.insertHasKey(entity);
        try {
           return (ID) entity.getClass().getMethod("getId").invoke(entity);
        } catch (Exception e) {
            e.printStackTrace();
            return  null;
        }
    }

    /**
     * 批量添加
     * @param entities
     * @return
     */
    public Integer insertBatch(List<T> entities) throws DataAccessException{
        return baseMapper.insertBatch(entities);
    }


    /**
     * 根据id 查询详情
     * @param id
     * @return
     */
    public T selectByPrimaryKey(ID id) throws DataAccessException{
        return baseMapper.selectByPrimaryKey(id);
    }


    /**
     * 多条件查询
     * @param baseQuery
     * @return
     */
    public List<T> selectByParams(BaseQuery baseQuery) throws DataAccessException{
        return baseMapper.selectByParams(baseQuery);
    }


    /**
     * 更新单条记录
     * @param entity
     * @return
     */
    public Integer updateByPrimaryKeySelective(T entity) throws DataAccessException{
        return baseMapper.updateByPrimaryKeySelective(entity);
    }


    /**
     * 批量更新
     * @param entities
     * @return
     */
    public Integer updateBatch(List<T> entities) throws DataAccessException{
        return baseMapper.updateBatch(entities);
    }

    /**
     * 删除单条记录
     * @param id
     * @return
     */
    public Integer deleteByPrimaryKey(ID id) throws DataAccessException{
        return baseMapper.deleteByPrimaryKey(id);
    }

    /**
     * 批量删除
     * @param ids
     * @return
     */
    public Integer deleteBatch(ID[] ids) throws DataAccessException{
        return baseMapper.deleteBatch(ids);
    }
}

2.3实现dao层中baseMapper方法

public interface ProductCategoryMapper extends BaseMapper<ProductCategory,Long> {

}

.xml文件

<?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.shsxt.dao.ProductCategoryMapper" >
  <resultMap id="BaseResultMap" type="com.shsxt.vo.ProductCategory" >

    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="create_date" property="createDate" jdbcType="TIMESTAMP" />
    <result column="modify_date" property="modifyDate" jdbcType="TIMESTAMP" />
    <result column="version" property="version" jdbcType="BIGINT" />
    <result column="orders" property="orders" jdbcType="INTEGER" />
    <result column="grade" property="grade" jdbcType="INTEGER" />
    <result column="name" property="name" jdbcType="VARCHAR" />
    <result column="seo_description" property="seoDescription" jdbcType="VARCHAR" />
    <result column="seo_keywords" property="seoKeywords" jdbcType="VARCHAR" />
    <result column="seo_title" property="seoTitle" jdbcType="VARCHAR" />
    <result column="tree_path" property="treePath" jdbcType="VARCHAR" />
    <result column="parent" property="parent" jdbcType="BIGINT" />
  </resultMap>
  <sql id="Base_Column_List" >

    id, create_date, modify_date, version, orders, grade, name, seo_description, seo_keywords, 
    seo_title, tree_path, parent
  </sql>
  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Long" >

    select 
    <include refid="Base_Column_List" />
    from xx_product_category
    where id = #{id,jdbcType=BIGINT}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >

    delete from xx_product_category
    where id = #{id,jdbcType=BIGINT}
  </delete>
  <insert id="insertSelective" parameterType="com.shsxt.vo.ProductCategory" >

    insert into xx_product_category
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        id,
      </if>
      <if test="createDate != null" >
        create_date,
      </if>
      <if test="modifyDate != null" >
        modify_date,
      </if>
      <if test="version != null" >
        version,
      </if>
      <if test="orders != null" >
        orders,
      </if>
      <if test="grade != null" >
        grade,
      </if>
      <if test="name != null" >
        name,
      </if>
      <if test="seoDescription != null" >
        seo_description,
      </if>
      <if test="seoKeywords != null" >
        seo_keywords,
      </if>
      <if test="seoTitle != null" >
        seo_title,
      </if>
      <if test="treePath != null" >
        tree_path,
      </if>
      <if test="parent != null" >
        parent,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=BIGINT},
      </if>
      <if test="createDate != null" >
        #{createDate,jdbcType=TIMESTAMP},
      </if>
      <if test="modifyDate != null" >
        #{modifyDate,jdbcType=TIMESTAMP},
      </if>
      <if test="version != null" >
        #{version,jdbcType=BIGINT},
      </if>
      <if test="orders != null" >
        #{orders,jdbcType=INTEGER},
      </if>
      <if test="grade != null" >
        #{grade,jdbcType=INTEGER},
      </if>
      <if test="name != null" >
        #{name,jdbcType=VARCHAR},
      </if>
      <if test="seoDescription != null" >
        #{seoDescription,jdbcType=VARCHAR},
      </if>
      <if test="seoKeywords != null" >
        #{seoKeywords,jdbcType=VARCHAR},
      </if>
      <if test="seoTitle != null" >
        #{seoTitle,jdbcType=VARCHAR},
      </if>
      <if test="treePath != null" >
        #{treePath,jdbcType=VARCHAR},
      </if>
      <if test="parent != null" >
        #{parent,jdbcType=BIGINT},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.shsxt.vo.ProductCategory" >

    update xx_product_category
    <set >
      <if test="createDate != null" >
        create_date = #{createDate,jdbcType=TIMESTAMP},
      </if>
      <if test="modifyDate != null" >
        modify_date = #{modifyDate,jdbcType=TIMESTAMP},
      </if>
      <if test="version != null" >
        version = #{version,jdbcType=BIGINT},
      </if>
      <if test="orders != null" >
        orders = #{orders,jdbcType=INTEGER},
      </if>
      <if test="grade != null" >
        grade = #{grade,jdbcType=INTEGER},
      </if>
      <if test="name != null" >
        name = #{name,jdbcType=VARCHAR},
      </if>
      <if test="seoDescription != null" >
        seo_description = #{seoDescription,jdbcType=VARCHAR},
      </if>
      <if test="seoKeywords != null" >
        seo_keywords = #{seoKeywords,jdbcType=VARCHAR},
      </if>
      <if test="seoTitle != null" >
        seo_title = #{seoTitle,jdbcType=VARCHAR},
      </if>
      <if test="treePath != null" >
        tree_path = #{treePath,jdbcType=VARCHAR},
      </if>
      <if test="parent != null" >
        parent = #{parent,jdbcType=BIGINT},
      </if>
    </set>
    where id = #{id,jdbcType=BIGINT}
  </update>

</mapper>