Giới thiệu về JDBC

Java hàng đầu

Tôi vừa công bố khóa học Learn Spring mới , tập trung vào các nguyên tắc cơ bản của Spring 5 và Spring Boot 2:

>> KIỂM TRA KHÓA HỌC

1. Khái quát chung

Trong bài viết này, chúng ta sẽ xem xét JDBC (Java Database Connectivity) là một API để kết nối và thực thi các truy vấn trên cơ sở dữ liệu.

JDBC có thể hoạt động với bất kỳ cơ sở dữ liệu nào miễn là các trình điều khiển phù hợp được cung cấp.

2. Trình điều khiển JDBC

Trình điều khiển JDBC là một triển khai API JDBC được sử dụng để kết nối với một loại cơ sở dữ liệu cụ thể. Có một số loại trình điều khiển JDBC:

  • Loại 1 - chứa ánh xạ tới một API truy cập dữ liệu khác; một ví dụ về điều này là trình điều khiển JDBC-ODBC
  • Loại 2 - là một triển khai sử dụng các thư viện phía máy khách của cơ sở dữ liệu đích; còn được gọi là trình điều khiển API gốc
  • Loại 3 - sử dụng phần mềm trung gian để chuyển đổi các cuộc gọi JDBC thành các cuộc gọi dành riêng cho cơ sở dữ liệu; còn được gọi là trình điều khiển giao thức mạng
  • Loại 4 - kết nối trực tiếp với cơ sở dữ liệu bằng cách chuyển đổi các cuộc gọi JDBC thành các cuộc gọi dành riêng cho cơ sở dữ liệu; được gọi là trình điều khiển giao thức cơ sở dữ liệu hoặc trình điều khiển mỏng,

Loại được sử dụng phổ biến nhất là loại 4, vì nó có ưu điểm là độc lập với nền tảng . Kết nối trực tiếp với máy chủ cơ sở dữ liệu cung cấp hiệu suất tốt hơn so với các loại khác. Nhược điểm của loại trình điều khiển này là nó dành riêng cho cơ sở dữ liệu - do mỗi cơ sở dữ liệu có giao thức cụ thể của riêng nó.

3. Kết nối với Cơ sở dữ liệu

Để kết nối với cơ sở dữ liệu, chúng ta chỉ cần khởi tạo trình điều khiển và mở kết nối cơ sở dữ liệu.

3.1. Đăng ký tài xế

Đối với ví dụ của chúng tôi, chúng tôi sẽ sử dụng trình điều khiển giao thức cơ sở dữ liệu loại 4.

Vì chúng tôi đang sử dụng cơ sở dữ liệu MySQL, chúng tôi cần sự phụ thuộc mysql-connector-java :

 mysql mysql-connector-java 6.0.6 

Tiếp theo, hãy đăng ký trình điều khiển bằng phương thức Class.forName () , phương thức này sẽ tải động lớp trình điều khiển:

Class.forName("com.mysql.cj.jdbc.Driver");

Trong các phiên bản cũ hơn của JDBC, trước khi có kết nối, trước tiên chúng ta phải khởi tạo trình điều khiển JDBC bằng cách gọi phương thức Class.forName . Kể từ JDBC 4.0, tất cả các trình điều khiển được tìm thấy trong classpath đều được tải tự động . Do đó, chúng ta sẽ không cần phần Class.forName này trong môi trường hiện đại.

3.2. Tạo kết nối

Để mở một kết nối, chúng ta có thể sử dụng phương thức getConnection () của lớp DriverManager . Phương thức này yêu cầu tham số Chuỗi URL kết nối :

