Mybatis simple understanding and basic use

1. History of database operation framework

JDBC

JDBC (Java data base connection) is a Java API for executing SQL statements. It can provide unified access to a variety of relational databases. It is composed of a group of classes and interfaces written in Java language JDBC provides a benchmark by which more advanced tools and interfaces can be built to enable database developers to write database applications

• Advantages: operation period: fast and efficient
• Disadvantages: editing period: large amount of code, cumbersome exception handling, and does not support database cross platform

jdbc core api

• 1.DriverManager connection database
• 2. Abstraction of connection database
• 3. Execute SQL statement
• 4.ResultSet data result set

DBUtils

DBUtils is a database operation utility in Java programming. It is small, simple and practical.

DBUtils encapsulates the JDBC operation, simplifies the JDBC operation, and can write less code.

Introduction to three core functions of DBUtils

• 1. The query runner provides API s for sql statement operations
• 2. ResultSetHandler interface is used to define how to encapsulate the result set after the select operation
• 3. DBUtils class, which is a tool class, defines methods to close resources and transactions

Hibernate

Hibernate introduction

• Hibernate is an open source object relational framework created by Gavin King in 2001. It is powerful and efficient to build Java applications with relational object persistence and query services.
• Hibernate maps Java classes to database tables, from Java data types to SQL data types, and liberates developers from 95% of public data persistence programming.
• Hibernate is a bridge between traditional Java objects and database server, which is used to deal with those objects based on O/R mapping mechanism and schema.

Hibernate advantages

• Hibernate uses XML files to handle mapping Java categories into database tables without writing any code.
• Provides simple APIs for storing and retrieving Java objects directly in the database.
• If there is a change in the database or any other table, only the XML file attributes need to be changed.
• Abstract unfamiliar SQL types and provide us with Java objects familiar in our work.
• Hibernate does not require an application server to operate.
• Manipulate complex associations of objects in your database.
• Intelligent extraction strategy for minimizing and accessing database.
• Provide simple data query.

Hibernate disadvantages

• The complete encapsulation of hibernate makes it impossible to use some functions of data.
• Hibernate cache problem.
• Hibernate is too coupled to the code.
• Hibernate is difficult to find bug s.
• Hibernate batch data operations require a lot of memory space, and too many objects are required during execution

JDBCTemplate

JdbcTemplate provides multiple overloaded template methods for data query. You can choose different template methods according to your needs If your query is very simple, just pass in the corresponding SQL or related parameters, and then get a single result, you can choose the following set of convenient template methods.

advantage

• Operation period: high efficiency
• Embedded in Spring framework
• Support AOP based declarative transactions

shortcoming

• Must be used in conjunction with the Spring framework
• Database cross platform is not supported
• No cache by default

2. What is Mybatis?

MyBatis is an excellent persistence layer framework / semi-automatic ORM, which supports custom SQL, stored procedures and advanced mapping. MyBatis eliminates almost all JDBC code and the work of setting parameters and obtaining result sets. MyBatis can configure and map primitive types, interfaces and Java POJO s (Plain Old Java Objects) to records in the database through simple XML or annotations.

advantage

• 1. Compared with JDBC, the amount of code is reduced by 50%
• 2. The simplest persistence framework is easy to learn
• 3. SQL code is completely separated from program code and can be reused
• 4. Provide XML tags to support writing dynamic SQL
• 5. The mapping label is provided to support the mapping between the object and the ORM field of the database
• Support caching, connection pool, database migration

shortcoming

• 1. SQL statement writing workload is large and proficiency is high
• 2. Database portability is poor. If you need to switch databases, SQL statements will be very different

3. Quickly build Mybatis project

1. Create a normal maven project

2. Import related dependent POM xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>cn.tulingxueyuan</groupId>
    <artifactId>mybatis_helloworld</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.4</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
    </dependencies>

</project>
Copy code

3. Create corresponding data table

