Remove a query condition from an SQL statement

Job Requirements: Query data according to different conditions, there are drop-down list and radio button two

If you're just querying for a specific condition, it's simple, just add the appropriate condition after the where condition, like this: where type = ' outpatient '. When the query is all or all, it is clear that the corresponding conditions should be removed, if you are using MyBatis, it is very simple, just need to add some judgment in the XML mapping file, like this:

 <select id= "user" parametertype= "user" resulttype= "user" > select  * from user   <where> <if
      test= "Id!=null and id!=" "> ID  =#{id  </if  > <if  Test= "Type!=null and type!=" "> and type  = #{type}  </if  > <if
       test= "Gender!=null and gender!=" "> and gender  =#{gender}  </if  > </where> < /select> 

But the requirements of the project are often strange, because my SQL statement is written in the JSON configuration file, and I am using the MyBatis framework, I can not write SQL statements directly in the MyBatis XML map file, I only have to pass SQL as a parameter to the mapping file, Just like the above passing id,type,gender.

<select id= "ExecuteSQL" parametertype= "java.lang.String" resulttype= "Map" >        <![ cdata[${sql}]]>     </select>

Because I'm passing a SQL statement instead of a specific condition, XML will escape me when special characters such as comparison characters appear in my SQL statement, and I don't need XML to escape my SQL, so I'm going to use

<! [cdata[  

Wrap up my SQL statements so that the SQL statements are not escaped. With ${SQL} instead of #{SQL}, #将传入的数据都当成一个字符串 will add a double quotation mark to the automatically passed data, and the direct display of the incoming data is generated in SQL, obviously my SQL statement does not need double quotes. (Here I also do not consider the use of $ will cause SQL injection problems, later I will do SQL check)

Now that the background is clear, if you want to complete the requirements, I only have to dispose of some conditions before SQL enters the XML mapping file. SQL is written in the JSON configuration file, the pre-conditions are also written dead, as follows:

WHERE and C.YLJGMC = #{select1_val_select}  and type = #{radio1_val_radio}"

When the user chooses to select all or all, I need to

and C.YLJGMC = #{select1_val_select} and  type = #{radio1_val_radio}

Remove. Not all or all, I need to put

and the  #{radio1_val_radio}

replaced with the appropriate conditions.

The code is as follows:

/*** Processing SQL statements * *@paramSQL *@return     */     Public Staticstring Dealsql (String sql, String str) {SQL= Sql.trim (). toUpperCase (). ReplaceAll ("+", "" "); intform = Sql.indexof ("from"); String begin= sql.substring (0, form); Begin= Begin.replaceall ("as", "as C_"); String End=sql.substring (Form, sql.length ()); SQL= begin+end; String[] Split1= Str.trim (). toUpperCase (). ReplaceAll ("'", ""). ReplaceAll ("\" "," "). Split (", ");  for(String s:split1) {if(Stringutils.isnotblank (s)) {string[] split2= S.split (":"); if(Sql.contains (split2[0])) {                    if(Split2[0].contains ("Val_radio") | | split2[0].contains ("val_select"))) {                            if(Split2[1].equals ("Select All") | | split2[1].equals ("all")) ) {SQL=removesql (sql, "#{" +split2[0]+ "}"); }Else{SQL= Sql.replace ("#{" +split2[0]+ "}", "'" + split2[1] + "'"); }                    }Else{SQL= Sql.replace ("#{" +split2[0]+ "}", "'" + split2[1] + "'"); }                }            }        }        returnSQL; }

The JSON configuration file specifies that the required fields are used as aliases, but if the alias is a number or a special character, Oracle is not recognized, if the alias is enclosed in double quotation marks, Orace but the JSON file is not recognized, so I use the most low method, Add all aliases to a c_ so that the database is recognized.

Take a look at the Removesql method:

/*** Remove SQL statement for a query condition *@paramSQL *@paramChoice *@return     */     Public Staticstring Removesql (String sql,string choice) {intCho_first =sql.indexof (choice); intBefore_and = Sql.lastindexof ("and", Cho_first); intBefore_where = Sql.lastindexof ("where", Cho_first); intAfter_and = Sql.indexof ("and", Cho_first); intAfter_where = Sql.indexof ("where", Cho_first); if(Before_where! =-1) {            if(Before_and! =-1) {                if(Before_and >before_where) {SQL= Sql.replace (sql.substring (Before_and, Cho_first), ""). Replace (choice, ""); }Else {                    if(After_and! =-1) {                        if(After_where! =-1) {                            if(After_and <after_where) {SQL= Sql.replace (sql.substring (before_where+7, after_and+5), ""); }Else{SQL= Sql.replace (sql.substring (Before_where, Cho_first), ""). Replace (choice, ""); }                        }Else{SQL= Sql.replace (sql.substring (before_where+7, after_and+5), ""); }                    }Else{SQL= Sql.replace (sql.substring (Before_where, Cho_first), ""). Replace (choice, ""); }                }            }Else{                if(After_and! =-1) {                    if(After_where! =-1) {                        if(After_and <after_where) {SQL= Sql.replace (sql.substring (before_where+7, after_and+5), ""); }Else{SQL= Sql.replace (sql.substring (Before_where, Cho_first), ""). Replace (choice, ""); }                    }Else{SQL= Sql.replace (sql.substring (before_where+7, after_and+5), ""); }                }Else{SQL= Sql.replace (sql.substring (Before_where, Cho_first), ""). Replace (choice, ""); }            }        }        returnSQL; }

Logic is: When the conditions in different places, the processing method is different, the following is a list of possible conditions of the place, the above code is for these different places, these conditions together with its former and or where (does not affect the subsequent and conditions of use of where, The condition of the first position of the second statement) is removed. (for complex SQL, such as using with as a temporary table for querying data, and then using Select to query the temporary table, the code cannot be processed)

Remove a query condition from an SQL statement