DbLib + Sql2o: Работа с базами данных

[fromgate]

DbLib + Sql2o: Работа с базами данных

Вчера я выложил новую версию DbLib 0.1.0, которая теперь включает в себя ещё и библиотеку Sql2o.

Конечно же и без неё можно было работать с базами данных, особенно если учитывать, что и обновлённая после трёх лет затишья ORMLite также включена в DbLib.

Однако ORMLite — это всё таки ORM, которая зачастую может показаться избыточной, а условиях переноса плагинов, допустим, с PocketMine не всегда удобной: всё-таки для многих SQL-запросы привычнее.

И именно в этой ситуации Sql2o оказывается очень неплохим инструментом:
Во-первых, здесь используются привычные и понятные запросы. Т.е. если Вы портируете плагин с PocketMine, то запрос будет тем же и менять его не нужно.
Во-вторых, тут есть возможность получить результат выполнения запроса в виде объекта определённого класса, а не заниматься его приведением к работоспособному виду.
В-третьих, сам код при использовании этой библиотеки становится гораздо лаконичнее, чем если бы Вы использовали традиционную работу через драйвер jdbc.

Подробная документация о Sql2o приведена на сайте разработчика: http://www.sql2o.org/

Здесь же я кратко рассмотрю несколько моментов, которые я реализовал в виде класса для плагина-примера, в котором рассмотрены все варианты работы с базами данных (применительно к DbLib). Исходный код плагина приведёнт на гитхабе.

1. Что представляют собой запросы Sql2o
Тут ничего хитрого, это обычная строка. Единственное отличие, в этой строке можно указать параметры, значение которхых может быть указано непосредственно перед выполнением.
Это позволяет описать запрос один раз, а не создавать его динамически.

В примере, это сделано так:

	private final static String createSQL = "create table if not exists sql2o_test (name varchar(20), lastname varchar(20))";
	private final static String insetQuery = "insert into sql2o_test (name, lastname) values (:name, :lastname)";
	private final static String selectQuery = "select * from sql2o_test where name=:name";

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

2. Подключение к базе данных
Для подключения к базе данных Вы можете воспользоваться способом, предоставляемым самой библиотекой:

Sql2o sql2o = new Sql2o(DB_URL, USER, PASS);

либо можно воспользоваться методами предоставляемыми DbLib:

# Создать новый объект Sql2o для базы MySQL
DbLib.getSql2oMySql (String host, int port, String database, String userName, String password);
 
# Создать новый объект Sq2lo для базы любого типа, на основе jdbc-url
DbLib.getSql2o(String url, String userName, String password);
 
# Получить стандартный объект Sq2lo, соответствующей базе, настроенной в конфиге DbLib
DbLib.getSql2o();

Все эти методы возвращают объект класса Sql2o, который можно будет использовать точно так как это описано в документации Sql2o.

3. Создание таблицы в базе данных
Создание таблицы сводится к выполнению SQL запроса "CREATE TABLE IF NOT EXISTS....".
Это первый запрос в моём примере:

private final static String createSQL = "create table if not exists sql2o_test (name varchar(20), lastname varchar(20))";

Для выполнения этого запроса необходимо будет выполнить следующий код:

		try (Connection con = sql2o.open()){
			con.createQuery(createSQL).executeUpdate();
			con.close();
		}

После успешного выполнения этого кода, можно считать, что таблица создана и существует.

4. Запись данных в таблицу
Для выполнения записи в таблицу, нужно вновь подготовить SQL-запрос. В примере, я использую следующий:

	private final static String insetQuery = "insert into sql2o_test (name, lastname) values (:name, :lastname)";

Здесь я хочу обратить Ваше внимание на параметры :name, :lastname. Это как раз и есть те параметры, которым необходимо будет задавать конкретные значения.

Если бы этих параметров не было (а там явно было бы прописано добавляемые в таблицу значения), то достаточно было бы просто выполнить запрос таким образом:

		try (Connection con = sql2o.open()){
			con.createQuery(insetQuery).executeUpdate();
			con.close();
		}

Но в нашем случае, нужно "прописать" значения используя метод addParameter. Ну и в этом случае всё удобно оформить в виде процедуры:

	public static void insertData(String name, String lastName){
		try (Connection con = sql2o.open()){
			con.createQuery(insetQuery).addParameter("name",name).addParameter("lastname",lastName).executeUpdate();
			con.close();
		}
	}

5. Чтение данных из таблицы
Естественно, это снова SQL-запрос.
В нашем примере такой:

	private final static String selectQuery = "select * from sql2o_test where name=:name";

Здесь тоже используется параметр "։name" - т.е. можно получать все записи, у которых значение в колонке name соответствует заданному.

В результате, понятное дело будет таблица вида: name, lastname (ибо она такая у нас и есть), а количество столбцов может быть разным:
0 - если ничего не найдено
1 - если есть одна запись с указанными именем (и больше если таких записей больше).
Соответственно результат - это коллекция List.

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


Для этого, нужно будет создать такой класс:

public class Sql2oTable {
 
	String name;
	String lastname;
}

Обратите внимание, что поля в нём называются точно так же как названия колонок в таблице базы данных.

Ну а само получение данных будет выглядеть так:

		List<Sql2oTable> result = null;
		try (Connection con = sql2o.open()){
			result = con.createQuery(selectQuery).addParameter("name","dima").executeAndFetch(Sql2oTable.class);
			con.close();
		}

Приводить пример, как пользоваться данными из полученного List'а, думаю не нужно.

Остановлюсь лишь на том, что вместо List<Sql2oTable> можно было бы использовать и обычный List<String> в том случае, когда возвращаемый результат содержит не несколько колонок, а одну.

Выглядеть это может примерно так:

		String query = "select lastname from sql2o_test where name=:name";
		List<String> lastNames= null;
		try (Connection con = sql2o.open()){
			lastNames = con.createQuery(query).addParameter("name","dima").executeScalarList(String.class);
			con.close();
		}

Заключение
Приведённые примеры, естественно, не могут претендовать на полное руководство по Sql2o, но демонстрируют общий подход к работе с базами данных при помощи этой библиотеки.

Плагин, который я использовал в качестве примера прилагается к этому сообщению (исходный код, как я уже говорил, на гитхабе)
Ну а более полное руководство об использовании Sql2o, можно просмотреть на сайте http://www.sql2o.org/