[Java知识库] ssm框架之mybatis

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> Java知识库 -> ssm框架之mybatis -> 正文阅读

[Java知识库]ssm框架之mybatis

文章目录

简介

MyBatis 本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了google code，并且改名为MyBatis 。2013年11月迁移到Github。

iBATIS一词来源于“internet”和“abatis”的组合，是一个基于Java的持久层框架。iBATIS提供的持久层框架包括SQL Maps和Data Access Objects（DAO）。

MyBatis 是支持普通 SQL查询，存储过程和高级映射的优秀持久层框架。MyBatis 消除了几乎所有的JDBC代码和参数的手工设置以及结果集的检索。MyBatis 使用简单的 XML或注解用于配置和原始映射，将接口和 Java 的POJOs（Plain Ordinary Java Objects，普通的 Java对象）映射成数据库中的记录。

mybatis入门示例和xml配置

准备数据

create database mybatisstu;
use mybatisstu;

create table mbuser( id int not null auto_increment primary key,
userCode varchar(32),
userName varchar(32),
userpassword varchar(32),
gender int,
birthday varchar(32),
userHole int
);

create table mbrole(
id int not null auto_increment primary key,
roleCode varchar(32),
roleName varchar(32),
createBy int,
creationDate date,
modifyBy int,
modifyDate date
)
	
	
insert into mbuser(userCode,userName,userpassword,gender,birthday,userHole)
values
("001","admin","admin",1,"1982-12-12",1),
("002","leader","leader",1,"1998-10-12",2),
("003","leader2","leader2",0,"1978-2-12",2),
("004","worker1","worker1",1,"1979-09-12",3),	
("005","worker2","worker2",1,"1988-06-12",3)	
	
	
insert into mbrole(roleCode,roleName,createBy,creationDate)
values("r0001","admin",1,now()),
("r002","leader",1,now()),
("r003","worker",1,now());

创建一个工程

pom.xml

 <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.5.6</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.38</version>
    </dependency>
    <dependency>
      <groupId>com.oracle</groupId>
      <artifactId>ojdbc8</artifactId>
      <version>12.2.0.1</version>
    </dependency>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>RELEASE</version>
      <scope>compile</scope>
    </dependency>

创建resources资源文件夹

在内创建database.properties文件

内容如下：

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.119.250:3306/mybatisstu?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
user=root
password=ok

创建mybatis-config.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">
<!-- 可以配置多个运行环境，但是每个 SqlSessionFactory 实例只能选择一个运行环境  
  一、development:开发模式
   二、work：工作模式-->
<configuration>
    <properties resource="database.properties"></properties>
    <!--刚刚创建的文件，配置环境用-->
    <typeAliases>
        <package name="nj.zb.kgc.pojo"/>
    </typeAliases>
    <!-- typeAliases配置包路径，然后引用类时就不需要写全路径例如 mybatis.test.user 直接写user-->
    <environments default="default"><!--使用id为default的-->
        <environment id="default">
            <transactionManager type="JDBC"></transactionManager>
             <!--dataSource 元素使用标准的 JDBC 数据源接口来配置 JDBC 连接对象源  -->
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${user}"/>
                <property name="password" value="${password}"/>
            </dataSource>
            
        </environment>
        <--
        <environment id="oracledev">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                    <property name="driver" value="${oracledriver}"/>
                    <property name="url" value="${oracleurl}"/>
                    <property name="username" value="${oracleuser}"/>
                    <property name="password" value="${oraclepassword}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/RoleDao.xml"/>
        <!--向 mybatis-config.xml 配置文件中注册 RoleDao.xml 文件-->
        <mapper resource="mapper/UserDao.xml"/>
        <!--向 mybatis-config.xml 配置文件中注册 UserDao.xml文件-->
    </mappers>

</configuration>

定义表所对应的实体类

mbuser表

package nj.zb.kgc.pojo;

/**
 * @author zhouhu
 * @Date
 * @Desription
 */

