Lesson 2	The course project assists in learning JDBC
Objective	Describe the course project purpose and how it will assist you in learning JDBC.

Steps involved in establishing JDBC Database Connection

Here is a breakdown of the steps involved in establishing a database connection using JDBC:

Include the JDBC Library: Ensure the JDBC driver library (JAR file) for your specific database is included in your project's classpath. This library provides the classes and methods needed for interaction.
Load the JDBC Driver: Use `Class.forName()` to dynamically load the JDBC driver class. This registers the driver with the JDBC `DriverManager`.
Example:
```
Class.forName("com.mysql.cj.jdbc.Driver"); // For MySQL
```
(Replace with the appropriate driver class for your database.)

Establish a Connection:

Obtain a `Connection` object using `DriverManager.getConnection()`.
Provide the connection URL, username, and password as arguments.

Example:

String url = "jdbc:mysql://localhost:3306/mydatabase"; 
String user = "myuser";
String password = "mypassword";
Connection connection = DriverManager.getConnection(url, user, password);

Create a Statement: Use the `Connection` object to create a `Statement` object. This statement will be used to execute SQL queries.
Example:
```
Statement statement = connection.createStatement();
```
Execute SQL Queries:
- Use the `Statement` object's `executeQuery()` method to execute SELECT queries and retrieve results in a `ResultSet` object.
- Use `executeUpdate()` for INSERT, UPDATE, or DELETE queries, which return the number of affected rows.
Process Results (if applicable): Iterate through the `ResultSet` (if you executed a SELECT query) to retrieve data from the database.
Close Resources: It's crucial to close the `ResultSet`, `Statement`, and `Connection` objects in the reverse order they were created to release database resources.

Example (Complete):

import java.sql.*;

public class DatabaseConnectionExample {
    public static void main(String[] args) {
        // ... (Steps 1-3 as described above) ...

        try (Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery("SELECT * FROM employees")) {
            while (resultSet.next()) {
                // Process results
            }
        } catch (SQLException e) {
            // Handle exceptions
        } finally {
            // Close resources in reverse order
            try { if (statement != null) statement.close(); } catch (SQLException ignored) {}
            try { if (connection != null) connection.close(); } catch (SQLException ignored) {}
        }
    }
}

Key Considerations:

Error Handling: Use `try-catch` blocks to handle `SQLException` exceptions that might occur during the connection or query execution process.
Security: Never hardcode sensitive information like passwords directly in your code. Consider using environment variables, configuration files, or other secure mechanisms.
Connection Pooling: In production environments, use connection pooling to manage database connections efficiently.

Fundamental Concepts and Syntax to perform Simple Database Tasks using JDBC

Here are the fundamental concepts and syntax to perform simple database tasks with JDBC:

Loading the JDBC driver: Before you can connect to a database using JDBC, you need to load the JDBC driver for the database you are using. You can load the driver using the Class.forName() method, as shown in the following example:
```
Class.forName("com.mysql.jdbc.Driver");
```
Creating a database connection: Once you have loaded the JDBC driver, you can create a database connection using the DriverManager.getConnection() method. You need to provide the URL of the database, the username, and the password as parameters to this method, as shown in the following example:
```
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/mydatabase", "myuser", "mypassword");
```
Creating a statement object: Once you have a database connection, you can create a statement object using the Connection.createStatement() method. You can use this statement object to execute SQL queries and updates, as shown in the following example:
```
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM mytable");
```
Executing SQL queries and updates: You can use the statement object to execute SQL queries and updates using the executeQuery() and executeUpdate() methods, respectively. The executeQuery() method returns a ResultSet object that contains the results of the query, while the executeUpdate() method returns the number of rows affected by the update. Here are some examples:
```
ResultSet rs = stmt.executeQuery("SELECT * FROM mytable");
int rowsAffected = stmt.executeUpdate("UPDATE mytable SET name='John' WHERE id=1");
```
Retrieving data from the ResultSet: Once you have executed a query and obtained a ResultSet object, you can retrieve the data from the result set using the ResultSet.next() method to move the cursor to the next row, and the ResultSet.getXXX() methods to retrieve the values of the columns in the current row. Here's an example:
```
ResultSet rs = stmt.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
    int id = rs.getInt("id");
    String name = rs.getString("name");
    int age = rs.getInt("age");
    // process the data
}
```
Closing the database resources: Once you are done with the database resources, you should close them to release the database connections and statements. You can use the close() method to close the resources, as shown in the following example:
```
rs.close();
stmt.close();
conn.close();
```

You now know the basics of the course project. Next, we will explore your case study and learn about your responsibilities as a member of a solution development team.
Click the Exercise link to download the course Brazil Project source code to your student folder.
Course Project - Exercise