SmileyVars — это легкий шаблонный движок на основе Java для SQL. Это поможет вам избежать многократного написания подобного SQL, потому что нужны простые варианты.
SmileyVars изначально разрабатывался с интеграцией в Spring JsbcTemplate. Другие интеграции возможны.
Предположим, у нас есть таблица, которая отслеживает содержимое бункеров на складе. Предположим , что контейнеры идентифицируются aisle, levelи bin_number. Запрос для получения информации о содержимом одной корзины может выглядеть так:
SQL
xxxxxxxxxx
1
SELECT item_number, quantity FROM bin_tbl WHERE aisle=:aisle and level=:level and bin_number=:bin
Первое, что вы можете заметить в этом примере, это то, что значение, которое будет подставлено в SQL, обозначается именем с префиксом «:». Если мы предоставим значения aisle=32, level=4и bin=17, это расширится до:
SQL
xxxxxxxxxx
1
SELECT item_number, quantity FROM bin_tbl WHERE aisle=32 and level=4 and bin_number=17
Предположим, что мы хотели бы использовать один и тот же SQL даже в тех случаях, когда мы хотим получить несколько строк. Мы могли бы написать:
SQL
xxxxxxxxxx
1
SELECT item_number, quantity FROM bin_tbl WHERE aisle=:aisle (: and level=:level 🙂 (: and bin_number=:bin 🙂
Вам также могут понравиться:
Современные типовые безопасные шаблоны (часть 1)
То, что мы сделали, это заключили в скобки две части запроса между (:и :). Если часть SQL заключена в квадратные скобки таким образом, если часть в скобках содержит любые переменные : и значения не предоставляются для всех переменных : тогда эта часть SQL не включается в расширение. Если все значения предоставлены для приведенного выше примера, он будет расширен до точно такого же SQL, как и в предыдущем примере. Однако, если мы поставляем только значения aisle=32и bin=17без значения для bin, она расширяется
SQL
xxxxxxxxxx
1
SELECT item_number, quantity FROM bin_tbl WHERE aisle=32 and bin_number=17
Если мы поставляем только aisle=32, это расширяется до:
SQL
xxxxxxxxxx
1
SELECT item_number, quantity FROM bin_tbl WHERE aisle=32
Что если бы мы хотели иметь возможность не указывать aisle? Простое заключение в скобки этой части предложения WHERE не работает :
SQL
xxxxxxxxxx
1
SELECT item_number, quantity FROM bin_tbl WHERE (: aisle=:aisle 🙂 (: and level=:level 🙂 (: and bin_number=:bin 🙂
Если первая часть этого запроса в квадратных скобках отсутствует в расширении, это недопустимый SQL. Существует простой синтаксический прием, который мы можем использовать, чтобы избежать этой проблемы. Мы можем начать WHEREпредложение с 1=1этого:
SQL
xxxxxxxxxx
1
SELECT item_number, quantity FROM bin_tbl WHERE 1=1 (: and aisle=:aisle 🙂 (: and level=:level 🙂 (: and bin_number=:bin 🙂
Эта форма SQL-запроса позволяет нам предоставить все, некоторые или ни одного из значений и расширить его до допустимого SQL-запроса.
Одна вещь , чтобы уведомление об этом запросе , что SELECTсписок не включает aisle, levelили bin_numberстолбцы. Из-за этого, когда мы получаем результаты запроса, мы не знаем, с какими строками бинарных результатов связаны.
Разумный способ решить эту проблему — просто добавить эти столбцы в список выбора следующим образом:
SQL
xxxxxxxxxx
1
SELECT item_number, quantity, aisle, level, bin_number FROM bin_tbl WHERE 1=1 (: and aisle=:aisle 🙂 (: and level=:level 🙂 (: and bin_number=:bin 🙂
Примечание:
Если шаблон содержит более одного :variableмежду (:скобками :), то текст в скобках будет включен в расширении template’s только тогда , когда значения поставляются для всех :variableс.
SmileyVars также полезен для UPDATEбольшей гибкости. Например, если мы хотим обновить то, что находится в определенном месте, мы могли бы написать:
SQL
xxxxxxxxxx
1
UPDATE bin_tbl SET level=level (:, item_number=:item_number :)(:, quantity=:quantity 🙂 WHERE aisle=:aisle AND level=:level AND bin_number=:bin_number
Этот шаблон требует aisle, levelи bin_numberимеет значение , потому что они не внутри (: 🙂 скобки. Это позволяет itemили quantityиметь значения или нет. Если значение itemили quantityне имеет значения, оно не будет обновлено.
level=levelВключается в UPDATEтой же причине мы включаем 1=1в WHEREпунктах. Это не меняет действие команды, но позволяет SmileyVars исключить последующее, не вызывая синтаксических ошибок.
Типы данных
Когда шаблон SmileyVar расширяются, переменные заменяются литералы SQL , такие как 123, 'abc'или DATE '2020-04-28'. Тип литерала, которым заменяется переменная, может зависеть только от типа значения, предоставленного для переменной:
- Значения, которые являются экземплярами
Number, форматируются как числовые литералы SQL - Значения, которые являются
Stringобъектами, форматируются как строковые литералы SQL - Значения, которые являются
Calendarобъектами, форматируются как литералы отметки времени SQL
Например, если rateэто Integerзначение 31, deptэто Stringзначение "nonce"и dayявляется Calendarзначением 18FEB2020 13:43:56EST, то:
SQL
xxxxxxxxxx
1
SELECT * FROM data WHERE 1=1 (: and rate=:rate:)(: and dept=:dept:)(: and day=:day:)
расширяется до:
SQL
xxxxxxxxxx
1
SELECT * FROM data WHERE 1=1 and rate=31 and dept='nonce' and day=TIMESTAMP '2020-2-18 13:43:56-5:0'
В некоторых случаях вы хотите явно указать, какой тип литерала должен быть отформатирован как значение. Например, вы можете захотеть, чтобы Calendarзначение форматировалось как литерал даты (без компонента времени), а не как метка времени. Вы можете указать форматирование, которое вы хотите для переменной, следуя за ней двоеточием (:) и именем такого формата:
SQL
xxxxxxxxxx
1
SELECT * FROM data WHERE 1=1 (:and day=:day:date:)
Если dayэто Calendarзначение, 18FEB2020 13:43:56ESTто приведенный выше пример расширяется до
SQL
xxxxxxxxxx
1
SELECT * FROM data WHERE 1=1 and day=DATE '2020-2-18'
Это поддерживаемые форматы:
| Формат | По умолчанию | Применяется к типам Java | Производит | Включено в шаблон |
|---|---|---|---|---|
| число | да | Number |
числовой литерал | все |
| строка | да | String |
строковый литерал | все |
| отметка времени | да | Date, Calendar,TemporalAccessor |
TIMESTAMP буквальный | все |
| Дата | нет | Date, Calendar,TemporalAccessor |
ДАТА буквальный | все |
| логический | да | Boolean |
логический литерал | PostgreSQL |
Имя формата — это имя, используемое при явном указании формата.
Отображение по умолчанию — да, если формат будет автоматически использоваться в зависимости от типа значения, когда не указан форматер.
Применительно к типам Java показывает типы Java, с которыми может использоваться форматтер. Обратите внимание, что некоторые из этих типов являются абстрактными классами интерфейсов, которые расширены или реализованы многими конкретными классами. Например, Numberпродлевается BigDecimal, Double, Integerи другие классы , которые представляют числовые значения. TemporalAccessorреализуются Instant, LocalDateTime, Yearи другими классами , которые представляют собой точку во время.
Produces — это тип SQL-литерала, который создает средство форматирования.
Включенный в Тип шаблона имеет отношение к функции SmilelyVars, которую мы еще не обсуждали. Когда вы создаете шаблон SmilelyVars, он создается для определенного диалекта SQL, такого как PostgreSQL, Oracle Transact-SQL (Sql Server). Некоторые форматеры включены во все типы шаблонов. Другие средства форматирования предназначены для использования только в одном типе шаблона.
Примечание. Для этой версии SmileyVars не было задано ни одного из указанных диалектных форматов.
Использование SmileyVars
Вы можете использовать SmileyVars в качестве автономного препроцессора для SQL. Однако планируется более удобная интеграция с другими библиотеками. В этом разделе мы покажем вам, как использовать SmileyVars в качестве автономного препроцессора.
Первый шаг — добавление jar-файла SmileyVars в ваш проект. Рекомендуемый способ получить библиотеку — позволить maven или другому инструменту управления зависимостями автоматически загружать ее. Информация о зависимости maven:
XML
xxxxxxxxxx
1
<dependency>
2
<groupId>com.markgrand.smileyVars</groupId>
3
<artifactId>smiley-vars</artifactId>
4
<version>0.2.1-RELEASE</version>
5
</dependency>
Кроме того, вы можете построить его самостоятельно. Загрузите исходный код с https://github.com/mgrand/smileyVars . Вы можете использовать Maven для его сборки с помощью команды:
Джава
xxxxxxxxxx
1
mvn clean install
Использование SmilelyVars в вашем Java-коде очень просто. Есть только два шага:
- Создать шаблон.
- Применить значения к шаблону.
Это иллюстрируется следующим примером кода:
Джава
xxxxxxxxxx
1
import com.markgrand.smileyvars.DatabaseType;import com.markgrand.smileyvars.SmileyVarsTemplate;
2
//...
3
public class SimpleAnsiExample {
4
private static final SmileyVarsTemplate selectTemplate
5
= SmileyVarsTemplate.Template(DatabaseType.ANSI, "SELECT item, quant FROM bin_tbl WHERE 1=1(: and aisle=:aisle:)(: and bin_number=:bin :)");
6
public StorageLocation getLocation(Connection conn, String aisle, Integer bin) throws SQLException {
8
Statement stmt = conn.createStatement();
9
Map<String, Object> map = new HashMap<>();
10
map.put("aisle", aisle);
11
map.put("bin", bin);
12
ResultSet rs = stmt.executeQuery(selectTemplate.apply(map));
13
//...
14
}
15
//...
16
}
Вызов статического метода SmileyVarsTemplate.templateсоздает шаблон с заданным телом для анализа в соответствии с правилами для указанного типа базы данных. Значение DatabaseType.ANSIуказывает общие правила, которые поддерживают функции, общие для большинства реляционных баз данных. Существуют и другие значения для определенного типа реляционной базы данных:
| метод | База данных |
|---|---|
DatabaseType.POSTGRESQL |
PostgreSql |
DatabaseType.ORACLE |
оракул |
DatabaseType.SQL_SERVER |
SQL Server |
Чтобы применить значения к шаблону, вам нужно поместить имена переменных и их значения в карту. Затем передайте карту applyметоду шаблона . Метод apply возвращает расширенное тело шаблона.
Интеграция с PreparedStatement
SmileyVars также можно использовать с PreparedStatementобъектами. Эта интеграция использует класс с именем SmileyVarsPreparedStatement.
Методы SmileyVarsPreparedStatementкласса похожи на PreparedStatementкласс. Вот пример того, как использовать SmileyVarsPreparedStatement:
Джава
xxxxxxxxxx
1
try (SmileyVarsPreparedStatement svps
2
= new SmileyVarsPreparedStatement(h2Connection, "SELECT * FROM square WHERE 1=1 (: AND x=:x:)(: AND y=:y :)")) {
3
svps.setInt("x", 3);
4
svps.setInt("y", 9);
5
ResultSet rs = svps.executeQuery();
6
...
7
}
Чтобы создать SmileyVarsPreparedStatementобъект, вы передаете соединение, которое он будет использовать, и строку, которая будет использоваться в качестве шаблона SmileyVars. Чтобы установить значения SmileyVars, вы вызываете методы «set», аналогичные тем, которые используются в PreparedStatementклассе. Однако эти методы идентифицируют значение имени, которое вы предоставляете, скорее с именем, а не с индексным номером. Методы выполнения запросов одинаковы.
Есть еще один способ, которым использование SmileyVarsPreparedStatementотличается от PreparedStatement. Большинство исключений, которые будут выброшены при вызове метода set для PreparedStatementобъекта, не генерируются в это время. Вместо этого они могут быть выброшены во время выполнения одного из методов execute.
Причина этого заключается в SmileyVarsPreparedStatementиспользовании PreparedStatementобъектов для выполнения запросов. Однако он не знает, что PreparedStatementему нужно, пока не будет вызван метод execute. По этой причине все значения, которые должны быть установлены для PreparedStatementобъекта, устанавливаются непосредственно перед вызовом оператора execute.
Вы можете использовать свободный стиль кодирования для настройки SmileyVarsPreparedStatement. Поскольку все его методы set возвращают SmileyVarsPreparedStatementобъект, вы можете написать приведенный выше пример более кратко, например:
Джава
xxxxxxxxxx
1
try (SmileyVarsPreparedStatement svps
2
= new SmileyVarsPreparedStatement(h2Connection, "SELECT * FROM square WHERE 1=1 (: AND x=:x:)(: AND y=:y :)")) {
3
ResultSet rs = svps.setInt("x", 3).setInt("y", 9).executeQuery();
4
...
5
}
логирование
SmileyVars использует slf4j для своей регистрации. Slf4j интегрируется со всеми популярными библиотеками журналирования (Logback, log4j,…). Вы можете найти документацию для slf4j по адресу https://www.slf4j.org/manual.html .
Дорожная карта
Это список запланированных будущих функций в произвольном порядке:
- Поддержите долларовые котировки для H2
- Поддержка вложенных скобок SmileyVars.
- Встроенная поддержка дополнительных типов данных:
- TimeDuration
- Время
- Деньги
- уникальный идентификатор / GUID
- Интеграция с Spring JdbcTemplate
- Поддержка разбора строк национальных символов.
- Поддержка синтаксического анализа строковых литералов Unicode.
Приложение: Синтаксис SmileyVars
Грамматика EBNF ниже описывает синтаксис smileyVars. Вы также можете просмотреть его как [синтаксис / схема железной дороги] https://gitcdn.link/repo/mgrand/smileyVars/master/documentation/sv-grammar.xhtml ) (созданный с использованием https://www.bottlecaps.de/ rr / ui ).
`` `EBNF / *
- smileyVars грамматика * /
| template_body :: = (sql_text | bracketed_text) * |
| sql_text :: = (other_char | quoted_string | quoted_identifier | комментарий | '(' [^:]) * |
| quoted_string :: = ansi_quoted_string | postgresql_escape_string | postgresql_dollar_string |
| oracle_delimited_string |
| ansi_quoted_string :: = «'» ([^'] | ”'” “'”) * “'” |
| postgresql_escape_string :: = [eE] «'» ([^'] | «„“» | «\» | ”'”) * “'” |
/ * Dollatag на каждом конце должен быть одинаковым / postgresql_dollar_string :: = dollar_tag [^ # x0] dollar_tag
dollar_tag :: = '$' [^ $] * '$'
oracle_delimited_string :: = [Qq] «'» («(« ([^)] | «)» [^']) * «)» | «[» ([^ # X5D] | «]» [^ ']) * «]» | «{« ([^}] | «}» [^ ']) * «}» | «<» ([^>] | «>» [^ ']) * «>» | delimiter_char [^ # x0] * delimiter_char) «'» / * Оба вхождения delimiter_char должны быть одинаковыми символами * /
| quoted_identifier :: = '”' ([^”] | '”' '”') * '”' |
other_char :: = [^ '”(]
| комментарий :: = line_comment | block_comment |
line_comment :: = «-» [^ # x0a # x0d] * [# x0a # x0d]
/ * Они должны иметь возможность вложения как поддерживается для PostgreSQL, SQLServer и DB2 / block_comment :: = «/ » ([^ ] | ' ' [^ /]) * «* /»
| bracketed_text :: = «(:» (bracketed_char | quoted_string | quoted_identifier |
| комментарий | («:» Var («:» type)?)) «:)» |
bracketed_char :: = [^ '”:]
var :: = [A-Za-z] [A-Za-z0-9 _] *
тип :: = [$ A-Za-z] [$ A-Za-z0-9 _] *
Спасибо за прочтение!