Developing a flexible search interface is a common requirement in web applications. This article details the implementation of a dynamic multi-condition query feature within a Java web application, allowing users to combine multiple search criteria with various logical operators (AND, OR, NOT) and match types (exact, fuzzy).
The user interface for such a system typical presents a series of input rows, where each row allows selecting a field, entering a search value, and specifying whether the match should be exact or fuzzy. Subsequent rows also include a logical connector (e.g., AND, OR, AND NOT) to link them with previous conditions.
For demonstration, we will consider a simple data model representing 'Applicant Profiles' with fields like name, gender, ethnicity, registration time, age, political status, and service category. The backend is built using standard Java Servlets, JSP for the view, and JDBC for database interaction.
1. Entity Class (ApplicantProfile)
This Plain Old Java Object (POJO) represents a single applicant's profile, mapping directly to database table columns. For better clarity and maintainability, we use descriptive field names.
package com.app.model;
public class ApplicantProfile {
private String applicantName;
private String gender;
private String ethnicity;
private String registrationTimestamp; // Changed from zhucetime
private int age;
private String politicalStatus; // Changed from mianmao
private String serviceCategory; // Changed from fuwuleibie
public ApplicantProfile() {
// Default constructor
}
public ApplicantProfile(String applicantName, String gender, String ethnicity, String registrationTimestamp,
int age, String politicalStatus, String serviceCategory) {
this.applicantName = applicantName;
this.gender = gender;
this.ethnicity = ethnicity;
this.registrationTimestamp = registrationTimestamp;
this.age = age;
this.politicalStatus = politicalStatus;
this.serviceCategory = serviceCategory;
}
// Getters and Setters
public String getApplicantName() {
return applicantName;
}
public void setApplicantName(String applicantName) {
this.applicantName = applicantName;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getEthnicity() {
return ethnicity;
}
public void setEthnicity(String ethnicity) {
this.ethnicity = ethnicity;
}
public String getRegistrationTimestamp() {
return registrationTimestamp;
}
public void setRegistrationTimestamp(String registrationTimestamp) {
this.registrationTimestamp = registrationTimestamp;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getPoliticalStatus() {
return politicalStatus;
}
public void setPoliticalStatus(String politicalStatus) {
this.politicalStatus = politicalStatus;
}
public String getServiceCategory() {
return serviceCategory;
}
public void setServiceCategory(String serviceCategory) {
this.serviceCategory = serviceCategory;
}
@Override
public String toString() {
return "ApplicantProfile{" +
"applicantName='" + applicantName + '\'' +
", gender='" + gender + '\'' +
", ethnicity='" + ethnicity + '\'' +
", registrationTimestamp='" + registrationTimestamp + '\'' +
", age=" + age +
", politicalStatus='" + politicalStatus + '\'' +
", serviceCategory='" + serviceCategory + '\'' +
'}';
}
}
2. Database Utility (DatabaseConnectionManager)
This utility class handles JDBC connection management and resource closing. It utilizes the try-with-resources statement (available since Java 7) to ensure that database resources are properly closed, evenif exceptions occur. This significantly cleans up resource management compared to manual finally blocks.
package com.app.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class DatabaseConnectionManager {
private static final String DB_URL = "jdbc:mysql://localhost:3306/db_database01?serverTimezone=UTC&characterEncoding=UTF-8&allowPublicKeyRetrieval=true&useSSL=false";
private static final String DB_USER = "root";
private static final String DB_PASSWORD = "101032"; // In a production environment, this should be externalized and secured.
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
System.out.println("JDBC Driver loaded successfully.");
} catch (ClassNotFoundException e) {
System.err.println("Failed to load JDBC Driver: " + e.getMessage());
e.printStackTrace();
throw new RuntimeException("Cannot load JDBC driver", e);
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
}
// No explicit close methods needed for Connection/Statement/ResultSet if using try-with-resources.
// If try-with-resources is not an option (e.g., older Java versions or specific framework requirements),
// a helper method for graceful closing can be provided, but it needs to be carefully implemented.
}
3. Query Parameter Model (QueryCriterion)
To safely and dynamically build SQL queries, we introduce a model to represent each individual search condition. This helps in abstracting query logic from raw strings and allows for parameterization using PreparedStatement.
package com.app.model;
public class QueryCriterion {
private String fieldName;
private String searchValue;
private QueryOperator operator; // e.g., EQUALS, LIKE
private LogicalConjunction conjunction; // e.g., AND, OR, AND_NOT (for subsequent conditions)
public QueryCriterion(String fieldName, String searchValue, QueryOperator operator, LogicalConjunction conjunction) {
this.fieldName = fieldName;
this.searchValue = searchValue;
this.operator = operator;
this.conjunction = conjunction;
}
public String getFieldName() {
return fieldName;
}
public String getSearchValue() {
return searchValue;
}
public QueryOperator getOperator() {
return operator;
}
public LogicalConjunction getConjunction() {
return conjunction;
}
public enum QueryOperator {
EQUALS,
LIKE
}
public enum LogicalConjunction {
AND,
OR,
AND_NOT,
NONE // For the first condition
}
}
4. Data Access Object (ApplicantProfileDAO)
The DAO layer is responsible for interacting with the database. The findProfilesByCriteria method dynamically constructs a PreparedStatement based on a list of QueryCriterion objects. This approach prevents SQL injection and makes the query building more structured.
package com.app.dao;
import com.app.model.ApplicantProfile;
import com.app.model.QueryCriterion;
import com.app.util.DatabaseConnectionManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class ApplicantProfileDAO {
private static final String TABLE_NAME = "tb_qingnian"; // Original table name
// Helper to map UI field names to DB column names (if different)
private String mapFieldNameToColumn(String fieldName) {
switch (fieldName) {
case "name": return "name"; // Assuming UI 'name' maps to DB 'name'
case "sex": return "sex";
case "minzu": return "minzu";
case "zhucetime": return "zhucetime";
case "age": return "age";
case "mianmao": return "mianmao";
case "fuwuleibie": return "fuwuleibie";
default: return fieldName; // Fallback, but prefer explicit mapping
}
}
public List<ApplicantProfile> findProfilesByCriteria(List<QueryCriterion> criteria) throws SQLException {
List<ApplicantProfile> profiles = new ArrayList<>();
StringBuilder sqlBuilder = new StringBuilder("SELECT name, sex, minzu, zhucetime, age, mianmao, fuwuleibie FROM " + TABLE_NAME + " WHERE 1=1");
List<Object> parameters = new ArrayList<>();
for (QueryCriterion criterion : criteria) {
String dbColumnName = mapFieldNameToColumn(criterion.getFieldName());
if (dbColumnName == null || dbColumnName.isEmpty()) {
System.err.println("Invalid field name in query criterion: " + criterion.getFieldName());
continue; // Skip invalid criteria
}
// Append logical conjunction for subsequent criteria
if (criterion.getConjunction() != QueryCriterion.LogicalConjunction.NONE) {
sqlBuilder.append(" ").append(criterion.getConjunction().name().replace("_", " "));
}
// Append condition
sqlBuilder.append(" ").append(dbColumnName);
if (criterion.getOperator() == QueryCriterion.QueryOperator.LIKE) {
sqlBuilder.append(" LIKE ?");
parameters.add("%" + criterion.getSearchValue() + "%");
} else { // EQUALS
sqlBuilder.append(" = ?");
parameters.add(criterion.getSearchValue());
}
}
String finalSql = sqlBuilder.toString();
System.out.println("Generated SQL: " + finalSql);
try (Connection conn = DatabaseConnectionManager.getConnection();
PreparedStatement pstmt = conn.prepareStatement(finalSql)) {
for (int i = 0; i < parameters.size(); i++) {
pstmt.setObject(i + 1, parameters.get(i));
}
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
ApplicantProfile profile = new ApplicantProfile(
rs.getString("name"),
rs.getString("sex"),
rs.getString("minzu"),
rs.getString("zhucetime"),
rs.getInt("age"), // Assuming 'age' is INT in DB
rs.getString("mianmao"),
rs.getString("fuwuleibie")
);
profiles.add(profile);
}
}
}
return profiles;
}
// Other CRUD operations (simplified, showing insert for example with PreparedStatement)
public boolean insertProfile(ApplicantProfile profile) throws SQLException {
String sql = "INSERT INTO " + TABLE_NAME + "(name, sex, minzu, zhucetime, age, mianmao, fuwuleibie) VALUES (?, ?, ?, ?, ?, ?, ?)";
try (Connection conn = DatabaseConnectionManager.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, profile.getApplicantName());
pstmt.setString(2, profile.getGender());
pstmt.setString(3, profile.getEthnicity());
pstmt.setString(4, profile.getRegistrationTimestamp());
pstmt.setInt(5, profile.getAge());
pstmt.setString(6, profile.getPoliticalStatus());
pstmt.setString(7, profile.getServiceCategory());
return pstmt.executeUpdate() > 0;
}
}
public List<ApplicantProfile> findAllProfiles() throws SQLException {
List<ApplicantProfile> profiles = new ArrayList<>();
String sql = "SELECT name, sex, minzu, zhucetime, age, mianmao, fuwuleibie FROM " + TABLE_NAME;
try (Connection conn = DatabaseConnectionManager.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
ApplicantProfile profile = new ApplicantProfile(
rs.getString("name"),
rs.getString("sex"),
rs.getString("minzu"),
rs.getString("zhucetime"),
rs.getInt("age"),
rs.getString("mianmao"),
rs.getString("fuwuleibie")
);
profiles.add(profile);
}
}
return profiles;
}
}
5. Service Layer (ApplicantProfileService)
The service layer acts as an intermediary, encapsulating business logic and orchestrating calls to the DAO. For this example, it primarily delegates requests to the ApplicantProfileDAO.
package com.app.service;
import com.app.dao.ApplicantProfileDAO;
import com.app.model.ApplicantProfile;
import com.app.model.QueryCriterion;
import java.sql.SQLException;
import java.util.List;
public class ApplicantProfileService {
private ApplicantProfileDAO applicantProfileDAO = new ApplicantProfileDAO();
public List<ApplicantProfile> retrieveProfilesByCriteria(List<QueryCriterion> criteria) {
try {
return applicantProfileDAO.findProfilesByCriteria(criteria);
} catch (SQLException e) {
System.err.println("Database error during multi-condition query: " + e.getMessage());
e.printStackTrace();
return List.of(); // Return empty list on error
}
}
public List<ApplicantProfile> getAllProfiles() {
try {
return applicantProfileDAO.findAllProfiles();
} catch (SQLException e) {
System.err.println("Database error during fetching all profiles: " + e.getMessage());
e.printStackTrace();
return List.of();
}
}
public boolean addApplicantProfile(ApplicantProfile profile) {
try {
return applicantProfileDAO.insertProfile(profile);
} catch (SQLException e) {
System.err.println("Database error during profile insertion: " + e.getMessage());
e.printStackTrace();
return false;
}
}
// Other service methods can be added here (update, delete, etc.)
}
6. Servlet Controller (ProfileQueryServlet)
The Servlet receives requests from the JSP, parses the dynamic query parameters, constructs a list of QueryCriterion objects, and then passes them to the service layer. It finally forwards the results or an error message back to a JSP view.
package com.app.controller;
import com.app.model.ApplicantProfile;
import com.app.model.QueryCriterion;
import com.app.service.ApplicantProfileService;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;
@WebServlet("/ProfileQueryServlet")
public class ProfileQueryServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private ApplicantProfileService profileService = new ApplicantProfileService();
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
processRequest(request, response);
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
processRequest(request, response);
}
private void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
String action = request.getParameter("action");
if (action == null) {
// Default action: show all profiles
displayAllProfiles(request, response);
} else {
switch (action) {
case "searchDynamic":
handleDynamicSearch(request, response);
break;
case "showAll":
displayAllProfiles(request, response);
break;
case "insertProfile":
handleInsertProfile(request, response);
break;
// Add other actions (update, delete, specific searches) as needed
default:
response.sendError(HttpServletResponse.SC_BAD_REQUEST, "Invalid action specified.");
break;
}
}
}
private void displayAllProfiles(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
List<ApplicantProfile> profiles = profileService.getAllProfiles();
request.setAttribute("profileList", profiles);
request.getRequestDispatcher("profileList.jsp").forward(request, response);
}
private void handleInsertProfile(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String name = request.getParameter("applicantName");
String gender = request.getParameter("gender");
String ethnicity = request.getParameter("ethnicity");
String regTime = request.getParameter("registrationTimestamp");
int age = Integer.parseInt(request.getParameter("age"));
String politicalStatus = request.getParameter("politicalStatus");
String[] categories = request.getParameterValues("serviceCategory");
String serviceCategory = String.join("，", categories); // Use comma as separator if multiple selected
ApplicantProfile newProfile = new ApplicantProfile(name, gender, ethnicity, regTime, age, politicalStatus, serviceCategory);
if (profileService.addApplicantProfile(newProfile)) {
request.setAttribute("message", "Profile added successfully!");
displayAllProfiles(request, response); // Redirect to show all
} else {
request.setAttribute("errorMessage", "Failed to add profile or profile already exists.");
request.getRequestDispatcher("insertForm.jsp").forward(request, response); // Return to form with error
}
}
private void handleDynamicSearch(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
List<QueryCriterion> searchCriteria = new ArrayList<>();
for (int i = 1; i <= 6; i++) { // Max 6 conditions based on UI design
String fieldName = request.getParameter("field" + i);
String searchValue = request.getParameter("value" + i);
String operatorType = request.getParameter("op" + i);
String conjunctionType = request.getParameter("conn" + i); // For i > 1
if (fieldName != null && !fieldName.isEmpty() && searchValue != null && !searchValue.trim().isEmpty()) {
QueryCriterion.QueryOperator operator = "fuzzy".equals(operatorType) ?
QueryCriterion.QueryOperator.LIKE : QueryCriterion.QueryOperator.EQUALS;
QueryCriterion.LogicalConjunction conjunction;
if (i == 1) {
conjunction = QueryCriterion.LogicalConjunction.NONE; // First condition has no preceding conjunction
} else {
switch (conjunctionType) {
case "or": conjunction = QueryCriterion.LogicalConjunction.OR; break;
case "and not": conjunction = QueryCriterion.LogicalConjunction.AND_NOT; break;
case "and":
default: conjunction = QueryCriterion.LogicalConjunction.AND; break;
}
}
searchCriteria.add(new QueryCriterion(fieldName, searchValue, operator, conjunction));
}
}
List<ApplicantProfile> results = profileService.retrieveProfilesByCriteria(searchCriteria);
if (!results.isEmpty()) {
request.setAttribute("profileList", results);
request.getRequestDispatcher("profileList.jsp").forward(request, response);
} else {
request.getRequestDispatcher("notFound.jsp").forward(request, response);
}
}
}
7. JSP Views
Dynamic Search Interface (dynamicSearch.jsp)
This JSP provides the UI for adding multiple search conditions. JavaScript is used to dynamically add and remove input rows, each containing dropdowns for field selection, search value input, and search type, along with logical connectors for subsequent rows.
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<html>
<head>
<meta charset="UTF-8">
<title>Applicant Profile Search</title>
<style>
body { font-family: Arial, sans-serif; margin: 20px; }
.query-row { margin-bottom: 10px; }
select, input[type="text"] { padding: 5px; margin-right: 5px; }
button { padding: 8px 12px; cursor: pointer; }
</style>
</head>
<body>
<div align="center">
<h2>Dynamic Applicant Profile Search</h2>
<button onclick="addSearchRow()" type="button">+ Add Condition</button>
<button onclick="removeSearchRow()" type="button">- Remove Last</button>
<form action="ProfileQueryServlet?action=searchDynamic" method="post">
<table id="searchConditionsTable">
<!-- Initial search row -->
<tr class="query-row" id="row_1">
<td>
<select id="field1" name="field1">
<option value="applicantName">Name</option>
<option value="gender">Gender</option>
<option value="ethnicity">Ethnicity</option>
<option value="registrationTimestamp">Reg. Time</option>
<option value="age">Age</option>
<option value="politicalStatus">Political Status</option>
<option value="serviceCategory">Service Category</option>
</select>
<input type="text" id="value1" name="value1" placeholder="Search Value">
<select id="op1" name="op1">
<option value="exact">Exact</option>
<option value="fuzzy">Fuzzy</option>
</select>
</td>
</tr>
</table>
<div style="margin-top: 20px;">
<button type="submit">Execute Search</button>
</div>
<div style="margin-top: 10px;">
<a href="ProfileQueryServlet?action=showAll">View All Profiles</a>
<span> | </span>
<a href="insertForm.jsp">Add New Profile</a>
</div>
</form>
</div>
<script type="text/javascript">
var rowCounter = 1;
const MAX_ROWS = 6; // Limit based on original application design
function addSearchRow() {
if (rowCounter < MAX_ROWS) {
rowCounter++;
var table = document.getElementById("searchConditionsTable");
var newRow = table.insertRow();
newRow.className = "query-row";
newRow.id = "row_" + rowCounter;
var cell = newRow.insertCell();
cell.innerHTML =
"<select id='conn" + rowCounter + "' name='conn" + rowCounter + "'>" +
"<option value='and'>AND</option>" +
"<option value='or'>OR</option>" +
"<option value='and not'>AND NOT</option>" + // Note: 'AND NOT' needs careful SQL parsing
"</select>" +
"<select id='field" + rowCounter + "' name='field" + rowCounter + "'>" +
"<option value='applicantName'>Name</option>" +
"<option value='gender'>Gender</option>" +
"<option value='ethnicity'>Ethnicity</option>" +
"<option value='registrationTimestamp'>Reg. Time</option>" +
"<option value='age'>Age</option>" +
"<option value='politicalStatus'>Political Status</option>" +
"<option value='serviceCategory'>Service Category</option>" +
"</select>" +
"<input type='text' id='value" + rowCounter + "' name='value" + rowCounter + "' placeholder='Search Value' />" +
"<select id='op" + rowCounter + "' name='op" + rowCounter + "'>" +
"<option value='exact'>Exact</option>" +
"<option value='fuzzy'>Fuzzy</option>" +
"</select>";
} else {
alert("Maximum " + MAX_ROWS + " search conditions allowed!");
}
}
function removeSearchRow() {
if (rowCounter > 1) {
var table = document.getElementById("searchConditionsTable");
table.deleteRow(table.rows.length - 1);
rowCounter--;
} else {
alert("Cannot remove the last search condition.");
}
}
</script>
</body>
</html>
Profile List Display (profileList.jsp)
This JSP uses JSTL to iterate through the list of ApplicantProfile objects received from the servlet and display them in an HTML table.
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html>
<head>
<meta charset="UTF-8">
<title>Applicant Profiles</title>
<style>
body { font-family: Arial, sans-serif; margin: 20px; }
table { width: 90%; border-collapse: collapse; margin-top: 20px; }
th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
th { background-color: #f2f2f2; }
</style>
</head>
<body>
<div align="center">
<h1>Applicant Profile Listings</h1>
<a href="dynamicSearch.jsp">Return to Search</a>
<span> | </span>
<a href="ProfileQueryServlet?action=showAll">Refresh All Profiles</a>
<c:if test="${not empty message}">
<p style="color: green;"><c:out value="${message}"/></p>
</c:if>
<c:choose>
<c:when test="${not empty profileList}">
<table>
<thead>
<tr>
<th>Name</th>
<th>Gender</th>
<th>Ethnicity</th>
<th>Reg. Time</th>
<th>Age</th>
<th>Political Status</th>
<th>Service Category</th>
<th colspan="2">Actions</th>
</tr>
</thead>
<tbody>
<c:forEach items="${profileList}" var="profile">
<tr>
<td><c:out value="${profile.applicantName}"/></td>
<td><c:out value="${profile.gender}"/></td>
<td><c:out value="${profile.ethnicity}"/></td>
<td><c:out value="${profile.registrationTimestamp}"/></td>
<td><c:out value="${profile.age}"/></td>
<td><c:out value="${profile.politicalStatus}"/></td>
<td><c:out value="${profile.serviceCategory}"/></td>
<!-- Example actions, actual implementation would require more servlets/logic -->
<td><a href="editProfile.jsp?name=${profile.applicantName}">Edit</a></td>
<td><a href="javascript:if(confirm('Confirm delete for ${profile.applicantName}?')){ window.location='ProfileQueryServlet?action=delete&name=${profile.applicantName}'}">Delete</a></td>
</tr>
</c:forEach>
</tbody>
</table>
</c:when>
<c:otherwise>
<p>No profiles found matching the criteria.</p>
</c:otherwise>
</c:choose>
</div>
</body>
</html>
No Results Found (notFound.jsp)
A simple page to inform the user when no records match their search criteria.
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<html>
<head>
<meta charset="UTF-8">
<title>No Results</title>
<style>
body { font-family: Arial, sans-serif; margin: 20px; text-align: center; }
h3 { color: #cc0000; }
</style>
</head>
<body>
<div>
<h3>No matching profiles found.</h3>
<p>Please try different search criteria.</p>
<a href="dynamicSearch.jsp">Return to Search Page</a>
</div>
</body>
</html>