mysqlnd

Типы запросов

В библиотеке MySQL++ предлагаются три основных способа выполнения запросов — Query::execute(), Query::store() и Query::use(). Попробуем выяснить, для каких ситуаций лучше всего подходит каждый из перечисленных выше запросов.

1.1. Запрос Query::execute()

Этот тип запроса предназначен для тех случаев, когда нет необходимости возвращать какие-либо данные. Например, при выполнении команды CREATE INDEX. Тем не менее, некоторую информацию от MySQL-сервера метод execute() всё же передаёт вызывающей стороне — в объекте SimpleResult. Кроме явной характеристики: флага статуса завершения операции, этот объект содержит также информацию о количестве строк таблицы базы данных, на которые повлияло выполнение заданной команды.

Если после выполнения команды требуется только оценка статуса её завершения, то можно воспользоваться немного более эффективным методом Query::exec(), который возвращает только характеристику результата выполнения в виде значения типа bool.

1.2. Запрос Query::store()

Самый простой способ извлечения данных из таблиц БД — использование метода store(). Он возвращает объект StoreQueryResult, который содержит полный набор результатов (result set). Поскольку класс StoreQueryResult является производным от std::vector<:row>, открывается доступ ко всем STL-операциям над строками в наборе результатов. Обращение к произвольным строкам набора с использованием их индексов, построчные итерации в прямом и обратном направлении, применение STL-алгоритмов в наборе результатов — всё доступно и всё работает.

Практическое применение метода store() рассматривалось в простом примере, приведённом в самой первой статье данного цикла.

Если возникает необходимость сохранения полученного набора результатов в STL-контейнере, но при этом нежелательно использование std::vector, то вам поможет метод Query::storein(). Он позволяет сохранять данные не в StoreQueryResult, а в любом стандартном STL-контейнере, как с последовательным доступом, так и с произвольным (ассоциативный набор).

Здесь необходимо отметить, что, несмотря на простоту и удобство запросов типа store(), способ хранения извлекаемых с их помощью данных в основной памяти может порождать серьёзные проблемы. Если запись в базе данных имеет размер 2 Кбайта, а по запросу может сгенерироваться набор результатов, содержащий миллион записей, то потребуется более двух гигабайтов основной памяти клиента для работы с таким набором данных (с учётом вспомогательной информации и памяти, занимаемой самим MySQL++-приложением и библиотекой C API-интерфейса для его поддержки). В подобных случаях более разумным решением является использование третьего типа запроса.

1.3. Запрос Query::use()

Для наборов результатов с большим объёмом данных оптимальный вариант их получения и обработки обеспечивается методом use(), который возвращает объект типа UseQueryResult, обладающий весьма ограниченными возможностями произвольного доступа к строкам данных по сравнению с StoreQueryResult. Эти ограничения являются следствием того, что в ответ на use-запрос сервер возвращает результат по одной строке, то есть, в данном случае возможна только последовательная обработка получаемых строк. Аналогом может служить итератор входного потока в языке C++.

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

#include 
#include 
#include 

using namespace std;

int main( int argc, char *argv[] )
{
  // установление соединения с тестовой базой данных
  mysqlpp::Connection con( false );
  if( con.connect( "test_db", "localhost", "tdb_user", "tdb_password" ) )
  {
    // выполнение запроса и вывод полученных результатов.
    // Здесь извлекаются и выводятся все строки из таблицы dvd -
    // по одной строке последовательно, поэтому нет необходимости
    // хранить весь набор результатов в основной памяти
    mysqlpp::Query query = con.query( "select * from dvd" );
    if( mysqlpp::UseQueryResult res = query.use() )
    {
      cout.setf( ios::left );
      cout 

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

Кейс «QUERY и выпадающий список»

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

Итак, выпадающий список. Вначале создадим новый лист (допустим, наша исходная таблица огромна, и всю аналитику мы хотим производить на другом листе). Кликаем правой кнопкой мыши на ячейку А1, выбираем Проверка данных.

В Правилах выбираем Значение из списка, перечисляем все наши тематики через запятую и нажимаем Сохранить:

Список получился вот таким:

В соседнюю ячейку А2 впишем следующую формулу:

=QUERY(‘Книги’!A1:C13,»SELECT A, C WHERE B = ‘»&A1&»‘ ORDER BY C DESC»)

И разберем ее по частям:

‘Книги’!A1:C13 — исходный диапазон, таблица с продажами, книгами и тематиками.
SELECT A, C — в сформированную функцией таблицу попадут данные из этих столбцов, то есть названия книг и продажи.
WHERE B = ‘»&A1&»‘ отбирает только те книги, тематика (в столбце B) которых соответствует указанной в ячейке A1

Обратите внимание на синтаксис: текст из ячейки указывается между апострофов, которые относятся к тексту запроса. После них идут кавычки (мы закрываем текст запроса), амперсанд (присоединяем к тексту запроса текст из ячейки), адрес ячейки, еще один амперсанд, после которого в кавычках продолжается текст запроса.
ORDER BY C DESC — сортируем данные по столбцу B (продажам) по убыванию.

Результат:

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

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

Ответы на запросыQuery Responses

При выполнении запроса DataServiceQuery он возвращает объект IEnumerable запрошенного типа сущности.When executed, the DataServiceQuery returns an IEnumerable of the requested entity type. Этот результат запроса может быть приведен к объекту QueryOperationResponse, как в следующем примере:This query result can be cast to a QueryOperationResponse object, as in the following example:

Экземпляры типа сущности, которые представляют сущности в службе данных, создаются на клиенте процессом, называемым материализацией объектов.The entity type instances that represent entities in the data service are created on the client by a process called object materialization. Дополнительные сведения см. в разделе материализация объектов.For more information, see Object Materialization. Объект QueryOperationResponse реализует объект IEnumerable для предоставления доступа к результатам запроса.The QueryOperationResponse object implements IEnumerable to provide access to the results of the query.

Объект QueryOperationResponse имеет также следующие члены, позволяющие производить доступ к дополнительным сведениям о результате запроса.The QueryOperationResponse also has the following members that enable you to access additional information about a query result:

  • Error — возвращает ошибку, инициируемую операцией, если ошибка возникает.Error — gets an error thrown by the operation, if any has occurred.

  • Headers — содержит коллекцию заголовков ответов HTTP, связанных с ответом на запрос.Headers — contains the collection of HTTP response headers associated with the query response.

  • Query — возвращает исходный объект DataServiceQuery, создавший ответ QueryOperationResponse.Query — gets the original DataServiceQuery that generated the QueryOperationResponse.

  • StatusCode — возвращает код ответа HTTP для ответа на запрос.StatusCode — gets the HTTP response code for the query response.

  • TotalCount — возвращает общее число сущностей в наборе сущностей, если метод IncludeTotalCount вызван на клиенте DataServiceQuery.TotalCount — gets the total number of entities in the entity set when the IncludeTotalCount method was called on the DataServiceQuery.

  • GetContinuation — возвращает объект DataServiceQueryContinuation, который содержит URI следующей страницы результатов.GetContinuation — returns a DataServiceQueryContinuation object that contains the URI of the next page of results.

По умолчанию WCF Data Services возвращает только те данные, которые явно выбраны с помощью URI запроса.By default, WCF Data Services only returns data that is explicitly selected by the query URI. Это дает возможность явно загрузить дополнительные данные из службы данных, если это необходимо.This gives you the option to explicitly load additional data from the data service when it is needed. Запрос отправляется в службу данных при каждой явной загрузке данных из службы данных.A request is sent to the data service each time you explicitly load data from the data service. Данные, которые можно явно загрузить, включают связанные сущности, разбитые на страницы данные ответа и потоки двоичных данных.Data that can be explicitly loaded includes related entities, paged response data, and binary data streams.

Примечание

Поскольку служба данных может возвратить разбитый на страницы ответ, рекомендуется, чтобы приложение использовало шаблон программирования для обработки разбитого на страницы ответа службы данных.Because a data service may return a paged response, we recommend that your application use the programming pattern to handle a paged data service response. Дополнительные сведения см. в разделе Загрузка отложенного содержимого.For more information, see Loading Deferred Content.

Объем возвращаемых запросом данных может быть также уменьшен указанием, что в ответе возвращаются только определенные свойства сущности.The amount of data returned by a query can also be reduced by specifying that only certain properties of an entity are returned in the response. Дополнительные сведения см. в разделе проекции запросов.For more information, see Query Projections.

Example — Query Multi-Type

The raw SQL query can be executed using the Query method and map the result to a list of different types.

string sql = "SELECT * FROM Invoice;";

using (var connection = My.ConnectionFactory())
{
    connection.Open();

    var invoices = new List();

    using (var reader = connection.ExecuteReader(sql))
    {
        var storeInvoiceParser = reader.GetRowParser();
        var webInvoiceParser = reader.GetRowParser();

        while (reader.Read())
        {
            Invoice invoice;

            switch ((InvoiceKind) reader.GetInt32(reader.GetOrdinal("Kind")))
            {
                case InvoiceKind.StoreInvoice:
                    invoice = storeInvoiceParser(reader);
                    break;
                case InvoiceKind.WebInvoice:
                    invoice = webInvoiceParser(reader);
                    break;
                default:
                    throw new Exception(ExceptionMessage.GeneralException);
            }

            invoices.Add(invoice);
        }
    }
    
    My.Result.Show(invoices);
}

Select Query

In order to define SQL to execute for a Spring Data repository method, we can annotate the method with the @Query annotation  — its value attribute contains the JPQL or SQL to execute.

The @Query annotation takes precedence over named queries, which are annotated with @NamedQuery or defined in an orm.xml file.

It’s a good approach to place a query definition just above the method inside the repository rather than inside our domain model as named queries. The repository is responsible for persistence, so it’s a better place to store these definitions.

2.1. JPQL

By default the query definition uses JPQL.

Let’s look at a simple repository method that returns active User entities from the database:

@Query("SELECT u FROM User u WHERE u.status = 1")
Collection findAllActiveUsers();

2.2. Native

We can use also native SQL to define our query. All we have to do is to set the value of the nativeQuery attribute to true and define the native SQL query in the value attribute of the annotation:

@Query(
  value = "SELECT * FROM USERS u WHERE u.status = 1", 
  nativeQuery = true)
Collection findAllActiveUsersNative();

Функции

JPQL поддерживает несколько функций базы данных. Эти функции баз данных, независимы от имя и синтаксиса, но требуют поддержки базы данных. Если база данных поддерживает эквивалентный функциональный или различный синтаксис, стандартная функция JPQL поддерживается, если база данных не обеспечивает способа выполнить функцию, то это не поддерживается. Для математических функций (+,-/, *) применяются правила BEDMAS. Некоторые провайдеры JPA может поддерживать дополнительные функции.

Поддерживаемые JPQL функции

Функция Описание Пример
вычитание
e.salary - 1000
+ сложение
e.salary + 1000
* умножение
e.salary * 2
деление
e.salary  2
ABS модуль числа
ABS(e.salary - e.manager.salary)
CASE определяет оператор выбора
CASE e.status WHEN  THEN 'active' WHEN 1 THEN 'consultant' ELSE 'unknown' END
COALESCE оценивает к первому не нулевое значение аргумента
COALESCE(e.salary, )
CONCAT конкатинирует строки
CONCAT(e.firstName, ' ', e.lastName)
CURRENT_DATE текущая дата
CURRENT_DATE
CURRENT_TIME текущее время
CURRENT_TIME
CURRENT_TIMESTAMP текущее дата и время
CURRENT_TIMESTAMP
LENGTH длинна строки или двоичного значения
LENGTH(e.lastName)
LOCATE индекс строки в последовательности, дополнительно начинающиейся по индексу запуска
LOCATE('-', e.lastName)
LOWER преобразование значение последовательности в нижний регистр
LOWER(e.lastName)
MOD вычисляет остаток от деления первого целого числа вторым
MOD(e.hoursWorked  8)
NULLIF возвращает null, если первый параметр будет равняться второму параметру, иначе возвращает первый параметр
NULLIF(e.salary, )
SQRT вычисляет квадратный корень числа
SQRT(o.result)
SUBSTRING подстрока от строки, начинающиеся по индексу, дополнительно с размером подстроки
SUBSTRING(e.lastName, , 2)
TRIM обрезание пробелов в конце и начале строки
TRIM(TRAILING FROM e.lastName), TRIM(e.lastName), TRIM(LEADING '-' FROM e.lastName)
UPPER преобразование значение последовательности в верхний регистр
UPPER(e.lastName)

Условная обработка результатов

Для демонстрации обработки наборов данных с учётом определённых условий сразу обратимся к практическому примеру. Допустим, что существует таблица товаров, хранящихся на складе. Присвоим этой таблице имя wares, а структура её может иметь следующий вид:

name  CHAR(25) NOT NULL (Наименование товара)
num   INT   (Количество единиц товара)
price REAL  (Цена единицы товара)

Предположим, что необходимо произвести инспекцию товарных запасов и вывести список тех наименований товаров, для которых требуется пополнение. Если товар имеет цену 2000 рублей и более, то минимальное количество единиц не должно быть меньше 10. Для более дешёвых товаров установлен минимум в 30 единиц. Если количество единиц какого-либо товара не дотягивает до установленного лимита, то этот товар должен быть вписан в заявочный список на пополнение.

#include 
#include 
#include 

// Определение специальной структуры для обработки данных
// (макрос, определённый в файле ssqls.h)
sql_create_3( wares, 1, 3,
  mysqlpp::sql_char, name,
  mysqlpp::sql_bigint, num,
  mysqlpp::sql_double, price )

// Определение функтора для проверки необходимости пополнения
struct is_deficit
{
  bool operator()( const wares &w )
  {
    if( ((w.price >= 2000.0) && (w.num  res;
    mysqlpp::Query query = con.query();
    query.store_if( res, wares(), is_deficit() );
    // вывод результатов выбора по условию
    cout.setf( ios::left );
    cout ::const_iterator it;
    for( it = res.begin(); it != res.end(); it++ )
    {
      cout.setf( ios::left );
      cout name.c_str() num price 

В этой программе используется вызов метода store_if(), который позволяет получить контейнер, заполненный строками данных, соответствующими заданному условию. Отметим, что при продемонстрированном подходе предоставляется возможность задавать такие условия отбора, которые не всегда можно записать с помощью SQL-выражений.

Define Order in a Query

We can pass an additional parameter of type Sort to a Spring Data method declaration that has the @Query annotation. It’ll be translated into the ORDER BY clause that gets passed to the database.

3.1. Sorting for JPA Provided and Derived Methods

For the methods we get out-of-the-box like findAll(Sort) or the ones that are generated by parsing method signatures, we can only use object properties to define our sort:

userRepository.findAll(new Sort(Sort.Direction.ASC, "name"));

Now imagine that we want to sort by the length of a name property:

userRepository.findAll(new Sort("LENGTH(name)"));

When we execute the above code we’ll receive an exception:

3.2. JPQL

When we use JPQL for a query definition, then Spring Data can handle sorting without any problem — all we have to do is to add a method parameter of type Sort:

@Query(value = "SELECT u FROM User u")
List findAllUsers(Sort sort);

We can call this method and pass a Sort parameter, which will order the result by the name property of the User object:

userRepository.findAllUsers(new Sort("name"));

And because we used @Query annotation, we can use the same method to get the sorted list of Users by the length of their names:

userRepository.findAllUsers(JpaSort.unsafe("LENGTH(name)"));

It’s crucial that we use JpaSort.unsafe() to create a Sort object instance.

When we use:

new Sort("LENGTH(name)");

then we’ll receive exactly the same exception as we saw above for the findAll() method.

When Spring Data discovers the unsafe Sort order for a method that uses the @Query annotation, then it just appends the sort clause to the query — it skips checking whether the property to sort by belongs to the domain model.

3.3. Native

When the @Query annotation uses native SQL, then it’s not possible to define a Sort.

If we do, we’ll receive an exception:

As the exception says, the sort isn’t supported for native queries. The error message gives us a hint that pagination will cause an exception too.

However, there is a workaround that enables pagination, and we’ll cover in the next section.

Preload functions

Preload also allows functions to be given. In such cases, the function
receives the IDs of the parent association and it must return the associated
data. Ecto then will map this data and sort it by the relationship key:

This is useful when the whole dataset was already loaded or must be
explicitly fetched from elsewhere. The IDs received by the preloading
function and the result returned depends on the association type:

  • For and — the function receives the IDs of
    the parent association and it must return a list of maps or structs
    with the associated entries. The associated map/struct must contain
    the «foreign_key» field. For example, if a post has many comments,
    when preloading the comments with a custom function, the function
    will receive a list of «post_ids» as argument and it must return
    maps or structs representing the comments. The maps/structs must
    include the field

  • For — it behaves similarly to a regular
    but note that the IDs received are the ones from the closest
    parent and not the furthest one. Imagine for example a post has
    many comments and each comment has an author. Therefore, a post
    may have many comments_authors, written as
    . When
    preloading authors with a custom function via ,
    the function will receive the IDs of the comments and not of the
    posts. That’s because through associations are still loaded step
    by step

  • For — the function receives the IDs of the parent
    association and it must return a tuple with the parent id as first
    element and the association map or struct as second. For example,
    if a post has many tags, when preloading the tags with a custom
    function, the function will receive a list of «post_ids» as argument
    and it must return a tuple in the format of

Sorting With JPA Criteria Query Object API

With JPA Criteria – the orderBy method is a “one stop” alternative to set all sorting parameters: both the order direction and the attributes to sort by can be set. Following is the method’s API:

  • orderBy(CriteriaBuilder.asc): Sorts in ascending order.
  • orderBy(CriteriaBuilder.desc): Sorts in descending order.

Each Order instance is created with the CriteriaBuilder object through its asc or desc methods.

Here is a quick example – sorting Foos by their name:

CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(Foo.class);
Root from = criteriaQuery.from(Foo.class);
CriteriaQuery select = criteriaQuery.select(from);
criteriaQuery.orderBy(criteriaBuilder.asc(from.get("name")));

The argument to the get method is case sensitive, since it needs to match the name of the attribute.

As opposed to simple JQL, the JPA Criteria Query Object API forces an explicit order direction in the query. Notice in the last line of this code snippet that the criteriaBuilder object specifies the sorting order to be ascending by calling its asc method.

When the above code is executed, JPA generates the SQL query shown below. JPA Criteria Object generates an SQL statement with an explicit asc clause:

Hibernate: select foo0_.id as id1_4_, foo0_.name as name2_4_
    from Foo foo0_ order by foo0_.name asc

3.1. Sorting by More Than One Attribute

To sort by more than one attribute simply pass an Order instance to the orderBy method for each attribute to sort by.

Here is a quick example – sorting by name and id, in asc and desc order, respectively:

CriteriaQuery criteriaQuery = criteriaBuilder.createQuery(Foo.class);
Root from = criteriaQuery.from(Foo.class); 
CriteriaQuery select = criteriaQuery.select(from); 
criteriaQuery.orderBy(criteriaBuilder.asc(from.get("name")),
    criteriaBuilder.desc(from.get("id")));

The corresponding SQL query is shown below:

Hibernate: select foo0_.id as id1_4_, foo0_.name as name2_4_ 
    from Foo foo0_ order by foo0_.name asc, foo0_.id desc

Типы запросовTypes of queries

Azure Когнитивный поиск поддерживает широкий спектр типов запросов.Azure Cognitive Search supports a broad range of query types.

Тип запросаQuery type

ИспользованиеUsage

Дополнительные сведения и примерыExamples and more information

Текстовый поиск со свободной формой записиFree form text search

Параметр поиска и любое средство синтаксического анализаSearch parameter and either parser

Полнотекстовый поиск позволяет найти один или несколько терминов во всех полях индекса с меткой searchable, примерно так же, как поисковая система Google или Bing.Full text search scans for one or more terms in all searchable fields in your index, and works the way you would expect a search engine like Google or Bing to work. В примере во введении представлен именно полнотекстовый поиск.The example in the introduction is full text search.Для полнотекстового поиска применяется анализ текста через стандартный анализатор Lucene (по умолчанию), с переводом всех терминов в нижний регистр и удалением стоп-слов (например, артиклей).Full text search undergoes text analysis using the standard Lucene analyzer (by default) to lower-case all terms, remove stop words like «the». Вместо этого анализатора по умолчанию вы можете использовать или , которые применяют другие правила анализа текста.You can override the default with or that modify text analysis. Например, keyword позволяет использовать все содержимое поля как один токен.An example is keyword that treats the entire contents of a field as a single token. Это полезно для данных некоторых типов, таких как почтовые индексы, идентификаторы и названия продуктов.This is useful for data like zip codes, IDs, and some product names.

Поиск с фильтрациейFiltered search

Выражение фильтра OData и любое средство синтаксического анализаOData filter expression and either parser

Запросы фильтрации оценивают логическое выражение по всем полям индекса с меткой filterable.Filter queries evaluate a boolean expression over all filterable fields in an index. В отличие от поиска, запрос фильтрации проверяет точное содержимое поля, в том числе учитывает регистр для строковых полей.Unlike search, a filter query matches the exact contents of a field, including case-sensitivity on string fields

Еще одно важное различие — запросы фильтрации выражаются в синтаксисе OData.Another difference is that filter queries are expressed in OData syntax. Геопространственный поискGeo-search

Тип Edm.GeographyPoint для поля, выражение фильтра и любое средство синтаксического анализаEdm.GeographyPoint type on the field, filter expression, and either parser

Координаты, хранящиеся в поле с типом Edm.GeographyPoint, используются для «поиска соседних объектов» и для элементов управления со встроенными картами.Coordinates stored in a field having an Edm.GeographyPoint are used for «find near me» or map-based search controls

Поиск по диапазонуRange search

Выражение фильтра и средство простого синтаксического анализаfilter expression and simple parser

В Когнитивный поиск Azure запросы к диапазону создаются с помощью параметра Filter.In Azure Cognitive Search, range queries are built using the filter parameter.

Параметр поиска и средство полного синтаксического анализаSearch parameter and Full parser

Создайте сложное выражение запроса, предназначенное для одного поля.Build a composite query expression targeting a single field.

;

Параметр поиска и средство полного синтаксического анализаSearch parameter and Full parser

Находит совпадения с терминами, которые имеют сходную конструкцию или орфографию.Matches on terms having a similar construction or spelling.

;

Параметр поиска и средство полного синтаксического анализаSearch parameter and Full parser

Находит термины, которые расположены в документе рядом друг с другом.Finds terms that are near each other in a document.

;

Параметр поиска и средство полного синтаксического анализаSearch parameter and Full parser

Присваивает более высокий приоритет документам, которые содержат определенный термин.Ranks a document higher if it contains the boosted term, relative to others that don’t.

;

Параметр поиска и средство полного синтаксического анализаSearch parameter and Full parser

Находит совпадения на основе содержимого регулярного выражения.Matches based on the contents of a regular expression.

Параметр поиска и средство полного синтаксического анализаSearch parameter and Full parser

Находит совпадения с учетом префикса и символа тильды () или одного символа ().Matches based on a prefix and tilde () or single character ().

Query Prefix

It is possible to set a prefix for the queries. For Postgres users,
this will specify the schema where the table is located, while for
MySQL users this will specify the database where the table is
located. When no prefix is set, Postgres queries are assumed to be
in the public schema, while MySQL queries are assumed to be in the
database set in the config for the repo.

The query prefix may be set either for the whole query or on each
individual and expression. If a is not given
to a or a , the prefix of the schema given to the
or is used. The query prefix is used only if none of the above
are declared.

Let’s see some examples. To see the query prefix globally, the simplest
mechanism is to pass an option to the repository operation:

You may also set the prefix for the whole query by setting the prefix field:

Setting the prefix in the query changes the default prefix of all
and expressions. You can override the query prefix by either setting
the in your schema definitions or by passing the prefix
option:

Overall, here is the prefix lookup precedence:

  1. The option given to / has the highest precedence
  2. Then it falls back to the attribute declared in the schema
    given to /
  3. Then it falls back to the query prefix
Ссылка на основную публикацию