[Start class javaEE 190802 per day] Basic overview of mybatis and first program

Introduction to mybatis

What is mybatis

  • MyBatis is an excellent persistence layer framework and is a semi-automated ORM framework
  • It encapsulates the process of using JDBC to manipulate the database so that developers only need to focus on the SQL itself rather than on the complex process code of jdbc, such as registering drivers, creating connection s, creating statement s, setting parameters manually, and retrieving result sets.

Where mybatis writes sql statements

  • Mybatis configures the various statements (statement, preparedStatement, CallableStatement) that will be executed through XML or annotations.
  • The final executed sql statement is generated by mapping the sql in the java object to the sql in the statement, and finally executed by the mybatis framework, mapping the result to a java object and returning it

Problems with JDBC code and mybatis resolution

  • Create database connection related operations, hard-coded Solution: Configure database connections through the Mybatis global configuration file

  • statement-related operations, hard-coded Solution: Configure statement-related processing through a Mapper mapping file.

  • Opening database connections frequently reduces database processing performance. Solution: Configure connection pools through Mybatis global profile

mybatis architecture principles

  • mybatis configuration file

    • SqlMapConfig.xml, which serves as the global configuration file for mybatis and configures information such as the running environment of mybatis.
    • Mapper.xml, which acts as the sql mapping file for mybatis and is configured with sql statements that manipulate the database.This file needs to be loaded in SqlMapConfig.xml.
  • SqlSessionFactory

    • The SqlSessionFactory, or session factory, is constructed from configuration information such as the mybatis environment.
  • sqlSession

    • Create a sqlSession, or session, through a session factory. Programmers add, delete, and alter databases through the sqlsession session interface.
  • Executor Executor

    • The mybatis underlying layer customizes the Executor executor interface to operate the database. The Executor interface has two implementations, one is the basic executor (default), one is the cache executor, and the sqlsession underlying layer operates the database through the executor interface.
  • Mapped Statement It is also a low-level encapsulation object for mybatis, which wraps mybatis configuration information, sql mapping information, and so on.A select\insert\updatedelete tag in the mapper.xml file corresponds to a Mapped Statement object. The id of the select\insert\update\delete tag is the id of the Mapped State.

    • Mapped Statement defines input parameters for SQL execution, including HashMap, Basic Type, pojo. Executor maps input java objects to SQL through Mapped Statement before executing sql. Input parameter mapping is setting parameters for preparedStatement in jdbc programming.
    • Mapped Statement defines the output of sql execution, including HashMap, Basic Type, pojo. Executor maps the output to a java object after executing sql through Mapped Statement, which is equivalent to parsing the result in jdbc programming.

Create maven project step in idea

After choosing a file, the new project or new module goes to the following page:

Next, enter the project point group ID, artifacted id, click next, and configure maven

Click finish after configuring, but there are no java, resources, and test directories when you use idea to build a maven project. You need to create a new one yourself for reference Articles by Others

Similarly, if you need a test folder, first create a normal folder in the src directory, then create a java folder and a resources folder in the test directory and set up the corresponding relationship:

The first small case of mybatis

Project Overall Catalog Structure

data base

The table creation statement is:

  `username` varchar(32) NOT NULL COMMENT 'User Name',
  `birthday` date DEFAULT NULL COMMENT 'Birthday',
  `sex` char(1) DEFAULT NULL COMMENT 'Gender',
  `address` varchar(256) DEFAULT NULL COMMENT 'address',
  PRIMARY KEY (`id`)

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'Test 1', null, '2', null);
INSERT INTO `user` VALUES ('10', 'Zhang San', '2014-07-10', '1', 'Beijing');
INSERT INTO `user` VALUES ('11', 'Li Si', null, '1', null);
INSERT INTO `user` VALUES ('16', 'Zhang Xiaoming', null, '1', 'Zhengzhou, Henan');
INSERT INTO `user` VALUES ('22', 'Chen Xiaoming', null, '1', 'Zhengzhou, Henan');
INSERT INTO `user` VALUES ('24', 'Zhang Sanfen', null, '1', 'Zhengzhou, Henan');
INSERT INTO `user` VALUES ('25', 'Chen Xiaoming', null, '1', 'Zhengzhou, Henan');
INSERT INTO `user` VALUES ('26', 'King Five', null, null, null);

