DW-CHEN的Java点滴记录之MyBatis-CSDN博客

本文链接：https://blog.csdn.net/qq_42795277/article/details/108539632

MyBatis框架

MyBatis需要导入mybatis-3.2.8.jar包，下载地址：https://download.csdn.net/download/qq_42795277/12838279

框架它是一款半成品软件，我们可以基于这个半成品软件继续开发来完成我们个性化的需求
MyBatis是一个非常好的基于Java持久层框架，它内部封装了JDBC，所有开发者只需要关注SQL语句本身，而不需要花费精力去处理加载驱动，创建连接，创建执行者等复杂的操纵

ORM思想

ORM(Object Relational Mapping):对象关系映射，指的是持久化数据和实体对象映射模式，为了解决面向对象与关系型数据库存在的互补匹配的现象技术。

映射规则

数据库：类
表字段：类属性
表数据：对象

MyBatis相关API

Resources

org.apache.ibatis.io.Resources:加载资源的工具类
核心方法

方法名	说明
inputStream getResourceAsStrea(String fileName)	通过类加载器返回指定资源的字节输入流

SqlSessionFactoryBuilder

org.apache.ibatis.session.SqlSessionFactoryBuilder：获取SqlSessionFactory工厂对象的功能类
核心方法

方法名	说明
SqlSessionFactory build(InputStream is)	通过指定资源字节输入流获取SqlSession工厂对象

SqlSessionFactory

org.apache.ibatis.session.SqlSessionFactory：获取SqlSession构建者对象的工厂接口
核心方法

方法名	说明
SqlSession openSession()	获取SqlSession构建者对象，并开启手动提交事务
SqlSession openSession(boolean autoCommit)	获取SqlSession构建者对象，如果参数为true，则开启自动提交事务

SqlSession

org.apache.ibatis.session.SqlSession：构建者对象接口，由于执行SQL，管理事务，接口代理

方法名	说明
List<E> selectList(String statement,Object paramter)	执行查询语句，返回List集合
T selectOne(String statement,Object paramter)	执行查询语句，返回结果对象
int insert(String statement,Object paramter)	执行新增语句，返回影响行数
int update(String statement,Object paramter)	执行修改语句，返回影响行数
int delete(String statement,Object paramter)	执行删除语句，返回影响行数
void commit()	提交事务
void rollback()	回滚事务
T getMapper(Class<T> cls)	获取指定接口的代理实现类对象
void close()	释放资源

MyBatis映射配置文件

映射配置文件包含了数据和对象之间的映射关系以及要执行的SQL语句

<mapper>:核心根标签，namespace属性：名称空间
<select>:查询功能标签
<insert>:新增功能标签
<update>:修改功能标签
<delete>:删除功能标签，id属性：唯一标识，paramterType属性：指定参数映射的对象类型，resultType属性：指定结果映射的对象类型
SQL获取参数：#{属性名}

MyBatis核心配置文件

MyBatis核心配置文件包含了MyBatis最核心的设置和属性信息，如数据库连接，事务，连接池信息等

<configuration>:核心根标签
<properties>:引入数据库连接信息配置文件标签
<typeAliases>:起别名的标签
<environments>:配置数据库环境标签
<environment>:配置数据库信息标签
<transactionManager>:事务管理标签
<dataSource>:数据源标签
<property>:数据库连接信息标签
<mappers>:引入映射文件标签

LOG4J

在日常开发中，排查问题是难免需要输出MyBatis真正执行的SQL语句，参数，结果等信息，我们可以借助LOG4J的功能来实现执行信息的输出

代码

使用的数据

/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.5.40 : Database - test
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

/*Data for the table `user` */

insert  into `user`(`id`,`name`,`age`) values (3,'小李',22),(11,'小莉',12),(12,'小李利',12),(13,'小行星',12),(14,'夏利',12);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

实体类

package cn.cdw.example;

/**
 * @author DW-CHEN
 * 定义实体类，对应数据库表中的字段
 */
public class User {
    private Integer id;
    private String name;
    private Integer age;

    public User() {
    }

