Mybatis – basic use

Time:2021-12-3

preface

MybatisIt’s aJavaPersistence layer framework, which will operate the database internallyJdbcThe relevant code is encapsulated, and theSQLStatement execution results andPojoDirect mapping. This article will firstJdbcLearn and learn on this basisMybatisBased on the use of bothJdbcstillMybatis, are based on native components and will not be associated withSpringAnd other frameworks.

Mybatisedition:3.5.6

text

1、 JDBC Foundation

JdbcFull nameJavaDataBase Connectivity, i.eJavaDatabase connection, based onJdbcThe database connection can be obtained and executedSQLStatement, can also handleSQLThe execution result set of the statement. The following will be aboutJdbcThe operation steps are introduced.

1. Load database driver

First, you need to load the database driver. HereMysqlTake the database driver as an example, as shown below.

Class.forName("com.mysql.jdbc.Driver");

2. Get database connection

Then you need to get the database connection, as shown below.

//URL is used to identify the database location, that is, to tell JDBC which database to connect to
String url = "jdbc:mysql://127.0.0.1:3306/test";
//Database user name and password
String username = "root";
String password = "root";
//Connection is an important object in database programming. All interactions between client and database depend on this object
Connection connection = DriverManager.getConnection(url, username, password);

ConnectionThe important methods of the object part are as follows.

method describe
Statement createStatement() Create send to databaseSQLofStatementobject
PreparedStatement prepareStatement(String sql) Create send to databaseprecompileSQLofPreparedStatementobject
commit() Commit transaction
rollback() Rollback transaction

3. Get statement object

StatementObject is used to send to the databaseSQLStatement, as shown below.

Statement statement = connection.createStatement();

StatementCommon methods for objects are as follows.

method describe
ResultSet executeQuery(String sql) Send query to databaseSQLsentence
int executeUpdate(String sql) Send inserts, updates, or deletions to the databaseSQLsentence
boolean execute(String sql) Send arbitrary to databaseSQLsentence

useStatementAn example of a query is shown below.

Statement statement = connection.createStatement();
String sql = "SELECT * FROM worker";
ResultSet resultSet = statement.executeQuery(sql);

becauseStatementThe use of will introducesecurityandefficiencyProblems, so usuallyJdbcimplementSQLStatement is based onPreparedStatementObject.PreparedStatementInherit fromStatement, usePreparedStatementAn example of a query is shown below.

String sql = "SELECT * FROM worker WHERE sex = ?"
//When obtaining the Preparedstatement object, you need to pass in the SQL statement for precompiling
PreparedStatement preparedStatement = connection.prepareStatement(sql);
//Set the parameter to replace the first placeholder with "male"
preparedStatement.setString(1, "male");
//Execute the query, and the query results are encapsulated by the resultset object
ResultSet resultSet = preparedStatement.executeQuery();

Compared toStatementObject,PreparedStatementObject execution is more efficient (becauseSQLStatement has completed precompiling), and can preventSQLInjection (because of acquisition)PreparedStatementObject needs to be passed inSQLStatement is precompiled, and no user input data changes will occur at this timeSQLThe phenomenon of sentence structure).

4. Processing execution results

ResultSetrepresentativeJdbcinSQLThe execution result of the statement.ResultSetThe execution results are encapsulated in a table,ResultSetThe object maintains a cursor pointing to the table data row. In the initial state, the cursor is in row 0 (at this time, it does not point to the result data). Callnext()Method, the cursor will point to the first row of data in the result data. At this time, you canResultSetObject to manipulate the row of data pointed to by the cursor.ResultSetThe common methods are as follows.

method describe
boolean next() Move cursor to next line
boolean previous() Move cursor to previous line
boolean absolute( int row ) Move cursor to specified row
Object getObject(int columnIndex) Gets the second row of the row pointed to by the cursorcolumnIndexAny type of data for the column
Object getObject(String columnLabel) Gets the name of the row pointed to by the cursorcolumnLabelAny type of data for the column

5. Release the connection

After operating the database, you need to release the connection, as shown below.

if (resultSet != null) {
    resultSet.close();
}
if (preparedStatement != null) {
    preparedStatement.close();
}
if (connection != null) {
    connection.close();
}

2、 Mybatis basic use

1. Mybatis structure

MybatisAn overall structure of the is shown below.

Mybatis - basic use

What appears in the figure aboveMybatisThe components of are described below.

