【Java】 Java Project 挑战系列第11篇：Java Database-• Java Database Connectivity （JDBC）

• JDBC API uses JDBC drivers to connect with the database.

•JDBC API使用JDBC驱动程序连接数据库。

• JDBC API provides the access to tabular data stored in any relational database.

•JDBC API提供了对存储在任何关系数据库中的表格数据的访问。

• JDBC API enables executing the query like save, update, delete, and fetch data from the database.

•JDBC API允许从数据库中执行查询，如保存，更新，删除和获取数据。

• Why Should We Use JDBC?

Before JDBC, Open Database Connectivity (ODBC) API was the database API to connect and execute the query with the database. However, the ODBC driver is written in C language (i.e. platform dependent and unsecured). That is why Java has defined its own API (JDBC API) that uses JDBC drivers (written in Java language).

There are 5 steps to connect any java application with the database using JDBC. These steps are as follows:

0. Import the JDBC packages: First, you need to import the necessary JDBC packages. This can be done by using the import statement, which includes the classes and interfaces that are required to establish a database connection.

1. Register the Driver class. Once the packages are imported, you need to load and register the JDBC driver. This is done by calling the Class.forName() method, which loads the driver class and registers it with the DriverManager.

2. Create connections. 

3. Create statements. After loading the driver, you can establish a connection to the database by calling the DriverManager.getConnection() method. This method takes a database URL, username, and password as its parameters.

4. Execute queries.  Execute the SQL statement: Finally, you can execute the SQL statement by calling the executeQuery() or executeUpdate() method of the Statement interface, depending on whether you want to retrieve data or update the database.

5. Close connection.

Register the driver class

The forName() method of Class class is used to register the driver class. This method is used to dynamically load the driver class.

Example to register the OracleDriver class:

// Registering the Oracle JDBC driver class dynamically using forName() method
Class.forName("oracle.jdbc.driver.OracleDriver");

Create the connection object

The getConnection() method of DriverManager class is used to establish a connection with the database.

Example to establish a connection with the Oracle database:

// Registering the Oracle JDBC driver class dynamically
Class.forName("oracle.jdbc.driver.OracleDriver");

// Establishing a connection with the Oracle database
String url = "jdbc:oracle:thin:@localhost:1521:xe";
String username = "your_username_here";
String password = "your_password_here";
Connection connection = DriverManager.getConnection(url, username, password);

In the above code snippet, the getConnection() method is called with three parameters - the database URL, username, and password. The URL format for an Oracle database is jdbc:oracle:thin:@<hostname>:<port>:<database name>. In this example, the database is running on the localhost with port 1521 and the name xe.

After the connection is established, the Connection object is returned and stored in the connection variable. This object is used to interact with the database to execute SQL statements and retrieve data.

What about the database Java Chatroom on my computer which is created by my MySQL?

Here's an example of how to establish a connection with a MySQL database named "JavaChatroom" running on your computer:

// Registering the MySQL JDBC driver class dynamically
Class.forName("com.mysql.cj.jdbc.Driver");

// Establishing a connection with the MySQL database
String url = "jdbc:mysql://localhost:3306/JavaChatroom";
String username = "your_username_here";
String password = "your_password_here";
Connection connection = DriverManager.getConnection(url, username, password);

Can I still make this step if there is no database and I want to Create one with my Java and then Get connection and use it?

No, you cannot create a database using Java code directly. You need to use a database management system (DBMS) like MySQL, Oracle, or PostgreSQL to create a database.

Create the Statement object

The createStatement() method of the Connection interface is used to create a statement. The object of the statement is responsible to execute queries with the database. 

Here's an example of how to establish a connection with a MySQL database named "JavaChatroom" and perform some basic operations like creating a table and inserting data into it:

// Registering the MySQL JDBC driver class dynamically
Class.forName("com.mysql.cj.jdbc.Driver");

// Establishing a connection with the MySQL database
String url = "jdbc:mysql://localhost:3306/JavaChatroom";
String username = "your_username_here";
String password = "your_password_here";
Connection connection = DriverManager.getConnection(url, username, password);

