Mybatis configuration file sqlmapconfig.xml


      
<! DOCTYPEconfiguration PUBLIC
        "- / / mybatis.org//DTD Config / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
    <! The properties TAB must be the first child of the Configuration TAB.
    <properties resource="jdbc.properties"></properties>

    <typeAliases>
        <! -- Alias a single fully qualified class name -->
        <! --<typeAlias type="com.idol.pojo.Product" alias="product"/>-->
        <! Alias all fully qualified class names under the specified package. The alias is case insensitive.
        <package name="com.idol.pojo"/>
    </typeAliases>
    
    <! -- Configure Mybatis plugin -->
    <plugins>
        <plugin interceptor="com.idol.plugin.MyPlugin">
            <property name="name" value="Run MyPlugin~~~~~~~~~~~"/>
        </plugin>
    </plugins>

    <! -- Set the id attribute of the environment tag to the default attribute of the system environment -->
    <environments default="development">
        <! Configure multiple environment tags -->
        <environment id="development">
            <! -- the value of type attribute: JDBC indicates that the database transaction is managed by JDBC -->
            <transactionManager type="JDBC"></transactionManager>
            <! POOLED --> POOLED --> POOLED
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <! Load a single mapper profile -->
        <! --<mapper resource="ProductMapper.xml"></mapper>-->
        <! Scan all configuration files in dao package -->
        <package name="com.idol.dao"/>
    </mappers>
</configuration>
Copy the code

Key points:

  1. If the database configuration is passedpropertiesTo load the label file, the label must beconfigurationThe first subtag that appears under.
  2. In order to reduce thexxxMapper.xmlFrequently configured in filesPOJOClass fully qualified class name verbose, availabletypeAliasesTag to set aliases for fully qualified class names.typeAliasSet up a single class,packageSets all classes under the specified package.
  3. configurationMybatisThe plugin,pluginsThe label must be intypeAliasAfter the tagenvironmentsTags appear before.
  4. Through themappersTags introducedmapperEnsure that packet scanning is used when configuring filesmapperFile with thedaoThe interface has the same name and is under the same package (compiled).

  1. Mybatis built-in fully qualified class name alias.

Dynamic SQL usage

The usage of Mybatis dynamic tags

, < WHERE >,

,
,

,

,

,

is sorted out by CRUD operation on the product table.

ProductMapper.xml


      
<! DOCTYPEmapper PUBLIC
        "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.idol.dao.ProductMapper">

    <! SQL > select * from database;
    <resultMap id="ExampleMap" type="product">
        <id column="T_id" property="id" javaType="int" jdbcType="INTEGER"></id>
        <result column="T_name" property="name" javaType="string" jdbcType="VARCHAR"></result>
        <result column="T_price" property="price" javaType="double" jdbcType="FLOAT"></result>
        <result column="T_type" property="type" javaType="string" jdbcType="VARCHAR"></result>
    </resultMap>

    <! SQL > create template SQL
    <sql id="BaseSql">
        select id, name, price, type from products
    </sql>

    <! Query all items. Customize the resultMap usage -->
    <select id="findAll" resultMap="ExampleMap">
        select id as T_id, name as T_name, price as T_price, type as T_type from products
    </select>

    <! -- Query individual items based on criteria. Custom SQL template usage; Use where tag with if tag -->
    <select id="findOne" parameterType="product" resultType="product">
        <include refid="BaseSql"></include>
        <! Select * from 'where' where 'and' >
        <where>
            <if test="id ! = null">
                and id=#{id}
            </if>
            <if test="name ! = null">
                and name=#{name}
            </if>
        </where>
    </select>

    <! Query product information in the specified array of ids. Foreach -->
    <select id="findByIdArr" parameterType="list" resultType="product">
        <include refid="BaseSql"></include>
        <where>
            <! -- Checks whether the parameter length is null. Size () for collections and.length--> for arrays
            <if test="array.length &lt; 1">1 = 2</if>
            <! List, array, Map element keys -->
            <foreach collection="array" item="id" open="id in (" close=")" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

    <! -- Add items. Trim tag usage -->
    <insert id="add" parameterType="product">
        insert into products
        <trim prefix="values(" suffix=")" suffixOverrides=",">
            <if test="id ! = null">
                #{id},
            </if>
            <if test="name ! = null">
                #{name},
            </if>
            <if test="price ! = null">
                #{price},
            </if>
            <if test="type ! = null">
                #{type},
            </if>
        </trim>
    </insert>

    <! -- Update merchandise. Set, choose, when, otherwise
    <update id="modify" parameterType="product">
        update products
        <set>
            <if test="name ! = null">
                name=#{name},
            </if>
            <if test="price ! = null">
                price=#{price},
            </if>
            <if test="type ! = null">
                type=#{type},
            </if>
        </set>
        <where>
            <choose>
                <when test="id ! = null">
                    id=#{id}
                </when>
                <otherwise>1 = 2</otherwise>
            </choose>
        </where>
    </update>

