Что такое шаблоны в sql

SQL Query Templates

Query Templates allow you to define reusable snippets of SQL components to be used across different reports. This is useful when you have a snippet of code that gets repeated on multiple reports.

A query template is defined by 3 components:

• name : name of template to be used in SQL reports
• variables : dynamic parameters inside the template
• body : content of the template

Example — Abstracting repeated components of your SQL#

Say you have a report that lists all jobs, together with their statuses (stored as int in the database).

You can see that the CASE WHEN J.status is something that will appear again in other reports with Status column. So we can define a << @job_status(status) >> query template as follows:

Then rewrite your report's SQL as:

Now, whenever your report runs, the query template will automatically be replace with the actual template's content. This is just like programming language's functions inside SQL itself.

Adding variables to Query Templates#

Using variables inside query templates allows you to accept additional parameters, when your query template runs inside a report.

The basic query template with variable structure is template(variable1, variable2) . In this example, the query template age_groups accepts two variables that were added, field and range . So this query template with variable would operate as age_groups(field, range) when called, and pass the respective input values into the query template.

Here you can see it in action in a report. In this report, the field variable has accepted a line of SQL code EXTRACT(year from age. ) , while range accepted a shared filter input called age_groups that was added to the report.

Для Дизайна Сайта

При условии, что в отношениях со второй половинкой имеются проблемы, предложите организовать анальный секс с проституткой. Формат свидания может быть разнообразным. Предложения от возбуждающей проститутки тут http://vse-prostitutki-krasnoyarska.info/serv/glubokij-minet/.|Временами молодым людям приходится кайфонуть трахом с безупречно престижными шлюхами. Они соблазнительные шлюхи предлагают вам воспользоваться своими услугами голого характера.

Навигация

Поиск по Сайту

Партнеры

Самоучитель. Книги.

2.14: SQL-шаблоны.

MySQL обеспечивает стандартные шаблоны SQL, основанные на расширенных регулярных выражениях, подобных используемым Unix-приложениями, вроде vi, grep и sed.

SQL-шаблоны позволяют вам использовать символ _, который соответствует любому одиночному символу (но только одному!), или символ %, который соответствует произвольному числу символов (включая нулевое количество). В MySQL SQL-шаблоны по умолчанию нечувствительны к регистру. Некоторые примеры показываются ниже. Обратите внимание, что вы не используете = или !=, когда применяете образцы SQL; используйте вместо них операторы сравнения LIKE или NOT LIKE.

Чтобы найти имена, начинающиеся с буквы b, введите:

Чтобы найти имена, заканчивающиеся на сочетание fy, введите:

Чтобы найти имена, содержащие букву w, введите:

Чтобы найти имена, состоящие из пяти символов, используйте набор символов _:

Другой тип образца, поддерживаемый MySQL, это расширенные регулярные выражения. Когда вы проверяете соответствия для этого типа образца, используйте операторы REGEXP и NOT REGEXP (или RLIKE и NOT RLIKE, которые являются синонимами) вместо уже знакомых LIKE и NOT LIKE. Некоторые правила задания расширенных регулярных выражений:

• точка (.) соответствует любому одиночному символу;
• символьный класс представляет собой набор символов, который обрабатывается при сравнении как один символ. Символьный класс [. ] соответствует любому символу внутри скобок. Например, [abc] соответствует а или b или с. Чтобы задать диапазон символов, используйте черточку. Например, [a-z] соответствует любому латинскому символу нижнего регистра, в то время как [0-9] определяет любую цифру;
• звездочка * соответствует нулю или большему количеству образцов, предшествующих ей. Например, х* соответствует любому числу символов х, [0-9]* соответствует любому количеству цифр, а.* соответствует любому количеству чего-нибудь;
• регулярные выражения чувствительны к регистру, но вы можете использовать такой символьный класс, чтобы он соответствовал обоим регистрам сразу. Например, [аА] соответствует символу а нижнего или верхнего регистра, a [a-zA-Z] соответствует любому латинскому символу;
• образец в MySQL считается соответствующим, если соответствие происходит где-нибудь в проверяемом значении. Классический SQL учитывает соответствие, только если оно удовлетворяет всему значению в целом;
• чтобы закреплять образец так, чтобы он соответствовал строго началу или концу проверяемого значения, используется символ циркумфлекса (^) в начале или доллара ($) в конце образца.

