Mybatis详解

先配置下 mybatis 

大概结构是这样的

不是maven项目 

建一个lib包 导入架包 

在建一个文件夹 resc 或者 resource 创建 Mybatis.xml文件 

这里我是事先配置的  生成之后就是这样的

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

    <settings>
        <setting name="autoMappingBehavior" value="FULL"/>
    </settings>

    <typeAliases>
        <package name="cn.bcsp.pojo"/>
    </typeAliases>


    <!-- 配置数据源，事务 -->
    <environments default="deploy">
        <environment id="deploy">
            <!-- 事务：JDBC/MANAGED-自己管理去 -->
            <transactionManager type="JDBC"/>
            <!-- 数据源：POOLED/UNPOOLED/JNDI -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url"
                          value="jdbc:mysql://localhost:3306/smbms?serverTimezone=GMT%2B8&amp;characterEncoding=utf8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="cn/bcsp/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

这里只有 mapper 和 数据库名字 账号密码 不一样 

这个log4j 是打印日志用的 可以看到SQL语句 以及日志

在这里我给他设置了规则 

  <typeAliases>
        <package name="cn.bcsp.pojo"/>
    </typeAliases>

按我这个方式创建 

mapper   就是之前的 接口加实现 

pojo         就只实体类 跟entity一样 

test          测试类

Util          工具类

下面就让我们来实现下 CRUD

从创建一个实体类开始 

public class User {
    private static final long serialVersionUID = 958605452743418727L;


    private Long Id;


//    private RoleInfo roleInfo;
//    public RoleInfo getRoleInfo() {
//        return roleInfo;
//    }
//
//
//    public void setRoleInfo(RoleInfo roleInfo) {
//        this.roleInfo = roleInfo;
//    }

    private String account;

    private String realName;

    private String password;

    private Integer sex;

    private Date birthday;

    private String phone;

    private String address;

    private Long roleId;

    private Long createdUserId;

    private Date createdTime;

    private Long updatedUserId;

    private Date updatedTime;


    private Integer age;

    private String roleName;

    public User(Long id, String account, String realName, String password, Integer sex, Date birthday, String phone, String address, Long roleId, Long createdUserId, Date createdTime, Long updatedUserId, Date updatedTime, Integer age, String roleName) {
        Id = id;
        this.account = account;
        this.realName = realName;
        this.password = password;
        this.sex = sex;
        this.birthday = birthday;
        this.phone = phone;
        this.address = address;
        this.roleId = roleId;
        this.createdUserId = createdUserId;
        this.createdTime = createdTime;
        this.updatedUserId = updatedUserId;
        this.updatedTime = updatedTime;
        this.age = age;
        this.roleName = roleName;
    }

    public Integer getAge() {


        return new Date().getYear()-birthday.getYear();
    }



    public String getRoleName() {
        return roleName;
    }

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

    public User(Long id, String account, String realName, String password, Integer sex, Date birthday, String phone, String address, Long roleId, Long createdUserId, Date createdTime, Long updatedUserId, Date updatedTime) {
        Id = id;
        this.account = account;
        this.realName = realName;
        this.password = password;
        this.sex = sex;
        this.birthday = birthday;
        this.phone = phone;
        this.address = address;
        this.roleId = roleId;
        this.createdUserId = createdUserId;
        this.createdTime = createdTime;
        this.updatedUserId = updatedUserId;
        this.updatedTime = updatedTime;
    }

    public User() {
    }

    public static long getSerialVersionUID() {
        return serialVersionUID;
    }

    public Long getId() {
        return Id;
    }

    public void setId(Long id) {
        Id = id;
    }

    public String getAccount() {
        return account;
    }

    public void setAccount(String account) {
        this.account = account;
    }

    public String getRealName() {
        return realName;
    }

    public void setRealName(String realName) {
        this.realName = realName;
    }

    public String getPassword() {
        return password;
    }

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

