Java持久层框架之Mybatis

前言

框架可以理解为半成品软件，框架做好以后，接下来在它基础上进行开发。

把重复的代码工作抽取出来，让程序员把精力专注在核心的业务代码实现上。

一、Mybatis入门

Mybatis是Java中1个半自动化的ORM框架，Mybatis可以解决程序对数据库操作问题。

Mybatis是一款优秀的持久层框架，它底层封装的是JDBC。

Mybatis通过XML描述符/注解，把实体类的对象与存储过程/SQL语句关联起来。

MyBatis是在Apache许可证2.0下分发的自由软件，是iBATIS 3.0的分支版本。

MyBatis是2001年开始开发的，是“internet”和“abtis(篱笆)”两个单词的组合，其目的是想做互联网的篱笆墙，围绕着数据库提供持久化服务的一个框架。

iBATIS在2004年时捐赠给了Apache软件基金会，2010年更名为MyBatis。

1.入门案例

1.1.数据库表

CREATE TABLE USER(
                     uid INT PRIMARY KEY AUTO_INCREMENT,
                     name VARCHAR(100) NOT NULL,
                     password VARCHAR(50) NOT NULL,
                     email VARCHAR(50),
                     birthday DATE
);

USER.sql

1.2.主配置文件

<?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://192.168.56.18:3306/dbForJava?characterEncoding=utf8"/>
                <property name="username" value="zhanggen"/>
                <property name="password" value="123.com"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--        导入SQL语句配置文件从src/下一层开始-->
        <mapper resource="mappers/UserMapper"/>
    </mappers>
</configuration>

SqlMapConfig.xml

1.3.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">

