Author: Mi Shu

What is Mybatis?

Mybatis is a persistence layer framework. Java through Mybatis, procedures can be very convenient to operate the database. Enables developers to focus on SQL statements and avoids repetitive JDBC code.

  • Using MyBatis avoids almost all of the JDBC code and manual setting of parameters and fetching result sets
  • Flexible and powerful, with support for customized SQL, stored procedures, and advanced mappings
  • SQL can be written in XML and annotations
  • Records in a database can be mapped to plain Java objects

To prepare

Environment:

Development tool: IDEA

Package management: Maven

The JDK: 1.8

Mybatis ：Mybatis 3.4.6

Problem scenario

Use Mybatis to complete the related operation and query of database table based on simple permission design

Table involved

T_user T_user_info T_ROLE T_menu t_user_role // Relational table T_ROLE_menu // Relational tableCopy the code

What operations to perform

  • T_user table simple add and delete change
  • Associate complex queries to query all permissions of a user
  • Dynamic query, which contains users with certain menu permissions

1. Create a new database table and initialize some data

-- ----------------------------
-- Table structure for `t_menu`
-- ----------------------------
DROP TABLE IF EXISTS `t_menu`;
CREATE TABLE `t_menu` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL.`type` int(11) DEFAULT NULL.`url` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`))ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for `t_role`
-- ----------------------------
DROP TABLE IF EXISTS `t_role`;
CREATE TABLE `t_role` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`))ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for `t_role_menu`
-- ----------------------------
DROP TABLE IF EXISTS `t_role_menu`;
CREATE TABLE `t_role_menu` (
  `role_id` int(10) DEFAULT NULL.`menu_id` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for `t_user`
-- ----------------------------
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL.`password` varchar(50) DEFAULT NULL.`nickname` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `t_user_username_uindex` (`username`))ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for `t_user_role`
-- ----------------------------
DROP TABLE IF EXISTS `t_user_role`;
CREATE TABLE `t_user_role` (
  `user_id` int(10) DEFAULT NULL.`role_id` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------
-- Table structure for `t_user_info`
-- ----------------------------
DROP TABLE IF EXISTS `t_user_info`;
CREATE TABLE `t_user_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL.`name` varchar(100) COLLATE utf8_bin DEFAULT NULL.`email` varchar(100) COLLATE utf8_bin DEFAULT NULL.`address` varchar(255) COLLATE utf8_bin DEFAULT NULL.`phone` varchar(20) COLLATE utf8_bin DEFAULT NULL.`sex` varchar(2) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`))ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

SET FOREIGN_KEY_CHECKS = 1;
Copy the code

Manually initialize some data and build some associated data for the following tests

2. Build the Maven project

Organize the Project directory

└ ─ ─ the SRC ├ ─ ─ the main │ ├ ─ ─ Java │ │ └ ─ ─ the me │ │ └ ─ ─ imcoding │ │ ├ ─ ─ dao │ │ ├ ─ ─ mapper │ │ ├ ─ ─ model │ └ ─ ─ resources │ └ ─ ─ Mapper └ ─ ─test└── garbage ─ garbageCopy the code

3. Introduce Mybatis dependencies

In a Maven environment, you need to add dependencies in the POM.xml file

 <dependencies>
    <! - introduction of Mybatis -- -- >
    <dependency>
      <groupId>org.mybatis</groupId>
      <artifactId>mybatis</artifactId>
      <version>3.4.6</version>
    </dependency>
    <! MySQL > create MySQL driver
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.46</version>
    </dependency>
     <! JSON toolkit -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>1.2.31</version>
    </dependency>
    <! -- Unit tests -->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
  </dependencies>

Copy the code

4. Configure Mybatis

Create mybatis-config.xml in the Resources directory

└─ Resources ├─ ├─ mybatis-config.xmlCopy the code

Configure the following

<?xml version="1.0" encoding="UTF-8" ? >

      
<configuration>
	<! -- Set the properties file -->
    <properties resource="dbconfig.properties"></properties>
 	<! -- Default configuration environment: development-->
    <environments default="development">
        <! -- Development environment Configuration -->
        <environment id="development">
            <! -- Transaction manager configuration -->
            <transactionManager type="JDBC"/>
            <! -- Configure data source -->
            <dataSource type="POOLED">
                <! -- Dynamic substitution of attribute values -->
                <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 = Mapper;
    </mappers>

</configuration>
Copy the code

For external property configuration, add dbconfig.properties file in resources directory

└─ Resources ├─ ├─ ├─ mybatis-config.xmlCopy the code

Configuration contents:

. Driver = com. Mysql. JDBC driver url = JDBC: mysql: / / 127.0.0.1:3306 / mybatis - demo? useUnicode=true&characterEncoding=utf8 username=root password=rootCopy the code

