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 _] *
Спасибо за прочтение!