Mybatis入门学习

最新推荐文章于 2022-07-01 15:03:11 发布

yj123ab

最新推荐文章于 2022-07-01 15:03:11 发布

阅读量219

点赞数 1

文章标签： mybatis

本文链接：https://blog.csdn.net/yj123ab/article/details/112317617

版权

Mybatis框架搭建流程

一.Ideal创建项目

在这里插入图片描述
二.引入maven标记
创建完项目后引入maven标记

<dependencies>
    //连接数据库
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>6.0.3</version>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.6</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/log4j/log4j -->
    <!-- https://mvnrepository.com/artifact/log4j/log4j -->
    //日志
    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core -->
    <dependency>
    <groupId>org.apache.logging.log4j</groupId>
    <artifactId>log4j-core</artifactId>
    <version>2.13.0</version>
    </dependency>
  </dependencies>

三.创建持久化类（News）与数据库中的表对应

 package com.ddbc.pojo;

import java.util.Date;

public class News {
    private int nid;
    private int ntid;
    private String ntitle;
    private String nauthor;
    private String ncontent;
    private String nsummary;
    private Date ncreateDate;
    public News(){}
    public News(int nid, int ntid, String ntitle, String nauthor, String ncontent, String nsummary,Date ncreateDate) {
        this.nid = nid;
        this.ntid = ntid;
        this.ntitle = ntitle;
        this.nauthor = nauthor;
        this.ncontent = ncontent;
        this.nsummary = nsummary;
        this.ncreateDate = ncreateDate;
    }

    public int getNid() {
        return nid;
    }

    public void setNid(int nid) {
        this.nid = nid;
    }

    public int getNtid() {
        return ntid;
    }

    public void setNtid(int ntid) {
        this.ntid = ntid;
    }

    public String getNtitle() {
        return ntitle;
    }

    public void setNtitle(String ntitle) {
        this.ntitle = ntitle;
    }

    public String getNauthor() {
        return nauthor;
    }

    public void setNauthor(String nauthor) {
        this.nauthor = nauthor;
    }

    public String getNcontent() {
        return ncontent;
    }

    public void setNcontent(String ncontent) {
        this.ncontent = ncontent;
    }

    public String getNsummary() {
        return nsummary;
    }

    public void setNsummary(String nsummary) {
        this.nsummary = nsummary;
    }

    public Date getNcreateDate() {
        return ncreateDate;
    }

    public void setNcreateDate(Date ncreateDate) {
        this.ncreateDate = ncreateDate;
    }
}

四.编写mybatis的核心配置文件
在这里插入图片描述

Resources下编写mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTDConfig3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--    引入数据库连接属性文件-->
    <properties resource="database.properties"></properties>
<!--    日志配置-->
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>
<!--    类型别名-->
    <typeAliases>
        <package name="com.ddbc.pojo"/>
    </typeAliases>
    <environments default="development">
    //对应多种数据库
        <environment id="development">
            <transactionManager type ="JDBC"/>
            <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="mappers/UserMapper.xml"/>
    </mappers>
</configuration>

编写database.properties数据源文件

driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/smbms?useSSL=false&serverTimezone=UTC
username = root
password = yujiang

编写映射文件NewsMapper.xml：mappers下，一个持久化类对应一个映射文件（.xml）

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTDMapper3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ddbc.dao.NewsMapper">
    <select id="getNewsList" resultType="News">
        select * from news
    </select>
    <select id="getNewsById" parameterType="int" resultType="News">
        select * from news where nid = #{id}
    </select>
    <update id="updateNews">
        update news set ntitle = #{ntitle} where nid = #{nid}
    </update>
    <update id="updateNews2">
        update news set ntitle = #{arg0} where nid = #{arg1}
    </update>
</mapper>

namespace对应接口
五.编写mapper.xml对应的接口

package com.ddbc.dao;