assembly explain
mybatis-config.xml MybatisGlobal profile for. to configureMybatisOperating environment (database connection, etc.).
Mapper.xml Map file. The operation database is configured in the mapping fileSQLsentence.
SqlSessionFactory adoptmybatis-config.xmlFile constructionSqlSessionFactory, called a session factory. Used to createSqlSession
SqlSession adoptSqlSessionFactoryestablishSqlSession, called a session. Database operation requiresSqlSessionconduct.
Executor SqlSessionInternal passExecutorPerform database operations.
MappedStatement ExecutoradoptMappedStatementIn executionSQLBefore the statement, map the input parameters toSQLStatement, when executingSQLStatement maps the output result to the set output result type.

2. Used by original mybatis

originalMybatisIn the use ofMybatisPackage and configurationmybatis-config.xmlAfter that, the most important thing is to write the mapping file(Mapper.xml)。 Here is a mapping file, as shown below.

<?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="MyMapper">
    <select id="selectWorker" parameterType="java.lang.String" resultType="com.test.po.Worker">
        SELECT * FROM worker WHERE sex = #{sex}
    </select>
</mapper>

originalMybatisWhen executing the mapping file, you first need to obtainSqlSession, and then throughSqlSessionTo call the mapping file. obtainSqlSessionThe steps are as follows.

//Specify global profile
String resource = "mybatis-config.xml";
//Read global configuration file
InputStream inputStream = Resources.getResourceAsStream(resource);
//Building sqlsessionfactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//Get sqlsession
SqlSession sqlSession = sqlSessionFactory.openSession();

SqlSessionAn example of calling a mapping file is shown below.

//The first parameter is the namespace of the mapping file + ".. + (the ID of the SQL statement of the mapping file)
//The second parameter is the parameter value of the SQL statement to be executed
List<Worker> workers = sqlSession.selectList("MyMapper.selectWorker", "male");

Finally, don’t forget to closeSqlSession, as shown below.

if (sqlSession != null) {
    sqlSession.close();
}

3. Use of dynamic proxy mybatis

Dynamic agentMybatisWhen using, you need to write a mapping file and mapping interface. The mapping file is given here, as shown below.

<mapper namespace="com.test.mapper.WorkerMapper">
    <select id="selectWorkerBySex" parameterType="java.lang.String" resultType="com.test.po.Worker">
        SELECT * FROM worker WHERE sex = #{sex}
    </select>
</mapper>

In the above mapping file,<mapper>TaggednamespaceProperty needs to be set to the fully qualified name of the mapping interface associated with the mapping file,<select>TaggedidThe attribute needs to be consistent with the method name defined by the mapping interface, that is, each method defined by the mapping interface needs to be consistent with one in the mapping fileSQLCorrespondingly,MybatisThe mapping instance will be generated for the mapping interface, and the operation on the database will be completed by calling the method of the mapping instance. The mapping interface associated with the above mapping file is shown below.

public interface WorkerMapper {

    List<Worker> selectWorkerBySex(String sex);

}

Dynamic agentMybatisWhen executing, you first need to obtainSqlSession, and then throughSqlSessionTo get an instance of the mapping interface. obtainSqlSessionThe steps are as follows.

//Specify global profile
String resource = "mybatis-config.xml";
//Read global configuration file
InputStream inputStream = Resources.getResourceAsStream(resource);
//Building sqlsessionfactory
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//Get sqlsession
SqlSession sqlSession = sqlSessionFactory.openSession();

The steps to get an instance of the mapping interface are as follows.

WorkerMapper workerMapper = sqlSession.getMapper(WorkerMapper.class);

After obtaining the instance of the mapping interface, it is executed by calling the method of the instance of the mapping interfaceSQLStatement, as shown below.

List<Worker> list = workerMapper.selectWorkerBySex("male");

Last closedSqlSession, as shown below.

if (sqlSession != null) {
    sqlSession.close();
}

3、 Mybatis tag syntax

This section analyzes the common tags in the mapping file. If there is no special description, it defaults toMybatisThe use of is based on a dynamic agent approach.

1. < Select > tag

<select>Tags represent queriesSQLStatements, labels, common attributes and descriptions are shown in the following table.

