This series of articles has been included in the Github Backend advancement Guide. The project is under development. Welcome star.

Know MyBatis

Mybatis reference site: http://www.mybatis.org/mybatis-3/zh/index.html

Making the source address: https://github.com/mybatis/mybatis-3

What is Mybatis

MyBatis is an excellent persistence layer framework that supports customized SQL, stored procedures, and advanced mapping. MyBatis avoids almost all of the JDBC code and manual setting of parameters and fetching result sets. It can configure and map SQL information using simple XML or annotations, Map the interface and Java’s POJOs(Plain Old Java Objects) to records in the database.

The origin of Mybatis

  • MyBatis is an open source project of Apache called iBatis.
  • In 2010 the project was migrated from Apache Software Foundation to Google Code and renamed MyBatis.
  • Migrated to Github in November 2013.

What is the ORM

OBJECT/RELATIONALMAPPING (ORM) is produced with the development of object-oriented software development method. Used to map objects represented by the object model to sqL-based relational model database structures. In this way, we do not need to deal with complex SQL statements in the specific operation of entity objects, but simply operate the attributes and methods of entity objects. ORM technology provides a bridge between objects and relationships, through which object data in the foreground and relational data in the database can be transformed into each other.

Difference between ORM framework and MyBatis

Compare the item Mybatis Hibernate
Market share high high
Suitable industry Internet e-commerce project Traditional (ERP, CRM, OA)
performance high low
Sql flexibility high low
To study the threshold low high
Sql configuration file Global configuration files, mapping files Global configuration files, mapping files
ORM semi-automatic Full automation
Database independence low high

Encoding process

  1. Write the global configuration file: xxxconfig.xml
  2. POJO class
  3. Mapping file: xxxmapper.xml
  4. Write DAO code: xxxDao interface, xxxDaoImpl implementation class
  5. Unit test class

demand

1. Query information about a user based on the user ID

2. Query the user information list based on the user name

3. Add a user

Project structures,

  • Create maven project: Mybatis -demo
  • POM file
<dependencies>

        <! -- Mybatis dependency -->

        <dependency>

            <groupId>org.mybatis</groupId>

            <artifactId>mybatis</artifactId>

            <version>3.4.6</version>

        </dependency>

        <! -- Mysql dependency -->

        <dependency>

            <groupId>mysql</groupId>

            <artifactId>mysql-connector-java</artifactId>

            <version>5.1.35</version>

        </dependency>



        <! -- Unit tests -->

        <dependency>

            <groupId>junit</groupId>

            <artifactId>junit</artifactId>

            <version>4.12</version>

        </dependency>

    </dependencies>

Copy the code
  • SqlMapConfig.xml

       

<! DOCTYPE configuration

PUBLIC "- / / mybatis.org//DTD Config / 3.0 / EN"

"http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>

    <properties resource="db.properties"></properties>

    <environments default="development">

        <environment id="development">

            <transactionManager type="JDBC" />

            <dataSource type="POOLED">

                <property name="driver" value="${db.driver}" />

                <property name="url" value="${db.url}" />

                <property name="username" value="${db.username}" />

                <property name="password" value="${db.password}" />

            </dataSource>

        </environment>

    </environments>

    <mappers>

        <mapper resource="UserMapper.xml" />

    </mappers>

</configuration>

Copy the code
  • UserMapper.xml

       

<! DOCTYPE mapper

PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN"

"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="test">

</mapper>

Copy the code
  • PO class
public class User {



    private int id;

    private String username;

    private Date birthday;

    private String sex;

    private String address;

    / / the getter \ setter methods

}

Copy the code

Need to implement

Query the user

The mapping file

<! Get user information by id -->

<select id="findUserById" parameterType="int" resultType="com.kkb.mybatis.po.User">

    select * from user where id = #{id} 

</select>



<! -- Query user list by name -->

<select id="findUserByUsername" parameterType="java.lang.String" 

            resultType="com.kkb.mybatis.po.User">

     select * from user where username like '%${value}%' 

</select>

Copy the code

Configuration description:

- parameterType: defines the Java types of input parameters.

- resultType: defines the result mapping type.

 - #{} : equivalent to JDBC? A placeholder

 - #{id} means to use preparedStatement to set the placeholder symbol and pass the input variable ID to SQL.

 - ${value} : Fetch the parameter namevalueThe value of the. The ${value} placeholder substitution.



Note: If you are taking a parameter of simple quantity type, the parameter name in parentheses must bevalue

Copy the code

Dao interfaces and implementation classes

public interface UserDao {

    public User findUserById(int id) throws Exception;

  public List<User> findUsersByName(String name) throws Exception;

}

Copy the code
  • Life cycle (scope of action)
  1. Sqlsession: method level
  2. SqlsessionFactory: Global scope (application level)
  3. SqlsessionFactoryBuilder: method level
public class UserDaoImpl implements UserDao {

    / / injection SqlSessionFactory

