Einführung in JDBC

Java Top

Ich habe gerade den neuen Learn Spring- Kurs angekündigt , der sich auf die Grundlagen von Spring 5 und Spring Boot 2 konzentriert:

>> Überprüfen Sie den Kurs

1. Übersicht

In diesem Artikel werfen wir einen Blick auf JDBC (Java Database Connectivity), eine API zum Verbinden und Ausführen von Abfragen in einer Datenbank.

JDBC kann mit jeder Datenbank arbeiten, solange die richtigen Treiber bereitgestellt werden.

2. JDBC-Treiber

Ein JDBC-Treiber ist eine JDBC-API-Implementierung, die zum Herstellen einer Verbindung zu einem bestimmten Datenbanktyp verwendet wird. Es gibt verschiedene Arten von JDBC-Treibern:

  • Typ 1 - enthält eine Zuordnung zu einer anderen Datenzugriffs-API; Ein Beispiel hierfür ist der JDBC-ODBC-Treiber
  • Typ 2 - ist eine Implementierung, die clientseitige Bibliotheken der Zieldatenbank verwendet. wird auch als nativer API-Treiber bezeichnet
  • Typ 3 - verwendet Middleware, um JDBC-Aufrufe in datenbankspezifische Aufrufe umzuwandeln. wird auch als Netzwerkprotokolltreiber bezeichnet
  • Typ 4 - Stellen Sie eine direkte Verbindung zu einer Datenbank her, indem Sie JDBC-Aufrufe in datenbankspezifische Aufrufe konvertieren. bekannt als Datenbankprotokolltreiber oder Thin-Treiber,

Der am häufigsten verwendete Typ ist Typ 4, da er den Vorteil hat, plattformunabhängig zu sein . Die direkte Verbindung mit einem Datenbankserver bietet im Vergleich zu anderen Typen eine bessere Leistung. Der Nachteil dieses Treibertyps ist, dass er datenbankspezifisch ist - vorausgesetzt, jede Datenbank verfügt über ein eigenes spezifisches Protokoll.

3. Herstellen einer Verbindung zu einer Datenbank

Um eine Verbindung zu einer Datenbank herzustellen, müssen Sie lediglich den Treiber initialisieren und eine Datenbankverbindung herstellen.

3.1. Registrieren des Treibers

In unserem Beispiel verwenden wir einen Datenbankprotokolltreiber vom Typ 4.

Da wir eine MySQL-Datenbank verwenden, benötigen wir die Abhängigkeit mysql-connector-java :

 mysql mysql-connector-java 6.0.6 

Als Nächstes registrieren wir den Treiber mithilfe der Class.forName () -Methode, mit der die Treiberklasse dynamisch geladen wird :

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

In älteren JDBC-Versionen mussten wir vor dem Herstellen einer Verbindung zunächst den JDBC-Treiber durch Aufrufen der Class.forName- Methode initialisieren . Ab JDBC 4.0 werden alle Treiber, die im Klassenpfad gefunden werden, automatisch geladen . Daher benötigen wir diesen Class.forName- Teil in modernen Umgebungen nicht.

3.2. Verbindung herstellen

Um eine Verbindung herzustellen, können wir die Methode getConnection () der DriverManager- Klasse verwenden. Diese Methode erfordert einen Verbindungs-URL- String- Parameter:

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

Da es sich bei der Verbindung um eine automatisch abschließbare Ressource handelt, sollten wir sie in einem Try-with-Resources- Block verwenden .

Die Syntax der Verbindungs-URL hängt vom verwendeten Datenbanktyp ab. Schauen wir uns einige Beispiele an:

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

Um eine Verbindung zur angegebenen myDb- Datenbank herzustellen , müssen wir die Datenbank und einen Benutzer erstellen und den erforderlichen Zugriff hinzufügen:

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

4. Ausführen von SQL-Anweisungen

Um SQL-Anweisungen an die Datenbank zu senden, können wir Instanzen vom Typ Statement , PreparedStatement oder CallableStatement verwenden, die wir über das Connection- Objekt erhalten können.

4.1. Erklärung

Die Anweisungsschnittstelle enthält die wesentlichen Funktionen zum Ausführen von SQL-Befehlen.

Zuerst erstellen wir ein Anweisungsobjekt :

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

Auch hier sollten wir mit Anweisungen in einem Try-with-Resources- Block für die automatische Ressourcenverwaltung arbeiten.

