Database Connectivity – SQL Queries

Introduction to SQL Queries

SQL (Structured Query Language) is a powerful language used for managing and manipulating data in relational databases. When working with databases in Java, you often need to write SQL queries to retrieve, insert, update, or delete data. In this section, we’ll explore how to execute SQL queries in Java applications using JDBC (Java Database Connectivity).

Executing SQL Queries in Java

Java applications can execute SQL queries by following these steps:

1. Establish a Database Connection: Before executing any SQL queries, you must establish a connection to the database using JDBC. This typically involves creating a connection object and providing the necessary connection URL, username, and password.
2. Create a SQL Statement: To execute SQL queries, you need to create a statement object. There are two main types of statement objects: Statement for executing simple queries and PreparedStatement for more complex queries with placeholders for parameters.
3. Execute SQL Queries: You can use the created statement object to execute SQL queries. There are four primary types of SQL queries:
   • SELECT: Used to retrieve data from the database.
   • INSERT: Used to add new records to a table.
   • UPDATE: Used to modify existing records in a table.
   • DELETE: Used to remove records from a table.
4. Process Query Results: If the SQL query is a SELECT statement, you’ll receive a result set containing the retrieved data. You can iterate through the result set to access the data and perform any necessary operations.
5. Close Resources: It’s essential to close the statement, result set, and database connection when you’re done with them to release resources and prevent memory leaks.

Example of Executing SQL Queries in Java

Here’s a Java code example that demonstrates how to execute SQL queries using JDBC. This example connects to a MySQL database, retrieves data from a “products” table, and prints the results:

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

public class QueryExample {

    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "yourUsername";
        String password = "yourPassword";

        try {
            // Establish a database connection
            Connection connection = DriverManager.getConnection(jdbcUrl, username, password);

            // Create a statement
            Statement statement = connection.createStatement();

            // Execute a SELECT query
            String selectQuery = "SELECT * FROM products";
            ResultSet resultSet = statement.executeQuery(selectQuery);

            // Process the query results
            while (resultSet.next()) {
                int productId = resultSet.getInt("product_id");
                String productName = resultSet.getString("product_name");
                double price = resultSet.getDouble("price");

                System.out.println("Product ID: " + productId);
                System.out.println("Product Name: " + productName);
                System.out.println("Price: " + price);
            }

            // Close resources
            resultSet.close();
            statement.close();
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Conclusion

SQL queries are essential for interacting with databases in Java applications. Whether you need to retrieve data, insert new records, update existing data, or delete records, understanding how to write and execute SQL queries using JDBC is a crucial skill for developers working with databases in Java.