</mapper>
Copy the code

Configuration file: one to one query

OrderMapper.xml


      
<! DOCTYPEmapper PUBLIC
        "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.idol.dao.OrderMapper">
    <! -- One-to-one query -->
    <resultMap id="BasePojo" type="order">
        <id column="o_id" property="id" javaType="int" jdbcType="INTEGER"></id>
        <result column="ordertime" property="ordertime" javaType="string" jdbcType="VARCHAR"></result>
        <result column="total" property="total" javaType="double" jdbcType="DOUBLE"></result>

        <association property="customer" javaType="customer">
            <id column="u_id" property="id"  javaType="int" jdbcType="INTEGER"></id>
            <result column="username" property="username" javaType="string" jdbcType="VARCHAR"></result>
            <result column="password" property="password" javaType="string" jdbcType="VARCHAR"></result>
            <result column="birthday" property="birthday" javaType="string" jdbcType="VARCHAR"></result>
        </association>
    </resultMap>

    <select id="findOne" parameterType="int" resultMap="BasePojo">
        SELECT
            o.id AS o_id,
            o.ordertime,
            o.total,
            c.id AS u_id,
            c.username,
            c.`password`,
            c.birthday
        FROM
            orders AS o
            INNER JOIN customer AS c ON o.uid = c.id
            AND o.id = #{id}
    </select>

</mapper>
Copy the code

Configuration file: one-to-many query

CustomerMapper.xml


      
<! DOCTYPEmapper PUBLIC
        "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.idol.dao.CustomerMapper">
    <! -- one-to-many query -->
    <resultMap id="BasePojo" type="customer">
        <id column="u_id" property="id"  javaType="int" jdbcType="INTEGER"></id>
        <result column="username" property="username" javaType="string" jdbcType="VARCHAR"></result>
        <result column="password" property="password" javaType="string" jdbcType="VARCHAR"></result>
        <result column="birthday" property="birthday" javaType="string" jdbcType="VARCHAR"></result>

        <collection property="orders" ofType="order">
            <id column="o_id" property="id" javaType="int" jdbcType="INTEGER"></id>
            <result column="ordertime" property="ordertime" javaType="string" jdbcType="VARCHAR"></result>
            <result column="total" property="total" javaType="double" jdbcType="DOUBLE"></result>
        </collection>
    </resultMap>

    <select id="findOne" parameterType="int" resultMap="BasePojo">
        SELECT
            c.id AS u_id,
            c.username,
            c.`password`,
            c.birthday,
            o.id AS o_id,
            o.ordertime,
            o.total
        FROM
            customer AS c
            INNER JOIN orders AS o ON o.uid = c.id
            AND c.id=#{id}
    </select>

</mapper>
Copy the code

Configuration file: many-to-many query

UserMapper.xml


      
<! DOCTYPEmapper PUBLIC
        "- / / mybatis.org//DTD Mapper / 3.0 / EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.idol.dao.UserMapper">
    <! -- many-to-many query -->
    <resultMap id="BasePojo" type="user">
        <id column="u_id" property="id" javaType="int" jdbcType="INTEGER"></id>
        <result column="name" property="name" javaType="string" jdbcType="VARCHAR"></result>

        <collection property="roleList" ofType="role">
            <id column="r_id" property="id" javaType="int" jdbcType="INTEGER"></id>
            <result column="rolename" property="rolename" javaType="string" jdbcType="VARCHAR"></result>
            <result column="roleDesc" property="roleDesc" javaType="string" jdbcType="VARCHAR"></result>
        </collection>
    </resultMap>

    <select id="findUserAndRole" parameterType="int" resultMap="BasePojo">SELECT u.id AS u_id, u.`name`, r.id AS r_id, r.rolename, r.roleDesc FROM sys_user_role ur INNER JOIN `user` u ON ur.userid = u.id AND u.id = #{id} LEFT JOIN sys_role r ON r.id =  ur.roleid</select>

</mapper>
Copy the code

Many-to-many (many-to-many) is a logical division of the table before the table at the database level, not much different from a one-to-many query at the code level. And many-to-many can be understood as a special one-to-many relationship. For example, the common many-to-many scenario is user and role. It can be thought of as two one-to-many relationships maintained through an intermediate table, that is, one user for multiple permissions, and one permission for multiple users.

Six, notes: one to one query

import com.idol.pojo.Order;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/ * * *@author Supreme_Sir
 * @version 1.0
 * @className IOrderDao
 * @description
 * @date2020/10/6 "* * /
