Filtering and Sorting Data

Querying a PostgreSQL Database in Java

Miller Trujillo

Staff Software Engineer

Why filter and sort?

Most applications don't show every record
Need to narrow down results
Organize data meaningfully
Show manageable chunks

WHERE, ORDER BY, LIMIT, OFFSET ✨

An online bookstore

Filtering with WHERE

String sql = "SELECT * FROM books WHERE publication_year > ?";
PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, 2000);
ResultSet rs = pstmt.executeQuery();

Use ? placeholder and bind with setInt()
Java code remains the same, only SQL changes

Can we filter in Java instead?

Filtering at the database level:
- Reduces data transferred ✅
- Prevents network congestion ✅
- Delivers better performance ✅
Exception: complex logic (external APIs, AI models) 💡

Ordering food analogy

Sorting with ORDER BY

String sql = "SELECT title, publication_year FROM books
WHERE category_id = ? ORDER BY publication_year DESC";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, 3);
ResultSet rs = pstmt.executeQuery();

// Adding dynamic behavior
boolean desc = true;
String sql = "SELECT title, publication_year FROM books
WHERE category_id = ? ORDER BY publication_year ";

sql += desc ? "DESC" : "ASC";

+= appends text to the SQL string
?: - ternary operator (compact if-else)

Pagination with LIMIT and OFFSET

LIMIT controls how many rows to return
OFFSET skips rows before returning results
ORDER BY ensures consistent results across pages

String sql = "SELECT title FROM books ORDER BY title ASC LIMIT ? OFFSET ?";
PreparedStatement pstmt = conn.prepareStatement(sql);

pstmt.setInt(1, 10);  // LIMIT: 10 books per page
pstmt.setInt(2, 20);  // OFFSET: skip first 20 rows
ResultSet rs = pstmt.executeQuery();

Let's practice!

Querying a PostgreSQL Database in Java