Use of MyBatis framework

1, Overview

1. Framework

The skeleton of framework project development encapsulates many details, so that developers can use simple interface calls to realize functions and improve development efficiency.

Problems to be solved by the framework

The most important thing of the framework is to solve the problem of technology integration. It should decouple the software design from the specific implementation technology. R & D should focus on the application design rather than the specific technical implementation. The technical implementation is the bottom support of the application, and should not directly affect the application, so as to avoid the impact of the complexity and risk of the technology on the application.

The framework is generally located in the middle layer between the low-level application platform (such as J2EE) and the high-level business logic.

2. MyBatis

mybatis: a persistence layer framework based on java, which encapsulates jdbc internally. Developers only need to pay attention to the sql statement itself, and do not need to spend energy on the complex processes such as loading drivers, creating connections, creating statements, etc.

Various statements to be executed are configured by means of xml or annotation, and the final executed sql statement is generated by mapping the java object and the dynamic parameters of sql in the statement. Finally, the MySQL framework executes sql and maps the results into java objects and returns them.
The ORM idea is adopted to solve the problem of entity and database mapping, encapsulate jdbc and shield the underlying access details of jdbc api, so that we can complete the persistence operation of database without dealing with jdbc api.

2, Configuration file

1. Import log

Import coordinates

<!-- log start -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.12</version>
</dependency>

<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-api</artifactId>
    <version>1.6.6</version>
</dependency>

<dependency>
    <groupId>org.slf4j</groupId>
    <artifactId>slf4j-log4j12</artifactId>
    <version>1.6.6</version>
</dependency>
<!-- log end -->

configuration file

log4j.rootLogger=DEBUG,stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
#[%-5p] %t %l %d %rms:%m%n
#%d{yyyy-MM-dd HH:mm:ss,SSS\} %-5p [%t] {%c}-%m%n
log4j.appender.stdout.layout.ConversionPattern=[%-5p] %t %l %d %rms:%m%n
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=D:\\idea_project\\itheima_mm_backend.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss,SSS\} %-5p [%t] {%c}-%m%n

2. Core profile

<?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>
    <properties resource="jdbc.properties"/>

    <typeAliases>
        <package name="com.demo.pojo"/>
    </typeAliases>

    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <package name="com.demo.dao"/>
    </mappers>
</configuration>

Properties: import external properties files

Define the external jdbc.properties file

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis_day01?characterEncoding=utf-8
jdbc.user=root
jdbc.password=123

Import to core profile

<configuration>
    <!--Reference external properties file-->
   <properties resource="jdbc.properties">
    </properties>
    
    <!--Data source configuration-->
    <environments default="development">
        <environment id="development">
           ...
        </environment>
    </environments>
    ....
</configuration>

typeAliases: type aliases

Core profile

Define a single alias

<typeAliases>
      <typeAlias type="com.demo.bean.User" alias="user"></typeAlias>
</typeAliases>

Batch definition alias

<typeAliases>
    <package name="com.demo.bean"/>
</typeAliases>

Modify mapping profile

<select id="findAll" resultType="user">
         SELECT  * FROM  user
</select>

environments: environment configuration

<environments default="development">
    <environment id="development">
        <transactionManager type="JDBC"/>
        <dataSource type="UNPOOLED">
            <property name="driver" value="${jdbc.driver}"/>
            <property name="url" value="${jdbc.url}"/>
            <property name="username" value="${jdbc.user}"/>
            <property name="password" value="${jdbc.password}"/>
        </dataSource>
    </environment>
</environments>

Mapper: loads the mapping profile for the current project

Method 1: single mapping file path

<mappers>
     <mapper resource="com/demo/dao/UserDao.xml"/>
 </mappers>

Mode 2: scan interface

Configure a single interface

<mappers>
 	<mapper class="com.demo.dao.UserDao"></mapper>
</mappers>

Batch configuration interface

<mappers>
   <package name="com.demo.dao"></package>
</mappers>

3. Mapping profile

Mapper interface development needs to follow the following specifications:

The storage path recommendations are consistent with the corresponding Dao interface

Map configuration file path: com/demo/dao/MBDao.xml

Dao interface file path: com.demo.dao.MBDao.java
It is recommended that the file name be consistent with the name of the corresponding Dao interface: the suffix can be different
The namespace attribute of the root tag of the configuration file must be consistent with the fully qualified name of the corresponding Dao interface
Each method in the interface corresponds to a label in the mapping configuration file:
1. Query method, corresponding to the select tag
2. Add a method corresponding to the insert tag
3. Delete method, corresponding to delete tag
4. Modify the method to correspond to the update tag
```
<mapper namespace="com.demo.dao.MBDao">
    <select id="findById" parameterType="int" resultType="MBUser">
        select * from t_user where id = #{uid};
    </select>
</mapper>
```
The id attribute of the tag in the mapping configuration file must be consistent with the method name of the corresponding method
```
public interface MBDao {
    public List<MBUser> findAll();
    public MBUser findById(Interger id);
}
```
The parameterType attribute of the label in the mapping configuration file must be consistent with the parameter type (fully qualified name) of the corresponding method
```
User findById(int id);
```
```
<select id="findById" parameterType="int" resultType="MBUser">
    select * from t_user where id = #{uid};
</select>
```

