Verwenden einer Werteliste in einer JdbcTemplate IN-Klausel

1. Einleitung

In einer SQL-Anweisung können wir den IN-Operator verwenden, um zu testen, ob ein Ausdruck mit einem Wert in einer Liste übereinstimmt. Daher können wir den IN-Operator anstelle mehrerer ODER-Bedingungen verwenden.

In diesem Lernprogramm wird gezeigt, wie eine Werteliste an die IN-Klausel einer Spring JDBC-Vorlagenabfrage übergeben wird.

2. Übergeben eines Liste Parameter zu IN - Klausel

Mit dem IN-Operator können wir mehrere Werte in einer WHERE-Klausel angeben. Zum Beispiel können wir damit alle Mitarbeiter finden, deren ID in einer angegebenen ID-Liste enthalten ist:

SELECT * FROM EMPLOYEE WHERE id IN (1, 2, 3)

In der Regel ist die Gesamtzahl der Werte in der IN-Klausel variabel. Daher müssen wir einen Platzhalter erstellen, der eine dynamische Liste von Werten unterstützen kann.

2.1. Mit JdbcTemplate

Mit JdbcTemplate können wir '?' Zeichen als Platzhalter für die Werteliste. Die Anzahl der '?' Die Zeichen entsprechen der Größe der Liste:

List getEmployeesFromIdList(List ids) { String inSql = String.join(",", Collections.nCopies(ids.size(), "?")); List employees = jdbcTemplate.query( String.format("SELECT * FROM EMPLOYEE WHERE id IN (%s)", inSql), ids.toArray(), (rs, rowNum) -> new Employee(rs.getInt("id"), rs.getString("first_name"), rs.getString("last_name"))); return employees; } 

Bei dieser Methode generieren wir zuerst eine Platzhalterzeichenfolge, die ids.size () '?' Enthält. durch Kommas getrennte Zeichen. Dann fügen wir diese Zeichenfolge in die IN-Klausel unserer SQL-Anweisung ein. Wenn die IDs- Liste beispielsweise drei Zahlen enthält , lautet die SQL-Anweisung:

SELECT * FROM EMPLOYEE WHERE id IN (?,?,?)

Bei der Abfragemethode übergeben wir die IDs- Liste als Parameter, der mit den Platzhaltern in der IN-Klausel übereinstimmt. Auf diese Weise können wir eine dynamische SQL-Anweisung basierend auf der Eingabeliste der Werte ausführen.

2.2. Mit NamedParameterJdbcTemplate

Eine andere Möglichkeit, mit der dynamischen Liste von Werten umzugehen, ist die Verwendung von NamedParameterJdbcTemplate . Zum Beispiel können wir direkt einen benannten Parameter für die Eingabeliste erstellen:

List getEmployeesFromIdListNamed(List ids) { SqlParameterSource parameters = new MapSqlParameterSource("ids", ids); List employees = namedJdbcTemplate.query( "SELECT * FROM EMPLOYEE WHERE id IN (:ids)", parameters, (rs, rowNum) -> new Employee(rs.getInt("id"), rs.getString("first_name"), rs.getString("last_name"))); return employees; }

In dieser Methode erstellen wir zuerst ein MapSqlParameterSource- Objekt, das die Eingabe-ID-Liste enthält. Dann verwenden wir nur einen benannten Parameter, um die dynamische Liste der Werte darzustellen.

Unter der Haube ersetzt NamedParameterJdbcTemplate die benannten Parameter für das '?' Platzhalter und verwendet JdbcTemplate , um die Abfrage auszuführen.

3. Umgang mit einer großen Liste

Wenn eine Liste eine große Anzahl von Werten enthält, sollten wir alternative Möglichkeiten in Betracht ziehen, diese an die JdbcTemplate- Abfrage zu übergeben.

Beispielsweise unterstützt die Oracle-Datenbank nicht mehr als 1.000 Literale in einer IN-Klausel.

Eine Möglichkeit, dies zu tun, besteht darin , eine temporäre Tabelle für die Liste zu erstellen . Unterschiedliche Datenbanken können jedoch unterschiedliche Möglichkeiten zum Erstellen temporärer Tabellen haben. Beispielsweise können wir die Anweisung CREATE GLOBAL TEMPORARY TABLE verwenden , um eine temporäre Tabelle in der Oracle-Datenbank zu erstellen.

Erstellen wir eine temporäre Tabelle für die H2-Datenbank:

List getEmployeesFromLargeIdList(List ids) { jdbcTemplate.execute("CREATE TEMPORARY TABLE IF NOT EXISTS employee_tmp (id INT NOT NULL)"); List employeeIds = new ArrayList(); for (Integer id : ids) { employeeIds.add(new Object[] { id }); } jdbcTemplate.batchUpdate("INSERT INTO employee_tmp VALUES(?)", employeeIds); List employees = jdbcTemplate.query( "SELECT * FROM EMPLOYEE WHERE id IN (SELECT id FROM employee_tmp)", (rs, rowNum) -> new Employee(rs.getInt("id"), rs.getString("first_name"), rs.getString("last_name"))); jdbcTemplate.update("DELETE FROM employee_tmp"); return employees; }

Hier erstellen wir zunächst eine temporäre Tabelle, die alle Werte der Eingabeliste enthält. Dann fügen wir die Werte der Eingabeliste in diese Tabelle ein.

In unserer resultierenden SQL-Anweisung stammen die Werte in der IN-Klausel aus der temporären Tabelle , und wir haben vermieden, eine IN-Klausel mit einer großen Anzahl von Platzhaltern zu erstellen.

Nachdem wir die Abfrage abgeschlossen haben, bereinigen wir die temporäre Tabelle für die zukünftige Wiederverwendung.

4. Fazit

In diesem Tutorial haben wir gezeigt, wie JdbcTemplate und NamedParameterJdbcTemplate verwendet werden , um eine Liste von Werten für die IN-Klausel einer SQL-Abfrage zu übergeben. Außerdem haben wir eine alternative Möglichkeit bereitgestellt, eine große Anzahl von Listenwerten mithilfe einer temporären Tabelle zu verarbeiten.

Wie immer ist der Quellcode für den Artikel auf GitHub verfügbar.