Grabduck

PreparedStatement (подготовленные запросы)

:

NB: хотя здесь и ведется повествование про PreparedStatement, большая часть оного правдива и для CallableStatement.
Как, наверное, большинству известно в JDBC можно создавать разные виды Statement’ов включая обычные Statement, PreparedStatement и CallableStatement. Здесь мы обсудим что такое PreparedStatement и зачем он нужен.
Итак, начнем с того, что СУБД при поступлении в них запросов, проверяют их синтаксис, разбирают (soft parse), оптимизируют (hard parse) и создают некий query plan – то как на самом деле уже СУБД будет выполнять запрос, какие операции она будет при этом делать.
СУБД умеют кешировать выполненные запросы, то бишь если взять запрос: select * from books. И выполнять его несколько раз, то на каком-то этапе СУБД начнет его кешировать. Кеш во многих системах – это мапа, что значит, что у нее есть ключ и значение. В данном случае в качестве ключа будет SQL запрос (а точнее его хеш), а в качестве значения – разобранный план. Когда СУБД получает очередной запрос с тем же текстом, она проверяет кеш и если там уже есть скомпилированный запрос, то она использует его вместо того, чтоб наново его разбирать (на самом деле периодически СУБД доделывают hard parse, чтоб оптимизировать запрос еще больше).
Заметьте, что в качестве ключа используется полностью тело запроса. Это значит, что запрос:
select * from books where id=1 и запрос select * from books where id=2 – не являются одинаковыми и оба будут компилироваться, что значит что мы не можем кешировать один и тот же запрос только из-за того, что параметры каждый раз разные! Эту проблему решает PreparedStatement, который имеет вид: select * from books where id=? В данном случае СУБД закеширует запрос и будет только лишь подставлять новые параметры вместо знака вопроса. Это позволяет существенно ускорить обработку запросов. Разные СУБД конечно же по-разному реализуют кеш и время, когда он будет задействован (что часто основывается на статистике или просто на указаном пользователем значении).
Что происходит под капотом:

  1. Когда выполняется connection.prepareStatement("some query") драйвер обращается к СУБД для подготовки запроса*, которая возвращает обратно идентификатор запроса (его хеш как правило) и еще некоторые данные, такие как количество параметров в запросе.
  2. При вызове executeQuery() драйвер отсылает лишь идентификатор запроса и параметры, СУБД по ID находит уже разобранный запрос и выполняет его.

Но прозорливый читатель сразу заметит, что при следующем выполнении connection.prepareStatement() снова произойдет сначала вызов prepare к БД, затем собственно вызов для выполнения конкретного запроса. Во-первых, это два вызова по сети, что не хорошо с точки зрения производительности. Во-вторых, хоть СУБД и закешировало запрос и не будет его второй раз разбирать, у нас уже есть ID запроса после первого раза, зачем нам снова лазить в БД за ним, если его можно где-то сохранить и переиспользовать? Собственно так это и реализовано в большинстве драйверов:
Connection#prepareStatement(String sql) – здесь и происходит вся магия, по переданной в метод строке драйвер проверяет объект в своем внутреннем (не СУБД!) кеше, и, если тот там есть, возвращает его; если нет – создает новый.** Это называется неявным кешированием (implicit cache)***.
Идем дальше: PreparedStatement#close() - собственно этот метод и не оправдывает своего имени в данном случае – он не закрывает на самом деле statement, а помещает его как раз таки в кеш. Физически PreparedStatement закрывается только в случае а) если соединение с БД было закрыто б) когда кеш достигает своего максимальной вместимости и нужно освобождать его от старых и малоиспользуемых statement’ов в) если кеш отключен г) если кеш не поддерживается драйвером :)
Но это еще не все. В большистве случаев приложения работают не напрямую с соединениями, создавая и закрывая их, а с пулами соединений (например, DBCP, C3P0), которые сами их создают и предоставляют вашему коду. Так вот, если в обычном случае PreparedStatements привязаны к одному соединению и не могут быть переиспользованы, то пулы позволяют каждому соединению использовать подготовленные запросы других соединений, что означает, что производительность вырастет еще больше.
Опишу пункты, которые нужно знать при работе с подготовленными запросами в MySQL (многое из этого подходит и для других СУБД):

  • Запросы должны точно совпадать (запросы со словами USERS и users - будут считаться разными) - это правда для всех СУБД****

  • Не всегда PreparedStatement кешируются с первого раза, часто их нужно выполнить по несколько раз.

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

  • Запрос не должен начинаться с пробелов (если честно, то не уверен, что это правда для MySQL, но уже сил нет читать документацию :) Для PostgreSQL это так).

  • Подзапросы и запросы с UNION не кешируются.

  • Запросы внутри хранимых процедур не кешируются.

  • MySQL (не драйвер, а сам сервер) до версии 5.1.17 не кеширует запросы, у версий выше есть тоже свои “пасхальные яйца”, из-за который невозможно кешировать запрос, поэтому читайте обязательно документацию.

  • Обязательно установите свойство cachePrepStmts в true, ибо по умолчанию оно отключено! Используйте параметры соединения, такие как prepStmtCacheSize и prepStmtCacheSqlLimit для конфигурации MySQL драйвера.

