Путевые заметки, или вкус кофе для слонов

Уже догадались, о чем будет статья?

Третий год занимаюсь разработкой крупной системы на Java с использованием СУБД PostgreSQL. Система десктопная, клиент-серверная. Опытного Senior-Java-Developer-а у нас нет, поэтому приходится думать самим. Думать, строить, ломать, строить заново, опять ломать…
За время работы накопился некоторый опыт как по организации непосредственно работы с БД, так и по взаимоувязыванию этих платформ, о котором и хочу рассказать в этой статье.

Опишу выборочно некоторые вопросы, с которыми мы столкнулись при разработке и которые решили.

1. Использование ORM

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

Однако мы работаем с векторной графикой, и оказалось крайне неудобно векторные объекты делать как Persistence-объекты, с соответствующими сеттерами и геттерами. Векторные объекты сложные, с множеством логических свойств (т.е. не только графика, но и логика). Как их маппить в Hibernate — я так и не придумал.
Зато придумал свою структуру описания логических свойств объектов в XML, где для каждого свойства указана таблица и колонка БД, и алгоритмы, которые на этой основе формируют динамические SQL-запросы.
Так что — не используем мы ORM. Работаем через JDBC и используем XML-маппинг собственной разработки.
А зависимость от СУБД все равно имеется, т.к. без хранимых процедур не обойтись (!!!).

2. Организация подключений к БД

PostgreSQL JDBC driver, java.sql.Connection, и вперед — в каждом классе открытие и закрытие подключения.

Так было первые пару месяцев.
Сегодня у нас есть один класс — менеджер подключения, очень простой. Он хранит сам объект — подключение (одно на весь проект!) и следит за тем, чтобы это подключение к БД не использовалось одновременно, особенно несколькими потоками, поскольку, это небезопасно.

3. Организация SQL-запросов в проекте.

В начале разработки в каждом втором классе можно было встретить SQL-запросы. Работали они замечательно.

Но БД менялась параллельно, и в какой-то момент понадобилось взять и переименовать таблицу. Переименовать-то просто, а как быть с проектом? Рефакторинг? Ан нет, переименовать все обращения к таблице в исходном коде можно только с помощью контекстной замены по проекту. Но ведь среда не знает, где у вас имя таблицы, а где одноименная переменная…
После того как на переименование таблицы в БД ушло 2 дня, мы решили сделать отдельный класс, содержащий все SQL-запросы к БД либо в виде констант, либо в виде методов. Все константы именуются по одному принципу "<тип_запроса>_<имя класса>_<смысл запроса>". Например, константа на выборку графических линий, вызываемая в классе Graph, называется SELECT_GRAPH_LINES. Если бы это был метод, то он назывался бы selectGraphLines(). Теперь мы имеем легко меняемый и красивый код с константами вместо sql-текста.

4. Оптимизация выполнения SQL-запросов.

Расскажу не о том, как настраивать PostgreSQL. А об известном способе JDBC — PreparedStatement — executeBatch();

Если говорить о запросах на вставку, то встает вопрос: как быстро вставлять в таблицу сразу помногу строк?
PostgreSQL подерживает multiinsert, т.е. можно писать нечто типа

insert into table (a, b, c) 
values (
(1,2,3),
(4,5,6),
(...)
)

Так вот, формировать мультиинсерты динамически не есть хорошо. Достаточно один раз написать

PreparedStatement stmt = db.prepareStatement("insert into table (a, b, c) values (?, ?, ?)").

а потом в цикле делать

stmt.setString(1,a);
stmt.setString(2,b);
stmt.setString(3,c);
stmt.addBatch();

а потом сделать

stmt.executeBatch()

и все данные вставятся у вас за предельно малое время.
Так например, вставка данных из 1600 xml-файлов по ~10 кБ простыми INSERT заняла более 10 минут. Вставка с использованием executeBatch — около 2 с. Поэтому применять такую схему желательно везде, где используется много однотипных запросов на вставку.

5. Работа с XPath в PostgreSQL

Столкнулся с проблемой, ответ на которую в google тяжело найти, а из документации PostgreSQL — непонятно.
Дано: таблица в БД с полем типа xml.
Требуется: сделать выборку всех записей из таблицы, где некоторый элемент XML имеет значение такое-то.

Ясное дело, что самое простое — сделать это через XPath. Пишем:

SELECT xpath('//child/child/text()', <имя XML-поля>) from таблица

Видим, что все записи у нас пустые. После поисков понимаем, что XML-данные записаны с т.н. дефолтным неймспейсом, т.е. все XML-теги пишутся просто:

<xmltag>блаблабла</xmltag>

а не

<ns:xmltag>блаблабла</ns:xmltag>

Потребовалось немало времени, чтобы понять, что нужно регистрировать namespace по умолчанию, и делать это так:

SELECT xpath('//my:root/my:child/text()', <имя XML-поля>,  ARRAY[ARRAY['my', 'http://example.com']]);

где my — это любое слово на ваш вкус — название неймспейса http:/ /example.com — значение xmlns:

xmlns="http://example.com"/

Причем обязательно надо словом my предварять все элементы в xpath-запросе, а вот атрибуты не надо.

6. Текстовые редакторы и хранимые процедуры PostgreSQL

В SQL-редакторе Netbeans вот такой код

CREATE FUNCTION AAA (a int, b varchar) RETURNS int AS
$body$
BEGIN
...
END
$body$ LANGUAGE plpgsql;

вызовет ошибку. То же произойдет в SQL Workbench и в Eclipse. И еще во многих приложениях, где для выполнения SQL-запросов используется JDBC-драйвер.

А проблема в $body$ — так называемом dollar-quoting, который используется в postgres для обозначения тела функций. $body$ открывает тело функции и закрывает его.
В pgAdmin проблем нет. В SQL Workbench специально обрабатывается данный случай, но при этом используются костыли: требуется обозначать конец тела хранимой процедуры в редакторе специальным символом — alternate delimiter, по умолчанию это /.

А в Netbeans — не работает. И в Eclipse — тоже.

P.S.
Проблем и вопросов за три года конечно было много больше. Это то, что вспомнилось, и чем мне захотелось поделиться.
Эта статья не есть авторитетное заявление «делать надо так». Будет интересно увидеть критику того, как мы решали описанные в статье задачи.

Автор: Koyotter