public interface OrderMapper {
    /** ** annotation mode one to one */
    @Results({ @Result(column = "id", property = "id"), @Result(column = "ordertime", property = "ordertime"), @result (column = "total", property = "total"), /* column property = "total" The value of the select attribute in @one is: statementID of the subquery, that is, the fully qualified class name of the subquery. */ @result (column = "uid", property = "customer", one = @One(select = "com.idol.annotation.dao.CustomerMapper.findCustomerByID")) })
    @Select("select id, ordertime, total, uid from orders where id=#{id}")
    Order findOne(Integer id);

    @Select("select id, ordertime, total, uid from orders where uid=#{uid}")
    List<Order> selectOrderByUid(Integer uid);
}

Copy the code

Seven, notes: one – to – many query

import com.idol.pojo.Customer;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/ * * *@author Supreme_Sir
 * @version 1.0
 * @className CustomerMapper
 * @description
 * @date2020/10/6 8:07 * * /
public interface CustomerMapper {
    /** * annotation mode one-to-many */
    @Results({ @Result(column = "id", property = "id"), @Result(column = "username", property = "username"), @Result(column = "password", property = "password"), @Result(column = "birthday", property = "birthday"), /* The value of the column attribute is: the value of the conditional property attribute passing in the next query is: the property name of the Order object in the Customer object javaType the value of the javaType attribute is: the type of the Orders attribute @many the value of the select attribute is: The statementID of the subquery, which is the fully qualified class name of the subquery. */ @result (column = "id", property = "Orders ", javaType = list.class, many = @Many( select = "com.idol.annotation.dao.OrderMapper.selectOrderByUid" )), })
    @Select("select id, username, password, birthday from customer where id=#{id}")
    Customer findOne(Integer id);

    @Select("select id, username, password, birthday from customer where id=#{id}")
    Customer findCustomerByID(Integer id);
}
Copy the code

Eight, notes: many-to-many query

import com.idol.pojo.User;
import org.apache.ibatis.annotations.*;

import java.util.List;

/ * * *@author Supreme_Sir
 * @version 1.0
 * @className UserMapper
 * @description
 * @date2020/10/6 8:36 * * /
public interface UserMapper {

    /*** * annotations are many-to-many */
    @Select("select id, name from user where id=#{userID}")
    @results ({@result (column = "id", property = "id"), @result (column = "name", property = "name"), /* column: The value of the conditional property property passed in for the next query is: the name of the Role object's property in the User object javaType the value of the javaType property is: the type of the roleList property @many the value of the select property is: The statementID of the subquery, which is the fully qualified class name of the subquery. */ @result (column = "id", property = "roleList", javaType = list.class, many = @Many( select = "com.idol.annotation.dao.RoleMapper.findRoleByUid" )) })
    User findUserAndRole(Integer userID);

    /** * add */ to the annotation mode
    @Insert("insert into user values(#{id}, #{name})")
    void insertUser(User user);

    /** ** delete */
    @Delete("delete from user where id=#{id}")
    void deleteUser(Integer id);

    /** ** Update the annotation mode */
    @Update("update user set name=#{name} where id=#{id}")
    void updateUser(User user);

    / * * * query annotation way * /
    @Select("select id, name from user")
    List<User> selectAllUser(a);
}
Copy the code

9. Customize Mybatis plug-in

import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.plugin.*;

import java.sql.Connection;
import java.util.Properties;

/ * * *@author Supreme_Sir
 * @version 1.0
 * @className MyPlugin
 * @description
 * @date2020/10/6 16:51 * * /

@intercepts ({/* type: the processor to intercept method: the method name of the intercepted processor args: */ @signature (type = statementhandler. class, method = "prepare", args = {connection.class, integer.class})})
public class MyPlugin implements Interceptor {
    @Override
    Intercepting method: This method is executed whenever the target method of the intercepted target object is executed
    public Object intercept(Invocation invocation) throws Throwable {
        System.out.println("The target method is intercepted and the original method is enhanced.");
        // Let the original method execute
        return invocation.proceed();
    }

    @Override
    // Generate a proxy for the current interceptor and store it in the interceptor chain
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    // Get the attributes in the sqlmapconfig. XML configuration file
    public void setProperties(Properties properties) {
        System.out.println("The obtained configuration file parameters are:"+ properties); }}Copy the code

PageHelper PageHelper

First add the PageHelper utility class coordinates to the POM file.

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.1.8</version>
</dependency>

<dependency>
    <groupId>com.github.jsqlparser</groupId>
    <artifactId>jsqlparser</artifactId>
    <version>1.2</version>
</dependency>
Copy the code

Then add the PageHelper plug-in to sqlmapconfig.xml.