// Creating a table named "users"
Statement statement = connection.createStatement();
String createTableQuery = "CREATE TABLE users (id INT, name VARCHAR(50))";
statement.executeUpdate(createTableQuery);

// Inserting data into the "users" table
String insertDataQuery = "INSERT INTO users VALUES (1, 'John Doe'), (2, 'Jane Smith')";
statement.executeUpdate(insertDataQuery);

// Retrieving data from the "users" table
String retrieveDataQuery = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(retrieveDataQuery);

// Displaying the retrieved data
while (resultSet.next()) {
    int id = resultSet.getInt("id");
    String name = resultSet.getString("name");
    System.out.println("id: " + id + ", name: " + name);
}

Create the Statement object

The PreparedStatement() method of the Connection interface is used to create a parameterized statement. It is used to execute the parameterized query with the database. Why use PreparedStatement? Improves performance: The performance of the application will be faster if you use the PreparedStatement interface because query is compiled only once. 

Actually, to clarify, the PreparedStatement interface is used to create a precompiled SQL statement with placeholders for parameters. These placeholders can be filled with actual parameter values at runtime, which can improve performance and prevent SQL injection attacks.

Here's an example of how to create a PreparedStatement object to execute a parameterized SQL query that retrieves data from a hypothetical "employees" table:

// Assuming that a Connection object named "connection" has already been created
String sqlQuery = "SELECT * FROM employees WHERE department = ?";
PreparedStatement statement = connection.prepareStatement(sqlQuery);

In the above code snippet, the sqlQuery string contains a parameterized SQL query that retrieves data from the "employees" table based on the value of a "department" column. The ? character represents a placeholder for the parameter value that will be filled in later.

Note that the PreparedStatement interface extends the Statement interface, so you can still use the executeQuery() and executeUpdate() methods to execute the query and retrieve data from the result set.

Execute the query and obtain the result

In the following example, the executeQuery() method of the Statement interface is used to execute queries to the database. This method returns the object of ResultSet that can be used to get all the table records. Example to create the statement object:

// Assuming that a Connection object named "connection" has already been created
Statement statement = connection.createStatement();

// Executing a SELECT query to retrieve all records from a hypothetical "employees" table
String sqlQuery = "SELECT * FROM employees";
ResultSet resultSet = statement.executeQuery(sqlQuery);

// Looping through the result set and printing each record
while (resultSet.next()) {
    int employeeId = resultSet.getInt("employee_id");
    String firstName = resultSet.getString("first_name");
    String lastName = resultSet.getString("last_name");
    String department = resultSet.getString("department");
    double salary = resultSet.getDouble("salary");
    System.out.println("Employee ID: " + employeeId + ", Name: " + firstName + " " + lastName +
        ", Department: " + department + ", Salary: " + salary);
}

The executeQuery() method is called with the sqlQuery string as a parameter to execute the query and retrieve a ResultSet object. This object contains the selected records from the table.

The while loop is used to iterate through the ResultSet object using the next() method. This method moves the cursor to the next record in the result set and returns true if there are more records, or false if there are no more records.

Inside the loop, the getInt(), getString(), and getDouble() methods of the ResultSet interface are used to retrieve the values of each column in the current record, based on their column names. These values are then printed to the console using System.out.println().

More about Execute the query

The Statement interface provides methods to execute queries with the database.

• public ResultSet executeQuery(String sql): is used to execute SELECT query. It returns the object of ResultSet.

• public int executeUpdate(String SQL): is used to execute the specified query, it may be create, drop, insert, update, delete, etc.

• public boolean execute(String sql): is used to execute queries that may return multiple results.(存疑？)

• public int[] executeBatch(): is used to execute batch of commands.

Here's an example code that demonstrates how to connect to a MySQL database using JDBC and execute a few SQL queries using the Statement interface:

import java.sql.*;

