Posts tagged: SQLite)

Retrieving data from a database in Java typically involves using the JDBC (Java Database Connectivity) API. The ResultSet interface in JDBC is fundamental for fetching and processing data from a relational database. In this example, I’ll guide you through creating a simple Java program that connects to a database, executes a query, and retrieves the results using the ResultSet interface.

Setting Up the Database

Before you begin, ensure you have a database installed (e.g., MySQL, PostgreSQL, SQLite) and the necessary JDBC driver for that database. For this example, let’s assume you are using MySQL.

1. Download MySQL Connector/J: Download the MySQL Connector/J JDBC driver from the official MySQL website: MySQL Connector/J.
2. Create a Database and Table: Create a database and a sample table. Here’s an example SQL script:

CREATE DATABASE sampledb;
USE sampledb;

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

INSERT INTO employees VALUES (1, 'John Doe', 30);
INSERT INTO employees VALUES (2, 'Jane Doe', 25);

Writing the Java Program

Now, let’s create a Java program that connects to the database, executes a query, and retrieves the data using the ResultSet interface.

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

public class DatabaseRetrievalExample {

    // JDBC URL, username, and password of MySQL server
    private static final String JDBC_URL = "jdbc:mysql://localhost:3306/sampledb";
    private static final String USERNAME = "your_username";
    private static final String PASSWORD = "your_password";

    public static void main(String[] args) {
        // Step 1: Load and register the JDBC driver
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.err.println("Error loading JDBC driver: " + e.getMessage());
            e.printStackTrace();
            return;
        }

        // Step 2: Establish a connection to the database
        try (Connection connection = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD)) {

            // Step 3: Create a Statement object to execute SQL queries
            try (Statement statement = connection.createStatement()) {

                // Step 4: Execute a SQL query
                String query = "SELECT * FROM employees";
                try (ResultSet resultSet = statement.executeQuery(query)) {

                    // Step 5: Process the result set
                    while (resultSet.next()) {
                        int id = resultSet.getInt("id");
                        String name = resultSet.getString("name");
                        int age = resultSet.getInt("age");

                        // Process the retrieved data (you can print or use it as needed)
                        System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
                    }
                }
            }

        } catch (Exception e) {
            System.err.println("Database connection error: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

Explanation of the Code

1. Load and Register JDBC Driver:

Class.forName("com.mysql.cj.jdbc.Driver");

1. This line loads and registers the MySQL JDBC driver. It’s necessary to load the driver before establishing a connection.
2. Establish a Connection:

Connection connection = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);

1. Use the DriverManager.getConnection method to establish a connection to the database. Replace "your_username" and "your_password" with your actual database username and password.
2. Create a Statement:

Statement statement = connection.createStatement();

1. The createStatement method creates a Statement object, which is used to execute SQL queries.
2. Execute a SQL Query:

String query = "SELECT * FROM employees";
ResultSet resultSet = statement.executeQuery(query);

1. The executeQuery method is used to execute a SQL query and obtain a ResultSet containing the results.
2. Process the Result Set:

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

    // Process the retrieved data (you can print or use it as needed)
    System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
}

1. The next method of the ResultSet moves the cursor to the next row, and you can retrieve data from each column using appropriate getter methods.

Running the Program:

1. Compile the Java Program:

javac DatabaseRetrievalExample.java

2.Run the Java Program:

java DatabaseRetrievalExample

If everything is set up correctly, you should see the program connect to the database, execute the query, and print the retrieved data.

Remember to replace the placeholder values for USERNAME and PASSWORD with your actual database credentials. Additionally, make sure the JDBC driver for your specific database is on the classpath when running the program.