A Summary
1. What is dynamic query?
Random selection of several query conditions from a number of combinations into a DQL statement for query, this process is called dynamic query.
2. Difficulties of Dynamic Query
There are many optional query conditions and combinations, so it is difficult to list them one by one.
3. Composition of Final Query Statement
Once the user enters data into the query condition, the query condition becomes part of the final condition.
Two basic principles
1. Basic framework of SQL
Regardless of the query conditions, the query fields and databases are fixed and invariant, which constitute the basic framework of SQL statements, such as
select column... from table.
2.StringBuilder Forms DQL
Get form input, if the request parameter is not empty, generate query conditions according to the request parameter, such as "name=?", "age>?", and append the query conditions to the basic framework. When StringBuilder is used to append query conditions, a problem arises. How to judge whether "and" is needed in the generated query conditions?
If the query condition is the first query condition, there is no need to add "and" or "and". The problem becomes more complex. Every time a query condition is generated, it is necessary to determine whether there is a query condition in front of it.
We can consider adding a query condition in the basic framework of SQL. The existence of the query condition does not affect the query results, but only acts as a placeholder, avoiding the need to add "and" when adding query conditions dynamically. According to these requirements, this query condition must always be true. Here we take "1 = 1" and the basic framework of SQL becomes
select column...from table where 1=1
Add "and" to the preceding paragraph of each dynamic query condition.
3.List sets are placeholder assignments
With DQL statements, you need to consider how to assign placeholders. While generating query conditions, we can collect the parameters corresponding to placeholders and store them in an ordered set. Here we select the List set, so that the placeholders correspond to the elements in the List set in sequence. The nth placeholder corresponds to the nth element, and traversing the set can assign values to placeholders.
When assigning placeholders, it is not only necessary to transfer data to placeholders, but also need to select data types consistent with fields. List sets can not only store data, but also need to add field information to distinguish different fields and select different data types. Here the elements in the collection take the form of "column+data".
Three Demo s
1. Database
2. Pages
<!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <style> span { display: inline-block; width: 75px; margin-bottom: 15px; } </style> <title>Dynamic Query</title> </head> <body> <form action="http://localhost:8080/JavaSETest/dynamicQueryServlet"> <div> <span>Full name:</span><input type="text" name="name"> </div> <div> <span>Gender:</span><input type="text" name="sex"> </div> <div> <span>Age:</span><input type="text" name="age"> </div> <div> <span>Department Number:</span><input type="text" name="depNo"> </div> <div> <input type="submit"value="query"> <input type="reset"value="Reset"> </div> </form> </body> </html>
3. Servlet
package com.javase.jdbc; import java.io.IOException; import java.io.PrintWriter; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import javax.servlet.ServletException; import javax.servlet.annotation.WebServlet; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; @WebServlet("/dynamicQueryServlet") public class DynamicQueryServlet extends HttpServlet { private static final long serialVersionUID = 1L; @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html;charset=UTF-8"); // Get the request parameters String name = request.getParameter("name"); String sex = request.getParameter("sex"); String age = request.getParameter("age"); String depNo = request.getParameter("depNo"); // The key is"where 1=1"，No need to determine whether additional query conditions need to be added before they are added and，Unification added before and String baseSQL = "select name,sex,age,depNo from tb_employee where 1=1"; StringBuilder builder = new StringBuilder();// For stitching SQL Sentence // For mapping between placeholders and parameter values, placeholders and parameter values are sorted in the same order in their respective sequences, for example name The placeholder for the SQL First in the sentence,name The parameter values are in
// First in the collection. List<String> params = new ArrayList<String>(); builder.append(baseSQL); if (isNotEmpty(name)) { builder.append(" and name=? "); params.add("name," + name);// Collections can not only store specific data, but also store field names so that data types can be selected according to field names. } if (isNotEmpty(sex)) { builder.append(" and sex=? "); params.add("sex," + sex); } if (isNotEmpty(age)) { builder.append(" and age=? "); params.add("age," + age); } if (isNotEmpty(depNo)) { builder.append(" and depNo=?"); params.add("depNo," + depNo); } Connection conn = null; PreparedStatement ps = null; ResultSet res = null; StringBuilder resStr = new StringBuilder(); try { conn = getConnection(); ps = conn.prepareStatement(builder.toString()); for (int i = 0; i < params.size(); i++) { String str = params.get(i); String[] arr = str.split(",");//arr[0]Store field information to distinguish fields; arr[1]Store data to assign placeholders // Because when assigning placeholders, you need to select the data type according to the field type, so you can judge the type here. if (arr[0].equals("age")) { int a = Integer.parseInt(arr[1]); ps.setInt(i + 1, a); } else { ps.setString(i + 1, arr[1]); } } res = ps.executeQuery(); while (res.next()) { String targetName = res.getString("name"); String targetSex = res.getString("sex"); int targetAge = res.getInt("age"); String targetDepNo = res.getString("depNo"); String temp = "name=" + targetName + "--" + "sex=" + targetSex + "--" + "age=" + targetAge + "--" + "depNo=" + targetDepNo; resStr.append(temp + "<br>"); } } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } finally { if (res != null) try { res.close(); } catch (SQLException e) { e.printStackTrace(); } if (ps != null) try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } if (conn != null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } PrintWriter out = response.getWriter(); int length = resStr.length(); if (length == 0) out.write("The query is empty"); else out.write(builder.toString() + "<br>" + resStr.toString()); } /** * Determine whether the request parameter exists and whether there is data input * * @param str * @return */ private boolean isNotEmpty(String str) { if (str == null | str.equals("")) { return false; } return true; } public static Connection getConnection() throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); return DriverManager.getConnection("jdbc:mysql://localhost:3366/test01", "root", "123"); } }
//