Программные интерфейсы СУБД

Все СУБД имеют программные интерфейсы, которые позволяют подключаться к ним из сторонних приложений. Эти интерфейсы могут иметь вид программных библиотек различного уровня сложности и/или абстракции, а могут представлять собой драйверы для распространенных систем взаимодействия с СУБД, как например JDBC (Java Database Connectivity) или ODBC (Open Database Connectivity).

Все интерфейсы должны реализовывать следующие функции:

Подключение к СУБД : Подключение к серверу СУБД подразумевает, как правило, указание пользователя, подключающегося к базе данных, пароля или другого способа аутентификации, и, собственно, базы данных, с которой планируется производить работу
Выполнение SQL-команд : В подавляющем большинстве случаев, после успешного подключения к СУБД, программный интерфейс позволяет выполнять SQL-команды непосредственно.
Получение данных от СУБД : Поскольку любой SQL-запрос возвращает множество кортежей, часто – особенно в языках со статической типизацией – требуются специальные средства для получения результатов запросов. Обычно это функции, позволяющие получать и итерировать полученные записи, а так же получать и преобразовывать типы значений столбцов конкретной записи.

Программные библиотеки

Программные библиотеки могут, в зависимости от продукта и целевого языка, иметь процедурную, объектно-ориентированную или другую архитектуру (например, некоторые библиотеки для функциональных языков программирования имеют интерфейс основанный на монадах).

Они могут разрабатываться как компанией, выпускающей СУБД, так и сторонними разработчиками.

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

