MyBatis single form CRUD operation

The CRUD operation of single standard is mainly based on how to write high-quality SQL statements. If the column and entity attributes in the table do not correspond to each other in single standard operation, it is suggested to use aliases for mapping.

# The difference between {}/ ${value}

• # {} denotes placeholders
• The ${value} is a splicing of SQL strings where value must be written. If the parameter type is an entity class, then the attribute name in the entity class is written here.

Writing of Fuzzy Query SQL Statement

- SQL:select * from tb_user where username like '%username%'

- MyBatis:
	- select * from tb_user where username like concat('%',#{username},'%')
    
	- select * from tb_user where username like "%"#{username}""%";
	
	- select * from tb_user where username LIKE '%${value}%' ;
	    - ###If the transfer is a simple data type, value must be written in ${}

Reconstructing Extracting Repeated SQL

Principle of Reconstruction: Write once, no more than three times, if more than three times, need to be reconstructed.

There is an SQL fragment in MyBatis. Extract duplicated SQL

<sql id="tbuser">
	username,
	password,
	email,
	phone
</sql>

Use <include refid="tbuser"/>
Object-oriented design features: encapsulation, inheritance, polymorphism.
Principle of Reconstruction: Write once, no more than three times, if more than three times, need to be reconstructed.

<?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="shop.tbzj.mapper.UserMapper">

    <sql id="tbUserInfo">
        a.id,
          a.username,
          a.password,
          a.phone,
          a.email,
          a.created,
          a.update
    </sql>

    <select id="selectAll" resultType="TbUser">
        SELECT
          <include refid="tbUserInfo"/>
        FROM
          tbuser AS a
    </select>

    <select id="selectByUsername" resultType="TbUser">
       SELECT

        <include refid="tbUserInfo"/>
  FROM
	tbuser AS a
  WHERE
	username

	LIKE CONCAT("%",#{username},"%");

    </select>
</mapper>

Writing of mapping file for multi-table association operation

Single table or one-to-one result mapping

- If entity attributes and column names are inconsistent in a form operation, in addition to using aliases,
 You can also use `resultMap'for one-to-one mapping in addition to.

<?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="shop.tbzj.mapper.UserMapper">

    <sql id="tbUserInfo">
        a.id,
          a.username,
          a.password,
          a.phone,
          a.email,
          a.created,
          a.update
    </sql>

    <select id="selectAll" resultMap="tbUserMapping">
        SELECT
          <include refid="tbUserInfo"/>
        FROM
          tbuser AS a
    </select>

    <!-- resultMap Ultimately, the result is mapped to pojo On, type It specifies which one to map to. pojo -->
    <resultMap id="tbUserMapping" type="TbUser">
        <!-- Primary key ,It's very important. If it is multiple fields,Define multiple id -->
        <!-- property: The primary key is in pojo Property name in -->
        <!-- column: Column name of primary key in database -->
       <!--Primary key -->
        <id column="id" property="id"/>
        <!-- Define common attributes -->
        <result column="username" property="user_name"/>
        <result column="password" property="password"/>
        <result column="phone" property="phone"/>
        <result column="email" property="email"/>
        <result column="created" property="created"/>
        <result column="update" property="update"/>
    </resultMap>


<select id="selectByUsername" resultMap="tbUserMapping">
       SELECT
        <include refid="tbUserInfo"/>
        FROM
        	tbuser AS a
        WHERE
        	username
        LIKE CONCAT("%",#{username},"%");
</select>

Relevant Result Set Mapping

One-on-one

For example, an order belongs to a user.

For example, the relationship between the user table and the order table is that a user corresponds to multiple orders. There are two ways to process the mapping of query results.

• Result set mapping uses resultType.
  Specialized use of a pojo class contains order information and user information.
  For example:

    public class UserOrder extends Order{
    //The following fields are user information fields
    //Inheritance is used to inherit the non-private fields of the Order class.
        private Long id;
        private String user_name;
        private String password;
        private String phone;
        private String email;
        private Date created;
        private Date update;
        
    }
    
    //Write "UserOrder" in resultType.
    
    
    <select id="queryOrderUser" resultType="orderUser">
	SELECT
    	o.id,
    	o.user_id
    	userId,
    	o.number,
    	o.createtime,
    	o.note,
    	u.username,
    	u.address
	FROM
	    `order` o
	LEFT JOIN 
	    `user` u ON o.user_id = u.id
</select>
    
//One drawback is that inheritance in java does not inherit private ownership. If the subclass accesses the non-private attributes and methods of the parent class, they are accessed by the keyword super().

//Classes modified by final cannot be inherited.

• Result set mapping using resultMap

<resultMap type="order" id="orderUserResultMap">
	<id property="id" column="id" />
	<result property="userId" column="user_id" />
	<result property="number" column="number" />
	<result property="createtime" column="createtime" />
	<result property="note" column="note" />

	<!-- association : Configure one-to-one attributes -->
	<!-- property:order Inside User Property name -->
	<!-- javaType:Attribute type -->
	<association property="user" javaType="user">
		<!-- id:Declare the primary key to indicate user_id Is the unique identifier of the associated query object-->
		<id property="id" column="user_id" />
		<result property="username" column="username" />
		<result property="address" column="address" />
	</association>
</resultMap>

<!-- One-to-one associations, queries orders, orders containing user attributes -->
<select id="queryOrderUserResultMap" resultMap="orderUserResultMap">
	SELECT
	o.id,
	o.user_id,
	o.number,
	o.createtime,
	o.note,
	u.username,
	u.address
	FROM
	`order` o
	LEFT JOIN `user` u ON o.user_id = u.id
</select>

One-to-many result mapping

For example, a user has one or more orders

public class User{
    private int id;
    private String username;
    private Date birthday;
    private String addres;
    //A user has multiple orders stored using the List process.
    private List<Order> orders;
}

UserMapper.xml Writing:

<resultMap type="user" id="userOrderResultMap">
	<id property="id" column="id" />
	<result property="username" column="username" />
	<result property="birthday" column="birthday" />
	<result property="sex" column="sex" />
	<result property="address" column="address" />

	<!-- Configure one-to-many relationships -->
	<collection property="orders" javaType="list" ofType="order">
		<!-- The key to configuration is association. Order Unique ID -->
		<id property="id" column="oid" />
		<result property="number" column="number" />
		<result property="createtime" column="createtime" />
		<result property="note" column="note" />
	</collection>
</resultMap>

<!-- One-to-many association, querying orders and querying orders placed by the user at the same time -->
<select id="queryUserOrder" resultMap="userOrderResultMap">
	SELECT
	u.id,
	u.username,
	u.birthday,
	u.sex,
	u.address,
	o.id oid,
	o.number,
	o.createtime,
	o.note
	FROM
	`user` u
	LEFT JOIN `orders` o ON u.id = o.user_id
</select>
    

Insertion data return primary key problem

• The table primary key type in MySQL is self-increasing

public class Order {
    private Long id;
    private String name;
    private Double price;
    }

OrderMapper.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="shop.tbzj.mapper.OrderMapper">
    <insert id="insertOrder" useGeneratedKeys="true" keyProperty="id">
          insert into tb_order(name,price)values(#{name},#{price});
    </insert>
</mapper>

Test:

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import shop.tbzj.entity.Order;
import shop.tbzj.mapper.OrderMapper;


@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath:spring-context.xml")
public class TestOrder {

    @Autowired
    private OrderMapper orderMapper;

    @Test
    public void insertOrder() {
        Order order = new Order();
        order.setName("tom2");
        order.setPrice(22.3);

        orderMapper.insertOrder(order);
        //Insert a data and return the primary key id to the id in the entity class Order
        System.out.println(order.toString());
    }

}

• The table primary key type in MySQL is UUID type

<! - Save the order without parameterType because it's in spring-contxt.xml
    Configured in sqlSession Factroy
-->
<insert id="saveOrder">
	<! - SelectKey tag returns the primary key - >.
	Key Column: Which column in the table corresponding to the primary key
	Key Property: Which property in the pojo corresponding to the primary key
	<! - order: The sql to execute the query id before executing the insert statement or the sql UUID to execute the query id after executing the insert statement is BEFORE-->
	<! -- resultType: Set the type of id returned - >
	<selectKey keyColumn="id" keyProperty="id" order="BEFORE"
		resultType="string">
		SELECT LAST_INSERT_ID()
	</selectKey>
	insert into tb_order(id,name,price)values(#{id},#{name},#{price})
</insert>

select last_insert_id(); see what the last insert value is.

How to resolve primary key ID conflict in distributed cluster?
    The discussion here is about id for automatic growth: setting the step size
        One drawback is that if the number of clusters is changed, the step size needs to be reset.
        
    Query self-incremental step size
    SHOW VARIABLES LIKE 'auto_inc%'
    Modify the self-increasing step size
    SET @@auto_increment_increment=10;
    Modify the starting value
    SET @@auto_increment_offset=5;