explain

This column specifically records some problems and solutions in their actual combat projects, including the expansion of this knowledge point.

experience

1, When querying and then modifying the data in the same database, you can directly write an SQL statement

<update id="updateCloneGrandTotalTotalSyncService" parameterType="java.util.List">
        update crm_cloned_resource a, (
            SELECT cloned_code, count(*) totalCooperationCount FROM `crm_main_order` WHERE open_time IS NOT NULL GROUP BY cloned_code
        ) b  set a.total_cooperation_count = b.totalCooperationCount where a.cloned_code = b.cloned_code
    </update>

2, Swagger document field comments

(1) Request parameter comment

(2) Return value type comment

This entity needs to be added when returning the value

(3) Advanced query

1) Train of thought
(1) All conditions are written into an SQL (the dic_city table is used in the agent region and agent location. How to judge the returned value?)
Advantages: easy paging
Disadvantages: high maintenance and error prone splicing of many tables.
(2) Split SQL
SQL is only used as query data, and then processed by code. Multiple tables are processed by agent_id.
2) Split SQL logic
From the field classification, we can see that the company name, agent level, agent location, contract period, Department / Manager (only manager Id) and status are all in the agent_info table.
The agent region and agent are through the intermediate table agent_ city_ Relationship. So we can divide the situation.
(1) Only query the company name, agent level, agent location, contract period, Department / Manager (only manager Id) and status
Directly through paging, and then conditionally query the agent_ Just the data in the info table
(2) Query proxy region only
Gets the proxy region id of the query
Query the corresponding agent information through the agent area id - Page
(3) Mixed query (1) and (2)
Paging has been performed when querying the information of all agents in the agent area ------------ (1), so it is not necessary to process paging separately
Query all the information that does not contain the agent area
Both through agent_id is associated and filtered.
(4) Query with nothing selected

4, @ DateTimeFormat(pattern = "yyyy MM DD HH: mm: SS"

reason:
Java error Failed to convert property value of type 'Java lang.String’ to required type 'java.util.Date
The reason is that the string is transmitted from the front end to the background, and the Date date type of Java class attribute needs the @ DateTimeFormat annotation at this time, but pay attention to the usage
So it needs to be annotated

/**
* birthday
*/
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private Date eBirthday;

5, When developing, you must be optimistic about which fields you need, and carry out logical processing according to the fields, so that the fields will not fall (you can add, delete, modify and query).

6, Add verification

You can only enter 0 and 1 for this status, but you need to add the @ Validated annotation on the controller

7, Collectionutil isNotEmpty()

This includes both cases where the collection is Null and cases where the collection size() is 0

8, ctrl+R in idea can realize full-text query and replacement

9, Usage of BETWEEN... AND in Mysql

• syntax
WHERE column BETWEEN value1 AND value2
WHERE column NOT BETWEEN value1 AND value2
Generally, value1 should be less than value2. When the not operator is added before between, it means the opposite of Bewtween, that is, select values outside this range.
• scope
Contains value1 and value2

• type
It can be a number or a letter. If it is a timestamp, it will be more efficient

10, MySQL CONVERT() function in Mysql

When we need to convert to datetime, we need to add splicing

<if test="agentInfo.expStartTime!=null and agentInfo.expStartTime!='' and agentInfo.expEndTime!=null and agentInfo.expEndTime!='' ">
    and
    t1.contract_expiration_time BETWEEN CONVERT(CONCAT(#{agentInfo.expStartTime},' 00:00:00'),datetime) AND  CONVERT(CONCAT(#{agentInfo.expEndTime},' 00:00:00'),datetime)
</if>
<if test="agentInfo.expStartTime!=null and agentInfo.expStartTime!='' and  agentInfo.expEndTime==null ">
    and
    t1.contract_expiration_time   &gt;= CONVERT(CONCAT(#{agentInfo.expStartTime},' 00:00:00'),datetime)
</if>
<if test="agentInfo.expEndTime !=null and agentInfo.expEndTime !='' and agentInfo.expStartTime==null">
    and
    t1.contract_expiration_time   &lt;= CONVERT(CONCAT(#{agentInfo.expEndTime},' 00:00:00'),datetime)
</if>