How to SELECT data from a MySQL database using Java

A guide on how to use Prepared Statements in Java to query data from a table in a MySQL database safely and effectively.

In this article, you'll learn how to connect to a MySQL database and use Java with Prepared Statements to execute a SELECT statement, allowing you to retrieve data from a table in the database using multiple parameters.

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

public class SelectData {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/test_db";
        String user = "root";
        String password = "password";
        
        String selectQuery = "SELECT * FROM students WHERE id = ? AND name = ?";
        
        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement pstmt = conn.prepareStatement(selectQuery)) {
            
            // Set parameters for Prepared Statement
            pstmt.setInt(1, 1);
            pstmt.setString(2, "John Doe");
            
            // Execute query
            ResultSet rs = pstmt.executeQuery();
            
            // Process query results
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                System.out.println("ID: " + id + ", Name: " + name);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Detailed explanation:

1. import java.sql.*;: Imports the necessary classes for database operations.
2. String url = "jdbc:mysql://localhost:3306/test_db";: The connection string to the MySQL database.
3. String user = "root";: The username for the connection.
4. String password = "password";: The user's password.
5. String selectQuery = "SELECT * FROM students WHERE id = ? AND name = ?";: Defines the SELECT statement with parameters.
6. try (Connection conn = DriverManager.getConnection(url, user, password); PreparedStatement pstmt = conn.prepareStatement(selectQuery)): Creates the connection and Prepared Statement in a try-with-resources block for automatic resource management.
7. pstmt.setInt(1, 1);: Sets the first parameter (ID) for the Prepared Statement.
8. pstmt.setString(2, "John Doe");: Sets the second parameter (Name) for the Prepared Statement.
9. ResultSet rs = pstmt.executeQuery();: Executes the query and stores the result in a ResultSet.
10. while (rs.next()) {...}: Iterates through the records in the ResultSet and prints the information.
11. e.printStackTrace();: Prints the error message if an exception occurs.

System Requirements:

• Java 8 or higher
• MySQL Connector/J library

How to install the libraries needed to run the Java code above:

• Download MySQL Connector/J from the MySQL Connector/J page and add it to your project's classpath.

Tips:

• Ensure your MySQL server is running and properly configured.
• Double-check your connection details like URL, username, and password to avoid connection errors.
• Use Prepared Statements to protect against SQL injection attacks.