Executing SQL queries and transactions in Java

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases. Java provides an extensive support for executing SQL queries and transactions, which allows developers to interact with databases seamlessly.

Establishing Database Connection

Before executing SQL queries or transactions, it is essential to establish a connection to the database. Java provides the JDBC (Java Database Connectivity) API, which serves as a bridge between the Java code and the underlying database.

To establish a database connection, you need to: ```java import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException;

public class DatabaseConnection { private static final String URL = "jdbc:mysql://localhost:3306/mydatabase"; private static final String USERNAME = "username"; private static final String PASSWORD = "password";

public static Connection getConnection() throws SQLException {
    return DriverManager.getConnection(URL, USERNAME, PASSWORD);
}

} ```

Here, you need to replace the URL, USERNAME, and PASSWORD with appropriate values for your database.

Executing SQL Queries

Once the connection is established, you can execute SQL queries using the Statement or PreparedStatement classes provided by JDBC.

Using Statement

The Statement interface allows you to execute simple SQL queries without any parameters.

import java.sql.*;

public class Example {
    public static void main(String[] args) {
        try (Connection connection = DatabaseConnection.getConnection();
             Statement statement = connection.createStatement()) {

            String sql = "SELECT * FROM customers";
            ResultSet resultSet = statement.executeQuery(sql);

            while (resultSet.next()) {
                // Process the retrieved data
                System.out.println(resultSet.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Using PreparedStatement

The PreparedStatement interface is used for parameterized SQL queries to prevent SQL injection attacks.

import java.sql.*;

public class Example {
    public static void main(String[] args) {
        try (Connection connection = DatabaseConnection.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM customers WHERE age > ?")) {

            preparedStatement.setInt(1, 18);
            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                // Process the retrieved data
                System.out.println(resultSet.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Managing Transactions

Transactions are used to ensure the integrity of data in the database. In Java, you can manage transactions using the Connection interface.

import java.sql.*;

public class Example {
    public static void main(String[] args) {
        try (Connection connection = DatabaseConnection.getConnection()) {
            connection.setAutoCommit(false); // Disable auto-commit

            try {
                // Execute multiple SQL statements as a transaction

                // Commit the transaction if everything is successful
                connection.commit();
            
            } catch (SQLException e) {
                // Rollback the transaction if an error occurs
                connection.rollback();
                e.printStackTrace();
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

By disabling the auto-commit mode, you have control over when to commit or rollback the transaction.

Conclusion

Executing SQL queries and transactions in Java becomes easier with the JDBC API. By establishing a database connection and using Statement or PreparedStatement, you can execute SQL queries efficiently. Additionally, managing transactions using the Connection interface ensures data integrity in the database.