CODEWITHSUNDEEP
postgresqljdbcclojureuuid
Joel
Joel

Reputation: 247

Checking if uuid exists in PostgreSQL table using clojure.java.jdbc/db-do-prepared fails

I'm connecting to a PostgreSQL database and I want to know whether or not a uuid exists in a UUID-type column in some table. The parameter uuid is generated in another function. In clojure I try

(jdbc/db-do-prepared {:datasource datasource}
             "SELECT exists (SELECT 1 FROM account WHERE guid=?::uuid)"
             [uuid])

But it throws an exception:

BatchUpdateException Batch entry 0 SELECT exists (SELECT 1 FROM table WHERE guid='dbe8cda5-d37c-3446-9b9c-85e0344af3b1'::uuid) was aborted.  Call getNextException to see the cause.  org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError (AbstractJdbc2Statement.java:2781)

If I connect to the database and paste the generated SQL and execute it it works fine. I can also insert from clojure using the following code:

(jdbc/db-do-prepared {:datasource datasource}
                   "INSERT INTO table(guid) VALUES (?::uuid)"
                   [uuid])

Relevant dependencies from project.clj:

[org.clojure/java.jdbc "0.4.2"]
[org.postgresql/postgresql "9.4-1205-jdbc42"]
[hikari-cp "1.3.1"]

Upvotes: 3

Views: 935

Answers (1)

NielsK
NielsK

Reputation: 6956

From the documentation of do-prepared:

Return a seq of update counts (one count for each param-group)

UPDATE and DELETE are the only SQL CRUD actions that return update counts. So do-prepared should only be used with those. However, there are more direct abstractions: for UPDATE use update! and for DELETE use delete!.

INSERTs return generated keys, and can work with do-prepared returning insert counts. However, this way you cannot get the value of generated primary keys. Use do-prepared-return-keys, or better, the direct abstraction insert!

SELECTs return result sets, not an update count, and won't work with do-prepared.

If you do want to use batch SELECTs with a prepared statement, do so by:

  • binding a single database connection using with-db-connection
  • create a prepared statement on that connection and bind it to a var
  • use a query function referencing both bound connection and prepared statement

Like so:

(j/with-db-connection [c datasource]
      (let [ps (j/prepare-statement (j/get-connection c)
                "SELECT count(*) from person where left(name,1)=?")]
        (doall (map #(j/query c [ps %]) ["a" "b" "c"]))))

While I initially thought the database would be able to optimize queries thanks to their parametrization, further tests have shown me that this can speed up queries by 60%. Most of this speedup is on the database side, since statement preparation overhead is negligible. This test was done with the same use case as the OP mentioned, checking UUIDs (from 1K to 100K).

A generic version for using prepared statements can be cleaned up with a little macro magic into the following:

(defmacro with-prepared-statement
  [[connection-bind datasource
    statement-bind sql & keys]
   & body]
  `(clojure.java.jdbc/with-db-connection [~connection-bind datasource]
                                         (let [~statement-bind (apply clojure.java.jdbc/prepare-statement
                                                                      (clojure.java.jdbc/get-connection datasource) ~sql ~keys)]
                                           ~@body)))

(with-prepared-statement [c datasource
                          ps "SELECT count(*) from persoon where left(voornaam,1)=?"]
                         (doall (map #(j/query c [ps %]) ["a" "b" "c"])))

Upvotes: 1

Related Questions