import com.ddbc.pojo.News;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface NewsMapper {
    public List<News> getNewsList();
    public News getNewsById(int id);
    public int updateNews(@Param("ntitle") String ntitle,@Param("nid") int nid);
    public int updateNews2(String ntitle,int nid);
}

六.测试类

public class test {
    public static void main(String[] args) {
        SqlSession sqlSession=null;
        try {
        	//加载配置文件
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
            //得到SqlSession对象
            sqlSession = factory.openSession(true);//true:自动提交事务
//            List<News> newsList = sqlSession.selectList("com.ddbc.dao.NewsMapper.getNewsList");
//            List<News> newsList = sqlSession.getMapper(NewsMapper.class).getNewsList();
//            for(News news:newsList){
//                System.out.println("作者："+news.getNauthor());
//            }
//            News news1 = sqlSession.selectOne("com.ddbc.dao.NewsMapper.getNewsById",48);
//            News news1 = sqlSession.getMapper(NewsMapper.class).getNewsById(48);
//            System.out.println("新闻内容："+news1.getNcontent());
              int i = sqlSession.getMapper(NewsMapper.class).updateNews("日照职业技术学院2",212);
              //sqlSession.commit();
              if(i>0){
                  System.out.println("修改成功");
              }
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            sqlSession.close();
        }
    }
}

大体流程是这样的。

作用域和生命周期

SqlSessionFactory（即用即毁）

这个类可以被初始、使用和丢弃，如果你已经创建好了一个SqlSessionFactory后就不用再保留它。因此，SqlSessionFactoryBuilder的最好作用域是方法体内，比如说定义一个方法变量。你可以重复使用SqlSessionFactoryBuilder生成多个SqlSessionFactory实例，但是最好不要强行保留，因为XML的解析资源要用来做其它更重要的事。

SqlSessionFactory（只创建一次）

一旦创建，SqlSessionFactory就会在整个应用过程中始终存在。所以没有理由去销毁和再创建它，一个应用运行中也不建议多次创建SqlSessionFactory。如果真的那样做，会显得很拙劣。因此SqlSessionFactory最好的作用域是Application。可以有多种方法实现。最简单的方法是单例模式或者是静态单例模式。然而这既不是广泛赞成和好用的。反而，使用GoogleGuice或Spring来进行依赖反射会更好。这些框架允许你生成管理器来管理SqlSessionFactory的单例生命周期。

SqlSession

每个线程都有自己的SqlSession实例，SqlSession实例是不能被共享，也是不是线程安全的。因此最好使用Request作用域或者方法体作用域。不要使用类的静态变量来引用一个SqlSession实例，甚至不要使用类的一个实例变更来引用。永远不要在一个被管理域中引用SqlSession，比如说在Servlet中的HttpSession中。如果你正在使用WEB框架，应该让SqlSession跟随HTTP请求的相似作用域。也就是说，在收到一个HTTP请求过后，打开SqlSession，等返回一个回应以后，立马关掉这个SqlSession。关闭SqlSession是非常重要的。你必须要确保SqlSession在finally方法体中正常关闭。可以使用下面的标准方式来关闭：SqlSessionsession=sqlSessionFactory.openSession();
try{
//dowork
}finally{
session.close();
}
静态单例模式
编写一个工具类，用来创建sqlSession

package com.ddbc.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 {
    //静态单例模式：SqlSessionFactiory在整个应用过程中始终存在
    private static  SqlSessionFactory sessionFactory;
    static {
        try {
            InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
            sessionFactory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static SqlSession createSqlsession(){
        SqlSession sqlSession = sessionFactory.openSession(true);
        return  sqlSession;
    }
    public static void close(SqlSession sqlSession){
        if(sqlSession!=null){
            sqlSession.close();
        }
    }
}

测试类：

public class test2 {
    public static void main(String[] args) {
        SqlSession sqlSession = mybatisUtil.createSqlsession();
        List<News> news = sqlSession.getMapper(NewsMapper.class).getNewsList();
        for(News news1:news){
            System.out.println(news1.getNauthor());
        }
        mybatisUtil.close(sqlSession);
    }
}

映射文件xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTDMapper3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ddbc.dao.NewsMapper">
    <select id="getNewsList" resultType="News">
        select * from news
    </select>
    <select id="getNewsById" parameterType="int" resultType="News">
        select * from news where nid = #{id}
    </select>
    <update id="updateNews">
        update news set ntitle = #{ntitle} where nid = #{nid}
    </update>
    <update id="updateNews2">
        update news set ntitle = #{arg0} where nid = #{arg1}
    </update>
</mapper>

每个语句中的id对应接口方法名

 public interface NewsMapper {
    public List<News> getNewsList();
    public News getNewsById(int id);
    public int updateNews(@Param("ntitle") String ntitle,@Param("nid") int nid);
    public int updateNews2(String ntitle,int nid);
}

parameterType传参
有3种传参方式
1.参数是持久化类（一般用来添加新的数据) parameterType=“News”
2.有几个参数，参数类型不同，适合参数数量不多
这时不需要写类型，使用注解

 public int updateNews(@Param("ntitle") String ntitle,@Param("nid") int nid);

 <update id="updateNews">
        update news set ntitle = #{ntitle} where nid = #{nid}
  </update>

3.用argn,根据参数列表位置一一对应,arg0,arg1,arg2(这种最简洁，比第二种简单)

public int updateNews2(String ntitle,int nid);

 <update id="updateNews2">
        update news set ntitle = #{arg0} where nid = #{arg1}
 </update>

ResultType返回类型:一般有booean,int,对象
ResultMap:返回类型不确定：一般用于查询表一对一，或一对多（一个表对应多个表）
一对一：一个用户表对应一个角色表

联表查询，返回的是两个表的数据
resultType返回类型是固定的，这时用resultMap自定义返回类型
再编写sql语句之前我们先编写一下用户表，和角色表对应的持久化类
编写角色类

 public class Smbms_role {
    private int id;
    private String roleCode;
    private String roleName;

    public int getId() {
        return id;
    }

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

    public String getRoleCode() {
        return roleCode;
    }

    public void setRoleCode(String roleCode) {
        this.roleCode = roleCode;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }
}

编写用户类

public class Smbms_user {
    private int id;
    private String userCode;
    private String userName;
    private String userPassword;
    private int gender;
    private Date birthday;
    private String phone;
    private String address;
    private int userRole;
    private Smbms_role smbms_role;//角色类作为用户类的一个属性

    public List<Smbms_address> getSmbms_addressList() {
        return smbms_addressList;
    }

    public void setSmbms_addressList(List<Smbms_address> smbms_addressList) {
        this.smbms_addressList = smbms_addressList;
    }

    public int getId() {
        return id;
    }

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

    public String getUserCode() {
        return userCode;
    }

    public void setUserCode(String userCode) {
        this.userCode = userCode;
    }

    public String getUserName() {
        return userName;
    }

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

    public String getUserPassword() {
        return userPassword;
    }

    public void setUserPassword(String userPassword) {
        this.userPassword = userPassword;
    }

    public int getGender() {
        return gender;
    }

    public void setGender(int gender) {
        this.gender = gender;
    }

    public Date getBirthday() {
        return birthday;
    }

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

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public int getUserRole() {
        return userRole;
    }

    public void setUserRole(int userRole) {
        this.userRole = userRole;
    }

    public Smbms_role getSmbms_role() {
        return smbms_role;
    }

    public void setSmbms_role(Smbms_role smbms_role) {
        this.smbms_role = smbms_role;
    }
}

编写接口

 public List<Smbms_user> getUserList();

编写sql语句

<select id="getUserList" resultMap="userMap">
        SELECT s.*,r.id r_id,r.`roleCode`,r.`roleName` FROM smbms_user s LEFT JOIN smbms_role r ON s.`userRole` = r.`id`
 </select>

<resultMap id="userMap" type="Smbms_user">//type:查询结果返回类型
        <id column="id" property="id"></id>//id是主键，column对应数据表中的字段，property对象实体类中的属性
        <result column="userCode" property="userCode"></result>
        <result column="userName" property="userName"></result>
        <result column="userPassword" property="userPassword"></result>
        <result column="gender" property="gender"></result>
        <result column="birthday" property="birthday"></result>
        <result column="phone" property="phone"></result>
        <result column="address" property="address"></result>
        <result column="userRole" property="userRole"></result>
        <association property="smbms_role" javaType="Smbms_role">
            <id column="r_id" property="id"></id>
            <result column="roleCode" property="roleCode"></result>
            <result column="roleName" property="roleName"></result>
        </association>
  </resultMap>

编写测试类

package com.ddbc.test;

import com.ddbc.dao.UserMapper;
import com.ddbc.pojo.Smbms_user;
import com.ddbc.util.mybatisUtil;
import org.apache.ibatis.session.SqlSession;

import java.util.List;

public class userTest {
    public static void main(String[] args) {
        SqlSession sqlSession = mybatisUtil.createSqlsession();
        List<Smbms_user> users = sqlSession.getMapper(UserMapper.class).getUserList();
        for(Smbms_user user:users){
            System.out.println("用户名："+user.getUserName()+"角色名："+user.getSmbms_role().getRoleName());
        }
        mybatisUtil.close(sqlSession);
    }
}

一对多
一个用户有多个地址，一个用户表对应多个地址表
在这里插入图片描述

编写smbms_address对应的实体类

 package com.ddbc.pojo;

import java.util.Date;

public class Smbms_address {
    private int id;
    private String contact;
    private String addressDesc;
    private String postCode;
    private String tel;
    private int createdBy;
    private Date creationDate;
    private int userId;

    public int getId() {
        return id;
    }

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

    public String getContact() {
        return contact;
    }

    public void setContact(String contact) {
        this.contact = contact;
    }

    public String getAddressDesc() {
        return addressDesc;
    }

    public void setAddressDesc(String addressDesc) {
        this.addressDesc = addressDesc;
    }

    public String getPostCode() {
        return postCode;
    }

    public void setPostCode(String postCode) {
        this.postCode = postCode;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public int getCreatedBy() {
        return createdBy;
    }

    public void setCreatedBy(int createdBy) {
        this.createdBy = createdBy;
    }

    public Date getCreationDate() {
        return creationDate;
    }

    public void setCreationDate(Date creationDate) {
        this.creationDate = creationDate;
    }

    public int getUserId() {
        return userId;
    }

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

修改用户实体类

public class Smbms_user {
    private int id;
    private String userCode;
    private String userName;
    private String userPassword;
    private int gender;
    private Date birthday;
    private String phone;
    private String address;
    private int userRole;
    private Smbms_role smbms_role;
    private List<Smbms_address> smbms_addressList;//一个用户有多个地址，地址类作为用户的一个属性

    public List<Smbms_address> getSmbms_addressList() {
        return smbms_addressList;
    }

    public void setSmbms_addressList(List<Smbms_address> smbms_addressList) {
        this.smbms_addressList = smbms_addressList;
    }

    public int getId() {
        return id;
    }

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

    public String getUserCode() {
        return userCode;
    }

    public void setUserCode(String userCode) {
        this.userCode = userCode;
    }

    public String getUserName() {
        return userName;
    }

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

    public String getUserPassword() {
        return userPassword;
    }

    public void setUserPassword(String userPassword) {
        this.userPassword = userPassword;
    }

    public int getGender() {
        return gender;
    }

    public void setGender(int gender) {
        this.gender = gender;
    }

    public Date getBirthday() {
        return birthday;
    }

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

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public int getUserRole() {
        return userRole;
    }

    public void setUserRole(int userRole) {
        this.userRole = userRole;
    }

    public Smbms_role getSmbms_role() {
        return smbms_role;
    }

    public void setSmbms_role(Smbms_role smbms_role) {
        this.smbms_role = smbms_role;
    }
}

编写接口

 public Smbms_user getUserById(int id);

写查询语句

<select id="getUserById" resultMap="User_addressMap">
        SELECT *,a.id a_id FROM smbms_user u LEFT JOIN smbms_address a ON u.id = a.`userId` WHERE u.id = #{id}
</select>

<resultMap id="User_addressMap" type="Smbms_user">
        <id column="id" property="id"></id>
        <result column="userCode" property="userCode"></result>
        <result column="userName" property="userName"></result>
        <result column="userPassword" property="userPassword"></result>
        <result column="gender" property="gender"></result>
        <result column="birthday" property="birthday"></result>
        <result column="phone" property="phone"></result>
        <result column="address" property="address"></result>
        <result column="userRole" property="userRole"></result>
        <collection property="smbms_addressList" ofType="Smbms_address">//和一对一写法差不多，这里是collection javaType换成了ofType
            <id column="a_id" property="id"></id>
            <result column="addressDesc" property="addressDesc"></result>
        </collection>
</resultMap>

编写测试类

 public class addressTest {
    public static void main(String[] args) {
        SqlSession sqlSession = mybatisUtil.createSqlsession();
        Smbms_user user = sqlSession.getMapper(UserMapper.class).getUserById(1);
        for(Smbms_address address:user.getSmbms_addressList()){
            System.out.println(user.getUserName()+","+address.getAddressDesc());
        }
    }
}

结果：
在这里插入图片描述

动态Sql

基于OGNL表达式 OGNL 对象图导航语言

条件判断if

用于查询语句

接口：

//根据角色和用户名获得用户列表
   public List<Smbms_user> getUserListByName(int userRole,String userName);
}

sql语句：

<select id="getUserListByName" resultType="Smbms_user">
    select * from smbms_user where 1=1
    <if test="arg0!=0">//test里面的条件符合才执行
        and userRole = #{arg0}
    </if>
    <if test="arg1!=null || arg1!=''">
        and userName like concat('%',#{arg1},'%')
    </if>
</select>

或者

<select id="getUserListByName" resultType="Smbms_user">
    select * from smbms_user
    <where>
        <if test="arg0!=0">
            and userRole = #{arg0}
        </if>
        <if test="arg1!=null || arg1!=''">
            and userName like concat('%',#{arg1},'%')
        </if>
    </where>
</select>

第二种更灵活，自动的把第一个的and去掉
用于更新数据

 update smbms_user
  <set>
            <if test="userCode!=null">userCode=#{userCode},</if>
            <if test="userName!=null">userName=#{userName},</if>
            <if test="userPassword!=null">userPassword=#{userPassword},</if>
            <if test="gender!=0">gender=#{gender},</if>
            <if test="phone!=null">phone=#{phone},</if>
            <if test="address!=null">address=#{address},</if>
            <if test="userRole!=0">userRole=#{userRole},</if>
   </set>
   where id = #{id}

trim语句

 <update id="updateUserById" parameterType="Smbms_user">
        update smbms_user
        <trim prefix="set" suffixOverrides="," suffix="where id = #{id}">
            <if test="userCode!=null">userCode=#{userCode},</if>
            <if test="userName!=null">userName=#{userName},</if>
            <if test="userPassword!=null">userPassword=#{userPassword},</if>
            <if test="gender!=0">gender=#{gender},</if>
            <if test="phone!=null">phone=#{phone},</if>
            <if test="address!=null">address=#{address},</if>
            <if test="userRole!=0">userRole=#{userRole},</if>
 </trim>

prefix:if语句前缀，if语句suffix后缀，suffexOverrides自动过滤最后一个if语句的逗号
前面的查询语句也可以用trim

<select id="getUserListByName" resultType="Smbms_user">
        select * from smbms_user
        <trim prefix="where" prefixOverrides="and">//prefixOverrides:自动过滤if语句的第一个and
            <if test="arg0!=0">
               and userRole = #{arg0}
            </if>
            <if test="arg1!=null || arg1!=''">
            and userName like concat('%',#{arg1},'%')
            </if>
        </trim>
</slect>

foreach语句
Collection:遍历对象类型（array,list…）
Item:遍历的每个元素
Open:开始符号
Close:结束符号
Separator:分隔符
接口：

public List<Smbms_user> getUserListByRoles(Integer[] roleIds);
 public List<Smbms_user> getUserListByRoles2(List<Integer> roles);

select id="getUserListByRoles" resultType="Smbms_user">
        select * from smbms_user where userRole in
        <foreach collection="array" open="(" close=")" separator="," item="roleId">
            #{roleId}
        </foreach>
    </select>
    <select id="getUserListByRoles2" resultType="Smbms_user">
        select * from smbms_user where userRole in
        <foreach collection="list" open="(" close=")" separator="," item="roleId">
            #{roleId}
        </foreach>
   </select>

测试类

public class forTest {
    public static void main(String[] args) {
        SqlSession sqlSession = mybatisUtil.createSqlsession();
//        Integer[] roles = {1,2};
//        List<Smbms_user> users = sqlSession.getMapper(UserMapper.class).getUserListByRoles(roles);
//        for(Smbms_user user:users){
//            System.out.println(user.getUserName());
//        }
        List<Integer> list = new ArrayList<Integer>();
        list.add(1);
        list.add(2);
        List<Smbms_user> users = sqlSession.getMapper(UserMapper.class).getUserListByRoles2(list);
           for(Smbms_user user:users){
            System.out.println(user.getUserName());
       }
    }
}

分页插件

分页插件学习链接
使用分页插件如何分页？

<nav aria-label="Page navigation">
            <ul class="pagination">
                <li><a href="#" aria-label="Previous" one-link-mark="yes" th:href="@{'stus/Page?pageNum='+${pageInfo.hasPreviousPage?(pageInfo.pageNum)-1:(pageInfo.pageNum)}}"><span aria-hidden="true">«</span></a></li>
                <li th:each="pageNum :${pageInfo.getNavigatepageNums()}" class=""th:class="${pageInfo.pageNum == pageNum} ? 'active' : '' "><a href="#" one-link-mark="yes" th:text="${pageNum}" th:href="@{'stus/Page?pageNum='+${pageNum}}"> <span class="sr-only">(current)</span></a></li>
                <li><a href="#" aria-label="Next" one-link-mark="yes" th:href="@{'stus/Page?pageNum='+${pageInfo.hasNextPage?(pageInfo.pageNum+1):(pageInfo.pageNum)}}"><span aria-hidden="true">»</span></a></li>
            </ul>
        </nav>

测试类
在这里插入图片描述

mybatis整合SpringBoot一对多分页查询数量数减少

当我们对一对多查询的数据进行使用分页插件进行分页时，会出现数量减少的清况。
代码示例：
在这里插入图片描述

<resultMap id="RemarksResultMap" type="com.dbddd.yyedu.pojo.Students">
    <id column="sid" property="sid" jdbcType="INTEGER" />
    <result column="studentname" property="studentname" jdbcType="VARCHAR" />
    <result column="classesid" property="classesid" jdbcType="INTEGER" />
    <result column="extendJson" property="extendjson" jdbcType="VARCHAR" />
    <collection property="remarksList" ofType="com.dbddd.yyedu.pojo.Remarks">
            <id column="rid" property="rid" jdbcType="INTEGER" />
            <result column="studentid" property="studentid" jdbcType="INTEGER" />
            <result column="remarks" property="remarks" jdbcType="VARCHAR" />
    </collection>
	
	<select id="selectWithRemarks" resultMap="RemarksResultMap">
    SELECT * FROM students s LEFT JOIN remarks r ON s.`sid` = r.studentid
  </select>

学生和备注是一对多，一个学生有多个备注。这里查到了68条数据。sid=97的对应3条数据。
我们在已经写好的前端页面点击分页。
在这里插入图片描述

每页显示10条数据，点击第二页时，只显示了8条数据。这时因为remarkslist也是一个列表，分页也把它算进去了。解决方案很简单。使用子查询即可。修改collection.

<resultMap id="RemarksResultMap" type="com.dbddd.yyedu.pojo.Students">
    <id column="sid" property="sid" jdbcType="INTEGER" />
    <result column="studentname" property="studentname" jdbcType="VARCHAR" />
    <result column="classesid" property="classesid" jdbcType="INTEGER" />
    <result column="extendJson" property="extendjson" jdbcType="VARCHAR" />
    <collection property="remarksList" ofType="com.dbddd.yyedu.pojo.Remarks" select="remarks" column="s_id=sid">
    </collection>
  </resultMap>
  <select id="remarks" resultType="com.dbddd.yyedu.pojo.Remarks">
    select * from remarks where studentid = #{s_id}
  </select>

这时我们再看前端页面。
在这里插入图片描述
这次就没问题了。

逆向工程

自动生成po类，mapper映射文件，mapper接口

首先我们需要安装mybatis逆向工程插件mybatis Generator：

在这里插入图片描述
2. pom.xml 配置在build里添加

 <build>
    <plugins>
      <plugin>
        <groupId>org.mortbay.jetty</groupId>
        <artifactId>maven-jetty-plugin</artifactId>
        <version>6.1.7</version>
        <configuration>
          <connectors>
            <connector implementation="org.mortbay.jetty.nio.SelectChannelConnector">
              <port>8888</port>
              <maxIdleTime>30000</maxIdleTime>
            </connector>
          </connectors>
          <webAppSourceDirectory>${project.build.directory}/${pom.artifactId}-${pom.version}</webAppSourceDirectory>
          <contextPath>/</contextPath>
        </configuration>
      </plugin>
      //添加plugin
      <plugin>
        <groupId>org.mybatis.generator</groupId>
        <artifactId>mybatis-generator-maven-plugin</artifactId>
        <version>1.3.2</version>
        <dependencies>
          <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.22</version>
          </dependency>
        </dependencies>
        <configuration>
          <!--配置文件的路径-->
          <configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
           <overwrite>true</overwrite>
          </configuration>
      </plugin>
    </plugins>
  </build>

3.在resources添加文件 generatorConfig.xml

 <?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>
    <context id="testTables" targetRuntime="MyBatis3">
        <commentGenerator>
            <!-- 是否去除自动生成的注释 true：是 ： false:否 -->
            <property name="suppressAllComments" value="true" />
        </commentGenerator>
        <!--数据库连接的信息：驱动类、连接地址、用户名、密码 -->//需要修改
        <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                        connectionURL="jdbc:mysql://localhost:3306/smbms?useSSL=false&amp;serverTimezone=UTC" userId="root"
                        password="yujiang">
        </jdbcConnection>
        <!-- <jdbcConnection driverClass="oracle.jdbc.OracleDriver"
            connectionURL="jdbc:oracle:thin:@127.0.0.1:1521:yycg"
            userId="yycg"
            password="yycg">
        </jdbcConnection> -->

        <!-- 默认false，把JDBC DECIMAL 和 NUMERIC 类型解析为 Integer，为 true时把JDBC DECIMAL 和
            NUMERIC 类型解析为java.math.BigDecimal -->
        <javaTypeResolver>
            <property name="forceBigDecimals" value="false" />
        </javaTypeResolver>

        <!-- targetProject:生成PO类的位置 -->
        <javaModelGenerator targetPackage="com.dbddd.pojo"
                            targetProject=".\src">
            <!-- enableSubPackages:是否让schema作为包的后缀 -->
            <property name="enableSubPackages" value="false" />
            <!-- 从数据库返回的值被清理前后的空格 -->
            <property name="trimStrings" value="true" />
        </javaModelGenerator>
        <!-- targetProject:mapper映射文件生成的位置 -->
        <sqlMapGenerator targetPackage="com.dbddd.dao"
                         targetProject=".\src">
            <!-- enableSubPackages:是否让schema作为包的后缀 -->
            <property name="enableSubPackages" value="false" />
        </sqlMapGenerator>
        <!-- targetPackage：mapper接口生成的位置 -->
        <javaClientGenerator type="XMLMAPPER"
                             targetPackage="com.dbddd.dao"
                             targetProject=".\src">
            <!-- enableSubPackages:是否让schema作为包的后缀 -->
            <property name="enableSubPackages" value="false" />
        </javaClientGenerator>
        <!-- 指定数据库表 -->//需要修改
        <table tableName="smbms_user"></table>
        <table tableName="smbms_role"></table>
        <table tableName="smbms_address"></table>
        <table tableName="smbms_bill"></table>
        <table tableName="smbms_provider"></table>
        <!-- <table schema="" tableName="sys_user"></table>
        <table schema="" tableName="sys_role"></table>
        <table schema="" tableName="sys_permission"></table>
        <table schema="" tableName="sys_user_role"></table>
        <table schema="" tableName="sys_role_permission"></table> -->

        <!-- 有些表的字段需要指定java类型
         <table schema="" tableName="">
            <columnOverride column="" javaType="" />
        </table> -->
    </context>
</generatorConfiguration>

待idea 下载完成后点击最右边的Mavenprojects，运行生成文件

出现配置文件，实体等基本可以了。注意只要运行一次，不要点击多次，容易出错。

数据库日期格式转换问题

用逆向生成实体类后，获得日期显示有问题
这里是springboot整合mybatis
代码示例：

<tr th:each="stu : ${pageInfo.getList()}">
                <th scope="row"><a><span th:text="${stu.getSid()}"></span></a></th>
                <td th:text="${stu.getStudentname()}"></td>
                <td th:text="${stu.getClasses().getCname()}"></td>
                <td th:text="${stu.getSubjectname()}"></td>
                <td th:text="${stu.getMoney()}"></td>
                <td th:text="${stu.getStartdate()}"></td>
                <td th:text="${#dates.format(stu.getEnddate(),'yyyy-MM-dd')}"></td>
                <td th:text="${stu.getRemarks()}"></td>
                <td>
                    <a class="btn btn-default btn-sm" href="#" role="button">查看</a>
                    <a class="btn btn-default btn-sm" th:href="@{'stus/modify/'+${stu.getSid()}}" role="button">编辑</a>
                    <a class="btn btn-danger  btn-sm" th:href="@{'stus/delStu/'+${stu.getSid()}}" role="button">删除</a>
                </td>
            </tr>

结果图：
在这里插入图片描述
缴费时间格式明显不对。
修改代码：

<`td th:text="${#dates.format(stu.getEnddate(),'yyyy-MM-dd')}"></td>`
使用dates.format()就行了

同时修改实体类

@DateTimeFormat(pattern = "yyyy-MM-dd")//页面写入数据库时格式化
    private Date startdate;
    @DateTimeFormat(pattern = "yyyy-MM-dd")//页面写入数据库时格式化
    private Date enddate;

当我们修改或添加数据时，会自动转换成数据库对应的格式

  <div class="form-group">
            <label  class="col-sm-2 control-label">缴费时间</label>
            <div class="col-sm-10">
                <input type="date" class="form-control"  name="startdate" >
            </div>
        </div>
        <div class="form-group">
            <label  class="col-sm-2 control-label">到期时间</label>
            <div class="col-sm-10">
                <input type="date" class="form-control"  name="enddate">
            </div>
        </div>