one , What is advanced query :

① Multi condition filter query

in brief , Namely   Splicing sql sentence  , stay sql Use after query statement :
where condition 1 and/or condition 2 and/or condition 3 …
② Paging query

two , Multi condition filter query :

1,WHERE 1=1:

​ Is a forever true Conditions of 【mysql Security injection vulnerability 】, adopt 1=1 Conditions of ,  Avoid judgment where Which field should be received later  【 Considering that several fields of splicing are empty 】.
StringBuilder sql = new StringBuilder(); sql.append(" WHERE 1=1"); // Splice name if
(StringUtils.isNotBlank(name)) { sql.append(" AND name LIKE ?");
parameters.add("%" + name + "%"); } // Minimum age of splicing if (minAge != null) {
sql.append(" AND age >= ?"); parameters.add(minAge); }
2,WHERE 1=1 problem : Affect performance , Because it cannot be used   Index query   Yes .

solve where 1=1 Indexing problems 【  Define a container , When the container is placed in the condition ,  Conditions for taking out the container  ( The first condition is spliced before where, Other splices and) 】
sql.append(" WHERE "); // utilize Apached Components of Apache commons-lang
assembly :StringUtils of join method : Connect each element in the set with a specific string
sql.append(StringUtils.join(conditions, " AND "));
3, thinking : Encapsulation and separation of duties

1: encapsulation : Too many parameters should be encapsulated into an object

2: Separation of duties : Who owns the data , Who should include the method of operating the data

4, Keyword query ( Query from multiple columns )

for example : Query the product name or brand with ' China '.

details : AND Priority of is higher than OR  Keyword query   Multi column query splicing sql  Remember to use () Enclose
// Exposure to subclasses : Let subclasses in customizedQuery Call in , Add query conditions and parameters of bytes protected void addQuery(String
condition, Object... param) {
// details : Variable length parameters are arrays ---- aggregate parameters.add( array ) Is to add an array as an element
// solve : First convert the array into a set , Then assemble parameters.addAll( aggregate ) this.conditions.add(condition);
this.parameters.addAll(Arrays.asList(param)); } // Splicing keywords if (keyword != null) {
super.addQuery("(name LIKE ? OR dormBuildId LIKE ?)","%" + keyword + "%", "%" +
keyword + "%"); }

Technology
Daily Recommendation
views 6
views 5