public class User {
    private Integer id;
    private String userCode;
    private String userName;
    private String userPassword;
    private Integer gender;
    private String birthday;
    private Role role; //关系对应，一个用户对应一个角色

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", userCode='" + userCode + '\'' +
                ", userName='" + userName + '\'' +
                ", userPassword='" + userPassword + '\'' +
                ", gender=" + gender +
                ", birthday='" + birthday + '\'' +
                ", role=" + role +
                '}';
    }

    public User() {
    }

    public User(Integer id, String userCode, String userName, String userPassword, Integer gender, String birthday, Role role) {
        this.id = id;
        this.userCode = userCode;
        this.userName = userName;
        this.userPassword = userPassword;
        this.gender = gender;
        this.birthday = birthday;
        this.role = role;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer 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 Integer getGender() {
        return gender;
    }

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

    public String getBirthday() {
        return birthday;
    }

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

    public Role getRole() {
        return role;
    }

    public void setRole(Role role) {
        this.role = role;
    }
}

mbrole表

package nj.zb.kgc.pojo;

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

/**
 * @author zhouhu
 * @Date
 * @Desription
 */

public class Role {
    private Integer id;
    private String roleCode;
    private String roleName;
    private Integer createBy;
    private Date creationDate;
    private Integer modifyBy;
    private Date modifyDate;

//     很多user 对应一个role
    private List<User> users;

    public List<User> getUsers() {
        return users;
    }

    public void setUsers(List<User> users) {
        this.users = users;
    }

    public Role() {
    }

    public Role(Integer id, String roleCode, String roleName, Integer createBy, Date creationDate, Integer modifyBy, Date modifyDate) {
        this.id = id;
        this.roleCode = roleCode;
        this.roleName = roleName;
        this.createBy = createBy;
        this.creationDate = creationDate;
        this.modifyBy = modifyBy;
        this.modifyDate = modifyDate;
    }

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", roleCode='" + roleCode + '\'' +
                ", roleName='" + roleName + '\'' +
                ", createBy=" + createBy +
                ", creationDate=" + creationDate +
                ", modifyBy=" + modifyBy +
                ", modifyDate=" + modifyDate +
                '}';
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer 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 Integer getCreateBy() {
        return createBy;
    }

    public void setCreateBy(Integer createBy) {
        this.createBy = createBy;
    }

    public Date getCreationDate() {
        return creationDate;
    }

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

    public Integer getModifyBy() {
        return modifyBy;
    }

    public void setModifyBy(Integer modifyBy) {
        this.modifyBy = modifyBy;
    }

    public Date getModifyDate() {
        return modifyDate;
    }

    public void setModifyDate(Date modifyDate) {
        this.modifyDate = modifyDate;
    }
}

创建UserDao和RoleDao接口

RoleDao

package nj.zb.kgc.dao;

import nj.zb.kgc.pojo.Role;
import org.apache.ibatis.annotations.Param;

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

public interface RoleDao {
    List<Role> getAll(); //select * from mrole;//查
    Integer addRole(Role role);//增
    Integer updateRole(Role role);//改
    Integer deleteRole(Integer integer);//删

    //查询角色的同时，一并查出角色对应的用户列表
    List<Role> getAll2();
    //根据传入的roleName查询角色信息
    List<Role> getRoleByName(String roleName);

    /*
    根据条件查询Role信息
    roleName 模糊查询
    createBy 创建人ID
    order 排序 创建时间 1、倒叙 2、升序
    return
     */

    List<Role> getRoleByCondition(@Param("roleName") String roleName,
                                  @Param("createBy") Integer createBy,
                                  @Param("order") Integer order);
	//动态sql
	//动态插入
    Integer addRole2(Role role);
    //动态修改
    Integer updateRole2(Role role);
    //动态删除
    Integer deleteRole2(@Param("ids") List<Integer> aa);
	//批量插入
    public Integer batchAdd(@Param("roles") List<Role> roles);
	//批量更改
    public Integer batchUpdate(@Param("roles") List<Role> roles);
}

UserDao

package nj.zb.kgc.dao;

import nj.zb.kgc.pojo.User;

import java.util.List;

public interface UserDao {
//查询所有用户和对应角色
    List<User> getAllUsers();
    //根据userName查询
    User getUserByName(String userName);
}

定义操作两张表的sql映射文件

