JDBC Database

Key JDBC Concepts

Connection conn = DriverManager
    .getConnection(url, user, pass);

Statement stmt = conn.createStatement();
stmt.executeQuery("SELECT * FROM table");

ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) { /* process */ }

PreparedStatement ps = conn
    .prepareStatement("SELECT * FROM users WHERE id = ?");

🔹 JDBC Connection Steps

Standard process for connecting to a database:

import java.sql.*;

public class JDBCConnection {
    // Database credentials
    private static final String URL = "jdbc:mysql://localhost:3306/testdb";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "password";
    
    public static void main(String[] args) {
        Connection connection = null;
        
        try {
            // Step 1: Load the driver (optional in newer versions)
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            // Step 2: Establish connection
            connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            System.out.println("Connected to database successfully!");
            
            // Step 3: Create statement
            Statement statement = connection.createStatement();
            
            // Step 4: Execute query
            String sql = "SELECT id, name, email FROM users";
            ResultSet resultSet = statement.executeQuery(sql);
            
            // Step 5: Process results
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String email = resultSet.getString("email");
                
                System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
            }
            
        } catch (ClassNotFoundException e) {
            System.out.println("MySQL JDBC Driver not found!");
        } catch (SQLException e) {
            System.out.println("Connection failed: " + e.getMessage());
        } finally {
            // Step 6: Close connection
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

🔹 CRUD Operations

Create, Read, Update, Delete operations with JDBC:

public class CRUDOperations {
    private Connection connection;
    
    public CRUDOperations() throws SQLException {
        connection = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/testdb", "root", "password");
    }
    
    // CREATE - Insert new record
    public void insertUser(String name, String email) throws SQLException {
        String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setString(1, name);
        pstmt.setString(2, email);
        
        int rowsAffected = pstmt.executeUpdate();
        System.out.println(rowsAffected + " row(s) inserted.");
    }
    
    // READ - Select records
    public void getAllUsers() throws SQLException {
        String sql = "SELECT * FROM users";
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery(sql);
        
        while (rs.next()) {
            System.out.println("ID: " + rs.getInt("id") + 
                             ", Name: " + rs.getString("name") + 
                             ", Email: " + rs.getString("email"));
        }
    }
    
    // UPDATE - Modify existing record
    public void updateUser(int id, String newName) throws SQLException {
        String sql = "UPDATE users SET name = ? WHERE id = ?";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setString(1, newName);
        pstmt.setInt(2, id);
        
        int rowsAffected = pstmt.executeUpdate();
        System.out.println(rowsAffected + " row(s) updated.");
    }
    
    // DELETE - Remove record
    public void deleteUser(int id) throws SQLException {
        String sql = "DELETE FROM users WHERE id = ?";
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setInt(1, id);
        
        int rowsAffected = pstmt.executeUpdate();
        System.out.println(rowsAffected + " row(s) deleted.");
    }
    
    public void close() throws SQLException {
        if (connection != null) {
            connection.close();
        }
    }
}

🔹 PreparedStatement Benefits

Using PreparedStatement for secure and efficient queries:

public class PreparedStatementExample {
    
    // Secure way to handle user input
    public User getUserById(int userId) throws SQLException {
        String sql = "SELECT * FROM users WHERE id = ?";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, userId);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setEmail(rs.getString("email"));
                return user;
            }
        }
        return null;
    }
    
    // Batch operations for better performance
    public void insertMultipleUsers(List<User> users) throws SQLException {
        String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
        
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            for (User user : users) {
                pstmt.setString(1, user.getName());
                pstmt.setString(2, user.getEmail());
                pstmt.addBatch();
            }
            
            int[] results = pstmt.executeBatch();
            System.out.println("Inserted " + results.length + " users");
        }
    }
}