Чтобы проиллюстрировать, как выполняется работа регулярных выражений, показанных выше, запросы LIKE переделаны ниже так, чтобы использовать их с REGEXP.

Чтобы находить имена, начинающиеся на b, примените ^ для соответствия началу имени:

До MySQL версии 3.23.4 REGEXP чувствителен к регистру, и предыдущий запрос не возвратит никаких строк. Чтобы соответствовать нижнему или верхнему регистру b, используйте взамен этот запрос:

mysql> SELECT * FROM pet WHERE паше REGEXP «^[bВ]»;

В MySQL 3.23.4, чтобы вынудить сравнение REGEXP стать чувствительным к регистру, используют ключевое слово BINARY, чтобы сделать одну из строк двоичной (в этом случае MySQL начинает сравнивать строки с учетом регистра посимвольно). Этот запрос будет соответствовать только нижнему регистру буквы b строго в начале имени:

mysql> SELECT * FROM pet WHERE name REGEXP BINARY «^b»;

Чтобы находить имена, заканчивающиеся на fy, примените $ для соответствия концу имени:

Чтобы находить имена, содержащие w, неважно в каком регистре, используйте этот запрос:

mysql> SELECT * FROM pet WHERE name REGEXP «w»;
+———-+———+———+——+————+————+
| name | owner | species | sex | birth | death |
+———-+———+———+——+————+————+
| Claws | Gwen | кошка | m | 1994-03-17 | NULL |
| Bowser | Diane | собака | m | 1998-08-31 | 1995-07-29 |
| Whistler | Gwen | птичка | NULL | 1997-12-09 | NULL |
+———-+———+———+——+————+————+

Потому что регулярное выражение срабатывает, если соответствие происходит где-нибудь в значении, в предыдущем запросе необязательно размещать групповой символ с обеих сторон образца, чтобы соответствовать всему значению. Хотя именно так пришлось бы поступить в стандартном SQL.

Чтобы находить имена, содержащие точно пять символов, используйте ^ и $, чтобы соответствовать началу, концу имени и пяти образцам точки . между ними:

Вы могли бы также переписать предыдущий запрос, используя еще одну хитрость. Если после образца символа для соответствия поставить число n в фигурных скобках, то образец будет повторен п раз:

SQL templates¶

Doma supports SQL templates, called “two-way SQL”. “Two-way SQL” means that the SQL templates can be used in two ways:

• To build dynamic SQL statements from the templates.
• To execute the templates in SQL tools as they are.

Every SQL template must correspond to a DAO method. For example, suppose you have the pair of an SQL template and a DAO method as follows:

The employeeId expression enclosed between /* and */ corresponds to the method parameter “employeeId” of the DAO. In runtime, the SQL comment and following number /* employeeId */99 is replaced with a bind variable ? and the method parameter “employeeId” is passed to the variable. The SQL statement generated from the SQL template is as follows:

The number 99 in the SQL template is test data and never used in runtime. The test data is only useful when you execute the SQL template as is. In other words, you can check whether the SQL template is grammatically correct with your favorite SQL tools.

Each SQL template is represented either a text file or an annotation.

SQL templates in files¶

You can specify SQL templates in text files:

Above selectById and deleteByName methods are mapped onto their own SQL files. DAO methods must be annotated with one of following annotations:

• @Select
• @Insert(sqlFile = true)
• @Update(sqlFile = true)
• @Delete(sqlFile = true)
• @BatchInsert(sqlFile = true)
• @BatchUpdate(sqlFile = true)
• @BatchDelete(sqlFile = true)

