{"id":7533,"date":"2015-05-28T16:24:52","date_gmt":"2015-05-28T21:24:52","guid":{"rendered":"http:\/\/bobbeaty.com\/wp\/?p=7533"},"modified":"2015-05-29T04:08:12","modified_gmt":"2015-05-29T09:08:12","slug":"excellent-jdbc4-fix-for-operators-in-postgres","status":"publish","type":"post","link":"https:\/\/bobbeaty.com\/wp\/archives\/7533","title":{"rendered":"Excellent JDBC4 Fix for ?-operators in Postgres"},"content":{"rendered":"<p><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/bobbeaty.com\/wp\/wp-content\/uploads\/2008\/05\/postgresql.jpg\" alt=\"PostgreSQL.jpg\" title=\"PostgreSQL.jpg\" border=\"0\" width=\"128\" height=\"128\" style=\"float:right;\" \/><\/p>\n<p>In Postgres 9.4, there are several JSONB operators that include a <tt>?<\/tt> as part of the operator. This is nasty for the JDBC usage because the JDBC driver uses the question-mark as the <em>substitute parameter<\/em> place-holder for arguments. And there's no escaping of values in a JDBC <tt>PreparedStatement<\/tt> so what's a guy to do?<\/p>\n<p>Sure, you can make a simple custom function to do this, but that's kinda wasteful, and it's not very transportable. Thankfully, I was reading the <a href=\"https:\/\/jdbc.postgresql.org\/documentation\/changelog.html\">release notes<\/a> for the 9.4 JDBC driver and saw <a href=\"https:\/\/github.com\/pgjdbc\/pgjdbc\/pull\/227\">this<\/a>.<\/p>\n<p>Using the latest, released JDBC driver in your <tt>project.clj<\/tt> file:<\/p>\n<pre class=\"clojure\" style=\"font-family:monospace;\">  <span style=\"color: #66cc66;\">&#91;<\/span>org<span style=\"color: #66cc66;\">.<\/span>postgres<span style=\"color: #66cc66;\">\/<\/span>postgres <span style=\"color: #ff0000;\">&quot;9.4-1201-jdbc41&quot;<\/span><span style=\"color: #66cc66;\">&#93;<\/span><\/pre>\n<p>you can pick up this pull request, and feature.<\/p>\n<p>By simply <em>doubling<\/em> the question mark, it'll now reduce this to one <tt>?<\/tt> and the operator will work. Very slick. So now in a clojure SQL statement, just put <tt>??<\/tt> when you need <tt>?<\/tt> and you're good to go!<\/p>\n<p>Fantastic! I've checked it and it works perfectly.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Postgres 9.4, there are several JSONB operators that include a ? as part of the operator. This is nasty for the JDBC usage because the JDBC driver uses the question-mark as the substitute parameter place-holder for arguments. And there&#8217;s no escaping of values in a JDBC PreparedStatement so what&#8217;s a guy to do? Sure, [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11,4],"tags":[],"class_list":["post-7533","post","type-post","status-publish","format-standard","hentry","category-clojure-coding","category-open-source-software"],"_links":{"self":[{"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/posts\/7533","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/comments?post=7533"}],"version-history":[{"count":2,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/posts\/7533\/revisions"}],"predecessor-version":[{"id":7535,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/posts\/7533\/revisions\/7535"}],"wp:attachment":[{"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/media?parent=7533"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/categories?post=7533"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bobbeaty.com\/wp\/wp-json\/wp\/v2\/tags?post=7533"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}