Java即席查询案例

本文目录导读：

1. 项目结构
2. 请求模型类
3. SQL构建工具类
4. 服务层实现
5. 控制器实现
6. 使用示例
7. JSON请求示例
8. 关键特性

我来为您提供一个完整的Java即席查询（Ad-hoc Query）案例，包含动态SQL构建、参数处理和结果解析。

项目结构

src/main/java/com/example/adhoc/
├── model/
│   ├── QueryRequest.java
│   └── QueryCondition.java
├── service/
│   └── AdhocQueryService.java
├── controller/
│   └── QueryController.java
└── util/
    └── QueryBuilder.java

请求模型类

QueryRequest.java

package com.example.adhoc.model;
import java.util.List;
public class QueryRequest {
    private String tableName;
    private List<String> selectFields;
    private List<QueryCondition> conditions;
    private String orderBy;
    private String orderDirection;
    private Integer pageNum;
    private Integer pageSize;
    // Getters and Setters
    public String getTableName() {
        return tableName;
    }
    public void setTableName(String tableName) {
        this.tableName = tableName;
    }
    public List<String> getSelectFields() {
        return selectFields;
    }
    public void setSelectFields(List<String> selectFields) {
        this.selectFields = selectFields;
    }
    public List<QueryCondition> getConditions() {
        return conditions;
    }
    public void setConditions(List<QueryCondition> conditions) {
        this.conditions = conditions;
    }
    public String getOrderBy() {
        return orderBy;
    }
    public void setOrderBy(String orderBy) {
        this.orderBy = orderBy;
    }
    public String getOrderDirection() {
        return orderDirection;
    }
    public void setOrderDirection(String orderDirection) {
        this.orderDirection = orderDirection;
    }
    public Integer getPageNum() {
        return pageNum;
    }
    public void setPageNum(Integer pageNum) {
        this.pageNum = pageNum;
    }
    public Integer getPageSize() {
        return pageSize;
    }
    public void setPageSize(Integer pageSize) {
        this.pageSize = pageSize;
    }
}

QueryCondition.java

package com.example.adhoc.model;
public class QueryCondition {
    private String field;
    private String operator;
    private Object value;
    private String logicOperator; // AND, OR
    // Getters and Setters
    public String getField() {
        return field;
    }
    public void setField(String field) {
        this.field = field;
    }
    public String getOperator() {
        return operator;
    }
    public void setOperator(String operator) {
        this.operator = operator;
    }
    public Object getValue() {
        return value;
    }
    public void setValue(Object value) {
        this.value = value;
    }
    public String getLogicOperator() {
        return logicOperator;
    }
    public void setLogicOperator(String logicOperator) {
        this.logicOperator = logicOperator;
    }
}

SQL构建工具类

QueryBuilder.java

