36. JDBC: Connecting to SQL Databases

[!NOTE] Until now, variables disappeared when the program closed. To build real applications, you need persistent state. Java Database Connectivity (JDBC) is the official API used to connect Java directly to relational databases.

The JDBC Architecture Pipeline

Whether you are connecting to Oracle, PostgreSQL, or MySQL, JDBC uses the exact same interface code. You just drop in the correct "Driver" binary file provided by your specific database company.

The Pipeline:

1. Securely Connect to the Database.
2. Create a SQL Statement.
3. Execute the Statement.
4. Process the returned ResultSet.
5. Close the connection to prevent memory leaks!

Example: Selecting Data

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DatabaseExample {
    public static void main(String[] args) {
        
        String url = "jdbc:postgresql://localhost:5432/my_company";
        String user = "admin";
        String pwd = "super_secret_password";

        // We wrap database connections in Try-Catch because networking can fail!
        try (Connection conn = DriverManager.getConnection(url, user, pwd)) {

            System.out.println("Connection physically established to PostgreSQL!");

            // Create the Query Object
            Statement stmt = conn.createStatement();
            
            // Execute the raw query safely!
            String sql = "SELECT id, username, permission_level FROM users";
            ResultSet rs = stmt.executeQuery(sql);

            // Iterate through the results vertically!
            while (rs.next()) {
                // Extract column data from the current row
                int id = rs.getInt("id");
                String name = rs.getString("username");
                System.out.println("Row Scanned! ID: " + id + ", Name: " + name);
            }

        } catch (Exception e) {
            System.err.println("Fatal Database Connection Error!");
            e.printStackTrace();
        }
    }
}

  

The Terror of SQL Injection

If your application asks the user to type in their "Username" into an HTML form to trigger a SELECT check, a malicious hacker can type in exactly this string: '; DROP TABLE users; --

If you blindly concatenate their string directly into your raw SQL format: "SELECT * FROM users WHERE name = '" + userInput + "';"

You will physically transmit the command to drop the entire table, instantly wiping production data.

The Fix: PreparedStatement

Never, ever use string concatenation for SQL queries. Always use PreparedStatement. It mathematically escapes hacker strings safely as raw text.

// 1. The '?' acts as an armored parameter slot
String safeSql = "SELECT * FROM users WHERE username = ?";

// 2. PreparedStatement pre-compiles the query defensively
PreparedStatement pstmt = conn.prepareStatement(safeSql);

// 3. You inject the user's string. If they typed '; DROP TABLE', it is safely escaped!
pstmt.setString(1, userInput);

ResultSet rs = pstmt.executeQuery();

  

String sql = "SELECT id, username FROM users WHERE username = ?";

try (
    Connection connection = dataSource.getConnection();
    PreparedStatement statement = connection.prepareStatement(sql)
) {
    statement.setString(1, username);
    try (ResultSet rs = statement.executeQuery()) {
        while (rs.next()) {
            System.out.println(rs.getString("username"));
        }
    }
}
  

connection.setAutoCommit(false);
try {
    // execute multiple related updates
    connection.commit();
} catch (SQLException e) {
    connection.rollback();
    throw e;
}