Filtering and Sorting Data

1. Filtering and Sorting Data

Now that we know how to use Prepared Statements, let's explore how to filter and sort the data we retrieve.

2. Why filter and sort?

Think about it: most applications don't show users every single record from a database. An online bookstore doesn't dump all ten thousand books on one page! We need ways to narrow down results, organize them, and show manageable chunks. That's where SQL clauses like WHERE, ORDER BY, LIMIT, and OFFSET come in. Let's see how to integrate them into our Java code.

3. Filtering with WHERE

Most of the time, we don't want all the rows from a table, and that's completely normal in any real-world application. Here, we use the WHERE clause to filter rows directly at the database level. We're filtering books published after the year 2000, using a question mark placeholder and binding it with setInt to keep our query secure. Notice the Java code structure remains the same as before. JDBC simply sends our SQL to the database, so we can use any valid SQL conditions like AND, OR, and others.

4. Can we filter in Java instead?

Now you might be thinking: "Can't we just retrieve all the data and filter it in Java while iterating the ResultSet?" Technically, yes. But should we? Almost always, no. Think of it like ordering food: would we ask a restaurant to bring us their entire menu, then pick what we want from a mountain of plates? Or would we just order what we need? Filtering at the database level reduces the amount of data transferred, prevents network congestion, and delivers better performance. However, there are rare exceptions, like when filtering depends on external APIs or AI models. But for most cases, let the database do the filtering.

5. Sorting with ORDER BY

Since JDBC doesn't limit our SQL capabilities, we can use ORDER BY directly in our queries to sort results. Here, we select books from category 3, sorted by publication year in descending order. But what if users want to choose the sort direction? We can add dynamic behavior using Java. We build our base query, then use plus-equals to append additional text to our SQL string. Here, we add either DESC or ASC based on a variable. The question mark followed by a colon is Java's ternary operator, a compact if-else statement that returns one value if the condition is true, and another if it is false.

6. Pagination with LIMIT and OFFSET

Finally, let's tackle pagination. Think about our library app: with thousands of books, we need to display them across multiple pages. To create pages, we use LIMIT and OFFSET together. LIMIT controls how many rows to return, like 10 books per page. OFFSET tells the database how many rows to skip before returning results, so for page one, we skip zero, for page two, we skip 10, and for page three, we skip 20. We also need ORDER BY to ensure books appear in the same order every time, otherwise the same page might show different results. Now let's see this in code. We build our query with ORDER BY for consistent sorting, followed by LIMIT and OFFSET placeholders. Then we bind LIMIT to 10 for 10 books per page, and OFFSET to 20 to skip the first 20 rows. This gives us page three of our results.

7. Let's practice!

Time to put these filtering and sorting techniques into practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.