Лекция № 21
Тема: "Сортировка, поиск и фильтрация данных"
План
1. Сортировка данных
2. Интерактивный поиск данных
3. Фильтрация данных с помощью запросов
1. Сортировка данных
При использовании сетки для просмотра таблиц баз данных в качестве одной из функций должна быть возможность сортировки данных с помощью щелчка на заголовках столбцов сетки. При этом последовательный щелчок на одном заголовке переводит порядок сортировки в противоположное состояние (по возрастанию или по убыванию).
Для сортировки в компоненте ADOTable имеется свойство IndexFieldNames, которому нужно присвоить имя поля с указанием направления сортировки: ASC - сортировка по возрастанию (используется по умолчанию и может не указываться), DESC - сортировка по убыванию. Обратите внимание, что параметры обязательно записываются прописными буквами.
Например, для сортировки таблицы по полю 'fam' нужно ввести команду:
ADOTable1.IndexFieldNames:='fam'; // сортировка по возрастанию
ADOTable1.IndexFieldNames:='fam DESC'; // сортировка по убыванию
Если пользователь щелкает на заголовках сетки, то для компонента DBGrid возникает событие OnTitleClick. Данное событие имеет параметр Column, с помощью которого можно определить имя поля, по заголовку которого щелкнули мышью. Для распознавания имени столбца используют запись Column.FieldName.
Для сортировки данных в сетке нужно в события сетки OnTitleClick написать код:
// если сетка отсортирована по полю по убыванию
if ADOTable1.IndexFieldNames = Column.FieldName+' DESC' then
// сортируем сетку по возрастанию
ADOTable1.IndexFieldNames:=column.FieldName
else
// иначе сортируем сетку по убыванию
ADOTable1.IndexFieldNames:=Column.FieldName+' DESC';
2. Интерактивный поиск данных
Одной из основных функций информационной системы является поиск данных по различным критериям. В Delphi есть несколько способов поиска. Одним из них является использование метода Locate. Запись метода имеет вид:
ADOTable1.Locate('имя поля1;,имя поля2;...,имя поляN',VarArrayOf([условие1,условие2,...,условиеN]),[loCaseInSensitive,loPartialKey]);
Первым параметром указывают список полей, по которым выполняется поиск. Имена указываются через точку с запятой и весь список берется в одинарные кавычки.
Вторым параметром указывают список условий. Тип условия должен совпадать с соответствующим типом поля.
В квадратных скобках указывают параметры поиска данных. Первый параметр (loCaseInSensitive) указывает на то, что поиск нужно выполнять без учета регистра. Второй параметр (loPartialKey) указывает на то, что поиск нужно проводить по частичному совпадении. Если ни один из параметров не нужен, то указывают пустые квадратные скобки.
Пример. Пусть на форме имеются текстовые поля Edit1 и Edit2 для ввода ключа поиска по полям "fam" и "oklad". Напишите код, выполняющий поиск данных в таблице по введенным значением ключа.
ADOTable1.Locate('fam;oklad',VarArrayOf([Edit1.Text,StrToFloat(Edit2.Text)]),[loCaseInsensitive,loPartialKey]);
Обратите внимание, что условие для поля oklad конвертируется в число, так как поле имеет числовой тип.
Интерактивный поиск позволяет установить указатель на найденную запись. При этом другие записи также остаются видимыми в сетке.
Часто эта команда используется для восстановления позиции в таблице после ее обновления.
Пример. Пусть в таблице имеется ключевое поле id_sotr. Необходимо после обновления таблицы восстановить текущее положение.
//переменная для сохранения значению ключевого поля
var pos:integer;
. . .
//записываем в переменную текущее значение ключа
pos:=ADOTable1.FieldByName('id_sotr').Value;
//обновляем таблицу (указатель перейдет в начало таблицы)
ADOTable1.Requery();
//ищем в ключевом поле сохраненное ранее значение
//и восстанавливаем позицию в таблице
ADOTable1.Locate('id_sotr',pos,[]);
3. Фильтрация данных с помощью запросов
Фильтрация также выполняет поиск, но те записи, которые не соответствуют заданному условию, временно скрываются от просмотра.
Для фильтрации компонент ADOTable имеет следующие характеристики:
- Filter = условие поиска в виде строки;
- Filtered = true / false (включить или выключить фильтрацию по условию
// задаем условие поиска (Фамилия начинается на А и средний балл = 5)
ADOTable1.Filter := 'fam like ''А%'' and srbal = 5'
// включаем фильтрацию
ADOTAble1.Filtered := true;
При записи условий поиска надо использовать следующие правила:
- ключ для поиска по текстовым полям берется в одинарные кавычки:
fam = 'Иванов'
- если поиск по текстовым полям выполняется по части символов, то используют функцию like и символы подстановки %.
fam like 'ива%' //начинается на ива
fam like '%ива' //заканчивается на ива
fam like '%ива%' //содержит ива
- ключ для поиска по числовым полям в кавычки не берется:
oklad = 1500, oklad <= 1500, oklad> = 1500 и т.д.
- ключ для поиска по датам берется в одинарные кавычки, а дата записывается в формате нужной СУБД
#dd/mm/yyyy# - формат Access
'yyyy-mm-dd' – формат MySQL
'dd.mm.yyyy' – формат SQL Server
Форматирование дат для условий поиска
При вводе условий поиска пользователь будет задавать даты в обычном формате: dd.mm.yyyy. Такой формат не всегда подходит для использования в запросах, поэтому его предварительно надо конвертировать в нужный формат.
Для этого в Delphi есть специальная функция конвертирования дат:
FormatDateTime('шаблон',дата)
В качестве шаблона надо указать шаблон, в соответствии с которым конвертируется дата.
Пример. В поле Edit1 введена дата в обычном формате. Надо конвертировать ее в формат для запроса к СУБД Access. Поле Edit1 сохраняет значение даты как текст, а функция требует, чтобы второй ее параметр имел тип даты. Поэтому мы конвертуем текст в дату с помощью функции StrToDate:
FormatDateTime('#dd"/"mm"/"yyyy#',StrToDate(Edit1.Text))
Обратите внимание, что символ / берется в двойныекавычки. Если этого не сделать, то функция FormatDateTime заменяет этот символ на точку. Если же эти символы взять в двойные кавычки, то функция FormatDateTime записывает его в исходном виде ничего не заменяя.
Пример. Переписать предыдущий пример для СУБД MySQL
FormatDateTime('yyyy-mm-dd',StrToDate(Edit1.Text))
Пример. Переписать предыдущий пример для СУБД SQL Server
FormatDateTime('dd/mm/yyyy',StrToDate(Edit1.Text))
Пример фильтрации данных
Выполнение фильтрации рассмотрим на примере. Пусть имеется форма для поиска данных:
Пользователь может выборочно ввести условия поиска в нужных полях. Для текстовых полей можно поиск выполняется по первым введенным символами, для табельного номера проверяется полное совпадение значений, для поля Оклад и Дата рождения можно ввести диапазон значений или один из отрезков диапазона.
Кнопка Отобрать формирует запрос, отбирает данные и отображает их в сетке. Для кнопки напишем код:
procedure TForm1.Button2Click (Sender: Tobject)
var s: string;
begin
// переменная для хранения условия поиска
s: := '';
// поиск по табельным номером
if Edit1.Text <> '' then
s := s + 'tabnom =' + Edit1.Text;
// поиск по фамилии
if Edit2.Text <> '' then
if s <> '' then
s := s + ' and fam like''' + Edit2.Text + '%'''
else
s := s + 'fam like''' + Edit2.Text + '%''';
// поиск по имени
if Edit3.Text <> '' then
if s <> '' then
s := s + ' and imya like''' + Edit3.Text + '%'''
else
s := s + 'imya like' '' + Edit3.Text + '%''';
// поиск по отчество
if Edit4.Text <> '' then
if s <> '' then
s := s + ' and otch like''' + Edit4.Text + '%'''
else
s := s + 'otch like''' + Edit4.Text + '%''';
// поиск по дате род. (Дата рожд. от)
// дата форматируется в формат Access
if Edit5.Text <> '' then
if s <> '' then
s := s + ' and datar> =' + formatdatetime ('#dd"/"mm"/"yyyy#',StrToDate(Edit5.Text)
else
s := s + 'datar> =' + FormatDateTime('#dd”/”mm"/"yyyy#', StrToDate(Edit5.Text);
// поиск по дате род. (Дата рожд. до)
// дата форматируется в формат Access
if Edit6.Text <> '' then
if s <> '' then
s := s + ' and datar<=' + formatdatetime ('#dd"/"mm"/"yyyy#',StrToDate(Edit6.Text)
else
s := s + 'datar<=' + FormatDateTime('#dd"/"mm"/"yyyy#', StrToDate(Edit6.Text);
// поиск по окладу (оклад выше)
if Edit7.Text <> '' then
if s <> '' then
s := s + ' and oklad> =' + Edit7.Text
else
s := s + 'oklad> =' + Edit7.Text;
// поиск по окладу (оклад до)
if Edit8.Text <> '' then
if s <> '' then
s := s + ' and oklad <=' + Edit8.Text
else
s := s + 'oklad <= '+ Edit8.Text;
// если условие задано
if s <> '' then
begin
// задаем условие для фильтра
ADOTable1.Filter := s;
// включаем фильтрацию
ADOTable1.Filtered := true;
end;
end;
Кнопка Очистить очищает все поля для ввода условий фильтрации и отражает в сетке все записи. Для кнопки напишем код:
procedure TForm1.Button3Click (Sender: Tobject)
begin
// очищаем поля
Edit1.Clear;
Edit2.Clear;
Edit3.Clear;
Edit4.Clear;
Edit5.Clear;
Edit6.Clear;
// ставим курсор в первое поле
Edit1.Setfocus;
// выключаю фильтрацию
ADOTable1.Filtered := true;
end;
Вопросы для самоконтроля
1. Какое свойство позволяет выполнить сортировку в таблице?Как указать направление сортировки данных в таблице?
2. В каком событии сетки можно написать код для сортировки данных по щелчку на заголовках полей? Пример.
3. Какая команда позволяет выполнять интерактивный поиск в таблицах? Приведите синтаксиси этой команды.
4. Как выполняется фильтрация данных в таблице? Какие особенности задания условий для текстовых и числовых полей вы знаете?
5. Какие особенности задания условий в запросах для полей типа дата вы знаете? Как можно конвертировать дату в нужный формат для использования в запросах?