Attribute information about database connections

5. Obtain SqlSession

Core code:

// Config file location
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = factory.openSession();
Copy the code

Write a utility class MybatisUtils:

├── Java ├─ ├─ Java ├─ Java ├─ Java ├─Copy the code
public class MybatisUtils {

    private static SqlSessionFactory factory;

    /** * Initializes factory */ according to the mybatis-config.xml configuration
    private static void initialFactory(a) {
        String resource = "mybatis-config.xml";
        try {
            InputStream in = Resources.getResourceAsStream(resource);
            factory = new SqlSessionFactoryBuilder().build(in);
        } catch(IOException e) { e.printStackTrace(); }}public static SqlSession getSession(a) {
        if (factory == null) {
            initialFactory();
        }
        OpenSession (true) - Enables automatic transaction commits
        SqlSession sqlSession = factory.openSession();
        returnsqlSession; }}Copy the code

Basic Add, Delete, Modify and check (CURD)

1. Build the Model class

Directory:

├── Model ├─ Menu. Java ├─ RoleCopy the code

User

public class User {
    private Integer id;
    private String username;
    private String password;
    private String nickname;

   	/* The setter and getter omit */
}
Copy the code

Role

public class Role {
    private Integer id;
    private String name;
    
    // Setters and getters omit...
}
Copy the code

Menu

public class Menu {
    private Integer id;
    private String name;
    private int type;
    private String url;
    
    // setter and getter ...    
}
Copy the code

UserInfo

public class UserInfo {

    private Integer id;
    private String name;
    private String sex;
    private String address;
    private String phone;
    private String email;
    
    //setter and getter....
}
Copy the code

2. Build a Mapper interface

Directory:

├ ─ ─ mapper └ ─ ─ UserMapper. JavaCopy the code

UserMapper

public interface UserMapper {

    / / the new User
    int insertUser(User user);

    // Delete by primary key
    int deleteUser(int id);

    / / update User
    int updateUser(User user);

    // Query by primary key ID
    User selectByKey(int id);

    // Query by user name and password
    User selectByUsernameAndPassword(String username, String password);
  
}
Copy the code

3. Define Mapper XML

New UserMapper. XML

└ ─ ─ resources ├ ─ ─ dbconfig. Properties ├ ─ ─ mapper │ └ ─ ─ UserMapper. XML └ ─ ─ mybatis - config. XMLCopy the code

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ? >

      