mybatis master configuration file SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
    <!--Load Profile-->
    <properties resource="db.properties"></properties>
    <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}"/>
        <mapper resource="UserMapper.xml"/>

Placeholders are used in the main configuration file to connect to the database using jdbc, so you need to write the database configuration file and load the mapper file in the main configuration file of mybatis, so you also need to write the sql mapping file

Database configuration file db.properties


sql mapping file UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN "
<!--namespace The purpose is to classify and manage the MapperdStatement object-->
<mapper namespace="test">
    <select id="findUserById" parameterType="int" resultType="test.User">
    select * from User where id = #{id}


package test;
import java.util.Date;
public class User {
    private int id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;
    public int getId() {
        return id;
    public void setId(int id) {
        this.id = id;
    public String getUsername() {
        return username;
    public void setUsername(String username) {
        this.username = username;
    public Date getBirthday() {
        return birthday;
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    public String getSex() {
        return sex;
    public void setSex(String sex) {
        this.sex = sex;
    public String getAddress() {
        return address;
    public void setAddress(String address) {
        this.address = address;
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday=" + birthday +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +

Test Class

package test;

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 org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;

public class TestUser {

    // Session Factory
    private SqlSessionFactory sqlSessionFactory;

    // This comment can be executed before the @test comment
    public void createSqlSessionFactory() throws IOException {
        // Load Profile
        String resource = "SqlMapConfig.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        // Create sqlsessionfactory from xml using sqlSessionFactoryBuilder
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    public void test() {
        SqlSession sqlSession = null;
        sqlSession = sqlSessionFactory.openSession();
        User user = sqlSession.selectOne("test.findUserById", 1);

The small case for this query has been completed.


Test Fuzzy Query

sql mapping file

Or the file above, just an extension

<!--${value}Indicates that the input parameter will ${value}Replace by splicing strings directly. If you take a simple type of parameter, the parameter name in parentheses must be value-->
    <select id="findUserByUsername" parameterType="java.lang.String" resultType="test.User">
      select * from user where username like '%${value}%'

Test Code

    public void testLike() {
        SqlSession sqlSession = null;
        sqlSession = sqlSessionFactory.openSession();
        List<Object> list = sqlSession.selectList("test.findUserByUsername", "Zhang San");

That's okay, but when writing sql mapping files, you said that if you take a simple type, you can only use value here. For example, if you use hello here, you will get the following error:

There is no hello and no get method shown above, but even if I switch to username (which is in pojo), the query results are incorrect although it does not error.For mybatis fuzzy queries you can see Reference Article

Test Add

sql mapping file

insert id="insertUser" parameterType="test.User">
        insert into user(username, birthday, sex, address)
        values (#{username}, #{birthday}, #{sex}, #{address})

Test Code

    public void testInsert() {
        SqlSession sqlSession = null;
        sqlSession = sqlSessionFactory.openSession();
        User user = new User();
        user.setUsername("King Five");
        user.setBirthday(new Date());
        sqlSession.insert("test.insertUser", user);
        // Submit Transaction

Test Primary Key Return

sql mapping file

<insert id="insertUser" parameterType="test.User">
        <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
            select LAST_INSERT_ID()
        insert into user(username, birthday, sex, address)
        values (#{username}, #{birthday}, #{sex}, #{address})

Add a selectKey tag for primary key return.

  • keyProperty: Specifies the primary key returned, which property is stored in the pojo
  • The execution order of sql in the order:selectKey tag is relative to the insert statement.Because of the self-increasing principle of mysql, the insert statement is executed before the primary key is generated, so the selectKey execution order here is after.
  • resultType: JAVA type corresponding to the primary key returned
  • LAST_INSERT_ID(): is a function of mysql that returns the id value of a new record from the auto_increment column.

Tags: Programming Mybatis SQL xml Java

Posted on Fri, 02 Aug 2019 17:50:38 -0700 by cody44