Submit Queries and Read Results from a Database in Java

A Guide to Using JDBC for Database Operations

Introduction

Java Database Connectivity (JDBC) is an API that allows Java applications to interact with relational databases. Using JDBC, you can submit queries, retrieve result sets, and iterate through the results with ease. This tutorial covers creating statements, executing queries, and processing data.

Prerequisites

• A database management system (e.g., MySQL, PostgreSQL, SQLite).
• JDBC driver for your database (e.g., mysql-connector-java for MySQL).
• A basic understanding of SQL.

Establishing a Database Connection

To interact with a database, you first need to establish a connection using the DriverManager class. Here’s how to connect to a MySQL database:

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

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

        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            System.out.println("Connected to the database!");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
            

Replace your_database, your_username, and your_password with your actual database details.

Submitting Queries and Retrieving Results

Once connected, you can create a Statement or PreparedStatement to execute SQL queries. Here’s an example of retrieving data from a table:

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

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

        try (Connection connection = DriverManager.getConnection(url, user, password);
             Statement statement = connection.createStatement()) {

            String query = "SELECT id, name, age FROM users";
            ResultSet resultSet = statement.executeQuery(query);

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

                System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
            

In this example:

• executeQuery() is used for SELECT statements, returning a ResultSet.
• resultSet.next() advances to the next row in the result set.
• getInt() and getString() retrieve column values by name or index.

Using Prepared Statements

Prepared statements are a more secure and efficient way to execute SQL queries, especially when parameters are involved. Here’s an example:

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

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

        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            String query = "SELECT id, name, age FROM users WHERE age > ?";
            PreparedStatement preparedStatement = connection.prepareStatement(query);
            preparedStatement.setInt(1, 25);

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");

                System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
            

Benefits of using PreparedStatement:

• Prevents SQL injection attacks.
• Improves performance for repeated queries.
• Supports dynamic query parameters.

Iterating Through a Result Set

A ResultSet is a cursor-like structure that allows you to traverse query results. Common methods include:

• next(): Moves to the next row.
• getInt(), getString(), etc.: Retrieve column values by name or index.
• close(): Closes the result set to release resources.

Example of iterating with metadata:

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

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

        try (Connection connection = DriverManager.getConnection(url, user, password);
             Statement statement = connection.createStatement()) {

            String query = "SELECT id, name, age FROM users";
            ResultSet resultSet = statement.executeQuery(query);
            ResultSetMetaData metaData = resultSet.getMetaData();

            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    System.out.print(metaData.getColumnName(i) + ": " + resultSet.getObject(i) + " ");
                }
                System.out.println();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
            

Best Practices

• Always close Connection, Statement, and ResultSet objects to prevent resource leaks.
• Use try-with-resources for automatic resource management.
• Validate and sanitize user input to prevent SQL injection.
• Use connection pools (e.g., HikariCP) for better performance in production environments.

Conclusion

JDBC provides a robust framework for interacting with relational databases in Java. By mastering how to submit queries, retrieve results, and iterate through result sets, you can build efficient and secure database-driven applications. Combine these skills with advanced features like transactions and connection pooling for more complex use cases.