How does JDBC implement dynamic query?

In network development, comprehensive queries with multiple conditions are very common. To deal with this business need, we usually use the following methods to achieve it:
1. Directly Splice the parameter values ​​into the SQL statement and then query.
The security of this method should be said to be relatively poor, and it can be accidentally injected by SQL. Although you can filter special characters in parameter values ​​first, it doesn't always feel very elegant.
2. Use placeholder '? ' to splice SQL, and then fill in the PreparedStatement through conditional judgment.
Anyone who has used TX in this way knows the complexity of this method. First, you have to make a judgment once when you are spelling SQL, and then you have to make a judgment again when filling in PST, which is troublesome.
3. Procedure Storage
I have always been unhappy with stored procedures. I once had a project migrated from MySQL to MSSQL, and later to ORACLE. In the end, different versions of the product ran on different databases, and I almost had to do it at that time. It’s my life.
In fact, all I have to say is that I want a relatively elegant and simple query method. I was inspired by the SQLHelper provided in .NET in the previous paragraph, and then I wrote such a similar component (in fact, I Googled half of it No foundation that meets the requirements has been found for hours

1. What is dynamic query?

## Randomly select from multiple query conditions. Several are combined into one DQL statement for query. This process is called dynamic query

2. Difficulties of dynamic query

## Available options. There are many query conditions and many combinations, making it difficult to list them all. 3. The composition of the final query statement

Once the user submits the query conditions. Enter data into the query condition, and the query condition becomes part of the final condition. 2. Basic principles

1.SQL basic framework

Regardless of the query conditions, the query fields and the database are fixed. These fixed contents constitute the basic framework of the SQL statement, such as

select column... from table。

2.StringBuilder forms DQL

Get form input. If the request parameter is not empty, generate query conditions based on the request parameter, such as "name=?", "age> ;?", append the query conditions to the

basic framework. Use StringBuilder to append the query conditions. At this time, a question arises. How to judge whether "and" needs to be added to the generated query conditions?

If the query condition is the first query condition, there is no need to add "and", otherwise the problem becomes complicated, and it is required every time the query condition is generated. Determine whether there is a query condition in front.

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

select column...from table where 1=1

Add "and" in front of each dynamic query condition 3. The List collection is a placeholder. Character assignment

With the DQL statement, you need to consider how to assign values ​​to the placeholders. You can collect the parameters corresponding to the placeholders and store them# while generating the query conditions.

##In an ordered collection, the List collection is selected here, so that the placeholders form a sequential correspondence with the elements in the List collection. The nth placeholder corresponds to the nth placeholder. elements, you can assign values ​​to the placeholders by traversing the collection.

When assigning a value to a placeholder, you not only need to pass the data to the placeholder, but also need to select a data type that is consistent with the field. The List collection only stores data and is no longer

To meet the requirements, you still need to add field information to distinguish different fields and select different data types. The elements in the collection here are in the form of

"column+data". 三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>动态查询</title></head><body><form action="http://localhost:8080/JavaSETest/dynamicQueryServlet"><div><span>姓名:</span><input type="text" name="name"></div><div><span>性别:</span><input type="text" name="sex"></div><div><span>年龄:</span><input type="text" name="age"></div><div><span>部门编号:</span><input type="text" name="depNo"></div><div><input type="submit"value="查询"> <input type="reset"value="重置"></div></form></body></html>

3.服务器端（Servlet）

"/dynamicQueryServlet"  DynamicQueryServlet     serialVersionUID = 1L "text/html;charset=UTF-8"String name = request.getParameter("name"= request.getParameter("sex"= request.getParameter("age"= request.getParameter("depNo"String baseSQL = "select name,sex,age,depNo from tb_employee where 1=1"=  StringBuilder();List<String> params =  ArrayList<String>" and name=? ""name," + name);" and sex=? ""sex," +" and age=? ""age," +" and depNo=?""depNo," += = = = == ( i = 0; i < params.size(); i++== str.split(","); (arr[0].equals("age" a = Integer.parseInt(arr[1+ 1+ 1, arr[1== res.getString("name"= res.getString("sex" targetAge = res.getInt("age"= res.getString("depNo"= "name=" + targetName + "--" + "sex=" + targetSex + "--" + "age=" + targetAge + "--"
                        + "depNo=" ++ "<br>" (ClassNotFoundException | (res !=  (ps !=  (conn != = length = (length == 0"查询为空"+ "<br>" +   (str ==  | str.equals(""    Connection getConnection() "com.mysql.jdbc.Driver" DriverManager.getConnection("jdbc:mysql://localhost:3366/test01", "root", "123"

The above is the detailed content of How does JDBC implement dynamic query?. For more information, please follow other related articles on the PHP Chinese website!