Реляционные запросы

Реляционные запросы#

Реляционные запросы* — SQL-запросы, направляемые напрямую в БД. Игнорируют текущие изменения сессии, не синхронизированные с БД, требуют явного управления транзакциями. Используются для сложных выборок, массовых операций и сценариев, где важна производительность на уровне СУБД.

  • ASQL;

  • ATSQL;

  • ASelect;

  • JDBC / connectionAccessor / PreparedStatement;

  • SQL(...).on().

Введение#

Для обработки реляционных запросов в основном используется методы на базе anorm.

Для более удобного использования в контекст бизнес логики добавлены дополнительные функции.

Внимание

Реляционные запросы полностью игнорируют изменения текущей сессии, не синхронизированные с базой данных. Если требуется выполнить согласованный запрос, используйте метод session.flush()

Реляционные запросы выполняются напрямую через SQL.

Особенности:

  • игнорируют изменения в текущей сессии;

  • требуют явного управления транзакциями.

Примечание

  • Запрос на чтение (ASQL, ASelect) может выполняться без явной транзакции, но в рамках сессии БД;

  • Запрос на изменение данных (ATSQL) должен выполняться в транзакции для обеспечения целостности;

  • Объектные запросы (OQuery, load) выполняются в контексте ORM-сессии.

    • Если транзакция не начата, чтение выполняется без явной транзакции (в отдельной SQL-сессии).

    • Если транзакция активна, запрос выполняется в её рамках и видит все локальные изменения текущей сессии, включая ещё не отправленные в БД данные.

Именования запросов

Аббревиатуры в названиях методов следуют правилу:

Префикс

Значение

Описание

A

AnormSQL

Указывает на использование стандартного Scala-пакета anorm

T

Transactional

Означает, что запрос изменяет данные в БД и требует транзакции

ASQL#

SQL-запрос на чтение данных.

ASQL удобен для получения значения по одному столбцу результата запроса.

Когда использовать
SQL-запрос с небольшим количеством колонок. Рекомендуется использовать при выборе не более двух колонок, чтобы избежать усложнения запроса.

Пример:

ASQL"select id, sCode from Bs_Goods where id=$id"

Подстановка связанных переменных (binding)#

Примечание

Использование ASQL с подстановкой связанных переменных является полезной практикой, потому что запрос остаётся неизменным, меняется лишь значение параметра. Тем самым запрос не воспринимается системой, как новый, и будет записан в системную таблицу запросов единожды, что не приводит к распуханию БД.

Инструмент ASQL"""<текст запроса>""" подставляет бинды с учётом типа данных переменной.

  • Если бы была подстановка NString, то ASQL сам обернул бы значение в одинарные кавычки, т.е. нет необходимости их указывать вручную.

  • Если NString подставляется в текст s"""<текст запроса>""", то одинарные кавычки необходимо указывать вручную.

Если текст запроса для ASQL"""<текст запроса>""" собирается динамически средствами scala, в том числе название таблицы для select формируется переменной, то его необходимо подставлять через #$bind, чтобы ASQL не обернул подставляемое значение в одинарные кавычки.

val sNameTb = {
  if (a = 1) "Bs_Goods".ns
  else "Bs_Person".ns
}

val ida: List[NLong] = ASQL"""
  select t.id 
  from #$sNameTb
  where idObjectType = $idvObjectType
""".as(nLong(1).*)

Существует иной формат binding, когда запрос формируется в переменной String/NString, где указываются ключи для подстановки значения, карта подстановки указывается в .on().

val svRequest =
 s"""
  select t.id 
  from Bs_Goods
  where idObjectType = {idObjectType}
"""
ASQL(svRequest).on("idObjectType" -> idvObjectType)

new ASelect {
 val sCode = asNString("sCode")
 val id = asNLong("id")
 SQL(s"""
    select p.id
          ,p.sCode
    from Bs_Person p
    where idObjectType = {idObjectType}
  """)
  .on("idObjectType" -> idvObjectType)
}.map { rv => //rv представляет собой одну строку результата запроса
 rv.id() -> rv.sCode()
}.toMap

Может быть полезно, если запрос собирается в переменной String/NString.

Работа с несколькими столбцами через RowParser#

ASQL поддерживает маппинг результата в типизированные структуры через RowParser. Используйте, когда нужен компактный синтаксис с типобезопасностью.

Когда выбирать этот подход:

  • нужна типобезопасность, но без объявления блока ASelect;

  • запрос используется в функциональном стиле с цепочками .map / .filter;

  • результат маппится в коллекцию для дальнейшей обработки.

case class Data(idGitOwner: NLong, idGitProject: NLong, nType: NNumber)

val dataParser: RowParser[Data] = {
  SqlParser.get[NLong]("idGitOwner") ~ 
  SqlParser.get[NLong]("idGitProject") ~ 
  SqlParser.get[NNumber]("nType")
}.map { case idGitOwner ~ idGitProject ~ nType => 
  Data(idGitOwner, idGitProject, nType) 
}

val vData = ASQL"""
  select id_git_owner, id_git_project, n_type 
  from git_links 
  where project_id = {pid}
""".on(Symbol("pid") -> projectId).as(dataParser.*)

// Использование результата
vData.withFilter(_.nType === 1.nr).foreach { rv => 
  register(rv.idGitOwner, rv.idGitProject) 
}

ATSQL#

SQL-запрос на изменение данных или выполнение операций, требующих транзакции (например, блокировки).

Особенности:

  • при вызове устанавливает признак необходимости транзакции в текущей сессии;

  • если транзакция ещё не начата, она будет инициирована;

  • запрос выполняется на основном (master) сервере БД и не может быть отправлен на реплику;

  • влияет на выбор пула соединений (используется пул для транзакционных операций).

Пример:

ATSQL"update table set value=$v where id=$id"

ASelect#

SQL-запрос с большим количеством колонок.

ASelect удобен для получения значений по нескольким столбцам результата запроса.

Позволяет типизированно получать результат.

Когда использовать:

  • сложные SQL запросы;

  • большое количество колонок;

  • аналитические выборки.

Пример:

for (rv <- new ASelect {
  val id = asLong("id")
  val name = asString("name")

  SQL"""
  select id,name from table
  """
}) {
  println(rv.id())
}

Низкоуровневый доступ через JDBC#

В некоторых случаях SQL-запросы могут выполняться напрямую через JDBC (java.sql), например с использованием PreparedStatement и session.connectionAccessor.

Этот способ даёт полный контроль над выполнением запроса и часто используется для высокопроизводительных операций, таких как batch insert.

Особенности:

  • для INSERT, UPDATE, DELETE и других операций изменения данных перед выполнением запроса необходимо зарегистрировать транзакцию в сессии;

  • это гарантирует, что запрос будет выполнен в транзакционном контексте и не будет отправлен на реплику;

  • при работе через JDBC управление параметрами, batch-операциями и выполнением запроса выполняется вручную.

Пример:

session.registerTransaction()

session.connectionAccessor forIt { connection =>
  Using.resource(connection.prepareStatement(queryBuilder.toString())) { stmt =>
    for (data <- items) {
      stmt.setLong(1, data.id)
      stmt.setString(2, data.code)
      stmt.addBatch()
    }
    stmt.executeBatch()
  }
}

Внимание

Если JDBC-запрос изменяет данные, перед его выполнением необходимо вызвать session.registerTransaction(). Иначе запрос может быть выполнен вне транзакционного контекста.

Использование SQL(…) и параметров через on(…)#

Помимо интерполяции (ASQL"...", ATSQL"...", SQL"..."), запрос может быть сформирован из обычной строки с последующей подстановкой параметров через метод on.

Этот способ используется, когда:

  • SQL формируется динамически;

  • требуется условная сборка запроса;

  • невозможно использовать строковую интерполяцию.

Пример:

new ASelect {
  val id = asNLong("id")

  SQL(
    s"""
    select t.id
    from table t
    where t.value = {value}
    """
  ).on(
    Symbol("value") -> someValue
  )
}

Особенности:

  • параметры передаются через {param} и on(Symbol(…) -> value);

  • используется обычная строка (SQL(…)), а не интерполяция;

  • требует аккуратности при формировании SQL.

  • полный ручной контроль над SQL и параметрами;

  • не используется ORM и кеш;

  • требуется явное управление транзакциями;

  • подходит для массовых операций (batch insert/update).

Примечание

Используйте этот способ только там, где возможностей ASQL, ATSQL и ASelect недостаточно.

Типичные ошибки#

Некорректное использование запросов на чтение#

Внимание

Даже если такой код работает в тестовом формате, это будет приводить к ошибкам в продакшене.

Пример неверного запроса:

ASQL"select * from table where id=$id for no key update".execute()

Данный запрос не регистрирует транзакцию, он может быть выполнен в режиме автокоммита, что фактически делает его бессмысленным, так как блокировка снимается сразу же после выполнения запроса.

ASQL"update table set a=$v where id=$c".execute()

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

Внимание

Внимание, все запросы на изменения данных должны быть транзакционными.

ATSQL"select * from table where id=$id for no key update".execute()

ATSQL"update table set a=$v where id=$c".execute()

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

Некорректное использование блокировок#

ATSQL"select * from reference where id=$id for update".execute()

Данная блокировка заблокирует работу всех документов по справочнику, так как блокируется не только запись, но и все внешние ключи.

Всегда пишите for no key update, если у вас нет четкого обоснования необходимости иной блокировки.

Некорректное формирование запроса#

Будьте осторожнее с подставлением значений в строку (интерполяции) при формировании sql запроса.

ATSQL(s"select * from reference where gid=$gid for update")
ASQL(s"select * from reference where gid=$gid for update")
new ASelect {
  //...
  SQL(s"select * from reference where gid=$gid for update")
}

В данном примере значение gid неправильно подставится в sql выражение, т.к. технически подстановка идет в обычную строку. Никто не догадается правильно конвертировать данные в корректную для sql форму

ATSQL(s"select * from reference where gid={gid} for update").on(Symbol("gid") -> gid)
ASQL(s"select * from reference where gid={gid} for update").on(Symbol("gid") -> gid)
new ASelect {
  //...
  SQL(s"select * from reference where gid={gid} for update")
    on(Symbol("gid") -> gid/*, другие параметры*/)
}

Для случаев, когда вам нужно собрать запрос из обычной строки, используйте замену символов on(Symbol("символ для замены") -> ваша переменная, ...)

ATSQL"select * from reference where gid=$gid for update"
ASQL"select * from reference where gid=$gid for update"
new ASelect {
  //...
  SQL"select * from reference where gid=$gid for update"
}

Тут будет подставлено все правильно

Содержание