mbuser表的UserDao.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="nj.zb.kgc.dao.UserDao">
    <select id="getAllUsers" resultMap="userMap">
    <include refid="findUser"></include>
    </select>
    <resultMap id="userMap" type="User">
        <id property="id" column="id"></id>
        <result property="userCode" column="userCode"></result>
        <result property="userName" column="userName"></result>
        <result property="userPassword" column="userpassword"></result>
        <result property="gender" column="gender"></result>
        <result property="birthday" column="birthday"></result>
        <association property="role" javaType="Role">
            <id property="id" column="id"></id>
            <result property="roleCode" column="roleCode"></result>
            <result property="roleName" column="roleName"></result>
            <result property="createBy" column="createBy"></result>
            <result property="creationDate" column="creationDate"></result>
            <result property="modifyBy" column="modifyBy"></result>
            <result property="modifyDate" column="modifyDate"></result>
        </association>
    </resultMap>
    <select id="getUserByName" resultMap="userMap">
    <include refid="findUser"></include>
    where u.`userName`=#{userName}
    </select>
    <sql id="findUser">
      select
u.`id`,u.`userCode`,u.`userName`,u.`userpassword`,u.`gender`,u.`birthday`,u.`userHole`,
r.`id`,r.`roleCode`,r.`roleName`,r.`createBy`,r.`creationDate`,r.`modifyBy`,r.`modifyDate`
from `mbuser` u left join `mbrole` r on r.`id`=u.`userHole`

    </sql>
</mapper>

