Executing a simple SQL query using a Statement object in JDBC follows a straightforward pattern: establish a connection, create the statement, execute the query, and process the results.

Here is a clean example of how to perform a SELECT query:

Simple SQL Query Example

package org.kodejava.jdbc;

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

public class SimpleQueryExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/your_database";
        String user = "username";
        String password = "password";

        String sql = "SELECT id, username, email FROM users";

        // Use try-with-resources to ensure resources are closed automatically
        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            // Iterate through the result set
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("username");
                String email = rs.getString("email");

                System.out.println("ID: " + id + ", Name: " + name + ", Email: " + email);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Key Methods to Know

The Statement interface provides different methods depending on the type of SQL you are running:

1. executeQuery(String sql): Used for SELECT statements. It returns a ResultSet containing the data.
2. executeUpdate(String sql): Used for INSERT, UPDATE, or DELETE statements. It returns an int representing the number of rows affected.
3. execute(String sql): A general-purpose method that can execute any SQL statement. It returns true if the result is a ResultSet (query) and false if it is an update count or there are no results.

Important Tips

• Try-with-Resources: Always use the try-with-resources block (shown above) for Connection, Statement, and ResultSet. This prevents memory leaks by ensuring the database handles are closed even if an exception occurs.
• Security: While Statement is great for simple or static queries, use PreparedStatement if your query includes variables provided by a user. This prevents SQL Injection attacks.
• Indices vs. Names: When reading from a ResultSet, you can use column names (e.g., rs.getString("username")) or 1-based indices (e.g., rs.getString(1)). Names are generally more readable and maintainable.