public class ExampleJDBC {
    public static void main(String[] args) {
        try {
            // Step 1: Establishing a Connection
            Connection conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/mydatabase", "root", "mypassword");

            // Step 2: Creating a Statement
            Statement statement = conn.createStatement();

            // Step 3: Executing a Query
            ResultSet resultSet = statement.executeQuery("SELECT * FROM employees");

            // Step 4: Processing the Result Set
            while (resultSet.next()) {
                System.out.println("Employee ID: " + resultSet.getInt("id") +
                        ", Name: " + resultSet.getString("name") +
                        ", Age: " + resultSet.getInt("age"));
            }

            // Step 5: Executing an Update Statement
            int rowsAffected = statement.executeUpdate(
                    "UPDATE employees SET age = 25 WHERE id = 1");
            System.out.println(rowsAffected + " rows updated");

            // Step 6: Executing a Batch of Statements
            statement.addBatch("INSERT INTO employees VALUES (4, 'John Doe', 30)");
            statement.addBatch("INSERT INTO employees VALUES (5, 'Jane Smith', 28)");
            int[] batchResult = statement.executeBatch();
            System.out.println("Batch executed, " + batchResult.length + " statements executed");

            // Step 7: Closing the Connection and Statement
            statement.close();
            conn.close();

        } catch (SQLException e) {
            System.err.println("Error: " + e.getMessage());
        }
    }
}

In this example code, we first establish a connection to the MySQL database using the DriverManager.getConnection() method. Then, we create a Statement object and execute a SELECT query to retrieve all records from a hypothetical "employees" table. We then process the result set using the next() method and print each record to the console.

Next, we execute an UPDATE statement that sets the age of the employee with ID 1 to 25, and print the number of rows affected. Finally, we create a batch of two INSERT statements and execute them together using the executeBatch() method of the Statement interface. We print the number of statements executed in the batch.

Finally, we close the Statement and Connection objects using their close() methods. If an exception occurs during any of these steps, we print an error message to the console.

The PreparedStatement interface is a subinterface of Statement. It is used to execute the parameterized query.

• public void setInt(int paramIndex, int value): sets the integer value to the given parameter index..

• public void setString(int paramIndex, String value): sets the String value to the given parameter index.

• public void setFloat(int paramIndex, float value): sets the float value to the given parameter index.

• public void setDouble(int paramIndex, double value): sets the double value to the given parameter index.

These methods take two parameters: the index of the parameter (starting at 1), and the value to set for that parameter. They are used in this way:

String sql = "SELECT * FROM my_table WHERE column1 = ? AND column2 = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setString(1, "some_value");
statement.setInt(2, 123);
ResultSet resultSet = statement.executeQuery();

• public int executeUpdate(): executes the query. It is used for create, drop, insert, update, delete etc. (For example, if you execute an UPDATE statement that modifies 10 rows in a table, the executeUpdate() method will return the value 10.)

• public ResultSet executeQuery(): executes the select query. It returns an instance of ResultSet.

The object of ResultSet maintains a cursor pointing to a row of a table. Initially, cursor points to before the first row.

• By default, ResultSet object can be moved forward only and it is not updatable.

• But we can make this object to move forward and backward direction.

I don't understand what it talks about. With simple and vivid language?

When you execute a SQL query using the JDBC API, you typically get a ResultSet object back that represents the results of the query. The ResultSet object maintains a cursor that points to the current row of the result set. By default, this cursor is positioned before the first row, so you need to call the next() method to move it to the first row.

By default, the ResultSet object can only be moved forward, which means that you can only retrieve rows in the order that they were returned by the query. However, it is possible to make the ResultSet object scrollable, which allows you to move the cursor both forward and backward through the rows of the result set.

To make a ResultSet object scrollable, you can pass a second argument to the createStatement() method or the prepareStatement() method when you execute the query. For example, to create a scrollable ResultSet object that can be moved both forward and backward, you can use the following code:

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT * FROM mytable");

In this example, we're creating a Statement object that will return a ResultSet object that is scrollable and insensitive to changes made by other users. We're also setting the concurrency mode to read-only, which means that we can't update the rows of the result set.

Once you have a scrollable ResultSet object, you can move the cursor both forward and backward using the next(), previous(), first(), last(), and absolute() methods. For example, to move the cursor to the last row of the result set, you can use the following code:

rs.last();

Similarly, you can move the cursor to any other row using the absolute() method:

rs.absolute(3);  // moves cursor to the third row of the result set

• public boolean relative(int row): This method moves the cursor to a row that is relative to the current row. The row parameter can be positive or negative, and it specifies the number of rows to move the cursor relative to the current position. If row is positive, the cursor moves forward that number of rows; if row is negative, the cursor moves backward that number of rows. The method returns true if the cursor is positioned on a valid row after the move, and false if there are no more rows in the result set.

• public int getInt(int columnIndex): This method returns the value of the specified column as an int. The columnIndex parameter specifies the index of the column, where the first column has index 1. The method retrieves the value from the current row of the result set.

• public int getInt(String columnName): This method is similar to getInt(int columnIndex), but it retrieves the value of the column based on its name instead of its index.

• public String getString(int columnIndex): This method returns the value of the specified column as a String. The columnIndex parameter specifies the index of the column, where the first column has index 1. The method retrieves the value from the current row of the result set.

• public String getString(String columnName): This method is similar to getString(int columnIndex), but it retrieves the value of the column based on its name instead of its index.

Close the connection object

Connection conn = null;
try {
    // Create a Connection object and use it to execute SQL queries
    conn = DriverManager.getConnection("jdbc:mysql://localhost/mydatabase", "myusername", "mypassword");

    // ... execute SQL queries using Statement and ResultSet objects ...

} catch (SQLException ex) {
    // Handle any exceptions that occur while using the Connection object
} finally {
    // Close the Connection object to release any resources it's holding
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException ex) {
            // Handle any exceptions that occur while closing the Connection object
        }
    }
}

Java Database Connectivity with MySQL

To connect the Java application with the MySQL database, we need the following steps.

1. Driver class: The driver class for the MySQL database is com.mysql.jdbc.Driver.

2. Connection URL: jdbc:mysql://localhost:3306/sonoo where 1) jdbc is the API, 2) mysql is the database, 3) localhost is the MySQL server name, (we may use other IP address), 5) 3306 is the port number and 6) sonoo is the database name.

3. Username: The default username for the mysql database is root.

4. Password: It is the password given by the user at the time of installing the mysql database. In this example, we are going to use the root as the password.

Let’s first create a table in the MySQL database, but before creating a table, we need to create a database first.

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

public class MySQLConnectionExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/sonoo";
        String username = "root";
        String password = "root";

        try {
            Connection conn = DriverManager.getConnection(url, username, password);
            System.out.println("Connected to MySQL database");

            // Execute a SELECT query and retrieve the results
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM customers");
            while (rs.next()) {
                String name = rs.getString("name");
                String email = rs.getString("email");
                int age = rs.getInt("age");
                System.out.println("Name: " + name + ", Email: " + email + ", Age: " + age);
            }

            // Close the resources
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException ex) {
            System.err.println("Failed to connect to MySQL database");
            ex.printStackTrace();
        }
    }
}

How does the rs.getstring() method work?

1. When you call rs.getString("columnName"), the method looks for the value of the specified column (identified by its name) in the current row of the ResultSet. If the column contains a NULL value, the method returns null.

2. If the column contains a non-NULL value, the method retrieves the value as a String. If the column is not of a string type, the method automatically converts the value to a String representation.

3. If the column value is too large to be represented as a String (for example, if it contains a very long text or binary data), the method may throw a SQLException with a message indicating that the data is truncated.

4. If the column value contains special characters that are not valid in a String (for example, if the column value contains a line break or a tab character), the method may replace them with escape sequences or throw a SQLException with a message indicating that the value cannot be represented as a String.

5. The rs.getString(int) method is similar to rs.getString(String) method, except that it takes an integer parameter instead of a column name. The integer parameter represents the index of the column in the ResultSet. 

6. When you call rs.getString(int columnIndex), the method looks for the value of the specified column (identified by its index) in the current row of the ResultSet. If the column contains a NULL value, the method returns null. The index of the first column in a ResultSet is 1, not 0. Therefore, when you call rs.getString(1), you are asking for the value of the first column in the current row of the ResultSet.

Any questions for today's info?