Encoding¶

The SQL files must be saved as UTF-8 encoded.

Location¶

The SQL files must be located in directories below a “META-INF” directory which is included in CLASSPATH.

Format of file path¶

The SQL file path must follow the following format:

For example, when the DAO interface name is aaa.bbb.EmployeeDao and the DAO method name is selectById , the SQL file path is as follows:

Dependency on a specific RDBMS¶

You can specify dependency on a specific RDBMS by file name. To do this, put the hyphen “-” and RDBMS name before the extension “.sql”. For example, the file path specific to PostgreSQL is as follows:

The SQL files specific to RDBMSs are given priority. For example, in the environment where PostgreSQL is used, “META-INF/aaa/bbb/EmployeeDao/selectById-postgres.sql” is chosen instead of “META-INF/aaa/bbb/EmployeeDao/selectById.sql”.

The RDBMS names are stem from dialects:

RDBMS	Dialect	RDBMS Name
DB2	Db2Dialect	db2
H2 Database	H2Dialect	h2
HSQLDB	HsqldbDialect	hsqldb
Microsoft SQL Server	MssqlDialect	mssql
MySQL	MySqlDialect	mysql
Oracle Database	OracleDialect	oracle
PostgreSQL	PostgresDialect	postgres
SQLite	SqliteDialect	sqlite

SQL templates in annotations¶

You can specify SQL templates to DAO methods with the @Sql annotation:

The @Sql annotation must be combined with following annotations:

• @Select
• @Script
• @Insert
• @Update
• @Delete
• @BatchInsert
• @BatchUpdate
• @BatchDelete

Directives¶

In SQL templates, the SQL comments following the specific rules are recognised as directives. Supported directives are as follows:

See also Expression language for information of the expression language available in directives.

Bind variable directive¶

Bind variable directive is represented with the format /*. */ . The expression enclosed between /* and */ is evaluated and its evaluation result is passed to bind variable in SQL statement. The directive must be followed by test data, which is never used in runtime.

Basic and domain parameters¶

The parameter whose type is one of Basic classes and Domain classes is recognised as a bind variable.

The following example is the pair of a DAO method and an SQL template:

The following SQL statement is generated from the SQL template:

Parameters in IN clause¶

The parameter whose type is a subtype of java.lang.Iterable or an array type is recognised as bind variables in IN clause. The type argument of java.lang.Iterable must be one of Basic classes and Domain classes . The directives must be followed by test data enclosed between ( and ) .

The following example is the pair of a DAO method and an SQL template:

In case that the employeeIdList contains five elements, the following SQL statement is generated from the SQL template:

In case that the employeeIdList is empty, the IN clause is replaced with in (null) in runtime:

Literal variable directive¶

Literal variable directive is represented with the format /*^. */ . The expression enclosed between /*^ and */ is evaluated and its evaluation result is converted to literal format to be embedded in SQL statement. The directive must be followed by test data, which is never used in runtime.

The following example is the pair of a DAO method and an SQL template:

The DAO method is invoked as follows:

The generated SQL statement is as follows:

Literal variable directives are helpful to avoid bind variables and fix SQL plans.

Literal variable directives do not escape parameters for SQL injection. But the directives reject parameters containing the single quotation ‘ .

Embedded variable directive¶

Embedded variable directive is represented with the format /*#. */ . The expression enclosed between /*# and */ is evaluated and its evaluation result is embedded in SQL statement.

The following example is the pair of a DAO method and an SQL template:

The DAO method is invoked as follows:

The generated SQL statement is as follows:

Embedded variable directives are helpful to build SQL fragments such as ORDER BY clause.

To prevent SQL injection vulnerabilities, embedded variable directives reject parameters containing the following values:

