Java - Database Connectivity - Executing SQL Queries

Overview

Executing SQL queries is a core function of database connectivity in Java applications. Java Database Connectivity (JDBC) provides a set of APIs to execute SQL queries and retrieve results. This tutorial explores how to execute different types of SQL queries using JDBC, including SELECT, INSERT, UPDATE, and DELETE.

Key Points:

SQL queries are executed using Statement or PreparedStatement objects.
ResultSet objects are used to process query results.
Proper resource management and error handling are crucial.

Executing a SELECT Query

To execute a SELECT query, you use the executeQuery method of the Statement or PreparedStatement object. This method returns a ResultSet object, which contains the data returned by the query. Here is an example:


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

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

        try {
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM users");

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

                System.out.println("ID: " + id);
                System.out.println("Name: " + name);
                System.out.println("Email: " + email);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Executing an INSERT Query

To execute an INSERT query, you use the executeUpdate method of the Statement or PreparedStatement object. This method returns the number of rows affected by the query. Here is an example:


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

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

        try {
            Connection connection = DriverManager.getConnection(url, username, password);
            String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "John Doe");
            preparedStatement.setString(2, "john.doe@example.com");
            int rows = preparedStatement.executeUpdate();

            if (rows > 0) {
                System.out.println("A new user has been inserted successfully!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Executing an UPDATE Query

To execute an UPDATE query, you use the executeUpdate method of the Statement or PreparedStatement object. This method returns the number of rows affected by the query. Here is an example:


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

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

        try {
            Connection connection = DriverManager.getConnection(url, username, password);
            String sql = "UPDATE users SET email = ? WHERE name = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "john.newemail@example.com");
            preparedStatement.setString(2, "John Doe");
            int rows = preparedStatement.executeUpdate();

            if (rows > 0) {
                System.out.println("User's email has been updated successfully!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Executing a DELETE Query

To execute a DELETE query, you use the executeUpdate method of the Statement or PreparedStatement object. This method returns the number of rows affected by the query. Here is an example:


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

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

        try {
            Connection connection = DriverManager.getConnection(url, username, password);
            String sql = "DELETE FROM users WHERE name = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "John Doe");
            int rows = preparedStatement.executeUpdate();

            if (rows > 0) {
                System.out.println("User has been deleted successfully!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Using Prepared Statements

PreparedStatement is used for executing parameterized queries, which can help prevent SQL injection attacks. Here is an example of using a prepared statement to insert data into a database:


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

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

        try {
            Connection connection = DriverManager.getConnection(url, username, password);
            String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "John Doe");
            preparedStatement.setString(2, "john.doe@example.com");
            int rows = preparedStatement.executeUpdate();

            if (rows > 0) {
                System.out.println("A new user has been inserted successfully!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Processing Results

The ResultSet object represents the result of a query. You can iterate through the result set to access data. Here is an example of processing a result set:


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

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

        try {
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM users");

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

                System.out.println("ID: " + id);
                System.out.println("Name: " + name);
                System.out.println("Email: " + email);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Example Code

Here is a complete example demonstrating how to connect to a database, execute a query, and process the results using JDBC:


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

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

        try {
            Connection connection = DriverManager.getConnection(url, username, password);
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM users");

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

                System.out.println("ID: " + id);
                System.out.println("Name: " + name);
                System.out.println("Email: " + email);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Summary

In this tutorial, you learned how to execute SQL queries using Java Database Connectivity (JDBC). You explored how to execute SELECT, INSERT, UPDATE, and DELETE queries, use prepared statements, and process the results using the ResultSet object. Understanding these steps is essential for building Java applications that interact with databases.