Existence reason
When we query in the database, we write fixed SQL, that is to say, we have to write a SQL for each query. But in the program, it is obvious that if there are too many fields in a table, we can query and update according to the combination of any fields. The amount of code is particularly huge. At this time, dynamic SQL appears to solve this problem, that is, for the condition I We splice, query if there is a condition, and do not query if there is no condition.
<select id="queryAuthor" resultType="Author" > select * from Author where 1=1 <if test="sex!=null">AND sex=#{sex}</if> <if test="aname!=null">AND aname=#{aname}</if> </select>
As can be seen from this code, we can query by gender or by name, and pay attention to where statement. Because it is SQL splicing, we should pay attention to the integrity and effectiveness of fields. This can be solved with labels,
<select id="queryTrim" resultType="Author"> select * from Author <trim prefix="where" prefixOverrides="AND|OR"> <if test="sex!=null">AND sex=#{sex}</if> <if test="aname!=null">AND aname=#{aname}</if> </trim> </select>
Here you can see that prefix is used to solve the problem. Here it represents the prefix of splicing SQL. prefixOverrides represents the string to be overwritten. Here, and or can be overwritten. That is to say, if and or are redundant, then you can directly overwrite.
When querying, we can also select conditions according to the situation, that is, use the choose tag, as well as when and other ways to query
<select id="querywhen" resultType="Author"> select * from Author where 1=1 <choose> <when test="sex!=null">and sex=#{sex}</when> <when test="aname!=null">and aname like #{aname}</when> <otherwise> and sex="male" </otherwise> </choose> </select>
In this case, only one of the conditions needs to be met and only one of them can be checked. If two conditions are met, then select according to the sequence of codes, and query as long as one is matched.
The dynamic processing of inserting a field involves writing all the possible fields to be inserted. In the actual insertion, the fields that need to be spliced can be inserted.
<insert id="insertCar" parameterType="Car" keyProperty="cid"> insert into car <trim prefix="(" suffix=")" suffixOverrides=","> <if test="cname!=null">cname,</if> <if test="cnum!=null">cnum,</if> <if test="color!=null">color,</if> <if test="csize!=null">csize,</if> <if test="cperson!=null">cperson,</if> </trim> values <trim prefix="(" suffix=")" suffixOverrides=","> <if test="cname!=null">#{cname},</if> <if test="cnum!=null">#{cnum},</if> <if test="color!=null">#{color},</if> <if test="csize!=null">#{csize},</if> <if test="cperson!=null">#{cperson},</if> </trim> </insert>
Update operation, the fields we update are often only part of it, so we need to dynamically rely on the conditions
<update id="updateCar" parameterType="Car"> update car <set> <if test="cname!=null">cname=#{cname},</if> <if test="color!=null">color=#{color},</if> <if test="cnum!=null">cnum=#{cnum},</if> <if test="csize!=null">csize=#{csize},</if> <if test="cperson!=null">cperson=#{cperson},</if> cid=#{cid} </set> where cid=#{cid} </update>
During query, we may perform some queries within the scope, such as:
SELECT * FROM car WHERE cid IN (1,3)
<select id="selectCar" resultType="Car"> select * from Car where cid in <foreach item="cars" collection="list" open="(" separator="," close=")"> #{cars} </foreach> </select>
Use foreach tag to traverse our incoming collection. Collection represents the type of the incoming collection. On the official website, it can be list, set or map, but I made an error when using set. Only list is right. This needs to be interpreted in detail by the source code. If it is solved, I will supplement it in the future, and welcome the visitors to let me know.
When judging this condition, we must pay attention to that if we judge whether a property is empty in the code, then we can't define it as a basic type when defining the property, otherwise, there is already a value when initializing, and there is no saying that the judgment is empty.