• a single quotation ‘
• a semi colon ;
• two hyphen —
• a slash and an asterisk /*

Condition directive¶

Condition directive allows you to build SQL statements conditionally.

Synopsis¶

The expressions condition , condition2 , and condition3 must be evaluated to either boolean or java.lang.Boolean .

The elseif directives and the else directive are optional.

Suppose you have the following SQL template:

If the employeeId is not null , the generated SQL statement is as follows:

If the employeeId is null , the generated SQL statement is as follows:

The SQL keyword where is removed automatically.

elseif and else¶

Suppose you have the following SQL template:

If the employeeId != null is evaluated true , the generated SQL statement is as follows:

If the employeeId == null && department_id != null is evaluated true , the generated SQL statement is as follows:

The SQL keyword and followed by department_id is remove automatically:

If the employeeId == null && department_id == null is evaluated true , the generated SQL statement is as follows:

The SQL keyword and followed by department_id is remove automatically:

Nested condition directive¶

You can nest condition directives as follows:

Removal of clauses on the condition directive¶

Following clauses can become unnecessary on the condition directive:

• WHERE
• HAVING
• ORDER BY
• GROUP BY

In the case, they are removed automatically.

Suppose you have the following SQL template:

If the employeeId != null is evaluated false , the generated SQL statement is as follows:

Because the SQL clause where followed by /*%if . */ is unnecessary, it is removed automatically.

Removal of AND and OR keywords on the condition directives¶

AND and OR keywords can become unnecessary on the condition directive. In the case, they are removed automatically.

Suppose you have the following SQL template:

If the employeeId != null is evaluated false , the generated SQL statement is as follows:

Because the SQL keyword and following /*%end*/ is unnecessary, it is removed automatically.

Restriction on condition directive¶

/*%if condition*/ and /*%end*/ must be included in same SQL clause and in same statement level.

The following template is invalid, because /*%if condition*/ is in the FROM clause and /*%end*/ is in the WHERE clause:

The following template is invalid, because /*%if condition*/ is in the outer statement and /*%end*/ is in the inner statement:

Loop directive¶

Loop directive allows you to build SQL statements using loop.

Synopsis¶

The item is the loop variable. The expression sequence must be evaluated to a subtype of java.lang.Iterable or an array type.

In the inside between /*%for item : sequence*/ and /*%end*/ , two extra loop variables are available:

item_index:	The index (0-based number) of the current item in the loop
item_has_next:	Boolean value that tells if the current item is the last in the sequence or not

The prefix item indicates the name of the loop variable.

for and item_has_next¶

Suppose you have the following SQL template:

If the sequence names contains three items, the generated SQL statement is as follows:

Removal of clauses on the loop directive¶

Following clauses can become unnecessary on the loop directive:

• WHERE
• HAVING
• ORDER BY
• GROUP BY

In the case, they are removed automatically.

Suppose you have the following SQL template:

If the sequence names is empty, the generated SQL statement is as follows:

Because the SQL clause where followed by /*%for . */ is unnecessary, it is removed automatically.

Removal of AND and OR keywords on the loop directive¶

AND and OR keywords can become unnecessary on the loop directive. In the case, they are removed automatically.

Suppose you have the following SQL template:

If the sequence names is empty, the generated SQL statement is as follows:

Because the SQL keyword or following /*%end*/ is unnecessary, it is removed automatically.

Restriction on loop directive¶

/*%for . */ and /*%end*/ must be included in same SQL clause and in same statement level.

Expansion directive¶

Expansion directive allows you to build column list of SELECT clause from the definition of Entity classes .

Synopsis¶

The expression alias is optional. If it is specified, it must be evaluated to java.lang.String .

The directive must be followed by the asterisk * .

expand¶

Suppose you have the following SQL template and the entity class mapped to the template:

The generated SQL statement is as follows:

If you specify an alias to the table, specify same alias to the expansion directive:

The generated SQL statement is as follows:

Population directive¶

Population directive allows you to build column list of UPDATE SET clause from the definition of Entity classes .

Synopsis¶

populate¶

