Member Avatar for Saboor880

hello to all! I am developing a data warhouse as my final year project. I have made my star schema succesfully. Now i want to make a query interface for end users so that they run their desired queries. But i am unable to understand that what type of interface should i provide them so that they can run parameterized queries? I think about interface like ms access but that is so complex to implement . Please guide me about developing the interface.
For example end user want to know that how much total fee was collected from students of bscs program in year 2012.
Manually it is so easy to write these queries for us developers. But what interface should we provide to end users?
Note: i am using java programming language and sql server 2008

  • 2 Contributors
  • 1 Reply
  • 253 Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by JamesCherrill

Dani AI

Generated

A practical, low-risk approach is twofold: ship a small set of parameterized report templates for common questions, and add a lightweight ad‑hoc filter builder for the rest. A "Total fees by program and year" template immediately answers the BSCS/2012 example. This follows ’s direction to start simple, but keeps end users away from raw SQL while still giving them flexibility.

Make the UI form-driven and metadata-aware. Expose a few control types: drop‑downs (populated server‑side from distinct values), autocomplete for long lists, a year/date picker, numeric ranges, and optional checkboxes for grouping/aggregation. Build the form dynamically from the database catalog (INFORMATION_SCHEMA) so adding dimensions doesn’t require UI changes. Keep advanced options hidden behind an “Advanced” panel to avoid overwhelming casual users.

Server-side rules are critical. Accept filter values from the UI and bind them with PreparedStatement parameters. Column names used for GROUP BY / ORDER BY must be validated against a whitelist before being appended to SQL (placeholders cannot be used for identifiers). Example (replace table/column names to match the schema):

String sql = "SELECT SUM(fee_column) FROM fact_table WHERE program = ? AND YEAR(date_column) = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setString(1, program);
    ps.setInt(2, year);
    try (ResultSet rs = ps.executeQuery()) {
        if (rs.next()) { long total = rs.getLong(1); }
    }
}

Operational tips: add pagination and a max‑row cap, enforce query timeouts, log slow queries, and index the program/date columns used in filters. Provide CSV export and a few saved‑report slots. For (Java + SQL Server 2008), a simple web UI is usually easier to deploy than trying to mimic Access; start with the template + filter MVP, measure which ad‑hoc features users actually need, then iterate.

Member Avatar for JamesCherrill

You could build a simple form with an entry fields (or drop-down lists) for each column in the database. Whatever the user enters you use as search criteria.
You could have a check box next to each field, and if it's checked you summarise the results by summing over each distinct value for that field. Or just a sinle check box to sum all the results to a single result?

You can start with a really simple subset (maybe just 1 or 2 fields, no summing) and build up from there. If you have it working for 2 fields then going to n fields is trivial.

You could start by hard-wiring all the fields etc, but maybe as a later development you could build the form dynamically based on the table's actual columns

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.