<!--
namespace（名称空间）：当前xml配置文件的唯一标识
namespace+id：就可以唯一定位到1条SQL语句
-->
<mapper namespace="UserMapper">
    <!--
    本质是一条SQL语句，但是需要我们传入1个User对象
    id:是一个表示在当下xml配置文件中不能重复，通过ID定位唯一1条SQL语句
    -->
    <insert id="save" parameterType="com.zhanggen.domain.User">
       insert into USER values (null,#{name},#{password},#{email},#{birthday})
   </insert>
</mapper>

UserMapper.xml

1.4.实体类

package com.zhanggen.domain;

import java.util.Date;
import java.util.Objects;

public class User {
    //
 /*
     基本数据类型int无法表示数据库中的null，只有引用类型没初始化时null
     uid INT PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(100) NOT NULL,
     password VARCHAR(50) NOT NULL,
     email VARCHAR(50),
     birthday DATE
 */
    private Integer uid;
    private String name;
    private String password;
    private String email;
    private Date birthday;  //使用java.util.Date类型

    public User() {
    }

    public User(Integer uid, String name, String password, String email, Date birthday) {
        this.uid = uid;
        this.name = name;
        this.password = password;
        this.email = email;
        this.birthday = birthday;
    }

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public String getName() {
        return name;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        User user = (User) o;
        return Objects.equals(uid, user.uid) &&
                Objects.equals(name, user.name) &&
                Objects.equals(password, user.password) &&
                Objects.equals(email, user.email) &&
                Objects.equals(birthday, user.birthday);
    }

    @Override
    public int hashCode() {
        return Objects.hash(uid, name, password, email, birthday);
    }
}

User.java

1.5.测试代码

package com.zhanggen.test;

import com.zhanggen.domain.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

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

public class UserTest {
    @Test
    public void testSave() throws IOException {
        //1.准备1个对象
        User user = new User();
        user.setName("张根");
        user.setPassword("123");
        user.setEmail("13220198866@163.com");
        user.setBirthday(new Date());
        //2.读取配置文件，将配置文件读取成流
        InputStream stream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //3.创建1个sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
        //4.获取session
        SqlSession sqlSession = sqlSessionFactory.openSession();//自动开启事务
        //5.使用sqlsesssion完成user对象的保存
       /*
            拼装出1条完整的SQL语句
           参数1:sql语句的位置
          参数2：sql语句中需要传入的参数
        */
        sqlSession.insert("UserMapper.save",user);
        //6.提交事务
        sqlSession.commit();
        //7.释放资源
        sqlSession.close();

    }
}

UserTest.java

1.6.日志配置

日志配置文件放在src目录下

log4j.rootLogger=DEBUG,console,file

#控制台输出的相关设置
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
#log4j.appender.console.layout.ConversionPattern=[%c]-%m%n
log4j.appender.console.layout.ConversionPattern=%5p [%t] - %m%n

#文件输出的相关设置
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/rzk.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n

#日志输出级别
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PreparedStatement=DEBUG

log4j.properties

1.7.API介绍

Resources:将配置文件读取成一个输入流(图纸)

InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");

SqlSessionFactoryBuilder(工人):用于创建SqlSessionFactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

SqlSessionFactory(工厂):用于获取SqlSession
生命周期: 项目创建,他就创建;项目停止,他就销毁
SqlSession sqlSession = sqlSessionFactory.openSession();

SqlSession(工具): 可以操作sql语句
生命周期: 用的时候就创建,用完就销毁
sqlSession.insert("UserMapper.save",user);

二、接口+实现类实现Dao层（传统）

Mybatis是持久层框架，那么我们使用mybatis实现Dao层，让sevice层调用；

1.接口

package com.zhanggen.mapper;

import com.zhanggen.domain.User;

public interface UserMapper {
    void save(User user);

}

UserMapper.interface

2.实现类

package com.zhanggen.mapper.impl;

import com.zhanggen.domain.User;
import com.zhanggen.mapper.UserMapper;
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;

public class UserMapperImpl implements UserMapper {

    @Override
    public void save(User user) {
        try {
            //1.读取配置文件，将配置文件读取成流
            InputStream stream = Resources.getResourceAsStream("SqlMapConfig.xml");
            //2.创建1个sqlSessionFactory
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
            //3.获取session
            SqlSession sqlSession = sqlSessionFactory.openSession();//自动开启事务
            //4.使用sqlsesssion完成user对象的保存
       /*
            拼装出1条完整的SQL语句
           参数1:sql语句的位置
          参数2：sql语句中需要传入的参数
        */
            sqlSession.insert("UserMapper.save", user);
            //5.提交事务
            sqlSession.commit();
            //6.释放资源
            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

UserMapperImpl.java

3.配置(映射)

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

<!--
namespace（名称空间）：当前xml配置文件的唯一标识
namespace+id：就可以唯一定位到1条SQL语句
-->
<mapper namespace="UserMapper">
    <!--
    本质是一条SQL语句，但是需要我们传入1个User对象
    id:是一个表示在当下xml配置文件中不能重复，通过ID定位唯一1条SQL语句
    -->
    <insert id="save" parameterType="com.zhanggen.domain.User">
       insert into USER values (null,#{name},#{password},#{email},#{birthday})
   </insert>
</mapper>

UserMapper.xml

4.测试

使用测试方法模拟service层对dao层进行调用进行dao层的功能测试；

package com.zhanggen.test;

import com.zhanggen.domain.User;
import com.zhanggen.mapper.impl.UserMapperImpl;
import org.junit.Test;

import java.io.IOException;
import java.util.Date;

public class MybatisTest {
    //模拟service层调用dao层对象
    @Test
    public void testSave() throws IOException {
        //1.准备1个对象
        User user = new User();
        user.setName("Martin");
        user.setPassword("123");
        user.setEmail("13220198866@163.com");
        user.setBirthday(new Date());
        //2.创建dao的对象，调用save()方法
        UserMapperImpl userMapper = new UserMapperImpl();
        userMapper.save(user);

    }
}

UserTest.java

三、Mybatis接口代理实现Dao层

以上的方式每次增加1条SQL都需要创建1个实现类对象，并且还要告诉Mybatis这个SQL的配置信息；

约定大于编程：框架和框架使用者之间，能通过xml配置约定出来的，都可以由框架去实现，无需框架使用者重复编程实现，

通过xml配置，可以让Mybatis框架，帮助我们创建1个接口的实现类对象；

1.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">

<!--
namespace（名称空间）：当前xml配置文件的唯一标识
namespace+id：就可以唯一定位到1条SQL语句
-->
<mapper namespace="com.zhanggen.mapper.UserMapper">
    <!--
    本质是一条SQL语句，但是需要我们传入1个User对象
    id:是一个表示在当下xml配置文件中不能重复，通过ID定位唯一1条SQL语句
    -->
    <insert id="save" parameterType="com.zhanggen.mapper.UserMapper">
       insert into USER values (null,#{name},#{password},#{email},#{birthday})
   </insert>
</mapper>

UserMapper.xml

2.约定不满足报错

org.apache.ibatis.binding.BindingException: Type interface com.zhanggen.mapper.UserMapper is not known to the MapperRegistry.

3.测试

package com.zhanggen.test;

import com.zhanggen.domain.User;
import com.zhanggen.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

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

public class MybatisTest {
    //模拟service层调用dao层对象
    @Test
    public void testSave() throws IOException {
        //1.准备1个对象
        User user = new User();
        user.setName("Martin");
        user.setPassword("123");
        user.setEmail("13220198866@163.com");
        user.setBirthday(new Date());
        //2.创建dao的对象，调用save()方法
//        UserMapperImpl userMapper = new UserMapperImpl();
//        userMapper.save(user);
        //2.使用接口代理的方式获取UserMapper接口的实现类对象
        //1.读取配置文件，将配置文件读取成流
        InputStream stream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.创建1个sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
        //3.获取session
        SqlSession sqlSession = sqlSessionFactory.openSession();//自动开启事务
        //4.
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.save(user);
        //
        sqlSession.commit();
        sqlSession.close();


    }
}

MybatisTest.java

4.增删改案例

至此使用mybatis操作数据库的步骤为接口+映射+测试；

4.1.接口

在接口中声明dao层的操作方法；

package com.zhanggen.mapper;

import com.zhanggen.domain.User;

public interface UserMapper {
    void save(User user);
    void update(User user);
    void delete(Integer uid);

}

UserMapper.interface

4.2.映射

在映射文件中定义接口方法对应的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">

<!--
namespace（名称空间）：当前xml配置文件的唯一标识
namespace+id：就可以唯一定位到1条SQL语句
-->
<mapper namespace="com.zhanggen.mapper.UserMapper">
    <!--
    本质是一条SQL语句，但是需要我们传入1个User对象
    id:是一个表示在当下xml配置文件中不能重复，通过ID定位唯一1条SQL语句
    -->
    <insert id="save" parameterType="com.zhanggen.mapper.UserMapper">
       insert into USER values (null,#{name},#{password},#{email},#{birthday});
   </insert>
    <update id="update" parameterType="com.zhanggen.mapper.UserMapper">
     update USER set name=#{name},password=#{password},email = #{email},birthday = #{birthday} where uid = #{uid};
    </update>
    <update id="delete" parameterType="java.lang.Integer">
     delete from USER  where uid = #{uid};
    </update>
</mapper>

UserMapper.xml

4.3.测试

通过Mybatis创建接口的实现类对象，通过实现类对象，调用接口中声明的方法。

package com.zhanggen.test;

import com.zhanggen.domain.User;
import com.zhanggen.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

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

public class MybatisTest {
    //模拟service层调用dao层对象
    @Test
    public void testSave() throws IOException {
        //1.准备1个对象
        User user = new User();
        user.setName("Martin");
        user.setPassword("123");
        user.setEmail("13220198866@163.com");
        user.setBirthday(new Date());
        //2.创建dao的对象，调用save()方法
//        UserMapperImpl userMapper = new UserMapperImpl();
//        userMapper.save(user);
        //2.使用接口代理的方式获取UserMapper接口的实现类对象
        //1.读取配置文件，将配置文件读取成流
        InputStream stream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.创建1个sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
        //3.获取session
        SqlSession sqlSession = sqlSessionFactory.openSession();//自动开启事务
        //4.
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.save(user);
        //
        sqlSession.commit();
        sqlSession.close();


    }
    @Test
    public void testUpdate() throws IOException {
        //1.准备1个对象
        User user = new User();
        user.setUid(11);
        user.setName("Martin666");
        user.setPassword("123");
        user.setEmail("13220198866@163.com");
        user.setBirthday(new Date());
        //2.创建dao的对象，调用save()方法
//        UserMapperImpl userMapper = new UserMapperImpl();
//        userMapper.save(user);
        //2.使用接口代理的方式获取UserMapper接口的实现类对象
        //1.读取配置文件，将配置文件读取成流
        InputStream stream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.创建1个sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
        //3.获取session
        SqlSession sqlSession = sqlSessionFactory.openSession();//自动开启事务
        //4.
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.update(user);
        //
        sqlSession.commit();
        sqlSession.close();


    }
    @Test
    public void testDelete() throws IOException {
        //1.读取配置文件，将配置文件读取成流
        InputStream stream = Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.创建1个sqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
        //3.获取session
        SqlSession sqlSession = sqlSessionFactory.openSession();//自动开启事务
        //4.
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.delete(11);
        //
        sqlSession.commit();
        sqlSession.close();


    }
}

MybatisTest.java

5.抽取工具类

避免每次执行SQL都重复创建sqlSessionFactory。

避免每次执行SQL前都创建SqlSession对象。

避免每次执行完SQL都关闭SqlSession对象。

5.1.工具类

抽取sqlSessionFactory和sqlSession创建过程为工具类

package com.zhanggen.util;

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;

public class MybatisUtil {
    private static SqlSessionFactory sqlSessionFactory = null;
    private static SqlSession sqlSession = null;

    static {
        try {
            //1.读取配置文件，将配置文件读取成流
            InputStream stream = Resources.getResourceAsStream("SqlMapConfig.xml");
            //2.创建1个sqlSessionFactory
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(stream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession() {
        sqlSession = sqlSessionFactory.openSession();
        return sqlSession;
    }

    public static void commitAndclose() {
        if (sqlSession != null) {
            sqlSession.commit();
            sqlSession.close();
        }
    }
}

MybatisUtil.java

5.2.测试工具类

package com.zhanggen.test;

import com.zhanggen.domain.User;
import com.zhanggen.mapper.UserMapper;
import com.zhanggen.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

//抽取sqlSessionFactory和sqlSession创建过程为工具类
public class MyBatisUtilTest {
    private SqlSession sqlSession = null;

    @Before
    public void beforTest() {
        sqlSession = MybatisUtil.getSqlSession();

    }

    @After
    public void afterTest() {
        MybatisUtil.commitAndclose();
    }


    @Test
    public void testSave() {
        //1.准备1个对象
        User user = new User();
        user.setUid(16);
        user.setName("Martin666");
        user.setPassword("123");
        user.setEmail("13220198866@163.com");
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.save(user);


    }

    @Test
    public void testUpdate() {
        //1.准备1个对象
        User user = new User();
        user.setUid(14);
        user.setName("Martin");
        user.setPassword("123");
        user.setEmail("13220198866@163.com");
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        sqlSession.getMapper(UserMapper.class);
        userMapper.update(user);


    }

}

MyBatisUtilTest.java

四、Mybatis静态查询

使用Mybatis持久层框架进行数据库查询；

1.resultType查询多条记录

当数据库返回的结果集中的字段和实体类中的属性名一一对应时, 使用resultType可以自动将结果封装到实体中。

1.1.接口

   //查询所有
    List<User> queryAll();

1.2.映射

    <!--List<User> queryAll();-->
    <!-- resultType只写方法返回值类型中的实体类型-->
    <select id="queryAll" resultType="com.zhanggen.domain.User">
        select * from USER;
    </select>

1.3.测试

        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.queryAll();
        for (User user : userList) {
            System.out.println(user);
        }

2.resultMap查询多条记录

当数据库返回的结果集中的字段和实体类中的属性名不一一对应的情况时；

我们可以在MyBatis的xml配置文件中自定义1种resultMap数据类型，来指定MyBatis返回指定的结果集。

2.1.接口

注意resultMap返回的是1个List<对象>而不是List<Map<String,Object>>；

 //查询所有（数据库返回的结果集和实体类属性名称不一一对应的情况）
    List<User> queryAll2();

2.2.映射

resultMap标签：用于自定义数据库返回的结果集合实体类属性名称的映射规则（手动指定）
id属性：指定当前规则的1个标识，要求唯一，以便调用；
type属性：指定数据库返回的结果集要映射到哪个实体类的属性上
result子标签： 1个result指1条具体的规则，column指定数据库字段名称 property指定实体类属性名称

    <resultMap id="userMap" type="com.zhanggen.domain.User">
        <result column="uid" property="uid"></result>
        <result column="name" property="userName"></result>
        <result column="password" property="password"></result>
        <result column="email" property="email"></result>
        <result column="birthday" property="birthday"></result>
    </resultMap>
<!-- select标签通过resultMap="userMap"属性，指定这次查询使用id="userMap"的规则进行结果解析-->
    <select id="queryAll2" resultMap="userMap">
        select uid,name as userName,password,email,birthday from USER;
    </select>

2.3.测试

   @Test
    public void testQueryAll2() {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.queryAll2();
        for (User user : userList) {
            System.out.println(user);
        }
    }

3.单条件查询1条记录

如果需要通过1个where条件进行数据库查询，返回单条数据库记录。

可以直接把这1个参数，传给映射文件，在映射配置中使用形参名称接收这1个参数。

   //根据主键查询
    User queryByID(int id);

并非结果集合

  List<User> queryAll2();

3.1.接口

    //根据主键查询
    User queryByUid(Integer uid);

3.2.映射

    <!--    单条件查询-->
    <select id="queryByUid" parameterType="java.lang.Integer" resultType="com.zhanggen.domain.User">
        select uid,name as userName,password,email,birthday from USER where uid=#{uid};
    </select>

3.3.测试

  @Test
    public void testQueryByID() {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.queryByUid(17);
        System.out.println(user);

    }

4.多条件查询

如果需要通过多个where条件进行数据库查询，那么当前接口的方法中就需要包含多个参数。

如何把多个参数，传给映射文件？

4.1.按位置+注解名称传参

1.接口

List<User> querybyUserAndPassword(@Param("user") String username,@Param("pwd")String password);

2.映射

 <select id="querybyUserAndPassword" resultType="com.zhanggen.domain.User">
        <!-- 方法有多个参数方式1 -->
        <!-- select * from USER where name= #{arg0} and password=#{arg1}; -->
        <!-- select * from USER where name= #{param1} and password=#{param2}; -->
        <!-- 方法有多个参数方式2 使用注解 -->
        select * from USER where name= #{user} and password=#{pwd};
    </select>

3.测试

@Test
    public void testQuerybyUserAndPassword() {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.querybyUserAndPassword("Martin", "123");
        for (User user : userList) {
            System.out.println(user);
        }

    }

4.2.所有参数封装到1个对象

1.接口

 List<User> querybyUserAndPassword2(User user );

2.映射

    <!-- 方法有多个参数方式3：参数太多，把多参数封装到1个对象中-->
    <select id="querybyUserAndPassword2" resultType="com.zhanggen.domain.User">
        select * from USER where name= #{name} and password=#{password};
    </select>

3.测试

   User userParam = new User();
        userParam.setUid(17);
        userParam.setName("Martin");
        userParam.setPassword("123");
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.querybyUserAndPassword2(userParam);
        for (User user : userList) {
            System.out.println(user);
        }

    }

5.模糊查询

在Mybatis可以通过2种方式进行like模糊匹配查询；

concat("%",#{name},"%")
"% ${value}%" 有SQL注入风险；

1.接口

   //模糊查询
    List<User> queryLikeName(String name);

2.映射

   <!-- 模糊查询-->
    <select id="queryLikeName" resultType="com.zhanggen.domain.User">
        select * from USER where name like concat("%",#{name},"%");
    </select>

方式2有SQL注入风险；

  <select id="queryLikeName1" resultType="com.zhanggen.domain.User">
        select * from USER where name like "% ${value}%";
    </select>

3.测试

    //模糊查询
    @Test
    public void queryLikeName(){
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.queryLikeName("M");
        for (User user : userList) {
            System.out.println(user);
        }
    }

6.查看新增记录主键ID

向数据库新增1条记录之后 , 如何获取到新增记录的主键id呢？

1.接口

    //保存
    void save(User user);

2.映射

    <!--
    useGeneratedKeys="true"：告诉Mybatis，我要使用刚刚新增记录的主键
    keyProperty="uid"      ：告诉Mybatis，把新增记录的主键 封装到指定属性
     -->
    <insert id="save" useGeneratedKeys="true" keyProperty="uid">
        insert into USER values(null,#{name},#{password},#{email},#{birthday});
    </insert>

3.测试

        User user = new User();
        user.setName("张翠山");
        user.setPassword("17822");
        user.setEmail("wudangshan@163.com");
        user.setBirthday(new Date());
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.save(user);
        //查看新增记录的 uid
        System.out.println(user.getUid());

7.MyBatis返回List<Map<String,Object>>

如果MyBatis每次查询都需要先在Java中创建1个实体类，然后去MySQL中执行查询操作，MyBatis根据实体类的属性进行数据的灌入实例类的对象；

其实有些查询不需要创建实体类，可以把MyBatis查询到的结果封装到1个List<Map<String, Object>>类型中返回；

7.1.Controller层

 //测试返回前端List嵌套map类型数据
    @GetMapping("/queryAll")
    public List<Map<String,Object>> queryAllInList(){
        List<Map<String,Object>> res= reviewService.queryAllInList();
        return res;
    }
    //测试返回前端map类型数据
    @GetMapping("/queryOne")
    public Map<String,Object> selectOne() {
        Map<String,Object> res= reviewService.queryOneInMap();
        return res;
    }
    //测试返回前端数字类型数据
    @GetMapping("/total")
    public Long selectNumber() {
        Long res= reviewService.queryTotal();
        return res;
    }

7.2.Service层

接口

    //测试返回List<Map>数据
    List<Map<String, Object>> queryAllInList();
    //测试返回Map数据
    Map<String, Object> queryOneInMap();
    //测试返回数字数据
    Long queryTotal();

实现类

  //测试返回 List<Map>数据
    @Override
    public List<Map<String, Object>> queryAllInList() {
        List<Map<String, Object>> res = reviewMapper.getListMaping();
        return res;
    }

    //测试返回 Map数据
    @Override
    public Map<String, Object> queryOneInMap() {
        Map<String, Object> res = reviewMapper.getMaping();
        return res;
    }
    //测试返回数字类型数据数据
    @Override
    public Long queryTotal() {
        Long number= reviewMapper.getTotalNumber();
        return number;
    }

7.3.Mapper层

    <!--测试返回 List<Map>数据-->
    <select id="getListMaping" resultType="java.util.Map">
        select * from mybatis_review
    </select>
    <!--测试返回Map-->
    <select id="getMaping" resultType="java.util.Map">
        select * from mybatis_review where id=1;
    </select>
    <!--测试返回数字数据-->
    <select id="getTotalNumber" resultType="java.lang.Long">
         select count(1) as number from mybatis_review;
    </select>

五、Mybatis动态查询

类似于Django的Q查询功能，Mybatis也可以根据前端用户输入的搜索条件，在后端动态组装where查询条件；

我们可以在映射配置文件中使用where标签，进行动态条件判断，完成多个where条件的动态组装。

1.if标签

接口

 List<User> dynamicQuery1(User user);

映射

 <!--if标签-->
    <select id="dynamicQuery1" resultType="com.zhanggen.domain.User">
        select * from USER
        <where>
            <if test="name != null and name !=''">
                and name=#{name}
            </if>
            <if test="email != null and email !=''">
                and email=#{email}
            </if>
        </where>

    </select>

测试

        User dynamicQueryUser = new User();
        dynamicQueryUser.setName("张翠山");
        dynamicQueryUser.setEmail("wudangshan@163.com");
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> usersList = userMapper.dynamicQuery1(dynamicQueryUser);
        System.out.println("--------------------");
        for (User user : usersList) {
            System.out.println(user);
        }
        System.out.println("--------------------");

2.choose标签

choose 用于包含多个条件
when 用于编写某个条件
otherwise所有的when都判断失败时，进入此分支

接口

    <!--  choose标签-->
    <select id="dynamicQuery2" resultType="com.zhanggen.domain.User">
        select * from USER
        <where>
            <choose>
                <when test="name !=null and name !=''">
                    and name=#{name}
                </when>
                <when test="email != null and email !=''">
                    and email=#{email}
                </when>
                <otherwise>
                    and false
                </otherwise>
            </choose>
        </where>

    </select>

映射

List<User> dynamicQuery2(User user);

测试

 //测试 choose when标签
    @Test
    public void dynamicQuery2() {
        User dynamicQueryUser = new User();
        dynamicQueryUser.setName("张翠山");
        dynamicQueryUser.setEmail("wudangshan@163.com");
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> usersList = userMapper.dynamicQuery2(dynamicQueryUser);
        System.out.println("--------------------");
        for (User user : usersList) {
            System.out.println(user);
        }
        System.out.println("--------------------");

    }

3.set更新标签

除了动态组装多个查询SQL也可以动态组装多个修改的SQL；

根据主键id找到1条记录 ,把1个实体类对象中不为空的属性更新到1条记录中；

1.接口

    //修改：根据主键id,动态更新1个user对象中不为空的属性
    void dynamicUpdate(User user);

2.映射

<!--  set标签  -->
    <update id="dynamicUpdate" parameterType="com.zhanggen.domain.User">
        update USER
        <set>
            <if test="name != null and name !=''">
                name=#{name},
            </if>
            <if test="email != null and email !=''">
                email=#{email},
            </if>
            <if test="birthday != null">
                birthday=#{birthday},
            </if>
        </set>
        where uid=#{uid};
    </update>

3.测试

 // 测试set标签
    @Test
    public void testdynamicUpdate() {
        User dynamicUpdateUser = new User();
        dynamicUpdateUser.setUid(17);
        dynamicUpdateUser.setName("张韬");
        dynamicUpdateUser.setEmail("zhangtao@zhang.com");
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.dynamicUpdate(dynamicUpdateUser);
        System.out.println("--------------------");
        System.out.println(dynamicUpdateUser);
        System.out.println("--------------------");
    }

4.foreach标签

foreach标签：用于在映射配置文件中，循环遍历接口方法传入的集合/数组/对象参数；

collection属性：collection属性配置和接口方法传入参数的数据类型有关

接口方法传入的参数是集合类型 ===== collection
接口方法传入的参数是数组类型 ===== array
接口方法传入的参数是对象 ===== 对象的属性名称

item属性：指定临时变量

separator属性：指定分隔符

open属性：指定开始字符

close属性：指定结束字符

1.接口

    //根据uids集合( 多个uid )查询 一个对象的集合
    List<User> dynamicQueryInList(List<Integer> idList);

    //根据uids数组( 多个uid )查询 一个对象的集合
    List<User> dynamicQueryInList1(Integer[] idList);

2.映射

 <!-- foreach标签-->
    <select id="dynamicQueryInList" resultType="com.zhanggen.domain.User">
        <!--循环遍历拼接in中的字符串 (17,19,22)-->
        select * from USER where uid in
        <foreach collection="collection" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </select>

<!-- 传入数组参数（Integer[]）-->
    <select id="dynamicQueryInList1" resultType="com.zhanggen.domain.User">
        <!--循环遍历拼接in中的字符串 (17,19,22)-->
        select * from USER where uid in
        <foreach collection="array" item="item" separator="," open="(" close=")">
            #{item}
        </foreach>
    </select>

3.测试

  // 测试foreach标签传入1个集合参数
    @Test
    public void testDynamicQueryInList() {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        ArrayList<Integer> idList = new ArrayList<>();
        idList.add(18);
        idList.add(19);
        idList.add(22);
        //传入1个集合参数
        List<User> usersList = userMapper.dynamicQueryInList(idList);
        System.out.println("--------------------");
        for (User user : usersList) {
            System.out.println(user);
        }
        System.out.println("--------------------");
    }

    // 测试foreach标签传入数组
    @Test
    public void testDynamicQueryInList1() {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        Integer[] idList = new Integer[]{18, 19, 22};
        //传入1个数组参数
        List<User> usersList = userMapper.dynamicQueryInList1(idList);
        System.out.println("--------------------");
        for (User user : usersList) {
            System.out.println(user);
        }
        System.out.println("--------------------");
    }

六、Mybatis主配置文件

1.接口和映射文件在同1个包下

2.Mybatis主配置文件功能

Mybatis主配置文件(SqlMapConfig.xml)功能如下：

配置Mybatis的工作环境
Mybatis连接的数据库信息
配置映射配置文件和映射接口所在的包

<?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标签：在当xml中导入1个properties配置文件，用于简化xml配置
     properties配置文件一定放在src/目录下
     -->
    <properties resource="mybatis.properties"/>

    <!-- 给映射文件中实体类型起别名别名：建议不使用别名，使用复制粘贴的方式，不容易出错-->
    <typeAliases>
        <!-- 方式1：给单个类起别名-->
        <!--        <typeAlias type="com.zhanggen.domain.User" alias="user"/>-->
        <!-- 式2：给1个包下的所有类起别命名，默认类名称小写-->
        <package name="com.zhanggen.domain"></package>
    </typeAliases>

    <!--    指定数据库环境:development环境 还是test环境 -->
    <environments default="development">
        <!--environment：开发(development)环境-->
        <environment id="development">
            <!--事务管理器：管理事务的提交和回滚-->
            <transactionManager type="JDBC"/>
            <!-- dataSource：使用数据库连接池，POOLED是mybatis自动的数据库连接池-->
            <dataSource type="POOLED">
                <!--  配置数据源的连接信息-->
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.development.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
        <!-- environment：测试（test）环境-->
        <environment id="test">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--  配置test环境下，数据库连接信息-->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="${dbc.test.url}"/>
                <property name="username" value="test"/>
                <property name="password" value="183.com"/>
            </dataSource>
        </environment>
    </environments>
    <!--    配置主配置文件如何找到映射文件-->
    <mappers>

        <!-- 方式1：配置映射文件的目录-->
        <!--<mapper resource="mapper/UserMapper"/>-->
        <!--方式2：配置UserMapper接口类名称-->
        <!-- <mapper class="com.zhanggen.mapper.UserMapper"/>-->
        <!-- 方式3（推荐）:配置UserMapper接口和UserMapper.xml所在的包名-->
        <package name="com.zhanggen.mapper"></package>
    </mappers>
</configuration>

SqlMapConfig.xml

七、Mybatis注解开发

使用注解其实是对XML配置的一种替代方式，2者实现的功能一致。

1.创建工程

2.注解实现增删改查

使用注解替代映射文件；

2.1.接口+注解

package com.itheima.mapper;

import com.itheima.domain.User;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface UserMapper {
    //增加
    @Insert("insert into USER values (null, #{name},#{password},#{email},#{birthday});")
    //返回主键id
    @Options(useGeneratedKeys = true, keyProperty = "uid", keyColumn = "uid")
    void insert(User user);

    //查询全部
    @Select("select * from USER;")
    List<User> queryAll();

    //主键查询
    @Select("select * from USER where uid=#{uid};")
    User queryByid(Integer uid);

    //删除
    @Delete("delete from USER where uid=#{uid};")
    void deleteByid(Integer uid);

    //模糊查询
    @Select("select * from USER where name like concat('%',#{name},'%')")
    List<User> queryLikeName(String name);


}

UserMapper.interface

2.2.测试

package com.itheima.test;

import com.itheima.domain.User;
import com.itheima.mapper.UserMapper;
import com.itheima.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.util.Date;
import java.util.List;

//动态SQL
public class MyBatisUtilTest {
    private SqlSession sqlSession = null;

    @Before
    public void beforTest() {
        sqlSession = MybatisUtil.getSqlSession();

    }

    @After
    public void afterTest() {
        MybatisUtil.commitAndclose();
    }

    //新增
    @Test
    public void testInsert() {
        User user = new User();
//        user.setName("张丹枫");
        user.setName("张根");
        user.setPassword("98jhd1");
        user.setEmail("zhangdanfeng@163.com");
        user.setBirthday(new Date());
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.insert(user);
        System.out.println(user);
    }

    //查询所有
    @Test
    public void testQueryAll() {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.queryAll();
        for (User user : userList) {
            System.out.println(user);
        }

    }

    //主键查询
    @Test
    public void testQueryByid() {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.queryByid(18);
        System.out.println(user);

    }

    //根据主键删除
    @Test
    public void testDeleteByid() {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        userMapper.deleteByid(24);
    }

    //模糊查询
    @Test
    public void testQueryLikeName() {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.queryLikeName("张");
        for (User user : userList) {
            System.out.println(user);
        }
    }
}

MyBatisUtilTest.java

3.结果集映射到实体类

如果数据库返回的结果集字段和实体类的属性名称不一致，可以使用@ResultMap("userMap")注解手动配置映射规则；

3.1.接口+注解

   //查询所有1：结果集和实体类属性名称一一对应,自动完成映射。（默认）
    @Select("select * from USER;")
    List<User> queryAll();

    //查询所有2:  数据库返回的结果集和实体类的属性名称（通过注解手动完成映射）
    @Results(
            id = "userMap", //当前结果集映射规则的名称
            value = {
                    @Result(column = "uid", property = "uid", id = true),
                    @Result(column = "username", property = "name"),
                    @Result(column = "password", property = "password"),
                    @Result(column = "email", property = "email"),
                    @Result(column = "birthday", property = "birthday")
            }
    )
    @Select("select uid,name as username,password,email,birthday from USER;")
    List<User> queryAll1();

    //查询所有3: 复用ResultMap(映射规则
    @ResultMap("userMap")
    @Select("select uid,name as username,password,email,birthday from USER;")
    List<User> queryAll2();

UserMapper.interface

3.2.测试

    //查询所有1: 数据库返回的结果集和实体类的属性名称一一对应（默认）
    @Test
    public void testQueryAll() {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.queryAll();
        for (User user : userList) {
            System.out.println(user);
        }

    }


    // 查询所有2:  数据库返回的结果集和实体类的属性名称（通过注解手动指定）
    @Test
    public void testQueryAll1() {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.queryAll1();
        for (User user : userList) {
            System.out.println(user);
        }

    }

    //查询所有3: 复用ResultMap(映射规则
    @Test
    public void testQueryAl2() {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.queryAll2();
        for (User user : userList) {
            System.out.println(user);
        }

    }

MyBatisUtilTest.java

4.注解实现动态SQL

注解中可以使用动态sql么？可以，当然可以。

@Update("<script>" +
            "update tb_person " +
            "<set>" +
            "<if test='name!=null'>" +
            "name=#{name}," +
            "</if>" +
            "<if test='age!=null'>" +
            "age=#{age}," +
            "</if>" +
            "<if test='sex!=null'>" +
            "sex=#{sex}" +
            "</if>" +
            "</set>" +
            "where id=#{id}" +
            "</script>")
    void updatePerson2(Person person)

简直是手撕sql，但凡有一个符号写错了，直接报错，还不如用映射文件呢。

八、Mybatis分页插件（PageHelper）

对数据库中数据进行分页是1个Web应用的核心功能；

后台分页的公式：

Mybatis自带了1个分页插件PageHelper，可以帮助程序员完成分页功能。

1.自实现分页功能

分页的核心其实就是通过limit进行数据库查询，自己实现分页功能，可以帮我们充分理解分页的原理。

先自己创建1个PageInfo对象并封装数据，之后再更换PageHelper的com.github.pagehelper.PageInfo对象。

1.1.创建项目

1.2.web层

-----------------------------

package com.zhanggen.web.filter;

import javax.servlet.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

public class CodingFilter implements Filter {
    @Override
    public void init(FilterConfig filterConfig) throws ServletException {

    }

    // 设置编码   
    @Override
    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
        HttpServletRequest request = (HttpServletRequest) servletRequest;
        HttpServletResponse response = (HttpServletResponse) servletResponse;
        request.setCharacterEncoding("utf-8");
        filterChain.doFilter(servletRequest, servletResponse);
        response.setContentType("text/html;charset=UTF-8 ");
    }

    @Override
    public void destroy() {

    }
}

CodingFilter.java

-----------------------------

package com.zhanggen.web.servlet;

import com.zhanggen.domain.PageInfo;
import com.zhanggen.domain.User;
import com.zhanggen.service.UserService;
import com.zhanggen.service.impl.UserServiceImpl;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;

@WebServlet("/userServlet")
public class UserServlet extends HttpServlet {
    private UserService userService = new UserServiceImpl();

    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //接收action的值
        String action = req.getParameter("action");
        if ("findAll".equals(action)) {
            findAll(req, resp);
        } else if ("findByPage".equals(action)) {
            findByPage(req, resp);
        } else {
            resp.getWriter().write("请求错误");
        }
    }

    //查询所有不分页
    private void findAll(HttpServletRequest req, HttpServletResponse resp) {

    }

    //  查询所有分页
    private void findByPage(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //1.接收页码信息
        String pageNumStr = req.getParameter("pageNum"); //前端当前在第几页
        String pageSizeStr = req.getParameter("pageSize");
        //2.设置默认值
        if (pageNumStr == null || "".equals(pageNumStr)) {
            pageNumStr = "1";
        }
        if (pageSizeStr == null || "".equals(pageSizeStr)) {
            pageSizeStr = "5";
        }
        //3.参数类型装转换
        Integer pageNum = Integer.parseInt(pageNumStr);
        Integer pageSize = Integer.parseInt(pageSizeStr);
        //4.调用service层查询
        PageInfo<User> pageInfo = userService.queryByPage(pageNum, pageSize);
        //结果放入request域对象
        req.setAttribute("pageInfo", pageInfo);
        //页码转发到list2.jsp
        req.getRequestDispatcher("/list2.jsp").forward(req, resp);
    }
}

UserServlet.java

1.3.service层

--------------------------------------

接口

package com.zhanggen.service;

import com.zhanggen.domain.PageInfo;
import com.zhanggen.domain.User;

public  interface UserService  {
    PageInfo<User> queryByPage(Integer pageNum, Integer pageSize);
}

UserService.java

实现类

package com.zhanggen.service.impl;

import com.zhanggen.domain.PageInfo;
import com.zhanggen.domain.User;
import com.zhanggen.mapper.UserMapper;
import com.zhanggen.service.UserService;
import com.zhanggen.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class UserServiceImpl implements UserService {
    @Override
    public PageInfo<User> queryByPage(Integer pageNum, Integer pageSize) {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        PageInfo<User> pageInfo = new PageInfo<>();
        UserMapper usermapper = sqlSession.getMapper(UserMapper.class);
        //totalEntry：数据库里的记录一共多少行
        Integer totalEntry = usermapper.queryToal();
        pageInfo.setTotal(totalEntry);
        //pages：数据库里一共多少条记录，前端总共需要分成多少页进行显示
        Integer pages = totalEntry % pageSize == 0 ? totalEntry / pageSize : (totalEntry / pageSize) + 1;
        pageInfo.setPages(pages);
        //startIndex：当前这1页数据在数据库中起始索引位置
        Integer startIndex = (pageNum - 1) * pageSize;
        //Mybatis获取数据库数据
        List<User> userList = usermapper.queryAllByPage(startIndex, pageSize);
        pageInfo.setList(userList);
        //pageSize：一页包含多少条记录，后端根据前端参数，设置每1页显示多少条记录
        pageInfo.setPageSize(pageSize);
        //pageNum：当前页的页码
        pageInfo.setPageNum(pageNum);
        MybatisUtil.commitAndclose();
        return pageInfo;
    }
}

UserService.java

1.4.mapper（dao）层

package com.zhanggen.mapper;

import com.zhanggen.domain.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface UserMapper {
    //计算数据库总共多少条记录
    @Select("select count(1) from USER;")
    Integer queryToal();

    // 分页查询
    @Select("select * from USER limit #{startIndex},#{pageSize};")
    List<User> queryAllByPage(@Param("startIndex") Integer startIndex,@Param("pageSize") Integer pageSize);
}

UserMapper.java

1.5.domain

自己创建的PageInfo对象。

package com.zhanggen.domain;

import java.util.List;

public class PageInfo<T> {
    private Integer total; //总记录条目
    private Integer pages;  //总页数
    private List<T> list;  //当前页数据
    private Integer pageNum;//当前页码
    private Integer pageSize; //控制每页显示5条记录

    public PageInfo() {
    }

    public PageInfo(Integer total, Integer pages, List<T> list, Integer pageNum, Integer pageSize) {
        this.total = total;
        this.pages = pages;
        this.list = list;
        this.pageNum = pageNum;
        this.pageSize = pageSize;
    }

    public Integer getTotal() {
        return total;
    }

    public void setTotal(Integer total) {
        this.total = total;
    }

    public Integer getPages() {
        return pages;
    }

    public void setPages(Integer pages) {
        this.pages = pages;
    }

    public List<T> getList() {
        return list;
    }

    public void setList(List<T> list) {
        this.list = list;
    }

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

PageInfo.java

--------------------------

package com.zhanggen.domain;

import java.util.Date;
import java.util.Objects;

public class User {
    //
 /*
     基本数据类型int无法表示数据库中的null，只有引用类型没初始化时null
     uid INT PRIMARY KEY AUTO_INCREMENT,
     name VARCHAR(100) NOT NULL,
     password VARCHAR(50) NOT NULL,
     email VARCHAR(50),
     birthday DATE
 */
    private Integer uid;
    private String name;
    private String password;
    private String email;
    private Date birthday;  //使用java.util.Date类型

    public User() {
    }

    public User(Integer uid, String name, String password, String email, Date birthday) {
        this.uid = uid;
        this.name = name;
        this.password = password;
        this.email = email;
        this.birthday = birthday;
    }

    public Integer getUid() {
        return uid;
    }

    public void setUid(Integer uid) {
        this.uid = uid;
    }

    public String getName() {
        return name;
    }

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

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        User user = (User) o;
        return Objects.equals(uid, user.uid) &&
                Objects.equals(name, user.name) &&
                Objects.equals(password, user.password) &&
                Objects.equals(email, user.email) &&
                Objects.equals(birthday, user.birthday);
    }

    @Override
    public int hashCode() {
        return Objects.hash(uid, name, password, email, birthday);
    }

    @Override
    public String toString() {
        return "User{" +
                "uid=" + uid +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", email='" + email + '\'' +
                ", birthday=" + birthday +
                '}';
    }
}

User.java

1.6.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
    <title>Title</title>
    <style>
        table {
            margin: 30px auto;
            text-align: center;
        }

        #page td {
            width: 20px;
            border: 1px solid gray;
        }
    </style>
</head>
<body>
<table border="1" cellpadding="0" cellspacing="0" width="600px">
    <tr>
        <td>编号</td>
        <td>姓名</td>
        <td>密码</td>
        <td>邮箱</td>
        <td>生日</td>
    </tr>

    <c:forEach items="${pageInfo.list}" var="emp">
        <tr>
            <td>${emp.uid }</td>
            <td>${emp.name }</td>
            <td>${emp.password }</td>
            <td>${emp.email }</td>
            <td>${emp.birthday }</td>
        </tr>
    </c:forEach>

</table>
<table>
    <tr>
        <td style="text-align: left">总共检索到${pageInfo.total}条记录,共分${pageInfo.pages}页</td>
    </tr>
</table>
<table id="page">
    <tr>
        <c:if test="${pageInfo.pageNum>1}">
            <td style="width:50px">
                <a href="${pageContext.request.contextPath}/userServlet?action=findByPage&pageNum=${pageInfo.pageNum-1}&pageSize=${pageInfo.pageSize}">
                    上一页
                </a>
            </td>
        </c:if>

        <c:forEach begin="1" end="${pageInfo.pages}" var="page">
            <td>
                <a href="${pageContext.request.contextPath}/userServlet?action=findByPage&pageNum=${page}&pageSize=${pageInfo.pageSize}">
                        ${page}
                </a>
            </td>
        </c:forEach>

        <c:if test="${pageInfo.pageNum<pageInfo.pages}">
            <td style="width:50px">
                <a href="${pageContext.request.contextPath}/userServlet?action=findByPage&pageNum=${pageInfo.pageNum+1}&pageSize=${pageInfo.pageSize}">
                    下一页
                </a>
            </td>
        </c:if>
    </tr>
</table>
</body>
</html>

list2.jsp

2.PageHelper插件实现分页

理解了分页的原理之后，就完全可以使用分页插件了；

PageHelper 是国内非常优秀的一款开源的Mybatis分页插件，它支持基本主流与常用的数据库，

他可以帮助程序员自动构建PageInfo对象，内部实现了分页逻辑，程序员可以直接使用。

官网：https://pagehelper.github.io/

2.1.导入jar包

2.1.主配置文件添加配置

在Mybatis的主配置文件environments标签上方，加入如下配置

    <!-- 添加PageHelper分页插件-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <property name="autoRuntimeDialect" value="true"/>
        </plugin>
    </plugins>

2.2.更换PageHelper内置的PageInfo对象

2.3.PageHelper插件核心原理

执行静态方法:开启PageHelper分页插件，设置拦截器，拦截所有select * from table查询所有的SQL语句，

在进行查询所有SQL语句后面进行SQL拼接，所以select * from table后面要加分号;

package com.zhanggen.service.impl;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.zhanggen.domain.User;
import com.zhanggen.mapper.UserMapper;
import com.zhanggen.service.UserService;
import com.zhanggen.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class UserServiceImpl implements UserService {
    //    @Override
//    public PageInfo<User> queryByPage(Integer pageNum, Integer pageSize) {
//        SqlSession sqlSession = MybatisUtil.getSqlSession();
//        PageInfo<User> pageInfo = new PageInfo<>();
//        UserMapper usermapper = sqlSession.getMapper(UserMapper.class);
//        //totalEntry：数据库里的记录一共多少行
//        Integer totalEntry = usermapper.queryToal();
//        pageInfo.setTotal(totalEntry);
//        //pages：数据库里一共多少条记录，前端总共需要分成多少页进行显示
//        Integer pages = totalEntry % pageSize == 0 ? totalEntry / pageSize : (totalEntry / pageSize) + 1;
//        pageInfo.setPages(pages);
//        //startIndex：当前这1页数据在数据库中起始索引位置
//        Integer startIndex = (pageNum - 1) * pageSize;
//        //Mybatis获取数据库数据
//        List<User> userList = usermapper.queryAllByPage(startIndex, pageSize);
//        pageInfo.setList(userList);
//        //pageSize：一页包含多少条记录，后端根据前端参数，设置每1页显示多少条记录
//        pageInfo.setPageSize(pageSize);
//        //pageNum：当前页的页码
//        pageInfo.setPageNum(pageNum);
//        MybatisUtil.commitAndclose();
//        return pageInfo;
//    }
    @Override
    public PageInfo<User> queryByPage(Integer pageNum, Integer pageSize) {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        UserMapper usermapper = sqlSession.getMapper(UserMapper.class);
        //1.执行静态方法:开启PageHelper分页插件（设置拦截器，拦截所有select * from table查询所有的SQL语句，
        // 在进行查询所有SQL语句后面进行SQL拼接，所以select * from table后面要加分号;）
        PageHelper.startPage(pageNum, pageSize);
        //2.执行查询所有SQL语句
        List<User> userList = usermapper.queryAll();
        //3.实例化封装1个PageInfo对象
        PageInfo<User> pageInfo = new PageInfo<>(userList);
        //4.是否数据库连接资源
        MybatisUtil.commitAndclose();
        //5.返回PageInfo对象
        return pageInfo;
    }
}

UserServiceImpl.java

九、MySQL实现业务功能

1.实现增长和累计增量趋势分析

1.1.存储过程生成连续日期表

利用存储过程，生成1张单独的时间表，避免使用程序生成时间；

-- 存储过程生成连续日期表
DELIMITER $$
DROP PROCEDURE IF EXISTS create_calendar $$
CREATE PROCEDURE create_calendar (s_date DATE, e_date DATE)
BEGIN
    SET @createSql = 'CREATE TABLE IF NOT EXISTS tb_day (
                      `date` date NOT NULL,
                         UNIQUE KEY `unique_date` (`date`) USING BTREE
                   )ENGINE=InnoDB DEFAULT CHARSET=utf8';
    prepare stmt from @createSql;
    execute stmt;

    WHILE s_date <= e_date DO
    INSERT IGNORE INTO tb_day VALUES (DATE(s_date)) ;
    SET s_date = s_date + INTERVAL 1 DAY ;
    END WHILE ;
END $$
DELIMITER ;

CALL create_calendar ('2020-01-01', '2040-12-31');

1.2.mapper层

在mapper层左连接刚刚生成的时间表

# 统计每天新增线索数量，格式化日期
select c.date, ifnull(num, 0) as num
from tb_day c
         left join
     (
         select date_format(create_time, '%Y-%m-%d') as time, count(id) as num
         from tb_clue
         where date_format(create_time, '%Y-%m-%d') between '2022-04-21' and '2022-06-25'
         group by time
     )
         as b on c.date = b.time
where c.date between '2022-06-01' and '2022-06-20'
order by c.date

1.3.service层

 //线索统计
    @Override
    public LineChartVO cluesStatistics(String beginCreateTime, String endCreateTime) {
        //准备返回给前端的线形图对象
        LineChartVO lineChartVO = new LineChartVO();

        //1. 查询时间范围内每天线索数量
        List<Map<String, Object>> list = reportMpper.cluesStatistics2(beginCreateTime, endCreateTime);

        //2. 准备y轴2个对象
        LineSeriesVO newClues = new LineSeriesVO();
        LineSeriesVO totalClues = new LineSeriesVO();

        //3. 遍历list,获取到每天的日期和新增数
        Long total = 0L;
        for (Map<String, Object> map : list) {
            String date = (String) map.get("date");
            Long num = (Long) map.get("num");

            //3-1 收集日期,设置xAxis
            lineChartVO.getxAxis().add(date);

            //3-2 收集新增线索数量
            newClues.getData().add(num);

            //3-3 收集线索总数量
            total += num;
            totalClues.getData().add(total);
        }

        //4. 设置y轴中的name
        newClues.setName("新增线索数量");
        totalClues.setName("线索总数量");

        //5. 设置y轴集合到返回对象
        lineChartVO.getSeries().add(newClues);
        lineChartVO.getSeries().add(totalClues);

        return lineChartVO;
    }

service层

参考

posted on 2022-01-02 19:16 Martin8866 阅读(173) 评论(0) 编辑收藏举报

刷新页面返回顶部