4. Create an entity class object corresponding to the table

• emp.java

public class Emp {
    private Integer id;
    private String username;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "id=" + id +
                ", username='" + username + ''' +
                '}';
    }
}
Copy code

5. Create corresponding Mapper interface

• EmpMapper.java

public interface EmpMapper {
    // Query Emp entity by id
    //@Select("select * from emp where id=#{id}")
    Emp selectEmp(Integer id);

    // insert
    Integer insertEmp(Emp emp);

    // to update
    Integer updateEmp(Emp emp);

    // delete
    Integer deleteEmp(Integer id);
}
Copy code

6. Write configuration file

• 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>
<environments default="development">
    <environment id="development">
        <transactionManager type="JDBC"/>
        <dataSource type="POOLED">
            <property name="driver" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql://localhost:3306/mybatis"/>
            <property name="username" value="root"/>
            <property name="password" value="123456"/>
        </dataSource>
    </environment>
</environments>
<mappers>
    <!--<mapper resource="EmpMapper.xml"/>-->
    <mapper class="com.mybatis.mapper.EmpMapper"></mapper>
</mappers>
</configuration>
Copy code

• EmpMapper.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="com.mybatis.mapper.EmpMapper">

<!--according to id query Emp entity-->
<select id="selectEmp" resultType="com.mybatis.pojo.Emp">
    select * from Emp where id = #{id}
</select>

<insert id="insertEmp">
    INSERT INTO
    `mybatis`.`emp` ( `username`)
    VALUES (#{username});
</insert>

<update id="updateEmp">
    UPDATE EMP
    SET username=#{username}
    WHERE id=#{id}
</update>

<delete id="deleteEmp">
    DELETE FROM emp
    WHERE id=#{id}
</delete>

</mapper>
Copy code

7. Writing test classes

• MyTest.java

/***
 *
 * MyBatis Construction steps:
 * 1.Add pom dependency (the core jar package of mybatis and the driver jar package of the corresponding version of the database version)
 * 2.New database and table
 * 3.Add the mybatis global configuration file (which can be copied from the official website)
 * 4.Modify the data source configuration information in the mybatis global configuration file
 * 5.Add the POJO object corresponding to the database table (equivalent to our previous entity class)
 * 6.Add the corresponding pojomapper XML (which maintains all sql)
 *      Modify namespace: if it is StatementId, there are no special requirements
 *                      If it is an interface, the binding method must be equal to the fully qualified name of the interface
 *      Modify the corresponding ID (unique) and the return type corresponding to the resultType. If it is a POJO, you need to formulate a full qualified name
 * 7.Modifying the mybatis global configuration file: modifying Mapper
 */
public class MybatisTest {

    SqlSessionFactory sqlSessionFactory;
    @Before
    public void before(){
        // Building SqlSessionFactory from XML
        String resource = "mybatis.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    /**
     * Execute SQL based on StatementId
     *      <mapper resource="EmpMapper.xml"/>
     * @throws IOException
     */
    @Test
    public void test01() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            Emp emp = (Emp) session.selectOne("cn.tulingxueyuan.pojo.EmpMapper.selectEmp", 1);
            System.out.println(emp);
        }
    }

    /**
     * Interface binding based approach
     *  1.Interface of new data access layer: POJOMapper
     *  2.Method of adding corresponding operation in mapper
     *      1.The method name should be consistent with the id of the corresponding operation node in the mapper
     *      2.The return type should be consistent with the resultType of the corresponding operation node in the mapper
     *      3.mapper The parameters of the node of the corresponding operation in must be declared in the parameters of the method
     *  3.Mapper.xml The namespace in must be consistent with the fully qualified name of the interface
     *  4.Modify mappers in mybatis global configuration file by interface binding:
     *        <mapper class="com.mybatis.EmpMapper"></mapper>
     *  5.Be sure to add mapper XML and interfaces are placed in the same level directory. You only need to create a new folder with the same structure as the interface in resources, and the generation will be merged together
     *
     * @throws IOException
     */
    @Test
    public void test02(){
        try (SqlSession session = sqlSessionFactory.openSession()) {
            EmpMapper mapper = session.getMapper(EmpMapper.class);
            Emp emp = mapper.selectEmp(1);
            System.out.println(emp);
        }
    }


    /**
     * Annotation based approach
     * 1.Write the corresponding annotation on the interface method
     *@Select("select * from emp where id=#{id}")
     * be careful:
     *      Annotations can be shared with xml, but the xml id corresponding to the method cannot exist at the same time
     *
     */
    @Test
    public void test03(){
        try (SqlSession session = sqlSessionFactory.openSession()) {
            EmpMapper mapper = session.getMapper(EmpMapper.class);
            Emp emp = mapper.selectEmp(1);
            System.out.println(emp);
        }
    }
}
Copy code

4. Basic operations of adding, deleting, modifying and querying

• EmpMapper.java

public interface EmpMapper {

    public Emp findEmpByEmpno(Integer empno);

    public int updateEmp(Emp emp);

    public int deleteEmp(Integer empno);

    public int insertEmp(Emp emp);

}
Copy code

• EmpMapper.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">
<!--namespace:Writing the full class name of the interface is to tell the specific implementation of which interface the configuration file is to be implemented-->
<mapper namespace="com.mybatis.mapper.EmpMapper">
<!--
        select:Indicates that this operation is a query operation
        id Represents the name of the method to match
        resultType:Represents the type of the return value. The query operation must contain the type of the return value
        #{property name}: indicates the name of the parameter to be passed
        -->
    <select id="findEmpByEmpno" resultType="com.mybatis.bean.Emp">
        select * from emp where empno = #{empno}
    </select>
<!--The addition, deletion, modification and query operation does not need to return a value. The addition, deletion and modification returns the number of affected rows, mybatis Will automatically make judgment-->
    <insert id="insertEmp">
        insert into emp(empno,ename) values(#{empno},#{ename})
    </insert>
    <update id="updateEmp">
        update emp set ename=#{ename} where empno = #{empno}
    </update>
    <delete id="deleteEmp">
        delete from emp where empno = #{empno}
    </delete>
</mapper>
Copy code

• MyTest.java

public class MyTest {
    SqlSessionFactory sqlSessionFactory = null;
    @Before
    public void init(){
        // SqlSessionFactory is created according to the global configuration file
        // SqlSessionFactory: the factory responsible for creating SqlSession objects
        // SqlSession: indicates a session suggested by the database
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory= new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    @Test
    public void test01() {

        // Get database session
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Emp empByEmpno = null;
        try {
            // Gets the interface class to call
            EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
            // Call method to start execution
            empByEmpno = mapper.findEmpByEmpno(7369);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }
        System.out.println(empByEmpno);
    }

    @Test
    public void test02(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
        int zhangsan = mapper.insertEmp(new Emp(1111, "zhangsan"));
        System.out.println(zhangsan);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void test03(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
        int zhangsan = mapper.updateEmp(new Emp(1111, "lisi"));
        System.out.println(zhangsan);
        sqlSession.commit();
        sqlSession.close();
    }

    @Test
    public void test04(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
        int zhangsan = mapper.deleteEmp(1111);
        System.out.println(zhangsan);
        sqlSession.commit();
        sqlSession.close();
    }
}
Copy code

• EmpMapperImpl.java

public interface EmpMapperImpl {

    @Select("select * from emp where id= #{id}")
    public Emp findEmpByEmpno(Integer empno);

    @Update("update emp set ename=#{ename} where id= #{id}")
    public int updateEmp(Emp emp);

    @Delete("delete from emp where id= #{id}")
    public int deleteEmp(Integer empno);

    @Insert("insert into emp(id,user_name) values(#{id},#{username})")
    public int insertEmp(Emp emp);

}