try (Connection con = DriverManager .getConnection("jdbc:mysql://localhost:3306/myDb", "user1", "pass")) { // use con here }

Kể từ khi kết nối là một AutoCloseable tài nguyên, chúng ta nên sử dụng nó bên trong một thử-với-nguồn khối .

Cú pháp của URL kết nối phụ thuộc vào loại cơ sở dữ liệu được sử dụng. Hãy xem một vài ví dụ:

jdbc:mysql://localhost:3306/myDb?user=user1&password=pass
jdbc:postgresql://localhost/myDb
jdbc:hsqldb:mem:myDb

Để kết nối với cơ sở dữ liệu myDb được chỉ định , chúng tôi sẽ phải tạo cơ sở dữ liệu và người dùng, đồng thời thêm quyền truy cập cần thiết:

CREATE DATABASE myDb; CREATE USER 'user1' IDENTIFIED BY 'pass'; GRANT ALL on myDb.* TO 'user1';

4. Thực thi câu lệnh SQL

Gửi hướng dẫn SQL tới cơ sở dữ liệu, chúng ta có thể sử dụng các phiên bản kiểu Statement , PreparedStatement hoặc CallableStatement, chúng ta có thể lấy được bằng đối tượng Connection .

4.1. Tuyên bố

Các Tuyên bố giao diện chứa các chức năng cần thiết để thực hiện các lệnh SQL.

Đầu tiên, hãy tạo một đối tượng Statement :

try (Statement stmt = con.createStatement()) { // use stmt here }

Một lần nữa, chúng ta nên làm việc với Statement bên trong khối try-with-resources để quản lý tài nguyên tự động.

Dù sao, việc thực thi các lệnh SQL có thể được thực hiện thông qua việc sử dụng ba phương pháp:

  • executeQuery () cho các hướng dẫn CHỌN
  • executeUpdate () để cập nhật dữ liệu hoặc cấu trúc cơ sở dữ liệu
  • execute () có thể được sử dụng cho cả hai trường hợp trên khi kết quả không xác định

Hãy sử dụng phương thức execute () để thêm bảng sinh viên vào cơ sở dữ liệu của chúng tôi:

String tableSql = "CREATE TABLE IF NOT EXISTS employees" + "(emp_id int PRIMARY KEY AUTO_INCREMENT, name varchar(30)," + "position varchar(30), salary double)"; stmt.execute(tableSql);

Khi sử dụng phương thức execute () để cập nhật dữ liệu, phương thức stmt.getUpdateCount () trả về số hàng bị ảnh hưởng.

Nếu kết quả là 0 thì không có hàng nào bị ảnh hưởng hoặc đó là lệnh cập nhật cấu trúc cơ sở dữ liệu.

Nếu giá trị là -1, thì lệnh đó là một truy vấn SELECT; sau đó chúng ta có thể nhận được kết quả bằng cách sử dụng stmt.getResultSet () .

Tiếp theo, hãy thêm một bản ghi vào bảng của chúng tôi bằng cách sử dụng phương thức executeUpdate () :

String insertSql = "INSERT INTO employees(name, position, salary)" + " VALUES('john', 'developer', 2000)"; stmt.executeUpdate(insertSql);

The method returns the number of affected rows for a command that updates rows or 0 for a command that updates the database structure.

We can retrieve the records from the table using the executeQuery() method which returns an object of type ResultSet:

String selectSql = "SELECT * FROM employees"; try (ResultSet resultSet = stmt.executeQuery(selectSql)) { // use resultSet here }

We should make sure to close the ResultSet instances after use. Otherwise, we may keep the underlying cursor open for a much longer period than expected. To do that, it's recommended to use a try-with-resources block, as in our example above.

4.2. PreparedStatement

PreparedStatement objects contain precompiled SQL sequences. They can have one or more parameters denoted by a question mark.

Let's create a PreparedStatement which updates records in the employees table based on given parameters:

String updatePositionSql = "UPDATE employees SET position=? WHERE emp_id=?"; try (PreparedStatement pstmt = con.prepareStatement(updatePositionSql)) { // use pstmt here }

To add parameters to the PreparedStatement, we can use simple setters – setX() – where X is the type of the parameter, and the method arguments are the order and value of the parameter:

pstmt.setString(1, "lead developer"); pstmt.setInt(2, 1);

The statement is executed with one of the same three methods described before: executeQuery(), executeUpdate(), execute() without the SQL String parameter:

int rowsAffected = pstmt.executeUpdate();

4.3. CallableStatement

The CallableStatement interface allows calling stored procedures.

To create a CallableStatement object, we can use the prepareCall() method of Connection:

String preparedSql = "{call insertEmployee(?,?,?,?)}"; try (CallableStatement cstmt = con.prepareCall(preparedSql)) { // use cstmt here }

Setting input parameter values for the stored procedure is done like in the PreparedStatement interface, using setX() methods:

cstmt.setString(2, "ana"); cstmt.setString(3, "tester"); cstmt.setDouble(4, 2000);

If the stored procedure has output parameters, we need to add them using the registerOutParameter() method:

cstmt.registerOutParameter(1, Types.INTEGER);

Then let's execute the statement and retrieve the returned value using a corresponding getX() method:

cstmt.execute(); int new_id = cstmt.getInt(1);

For example to work, we need to create the stored procedure in our MySql database:

delimiter // CREATE PROCEDURE insertEmployee(OUT emp_id int, IN emp_name varchar(30), IN position varchar(30), IN salary double) BEGIN INSERT INTO employees(name, position,salary) VALUES (emp_name,position,salary); SET emp_id = LAST_INSERT_ID(); END // delimiter ;

The insertEmployee procedure above will insert a new record into the employees table using the given parameters and return the id of the new record in the emp_id out parameter.

To be able to run a stored procedure from Java, the connection user needs to have access to the stored procedure's metadata. This can be achieved by granting rights to the user on all stored procedures in all databases:

GRANT ALL ON mysql.proc TO 'user1';

Alternatively, we can open the connection with the property noAccessToProcedureBodies set to true:

con = DriverManager.getConnection( "jdbc:mysql://localhost:3306/myDb?noAccessToProcedureBodies=true", "user1", "pass");

This will inform the JDBC API that the user does not have the rights to read the procedure metadata so that it will create all parameters as INOUT String parameters.

5. Parsing Query Results

After executing a query, the result is represented by a ResultSet object, which has a structure similar to a table, with lines and columns.

5.1. ResultSet Interface

The ResultSet uses the next() method to move to the next line.

Let's first create an Employee class to store our retrieved records:

public class Employee { private int id; private String name; private String position; private double salary; // standard constructor, getters, setters }

Next, let's traverse the ResultSet and create an Employee object for each record:

String selectSql = "SELECT * FROM employees"; try (ResultSet resultSet = stmt.executeQuery(selectSql)) { List employees = new ArrayList(); while (resultSet.next()) { Employee emp = new Employee(); emp.setId(resultSet.getInt("emp_id")); emp.setName(resultSet.getString("name")); emp.setPosition(resultSet.getString("position")); emp.setSalary(resultSet.getDouble("salary")); employees.add(emp); } }

Retrieving the value for each table cell can be done using methods of type getX() where X represents the type of the cell data.

The getX() methods can be used with an int parameter representing the order of the cell, or a String parameter representing the name of the column. The latter option is preferable in case we change the order of the columns in the query.

5.2. Updatable ResultSet

Implicitly, a ResultSet object can only be traversed forward and cannot be modified.

If we want to use the ResultSet to update data and traverse it in both directions, we need to create the Statement object with additional parameters:

stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE );

To navigate this type of ResultSet, we can use one of the methods:

  • first(), last(), beforeFirst(), beforeLast() – to move to the first or last line of a ResultSet or to the line before these
  • next(), previous() – to navigate forward and backward in the ResultSet
  • getRow() – to obtain the current row number
  • moveToInsertRow(), moveToCurrentRow() – to move to a new empty row to insert and back to the current one if on a new row
  • absolute(int row) – to move to the specified row
  • relative(int nrRows) – to move the cursor the given number of rows

Updating the ResultSet can be done using methods with the format updateX() where X is the type of cell data. These methods only update the ResultSet object and not the database tables.

To persist the ResultSet changes to the database, we must further use one of the methods:

  • updateRow() – to persist the changes to the current row to the database
  • insertRow(), deleteRow() – to add a new row or delete the current one from the database
  • refreshRow() – to refresh the ResultSet with any changes in the database
  • cancelRowUpdates() – to cancel changes made to the current row

Let's take a look at an example of using some of these methods by updating data in the employee's table:

try (Statement updatableStmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE)) { try (ResultSet updatableResultSet = updatableStmt.executeQuery(selectSql)) { updatableResultSet.moveToInsertRow(); updatableResultSet.updateString("name", "mark"); updatableResultSet.updateString("position", "analyst"); updatableResultSet.updateDouble("salary", 2000); updatableResultSet.insertRow(); } }

6. Parsing Metadata

The JDBC API allows looking up information about the database, called metadata.

6.1. DatabaseMetadata

The DatabaseMetadata interface can be used to obtain general information about the database such as the tables, stored procedures, or SQL dialect.

Let's have a quick look at how we can retrieve information on the database tables:

DatabaseMetaData dbmd = con.getMetaData(); ResultSet tablesResultSet = dbmd.getTables(null, null, "%", null); while (tablesResultSet.next()) { LOG.info(tablesResultSet.getString("TABLE_NAME")); }

6.2. ResultSetMetadata

This interface can be used to find information about a certain ResultSet, such as the number and name of its columns:

ResultSetMetaData rsmd = rs.getMetaData(); int nrColumns = rsmd.getColumnCount(); IntStream.range(1, nrColumns).forEach(i -> { try { LOG.info(rsmd.getColumnName(i)); } catch (SQLException e) { e.printStackTrace(); } });

7. Handling Transactions

By default, each SQL statement is committed right after it is completed. However, it's also possible to control transactions programmatically.

This may be necessary in cases when we want to preserve data consistency, for example when we only want to commit a transaction if a previous one has completed successfully.

First, we need to set the autoCommit property of Connection to false, then use the commit() and rollback() methods to control the transaction.

Let's add a second update statement for the salary column after the employee position column update and wrap them both in a transaction. This way, the salary will be updated only if the position was successfully updated:

String updatePositionSql = "UPDATE employees SET position=? WHERE emp_id=?"; PreparedStatement pstmt = con.prepareStatement(updatePositionSql); pstmt.setString(1, "lead developer"); pstmt.setInt(2, 1); String updateSalarySql = "UPDATE employees SET salary=? WHERE emp_id=?"; PreparedStatement pstmt2 = con.prepareStatement(updateSalarySql); pstmt.setDouble(1, 3000); pstmt.setInt(2, 1); boolean autoCommit = con.getAutoCommit(); try { con.setAutoCommit(false); pstmt.executeUpdate(); pstmt2.executeUpdate(); con.commit(); } catch (SQLException exc) { con.rollback(); } finally { con.setAutoCommit(autoCommit); }

For the sake of brevity, we omit the try-with-resources blocks here.

8. Closing the Resources

When we're no longer using it, we need to close the connection to release database resources.

We can do this using the close() API:

con.close();

Tuy nhiên, nếu chúng ta đang sử dụng tài nguyên trong khối try-with-resources , chúng ta không cần gọi phương thức close () một cách rõ ràng, vì khối try-with-resources sẽ tự động làm điều đó cho chúng ta.

Điều này cũng đúng với các câu lệnh Statement , PreparedStatement , CallableStatementResultSet .

9. Kết luận

Trong hướng dẫn này, chúng ta đã xem xét những điều cơ bản về cách làm việc với JDBC API.

Như mọi khi, mã nguồn đầy đủ của các ví dụ có thể được tìm thấy trên GitHub.

Java dưới cùng

Tôi vừa công bố khóa học Learn Spring mới , tập trung vào các nguyên tắc cơ bản của Spring 5 và Spring Boot 2:

>> KIỂM TRA KHÓA HỌC