Pengenalan kepada JDBC

Pengaturcaraan
Java Teratas

Saya baru sahaja mengumumkan kursus Learn Spring yang baru , yang berfokus pada asas-asas Spring 5 dan Spring Boot 2:

>> SEMAK KURSUS

1. Gambaran keseluruhan

Dalam artikel ini, kita akan melihat JDBC (Java Database Connectivity) yang merupakan API untuk menghubungkan dan melaksanakan pertanyaan pada pangkalan data.

JDBC boleh berfungsi dengan mana-mana pangkalan data asalkan pemacu yang betul disediakan.

2. Pemacu JDBC

Pemacu JDBC adalah implementasi JDBC API yang digunakan untuk menyambung ke pangkalan data jenis tertentu. Terdapat beberapa jenis pemacu JDBC:

  • Jenis 1 - mengandungi pemetaan ke API akses data lain; contohnya ialah pemacu JDBC-ODBC
  • Jenis 2 - adalah pelaksanaan yang menggunakan perpustakaan sisi klien pangkalan data sasaran; juga dipanggil pemacu API asli
  • Jenis 3 - menggunakan perisian tengah untuk menukar panggilan JDBC menjadi panggilan khusus pangkalan data; juga dikenali sebagai pemacu protokol rangkaian
  • Jenis 4 - sambung terus ke pangkalan data dengan menukar panggilan JDBC menjadi panggilan khusus pangkalan data; dikenali sebagai pemacu protokol pangkalan data atau pemacu nipis,

Jenis yang paling biasa digunakan adalah jenis 4, kerana mempunyai kelebihan untuk tidak bergantung pada platform . Menyambung terus ke pelayan pangkalan data memberikan prestasi yang lebih baik berbanding jenis lain. Kelemahan pemacu jenis ini adalah bahawa pangkalan data itu khusus - memandangkan setiap pangkalan data mempunyai protokol khususnya sendiri.

3. Menyambung ke Pangkalan Data

Untuk menyambung ke pangkalan data, kita hanya perlu menginisialisasi pemacu dan membuka sambungan pangkalan data.

3.1. Mendaftar Pemandu

Sebagai contoh, kami akan menggunakan pemacu protokol pangkalan data jenis 4.

Oleh kerana kami menggunakan pangkalan data MySQL, kami memerlukan pergantungan mysql-connector-java :

 mysql mysql-connector-java 6.0.6

Seterusnya, mari daftarkan pemacu menggunakan kaedah Class.forName () , yang memuat kelas pemacu secara dinamik:

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

Dalam versi lama JDBC, sebelum mendapatkan sambungan, pertama-tama kita harus memulakan pemacu JDBC dengan memanggil kaedah Class.forName . Sehingga JDBC 4.0, semua pemacu yang terdapat di classpath dimuat secara automatik . Oleh itu, kami tidak memerlukan bahagian Class.forName ini dalam persekitaran moden.

3.2. Membuat Sambungan

Untuk membuka sambungan, kita boleh menggunakan getConnection () kaedah DriverManager kelas. Kaedah ini memerlukan parameter String URL sambungan :

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

Oleh kerana Sambungan adalah sumber yang dapat ditutup secara automatik , kita harus menggunakannya dalam blok sumber-sumber .

Sintaks URL sambungan bergantung pada jenis pangkalan data yang digunakan. Mari kita lihat beberapa contoh:

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

Untuk menyambung ke pangkalan data myDb yang ditentukan , kita harus membuat pangkalan data dan pengguna, dan menambahkan akses yang diperlukan:

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

4. Melaksanakan Penyataan SQL

Arahan kirim SQL ke pangkalan data, kita dapat menggunakan contoh jenis Pernyataan , PreparedStatement, atau CallableStatement, yang dapat kita peroleh menggunakan objek Connection .

4.1. Penyataan

The Penyata muka mengandungi fungsi-fungsi penting untuk melaksanakan arahan SQL.

Pertama, mari buat objek Pernyataan :

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

Sekali lagi, kita harus bekerjasama dengan Pernyataan di dalam blok cuba-dengan-sumber untuk pengurusan sumber automatik.

