JDBC Database Connectivity Setup

JDBC (Java Database Connectivity) provides a standardized API for interacting with relational databases. The first step involves establishing a connection to the database server.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DbConnector {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/testdb";
    private static final String USER = "dbuser";
    private static final String PASS = "dbpass";

    public static Connection establish() throws SQLException {
        return DriverManager.getConnection(DB_URL, USER, PASS);
    }
}

Replace the connection parameters with your actual database credentials and endpoint.

Executing Queries and Processing Results

After establishing connectivity, you can execute SQL queries and iterate through the returned data using a PreparedStatement and ResultSet.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserRepository {
    public static void fetchUserRecords() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rows = null;
        
        try {
            conn = DbConnector.establish();
            String query = "SELECT username, age FROM users WHERE active = ?";
            stmt = conn.prepareStatement(query);
            stmt.setBoolean(1, true);
            rows = stmt.executeQuery();
            
            while (rows.next()) {
                String name = rows.getString("username");
                int userAge = rows.getInt("age");
                System.out.printf("User: %s | Age: %d%n", name, userAge);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            closeQuietly(rows);
            closeQuietly(stmt);
            closeQuietly(conn);
        }
    }
    
    private static void closeQuietly(AutoCloseable resource) {
        if (resource != null) {
            try {
                resource.close();
            } catch (Exception ignored) {}
        }
    }
}

The PreparedStatement approach prevents SQL injection by parameetrizing query inputs. The ResultSet cursor initially positions before the first row; calling next() advances through each record.

Handling Different Data Types

The ResultSet interface provides type-safe getters for various column types:

Method	Return Type	Use Case
getString(col)	String	VARCHAR, TEXT
getInt(col)	int	INTEGER
getDouble(col)	double	DECIMAL, FLOAT
getDate(col)	java.sql.Date	DATE
getBoolean(col)	boolean	BOOLEAN

Best Practices

Always close resources in a finally block or use try-with-resources (Java 7+) to prevent memory leaks. Maintain a single connection instance per database when possible rather than creating new connections for each query.