Използване на списък със стойности в клауза JdbcTemplate IN

1. Въведение

В оператор SQL можем да използваме оператора IN, за да проверим дали даден израз съответства на някаква стойност в списък. Следователно можем да използваме оператора IN вместо множество ИЛИ условия.

В този урок ще покажем как да предадете списък със стойности в клаузата IN на заявка за шаблон JDBC Spring.

2. Предаване на параметър на списък в клауза IN

Операторът IN ни позволява да зададем множество стойности в клауза WHERE. Например можем да го използваме, за да намерим всички служители, чийто идентификатор е в определен списък с идентификатори:

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

Обикновено общият брой стойности в клаузата IN е променлив. Следователно трябва да създадем заместител, който да поддържа динамичен списък със стойности.

2.1. С JdbcTemplate

С JdbcTemplate можем да използваме „?“ символи като заместители за списъка със стойности. Броят на '?' знаците ще бъдат същите като размера на списъка:

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

При този метод първо генерираме низ за контейнер, който съдържа ids.size () '?' знаци, разделени със запетаи. След това поставяме този низ в клаузата IN на нашия SQL израз. Например, ако имаме три числа в списъка с идентификатори , SQL изразът е:

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

В метода на заявката ние предаваме списъка с идентификатори като параметър, за да съответства на заместителите в клаузата IN. По този начин можем да изпълним динамичен SQL оператор въз основа на входния списък със стойности.

2.2. С NamedParameterJdbcTemplate

Друг начин за обработка на динамичния списък със стойности е използването на NamedParameterJdbcTemplate . Например, можем директно да създадем именен параметър за входния списък:

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

При този метод първо конструираме обект MapSqlParameterSource, който съдържа списъка с входни идентификатори. След това използваме само един именен параметър за представяне на динамичния списък със стойности.

Под капака NamedParameterJdbcTemplate замества посочените параметри за '?' заместители и използва JdbcTemplate за изпълнение на заявката.

3. Работа с голям списък

Когато имаме голям брой стойности в списък, трябва да обмислим алтернативни начини за предаването им в заявката JdbcTemplate .

Например базата данни Oracle не поддържа повече от 1000 литерала в клауза IN.

Един от начините да направите това е да създадете временна таблица за списъка . Различните бази данни обаче могат да имат различни начини за създаване на временни таблици. Например можем да използваме израза CREATE GLOBAL TEMPORARY TABLE , за да създадем временна таблица в базата данни на Oracle.

Нека създадем временна таблица за базата данни H2:

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

Тук първо създаваме временна таблица, която да съхранява всички стойности на входния списък. След това вмъкваме стойностите на входния списък в тази таблица.

В получения от нас израз на SQL стойностите в клаузата IN са от временната таблица и избягвахме да изграждаме клауза IN с голям брой заместители.

И накрая, след като завършим заявката, почистваме временната таблица за бъдеща повторна употреба.

4. Заключение

В този урок показахме как да използваме JdbcTemplate и NamedParameterJdbcTemplate за предаване на списък със стойности за клаузата IN на SQL заявка. Също така осигурихме алтернативен начин за обработка на голям брой стойности от списъка чрез използване на временна таблица.

Както винаги, изходният код на статията е достъпен в GitHub.