    public Integer getSex() {
        return sex;
    }

    public void setSex(Integer sex) {
        this.sex = sex;
    }

    public Object 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 Long getRoleId() {
        return roleId;
    }

    public void setRoleId(Long roleId) {
        this.roleId = roleId;
    }

    public Long getCreatedUserId() {
        return createdUserId;
    }

    public void setCreatedUserId(Long createdUserId) {
        this.createdUserId = createdUserId;
    }

    public Date getCreatedTime() {
        return createdTime;
    }

    public void setCreatedTime(Date createdTime) {
        this.createdTime = createdTime;
    }

    public Long getUpdatedUserId() {
        return updatedUserId;
    }

    public void setUpdatedUserId(Long updatedUserId) {
        this.updatedUserId = updatedUserId;
    }

    public Date getUpdatedTime() {
        return updatedTime;
    }

    public void setUpdatedTime(Date updatedTime) {
        this.updatedTime = updatedTime;
    }

    @Override
    public String toString() {
        return "User{" +
                "Id=" + Id +
                ", account='" + account + '\'' +
                ", realName='" + realName + '\'' +
                ", password='" + password + '\'' +
                ", sex=" + sex +
                ", birthday=" + birthday +
                ", phone='" + phone + '\'' +
                ", address='" + address + '\'' +
                ", roleId=" + roleId +
                ", createdUserId=" + createdUserId +
                ", createdTime=" + createdTime +
                ", updatedUserId=" + updatedUserId +
                ", updatedTime=" + updatedTime +
                '}';
    }
}

这没什么好看的 给他们生成 set get 方法 

下面我们创建一个 mapper 来写接口 

public interface UserMapper {
    int insert(User user);

    int insert2(User user);

    int delete(@Param("id") long id);

    int delete2(@Param("id") long id);

    int update(User user);

    int update2(User user);

    List<User> selectlist(Map<String, Object> map);

    List<User> selectlist2(Map<String, Object> map);

    List<User> selectChoose(Map<String, Object> map);

    List<User> selectChoose2(Map<String, Object> map);

    List<User> select(@Param("id") List<Long> id);