Какие еще плюшки нам дает PreparedStatement?

Кроме улучшения производительности, подготовленные запросы защищают от SQL Injections. Чтобы было совсем просто понять суть, пример будет очень простым и глупым. Допустим, есть функциональность на форуме, такая как “удалить пользователя”. Мы вводим в поле его имя и нажимаем на кнопку Submit. Передается запрос на сервер и мы работаем с обычным Statement, ну и для создания запроса используем конкатенацию:

Sring query = "delete from users where username=" + username;

Запомните раз и навсегда, что это плохо! Если какой-то злоумышленник в поле на форме введет следующую строку: vasia' or 'a'='a, это приведет к печальным последствиям. Результирующий запрос будет следующим:
delete from users where username='vasia' or 'a'='a'

Т.к. ‘a’ всегда ровняется ‘a’, то выражение в where всегда будет true и в результате запроса удалятся все записи из таблицы. Для избежания подобного, нужно заескейпить входящую строку. Это значит, что все входящие символы, если они представляют собой что-то, что для СУБД представялется значащим символом (например, кавычки), будут заменены на какую-то другую комбинацию символов. Делать это можно самому, можно использовать уже существующие методы/библиотеки, но в конце концов запрос выходит приблизительно такой:
delete from users where username='vasya\' or \'a\'=\'a'

То есть все кавычки в строке заменены на ', что для MySQL будет значит “воспринимай ковычку как часть строки”. Однако зачем нам это делать самим, когда все уже сделано для нас с помощью PreparedStatement? Используя его запрос будет выглядет следующим образом:
delete from users where username=?

А затем мы укажем параметр: preparedStatement.setString(1, username) И все переданное сюда будет восприниматься исключительно как текст, СУБД сама все заескейпит.

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


* Некоторые драйвера, иже не поддерживают пред-компиляцию, отсылают запрос только на этапе executeQuery().
** Заметьте, что при создании обычного Statement, никакой строки в объект соединения не передается, что значит, что они создаются каждый раз наново.
*** На самом деле некоторые JDBC драйвера (такие как Oracle) могут кешировать и обычные statement’ы. В случае Oracle JDBC Driver для этого нужно дергать implementation-specific API и оно не будет столь же эффективно, ну и плюс там есть свои заморочки. Это называется explicit statement caching.
**** Конечно может и не для всех, все я не смотрел, но для 3 СУБД из проверенных - это правда.

PS: огромной спасибо нашему Vlad’у за его блог пост по заказу, который очень помог в написании данной статьи.
PPS: Обсуждение на тему: PreparedStatements. Both DBMS & Java caching