package com.example.adhoc.util;
import com.example.adhoc.model.QueryCondition;
import com.example.adhoc.model.QueryRequest;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
public class QueryBuilder {
    /**
     * 构建动态查询SQL
     */
    public static QueryResult buildQuery(QueryRequest request) {
        StringBuilder sql = new StringBuilder();
        List<Object> params = new ArrayList<>();
        // 构建SELECT部分
        sql.append("SELECT ");
        if (request.getSelectFields() != null && !request.getSelectFields().isEmpty()) {
            sql.append(String.join(", ", request.getSelectFields()));
        } else {
            sql.append("*");
        }
        // 构建FROM部分
        sql.append(" FROM ");
        sql.append(validateTableName(request.getTableName()));
        // 构建WHERE部分
        if (request.getConditions() != null && !request.getConditions().isEmpty()) {
            sql.append(" WHERE ");
            buildConditions(sql, params, request.getConditions());
        }
        // 构建ORDER BY部分
        if (request.getOrderBy() != null && !request.getOrderBy().isEmpty()) {
            sql.append(" ORDER BY ").append(validateFieldName(request.getOrderBy()));
            if (request.getOrderDirection() != null && 
                request.getOrderDirection().equalsIgnoreCase("DESC")) {
                sql.append(" DESC");
            } else {
                sql.append(" ASC");
            }
        }
        // 构建分页部分
        if (request.getPageNum() != null && request.getPageSize() != null) {
            int offset = (request.getPageNum() - 1) * request.getPageSize();
            sql.append(" LIMIT ? OFFSET ?");
            params.add(request.getPageSize());
            params.add(offset);
        }
        return new QueryResult(sql.toString(), params);
    }
    /**
     * 构建条件部分
     */
    private static void buildConditions(StringBuilder sql, List<Object> params, 
                                       List<QueryCondition> conditions) {
        for (int i = 0; i < conditions.size(); i++) {
            QueryCondition condition = conditions.get(i);
            if (i > 0 && condition.getLogicOperator() != null) {
                sql.append(" ").append(condition.getLogicOperator()).append(" ");
            }
            String field = validateFieldName(condition.getField());
            switch (condition.getOperator().toLowerCase()) {
                case "equals":
                case "=":
                    sql.append(field).append(" = ?");
                    params.add(condition.getValue());
                    break;
                case "not_equals":
                case "!=":
                    sql.append(field).append(" != ?");
                    params.add(condition.getValue());
                    break;
                case "greater_than":
                case ">":
                    sql.append(field).append(" > ?");
                    params.add(condition.getValue());
                    break;
                case "less_than":
                case "<":
                    sql.append(field).append(" < ?");
                    params.add(condition.getValue());
                    break;
                case "greater_equal":
                case ">=":
                    sql.append(field).append(" >= ?");
                    params.add(condition.getValue());
                    break;
                case "less_equal":
                case "<=":
                    sql.append(field).append(" <= ?");
                    params.add(condition.getValue());
                    break;
                case "like":
                    sql.append(field).append(" LIKE ?");
                    params.add("%" + condition.getValue() + "%");
                    break;
                case "in":
                    List<?> inValues = (List<?>) condition.getValue();
                    String placeholders = inValues.stream()
                        .map(v -> {
                            params.add(v);
                            return "?";
                        })
                        .collect(Collectors.joining(", "));
                    sql.append(field).append(" IN (").append(placeholders).append(")");
                    break;
                case "between":
                    List<?> rangeValues = (List<?>) condition.getValue();
                    sql.append(field).append(" BETWEEN ? AND ?");
                    params.add(rangeValues.get(0));
                    params.add(rangeValues.get(1));
                    break;
                case "is_null":
                    sql.append(field).append(" IS NULL");
                    break;
                case "is_not_null":
                    sql.append(field).append(" IS NOT NULL");
                    break;
                default:
                    throw new IllegalArgumentException("Unsupported operator: " + condition.getOperator());
            }
        }
    }
    /**
     * 验证表名防止SQL注入
     */
    private static String validateTableName(String tableName) {
        if (!tableName.matches("^[a-zA-Z_][a-zA-Z0-9_]*$")) {
            throw new IllegalArgumentException("Invalid table name: " + tableName);
        }
        return tableName;
    }
    /**
     * 验证字段名防止SQL注入
     */
    private static String validateFieldName(String fieldName) {
        if (!fieldName.matches("^[a-zA-Z_][a-zA-Z0-9_]*$")) {
            throw new IllegalArgumentException("Invalid field name: " + fieldName);
        }
        return fieldName;
    }
    /**
     * 查询结果内部类
     */
    public static class QueryResult {
        private String sql;
        private List<Object> params;
        public QueryResult(String sql, List<Object> params) {
            this.sql = sql;
            this.params = params;
        }
        public String getSql() {
            return sql;
        }
        public List<Object> getParams() {
            return params;
        }
    }
}

服务层实现

AdhocQueryService.java

package com.example.adhoc.service;
import com.example.adhoc.model.QueryRequest;
import com.example.adhoc.util.QueryBuilder;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import java.util.List;
import java.util.Map;
@Service
public class AdhocQueryService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    /**
     * 执行即席查询
     */
    public List<Map<String, Object>> executeQuery(QueryRequest request) {
        // 构建SQL和参数
        QueryBuilder.QueryResult queryResult = QueryBuilder.buildQuery(request);
        // 记录查询日志（可选）
        logQuery(queryResult.getSql(), queryResult.getParams());
        // 执行查询
        List<Map<String, Object>> result = jdbcTemplate.queryForList(
            queryResult.getSql(), 
            queryResult.getParams().toArray()
        );
        return result;
    }
    /**
     * 获取查询记录数
     */
    public long countQuery(QueryRequest request) {
        QueryBuilder.QueryResult queryResult = QueryBuilder.buildQuery(request);
        String countSql = queryResult.getSql().replaceFirst(
            "SELECT .* FROM", 
            "SELECT COUNT(*) FROM"
        );
        // 移除ORDER BY和LIMIT部分
        countSql = countSql.replaceAll("ORDER BY .*", "");
        countSql = countSql.replaceAll("LIMIT .*", "");
        Long count = jdbcTemplate.queryForObject(
            countSql, 
            queryResult.getParams().toArray(), 
            Long.class
        );
        return count != null ? count : 0L;
    }
    /**
     * 记录查询日志
     */
    private void logQuery(String sql, List<Object> params) {
        System.out.println("Executing query: " + sql);
        System.out.println("With parameters: " + params);
    }
}