mbrole表的RoleDao.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="nj.zb.kgc.dao.RoleDao">
    <select id="getAll" resultType="Role">
      select
        `id`,`roleCode`,`roleName`,`createBy`,`creationDate`,`modifyBy`,`modifyDate`
      from `mbrole`;
    </select>

    <select id="getAllOracle" resultType="role">
      select id,roleCode,roleName,createBy,creationDate,modifyBy,modifyDate from mbrole
    </select>

    <insert id="addRole">
        insert into `mbrole`(`id`,`roleCode`,`roleName`,`createBy`,`creationDate`,`modifyBy`,`modifyDate`)
        values(null,#{roleCode},#{roleName},#{createBy},#{creationDate},#{modifyBy},#{modifyDate})
    </insert>

    <update id="updateRole">
        update `mbrole` set
        `roleCode`=#{roleCode},
        `roleName`=#{roleName},
        `modifyBy`=#{modifyBy},
        `modifyDate`=#{modifyDate}
    </update>
    <delete id="deleteRole">
        delete from `mrole` where `id`=#{roleId}
    </delete>
    <select id="getAll2" resultMap="roleMap">
      select
r.`id`,r.`roleCode`,r.`roleName`,r.`createBy`,r.`creationDate`,r.`modifyBy`,r.`modifyDate`,
u.`id`,u.`userCode`,u.`userName`,u.`userpassword`,u.`gender`,u.`birthday`,u.`userHole`
from `mbrole` r left join `mbuser` u on r.`id`=u.`userHole`
    </select>
    <resultMap id="roleMap" type="Role">
        <id property="id" column="id"></id>
        <result property="roleCode" column="roleCode"></result>
        <result property="roleName" column="roleName"></result>
        <result property="createBy" column="createBy"></result>
        <result property="creationDate" column="creationDate"></result>
        <result property="modifyBy" column="modifyBy"></result>
        <result property="modifyDate" column="modifyDate"></result>
        <collection property="users" ofType="User">
            <id property="id" column="id"></id>
            <result property="userCode" column="userCode"></result>
            <result property="userName" column="userName"></result>
            <result property="userPassword" column="userPassword"></result>
            <result property="gender" column="gender"></result>
            <result property="birthday" column="birthday"></result>
        </collection>
    </resultMap>
    <select id="getRoleByName" resultType="Role">
         select
        `id`,`roleCode`,`roleName`,`createBy`,`creationDate`,`modifyBy`,`modifyDate`
      from `mbrole` where `roleName`=#{roleName}
    </select>
    <select id="getRoleByCondition" resultType="Role">
    select
        `id`,`roleCode`,`roleName`,`createBy`,`creationDate`,`modifyBy`,`modifyDate`
      from `mbrole`
      <where>
          <if test="roleName!=null and roleName!=''" >
              and `roleName` like concat(%,#{roleName},%)
          </if>
          <if test="createBy!=null">
              and `createBy`=#{createBy}
          </if>
      </where>
      <choose>
          <when test="order==1">
              order by `creationDate` desc
          </when>
          <when test="order==2">
              order by `creationDate` asc
          </when>
          <otherwise>
              order by `id` desc
          </otherwise>
      </choose>
    </select>

    <!--insert into `mbrole`(`id`,`roleCode`,`roleName`,`createBy`,`creationDate`,`modifyBy`,`modifyDate`)-->
    <!--values(null,#{roleCode},#{roleName},#{createBy},#{creationDate},#{modifyBy},#{modifyDate})-->

    <insert id="addRole2">
        insert into `mbrole`
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="roleCode!=null and roleCode!=''">
                `roleCode`,
            </if>
            <if test="roleName!=null and roleName!=''">
                `roleName`,
            </if>
            <if test="createBy!=null">
                `createBy`,
            </if>
            <if test="creationDate!=null">
                `creationDate`,
            </if>
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="roleCode!=null and roleCode!=''">
                #{roleCode},
            </if>
            <if test="roleName!=null and roleName!=''">
                #{roleName},
            </if>
            <if test="createBy!=null">
                #{createBy},
            </if>
            <if test="creationDate!=null">
                #{creationDate},
            </if>
        </trim>



    </insert>
    <update id="updateRole2">
        update `mbrole`
        <set>
            <if test="roleCode!=null and roleCode!=''">
                `roleCode`=#{roleCode},
            </if>
            <if test="roleName!=null and roleName!=''">
                `roleName`=#{roleName},
            </if>
            <if test="modifyBy!=null">
                `modifyBy`=#{modifyBy},
            </if>
            <if test="modifyDate!=null">
                `modifyDate`=#{modifyDate},
            </if>
        </set>
        where `id`=#{id}
    </update>
    <delete id="deleteRole2">
        delete  from `mbrole` where `id` in
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>

    <insert id="batchAdd">
        insert into `mbrole` (`roleCode`,`roleName`,`createBy`,`creationDate`)
        values
        <foreach collection="roles" item="role" separator=",">
            (#{role.roleCode},#{role.roleName},#{role.createBy},#{role.creationDate})
        </foreach>
    </insert>
    <update id="batchUpdate">
        <foreach collection="roles" item="role">
            update `mbrole`
            <set>
                <if test="role.roleCode!=null and role.roleCode!='' ">
                    `roleCode`=#{role.roleCode},
                </if>
                <if test="role.roleName!=null and role.roleName!='' ">
                    `roleName`=#{role.roleName},
                </if>
                <if test="role.modifyBy!=null">
                    `modifyBy`=#{role.modifyBy},
                </if>
                <if test="role.modifyDate!=null and role.modifyDate!='' ">
                    `modifyDate`=#{role.modifyDate},
                </if>
            </set>
            where `id`=#{role.id};
        </foreach>
    </update>
</mapper>

测试类

package nj.zb.kgc.test;

import nj.zb.kgc.dao.RoleDao;
import nj.zb.kgc.dao.UserDao;
import nj.zb.kgc.pojo.Role;
import nj.zb.kgc.pojo.User;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author zhouhu
 * @Date
 * @Desription
 */

public class TestMybatis {
    SqlSessionFactory sessionFactory =null;
    SqlSession sqlSession=null;
    RoleDao roleDao=null;
    UserDao userDao=null;

    @Before
    public void start(){
        InputStream resource =
                TestMybatis.class.getClassLoader().getResourceAsStream("mybatis-config.xml");
        SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
        sessionFactory= builder.build(resource);
        sqlSession = sessionFactory.openSession();
        roleDao = sqlSession.getMapper(RoleDao.class);
        userDao = sqlSession.getMapper(UserDao.class);
    }

    @Test
    public void test(){
        System.out.println(sqlSession);
        System.out.println("roledao:"+roleDao);
    }

    @Test
    public void testGetAllUser(){
        List<User> users = userDao.getAllUsers();
        for (User user : users) {
            System.out.println(user.toString());
            Role role = user.getRole();
            System.out.println(role.toString());
        }
    }

    @Test
    public void testGetRoleByCondition(){
        List<Role> roles = roleDao.getRoleByCondition(null,1,3);
        for (Role role : roles) {
            System.out.println(role.toString());
        }
    }


    @Test
    public void testGetUserByName(){
        User user = userDao.getUserByName("admin");
        System.out.println(user.toString());
        System.out.println(user.getRole().toString());
    }

    @Test
    public void testAddRole2(){
        Role role = new Role();
        role.setRoleCode("raabbcc");
        Integer integer = roleDao.addRole2(role);
        System.out.println(integer);
    }

    @Test
    public void testUpdateRole2(){
        Role role = new Role();
        role.setId(4);
        role.setRoleName("temp");
        role.setModifyBy(1);
        role.setModifyDate(new Date());
        role.setRoleCode("r0004");
        Integer integer = roleDao.updateRole2(role);
        System.out.println(integer);
    }
    @Test
    public void testDeleteRole2(){
        List<Integer> ids=new ArrayList<>();
        ids.add(4);
        ids.add(5);
        Integer integer = roleDao.deleteRole2(ids);
        System.out.println(integer);
    }

    @Test
    public void testBatchAdd(){
        ArrayList<Role> roles = new ArrayList<>();
        Role role1 = new Role();
        role1.setRoleCode("role1test");
        role1.setRoleName("role1test");
        role1.setCreateBy(1);
        role1.setCreationDate(new Date());

        Role role2 = new Role();
        role2.setRoleCode("role2test");
        role2.setRoleName("role2test");
        role2.setCreateBy(2);
        role2.setCreationDate(new Date());

        roles.add(role1);
        roles.add(role2);

        Integer integer = roleDao.batchAdd(roles);
        System.out.println(integer);
    }

    @Test
    public void testbatchUpdate(){
        ArrayList<Role> roles = new ArrayList<>();
        Role role1 = new Role();
        role1.setId(6);
        role1.setRoleCode("role1batchUpdate");
        role1.setRoleName("role1batchUpdate");
        role1.setCreateBy(1);
        role1.setCreationDate(new Date());

        Role role2 = new Role();
        role2.setId(7);
        role2.setRoleCode("role2batchUpdate");
        role2.setRoleName("role2batchUpdate");
        role2.setCreateBy(2);
        role2.setCreationDate(new Date());

        roles.add(role1);
        roles.add(role2);
        Integer integer = roleDao.batchUpdate(roles);
        System.out.println(integer);
    }

    @Test
    public void getRoleByName(){
        List<Role> testRole = roleDao.getRoleByName("testRole");
        for (Role role : testRole) {
            System.out.println(role.toString());
        }

    }

    @Test
    public void testGetAll(){
        List<Role> roles = roleDao.getAllOracle();
        for (Role r:
            roles ) {
            System.out.println(r.toString());
        }
    }

    public void testDeleteRole(){
        Integer integer = roleDao.deleteRole(5);
        System.out.println(integer);
    }

    @Test
    public void testGetAll2(){
        List<Role> roles = roleDao.getAll2();
        for (Role r :
                roles) {
            System.out.println(r.toString());
            List<User> users = r.getUsers();
            if(users!=null){
                for (User user : users) {
                    System.out.println(user.toString());
                }
            }
        }
    }

    @Test
    public void testUpdate(){
        Role role = new Role();
        role.setId(5);
        role.setRoleCode("r0010");
        role.setRoleName("testRole");
        role.setModifyBy(1);
        role.setModifyDate(new Date());

        Integer integer = roleDao.updateRole(role);
        System.out.println(integer);


    }


    @Test
    public void testAddRole(){
        Role role = new Role();
        role.setRoleCode("0010");
        role.setRoleName("test");
        role.setCreateBy(1);
        role.setCreationDate(new Date());
        Integer integer = roleDao.addRole(role);
        System.out.println(integer);
    }

    @After
    public void end(){
        sqlSession.commit();
        sqlSession.close();
        System.out.println("Over");
    }
}