The resultType attribute of the label in the mapping configuration file must be consistent with the return value type (fully qualified name) of the corresponding method, but if the return value is List, it must be consistent with its generic type

@Data
@AllArgsConstructor
@NoArgsConstructor
public class MBUser {
    private Integer uid; //User id
    private String username;// User name
    private String sex;// Gender
    private Date birthday;// birthday
    private String address;// address
}

<?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.demo.dao.MBDao">
    <select id="findById" parameterType="int" resultType="MBUser">
        select * from t_user where id = #{uid};
    </select>
</mapper>

resultType how to get the return value:

Pass simple type

Long findTotal();

<select id="findById" parameterType="int" resultType="long">
    select count(*) from t_user;
</select>

pojo object or Map object

User findById(int id);
Map findByUsername(String username);

<select id="findById" parameterType="int" resultType="User">
    select * from t_user where id = #{uid};
</select>

<select id="findByUsername" parameterType="string" resultType="User">
    select * from t_user where username = #{uname};
</select>

pojo list or Map list

List<UserInfo> findAllUserInfo();

<select id="findByUsername"  resultType="UserInfo">
    select * from t_user;
</select>

The property name of pojo is different from the field name of the query result set

@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserInfo implements Serializable {
    private Integer userId;
    private String username;
    private String userSex;
    private String userBirthday;
    private String userAddress;
}

List<UserInfo> findAllUserInfo();

Automatic mapping: this problem is solved by aliasing the fields of the result set

<select id="findByUsername"  resultType="UserInfo">
    select uid userId, sex userSex, birthday userBirthday, address userAddress from t_user;
</select>

Manual mapping

<resultMap id="userInfo" type="MBUserInfo" autoMapping="true">
    <id column="uid" property="userId"></id>
    <result column="username" property="username"></result>
    <result column="sex" property="userSex"></result>
    <result column="birthday" property="userBirthday"></result>
    <result column="address" property="userAddress"></result>
</resultMap>
<select id="findAll" resultMap="userInfo">
    select * from t_user
</select>

4. Introduction examples

Introduce dependency

In the pom.xml file, dependent jar packages such as junit, lombok, mysql, mybatis and log4j are introduced

Writing pojo: javaBean objects

@Data
@AllArgsConstructor
@NoArgsConstructor
public class MBUser {
    private int uid; //User id
    private String username;// User name
    private String sex;// Gender
    private Date birthday;// birthday
    private String address;// address
}

Write Dao layer code

public interface MBDao {
    public void addUser(MBUser user);
    public void deleteUser(int id);
    public void updateUser(MBUser user);
    public MBUser qurryUser(int id);
    public List<MBUser> qurryUserByName(String username);
    public List<MBUser> findAll();
}

Write MyBatis configuration file

Core profile: naming unlimited

<?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>

    <!--Introduce external jdbc configuration file-->
    <properties resource="jdbc.properties"/>
	
    <!--Alias under batch named path-->
    <typeAliases>
        <package name="com.demo.pojo"/>
    </typeAliases>

    <!--to configure JDBC Environment-->
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <!--Use imported JDBC configuration file-->
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
            </dataSource>
        </environment>
    </environments>

    <!--scanning MyBatis All mapping files under this path in-->
    <mappers>
        <package name="com.demo.dao"/>
    </mappers>
</configuration>

Mapping configuration file: the path must be the same as the dao layer, and the name must be the same as the corresponding dao file

MBDao.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.demo.dao.MBDao">
   <insert id="addUser" parameterType="MBUser">
      <selectKey resultType="int" keyColumn="uid" order="AFTER" keyProperty="uid">
        select last_insert_id();
      </selectKey>
      insert into t_user (username, sex, birthday, address) values (#{username}, #{sex}, #{birthday}, #{address})
    </insert>

    <delete id="deleteUser" parameterType="int">
        delete from t_user where uid = "${a}";
    </delete>

    <select id="qurryUser" parameterType="int" resultType="MBUser">
        select * from t_user where uid = ${a};
    </select>

    <select id="qurryUserByName" parameterType="string" resultType="MBUser">
        select * from t_user where username like concat("%", #{username}, "%");
    </select>

    <update id="updateUser" parameterType="MBUser">
        update t_user set username=#{username} where uid=#{uid};
    </update>

</mapper>

Other configuration files

log4j.properties

log4j.rootLogger=DEBUG,stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
#[%-5p] %t %l %d %rms:%m%n
#%d{yyyy-MM-dd HH:mm:ss,SSS\} %-5p [%t] {%c}-%m%n
log4j.appender.stdout.layout.ConversionPattern=[%-5p] %t %l %d %rms:%m%n
log4j.appender.file=org.apache.log4j.FileAppender
log4j.appender.file.File=D:\\idea_project\\itheima_mm_backend.log
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{yyyy-MM-dd HH:mm:ss,SSS\} %-5p [%t] {%c}-%m%n

jdbc.properties

jdbc.username=root
jdbc.password=123456
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///mybatis?useSSL=false

test

public class MyBatisTest {

    private MBDao userDao;
    private SqlSession sqlSession;
    private InputStream is;

    @Before
    public void init() throws Exception {
        //1. Let the mybatis framework load the main configuration file
        //1.1 convert the main configuration file SqlMapConfig.xml into byte input stream
        is = Resources.getResourceAsStream("MyBatisCurdConfig.xml");
        //1.2 create a SqlSessionFactoryBuilder
        SqlSessionFactoryBuilder factoryBuilder = new SqlSessionFactoryBuilder();
        //1.3 use factoryBuilder object to load byte input stream and create SqlSessionFactory object
        SqlSessionFactory sessionFactory = factoryBuilder.build(is); //The builder pattern is used
        //1.4 create sqlSession object using sessionFactory object
        //Factory mode is used
        sqlSession = sessionFactory.openSession();

        //2. Use sqlSession object to create proxy object of UserDao interface
        //Dynamic proxy is used
        userDao = sqlSession.getMapper(MBDao.class);
    }
    @Test
        public void testCURDUser(){
        //3. Call the addUser method of userDao object to add a user
        MBUser user = new MBUser(null, "JAX", "female", new Date(), "Summoner's Rift ");
        //The id before adding the user is null
        userDao.addUser(user);
        userDao.deleteUser(8);
        user = userDao.qurryUser(9);
        user.setUsername("Jay Chou stick");
        userDao.updateUser(user);
        List<MBUser> users = userDao.qurryUserByName("week");
        for (MBUser mbUser : users) {
            System.out.println(mbUser);
        }
    }

    @After
    public void destroy() throws IOException {
        //Commit transaction
        sqlSession.commit();

        //4. Close resources
        sqlSession.close();
        is.close();
    }
}

3, Detailed explanation of mapping configuration properties

1. SQL statement parameters

1.1 parameterType

Pass simple type

For a single parameter, the method can be passed in as a simple type. The value of parameterType is the alias of this simple type: #{arbitrary string}

User findById(int id);

<select id="findById" parameterType="int" resultType="MBUser">
    select * from t_user where id = #{uid};
</select>

Pass pojo or Map objects

Encapsulate multiple parameters into a POJO. The value of parameterType is the fully qualified name or alias of the POJO: #{property name of POJO} or '${property name of POJO}'

Encapsulate multiple parameters into a map (the parameter to be encapsulated has no corresponding POJO). The value of parameterType is map: #{key} of map or '${key}' of map

void addUser(User user);

void updateUser(Map map);

<insert id="addUser" parameterType="User">
    insert into t_user(username,sex,birthday,address) values (#{username},#{sex},#{birthday},#{address})
</insert>

<update id="updateUser" parameterType="map">
    update t_user set username=#{username},sex=#{sex} where uid=#{uid}
</update>

Passing multiple objects specifies the parameter name using the Param annotation

<select id="findByUsernameAndAddress" resultType="User">
    select * from t_user where username=#{uname} and address=#{addr}
</select>

pojo wrapper object

pojo is used to transfer query criteria. Query criteria are comprehensive query criteria, including not only user query criteria, but also other query criteria (for example, the information of goods purchased by users is also used as query criteria). At this time, packaging objects can be used to transfer input parameters. The pojo class contains pojo.

public class QueryVo {
	private User user;
}

public interface UserDao {
    List<User> searchByCondition(QueryVo queryVo);
}

<select id="searchByCondition" parameterType="QueryVo" resultType="User">
    select * from t_user where sex=#{queryCondition.sex};
</select>

1.2 resultType

Simple data type

The result of the query is a single data, and the value of the resultType attribute is the type of this data

Long findTotal();

<select id="findById" parameterType="int" resultType="long">
    select count(*) from t_user;
</select>

pojo list or Map list. The query result is multi row data:

Store multiple pieces of data in the List, and the value of resultType in the mapping configuration file is the alias of POJO
Store multiple pieces of data in the List, and the value of resultType in the mapping configuration file is map

List<UserInfo> findAllUserInfo();

<select id="findByUsername"  resultType="UserInfo">
    select * from t_user;
</select>

The result of a pojo object or Map object query is a row of data

@Data
@AllArgsConstructor
@NoArgsConstructor
public class UserInfo implements Serializable {
    private Integer userId;
    private String username;
    private String userSex;
    private String userBirthday;
    private String userAddress;
}

List<UserInfo> findAllUserInfo();

Store this row of data in the POJO object, and the value of resultType is the fully qualified name or alias of the POJO
Store this row of data in the map object, and the value of resultType is map. At this time, the field name in the query result is the key of map, and the field value is the value of map

Automatic mapping:

The field name and type of query results should be consistent with the property name and type of POJO

List<UserInfo> findAllUserInfo();

<select id="findByUsername"  resultType="UserInfo">
    select uid userId, sex userSex, birthday userBirthday from t_user;
</select>

Manual mapping

Write a manual mapping rule using the resultMap tag, and then use the mapping rule

id is the unique identifier of the mapping rule
Type is the type to be manually mapped: UserInfo
autoMapping="true" means that the attributes that can be mapped automatically will be mapped automatically, and the attributes that cannot be mapped automatically will be mapped manually

<resultMap id="userInfo" type="MBUserInfo" autoMapping="true">
    <id column="uid" property="userId"></id>
    <result column="username" property="username"></result>
    <result column="sex" property="userSex"></result>
    <result column="birthday" property="userBirthday"></result>
    <result column="address" property="userAddress"></result>
</resultMap>
<select id="findAll" resultMap="userInfo">
    select * from t_user
</select>

2. Dynamic SQL

Different SQL statements are set in the mapping file according to different parameters

2.1 if label

public interface UserDao {
    //Query users according to address. If there is no incoming address, all users will be queried
    List<User> findUserListByAddress(@Param("address") String address);
    
    //Query the user according to the user's address and gender. If there is address, the address condition will be considered, and if there is sex, the sex condition will be considered
    List<User> findUserListByAddressAndSex(User user);
}

List<User> userList = mapper.findUserListByAddress(null);
User userRet = mapper.findUserListByAddressAndSex(user);

<select id="findUserListByAddress" parameterType="string" resultType="User">
    select * from t_user
    <if test="address != null">
        where address=#{address}
    </if>
</select>

<select id="findUserListByAddressAndSex" parameterType="User" resultType="User">
    select * from t_user
    where 1=1
    <if test="address != null">
        and address=#{address}
    </if>

    <if test="sex != null">
        and sex=#{sex}
    </if>
</select>

2.2 where label

The where tag is used to replace where '1' = '1' when writing sql statements. The where keyword will be added before the condition to automatically process the first and. It is recommended to add and to all

<select id="findUserListByAddressAndSex" parameterType="User" resultType="User">
	select * from t_user
    <where>
        <if test="address != null">
            and address=#{address}
        </if>

        <if test="sex != null">
            and sex=#{sex}
        </if>
    </where>
</select>

2.3 foreach label

Batch delete: deletes all elements according to the set of IDS

void deleteByIds(List<Integer> ids);

List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(2);
ids.add(3);
ids.add(4);

mapper.deleteByIds(ids);

<delete id="deleteByIds" parameterType="int">
    delete from t_user

    <!--
            Traverse the data in the incoming collection and put it into()inside
            use foreach Tag traversal
                collection attribute:To traverse the collection, if you want to traverse a List Is written as list
                item attribute: Each element traversed out
                separator attribute: The separator between each element traversed
                index attribute: Index of each element traversed
                open attribute: Concatenate the string before the first element traversed
                close attribute: Concatenate the string after the last element traversed
        -->
    <foreach collection="list" item="id" separator="," open="where uid in(" close=")">
        #{id}
    </foreach>
</delete>

2.4 SQL fragment

Duplicate sql can be extracted from sql and referenced with include when used, so as to achieve the purpose of sql reuse.

<sql id="select_all">
    select uid,username,sex,address,birthday from t_user
</sql>

<select id="findUserListByAddress" parameterType="string" resultType="User">
    <include refid="select_all"/>
    <if test="address != null">
        where address=#{address}
    </if>
</select>

Keywords: Database Mybatis

Added by Ryokotsusai on Sat, 04 Dec 2021 07:22:10 +0200

Programming VIP

Use of MyBatis framework

1, Overview

1. Framework

2. MyBatis

2, Configuration file

1. Import log

2. Core profile

3. Mapping profile

4. Introduction examples

3, Detailed explanation of mapping configuration properties

1. SQL statement parameters

1.1 parameterType

1.2 resultType

Automatic mapping:

Manual mapping

2. Dynamic SQL

2.1 if label

2.2 where label

2.3 foreach label

2.4 SQL fragment

Popular Keywords