    public UserDaoImpl(SqlSessionFactory sqlSessionFactory){

        this. sqlSessionFactory = sqlSessionFactory;

    }



    private SqlSessionFactory sqlSessionFactory;



    @Override

    public User findUserById(int id) throws Exception {

        SqlSession session = sqlSessionFactory.openSession();

        User user = null;

        try {

            // Call the selectOne method from sqlSession to get a result set

            Parameter 1: specifies the ID of the statement to be defined. Parameter 2: specifies the parameter to be passed to statement

            user = session.selectOne("test.findUserById", id);

            System.out.println(user);   

        } finally{

            session.close();

        }

        return user;

    }





    @Override

    public List<User> findUsersByName(String name) throws Exception {

        SqlSession session = sqlSessionFactory.openSession();

        List<User> users = null;

        try {

            users = session.selectList("test.findUsersByName", name);

            System.out.println(users);      

        } finally{

            session.close();

        }

        return users;

    }

}

Copy the code

The test code

public class MybatisTest {



    private SqlSessionFactory sqlSessionFactory;



    @Before

    public void init(a) throws Exception {

        SqlSessionFactoryBuilder sessionFactoryBuilder = new SqlSessionFactoryBuilder();

        InputStream inputStream = Resources.getResourceAsStream("SqlMapConfig.xml");

        sqlSessionFactory = sessionFactoryBuilder.build(inputStream);

    }



    @Test

    public void testFindUserById(a) {

        UserDao userDao = new UserDaoImpl(sqlSessionFactory);

        User user = userDao.findUserById(22);

        System.out.println(user);

    }

    @Test

    public void testFindUsersByName(a) {

        UserDao userDao = new UserDaoImpl(sqlSessionFactory);

        List<User> users = userDao.findUsersByName("Guo");

        System.out.println(users);

    }

}

Copy the code

#{} and ${}

  • The difference between 1
#{} : equivalent to placeholders in JDBC SQL statements? (PreparedStatement)



The ${}: corresponds to a connection in a JDBC SQL Statement that matches + (Statement).

Copy the code
  • The difference between two
#{} : Input mapping is performed to parse the parameter (if the parameter is String, then the SQL statement automatically adds' ').



The ${}: During input mapping, parameters are output to the SQL statement as is

Copy the code
  • The difference between 3
#{} : If the input mapping is for simple types (String, Date, 8 basic wrapper classes),The parameter names in #{} can be arbitrary



${} : If the input mapping is for simple types (String, Date, 8 basic wrapper classes),The ${}The parameter name must be value

Copy the code
  • The difference between 4
The ${}: SQL injection problems exist. The keyword OR 1=1 is used to ignore query conditions

Copy the code

Add user

#{} : StaticSqlSource is used to fetch data via reflection

${} : the OGNL expression changes hierarchically as the object is nested

The mapping file

<! Add user -->

    <insert id="insertUser" parameterType="com.kkb.mybatis.po.User">

      insert into user(username,birthday,sex,address) 

      values(#{username},#{birthday},#{sex},#{address})

    </insert>

Copy the code

Dao interfaces and implementation classes

public interface UserDao {

    public void insertUser(User user) throws Exception;

}

Copy the code
public class UserDaoImpl implements UserDao {

    / / injection SqlSessionFactory

    public UserDaoImpl(SqlSessionFactory sqlSessionFactory){

        this. sqlSessionFactory = sqlSessionFactory;

    }



    private SqlSessionFactory sqlSessionFactory;



    @Override

    Public void insertUser(User user) throws Exception {

        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {

            sqlSession.insert("test.insertUser", user);

            sqlSession.commit();

        } finally{

            session.close();

        }   

    }

}

Copy the code

The test code

    @Override

    Public void insertUser(User user) throws Exception {

        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {

            sqlSession.insert("insertUser", user);

            sqlSession.commit();

        } finally{

            session.close();

        }



    }

Copy the code

The primary key to return to

<insert id="insertUser" parameterType="com.kkb.mybatis.po.User">

        <! -- selectKey returns primary key, needs to return -->

        <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">

            select 1608976

        </selectKey>

       insert into user(username,birthday,sex,address)

        values(#{username},#{birthday},#{sex},#{address});

    </insert>

Copy the code

Add the selectKey tag for primary key return.

  • KeyProperty: Specifies the returned primary key, which property is stored in the POJO
  • The order in which SQL is executed in the ORDER: selectKey tag, as opposed to the INSERT statement. Selectkeys are executed in after order because of mysql’s increment principle that primary keys are generated after the insert statement.
  • ResultType: Indicates the JAVA type of the returned primary key
  • 1608976: is a mysql function that returns the id of a new auto_increment column.

This series of articles has been included in the Github Backend advancement Guide. The project is under development. Welcome star.

The articles in the public account are the original bloggers, and will always be updated. If you want to witness or grow with bloggers, welcome to follow!

Welcome to scan code attention oh!!