JDBC implements dynamic query and JDBC implements dynamic query.

JDBC implements dynamic query and JDBC implements dynamic query.
I. Overview 1. What is dynamic query?

Multiple query conditions are randomly selected and combined into a DQL statement for query. This process is called dynamic query.

2. Difficulties in dynamic query

Multiple query conditions and combinations are available, making it difficult to list them one by one.

3. Final query statement Composition

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

Ii. Basic Principles 1. Basic SQL framework

Regardless of the query conditions, the query fields and the database are fixed, and these fixed content constitute the basic framework of SQL statements, such

select column... from table。

2. StringBuilder forms DQL

Obtain the form input. If the request parameter is not blank, a query condition is generated based on the request parameter, for example, "name = ?", "Age> ?", Append query conditions to the basic framework. Using StringBuilder to append query conditions, a problem occurs. How can I determine whether to add "and" to the generated query conditions "?
If the query condition is the first query condition, you do not need to add "and". Otherwise, you need to add "and ". The problem becomes complicated. You must determine whether the preceding query conditions exist each time you generate a query condition.
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, and only acts as a placeholder, avoid adding "and" When dynamically adding query conditions ". According to these requirements, the query condition must always be true. Here we take "1 = 1", and the basic SQL framework is changed

select column...from table where 1=1

Add "and" to the front of each dynamic query condition ".

3. The List set is a placeholder value.

With the DQL statement, you need to consider how to assign values to placeholders. When a query condition is generated, the parameters corresponding to the placeholder can be collected and saved to an ordered set. Here, the List set is selected, in this way, the placeholder corresponds to the elements in the List set in order. The Nth placeholder corresponds to the nth element, and the traversal set can assign a value to the placeholder.
When assigning a value to a placeholder, you not only need to pass the data to the placeholder, but also need to select the Data Type consistent with the field. The List set can only store data and cannot meet the requirements, you also need to add field information to distinguish different fields and select different data types. Here, the elements in the set are in the "column + data" format.

Demo1. Database

2. Page

<! DOCTYPE html> 
3. Server (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. listener; @ WebServlet ("/dynamicQueryServlet") public class DynamicQueryServlet extends HttpServlet {private static final long serialVersionUID = 1L; @ Override protected void doGet (HttpServletRequest request, response) throws ServletException, IOException {response. setContentType ("text/html; charset = UTF-8"); // get the request parameter 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". You do not need to add and before determining the append query conditions, add and String baseSQL = "select name, sex, age, depNo from tb_employee where 1 = 1"; StringBuilder builder = new StringBuilder (); // concatenates SQL statements // It is used to establish mappings between placeholders and parameter values. placeholders and parameter values are sorted in the same order in their respective sequences. For example, name placeholders rank first in SQL statements, parameter Value of name
// Rank first in the set. List <String> params = new ArrayList <String> (); builder. append (baseSQL); if (isNotEmpty (name) {builder. append ("and name =? "); Params. add ("name," + name); // The set cannot only store specific data, but also store the field name so that you can select the data type based on the field name.} 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] Stores field information, used to differentiate fields; arr [1] stores data, used to assign values to placeholders // when assigning values to placeholders, select a data Class Based on the field type. Therefore, 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.exe cuteQuery (); 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 =" + t ArgetSex + "--" + "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 ("query is empty"); else out. write (builder. toString () + "<br>" + resStr. toString ();}/*** determines whether the request parameter exists and whether data is 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 ");}}