Een lijst met waarden gebruiken in een JdbcTemplate IN-clausule

1. Inleiding

In een SQL-instructie kunnen we de IN-operator gebruiken om te testen of een uitdrukking overeenkomt met een waarde in een lijst. Daarom kunnen we de IN-operator gebruiken in plaats van meerdere OF-voorwaarden.

In deze zelfstudie laten we zien hoe u een lijst met waarden kunt doorgeven aan de IN-clausule van een Spring JDBC-sjabloonquery.

2. Het passeren van een Lijst Parameter naar IN Clausule

Met de IN-operator kunnen we meerdere waarden specificeren in een WHERE-component. We kunnen het bijvoorbeeld gebruiken om alle werknemers te vinden wiens ID in een gespecificeerde ID-lijst staat:

SELECTEER * VAN WERKNEMER WAAR ID IN (1, 2, 3)

Doorgaans is het totale aantal waarden in de IN-clausule variabel. Daarom moeten we een tijdelijke aanduiding maken die een dynamische zoeklijst kan ondersteunen.

2.1. Met JdbcTemplate

Met JdbcTemplate, we kunnen gebruiken '?' tekens als tijdelijke aanduidingen voor de zoeklijst. Het aantal '?' tekens zullen hetzelfde zijn als de grootte van de lijst:

Lijst getEmployeesFromIdList (Lijst-id's) {String inSql = String.join (",", Collections.nCopies (ids.size (), "?")); Lijst werknemers = jdbcTemplate.query (String.format ("SELECT * VAN WERKNEMER WAAR id IN (% s)", inSql), ids.toArray (), (rs, rowNum) -> nieuwe werknemer (rs.getInt ("id "), rs.getString (" voornaam "), rs.getString (" achternaam "))); werknemers teruggeven; } 

Bij deze methode genereren we eerst een tijdelijke aanduidingstring die bevat ids.size () ‘? ' tekens gescheiden door komma's. Vervolgens plaatsen we deze string in de IN-clausule van onze SQL-instructie. Als we bijvoorbeeld drie cijfers hebben in de id's list, de SQL-instructie is:

SELECTEER * VAN WERKNEMER WAAR ID IN (?,?,?)

In de vraag methode passeren we de id's list als een parameter die overeenkomt met de tijdelijke aanduidingen in de IN-clausule. Op deze manier kunnen we een dynamische SQL-instructie uitvoeren op basis van de ingevoerde zoeklijst.

2.2. Met NamedParameterJdbcTemplate

Een andere manier om met de dynamische zoeklijst om te gaan, is door te gebruiken NamedParameterJdbcTemplate. We kunnen bijvoorbeeld direct een benoemde parameter maken voor de invoerlijst:

Lijst getEmployeesFromIdListNamed (Lijst-id's) {SqlParameterSource-parameters = nieuwe MapSqlParameterSource ("ids", ids); Lijst medewerkers = namedJdbcTemplate.query ("SELECT * FROM WERKNEMER WAAR id IN (: ids)", parameters, (rs, rowNum) -> nieuwe medewerker (rs.getInt ("id"), rs.getString ("voornaam") , rs.getString ("achternaam"))); werknemers teruggeven; }

Bij deze methode construeren we eerst een MapSqlParameterSource object dat de invoer-id-lijst bevat. Vervolgens gebruiken we slechts één benoemde parameter om de dynamische zoeklijst weer te geven.

Onder de motorkap, NamedParameterJdbcTemplate vervangt de benoemde parameters voor de ‘? ' tijdelijke aanduidingen en toepassingen JdbcTemplate om de vraag uit te voeren.

3. Omgaan met een grote Lijst

Als we een groot aantal waarden in een lijst hebben, moeten we alternatieve manieren overwegen om ze in het JdbcTemplate vraag.

De Oracle-database ondersteunt bijvoorbeeld niet meer dan 1000 literalen in een IN-clausule.

Een manier om dat te doen is door maak een tijdelijke tabel voor de lijst. Verschillende databases kunnen echter verschillende manieren hebben om tijdelijke tabellen te maken. We kunnen bijvoorbeeld de MAAK EEN WERELDWIJDE TIJDELIJKE TABEL instructie om een ​​tijdelijke tabel in de Oracle-database te maken.

Laten we een tijdelijke tabel maken voor de H2-database:

Lijst getEmployeesFromLargeIdList (Lijst-id's) {jdbcTemplate.execute ("CREATE TEMPORARY TABLE IF NOT EXISTS employee_tmp (id INT NOT NULL)"); List employeeIds = nieuwe ArrayList (); voor (Geheel getal id: ids) {employeeIds.add (nieuw object [] {id}); } jdbcTemplate.batchUpdate ("INVOEGEN IN employee_tmp WAARDEN (?)", employeeIds); Lijst werknemers = jdbcTemplate.query ("SELECTEER * VAN WERKNEMER WAAR id IN (SELECTEER id VAN werknemer_tmp)", (rs, rowNum) -> nieuwe werknemer (rs.getInt ("id"), rs.getString ("voornaam") , rs.getString ("achternaam"))); jdbcTemplate.update ("VERWIJDEREN VAN werknemer_tmp"); werknemers teruggeven; }

Hier maken we eerst een tijdelijke tabel om alle waarden van de invoerlijst te bevatten. Vervolgens voegen we de waarden van de invoerlijst in deze tabel in.

In onze resulterende SQL-instructie, de waarden in de IN-clausule zijn afkomstig uit de tijdelijke tabel, en we hebben het construeren van een IN-clausule met een groot aantal tijdelijke aanduidingen vermeden.

Ten slotte, nadat we de query hebben voltooid, schonen we de tijdelijke tabel op voor toekomstig hergebruik.

4. Conclusie

In deze zelfstudie hebben we laten zien hoe u JdbcTemplate en NamedParameterJdbcTemplate om een ​​zoeklijst door te geven voor de IN-clausule van een SQL-query. We hebben ook een alternatieve manier geboden om een ​​groot aantal lijstwaarden te verwerken door een tijdelijke tabel te gebruiken.

Zoals altijd is de broncode voor het artikel beschikbaar op GitHub.