JDBC Database Access

JDBC stands for Java Database Connectivity. It is a set of interfaces and classes in the java.sql package that allows a Java program to send SQL statements to a database and receive results back. Think of it as a universal translator: your Java code speaks JDBC, and the database vendor provides a JDBC driver (like a language pack) that translates JDBC calls into the database's native protocol.

To use JDBC, you first need a database installed and running. For the exam, you do not need to install anything — you just need to understand the code patterns. The essential steps are always: 1) Load the driver (though modern JDBC 4.0+ does this automatically if the driver JAR is on the classpath). 2) Establish a connection using DriverManager.getConnection(url, username, password). The URL looks like jdbc:vendor://host:port/databaseName — for example, jdbc:mysql://localhost:3306/mydb. 3) Create a statement object. There are three types: Statement, PreparedStatement, and CallableStatement. 4) Execute the query using methods like executeQuery() for SELECT, executeUpdate() for INSERT/UPDATE/DELETE, or execute() for any SQL. 5) Process the results, typically by looping through a ResultSet. 6) Close all resources (Connection, Statement, ResultSet) to avoid memory leaks.

A Statement is the simplest — you pass a complete SQL string to it. But this is dangerous because if that string includes user input, a malicious user could inject SQL commands. For example, a login form where the username field is ' OR 1=1 -- would trick Statement into returning all users. PreparedStatement prevents this by separating the SQL structure from the data. You write the SQL with question marks as placeholders, then call setString(1, userInput) to safely bind the user's value. The database compiles the PreparedStatement once and reuses it for every execution, which is also faster for repeated queries.

CallableStatement is used for calling stored procedures — pre-written SQL blocks stored inside the database. You write {call procedureName(?, ?)} and use registerOutParameter() to get output values. Processing results requires a ResultSet, which is a cursor pointing to a table of results. You call rs.next() to move to the first row, then getString("columnName") or getInt(1) to extract column values. The ResultSet is iterable forward-only by default, but can be made scrollable or updatable with extra parameters when creating the Statement.

Resource management is critical. Every Connection, Statement, and ResultSet should be closed in a finally block, or better, using try-with-resources (introduced in Java 7). The try-with-resources syntax automatically closes any resource that implements AutoCloseable when the try block ends. This is the pattern the exam expects you to use — it is clean and prevents resource leaks.

JDBC replaced older, database-specific APIs like ODBC (Open Database Connectivity) for Java programmers. Before JDBC, switching from Oracle to MySQL meant rewriting your data access code from scratch. JDBC makes the database vendor a configurable detail — you change the driver JAR and the URL, and the rest of your code stays the same.

An IT professional working at a healthcare software company needs to build a feature that lets doctors search patient records by surname. The developer starts by writing a Java method called findPatientsByLastName(String lastName). Inside this method, the developer creates a Connection to the PostgreSQL database that stores patient data. The connection URL includes the database server's IP address, the port (usually 5432 for PostgreSQL), the database name 'hospitals_db', and a user account with read-only permissions.

Next, the developer writes a PreparedStatement: "SELECT * FROM patients WHERE last_name = ?". Notice the question mark — this is the placeholder for the doctor's search input. The developer then calls preparedStatement.setString(1, lastName) to safely insert the doctor's search term. This prevents a scenario where a doctor types "Smith' OR 1=1 --" into the search box and accidentally (or maliciously) retrieves every patient record.

The developer then calls executeQuery() and gets back a ResultSet. The code loops through the ResultSet with while(rs.next()), extracting each patient's ID, first name, last name, date of birth, and NHS number using rs.getInt("patient_id") and rs.getString("first_name"). Each row is added to a List<Patient> object. After the loop, the developer must close the ResultSet, PreparedStatement, and Connection. Modern Java code uses try-with-resources to handle this automatically.

For batch operations — like importing thousands of lab results from a CSV file — the developer uses PreparedStatement.addBatch() and executeBatch(). This sends all the SQL statements in one network trip instead of one at a time, making the import ten times faster. The developer also uses transactions to ensure that if any one insert fails, the entire batch is rolled back so the database is not left with partial data. This is done by calling connection.setAutoCommit(false), running the batch, then calling connection.commit() or connection.rollback() on error.

Finally, the developer writes unit tests using an in-memory database like H2 to verify the query logic without touching the real hospital database. This is a common development practice: test with a lightweight database that behaves just like PostgreSQL but runs in process and resets between test runs.