Mybatis快速入门

Maven依赖

<dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.2.2</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.38</version>
    </dependency>

配置文件
database.properties

--数据库参数
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.56.12:3306/mybatis?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
username=root
password=ok
oracledriver=oracle.jdbc.driver.OracleDriver
oracleurl=jdbc:oracle:thin:@192.168.56.12:1521:prod
oracleusername=zhangpeng
oraclepassword=zp123456

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">
<configuration>
    <properties resource="database.properties"></properties>

    <typeAliases>
        <package name="cn.kgc.kb11.pojo"/>
    </typeAliases>

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

        <environment id="oracledev">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${oracledriver}"></property>
                <property name="url" value="${oracleurl}"></property>
                <property name="username" value="${oracleusername}"></property>
                <property name="password" value="${oraclepassword}"></property>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/roledao.xml"></mapper>
        <mapper resource="mapper/userdao.xml"></mapper>
    </mappers>
</configuration>

以User和Role两张表为例

创建User和Role两张表

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),
userRole int
);

CREATE TABLE mbrole(
rid int not NULL AUTO_INCREMENT PRIMARY KEY,
roleCode varchar(32),
roleName varchar(32),
createBy int,
createDate date,
modifyBy int,
modifyDate date
);

INSERT INTO mbuser(userCode,userName,userPassword,gender,birthday,userRole)
VALUES("0001","admin","admin",1,"1996-01-13",1),
("0002","leader","leader",1,"1995-01-15",2),
("0003","leader2","leader2",0,"1993-01-13",2),
("0004","worker1","worker1",1,"1996-01-24",3),
("0005","worker2","worker2",0,"1996-04-23",3);


INSERT INTO mbrole(roleCode,roleName,createBy,createDate)
VALUES("r0001","admin",1,NOW()),
("r0002","leader",1,NOW()),
("r0003","worker",1,NOW());

User实体类

package cn.kgc.kb11.pojo;

import java.util.Date;

/**
 * @Author ZhangPeng
 * @Date 2021/7/13
 * @Description
 */
public class User {
    private Integer id;
    private String userCode;
    private String userName;
    private String userPassword;
    private Integer gender;
    private Date birthday;
    private Role role;

    public User() {
    }

