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



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



    Третий год занимаюсь разработкой крупной системы на 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.
    Проблем и вопросов за три года конечно было много больше. Это то, что вспомнилось, и чем мне захотелось поделиться.
    Эта статья не есть авторитетное заявление «делать надо так». Будет интересно увидеть критику того, как мы решали описанные в статье задачи.

    Комментарии 44

      +2
      Попробуйте вместо работы с БД через голый JDBC, использовать Spring-Jdbc, вкусная штука, код становится приятнее, заодно и любой хитрый маппинг сможете сделать. Да и организация подключений к БД займется Spring.
        +2
        1. XML mapping для генерации кода или в рантайме используете?

        2. Вы сами сделали connection pool? А зачем? Почему не использовали Spring?

        3. Лучше по моему хранить такие вещи вообще не в коде, а в отдельных файлах. На iBatis не смотрели?
          +2
          Spring я смотрел, но мне на тот момент показалось, что это довольно тяжеловесный фреймворк, который не стоит использовать только для задач подключения. Если использовать Spring — то через него нужно организовывать всю работу с БД. Хотя, наверное, это правильнее, мы еще попробуем оптимизировать эту часть.

          XML-маппинг у нас сделан следующим образом: есть библиотеки графических объектов, которые при запуске системы загружаются в память. На этапе загрузки идет считывание файла XML-логики для каждого библиотечного элемента, на основе которой к графике объекта добавляются свойства. Для каждого свойства в XML прописаны тип, таблица и колонка БД.
          Когда идет формирование SQL-запросов, то данные берутся из самих свойств, т.е. не с XML работаем уже, а с объектами-свойствами. И в коде ничего не хранится.
            0
            Я бы тоже не стал тянуть с собой весь spring. Для организации пула подключений можно использовать библиотеку c3p0, надо только быть уверенным в том, что организация пула подключений даст выигрыш в вашей архитектуре.
              0
              Олдскульный десктопный клиент-сервер :)
              Когда-то подобное писал еще на delphi, только «словарь» приложения у меня был не в XML, а тоже в базе данных. Под конец и логика практически вся переехала на сервер (хранимые процедуры; а то, что должно выполняться на клиенте — в виде скриптов, хранимых в блобах, и исполняемых интерпретатором). Приложения до сих пор кое-где работают, хотя обновлений не было года 3.
              По опыту скажу, что connection pool в таких приложениях нужен редко (все запросы у вас и так в 1 потоке), а вот без кеширования обойтись трудно. Вот тут и можно использовать возможности уже готовых фреймворков, в том числе Hibernate
            –2
            Мне кажется что отсуствие грамотного java разработчика у вас скзалось, все таки не зря начиная проект, хоть 1 программист должен иметь достаточный опыт для построения архитектуры. И Hibernate бы тут ложился и много чего другого вкусного можно использовать было, как и писали выше Spring.
              +6
              зачем использовать хибернейт если он не нужен?
                –4
                Чтоб не задаваться вопросом «как переименовать табличку и ничего не сломать»?
                  –1
                  Ну я не знаю что тут ответить, а зачем воогбще нужен хибернейт? Помоему он совсем не нужен если так то посомтреть.
                    0
                    Hibernate — удобная штука, а то бы никто его не использовал. Вы можете организовать хорошую связь с бд, и вам не надо париться с вытаскиванием информации с PreparedStatement и сложными запросами SQL, вам просто достаточно грамотно связать данные и БД.
                    Но и в Хибернейт есть свои минусы, как и в любой другой технологии. Работу с очень большими БД лучше не осуществлять через Хибернейт(здесь стоит использовать JDBC).

                      –2
                      хибернейт не способен делать оптимальные сложные запросы.
                        0
                        Оу, можно подумать, что человек способен %) На собеседовании бывает сложно найти человека, который на бумажке запрос из двух табличек осилит. А вопрос про HAVING ставит в тупик 99% кандидатов.
                          0
                          Ну тут все просто, надо понять кого мы собеседумаем, если это молодой специалист то логично представить что он мало что знает и ждать чудес не надо, ибо врятли он с этим сталкивался.
                          ну а если предложить зп более 100 000 есть вероятность что будут приходить больше грамотные люди с пониманием дела.
                            0
                            Собеседуем PHP программиста, который будет писать под Wordpress и Битрикс, с окладом в районе 50-60 т.р. на испытательном сроке. Дальше можно поднять до 70-и.

                            Про молодого специалиста, вы что под этим понимаете? Человека который вчера открыл книгу «веб-программирование для чайников»?

                            Я под молодым специалистом понимаю человека который хотя-бы назовет различия между разными типами JOIN и опишет несколько знакомых шаблонов проектирования. Иначе это не специалист.

                              0
                              Простите, не уточните город, чтобы я правильно мог оценить уровень зарплат?
                                0
                                Москва
                            –1
                            А вопрос про HAVING ставит в тупик 99% кандидатов
                            — и что? Если человек работает с базами данных но не владеет предметом то он не подходит. Очень просто.

                            Автоматическое создание сложных запросов это примерно как автоматический перевод с английского на русский. Возможно он будет приемлимым когда-нибудь. Сейчас он выдаёт результат несравнимый с ручным переводом.

                            Вы предлагаете снизить качество продукта из-за нехватки специалистов нужного уровня? Да, это выход. Но хорошего в этом ничего нет.
                              0
                              ORM не может снизить качество продукта. Как и нож не может убить человека. Это инструмент. И я правильно заметил, что далеко не всякий девелопер в наше время может грамотно составлять запросы, но каждый может гордо заявлять, что ORM-ы для слабаков =).
                                –1
                                ORM поможет тем кто не знает SQL. Это правильно отмечено. На безрыбье и рак рыба.

                                Тот кто знает SQL может запустить профайлер, посмотреть что этот ORM генерит в качестве запросов и сделать выводы о качестве нагенерённого.
                                  0
                                  От своего опыта скажу, что все зависит от прикладной области. Если у вас сайт с тяжелой посещаемостью и чудо-технологиями, это одно. Если вы делаете аппликуху на 10-100 юзеров с развесистым бакендом — другое.

                                  В первом случае ОРМ может запросто решать рутину и сносно уживаться с возможностью делать кастомные запросы на чистом SQL, во втором ОРМ покроет 120% потребностей разработчика и можно будет вообще забыть о внутренностях БД.
                  +4
                  Для организации пула коннектов удобно использовать DBCP из apache commons, кстати тот же Spring c ним замечательно работает.
                    0
                    DBCP не очень себя зарекомендовал. Ничего хорошего в интернетах про него не пишут, да и опыт собственного тестирования это подтвердил. Лучше использовать c3p0.
                      0
                      Раньше в dbcp действительно были неприятные баги, но в свежих версиях всё нормально уже.
                        0
                        Мой опыт говорит ровно обратное: commons-dbcp работает отлично (используем во многих проектах), а вот с c3p0 под большой нагрузкой были проблемы.
                      0
                      На чем интерфейс пользователя делали? Обычный десктопный Swing/AWT?
                        0
                        Да. Swing/AWT. И большие куски интерфейса, в частности — меню, таблицы и т.д. генерятся на основе XML.
                        –1
                        4. Оптимизация выполнения SQL-запросов.

                        вы понимаете почему так происходит? С помощью addBatch вы добавляете команду в один большой кусок и потом выполняете его за одну транзакцию. Поэтому разница в скорости (много команд и транзакция на каждую или много команд в одну единую транзакцию).

                          0
                          Вообще у нас autocommit отключен в JDBC. Мы контролируем транзакции сами, например — если нужно сохранить графическую схему в БД, то она сохраняется только полностью, т.е. если все insert-ы прошли успешно, то мы делаем commit отдельно. Так вот — это делалось у нас всегда, и без addBatch.
                          И всеравно с addBatch быстрее.

                          По-моему, дело не только в транзакциях, но и в подготовке самого запроса. т.е. в механизме PREPARE в различных СУБД.
                            +1
                            Используя Spring вы получаете много плюшек. Работа с соединениями БД, управление тразакциями, связь между компонентами через IoC. Код, мне кажется, должен получиться более читабельным и компактным, некоторые моменты просто не потребуется реализовывать. Тот же пул коннектов на примере c3p0, позволит кэшировать prepared запросы, проверять на живучесть соединения и в случае обрыва соединения восстановит их. Кроме того, как уже упоминали про MyBatis, он в свою очередь тоже позволяет кэшировать результаты запросов, чтобы исключить их слишком частое повторное выполнение.
                              –2
                              типа Спринг будет работать с СКЛ-сервером лучше чем сам СКЛ-сервер?

                              Так не бывает.

                              Просто нужен хороший специалист по базам данных.
                                0
                                Не знаю с чего вы сделали подобные выводы. Я вас не понимаю. Может более развернуто напишите, что вы имели в виду? Я не касался работы СУБД в своем ответе, я лишь описывал что на клиентской стороне можно сделать для более комфортной с точки зрения удоства и производительности работы.
                          0
                          Лучше кривеньконаписанная система, которая работает, чем идеально проработанная архитектура, которая не работает.

                          Судя по тому, что было в статье написано я должен признать, что архитектура все еще кривенькая, но намного лучше, чем была раньше. Изобретены некоторые велосипелы, которые не стоило бы изобретать.

                          Из того, что описано выше, почему-то у меня возникает предположение, что наверняка у вас с транзакциями проблемы есть, если вы вообще их используете, а не просто автокоммитом все в базу забабахивает…

                          На самом деле вам не помешал бы один пускай не Senior, но хотя бы Middle девелопер, который успел понаступать на грабли и понабивать шишек.

                          А по сути в выправке дизайна вашей аппликации, успехов вам!
                            0
                            Спасибо. Я уже скоро сам стану таким, только поднаберу еще нужное количество шишек на голове.
                            0
                            А я для пула коннектов использовал бы tomcat-jdbc :)
                              0
                              Если база данных используется просто для key-value mapping, то да, сериализовать можно хоть в XML, хоть во что (если есть схема для валидации — лучше в XML, а если нет — то дешевле «во что»).
                              Если предполагается поиск по узлам/атрибутам и/или точечное их редактирование — не рассматривали вариант документо-ориентированного NoSQL-хранилища, например, MongoDB?
                                0
                                Да, мы специально рассматривали базы данных, ориентированные на XML. Но у нас XML — это небольшая часть от всего объема, который нужно хранить в БД. И БД точно не для key-value mapping, а это проектная структура, данные в которую попадают с графических схем и затем их нужно обрабатывать.
                                0
                                Честно говоря непонятно с какой целью и для кого написан топик, да и с postgresql связь не особо прослеживается.
                                В остальном по пунктам:
                                1. Да, часто бывает так, что если система не занимается CRUD'ом, то ORM особо и не нужен.
                                2. Как уже писали выше существует множество реализаций пулов, например c3p0.
                                3. Мешанина в архитектуре, что то на stored procedure, что то в коде, отсюда и все проблемы. Оберните уж тогда все запросы в процедуры и вызывайте их через callable statement.
                                4. Оптимизация sql запросов это совсем другое, у вас просто пакетная работа с запросами, одна из самых базовых в jdbc, вообще не важно какая у вас СУБД.
                                5. Разве этот нюанс работы с XML отсутствует в документации Postgresql?
                                6. Почему то кажется, что для ваших целей pgAdmin'а должно хватать за глаза, не очень понимаю за что боритесь пытаясь редактировать через eclipse/nedbeans.
                                  0
                                  1. Система СRUD-ом как раз занимается. Обычная работа с реляционной БД.
                                  2. Релизаций пулов много. Просто тот, который используем мы — хоть и самопальный, но нас устраивает. Зачем его менять на тот же c3p0? какой выигрыш от этого?
                                  3. Вот это мне совсем непонятно. Вы серьезно предлагаете ВСЕ SQL-запросы обернуть в процедуры под эгидой «уборки мешанины»?
                                  У нас есть задачи, которые связаны с тем, чтобы, например, получить данные в форму. Это обычный селект, и живет он в коде. А есть задачи обработки больших количеств данных внутри БД. Это мы пишем хранимой процедурой, т.к. иначе это будет очень медленно.
                                  5. Нюанс есть, но я очень долго в него вникал. И вообще не очень понятно из документации, где надо писать префикс неймспейса, где не надо и почему. Там приведен только формат функции xpath.
                                  6. Мы ни за что не боремся, просто удобно вести разработку в одной среде и SQL-запросов тоже. А выходит — нельзя.
                                    +1
                                    Если система — CRUD, то используйте ORM, тогда точно все головняки исчезнут(ну, почти все).
                                      0
                                      так не бывает. Исчезнут одни — появятся другие
                                      0
                                      1. Тогда действительно ORM может и пригодится, не обязательно JPA/Hibernate, можно посмотреть на iBatis.
                                      2. Свой велосипед всегда роднее и лучше? Вы действительно считаете, что способны реализовать лучше, чем в библиотеке реализованной профессионалами и используемой в многих тысячах проектов? Вы проводили анализ и Вам чего то не хватило в существующих пулах, какой выигрыш в том, что Вы потратили время на реализацию своего?
                                      3. То вы жалуетесь, что переименование таблицы рушит Вам половину запросов, то не понимаете зачем Вам разгребать мешанину. Как говорится «лучше безобразно, но единообразно», тогда проще будет поддерживать, плюс подозреваю, что тоже переименование таблицы приведет к некомпильности процедур, как следствие IDE Вам подскажет места, где нужно поправить.
                                    0
                                    А зачем хранить целиком весь константой? Попробуйте HQl, откроете для себя много нового. Подозреваю есть похожие запросы, которые сможете обернуть в методы с параметрами и соорудить себя DAO единое со всеми запросами.
                                    Если нет взаимосвязей, то можно было бы нереляционные бд использовать. надо конечно знать суть задачи, но возможно было бы лучше.
                                      0
                                      неужели SQL Workbench потдерживает слонов? когда-то SQL Workbench мне очень нравилась, пока не перешел с MySQL на на словнов, не хватоло этой проги)
                                      в гугле не нашел подтверждения((
                                        0
                                        Я имел ввиду SQL Workbench/J.
                                        он работает через JDBС, соответственно и слоны поддерживаются

                                      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                      Самое читаемое