    public User(Integer id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

MyBatis映射文件

<?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="UserMapper">
    <select id="findAll" resultType="user">
        select * from user;
    </select>

    <select id="findById" resultType="user" parameterType="int">
        select * from user  where id = #{id};
    </select>

    <insert id="add" parameterType="cn.cdw.example.User">
        insert into user values (null ,#{name},#{age});
    </insert>

    <update id="update" parameterType="cn.cdw.example.User">
        update user set name = #{name},age = #{age} where id = #{id};
    </update>

    <delete id="delete" parameterType="java.lang.Integer">
        delete from user where id = #{id};
    </delete>
</mapper>

MyBatis核心配置文件

jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=root

核心配置文件

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

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

    <settings>
        <setting name="logImpl" value="log4j"/><!--在MyBatis中配置核心配置文件-->
    </settings>

    <typeAliases>
        <!--<typeAlias type="cn.cdw.example.User" alias="user"/>--><!--在核心配置文件设置别名，我们就可以咋映射文件中使用别名了-->
        <package name="cn.cdw.example"/><!--如果需要定义包下多个类的别名，那么使用指定包即可，别名为类名的首字母小写-->
    </typeAliases>

    <environments default="environment1">
        <environment id="environment1"><!--配置环境变量-->
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers><!--引入映射文件-->
        <mapper resource="UserMapper.xml"></mapper>
    </mappers>
</configuration>

测试

package cn.cdw.example;

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

import java.io.InputStream;
import java.util.List;

/**
 * @author DW-CHEN
 * 测试
 */
public class UserTest {
    public static void main(String[] args) throws Exception {
        findAll();//查询全部数据
        //findById();
        //add();
        //update();
        //delete();
    }

    //获取SqlSession对象通用方法
    public static SqlSession getSqlSession() throws Exception {
        InputStream resourceAsStream = Resources.getResourceAsStream("MyBatisConfig.xml");//读取核心配置文件MyBatisConfig.xml
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);//获取SqlSessionFactory工厂
        SqlSession sqlSession = sqlSessionFactory.openSession(true);//通过SqlSessionFactory工厂获取SqlSession对象

        resourceAsStream.close();
        return sqlSession;
    }

    //查询全部
    public static void findAll() throws Exception {
        SqlSession sqlSession = getSqlSession();
        List<User> user = sqlSession.selectList("UserMapper.findAll");

        for (User user1 : user) {
            System.out.println(user1.getId() + " " + user1.getName() + " "+ user1.getAge());
        }
        sqlSession.close();
    }

    //根据id查询
    public static void findById() throws Exception {
        SqlSession sqlSession = getSqlSession();
        User user = sqlSession.selectOne("UserMapper.findById", 2);
        System.out.println(user);
        sqlSession.close();
    }

    //添加
    public static void add() throws Exception{
        SqlSession sqlSession = getSqlSession();
        sqlSession.insert("UserMapper.add",new User(null,"小李利",12));
        sqlSession.close();
    }

    //修改
    public static void update() throws Exception{
        SqlSession sqlSession = getSqlSession();
        sqlSession.update("UserMapper.update",new User(11,"修改小溪",32));
        sqlSession.close();
    }

    //根据id删除
    public static void delete() throws Exception{
        SqlSession sqlSession = getSqlSession();
        sqlSession.delete("UserMapper.delete",1);
        sqlSession.close();
    }
}

MyBatis接口代理方式实现Dao层

接口代理方式可以让我们只编写接口即可，实现类对象由MyBatis生成

实现规则

映射文件中的名称空间必须和Dao层接口的全类名相同
映射文件中的增删改查标签的id必须和Dao层接口的方法名相同
映射文件中的增删改查标签的parameterType属性必须和Dao层接口方法的参数相同
映射文件中的增删改查标签的resultType属性必须和Dao层接口方法的返回值相同
获取动态代理对象：SqlSession功能类的getMapper()方法

代码

使用的数据

/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.5.40 : Database - test
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

/*Data for the table `user` */

insert  into `user`(`id`,`name`,`age`) values (3,'小李',22),(11,'小莉',12),(12,'小李利',12),(13,'小行星',12),(14,'夏利',12);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

实体类

package cn.cdw.demo.pojo;

/**
 * @author DW-CHEN
 */
public class User {
    private Integer id;
    private String name;
    private Integer age;

    public User() {

    }
    public User(Integer id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

接口

package cn.cdw.demo.mapper;

import cn.cdw.demo.pojo.User;

import java.util.ArrayList;
import java.util.List;

/**
 * @author DW-CHEN
 * MyBatis接口代理方式实现dao层
 */
public interface UserMapper {

    public User findById(Integer id);
}

映射文件

<?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="cn.cdw.demo.mapper.UserMapper">
    <select id="findById" resultType="user" parameterType="int">
        select * from user where  id = #{id};
    </select>

</mapper>

jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=root

核心配置文件

<?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>
    <properties resource="jdbc.properties"/><!--读取配置文件-->

    <settings>
        <setting name="logImpl" value="log4j"/>
    </settings>

    <typeAliases><!--配置别名-->
        <package name="cn.cdw.demo.pojo"/>
    </typeAliases>

    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin><!--分页插件助手-->
    </plugins>

    <environments default="mysql">
        <environment id="mysql"><!--配置环境变量-->
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="UserMapper.xml"/><!--引入映射文件-->
    </mappers>
</configuration>

测试

package cn.cdw.demo.mapper;

import cn.cdw.demo.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

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

/**
 * @author DW-CHEN
 * 测试MyBatis接口代理方式实现dao层，不需要实现类了
 */
public class TestUserMapper {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);//获取动态代理对象
        User user = mapper.findById(2);

        System.out.println(user);
        sqlSession.close();
        resourceAsStream.close();

    }
}

MyBatis映射配置文件-动态SQL

动态SQL标签

sql语句可以根据条件或者参数的不同进行动态的变化

<where>：条件标签
<if>：条件判断标签
<foreach>：循环遍历标签

代码

接口

package cn.cdw.demo.mapper;

import cn.cdw.demo.pojo.User;

import java.util.ArrayList;
import java.util.List;

/**
 * @author DW-CHEN
 * MyBatis接口代理方式实现dao层
 */
public interface UserMapper {

    public User findById(Integer id);

    public User findByCondition(User user);

    public List<User> findByIds(ArrayList<Integer> ids);
}

映射文件

<?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="cn.cdw.demo.mapper.UserMapper">
    <select id="findById" resultType="user" parameterType="int">
        select * from user where  id = #{id};
    </select>

    <select id="findByCondition" resultType="user" parameterType="user">
        select * from user
        <where>
            <if test="id != null">
                id = #{id}
            </if>
            <if test="name != null">
                and name = #{name}
            </if>
            <if test="age != null">
                and age = #{age}
            </if>
        </where>
    </select>

    <select id="findByIds" parameterType="list" resultType="user">
        select * from user
        <where>
            <foreach collection="list" open="id in (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

</mapper>

测试

package cn.cdw.demo.mapper;

import cn.cdw.demo.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

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

/**
 * @author DW-CHEN
 * MyBatis动态sql
 * 根据不同条件进行查询
 */
public class TestUserMapper1 {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);//获取动态代理对象

        User u1 = mapper.findByCondition(new User(2, null, null));
        User u2 = mapper.findByCondition(new User(null, "小小", 12));
        User u3 = mapper.findByCondition(new User(null, null, 23));

        System.out.println(u1);
        System.out.println(u2);
        System.out.println(u3);

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

package cn.cdw.demo.mapper;

import cn.cdw.demo.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @author DW-CHEN
 * 根据多个id进行查询
 */
public class TestUserMapper2 {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);//获取动态代理对象

        ArrayList<Integer> arrayList = new ArrayList<>();
        arrayList.add(2);
        arrayList.add(4);
        arrayList.add(3);

        List<User> arrayListUser = (List<User>) mapper.findByIds(arrayList);

        System.out.println(arrayListUser);

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

SQL片段抽取

我们可以将一些重复性的SQL语句进行抽取，以达到复用的效果
<sql>：抽取sql语句标签
<include>：引入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">

<mapper namespace="cn.cdw.demo.mapper.UserMapper">
    <sql id="query">
        select * from user
    </sql>

    <select id="findAllByPage" resultType="user">
        <include refid="query"/>
    </select>

MyBatis核心配置文件-分页插件PageHelper

PageHelper需要导入的pagehelper-5.1.2.jar，jsqlparser-1.0.jar包,下载地址：https://download.csdn.net/download/qq_42795277/12838332

PageHelper:第三方分页助手，将复杂的分页操作进行封装

分页插件的相关参数

PageInfo:封装分页相关参数的功能类
核心方法

方法名	说明
long getTotal()	获取总条数
int getPages()	获取总页数
int getPageNum()	获取当前页
int getPageSize()	获取每页显示的条数
int getPrePage()	获取上一页
int getNextPage()	获取下一页
boolean islsFirstPage()	获取是否是第一页
boolean islsLastPage()	获取是否是最后一页

代码

接口

package cn.cdw.demo.mapper;

import cn.cdw.demo.pojo.User;

import java.util.ArrayList;
import java.util.List;

/**
 * @author DW-CHEN
 * MyBatis接口代理方式实现dao层
 */
public interface UserMapper {

    public List<User> findAllByPage();

}

映射文件

<?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="cn.cdw.demo.mapper.UserMapper">
    <sql id="query">
        select * from user
    </sql>

    <select id="findAllByPage" resultType="user">
        <include refid="query"/>
    </select>

核心配置文件

<?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>
    <properties resource="jdbc.properties"/><!--读取配置文件-->

    <settings>
        <setting name="logImpl" value="log4j"/>
    </settings>

    <typeAliases><!--配置别名-->
        <package name="cn.cdw.demo.pojo"/>
    </typeAliases>

    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin><!--分页插件助手-->
    </plugins>

    <environments default="mysql">
        <environment id="mysql"><!--配置环境变量-->
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="UserMapper.xml"/><!--引入映射文件-->
    </mappers>
</configuration>

分页信息

package cn.cdw.demo.mapper;

import cn.cdw.demo.pojo.User;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/**
 * @author DW-CHEN
 * PageHelper分页助手
 */
public class TestUserMapper3 {
    public static void main(String[] args) throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("MyBatisConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);

        UserMapper mapper = sqlSession.getMapper(UserMapper.class);//获取动态代理对象

        // PageHelper.startPage(1, 2);//从第一页开始，每页显示2条数据
       PageHelper.startPage(2, 2);//从第二页开始，每页显示2条数据

        List<User> all = mapper.findAllByPage();
        for (User user : all) {
            System.out.println(user);
        }

        PageInfo<User> pageInfo = new PageInfo<>(all);
        System.out.println("总数据：" + pageInfo.getTotal());
        System.out.println("总页数：" + pageInfo.getPages());
        System.out.println("当前页：" + pageInfo.getPageNum());
        System.out.println("每页显示的数据：" + pageInfo.getPageSize());
        System.out.println("上一页：" + pageInfo.getPrePage());
        System.out.println("下一页：" + pageInfo.getNextPage());
        System.out.println("是否时第一页：" + pageInfo.isIsFirstPage());
        System.out.println("是否是最后一页：" + pageInfo.isIsLastPage());

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

MyBatis多表查询

代码参考： git@github.com:chendingwu/MyBatisExample.git

映射配置文件方式

多表模型分类：一对一，一对多，多对多
<resultMap>:配置字段和对象属性的映射关系标签，id属性：唯一标识，type属性：实体对象类型
<id>:配置主键映射关系标签
<result>:配置非主键映射关系标签，column属性：标中字段名称，property属性：实体对象变量名称
<association>:配置被包含对象的映射关系标签，property属性：被包含对象的数据类型，javaType属性：被包含对象的数据类型
<collection>:配置被包含集合对象的映射关系标签，property属性：被包含集合对象的变量名，ofType属性：集合中保存的对象数据类型

注解方式

注意：需要在核心配置文件中配置映射关系

@Results:封装映射关系的父注解，Result[] value():定义了Result数组
@Result:封装了映射关的子注解，column属性：查询出表中字段名称，property属性：实体类对象中的属性名称，javaType属性：被包含对象的数据类型，one属性：一对一查询固定属性，many属性：一对多查询固定属性
@One：一对一查询的注解，select属性：指定调用某个接口中的方法
@Many：一对多查询的注解，select属性：指定调用某个接口中的方法

MyBatis构造SQL语句

MyBatis给我们提供了org.apache.ibatis.jdbc.SQL功能类，专门用于构建SQL语句的功能类，通过一些方法来替代SQL语句的关键字

方法名	说明
SELECT(String… column)	根据字段拼接查询语句
FROM(Sting… table)	根据表名拼接语句
WHERE(String… condition)	根据条件拼接语句
INSERT_INTO(String table)	根据表名拼接新增语句
VALUES(String column,String values)	根据字段和值拼接插入数据语句
UPDATE(String table)	根据表名拼接修改语句
DELETE_FROM(String table)	根据表名拼接删除语句

代码

数据库

/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.5.40 : Database - test
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `test`;

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

/*Data for the table `user` */

insert  into `user`(`id`,`name`,`age`) values (3,'小李',22),(11,'小莉',12),(12,'小李利',12),(13,'小行星',12),(14,'夏利',12);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

实体类

package cn.cdw.demo.pojo;

/**
 * @author DW-CHEN
 */
public class User {
    private Integer id;
    private String name;
    private Integer age;

    public User() {

    }

    public User(Integer id, String name, Integer age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public Integer getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

构建SQL语句

package cn.cdw.demo.sql;

import org.apache.ibatis.jdbc.SQL;

/**
 * @author DW-CHEN
 * 构建SQL语句
 */
public class Demo1 {

    public String select() { //构建查询SQL语句
        SQL sql = new SQL(){
            {
                SELECT("*");
                FROM("user");
            }
        };
        return sql.toString();
    }

    public String insert() {//构建添加sql语句
       SQL sql = new SQL(){
            {
                INSERT_INTO("user");
                VALUES("id,name,age", "#{id},#{name},#{age}");
            }
        };

       return sql.toString();
    }

    public String update() {//构建更新sql语句
        SQL sql = new SQL() {
            {
                UPDATE("user");
                SET("name = #{name},age = #{age}");
                WHERE("id = #{id}");
            }
        };

        return sql.toString();
    }

    public String delete() {//构建删除sql语句
        SQL sql = new SQL() {
            {
                DELETE_FROM("user");
                WHERE("id=#{id}");
            }
        };
        return sql.toString();
    }
}

接口

package cn.cdw.demo.mapper;

import cn.cdw.demo.sql.Demo1;
import cn.cdw.demo.pojo.User;
import org.apache.ibatis.annotations.DeleteProvider;
import org.apache.ibatis.annotations.InsertProvider;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.annotations.UpdateProvider;

import java.util.List;

/**
 * @author DW-CHEN
 * 使用构建SQL语句
 */
public interface UserMapper {

    @SelectProvider(type = Demo1.class,method = "select")
    public List<User> findAll();

    @InsertProvider(type = Demo1.class,method = "insert")
    public void inset(User user);

    @UpdateProvider(type = Demo1.class, method = "update")
    public void update(User user);

    @DeleteProvider(type = Demo1.class, method = "delete")
    public void delete(Integer id);
}

jdbc.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=root

核心配置文件

<?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>
    <properties resource="jdbc.properties"/>

    <environments default="my">
        <environment id="my">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <package name="cn.cdw.demo.mapper"/>
    </mappers>
</configuration>

测试

package cn.cdw.demo.mapper;

import cn.cdw.demo.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.util.List;

/**
 * @author DW-CHEN
 * 测试
 */
public class TestUserMapper {
    public static void main(String[] args) throws IOException {
        findAll();
       // insert();
       // update();
       // delete();

    }

    public static SqlSession getSqlSession(){
         SqlSession sqlSession = null;
        try {
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("MyBatisConfig.xml"));
             sqlSession = sqlSessionFactory.openSession(true);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return sqlSession;
    }

    public static void findAll() {//查询
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> all = mapper.findAll();

        for (User user : all) {
            System.out.println(user);
        }
        sqlSession.close();
    }

    public static void insert() {//添加
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        mapper.inset(new User(null,"夏利",12));
        System.out.println("添加成功....");
        sqlSession.close();
    }

    public static void update() {//更新
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        mapper.update(new User(11, "小莉",12));
        System.out.println("更新成功");
        sqlSession.close();
    }

    public static void delete() {//删除
        SqlSession sqlSession = getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        mapper.delete(2);
        System.out.println("删除成功");
        sqlSession.close();
    }
}