控制器实现

QueryController.java

package com.example.adhoc.controller;
import com.example.adhoc.model.QueryRequest;
import com.example.adhoc.service.AdhocQueryService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("/api/adhoc")
public class QueryController {
    @Autowired
    private AdhocQueryService adhocQueryService;
    /**
     * 执行即席查询
     */
    @PostMapping("/query")
    public ResponseEntity<Map<String, Object>> executeQuery(@RequestBody QueryRequest request) {
        try {
            // 执行查询
            List<Map<String, Object>> data = adhocQueryService.executeQuery(request);
            // 获取总记录数
            long total = adhocQueryService.countQuery(request);
            // 构建响应
            Map<String, Object> response = new HashMap<>();
            response.put("success", true);
            response.put("data", data);
            response.put("total", total);
            response.put("pageNum", request.getPageNum());
            response.put("pageSize", request.getPageSize());
            return ResponseEntity.ok(response);
        } catch (Exception e) {
            Map<String, Object> errorResponse = new HashMap<>();
            errorResponse.put("success", false);
            errorResponse.put("message", "Query execution failed: " + e.getMessage());
            return ResponseEntity.badRequest().body(errorResponse);
        }
    }
    /**
     * 获取元数据信息
     */
    @GetMapping("/metadata/{tableName}")
    public ResponseEntity<Map<String, Object>> getTableMetadata(@PathVariable String tableName) {
        // 这里可以返回表的元数据信息，如字段列表、类型等
        Map<String, Object> metadata = new HashMap<>();
        metadata.put("tableName", tableName);
        return ResponseEntity.ok(metadata);
    }
}

使用示例

测试代码

package com.example.adhoc;
import com.example.adhoc.model.QueryCondition;
import com.example.adhoc.model.QueryRequest;
import com.example.adhoc.util.QueryBuilder;
import java.util.ArrayList;
import java.util.List;
public class AdhocQueryExample {
    public static void main(String[] args) {
        // 创建查询请求
        QueryRequest request = new QueryRequest();
        request.setTableName("employees");
        // 设置查询字段
        List<String> fields = new ArrayList<>();
        fields.add("id");
        fields.add("name");
        fields.add("department");
        fields.add("salary");
        request.setSelectFields(fields);
        // 设置查询条件
        List<QueryCondition> conditions = new ArrayList<>();
        QueryCondition cond1 = new QueryCondition();
        cond1.setField("department");
        cond1.setOperator("equals");
        cond1.setValue("IT");
        cond1.setLogicOperator("AND");
        QueryCondition cond2 = new QueryCondition();
        cond2.setField("salary");
        cond2.setOperator("greater_than");
        cond2.setValue(50000);
        cond2.setLogicOperator("AND");
        conditions.add(cond1);
        conditions.add(cond2);
        request.setConditions(conditions);
        // 设置排序
        request.setOrderBy("salary");
        request.setOrderDirection("DESC");
        // 设置分页
        request.setPageNum(1);
        request.setPageSize(10);
        // 构建查询
        QueryBuilder.QueryResult result = QueryBuilder.buildQuery(request);
        System.out.println("Generated SQL: " + result.getSql());
        System.out.println("Parameters: " + result.getParams());
    }
}

JSON请求示例

发送的JSON请求

{
    "tableName": "employees",
    "selectFields": ["id", "name", "department", "salary", "hire_date"],
    "conditions": [
        {
            "field": "department",
            "operator": "in",
            "value": ["IT", "HR", "Finance"],
            "logicOperator": "AND"
        },
        {
            "field": "salary",
            "operator": "between",
            "value": [30000, 100000],
            "logicOperator": "AND"
        },
        {
            "field": "status",
            "operator": "equals",
            "value": "ACTIVE",
            "logicOperator": "AND"
        }
    ],
    "orderBy": "salary",
    "orderDirection": "DESC",
    "pageNum": 1,
    "pageSize": 20
}

关键特性

1. 动态SQL构建：根据请求参数动态生成SQL语句
2. SQL注入防护：通过字段名和表名校验防止注入
3. 参数化查询：使用PreparedStatement参数，避免SQL注入
4. 多种运算符支持：=, !=, >, <, >=, <=, LIKE, IN, BETWEEN, IS NULL等
5. 分页支持：内置分页功能
6. 排序支持：支持多字段排序
7. 逻辑运算符：支持AND/OR组合条件

这个即席查询系统可以灵活地处理各种查询需求,同时保证了安全性。