- •Основы sql. Курс лекций
- •Лекция 1: Введение в структурированный язык запросов sql
- •Основные понятия
- •Реляционные базы данных
- •Введение в технологию клиент-сервер
- •Типы команд sql
- •Запись sql-операторов
- •Описание учебной базы данных
- •Типы данных языка sql, определенные стандартом
- •Символьные данные
- •Varchar][длина]}
- •Округленные числа
- •Дата и время
- •Понятие домена
- •Создание пользовательского типа данных
- •If лог_выражение
- •Основные объекты структуры базы данных sql-сервера
- •Создание базы данных в среде ms sql Server
- •Индексы в среде ms sql Server
- •Уникальный индекс
- •Index имя_индекса on имя_таблицы(имя_столбца
- •Предложение from
- •Предложение where
- •Is not null используется для проверки присутствия значения в поле.
- •Пример 4.16. Список клиентов в алфавитном порядке.
- •Пример 4.17. Список фирм и клиентов. Названия фирм в алфавитном порядке, имена клиентов в каждой фирме в обратном порядке.
- •Операция соединения по двум отношениям (таблицам)
- •Операция тета-соединения
- •Лекция 6: Вычисления и подведение итогов в запросах
- •Insert into – запрос добавления ;
- •Запрос добавления
- •Пример 8.1. Добавление в таблицу товар новой записи.
- •Insert into Итог
- •Ссылочная целостность
- •Создание таблицы
- •Изменение и удаление таблицы
- •Ограничение первичного ключа (primary key)
- •Ограничение внешнего ключа (foreign key)
- •Ограничение уникального ключа (unique)
- •Ограничение по умолчанию (default)
- •Изменение таблицы
- •Удаление таблицы
- •Пример 10.1. Представление клиентов из Москвы.
- •Insert into view1 values (12,'Петров', 'Самара')
- •Независимость от данных
- •Понятие функции пользователя
- •Inline – функции содержат всего одну команду select и возвращают пользователю набор данных в виде значения типа данных table ;
- •Функции Scalar
- •Пример 11.3. Создание функции, которая для некоторого сотрудника выводит список всех его подчиненных.
- •Создание, изменение и удаление хранимых процедур
- •Выполнение хранимой процедуры
- •Пример 12.6. Процедура с входными и выходными параметрами. Создать процедуру для определения общей стоимости товаров, проданных за конкретный месяц.
- •Лекция 13: Курсоры: принципы работы
- •Реализация курсоров в среде ms sql Server
- •Изменение и удаление данных
- •0, Если выборка завершилась успешно;
- •Пример 13.5. Курсор для вывода списка фирм и клиентов из Москвы.
- •Пример 13.6. Курсор для вывода списка приобретенных клиентами из Москвы товаров и их общей стоимости.
- •Определение триггера в стандарте языка sql
- •Реализация триггеров в среде ms sql Server
- •If (columns_updates(){оператор_бит_обработки}
- •Типы триггеров
- •Программирование триггера
- •Пример 14.1. Использование триггера для реализации ограничений на значение.
- •Пример 14.2. Использования триггера для сбора статистических данных.
- •Пример 14.3. Триггер для обработки операции удаления записи из таблицы
- •Пример 14.4. Триггер для обработки операции изменения записи в таблице
- •Пример 14.5. Исправленный вариант триггера для обработки операции изменения записи в таблице
- •Реализация правил целостности данных
- •Добавление записи в рекурсивную структуру
- •Пример 15.2. Триггер для изменения записи в таблице.
- •Удаление записи из рекурсивной структуры
- •Пример 15.3. Триггер для удаления записи из таблицы.
- •Введение в транзакции
- •Acid-свойства транзакций
- •Блокировки
- •Управление транзакциями
- •Явные транзакции
- •Пример 16.1. Использование точек сохранения
- •Вложенные транзакции
- •"Мертвые" блокировки
- •Уровни изоляции sql Server
- •Insert – право вставлять в таблицу новые строки;
- •Предоставление привилегий пользователям
- •Отмена предоставленных пользователям привилегий
- •Предоставление прав
- •Права на выполнение команд sql
- •Неявные права
- •Запрещение доступа
- •Неявное отклонение доступа
- •Конфликты доступа
- •Пример 17.1. Создание новой базы данных, нового пользователя для этой базы данных, с предоставлением ему всех прав.
- •Sql и прикладные программы
- •Архитектура odbc
- •Источники данных и odbc
- •Взаимодействие с базой данных в Java-программах
- •Загрузка драйвера
- •Создание соединения: класс Connection
- •Создание оператора: класс Statement
- •Получение результатов: класс ResultSet
- •Item - извлечение определенного объекта Parameter.
- •Объект Connection
- •Пример 18.3. Выполнение статического sql-запроса к учебной базе данных из vbScript-сценария.
- •Пример 18.4. Выполнение динамического sql-запроса к учебной базе данных из vbScript-сценария.
Создание соединения: класс Connection
Класс DriverManager - самый верхний в иерархии классов. Он отслеживает информацию о драйвере, о состоянии, регистрирует драйвер в процессе его загрузки и т.д. Когда требуется установить связь, этот класс выбирает драйвер в зависимости от URL JDBC.
Создать соединение с источником данных можно с помощью метода getConnection класса DriverManager.
Для задания источника данных используется соглашения по присвоению имен URL. В соответствии с правилами Internet, JDBC идентифицирует базу данных при помощи URL, который имеет следующую форму:
jdbc:<субпротокол>:<имя, связанное с
СУБД или протоколом>
Имя субпротокола odbc зарезервировано для источника данных формата ODBC. Типичный JDBC URL для БД ODBC выглядит следующим образом:
jdbc:odbc:<DSN-имя ODBC>;
user=<имя пользователя>;pw=<пароль>
Класс Connection ( соединение ) - один из самых важных в JDBC. Он предоставляет широкий спектр возможностей: от создания операторов до обработки транзакций. ОбъектConnection управляет всеми аспектами установки связи. Фактически, Connection - это класс, ведущий к драйверу СУБД. Успешно создав объект Connection для источника данных, программист может взаимодействовать с источниками самыми разными способами. Наиболее распространенный подход состоит в том, чтобы объекты управляли SQL-операторами.
В JDBC имеется три основных типа объектов для операторов:
Statement ;
PreparedStatement ;
CallableStatement.
Оператор SQL может быть выполнен немедленно ( Statement ), откомпилирован ( PreparedStatement ), представлен в виде вызова процедур ( CallableStatement ).
Для создания этих объектов класс Connection предлагает, соответственно, три метода:
createStatement() ;
prepareStatement(String sql) ;
prepareCall(String sql).
Создание оператора: класс Statement
Объект Statement создается методом createStatement() из объекта Connection и лучше всего подходит для SQL-операторов, выполняемых один раз.
Когда запрос возвращает обычные строки данных, для выполнения SQL-оператора необходимо использовать метод executeQuery(String sql). В таком случае запрос - это статический SQL-запрос выборки SELECT. Метод executeQuery(String sql) принимает строку SQL, передает ее источнику данных через диспетчер драйверов, получает набор результатов и возвращает его прикладной программе. Метод возвращает только один набор результатов типа ResultSet. Если необходимо возвращать несколько наборов данных, применяется метод execute(String sql).
Для SQL-операторов, не возвращающих наборы результатов (например UPDATE, DELETE и INSERT ), объект Statement применяет метод executeUpdate(String sql), который берет строку SQL и возвращает целое. Это целое указывает количество строк, обработанных оператором SQL.
Получение результатов: класс ResultSet
Объект ResultSet, представляющий набор результатов, фактически является табличным набором данных, т.е. состоит из рядов данных, организованных в унифицированные столбцы. Программа на языке Java, использующая JDBC, единовременно способна выдать только один ряд данных. Чтобы перейти к следующему ряду, она должна вызвать метод next(). JDBC не предоставляет метод для обратного движения по ResultSet или запоминания рядов ("закладки" в ODBC). Получив ряд, программа может пользоваться позиционным указателем (1 - для первого столбца, 2 - для второго и т.д.) или названием столбца и получить значение поля, вызвав метод getXXXX ( getInt, getByte,getString и т.д.) в соответствии с типом возвращаемых данных.
Class.forName(
"com.ms.jdbc.odbc.JdbcOdbcDriver");
// при создании соединения указывается
// DSN источника данных (mystud),
// имя пользователя (user1), его пароль(123)
Connection myCon=DriverManager.getConnection
("jdbc:odbc:mystud","user1","123");
Statement mySt=myCon.createStatement();
ResultSet rs=mySt.executeQuery(
"SELECT название, тип, цена FROM товар");
while (rs.next()) {
String var1=rs.getString(1);
String var2=rs.getString(2);
int var3=rs.getInt(3);
// значения переменных var1, var2, var3 могут
// далее быть использованы в элементах
// пользовательского графического интерфейса
}
Пример 18.1. Выполнение статического SQL-запроса к учебной базе данных из Java-программы.
Создание оператора: класс PreparedStatement
В случае использования этого объекта программа подготавливает SQL-оператор при помощи метода prepareStatement (String sql) объекта Connection: берется SQL-строка и передается СУБД. Строка проходит через стадии синтаксического анализа, оптимизации и генерирования плана выполнения, но не выполняет оператор SQL. Ссылка на план выполнения возвращается JDBC, который сохраняет ее в объекте PreparedStatement.
Одно из важных свойств метода prepareStatement - возможность обработки входных параметров. Они помечаются в операторе SQL символом "?" на месте фактического значения. В программе Java проводится соответствие между параметрами и методами setXXXX() - setInt, setFloat, setString и т.д., в соответствии с типами входныхпараметров. Все методы setXXXX() принимают в качестве параметров значение параметра и указатель parameterIndex, который равен 1 для первого "?", 2 - для второго и т.д. Выполнение sql-оператора обеспечивается методами объекта PreparedStatement:
executeQuery() - для выполнения запроса выборки SELECT ;
execute() - для выполнения запроса, возвращающего множественные наборы данных ;
executeUpdate() - для выполнения запроса модификации данных INSERT, UPDATE или DELETE.
Объект PreparedStatement обеспечивает более быстрое выполнение оператора SQL и является примером реализации динамического SQL.
Class.forName(
"com.ms.jdbc.odbc.JdbcOdbcDriver");
Connection myCon=DriverManager.getConnection
("jdbc:odbc:mystud","user1","123");
PreparedStatement mySt=myCon.prepareStatement
("INSERT товар(название, тип, цена)
VALUES(?,?,?)");
mySt.setString(1, "Трюфели");
mySt.setString(2, "Конфеты");
mySt.setInt(3,156);
int res=mySt.executeUpdate();
Пример 18.2. Выполнение динамического SQL-запроса к учебной базе данных из Java-программы.
Обращение к базе данных с использованием технологии ADO
Технология Active Data Objects (ADO) - это программное расширение технологии активных серверных страниц ASP, реализованное в Web-сервере Internet Information Server (IIS) компании Microsoft с целью организации подключений к базам данных. В технологии ADO поддерживаются следующие основные функции:
независимо создаваемые объекты;
поддержка хранимых процедур с входными, выходными и возвращаемыми параметрами ;
курсоры различных типов (включая возможность поддержки разных специальных курсоров конечных пользователей);
пакетное обновление;
поддержка ограничений для числа возвращаемых строк или других параметров запроса;
поддержка нескольких наборов данных, возвращаемых хранимыми процедурами или пакетными операторами.
Основными преимуществами технологии ADO является простота использования, высокая скорость, небольшие потребности в оперативной памяти и незначительные затраты дисковой памяти.
Объектная модель ADO определяет набор ( коллекцию ) программируемых объектов, которые могут применяться с Visual Basic, Visual C++, VBScript. Объектная модель ADOразработана для выполнения большинства особенностей интерфейса OLE DB.
Если при помощи интерфейса ODBC операционная система осуществляет, как правило, доступ к реляционным БД, использующим структурированный язык запросов SQL, то интерфейс OLE DB (Object Linking and Embedding for DataBases) является более универсальной технологией для доступа к любым источникам данных.
В интерфейс OLE DB включен механизм провайдеров, под которыми понимаются поставщики данных, находящиеся в надстройке над физическим форматом данных. Их называют сервис-провайдерами, они помогают объединять в однотипную совокупность объекты, связанные с разнообразными источниками данных.
Интерфейс OLE DB может использовать для доступа к источникам данных интерфейс ODBC. В этом случае применяется провайдер OLE DB для доступа к ODBC-данным. Таким образом, интерфейс OLE DB не заменяет интерфейс ODBC, а позволяет организовать доступ к источникам данных через различные интерфейсы, и в том числе через ODBC.
ADO содержит семь объектов, которые инкапсулируют в себе большинство операций с базой данных - Connection ( соединение ), Command ( команда ), Parameter ( параметр), Recordset ( набор данных ), Field (поле), Property (свойство) и Error (ошибка), - а также четыре набора объектов ( коллекции ) - Fields (поля), Properties (свойства),Parameters ( параметры ) и Errors (ошибки).
Объект Connection устанавливает коммуникационную связь между приложением и источником данных, позволяет выполнять команды. Класс Connection включает следующие методы:
Open ( Close ) - установление (закрытие) физического соединения с источником данных;
Execute - выполнение команды для этого соединения ;
BeginTrans, CommitTrans, RollbackTrans - управление транзакциями для текущего подключения, включая вложенные транзакции, если источник данных поддерживает работу с ними.
Объект Command. Команда, направляемая в базу данных, как правило, представляет собой SQL-команду или вызов некоторой хранимой процедуры. Объект Command может настраиваться с помощью параметров, задаваемых посредством создания коллекции объектов класса Parameter. Класс Command включает следующие методы:
Execute - выполнение команды для данного соединения ;
CreateParameter - создание нового объекта класса Parameter.
Коллекция Parameters. Содержит любые параметры, которые используются вместе с данным объектом Command. Класс Parameters содержит следующие методы: