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