Das Ausführen von SQL-Anweisungen kann auf drei Arten erfolgen:

  • executeQuery () für SELECT-Anweisungen
  • executeUpdate () zum Aktualisieren der Daten oder der Datenbankstruktur
  • execute () kann für beide oben genannten Fälle verwendet werden, wenn das Ergebnis unbekannt ist

Verwenden Sie die Methode execute () , um unserer Datenbank eine Schülertabelle hinzuzufügen :

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

Bei Verwendung der execute () Methode , um die Daten zu aktualisieren, dann der stmt.getUpdateCount () kehrt Methode die Anzahl der Zeilen betroffen.

Wenn das Ergebnis 0 ist, waren entweder keine Zeilen betroffen oder es handelte sich um einen Befehl zum Aktualisieren der Datenbankstruktur.

Wenn der Wert -1 ist, war der Befehl eine SELECT-Abfrage. Wir können das Ergebnis dann mit stmt.getResultSet () erhalten .

Als Nächstes fügen wir unserer Tabelle mit der Methode executeUpdate () einen Datensatz hinzu :

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

Die Methode gibt die Anzahl der betroffenen Zeilen für einen Befehl zurück, der Zeilen aktualisiert, oder 0 für einen Befehl, der die Datenbankstruktur aktualisiert.

Wir können die Datensätze mit der Methode executeQuery () aus der Tabelle abrufen , die ein Objekt vom Typ ResultSet zurückgibt :

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

Wir sollten sicherstellen, dass die ResultSet- Instanzen nach der Verwendung geschlossen werden. Andernfalls können wir den zugrunde liegenden Cursor viel länger als erwartet offen halten. Zu diesem Zweck wird empfohlen, einen Try-with-Resources- Block zu verwenden, wie in unserem obigen Beispiel.

4.2. PreparedStatement

PreparedStatement- Objekte enthalten vorkompilierte SQL-Sequenzen. Sie können einen oder mehrere Parameter haben, die durch ein Fragezeichen gekennzeichnet sind.

Erstellen wir ein PreparedStatement, das die Datensätze in der Mitarbeitertabelle basierend auf den angegebenen Parametern aktualisiert :

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

Um dem PreparedStatement Parameter hinzuzufügen , können wir einfache Setter verwenden - setX () - wobei X der Typ des Parameters ist und die Methodenargumente die Reihenfolge und den Wert des Parameters sind:

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

Die Anweisung wird mit einer der drei zuvor beschriebenen Methoden ausgeführt: executeQuery (), executeUpdate (), execute () ohne den SQL String- Parameter:

int rowsAffected = pstmt.executeUpdate();

4.3. CallableStatement

Die CallableStatement- Schnittstelle ermöglicht das Aufrufen gespeicherter Prozeduren.

Um ein CallableStatement- Objekt zu erstellen , können Sie die Methode prepareCall () von Connection verwenden :

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

Das Festlegen von Eingabeparameterwerten für die gespeicherte Prozedur erfolgt wie in der PreparedStatement- Schnittstelle mithilfe der setX () -Methoden:

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

Wenn die gespeicherte Prozedur Ausgabeparameter enthält, müssen diese mit der Methode registerOutParameter () hinzugefügt werden:

cstmt.registerOutParameter(1, Types.INTEGER);

Führen Sie dann die Anweisung aus und rufen Sie den zurückgegebenen Wert mit einer entsprechenden getX () -Methode ab:

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

Zum Arbeiten müssen wir beispielsweise die gespeicherte Prozedur in unserer MySQL-Datenbank erstellen:

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

Wenn wir die Ressource jedoch in einem Try-with-Resources- Block verwenden, müssen wir die close () -Methode nicht explizit aufrufen , da der Try-with-Resources- Block dies automatisch für uns erledigt.

Gleiches gilt für die Anweisungen s, PreparedStatement s, CallableStatement s und ResultSet s.

9. Fazit

In diesem Tutorial haben wir uns mit den Grundlagen der Arbeit mit der JDBC-API befasst.

Wie immer finden Sie den vollständigen Quellcode der Beispiele auf GitHub.

Java unten

Ich habe gerade den neuen Learn Spring- Kurs angekündigt , der sich auf die Grundlagen von Spring 5 und Spring Boot 2 konzentriert:

>> Überprüfen Sie den Kurs