SoFunction
Updated on 2025-03-08

Detailed explanation of the examples of JDBC implementing dynamic query in Java

1 Overview

1.What is dynamic query?

Randomly select several of the query conditions from multiple query conditions to combine them into a DQL statement for querying. This process is called dynamic query.

2. Difficulties in dynamic query

There are many query conditions and many combinations available, which is difficult to list them one by one.

3. The composition of the final query statement

Once the user enters data into the query condition, the query condition becomes part of the final condition.

2 Basic Principles

Basic Framework

Regardless of the query conditions, the query fields and the database are fixed and unchanged. These fixed and unchanged contents form the basic framework of SQL statements, such as

select column... from table。

Form DQL

Get the form input. If the request parameter is not empty, generate a query condition based on the request parameter, such as "name=?", "age>?", and append the query condition to the basic framework. Use StringBuilder to append query conditions. A problem arises at this time. How to determine whether "and" needs to be added to the generated query conditions?
If the query condition is the first query condition, "and" is not required, otherwise "and" is required. The problem becomes complicated. Every time a query condition is generated, it is necessary to determine whether there are query conditions before it.

We can consider adding a query condition to the SQL basic framework. The existence of this query condition does not affect the query result and only acts as a placeholder to avoid determining whether "and" is needed when adding query conditions dynamically. According to these requirements, this query condition must be always true. Here we take "1=1", and the basic SQL framework becomes

select column...from table where 1=1

"and" is added in the preceding section of each dynamic query condition.

Set assign values ​​to placeholders

With DQL statements, you need to consider how to assign values ​​to placeholders. While generating query conditions, you can collect the parameters corresponding to the placeholder and store them in an ordered set. Here, the list set is selected, so that the placeholder forms an sequential correspondence with the elements in the List set. The nth placeholder corresponds to the nth element. By traversing the set, you can assign a value to the placeholder.
When assigning values ​​to placeholders, you not only need to pass data to placeholders, but also need to select a data type that is consistent with the field. The List collection only stores data and cannot meet the requirements. It also needs to add field information to distinguish different fields and select different data types. The elements in this collection take the form of "column+data".

Three Demo

1. Database

2. Page

<!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>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. Server side (Servlet)

package ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
@WebServlet("/dynamicQueryServlet")
public class DynamicQueryServlet extends HttpServlet {
  private static final long serialVersionUID = 1L;
  @Override
  protected void doGet(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    ("text/html;charset=UTF-8");
    // Get the request parameters    String name = ("name");
    String sex = ("sex");
    String age = ("age");
    String depNo = ("depNo");
    // The key is "where 1=1". There is no need to judge whether and needs to be added before the additional query conditions are added, and the previous one is added    String baseSQL = "select name,sex,age,depNo from tb_employee where 1=1";
    StringBuilder builder = new StringBuilder();// Used to splice SQL statements    // Used to establish a mapping between placeholders and parameter values. The placeholders and parameter values ​​are sorted the same in their respective sequences. For example, the placeholders of name rank first in SQL statements, and the parameter value of name is    // Ranked first in the collection.    List<String> params = new ArrayList<String>();
    (baseSQL);
    if (isNotEmpty(name)) {
      (" and name=? ");
      ("name," + name);// The collection cannot only store specific data, but also store field names so that the data type can be selected according to the field name in the future.    }
    if (isNotEmpty(sex)) {
      (" and sex=? ");
      ("sex," + sex);
    }
    if (isNotEmpty(age)) {
      (" and age=? ");
      ("age," + age);
    }
    if (isNotEmpty(depNo)) {
      (" and depNo=?");
      ("depNo," + depNo);
    }
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet res = null;
    StringBuilder resStr = new StringBuilder();
    try {
      conn = getConnection();
      ps = (());
      for (int i = 0; i < (); i++) {
        String str = (i);
        String[] arr = (",");//arr[0] stores field information for distinguishing fields; arr[1] stores data for assigning values ​​to placeholders        // Because when assigning a value to a placeholder, you need to select the data type according to the field type, so here is to determine the type        if (arr[0].equals("age")) {
          int a = (arr[1]);
          (i + 1, a);
        } else {
          (i + 1, arr[1]);
        }
      }
      res = ();
      while (()) {
        String targetName = ("name");
        String targetSex = ("sex");
        int targetAge = ("age");
        String targetDepNo = ("depNo");
        String temp = "name=" + targetName + "--" + "sex=" + targetSex + "--" + "age=" + targetAge + "--"
            + "depNo=" + targetDepNo;
        (temp + "<br>");
      }
    } catch (ClassNotFoundException | SQLException e) {
      ();
    } finally {
      if (res != null)
        try {
          ();
        } catch (SQLException e) {
          ();
        }
      if (ps != null)
        try {
          ();
        } catch (SQLException e) {
          ();
        }
      if (conn != null)
        try {
          ();
        } catch (SQLException e) {
          ();
        }
    }
    PrintWriter out = ();
    int length = ();
    if (length == 0)
      ("Query is empty");
    else
      (() + "<br>" + ());
  }
  /**
    * Determine whether the requested parameter exists and whether there is data input
    *
    * @param str
    * @return
    */
  private boolean isNotEmpty(String str) {
    if (str == null | ("")) {
      return false;
    }
    return true;
  }
  public static Connection getConnection() throws ClassNotFoundException, SQLException {
    ("");
    return ("jdbc:mysql://localhost:3366/test01", "root", "123");
  }
}

The above is a detailed explanation of the examples of JDBC implementing dynamic query in Java introduced to you by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support for my website!