attribute explain
id currentnamespaceUnique identification under, requirementsidThe value of is consistent with the method name in the mapping interface.
parameterType afferentSQLFully qualified name of the type of the parameter of the statement (can be omitted,MybatisYou can infer the type of the passed in parameter yourself).
resultType SQLThe fully qualified name of the type of data returned after the execution of the statement. If the returned is a collection type, thenresultTypeThe fully qualified name of the type of the element stored for the collection.
resultMap And<resultMap>Use with labels.

If you need to pass in multiple parameters, you can pass inMapperhapsJava BeanTo achieve. Two examples will be given below<select>Use of labels.

Example 1: PassMapPass in two parameters, age and salary, and then query the data of all workers whose age is greater than age and salary is greater than salary.

The mapping file is shown below.

<?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="com.test.mapper.WorkerMapper">
    <select id="selectAgeSalaryByMap" parameterType="java.util.Map" 
            resultType="com.test.po.Worker">
        SELECT * FROM worker WHERE age > #{age} and salary > #{salary}
    </select>
</mapper>

The mapping interface is shown below.

public interface WorkerMapper {

    List<Worker> selectAgeSalaryByMap(Map<String, Long> map);

}

The test code is as follows (omitted)SqlSessionGet of).

......
WorkerMapper workerMapper = sqlSession.getMapper(WorkerMapper.class);
Map<String, Long> map = new HashMap<>();
map.put("age", 20L);
map.put("salary", 10000L);
List<Worker> workers = workerMapper.selectAgeSalaryByMap(map);
......

Example 2: PassJava BeanPass in two parameters, age and salary, and then query the data of all workers whose age is greater than age and salary is greater than salary.

Java BeanAs shown below.

public class SelectInfo {

    private Long age;
    private Long salary;

    public Long getAge() {
        return age;
    }

    public void setAge(Long age) {
        this.age = age;
    }

    public Long getSalary() {
        return salary;
    }

    public void setSalary(Long salary) {
        this.salary = salary;
    }
    
}

The mapping file is shown below.

<?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="com.test.mapper.WorkerMapper">
    <select id="selectAgeSalaryByBean" parameterType="com.test.po.SelectInfo" 
            resultType="com.test.po.Worker">
        SELECT * FROM worker WHERE age > #{age} and salary > #{salary}
    </select>
</mapper>

The mapping interface is shown below.

public interface WorkerMapper {

    List<Worker> selectAgeSalaryByBean(SelectInfo selectInfo);

}

The test code is as follows (omitted)SqlSessionGet of).

......
WorkerMapper workerMapper = sqlSession.getMapper(WorkerMapper.class);
SelectInfo selectInfo = new SelectInfo();
selectInfo.setAge(20L);
selectInfo.setSalary(10000L);
List<Worker> workers = workerMapper.selectAgeSalaryByBean(selectInfo);
......

2. < Insert > tag

<insert>Label indicates insertionSQLsentence,MybatisAfter executing an insert statement, an integer is returned indicating the number of rows it affects.<insert>Label properties and<select>Most of the tag attributes are the same, and the following table gives different parts.

attribute explain
keyProperty When set as the attribute corresponding to the primary key, you can obtain the primary key value of the inserted data after the insertion operation.
useGeneratedKeys The attribute istrueWhen,MybatisCan useJDBCofgetGeneratedKeys()Method to obtain the primary key generated inside the database.

The following is an example of obtaining the self incrementing primary key of the inserted data after inserting data. The mapping file is shown below.

<?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="com.test.mapper.WorkerMapper">
    <insert id="insertWorker" parameterType="com.test.po.Worker"
            keyProperty="id" useGeneratedKeys="true">
        INSERT INTO
        worker (name, sex, age, salary)
        VALUES
        (#{name}, #{sex}, #{age}, #{salary})
    </insert>
</mapper>

The mapping interface is shown below.

public interface WorkerMapper {

    Integer insertWorker(Worker worker);

}

WorkerThe class structure is as follows.

public class Worker {

    private long id;
    private String name;
    private String sex;
    private long age;
    private long salary;

    public Worker() {}

    public Worker(String name, String sex,
                  long age, long salary) {
        this.name = name;
        this.sex = sex;
        this.age = age;
        this.salary = salary;
    }

    //Omit getters and setters
    ......

}

workerThe table creation statement is as follows.

CREATE TABLE worker(
    id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    sex VARCHAR(30) NOT NULL,
    age INT(11) NOT NULL,
    salary INT(11) NOT NULL
)

The test code is as follows (omitted)SqlSessionGet of).

