Simple library for create clojure.java.jdbc queries



dev dependencies


(this space intentionally left almost blank)

The core namespace exposes the main API functions for creating queries. It pulls in the functionality for building the different parts of the query from the other project namespaces.

  (require [finder.opts :as opts]
           [finder.where :as where]
           [finder.params :as params]))

Query wraps up all the query building and is called by the main API functions. It takes a table name, some parameters, and some options. Then returns a vector that can be used in a JDBC query.

  query [tbl params options]
  (let [where (where/get-where params)
        args (params/get-params params)
        opts (opts/get-options options)
        sql (format " select * from %s%s%s " (name tbl) where opts)]
    (apply vector
      (concat [sql] args))))


The API consists mainly of the 'where' function, with a few others that provide a slightly more meaningful syntax for particular queries, like finding by an ID.

The general structure for the functions is to take a table name, a series of parameters as the second argument, and then some options like ordering for the last parameter.

Find all records from table matching the parameters. The parameters should be a map or vector of maps. Last argument is the options map.

  ([tbl params] (where tbl params {}))
  ([tbl params opts] (query tbl params opts)))

Find records matching a single field and value. Can also take query options for order, limit and offset.

  ([tbl fld id] (by tbl fld id {}))
  ([tbl fld id opts] (where tbl (hash-map fld id) opts)))

Does a simple find on a table by the 'id' column.

  [tbl id]
  (by tbl "id" id))

Does a find all on a table to return every result by default, but can be given the standard options to order, limit and offset too.

  ([tbl] (all tbl {}))
  ([tbl opts] (where tbl {} opts)))

This namespace deals with the query options like order, limit and offset

  (:require [clojure.string :as string]))

Destructures a name/value vector and returns a single order clause as a string. ie. 'name desc'

  to-order-clause [[col dir]]
  (format "%s %s" (name col)
                  (name dir)))

Takes an order by column, or series of columns and builds the order by statement as a string to return.

  get-order [order]
  (let [orders (if (map? order) order 
                   (hash-map order :desc))]
    (str " order by "
      (string/join ", "
        (map to-order-clause orders)))))

Checks the options data to see if an option is present, and if it is then passes it to its handler function, returning the result.

  to-option [opts [opt func]]
  (if-let [data (get opts opt)]
    (func data) ""))


Takes the 'options' map of information about ordering, limit and offset. Then returns a string for those parts of the SQL query, in the correct order.

  get-options [opts]
  (let [clause #(format " %s %d" %1 %2)]
    (string/join ""
      (map (partial to-option opts)         
           {:order get-order
            :limit (partial clause "limit")
            :offset (partial clause "offset")}))))

This namespace handles extracting all the values from the query parameters that need to be bound by the prepared statement in JDBC before the query is executed.


Extract the value for an individual parameter, that could be a value, a set of values, or a vector comparator

  to-param [[_ value]]
    (set? value) (apply vector value)
    (vector? value) (second value)
    :else value))


Fetch all the parameter values from the specified parameters. These parameters could be values, sets, or comparator vectors. Returns an ordered vector of the parameters as they are specified.

  get-params [params]
  (let [param-vec (if (vector? params) params
                      (vector params))
        to-params #(concat %1 (map to-param %2))]
      (reduce to-params [] param-vec))))

This namespace handles turning the query parameters into the 'where' part of the SQL query.

  (:require [clojure.string :as string]))
(declare to-where-params)

Creates a pseudo 'where in' clause using a series of comparisons for each value instead (JDBC driver does not support binding values to 'in')

  to-where-in [fld value]
  (format "(%s)"
      (map (partial hash-map fld) value))))

Formats a single where clause, using = as the default operator, or the one specified by the value vector. eg. ['< 12]

  to-where [fld value]
  (let [operator (if (vector? value)
                     (first value)
    (format "%s %s ?" (name fld) operator)))

Formats a single where clause part, which could either be a single fld/value parameter, or a 'where in' clause using a set.

  to-where-clause [[fld value]]
      (set? value) (to-where-in fld value)
      :else (to-where fld value)))

Takes a bunch of param groups, joins them with ORs.

  to-where-params [groups]
  (let [to-group #(format "(%s)"
                   (string/join " and "
                     (map to-where-clause %)))]
    (string/join " or "
      (map to-group groups))))


Fetch the 'where' sql clause and return it as a string.

  get-where [params]
  (let [to-vector #(if (vector? %) % (vector %))]
    (if (empty? params) ""
        (format " where %s"
                (to-where-params (to-vector params))))))