Suppose you have the following SQL template and the entity class mapped to the template:

The generated SQL statement is as follows:

Comments¶

This section show you how to distinguish between directives and normal SQL comments.

Single line comment¶

Always the string consisting of two hyphens — is a single line comment. It is never directive.

Multi line comment¶

If the character following /* is not permissible as the first character in a Java identifier and it is neither % , # , @ , " nor ‘ , the /* is beginning of a multi line comment.

The followings are the beginning of a multi line comment:

• /**…*/
• /*+…*/
• /*=…*/
• /*:…*/
• /*;…*/
• /*(…*/
• /*)…*/
• /*&…*/

In other hand, the followings are the beginning of a directive:

• /* …*/
• /*a…*/
• /*$…*/
• /*@…*/
• /*”…*/
• /*’…*/
• /*#…*/
• /*%…*/

We recommend you always use /**. */ to begin multi line comments because it is simple.

doma-template module¶

The doma-template module helps obtain prepared SQL statements from SQL templates. The module only contains the following classes:

Шаблоны в SQL Server Management Studio

Допустим нам нужно создать таблицу, т.е. обычный Create Table. Мы можем писать весь текст вручную, мы можем скопировать уже готовый скрипт и его адаптировать. Но в этом примере мы всё сделаем с помощью шаблонов.

Короче кликаем по нужному шаблону (в примере “Create Table”, после чего появляется новый запрос и в нём текст запроса. У меня получился следующий:

Здесь хочется отметить, что параметр заключается в угловые скобки и состоит из 3-х частей, разделённых запятыми. Например для следующего параметра:

< schema_name , sysname , dbo >

schema_name – имя параметра в шаблоне

sysname – тип параметра (int, char(20) и т.п., а также sysname)

dbo – значение по умолчанию

В выше приведённом шаблоне тип создаваемого столбца также задаётся отдельным параметром, поэтому при объявлении самого столбца тип не задаётся:

< columns_in_primary_key , , c1 > < column1_datatype , , int > ……

Но если бы мы точно знали, что столбец c1 должен быть типа int, то могли бы записать так:

< columns_in_primary_key , int , c1 > ……

Вернёмся к примеру.

Осталось только параметры шаблона задать. Для этого в главном меню -> Query -> Specify Values for Template Parameters…

Ну или как можно заметить можно нажать Ctrl+Shift+M

Появляется окошко, где мы можем настраивать нужные параметры:

Пробежались, задали значения:

Жмём ОК, и наш запрос принимает нормальный вид:

Вот справочник и готов, а кода мы фактически и не писали! Но ведь я взял обычный шаблон, я бы сказал просто демонстрационный.

Пользовательские шаблоны

А теперь давайте создадим свой шаблон для создания справочника, чтобы в будущем нам нужно было делать минимум операций.

Для этого в нужной папке браузера шаблонов создаём новый.

Примечание: Вероятно есть смысл в создании специальной папки для пользовательских шаблонов, но я в примере новый шаблон добавил в папку Table

Теперь внесём в него необходимый код. Для этого в контекстном меню этого шаблона нужно выбрать Edit. Ведь двойной клик – это уже использование шаблона.

Далее пишем текст запроса, основу я скопирую из предыдущего примера, и внесу некоторые правки:

Можно сохранять шаблон.

Использование пользовательского шаблона

Как и раньше двойной клик на имени шаблона, открывается новый запрос заполненный текстом шаблона. Заходим в настройку параметров шаблона (Ctrl+Shift+M)

Можно заметить, что указать нужно 3 параметра, но если имя схемы в проекта будет всегда dbo, то можно и его убрать из параметров.

После чего будет сформирован следующий скрипт:

Выполняем его. Таблица успешно создаётся:

Written by Igor Motskin

Периодически мы проводим обучение и даем возможность стажировки на базе нашей платформы с управлением на SQL. Если вам это интересно, то пожалуйста посмотрите информацию об обучении/стажировке по SQL.