Bagaimanapun, melaksanakan arahan SQL dapat dilakukan melalui penggunaan tiga kaedah:

  • executeQuery () untuk arahan SELECT
  • executeUpdate () untuk mengemas kini data atau struktur pangkalan data
  • execute () dapat digunakan untuk kedua-dua kes di atas apabila hasilnya tidak diketahui

Mari gunakan kaedah execute () untuk menambahkan jadual pelajar ke pangkalan data kami:

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);

Semasa menggunakan kaedah execute () untuk mengemas kini data, maka kaedah stmt.getUpdateCount () mengembalikan bilangan baris yang terpengaruh.

Sekiranya hasilnya adalah 0 maka tidak ada baris yang terpengaruh, atau itu adalah arahan kemas kini struktur pangkalan data.

Sekiranya nilainya adalah -1, maka perintah itu adalah pertanyaan SELECT; kita kemudian dapat memperoleh hasilnya menggunakan stmt.getResultSet () .

Seterusnya, mari tambahkan rekod ke jadual kami menggunakan kaedah executeUpdate () :

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

Kaedah mengembalikan bilangan baris yang terpengaruh untuk perintah yang mengemas kini baris atau 0 untuk perintah yang mengemas kini struktur pangkalan data.

Kita boleh mengambil rekod dari jadual menggunakan kaedah executeQuery () yang mengembalikan objek jenis ResultSet :

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

Kita harus memastikan untuk menutup contoh ResultSet setelah digunakan. Jika tidak, kami mungkin membiarkan kursor yang mendasari terbuka untuk jangka masa yang lebih lama daripada yang dijangkakan. Untuk melakukannya, disarankan untuk menggunakan blok cubaan dengan sumber , seperti dalam contoh kami di atas.

4.2. Penyataan Bersedia

Objek PreparedStatement mengandungi urutan SQL yang dikompilasi. Mereka boleh mempunyai satu atau lebih parameter yang ditandai dengan tanda tanya.

Mari buat PreparedStatement yang mengemas kini rekod dalam jadual pekerja berdasarkan parameter yang diberikan:

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

Untuk menambahkan parameter ke PreparedStatement , kita dapat menggunakan setter sederhana - setX () - di mana X adalah jenis parameter, dan argumen metode adalah urutan dan nilai parameter:

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

Pernyataan tersebut dilaksanakan dengan salah satu dari tiga kaedah yang sama yang dijelaskan sebelumnya: executeQuery (), executeUpdate (), execute () tanpa parameter SQL String :

int rowsAffected = pstmt.executeUpdate();

4.3. Pernyataan Callable

Antara muka CallableStatement membolehkan memanggil prosedur yang disimpan.

Untuk membuat CallableStatement objek, kita boleh menggunakan prepareCall () kaedah Connection :

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

Menetapkan nilai parameter input untuk prosedur yang disimpan dilakukan seperti di antara muka PreparedStatement , menggunakan kaedah setX () :

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

Sekiranya prosedur yang disimpan mempunyai parameter output, kita perlu menambahkannya menggunakan kaedah registerOutParameter () :

cstmt.registerOutParameter(1, Types.INTEGER);

Kemudian mari kita laksanakan penyataan dan dapatkan kembali nilai menggunakan kaedah getX () yang sesuai :

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

Sebagai contoh untuk bekerja, kita perlu membuat prosedur yang tersimpan dalam pangkalan data MySql kami:

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();

Namun, jika kita menggunakan sumber dalam blok try-with-resources , kita tidak perlu memanggil kaedah close () secara eksplisit, kerana blok try-with-resources melakukannya untuk kita secara automatik.

Perkara yang sama adalah benar bagi Penyata s, PreparedStatement s, CallableStatement s, dan ResultSet s.

9. Kesimpulannya

Dalam tutorial ini, kami melihat asas-asas bekerja dengan JDBC API.

Seperti biasa, kod sumber penuh contoh boleh didapati di GitHub.

Bahagian bawah Java

Saya baru sahaja mengumumkan kursus Learn Spring yang baru , yang berfokus pada asas-asas Spring 5 dan Spring Boot 2:

>> SEMAK KURSUS