    public User(Integer id, String userCode, String userName, String userPassword, Integer gender, Date 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 Date getBirthday() {
        return birthday;
    }

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

    public Role getRole() {
        return role;
    }

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

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

Role实体类

package cn.kgc.kb11.pojo;

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

/**
 * @Author ZhangPeng
 * @Date 2021/7/13
 * @Description
 */
public class Role {
    private Integer id;
    private String roleCode;
    private String roleName;
    private Integer createBy;
    private Date createDate;
    private Integer modifyBy;
    private Date modifyDate;

    private List<User> users;

    public Role() {
    }

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

    public Role(Integer id, String roleCode, String roleName, Integer createBy, Date createDate, Integer modifyBy, Date modifyDate) {
        this.id = id;
        this.roleCode = roleCode;
        this.roleName = roleName;
        this.createBy = createBy;
        this.createDate = createDate;
        this.modifyBy = modifyBy;
        this.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 getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }

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

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

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

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

映射

RoleDao.java

package cn.kgc.kb11.dao;

import cn.kgc.kb11.pojo.Role;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @Author ZhangPeng
 * @Date 2021/7/13
 * @Description
 */
public interface RoleDao {
    List<Role> getAll();
    List<Role> getAll2();
    void addRole(Role role);
    Integer updateRole(Role role);
    Integer deleteRole(Role role);

    Role getRoleByName(String roleName);

    List<Role> getRolesByCondition(@Param("roleName") String roleName,
                                   @Param("createBy") Integer createBy,
                                   @Param("order") Integer order);

    Integer addRole2(Role role);

    Integer updateRole2(Role role);

    Integer deleteRole2(@Param("ids") List<Integer> ids);

    Integer batchAdd(@Param("roles") List<Role> roles);

    Integer batchUpdate(@Param("roles") List<Role> roles);
}

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="cn.kgc.kb11.dao.RoleDao">
    <select id="getAll" resultType="Role">
        SELECT
        `id`,`roleCode`,`roleName`,`createBy`,
        `createDate`,`modifyBy`,`modifyDate`
        FROM `mbrole`
    </select>

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

    <update id="updateRole">
        update `mbrole` set `roleCode`=#{roleCode},
        `roleName`=#{roleName},
        `modifyBy`=#{modifyBy},
        `modifyDate`=#{modifyDate} where `id`=#{id}
    </update>

    <select id="getAll2" resultMap="roleMap">
       SELECT
      r.`rid`,r.`roleCode`,r.`roleName`,r.`createBy`,
      r.`createDate`,r.`modifyBy`,r.`modifyDate`,
      u.`id`,u.`userCode`,u.`userName`,u.`gender`,u.`birthday`
      FROM `mbrole` r LEFT JOIN `mbuser` u ON r.`rid`=u.`userRole` ;
    </select>

    <resultMap id="roleMap" type="Role">
        <id property="id" column="rid"></id>
        <result property="roleName" column="roleName"></result>
        <result property="roleCode" column="roleCode"></result>
        <result property="createBy" column="createBy"></result>
        <result property="createDate" column="createDate"></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="gender" column="gender"></result>
            <result property="birthday" column="birthday"></result>
        </collection>
    </resultMap>

    <delete id="deleteRole">
        delete from `mbrole` where `rid`=#{id}
    </delete>
    
    <select id="getRoleByName" resultMap="roleMap">
        <include refid="findRoleSql"></include>
        where r.`roleName`=#{roleName}
    </select>

    <sql id="findRoleSql">
      SELECT
      r.`rid`,r.`roleCode`,r.`roleName`,r.`createBy`,
      r.`createDate`,r.`modifyBy`,r.`modifyDate`,
      u.`id`,u.`userCode`,u.`userName`,u.`gender`,u.`birthday`
      FROM `mbrole` r LEFT JOIN `mbuser` u ON r.`rid`=u.`userRole`
    </sql>
    
    <select id="getRolesByCondition" resultMap="roleMap">
        select
        `rid`,`roleCode`,`roleName`,`createBy`,
        `createDate`,`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 `createDate` desc
            </when>
            <when test="order==0">
                order by `createDate` asc
            </when>
            <otherwise>
                order by `rid` desc
            </otherwise>
        </choose>
    </select>

    <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="createDate!=null">
                `createDate`,
            </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="createDate!=null">
                #{createDate},
            </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="createBy!=null ">
                `createBy`=#{createBy},
            </if>
            <if test="createDate!=null and createDate!='' ">
                `createDate`=#{createDate}
            </if>
        </set>
        where `rid`=#{id}
    </update>

    <delete id="deleteRole2">
        delete from `mbrole` where `rid` in
        <foreach collection="ids" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </delete>
    
    <insert id="batchAdd">
        insert into `mbrole`(`roleCode`,`roleName`,`createBy`,`createDate`)
        values
        <foreach collection="roles" item="role" separator=",">
            (#{role.roleCode},#{role.roleName},#{role.createBy},#{role.createDate})
        </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.createBy!=null ">
                    `createBy`=#{role.createBy},
                </if>
                <if test="role.createDate!=null and role.createDate!='' ">
                    `createDate`=#{role.createDate},
                </if>
            </set>
            where `rid`=#{role.id}
        </foreach>
    </update>
</mapper>

UserDao.java

package cn.kgc.kb11.dao;

import cn.kgc.kb11.pojo.User;

import java.util.List;

/**
 * @Author ZhangPeng
 * @Date 2021/7/14
 * @Description
 */
public interface UserDao {
    List<User> getAllUser();

    User getUserByName(String name);
}

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="cn.kgc.kb11.dao.UserDao">
    <select id="getAllUser" resultMap="userMap">
        <include refid="findUserSql"></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="rid"></id>
            <result property="roleCode" column="roleCode"></result>
            <result property="roleName" column="roleName"></result>
            <result property="createBy" column="createBy"></result>
            <result property="createDate" column="createDate"></result>
            <result property="modifyBy" column="modifyBy"></result>
            <result property="modifyDate" column="modifyDate"></result>
        </association>
    </resultMap>

    <select id="getUserByName" resultMap="userMap">
      <include refid="findUserSql"></include>
      where u.`userName`=#{name};
    </select>
    
    <sql id="findUserSql">
      SELECT
      u.`id`,u.`userCode`,u.`userName`,u.`userPassword`,u.`gender`,u.`birthday`,u.`userRole`,
      r.`rid`,r.`roleCode`,r.`roleName`,r.`createBy`,
      r.`createDate`,r.`modifyBy`,r.`modifyDate`
      FROM `mbuser` u left JOIN `mbrole` r ON r.`rid`=u.`userRole`
    </sql>
</mapper>

测试类

TestMybatis.java

package cn.kgc.kb11.test;

import cn.kgc.kb11.dao.RoleDao;
import cn.kgc.kb11.dao.UserDao;
import cn.kgc.kb11.pojo.Role;
import cn.kgc.kb11.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 javax.management.relation.RoleInfo;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @Author ZhangPeng
 * @Date 2021/7/13
 * @Description
 */
public class TestMybatis {
    SqlSessionFactory sessionFactory = null;
    SqlSession sqlSession = null;
    RoleDao roleDao;
    UserDao userDao;

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

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

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

    @Test
    public void testAddRole(){
        Role role = new Role();
        role.setRoleCode("0010");
        role.setRoleName("test");
        role.setCreateBy(1);
        role.setCreateDate(new Date());
        roleDao.addRole(role);
    }

    @Test
    public void testUpdateRole(){
        Role role = new Role();
        role.setId(2);
        role.setRoleName("r0010");
        role.setRoleCode("r0010");
        role.setCreateBy(1);
        role.setCreateDate(new Date());

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

    @Test
    public void testDeleteRole(){
        Role role = new Role();
        role.setId(7);
        Integer integer = roleDao.deleteRole(role);
        System.out.println(integer);
    }

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

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

    @Test
    public void testGetRoleByName(){
        Role role = roleDao.getRoleByName("leader");
        System.out.println(role);
        List<User> users = role.getUsers();
        for (User user : users) {
            System.out.println(user);
        }
    }

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

    @Test
    public void testAddRole2(){
        Role role = new Role();
        role.setRoleCode("aabbcc2");
        role.setCreateBy(2);
        Integer i = roleDao.addRole2(role);
        System.out.println(i);
    }

    @Test
    public void testUpdateRole2(){
        Role role = new Role();
        role.setId(1);
        role.setCreateBy(2);
        role.setCreateDate(new Date());
        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();
        Role role2 = new Role();

        role1.setRoleCode("aabb");
        role1.setRoleName("aabb");
        role1.setCreateBy(1);
        role1.setCreateDate(new Date());

        role2.setRoleCode("aabb2");
        role2.setRoleName("aabb2");
        role2.setCreateBy(2);
        role2.setCreateDate(new Date());

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

        roleDao.batchAdd(roles);
    }

    @Test
    public void testBatchUpdate(){
        ArrayList<Role> roles = new ArrayList<>();
        Role role1 = new Role();
        Role role2 = new Role();

        role1.setId(6);
        role1.setRoleCode("aabb3");
        role1.setRoleName("aabb3");
        role1.setCreateBy(1);
        role1.setCreateDate(new Date());

        role2.setId(7);
        role2.setRoleCode("aabb4");
        role2.setRoleName("aabb4");
        role2.setCreateBy(2);
        role2.setCreateDate(new Date());

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

        roleDao.batchUpdate(roles);
    }

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

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