Installation and Configuration
Programming Methodology

Managing Connections and Transactions

In this exercise, you will learn how to manage database connections and transactions in Java using JDBC. By creating a program that efficiently manages connections and performs transactions to ensure data integrity, you will understand how to work with SQL transactions, handle errors, and gracefully close connections in a professional programming environment.

Topic

Databases and JDBC

Java Exercise

In this exercise, you will create a Java program that connects to a database using JDBC. The program will execute SQL queries to retrieve information and then display the results. You will begin by establishing a connection to the database, execute a SELECT query, and process the returned data. This exercise will help you understand how to integrate databases into Java applications, using SQL queries to interact with data effectively.

Instructions:

  1. Configure your environment to work with JDBC and ensure you have an accessible database.
  2. Create a connection to the database using the appropriate JDBC driver for your database.
  3. Execute an SQL query using a SELECT statement to retrieve data.
  4. Receive and process the results using a ResultSet.
  5. Close the connection once you have finished processing the data.

This exercise will give you a solid understanding of how to interact with databases from Java, using JDBC to execute SQL queries and process the results efficiently.


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

public class SQLQueries {
    private static final String URL = "jdbc:mysql://localhost:3306/myDatabase";
    private static final String USER = "user";
    private static final String PASSWORD = "password";

    public static void main(String[] args) {
        // Using try-with-resources to ensure resources are automatically closed
        try (
            // Declare resources inside the parentheses
            Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT name, salary FROM employees")
        ) {
            // Process the query results
            while (rs.next()) {
                String name = rs.getString("name");
                double salary = rs.getDouble("salary");
                System.out.println("Employee: " + name + ", Salary: " + salary);
            }

        } catch (SQLException e) {
            // Proper exception handling
            System.err.println("SQL Error: " + e.getMessage());
        }
    }
}

 Output:

Employee: Juan, Salary: 5000.0
Employee: Ana, Salary: 4500.0
Employee: Carlos, Salary: 6000.0

This program demonstrates how to handle database connections and perform transactions using JDBC in Java. A connection to the database is established, several SQL operations are performed within a transaction, and if everything is successful, the transaction is committed. If an error occurs, transactions are rolled back using the rollback() method, and resources (connections) are gracefully closed at the end.


 Share this JAVA exercise