SQLJet — это независимая чистая реализация с открытым исходным кодом Java основной функциональности СУБД SQLite . SQLJet предоставляет API для создания, чтения и изменения баз данных SQLite. Ниже вы найдете простое руководство, демонстрирующее, как можно использовать библиотеку SQLJet для выполнения основных операций с базой данных в формате SQLite.
Первая основная версия SQLJet (1.0.0) была только что выпущена. Для получения дополнительной информации и последних обновлений посетите домашнюю страницу SQLJet по адресу http://sqljet.com/ .
Содержание учебника
Это очень простое руководство по использованию API SQLJet для работы с базой данных в формате SQLite. Описаны следующие операции:
- Создать новую базу данных и настроить параметры
- Создать таблицу и индексы
- Вставить записи
- Выберите записи в порядке, указанном по индексу
- Поиск записей, соответствующих области или точному значению
- Обновлять и удалять записи
- Удалить таблицу и индекс
Полный рабочий исходный код этого примера доступен как часть проекта SQLJet в репозитории Subversion на Tutorial.java .
Создать новую базу данных
File dbFile = new File(DB_NAME); dbFile.delete(); SqlJetDb db = SqlJetDb.open(dbFile, true); db.getOptions().setAutovacuum(true); db.beginTransaction(SqlJetTransactionMode.WRITE); try { db.getOptions().setUserVersion(1); } finally { db.commit(); }
Ради атомности в этом примере всегда создается новая пустая база данных ( строки 1,2 ), затем для этого файла создается объект SqlJetDb ( строка 4 ), открытый для записи. Поскольку файл еще не существует, он будет создан.
Формат Sqlite поддерживает ряд опций. Некоторые из этих параметров должны быть установлены до того, как что-либо будет изменено в базе данных, даже до начала первой транзакции ( строка 5 ), потому что точный способ выполнения транзакции зависит от этих параметров. Другие параметры должны быть установлены внутри транзакции «запись» ( строка 6 ).
Есть в основном два способа выполнить определенный код как транзакцию. Сначала описано выше ( строки 6 и 10 ) — транзакция записи запускается, а затем фиксируется. Чтобы откатить транзакцию, нужно вызвать db.rollback () вместо db.commit () , например, в случае, если исключение выдается из блока try / catch.
Другой способ — создать подкласс класса SqlJetTransaction и запустить его с помощью метода SqlJetDb.runTransaction (…) :
Object result = db.runTransaction(new ISqlJetTransaction() { public Object run(SqlJetDb db) throws SqlJetException { db.getOptions().setUserVersion(1); return true; } } SqlJetTransactionMode.WRITE);
Вышеуказанный метод более удобен в том смысле, что транзакция будет автоматически откатываться в случае, если исключение выдается из метода run или фиксируется в случае отсутствия исключений. С другой стороны, использование анонимных или внутренних классов может быть неудобным, и тогда можно предпочесть первый способ запуска транзакции. В этом примере мы будем использовать первый способ сэкономить на отступе и фигурных скобках.
Обратите внимание, что когда вам больше не нужно работать с базой данных, имеет смысл закрыть ее, вызвав метод SqlJetDb.close () :
SqlJetDb db = SqlJetDb.open(dbFile, true); try { ... ... } finally { db.close(); }
Создать таблицу и индексы
Мы создадим одну таблицу с тремя полями и двумя индексами. Третий индекс (для поля первичного ключа) будет создан автоматически. В формате SQLite схема базы данных хранится в виде простых операторов SQL, и аналогичные операторы используются для создания таблиц и индексов.
Мы используем следующие заявления:
CREATE TABLE employees (second_name TEXT NOT NULL PRIMARY KEY , first_name TEXT NOT NULL, date_of_birth INTEGER NOT NULL) CREATE INDEX full_name_index ON employees(first_name,second_name) CREATE INDEX dob_index ON employees(date_of_birth)
И следующий код:
db.beginTransaction(SqlJetTransactionMode.WRITE); try { db.createTable(createTableQuery); db.createIndex(createFirstNameIndexQuery); db.createIndex(createDateIndexQuery); } finally { db.commit(); }
Первый индекс full_name_index является составным — он индексирует строки по значениям двух полей — first_name и second_name. Это означает, что поиск с использованием двух значений (имя и имя) будет использовать этот индекс и будет работать быстро.
dob_index — это простой индекс поля целочисленного типа. SQLite «целое число» всегда представляется как длинный подписанный в Java. Здесь мы используем тип длинного значения для хранения дат.
Наконец, SQLJet создаст еще один индекс, потому что одно из полей таблицы ( second_name ) объявлено как PRIMARY KEY . Этот индекс будет иметь имена sqlite_autoindex_employees_1, и это имя будет доступно позже, так что мы будем использовать этот индекс также.
Обратите внимание, что схема базы данных создается в транзакции записи.
Вставить записи
Теперь давайте заполним таблицу наших сотрудников, которую мы только что создали:
Calendar calendar = Calendar.getInstance(); calendar.clear(); db.beginTransaction(SqlJetTransactionMode.WRITE); try { ISqlJetTable table = db.getTable(TABLE_NAME); calendar.set(1991, 4, 19); table.insert("Prochaskova", "Elena", calendar.getTimeInMillis()); calendar.set(1967, 5, 19); table.insert("Scherbina", "Sergei", calendar.getTimeInMillis()); calendar.set(1987, 6, 19); table.insert("Vadishev", "Semen", calendar.getTimeInMillis()); calendar.set(1982, 7, 19); table.insert("Sinjushkin", "Alexander", calendar.getTimeInMillis()); calendar.set(1979, 8, 19); table.insert("Stadnik", "Dmitry", calendar.getTimeInMillis()); calendar.set(1977, 9, 19); table.insert("Kitaev", "Alexander", calendar.getTimeInMillis()); } finally { db.commit(); }
Приведенный выше код довольно прост: мы выбираем таблицу по имени ( сотрудники ), затем вызываем метод table.insert (…), передавая значения всех полей для каждой строки. Эти поля являются second_name , first_name и, наконец, date_of_birth .
SQLJet обновляет индексы автоматически при любых изменениях в таблицах, поэтому нет необходимости вызывать другие методы.
Выберите записи в порядке, указанном по индексу
Прежде чем смотреть на код, который выбирает записи из таблицы, давайте представим служебный метод, который упрощает отображение этих записей. Этот метод принимает ISqlJetCursor — объект типа итератора, который представляет упорядоченный набор строк — и выводит эти строки:
private static void printRecords(ISqlJetCursor cursor) throws SqlJetException { try { if (!cursor.eof()) { do { System.out.println(cursor.getRowId() + " : " + cursor.getString(FIRST_NAME_FIELD) + " " + cursor.getString(SECOND_NAME_FIELD) + " was born on " + formatDate(cursor.getInteger(DOB_FIELD))); } while(cursor.next()); } } finally { cursor.close(); } }
Этот служебный метод выполняет итерацию по упорядоченному набору строк с помощью метода cursor.next () до тех пор, пока курсор не окажется за последней строкой в упорядоченном наборе — cursor.next () не вернет false, а cursor.eof () вернет true .
В каждый конкретный момент своего времени курсор указывает на одну из строк в упорядоченном наборе, который он представляет (или на нулевую строку в случае достижения конца упорядоченного набора строк), и позволяет пользователю получить значения полей для самой строки указывает на. В дополнение к полям, определенным схемой, каждая строка имеет rowId — уникальное длинное целое число, которое по умолчанию равно номеру строки (на основе 1).
Когда курсор больше не нужен, метод cursor.close () освободит связанные ресурсы и сделает этот экземпляр курсора недействительным.
Теперь, когда был введен служебный метод, легко написать код, который выбирает записи, и затем распечатать:
db.beginTransaction(SqlJetTransactionMode.READ_ONLY); try { printRecords(table.order(table.getPrimaryKeyIndexName())); } finally { db.commit(); }
Метод table.order (String indexName) возвращает все строки в таблице в порядке, определенном указанным индексом. В этом случае мы используем индекс, который был автоматически создан для поля первичного ключа.
Обратите внимание, что мы выполняем код выше в транзакции READ_ONLY . Это помогает нам убедиться, что никакие параллельные операции записи не влияют на наш набор строк.
Другие примеры того, как можно выбирать строки:
db.beginTransaction(SqlJetTransactionMode.READ_ONLY); try { printRecords(table.order(FULL_NAME_INDEX)); } finally { db.commit(); }
— в порядке, определенном составным индексом, то есть отсортированным по конкатенации значений полей first_name и second_name.
db.beginTransaction(SqlJetTransactionMode.READ_ONLY); try { printRecords(table.open()); } finally { db.commit(); }
— в порядке, определенном rowId , т. е. отсортированные по порядку строки были добавлены в таблицу.
db.beginTransaction(SqlJetTransactionMode.READ_ONLY); try { printRecords(table.order(DOB_INDEX).reverse()); } finally { db.commit(); }
— от записей с меньшим значением в поле date_of_birth до записей с большими значениями.
Обратите внимание на использование метода ISqlJetCursor.reverse () — он «переворачивает» курсор, возвращая его зеркальную копию, которая будет перебирать строки в обратном порядке. «Перевернутый» курсор оборачивает исходный курсор, так что позже он меняет свою позицию, когда предыдущий повторяется. Достаточно закрыть перевернутый курсор, чтобы закрыть и оригинальный.
Поиск записей, соответствующих области или точному значению
Аналогичный подход на основе курсора используется для выбора только определенных записей — тех, которые соответствуют определенным критериям. Разница в том, что метод table.lookup (indexName, …) используется вместо table.order (indexName, …) .
Метод table.lookup (…) принимает имя индекса и значения полей для выбора записей. Это легко понять, посмотрев на примеры:
db.beginTransaction(SqlJetTransactionMode.READ_ONLY); try { printRecords(table.lookup(FULL_NAME_INDEX, "Alexander")); } finally { db.commit(); }
— получает все записи с первой частью full_name_index, равной ‘Alexander’. Это распечатывает две записи:
6 : Alexander Kitaev was born on Oct 19, 19774 : Alexander Sinjushkin was born on Aug 19, 1982
И с более строгими критериями:
db.beginTransaction(SqlJetTransactionMode.READ_ONLY); try { printRecords(table.lookup(FULL_NAME_INDEX, "Alexander", "Kitaev")); } finally { db.commit(); }
— получает все записи с указанием обеих частей full_name_index . Это печатает одну запись:
6 : Alexander Kitaev was born on Oct 19, 1977
Обратите внимание, что в настоящее время SQLJet позволяет только искать строку (указав ее начало), используя индексы, а не внутри строки (указав ее часть или регулярное выражение). Эта функциональность будет доступна в следующих версиях SQLJet.
Другой способ выбрать критерии соответствия записей — указать область , а не точные значения полей. Это можно сделать с помощью метода table.scope (…), который принимает имя индекса, начальное и конечное значения диапазона и возвращает наш старый курсор друга :
db.beginTransaction(SqlJetTransactionMode.READ_ONLY); try { printRecords(table.scope(FULL_NAME_INDEX, new Object[] {"B"}, new Object[] {"I"})); } finally { db.commit(); }
— печатает все записи с full_name_index (который является составной частью first_name и second_name ) в диапазоне от B до I включительно. Здесь это означает, что будут выбраны все сотрудники с именами, начинающимися с буквы B до I.
Calendar calendar = Calendar.getInstance(); calendar.setTime(new Date(System.currentTimeMillis())); calendar.add(Calendar.YEAR, -30); db.beginTransaction(SqlJetTransactionMode.READ_ONLY); try { printRecords(table.scope(DOB_INDEX, new Object[] {Long.MIN_VALUE}, new Object[] {calendar.getTimeInMillis()})); } finally { db.commit(); }
— печатает все записи со значением date_of_birth в области видимости между Long.MIN_VALUE и данными тридцать лет назад.
Обновлять и удалять записи
Для изменения (обновления) или удаления записей SQLJet использует следующий алгоритм:
- Начать WRITE транзакцию.
- Выберите строки, которые вы хотите изменить или удалить, другими словами получить курсор .
- Итерация по обновлению курсора или удалению строк по мере продвижения.
В приведенном ниже примере удаляются записи всех сотрудников старше тридцати лет ( строки 08:14 ). Затем он добавляет еще одну запись ( строка 17 ) и изменяет значение поля date_of_birth для всех записей в таблице ( строки 19:28 )):
Calendar calendar = Calendar.getInstance(); calendar.setTime(new Date(System.currentTimeMillis())); calendar.add(Calendar.YEAR, -30); db.beginTransaction(SqlJetTransactionMode.WRITE); try { // delete ISqlJetCursor deleteCursor = table.scope(DOB_INDEX, new Object[] {Long.MIN_VALUE}, new Object[] {calendar.getTimeInMillis()}); while (!deleteCursor.eof()) { deleteCursor.delete(); } deleteCursor.close(); // insert table.insert("Smith", "John", 0); // update calendar.setTime(new Date(System.currentTimeMillis())); ISqlJetCursor updateCursor = table.open(); do { updateCursor.update( updateCursor.getValue(SECOND_NAME_FIELD), updateCursor.getValue(FIRST_NAME_FIELD), calendar.getTimeInMillis()); } while(updateCursor.next()); updateCursor.close(); } finally { db.commit(); }
Разумеется, приведенный выше код запускается как транзакция WRITE и аналогичен table.insert (…) , методы delete и update выполняют все необходимые обновления индексов.
Drop таблица и индексы
Чтобы удалить (удалить) таблицу и индексы, используйте методы SqlJetDb.dropTable (String tableName) и SqlJetDb.dropIndex (String indexName) .
Это довольно ясно и более интересно, как выяснить, какие таблицы и индексы содержатся в конкретной базе данных. SQLJet предоставляет API для чтения схемы базы данных, а выбор имен всех таблиц и индексов прост:
db.beginTransaction(SqlJetTransactionMode.WRITE); try { Set<String> indices = db.getSchema().getIndexNames(); Set<String> tables = db.getSchema().getTableNames(); for (String tableName : tables) { ISqlJetTableDef tableDef = db.getSchema().getTable(tableName); Set<ISqlJetIndexDef> tableIndices = db.getSchema().getIndexes(tableName); for (ISqlJetIndexDef indexDef : tableIndices) { if (!indexDef.isImplicit()) { db.dropIndex(indexDef.getName()); } } db.dropTable(tableName); } } finally { db.commit(); }
Приведенный выше код получает имена всех таблиц, хранящихся в базе данных, и список индексов для каждой таблицы, а затем удаляет эти индексы и таблицы. Нет необходимости удалять сначала индексы, а затем таблицу — удаление таблицы автоматически удаляет индексы. Объекты ISqlJetTableDef и ISqlJetIndexDef предоставляют подробную информацию о таблице и индексе, включая все имена, поля и их типы.
Первая основная версия SQLJet (1.0.0) была только что выпущена. Для получения дополнительной информации и последних обновлений посетите домашнюю страницу SQLJet по адресу http://sqljet.com/ .
Если у вас есть какие-либо вопросы по этой статье или по SQLJet в целом, пожалуйста, оставьте свои комментарии здесь или свяжитесь с нами по адресу support@sqljet.com .