<plugins>
    <plugin interceptor="com.idol.plugin.MyPlugin">
        <property name="name" value="Run MyPlugin~~~~~~~~~~~"/>
    </plugin>
    <plugin interceptor="com.github.pagehelper.PageInterceptor">
        <! -- 3.* version needs to specify database dialect -->
        <! Type, set up the database Oracle, Mysql and MariaDB, SQLite, Hsqldb, PostgreSQL six database - >
        <! Pagehelper 5.1.8 pageHelper 5.1.8 pageHelper 5.1.8 pageHelper 5.1.8 PageHelper
        <! -- <property name="dialect" value="mysql"/> -->
    </plugin>
</plugins>
Copy the code

Note:

  1. PageHelperThe 5.* version of the interceptor iscom.github.pagehelper.PageInterceptor, 3.* version of the interceptor iscom.github.pagehelper.PageHelper.
  2. PageHelperYou do not need to configure the database dialect for the * version. You need to configure the database dialect for the * version.

A final example of using PageHelper:

@Test
public void pagehelperTest(a) {
    PageHelper.startPage(1.2);
    PageInfo<Product> pageInfo = new PageInfo<Product>(productDao.findAll());
    List<Product> products = pageInfo.getList();
    for (Product product : products) {
        System.out.println(product);
    }
    System.out.println("Total number of items:"+pageInfo.getTotal());
    System.out.println("Total pages:"+pageInfo. getPages ());
    System.out.println("Current page:"+pageInfo. getPageNum());
    System.out.println("Length per page:"+pageInfo.getPageSize());
    System.out.println("Is the first page:"+pageInfo.isIsFirstPage());
    System.out.println("Is the last page:"+pageInfo.isIsLastPage());
}
Copy the code

Output result:

Product{id=1, name=' keybox ', price=30.0, type=' keybox '} Product{id=2, name=' keybox ', price=400.0, type=' keybox '} 1 Length per page: 2 Yes First page: true Last page: falseCopy the code

General usage of Mapper

First add mapper utility class coordinates to the POM file.

<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper</artifactId>
    <version>3.1.2</version>
</dependency>
Copy the code

Then configure the generic Mapper plug-in

<plugin interceptor="tk.mybatis.mapper.mapperhelper.MapperInterceptor">
    <! -- Generic Mapper interface, multiple generic interfaces separated by commas -->
    <property name="mappers" value="tk.mybatis.mapper.common.Mapper"/>
</plugin>
Copy the code

Next, create the POJO and DAO interface objects

import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

/ * * *@author Supreme_Sir
 * @version 1.0
 * @className Product
 * @description
 * @date2020/10/6 21:49 * * /
@Table(name = "products")
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    private String name;
    private Double price;
    private String type;

   	// Getter and Setter    
}
Copy the code
import com.idol.mapper.pojo.Product;
import tk.mybatis.mapper.common.Mapper;

/ * * *@author Supreme_Sir
 * @version 1.0
 * @className ProductDao
 * @description
 * @date2020/10/6 21:52 * * /
public interface ProductDao extends Mapper<Product> {}Copy the code

Finally, examples of generic Mapper usage

import com.idol.mapper.dao.ProductDao;
import com.idol.mapper.pojo.Product;
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 tk.mybatis.mapper.entity.Example;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

/ * * *@author Supreme_Sir
 * @version 1.0
 * @className MapperTest
 * @description
 * @date2020/10/6 if * * /
public class MapperTest {
    private ProductDao mapper;

    @Before
    public void ready(a) throws IOException {
        InputStream inputStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        mapper = sqlSession.getMapper(ProductDao.class);
    }

    @Test
    Example usage of generic Mapper: query all
    public void mapperSelectAllTest(a) {
        List<Product> products = mapper.select(null);
        for(Product product : products) { System.out.println(product); }}@Test
    Example of generic Mapper usage: query by ID
    public void mapperSelectOneTest(a) {
        Product product = mapper.selectOne(new Product(1.null.null.null));
        System.out.println(product);
    }

    @Test
    // Example of generic Mapper usage: Insert record
    public void mapperInsertTest(a) {
        mapper.insert(new Product(12."Pants".100d."Articles of daily use"));
    }

    @Test
    Example of generic Mapper usage: Delete a record
    public void mapperDeleteTest(a) {
        mapper.delete(new Product(12."Pants".100d."Articles of daily use"));
    }

    @Test
    // Example of generic Mapper usage: update records
    public void mapperUpdateTest(a) {
        mapper.updateByPrimaryKey(new Product(11."Pants".100d."Articles of daily use"));
    }

    @Test
    Example of generic Mapper usage: conditional query
    public void mapperExampleTest(a) {
        Example example = new Example(Product.class);
        example.createCriteria().andEqualTo("type"."Articles of daily use");
        List<Product> products = mapper.selectByExample(example);
        for(Product product : products) { System.out.println(product); }}}Copy the code

The source code

Download the source code

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- to lay a lightning comes, you must be present -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --