CODEWITHSUNDEEP
postgresqljdbcclojure
Michiel Borkent
Michiel Borkent

Reputation: 34870

clojure.java.jdbc lazy query

I have a query that is basically a select *. In development this table is only 30000 rows, but in production it will much bigger. So I want to consume this query lazily. Why is the query below not lazy? I am using Postgres 9.5.4.1.

(do
  (def pg-uri {:connection-uri "jdbc:postgresql://localhost/..."})
  (def row (atom 0))
  (take 10 (clojure.java.jdbc/query 
          pg-uri
          ["select * from mytable"]
          {:fetch-size 10
           :auto-commit false
           :row-fn (fn [r] (swap! row inc))}))
  @row) ;;=> 300000

Upvotes: 7

Views: 2547

Answers (3)

Sean Corfield
Sean Corfield

Reputation: 6666

clojure.java.jdbc supports lazy processing of large result sets natively these days (the other answers here predate that native support). See the community documentation about it here:

http://clojure-doc.org/articles/ecosystem/java_jdbc/using_sql#processing-a-result-set-lazily

In particular, see the Additional Options? section for database-specific tweaks you might need. You can specify :auto-commit? false on any function that would open a new connection, and you can specify :fetch-size and the various cursor controls on any query-related function. See this StackOverflow question & answer for details of what PostgreSQL might need:

Java JDBC ignores setFetchSize?

Currently, you'll have to dig in the clojure.java.jdbc source or the prepare-statement reference documentation for more of those options. I'm continuing to work on the community documentation to surface all of that information.

Upvotes: 8

Alan Thompson
Alan Thompson

Reputation: 29984

You don't need the transaction and prepared-statement stuff above. It is the use of :result-set-fn that causes the lazy-sequence to be consumed. You may have meant to use :row-fn instead.

For full details please see The Clojure Cookbook. A print version is also available, which I recommend.

The jdbc/query function has several optional keyword parameters that control how it constructs the returned result set. The :result-set-fn parameter specifies a function that is applied to the entire result set (a lazy sequence) before it is returned. The default argument is the doall function:

(defn hi-lo [rs] [(first rs) (last rs)])

;; Find the highest- and lowest-cost fruits
(jdbc/query db-spec
            ["select * from fruit order by cost desc"]
            :result-set-fn hi-lo)
;; -> [{:grade nil, :unit nil, :cost 77, :appearance nil, :name "Kumquat"}
;;     {:grade 1.4, :unit nil, :cost 10, :appearance "rotten", :name "Tomato"}]

The :row-fn parameter specifies a function that is applied to each result row as the result is constructed. The default argument is the identity function:

(defn add-tax [row] (assoc row :tax (* 0.08 (row :cost))))

(jdbc/query db-spec
             ["select name,cost from fruit where cost = 12"]
             :row-fn add-tax)
;; -> ({:tax 0.96, :cost 12, :name "Plum"} {:tax 0.96, :cost 12, :name "Fig"})

Upvotes: 0

Michiel Borkent
Michiel Borkent

Reputation: 34870

First, see https://jdbc.postgresql.org/documentation/83/query.html#query-with-cursor.

Solved it like this.

(jdbc/with-db-transaction [tx connection]
  (jdbc/query tx
    [(jdbc/prepare-statement (:connection tx)
                              "select * from mytable"
                              {:fetch-size 10})]
     {:result-set-fn (fn [result-set] ...)}))

where :result-set-fn is a function that consumes the lazy result set.

with-db-transaction takes care of autoCommit set to false. :fetch-size is not passed from query so you have to make a prepare-statement yourself.

Upvotes: 9

Related Questions