How to Execute a SQL Query Using JDBC?

Java programming provides a lot of packages for solving problems in our case we need to execute SQL queries by using JDBC. We can execute a SQL query in two different approaches by using PreparedStatement and Statement. These two interfaces are available in java.sql package. When comparing both of them the PreparedStatement approach is secure.

Approaches to Execute a SQL Query using JDBC

We have two different approaches to executing a SQL query using JDBC. Below is the list and we will explain them with examples to understand the concept correctly.

• Using Statement
• Using PreparedStatement

Statement in JDBC

The Statement is an interface that is available in java.sql package with JDBC.

• This interface is part of JDBC API and can execute simple SQL queries without parameters.
• We can create a Statement by using createStatement().
• This method is available in the Connection class.

Example:

In this example, we will write an SQL query to fetch all data from the table in the database. We have already some data in the table. Now we will write an SQL query for fetching that data using Statement.

For this, we have used a database named books and the table name is a book.

Java

package geeksforgeeks;

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

public class RetrieveDataExample {

    public static void main(String[] args) {
        try {
            // load the MySQL JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // establish connection with the database
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/books", "root", "password");
            if (con != null) {
                // SQL query to retrieve data from the 'book' table
                String selectQuery = "SELECT * FROM book";
                Statement statement = con.createStatement();

                // execute the query and get the result set
                ResultSet resultSet = statement.executeQuery(selectQuery);
                System.out.println("The Available Data\n");

                // iterate through the result set and print the data
                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String author_name = resultSet.getString("author");
                    String book_name = resultSet.getString("name");
                    String book_price = resultSet.getString("price");

                    // print the retrieved data
                    System.out.println("ID: " + id + ", Author_Name: " + author_name + ", Book_Name: " + book_name
                            + ", Book_Price " + book_price);
                }
            } else {
                System.out.println("Not Connected...");
            }
        } catch (Exception e) {
            // handle any exceptions that occur
            System.out.println("Exception is " + e.getMessage());
        }
    }
}

Output:

Below we can see the retrieved data in book table.

Explanation of the Code:

• We fetched all data from book table from books database by using SQL query.
• First, we need to connect the database by using required configuration.
• After that create Statement by using createStatement() from connection object.
• After that write SQL query for fetch all data.
• Now Create ResultSet for this assign the result of SQL query.
• Then print that data by using loop statement.

PreparedStatement in JDBC

The PreparedStatement is an interface, and it provides for security for our data by using parameter concept in Java.

• It can prevent SQL Injection attack also from unknown source.
• It is better than Statement.
• We can create PreparedStatement by using prepareStatement() from connection class and it can take SQL query as String value.

Example:

Java

package geeksforgeeks;

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

public class RetrieveDataExample  {


    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/books", "root", "password");
            if (con != null) {

                String selectQuery = "SELECT * FROM book";
                PreparedStatement statement = con.prepareStatement(selectQuery);

                ResultSet resultSet = statement.executeQuery();
                System.out.println("The Available Data\n");
                while (resultSet.next()) {
                    int id = resultSet.getInt("id");
                    String author_name = resultSet.getString("author");
                    String book_name = resultSet.getString("name");
                    String book_price = resultSet.getString("price");

                    System.out.println("ID: " + id + ", Author_Name: " + author_name + ", Book_Name: "
                                       + book_name + ", Book_Price "+book_price);
                }
            } else {
                System.out.println("Not Connected...");
            }

        } catch (Exception e) {
            System.out.println("Exception is " + e.getMessage());
        }
    }
}

Output:

Explanation of the above Code:

• First, we have created Connection with Database by using required configuration.
• Then we have created PreparedStatement by using con.prepareStatement(selectQuery).
• Here selectQuery is the String which is required SQL query for fetching all records from table.
• After that we have created one ResultSet then assign this SQL query result to It.
• After that it display data by using ResultSet object with the help of loop statement.