...
WorkerMapper workerMapper = sqlSession.getMapper(WorkerMapper.class);
Worker worker = new Worker("Lee", "male", 20L, 10000L);
Integer result = workerMapper.insertWorker(worker);
System.out.println(result + " pieces of data were inserted.");
System.out.println("The primary key of the inserted data is " + worker.getId());
...

3. < update > and < delete > tags

The properties of these two tags are the same as<select>and<insert>The attributes of the tag are roughly the same, and an integer will be returned after execution, indicating the number of records affected.

4. < resultmap > tab

When querying data from a database, the queried data is usually mapped into aJava BeanHowever, if the column name of the database table is the same asJava BeanWhen the attribute names of are inconsistent, the mapping cannot be performed. You need to use<resultMap>Label to specify table column names andJava BeanMapping between attribute names. Now create a page calledbookAs shown below.

CREATE TABLE book(
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
    b_name VARCHAR(255) NOT NULL,
    b_price INT(11) NOT NULL
)

Create aBookClass for working withbookTable, as shown below.

public class Book {

    private long id;
    private String bookName;
    private long bookPrice;

    //Omit getters and setters
    ......

}

As you can see,BookClassbookNameandbookPriceAttribute andbookTabularb_nameandb_priceInconsistent column names, frombookThe data found in the table query cannot be mapped toBookClass, you need to use<resultMap>Tag to solve the problem of unable mapping. The mapping file is shown below.

<?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="com.test.mapper.BookMapper">
    <resultMap id="bookResultMap" type="com.test.po.Book">
        <result column="id" property="id"/>
        <result column="b_name" property="bookName"/>
        <result column="b_price" property="bookPrice"/>
    </resultMap>

    <select id="selectBookByName" parameterType="java.lang.String"
            resultMap="bookResultMap">
        SELECT * FROM book WHERE b_name=#{bookName}
    </select>
</mapper>

<resultMap>TaggedidProperty represents the currentnamespaceThe unique identifier under the can be<resultMap>TaggedidProperty to reference the<resultMap><resultMap>TaggedtypeThe fully qualified name of the attribute table mapping class, such asBookClass, thentypeField should beBookThe fully qualified name of the class.<resultMap>Under the label, one<result>The tag represents the mapping of a pair of table column names and class attribute names,<result>TaggedcolumnAttribute is the column name of the table,<result>TaggedpropertyProperty is a class property name.

The mapping interface corresponding to the above mapping file is shown below.

public interface BookMapper {

    List<Book> selectBookByName(String bookName);

}

The test code is as follows (omitted)SqlSessionGet of).

......
BookMapper bookMapper = sqlSession.getMapper(BookMapper.class);
List<Book> mathBooks = bookMapper.selectBookByName("Math");
......

IfBookClass has an attribute as a class, as shown below.

Book

public class Book {

    private long id;
    private String bookName;
    private long bookPrice;

    private BookStore bookStore;

    //Omit getters and setters
    ......

}

BookStore

public class BookStore {

    private long bsId;
    private String bsName;

    //Omit getters and setters
    ......

}

The mapping file is shown below.

<?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="com.test.mapper.BookMapper">
    <resultMap id="bookResultMap" type="com.test.po.Book">
        <result column="id" property="id"/>
        <result column="b_name" property="bookName"/>
        <result column="b_price" property="bookPrice"/>

        <association property="bookStore" javaType="com.test.po.BookStore">
            <result column="bs_id" property="bsId"/>
            <result column="bs_name" property="bsName"/>
        </association>
    </resultMap>

    <select id="selectBookByName" parameterType="java.lang.String"
            resultMap="bookResultMap">
        SELECT * FROM book WHERE b_name=#{bookName}
    </select>
</mapper>

aboutMybatisThe basic syntax of tags is analyzed here, in which<resultMap>Label asMybatisThe most powerful tag in, which can realize far more functions than those described above, will be discussed separately in the future<resultMap>The use of tags for in-depth study.

summary

This article onJDBCBasic use andMybatisThe basic use of is analyzedMybatisThe basic syntax of common tags in the mapping file is introduced. usuallyMybatisYou need to write a mapping file and a mapping interface, and a mapping file corresponds to a mapping interface,MybatisCan useJDKThe dynamic proxy generates proxy objects for the mapping interface. When calling methods of the mapping interface, the call request will be intercepted by the proxy object, and then locate the corresponding proxy object according to the fully qualified name of the mapping interface + method nameMappedStatementThen executeSQLsentence.