• Now all data be ready for publish, We had made our blog alive now. Haha.



  • These days CCP GFW blocked all the IPs from US and when I switch my IP in google Cloud, the disk data cannot be restore again. I have to restart my blog totally, Now the website is still building…



Avoid WHERE 1 = 1 in SQL

Java PingBook 1 months ago (10-24) 23 0

The most common thing to do in dynamic SQL is conditionally include a part of a where clause. For example:

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

What happens if none of the conditions are met? You would end up with SQL that looked like this:

SELECT * FROM BLOG
WHERE

MyBatis has a simple answer that will likely work in 90% of the cases. And in cases where it doesn’t, you can customize it so that it does. With one simple change, everything works fine:

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

The where element knows to only insert “WHERE” if there is any content returned by the containing tags. Furthermore, if that content begins with “AND” or “OR”, it knows to strip it off.




Copyright from PingBook Blog, If not specified, they are original. This site uses BY-NC-SAProtocol authenticated.
For reprinting, please indicate the link of the original text:Avoid WHERE 1 = 1 in SQL
LIKE (0)
[1725641479@qq.com]
SHARE (0)
PingBook
Author:
We create, We sharing! Tag every value data your sharing
Submit comments
Cancel comments
emoji picture bold strikethrough center italic check in

Hi,you need to provide your name and email adress!

  • Name (Required)
  • Email (Required)
  • Website