<! -- Map Mapper interface -->
<mapper namespace="me.imcoding.mapper.UserMapper">

    <! ParameterType: specifies the type of the primary key insertUser() id in the UserMapper table.
    <insert id="insertUser" parameterType="me.imcoding.model.User">
      insert into t_user(username, password, nickname)
          values (#{username}, #{password}, #{nickname})
    </insert>

    <! UserMapper deleteUser()-->
    <delete id="deleteUser" >
        delete from t_user where id = #{id}
    </delete>

    <! UpdateUser () <set> automatically handles dynamic SQL statements -->
    <update id="updateUser" parameterType="me.imcoding.model.User">
        update t_user
        <set>
            <if test="username ! = null">username = #{username}</if>
            <if test="password ! = null">password = #{password}</if>
            <if test="nickname ! = null">nickname = #{nickname}</if>
        </set>
        where id = #{id}
    </update>

    <! SelectUser ()-->
    <select id="selectUser" resultType="me.imcoding.model.User">
        select
          nickname,
          username,
          password
        from t_user
        where id = #{id}
    </select>

    <! In corresponding UserMapper interface selectByUsernameAndPassword () < where > can automatic processing dynamic SQL statements, avoid grammatical errors in the splicing statements -- -- >
    <select id="selectByUsernameAndPassword" resultType="me.imcoding.model.User">
        select
          nickname,
          username,
          password
        from t_user
        <where>
            <if test="username ! = null">
                username = #{username}
            </if>
            <if test="password ! = null">
                and password = #{password}
            </if>
        </where>
    </select>


</mapper>
Copy the code

Don’t forget to introduce usermapper.xml in mybatis-config.xml

mybatis-config.xml

<mappers>
    <! Mapper XML file -->
    <mapper resource="mapper/UserMapper.xml"></mapper>
</mappers>
Copy the code

Build the UserDao

Directory:

├─ ├─ class exercises, class exercises, class exercises, class exercisesCopy the code

UserDao

/** * Author: */
public class UserDao {

    / / new
    public int insertUser(User user) {
        SqlSession sqlSession = MybatisUtils.getSession();
        int bo;
        try {
            // Get Mapper instance from sqlSession
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            // Call the mapper method
            bo = mapper.insertUser(user);
            sqlSession.commit();// Commit the transaction
            return bo;
        } finally {
            // Be sure to close sqlSessionsqlSession.close(); }}/ / delete
    public int deleteUser(int id) {
        SqlSession sqlSession = MybatisUtils.getSession();
        int bo;
        try {
            // Get Mapper instance from sqlSession
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            // Call the mapper method
            bo = mapper.deleteUser(id);
            sqlSession.commit();// Commit the transaction
            return bo;
        } finally {
            // Be sure to close sqlSessionsqlSession.close(); }}/ / update
    public int updateUser(User user) {
        SqlSession sqlSession = MybatisUtils.getSession();
        int bo;
        try {
            // Get Mapper instance from sqlSession
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            // Call the mapper method
            bo = mapper.updateUser(user);
            sqlSession.commit();// Commit the transaction
            return bo;
        } finally {
            // Be sure to close sqlSessionsqlSession.close(); }}/ / query
    public User selectUser(int id) {
        SqlSession sqlSession = MybatisUtils.getSession();
        User user;
        try {
            // Get Mapper instance from sqlSession
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            // Call the mapper method
            user = mapper.selectUser(id);
            sqlSession.commit();// Commit the transaction
            return user;
        } finally {
            // Be sure to close sqlSessionsqlSession.close(); }}// Query based on username and password
    public User selectByUsernameAndPassword(String username, String password) {
        SqlSession sqlSession = MybatisUtils.getSession();
        User user;
        try {
            // Get Mapper instance from sqlSession
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            // Call the mapper method
            user = mapper.selectByUsernameAndPassword(username, password);
            sqlSession.commit();// Commit the transaction
            return user;
        } finally {
            // Be sure to close sqlSessionsqlSession.close(); }}}Copy the code

5, test,

Directory:

└ ─ ─test└── Java ├ ─ imcoding ├ ─Copy the code

AppTest

/** * Author: */
public class UserDao {

    / / new
    public int insertUser(User user) {
        SqlSession sqlSession = MybatisUtils.getSession();
        int bo;
        try {
            // Get Mapper instance from sqlSession
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            // Call the mapper method
            bo = mapper.insertUser(user);
            sqlSession.commit();// Commit the transaction
            return bo;
        } finally {
            // Be sure to close sqlSessionsqlSession.close(); }}/ / delete
    public int deleteUser(int id) {
        SqlSession sqlSession = MybatisUtils.getSession();
        int bo;
        try {
            // Get Mapper instance from sqlSession
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            // Call the mapper method
            bo = mapper.deleteUser(id);
            sqlSession.commit();// Commit the transaction
            return bo;
        } finally {
            // Be sure to close sqlSessionsqlSession.close(); }}/ / update
    public int updateUser(User user) {
        SqlSession sqlSession = MybatisUtils.getSession();
        int bo;
        try {
            // Get Mapper instance from sqlSession
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            // Call the mapper method
            bo = mapper.updateUser(user);
            sqlSession.commit();// Commit the transaction
            return bo;
        } finally {
            // Be sure to close sqlSessionsqlSession.close(); }}/ / query
    public User selectUser(int id) {
        SqlSession sqlSession = MybatisUtils.getSession();
        User user;
        try {
            // Get Mapper instance from sqlSession
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            // Call the mapper method
            user = mapper.selectUser(id);
            sqlSession.commit();// Commit the transaction
            return user;
        } finally {
            // Be sure to close sqlSessionsqlSession.close(); }}// Query based on username and password
    public User selectByUsernameAndPassword(String username, String password) {
        SqlSession sqlSession = MybatisUtils.getSession();
        User user;
        try {
            // Get Mapper instance from sqlSession
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            // Call the mapper method
            user = mapper.selectByUsernameAndPassword(username, password);
            sqlSession.commit();// Commit the transaction
            return user;
        } finally {
            // Be sure to close sqlSessionsqlSession.close(); }}}Copy the code

Complex queries

Query details and menu permissions of a user

SQL:

SELECT
  u.id,
  u.username,
  u.password,
  u.nickname,
  ui.name,
  ui.sex,
  ui.email,
  ui.address,
  ui.phone,
  r.id as role_id,
  r.name as role_name,
  m.id as menu_id,
  m.name as menu_name
FROM
  t_user u
  LEFT outer JOIN t_user_role ur ON u.id = ur.user_id
  LEFT outer JOIN t_role r ON r.id = ur.role_id
  left OUTER JOIN t_role_menu rm ON rm.role_id = r.id
  left outer join t_menu m on m.id = rm.menu_id
  left outer join t_user_info ui on u.id = ui.user_id
WHERE u.username = '[email protected]'
Copy the code

1. Add interface methods

UserMapper.java

public interface UserMapper {

    //....
    
    // Query user details and return results in map mode
    User selectUserInfo(int id);
    
    //....

}

Copy the code

2. Add usermapper. XML query

UserMapper.xml

<mapper>.<select id="selectUserInfo" resultMap="UserResultMap">SELECT u.id, u.username, u.password, u.nickname, ui.id as ui_id, ui.name as ui_name, ui.sex as ui_sex, ui.email as ui_email, ui.address as ui_address, ui.phone as ui_phone, r.id as role_id, r.name as role_name, m.id as menu_id, m.name as menu_name FROM t_user u LEFT outer JOIN t_user_role ur ON u.id = ur.user_id LEFT outer JOIN t_role r ON r.id =  ur.role_id left OUTER JOIN t_role_menu rm ON rm.role_id = r.id left outer join t_menu m on m.id = rm.menu_id left outer  join t_user_info ui on u.id = ui.user_id WHERE u.id = #{id}</select>

    <! Define the mapping of the return result
    <resultMap id="UserResultMap" type="me.imcoding.model.User">
        <! User id = User id
        <constructor>
            <idArg column="id" javaType="int" ></idArg>
        </constructor>
        <! - User attributes - >
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="nickname" property="nickname"></result>
        <! -- userInfo object property -->
        <association property="userInfo" javaType="me.imcoding.model.UserInfo" >
            <! -- Id is important -->
            <id column="ui_id" property="id"></id>
            <result column="ui_name" property="name"></result>
            <result column="ui_sex" property="sex"></result>
            <result column="ui_email" property="email"></result>
            <result column="ui_address" property="address"></result>
            <result column="ui_phone" property="phone"></result>
        </association>

        <! Roles set attribute for User -->
        <collection property="roles" ofType="me.imcoding.model.Role">
            <id column="role_id" property="id"></id>
            <result column="role_name" property="name"></result>
        </collection>
        <! -- Menus set properties of the corresponding User -->
        <collection property="menus" ofType="me.imcoding.model.Menu">
            <id column="menu_id" property="id"></id>
            <result column="menu_name" property="name"></result>
        </collection>

    </resultMap>.</mapper>
Copy the code

New attributes and constructs for the User class

User

public class User {
    
    / /...

    // No arguments
    public User(a) {}
    @param ("id") is the same as the name attribute in mapper XML
    public User(Integer id) {
        this.id = id;
    }

    private UserInfo userInfo;
    private List<Role> roles;
    private List<Menu> menus;



	/ /...
  
}

Copy the code

4. Add UserDao query

UserDao

public class UserDao {

    //....
    // Query all user details
    public User selectUserInfo(int id) {
        SqlSession sqlSession = MybatisUtils.getSession();
        User user;
        try {
            // Get Mapper instance from sqlSession
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            // Call the mapper method
            user = mapper.selectUserInfo(id);
            return user;
        } finally {
            // Be sure to close sqlSessionsqlSession.close(); }}/ /...

}
Copy the code

5, test,

AppTest

public class AppTest {
    
    / /...
    
     @Test
    public void selectUserInfo(a) {
        UserDao dao = new UserDao();
        User user = dao.selectUserInfo(1);
        System.out.println(JSON.toJSONString(user,true));
    }
    
    / /...
}
Copy the code

Results:

{
	"id": 1,"menus":[
		{
			"id": 1,"name":"Site Information"."type":0
		},
		{
			"id": 2."name":"Vehicle Enquiries"."type":0
		},
		{
			"id": 3."name":"People management"."type":0
		},
		{
			"id": 4."name":"User Management"."type":0
		},
		{
			"id": 5,"name":"Role Management"."type":0
		},
		{
			"id": 6,"name":"Menu Management"."type":0
		},
		{
			"id": 7,"name":"Site Maintenance"."type": 0}],"nickname":"All"."password":"111111"."roles":[
		{
			"id": 1,"name":"Administrator"}]."userInfo": {"address":"58 Minhang District, Shanghai"."email":"[email protected]"."id": 1,"name":"Fonda"."phone":"18276546543"."sex":"Male"
	},
	"username":"[email protected]"
}

Copy the code

The source code

mybatis-practice

Mybatis knowledge boundary

  • Mybatis introduced
  • Necessary configuration for Mybatis
  • Mybatis Mapper XML
  • Mybatis dynamic SQL
  • Mybatis SqlSession
  • Mybatis code generation
  • Mybatis cache
  • Mybatis transaction

Learning resources: www.mybatis.org/mybatis-3/z…

Mybatis core knowledge

  • Rules for using Mapper XML mapping files

    Study tips: www.mybatis.org/mybatis-3/z…

  • Processing rules for dynamic SQL

    Study tips: www.mybatis.org/mybatis-3/z…