In the project, mybatis is used as the persistence layer framework and mysql database. Before the project goes online, the DBA requires us to set the fields in each database table to the default value and not null. In the previous project, some insert statements listed all the fields in the table, and then used it as a general insert statement. Take a simple example: if a database table blog has the following fields: id, title, content and author, the default value Empty String is set for each field except id, and an insert statement is written as follows:  

<insert id="addOneBlog" parameterType="main.Blog">  
    insert into blog(title,author,content)  
    values(#{title},#{author},#{content})  
</insert>  

I thought it would be all right to have such an insert statement. We thought that no matter how many fields there are, we would use this insert statement. If the field has no value, it will be assigned as the default value of mysql field...  

    But it turns out that this is not the case at all.  

    When we only assign a value to the title field and then execute an insert statement, mybatis immediately reports such an exception:  

1. Caused by: com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException: Column 'content' cannot be null  

     Obviously, all fields specify not null. It seems that according to this insert method, the unassigned fields are not assigned the default value of mysql.   What if you remove the not null restriction of database fields? Execute the insert operation just now again, and no exception is thrown this time. However, after viewing the database, we find that the fields without assignment in the newly inserted table records are still not the default value of mysql, but the null value.  

    Therefore, according to the so-called general insert statement above, the value of an unassigned field cannot be changed to the mysql default value. This kind of insert statement is not universal.

1. Problem analysis

Because we mistakenly regard an insert statement containing all fields as a general insert. Obviously, the way to solve this problem is that we need to write different sql in strict accordance with the fields to be inserted according to different business requirements. The fields that do not need to be inserted cannot appear in the insert statement.

The sql can be written as follows:

<insert id="addOneBlog" parameterType="main.Blog">  
    insert into blog(author)  
    values (#{author})  
</insert>

Although this method works, in the actual project development, in order to meet the needs of development efficiency, we still hope to have a general insert statement for all businesses involving single table insertion. Just now, mybatis has a very powerful feature: dynamic sql, which can be used to solve this problem.

2. Problem solving

1) Introduction to < SQL >, < include >, < trim > tags

    The dynamic sql function of mybatis contains many practical Tags: < sql > tag represents an sql fragment. Using this tag can not only reuse a lot of sql code, but also make the sql statement clearer; After defining the < sql > tag, use the < include > tag where it is called to splice the defined sql fragments< The trim > tag can add or overwrite characters arbitrarily at the beginning and end of the sql fragment in the tag body.

The final solution is as follows:

<sql id="blogColumns">  
    <trim suffixOverrides=",">  
        <if test="title != null">title,</if>  
        <if test="author != null">author,</if>  
        <if test="content != null">content</if>  
    </trim>  
</sql>  
  
<sql id="blogValues">  
    <trim suffixOverrides=",">  
        <if test="title != null">#{title},</if>  
        <if test="author != null">#{author},</if>  
        <if test="content != null">#{content}</if>  
    </trim>  
</sql>  
  
<insert id="addOneBlog" parameterType="Blog">  
    insert into blog(<include refid="blogColumns"/>)  
    values (<include refid="blogValues"/>)  
</insert>  

reference resources:

https://www.cnblogs.com/yuhuameng/p/10703931.html

https://blog.csdn.net/c851204293/article/details/93623200