.Net (C#, Visual Basic, etc)
C++
C
Python
PHP
Perl
Ruby

Так же, забегая вперед, предоставляются ODBC- и JDBC-драйверы.

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

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

C++

MySQL предоставляет объектно-ориентированный интерфейс для С++, в целом похожий на JDBC, который называется Connector/C++.

Соответствующие классы определены в следующих заголовочных файлах (перечислены типичные):

#include <mysql_connection.h>

#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>

Так же требуется подключить библиотеку libmysqlcppconn.

Чтобы подключиться к СУБД, необходимо получить объект типа sql::Connection от экземпляра объекта sql::mysql::MySQL_Driver, который, в свою очередь, можно получить вызовом sql::mysql::MySQL_Driver::get_mysql_driver_instance().

Например:

sql::mysql::MySQL_Driver *driver = sql::mysql::MySQL_Driver::get_mysql_driver_instance();

sql::Connection *con = driver->connect("tcp://127.0.0.1:3306", "user", "password");

//...

delete con; // Удаление объекта закрывает соединение

Выполнение запросов осуществляется при помощи объектов типа sql::Statement, которые создаются при помощи метода createStatement() объекта sql::Connection.

Например:

sql::Statement *stmt = con->createStatement();

stmt->execute("USE database");
stmt->execute("DROP TABLE IF EXISTS test");
stmt->execute("CREATE TABLE test(id INT, label CHAR(1))");
stmt->execute("INSERT INTO test(id, label) VALUES (1, 'a')");

// ...

delete stmt;

Результаты возвращаются в виде объекта sql::ResultSet. Получить результат запроса можно через метод getResultSet() объекта sql::Statement.

stmt->execute("SELECT id, label FROM test ORDER BY id ASC");

sql::ResultSet *res = stmt->getResultSet();

// В отсутствие результатов, getResultSet() == NULL

if(res)
  while (res->next()) {
    // Можно использовать номера элементов кортежа
    cout << "id = " << res->getInt(1);
    // Или названия колонок
    cout << ", label = '" << res->getString("label") << "'" << endl;
  }

delete res;

Если запрос возвращает несколько результатов (например, если запрос – вызов хранимой процедуры), то возможно несколько раз вызывать getResultSet() для получения следующих наборов.

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

sql::PreparedStatement *prep_stmt = con->prepareStatement("INSERT INTO test(id, label) VALUES (?, ?)");

prep_stmt->setInt(1, 1);
prep_stmt->setString(2, "a");
prep_stmt->execute();

prep_stmt->setInt(1, 2);
prep_stmt->setString(2, "b");
prep_stmt->execute();

delete prep_stmt;

Для работы с транзакциями, следует, во-первых, отключить автоподтверждение:

con -> setAutoCommit(0);

Затем можно подтверждать и отменять транзакции используя соответственно методы con->commit() и con->rollback().

Точки сохранения создаются с помощью методов Connection::setSavepoint(const std::string& name), откат к ним производится через void Connection::rollback(Savepoint * savepoint), а удаление через Connection::releaseSavepoint(Savepoint * savepoint)

Например:

PreparedStatement *prep_stmt = con -> prepareStatement ("INSERT INTO City (CityName) VALUES (?)");

prep_stmt -> setString (1, "London, UK");
prep_stmt -> executeUpdate();

Savepoint *savept = con -> setSavepoint ("SAVEPT1");

prep_stmt -> setString (1, "Paris, France");
prep_stmt -> executeUpdate();

con -> rollback (savept);
con -> releaseSavepoint (savept);
delete savept;

con -> commit();

Кроме “родной” библиотеки, существуют сторонние библиотеки общего назначения, например, Qt, включающие компоненты для работы с реляционными СУБД.

ODBC

ODBC – стандартный интерфейс для доступа к СУБД, разработанный в 1990-х компанией Microsoft. Впоследствии, ODBC, являющийся открытым стандартом, был перенесен на множество платформ.

Существует достаточно много библиотек для работы с ODBC. Поддержка так же встроена во многие продукты Microsoft, например Excel и Access.

Однако, настройка окружения для работы с ODBC оказывается несколько сложнее, чем хотелось бы.

JDBC

JDBC – стандарт взаимодействия приложений Java с различными СУБД, и реализован в виде пакета java.sql, входящего в состав Java SE.

Интерфейс JDBC в целом не зависит от используемой СУБД. Связь с конкретной СУБД осуществляется при помощи драйвера, поставляемого разработчиком СУБД.

Модули, использующие JDBC, могут импортировать соответствующие объявления через

import java.sql.*;

Драйвер MySQL (если установлен) можно загрузить следующим образом:

Class.forName("com.mysql.jdbc.Driver").newInstance();

Замечание: .newInstance() на стандартной реализации Java не требуется, однако некоторые реализации не импортируют модуль до первого инстанцирования, поэтому в целях совместимости такой вариант предпочтителен.

Соединение с БД представляется объектом Connection, который может быть создан через вызов DriverManager.getConnection():

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_name", "user", "password");

Если соединение установить по каким-то причинам не удается, то getConnection возвращает null.

SQL-выражения выполняются с помощью объектов типа Satement, создаваемым через Connection::createStatement():

Statement stmt = conn.createStatement();

Выполнение выражений производится с помощью Statement::execute(), а получение результатов – через Statement::getResultSet()

ResultSet rs = null;
if (stmt.execute("SELECT foo FROM bar")) {
    rs = stmt.getResultSet();
}

По окончании работы, следует освобождать ресурсы ResultSet и Statement при помощи метода close(). При закрытии Statement, все связанные ResultSet так же освобождаются.

Так же существуют PreparedStatement и CallableStatement. Второй позволяет использовать выходные параметры вызываемых процедур. Кроме того, CallableStatement позволяет устанавливать параметры по имени в хранимой процедуре:

CallableStatement cStmt = conn.prepareCall("{call demoSp(?, ?)}"); // Фигурные скобки обязательны!

// Тип выходных и входных-выходных параметров должен быть объявлен до вызова хранимой процедуры
cStmt.registerOutParameter("outputParam", Types.INTEGER);

cStmt.setString("inputParam", "foo");

cStmt.execute();

ResultSet rs = cStmt.getResultSet();

int outputValue = cStmt.getInt("outputParam");

Здесь полагается, что существует процедура

demoSp(IN inputParam VARCHAR(255), OUT outputParam INT)

Получение значений из ResultSet производится итерацией по записям:

stmt.execute(
  "select COF_NAME, SUP_ID, PRICE, " +
  "SALES, TOTAL " + "from " + dbName + ".COFFEES"
  );
ResultSet rs = stmt.getResultSet();
while (rs.next()) {
    String coffeeName = rs.getString("COF_NAME");
    int supplierID = rs.getInt("SUP_ID");
    float price = rs.getFloat("PRICE");
    int sales = rs.getInt("SALES");
    int total = rs.getInt("TOTAL");
}

ORM-системы

ORM-системы предназначены для решения проблемы несоответствия между объектно-ориентированной и реляционной моделями данных. Представляют собой комплексы программного обеспечения, которые позволяют на основе объектной схемы генерировать схему базы данных и код для взаимодействия приложения с СУБД, либо облегчают задачу связывания объектного и реляционного представлений.

Большим преимуществом использования ORM-систем является значительное уменьшения количества необходимого однообразного кода и значительное упрощение модели взаимодействия.

Так, например, ORM-система может позволить писать код в духе такого:

Person p = database.GetPerson(10);
String name = p.getFirstName();

Однако, ORM-системы не лишены недостатков. Нередко автоматически сгенерированная схема не будет нормализованна, что приведет к высокой вероятности возникновения аномалий (дублирования). Кроме того, если “что-то пошло не так”, часто бывает сложно определить, почему, а сгенерированные ORM-системой запросы нередко катастрофически неоптимальны.

Из популярных ORM-систем можно выделить Hibernate для Java и ODB для C++. Оба поддерживают генерацию схемы из аннотированных объявлений классов, и имеют встроенные языки запросов, позволяющие производить выборки по объектам.

Например, описание класса в ODB

#pragma db object table("people")
class person {
  // ...

private:
  friend class odb::access;
  person ();

  #pragma db id auto
  unsigned long id_;

  string first_;
  string last_;

  #pragma type("INT UNSIGNED")
  unsigned short age_;
};

Операции в ODB

unsigned long joe_id = 1;
person john("John", "Doe", 31);
person jane("Jane", "Doe", 29);  

// Начало транзакции
transaction t(db.begin ());

// Сохранение в БД
db.persist(john);
db.persist(jane);

// Загрузка из БД
person *joe (db.load<person>(joe_id));

// Обновление в БД
jane.age(jane.age() + 1);
db.update(jane);

// Удаление из БД
db.erase(jane);

// Подтверждение транзакции
t.commit ();

Запрос в ODB

typedef odb::query<person> query;
typedef odb::result<person> result;

transaction t(db.begin ());

result r(db.query<person> (query::last == "Doe" && query::age < 30));

for(result::iterator i (r.begin ()); i != r.end (); ++i)
{
  cout << "Hello, " << i->first () << endl;
}

t.commit ();

Очевидна некоторая трудность с выполнением сложных статистических запросов. Ее можно избежать, используя SQL-запросы непосредственно, однако это нередко сводит на нет многие преимущества ORM-систем.

Программные интерфейсы СУБД. ORM-системы.

Программные интерфейсы СУБД

Программные библиотеки

C++

ODBC

JDBC

ORM-системы