    List<User> select2(@Param("id") List<Long> id);

名字起的很随意

先从增开始讲吧

int insert(User user); //接口 

写好接口后我们来创建一个 mapper 配置文件  我这里也是配置好的 直接使用就可以了

<?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.bcsp.mapper.UserMapper">
    <insert id="insert" parameterType="User">
        INSERT INTO smbms.t_sys_user
        (account,
         realName,
         password,
         sex,
         birthday,
         phone,
         address,
         roleId,
         createdUserId,
         createdTime,
         updatedUserId,
         updatedTime)
        VALUES (#{account},
                #{realName},
                #{password},
                #{sex},
                #{birthday},
                #{phone},
                #{address},
                #{roleId},
                #{createdUserId},
                #{createdTime},
                #{updatedUserId},
                #{updatedTime})
    </insert>
</mapper>

namespace里写的是我们 刚刚写的接口的位置 

如果你是增加那么就写一个 insert 标签  id是 这个接口的名字  parameterType 是 参数的类型

我的参数是对象 所以 就写对象就行

剩下的 在标签里直接写SQL语句就行

让我们来测试一下 

写测试之前 一定不要忘了 还有一个工具类

package cn.bcsp.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;

/**
 * @Description: TODO
 * @author: MengJiaLin
 * @date: 2021年06月16日 16:23
 */
public class MybatisUtil {
    private static SqlSessionFactory sqlSessionFactory;
    private static ThreadLocal<SqlSession> threadLocal = new ThreadLocal<>();

    static {
        try {
            SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
            InputStream resourceAsStream = Resources.getResourceAsStream("MyBatis.xml");
            sqlSessionFactory= sqlSessionFactoryBuilder.build(resourceAsStream);
            resourceAsStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
    public static SqlSession getSqlSession(){
        if(threadLocal.get()==null){
            SqlSession sqlSession = sqlSessionFactory.openSession(true);
            threadLocal.set(sqlSession);

        }
        return threadLocal.get();
    }
    public  static void closeSqlSession(){
        SqlSession sqlSession = threadLocal.get();
        if(sqlSession!=null){
            sqlSession.close();
            threadLocal.set(null);
        }
    }

}

sqlSession 为什么写true 因为要实现事务提交 这样就不用写 commit 了

增删改都要提交事务

下面正式来让我们 测试下 

测试成功 增加进去了 

getMapper里 接口.class

因为我数据库里 全都设置的可以为null所以我就增加了两个字段

下面写 删除 

删除 要根据id删 所以 这里参数给一个id

接口 用一个 注解 来代替 id 起个别名

    <delete id="delete" parameterType="long">
        DELETE
        FROM smbms.t_sys_user
        WHERE id = #{id};
    </delete>

这是删除 所以 写一个 delete的标签 id是接口的名字 parameterType 是参数的类型 long

标签里面写 删除语句 根据id删除 

下面我们来测试一下

这里是成功 删除的

改的话也简单 

更改

 int update(User user);

接口 参数可以写 id 和你要修改的属性 

也可以写对象 

<update id="update" parameterType="User">
        UPDATE smbms.t_sys_user
        SET account  = #{account},
            realName = #{realName}
        WHERE id = #{id}
</update>

和之前两个没啥区别 在标签里写SQL 语句  写了两个了 大家发现也就SQL语句不一样 

所以说会写SQL语句真的很重要！

下面来测试

这就修改好了 很简单把

下面到重点了

查询

    List<User> selectAll();//查所有list集合方式

    int Count();//差总数量

    int insert(User user);//用对象方式查所有

    List<User>selectIdAndName(Map<String,Object>map);//map集合查所有

    public RoleInfo select(@Param("roleId") long roleId);//通过参数 查询

这是 接口 注释写在上面了 

    <select id="selectAll" resultType="cn.bcsp.pojo.User" useCache="true">
        select *
        from t_sys_user
    </select>

这是普通的查询 

下面我们来测试一下

这就查出来了 所有的  

下面来查一下 总数量 测试

这是用Map集合来查

List<User>selectIdAndName(Map<String,Object>map);

    <select id="selectIdAndName" parameterType="map" resultType="User">
        SELECT t.id, account, sex, birthday
        FROM t_sys_user t
        INNER JOIN t_sys_role r ON t.id = r.id
        <where>
            <if test="account!=null and account!=''">
                account LIKE CONCAT('%', #{account}, '%')
            </if>
            <if test="roleId !=null and roleId !=0">
            and roleId = #{roleId}
            </if>
        </where>
    </select>

用的模糊查询 concat('%'#{对象}'%')

parameterType 是参数的类型

resultType 这是对象类型

还用到了 动态sql

where标签 可以代替 数据库 where 属性 写多个and 也没关系 他可以自动去掉你多余的and

if标签 用来判断 test里面是 写条件语句 来作为判断  在 判断里面不能使用&& 要使用and

这是 两个表连起来查 

来测试下 我们把数据全部填成空的 看有什么变化

SELECT t.id, account, sex, birthday FROM t_sys_user t INNER JOIN t_sys_role r ON t.id = r.id 

这是SQL语句 可以看出什么? 

对了 进入判断了 进我的两个if里了  

因为都是空 所以没有条件 直接查出来的所有 不拼接SQL语句

下面我们来添加下条件 

大家能看出来变化了没? 

对 有条件之后 SQL语句都拼接上了 

下面给大家讲一讲 动态SQL ！！ 注意听讲

一共有几个 经常用到的标签 

if         where         choose         foreach        

if 就是 可以进行判断 test里写条件语句 

where就是 可以sql拿过来 不用写 where 了  直接用 where标签就行  他可以 把多余的and去掉