Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Basic DSL for generating SQL/DDL, formerly java.jdbc.sql and java.jdbc.ddl

branch: master
README.md

java-jdbc/dsl

Basic DSL for SQL/DDL generation formerly part of org.clojure/java.jdbc (0.3.0).

This DSL was grown here in this repository as an experiment for inclusion in the java.jdbc contrib library but community feedback indicated that adding a DSL to java.jdbc caused confusion and was unnecessary. In particular, this DSL wasn't very sophisticated and I had no plans to make it sophisticated. Projects like HoneySQL and SQLingvo are always going to be better DSLs.

Because this DSL is being removed from org.clojure/java.jdbc, the java.jdbc.sql and java.jdbc.ddl namespaces have been moved to this external project so that anyone using the DSL can continue doing so by simply switching to the java-jdbc.sql and java-jdbc.ddl namespaces from this project instead.

Releases and Dependency Information

Latest stable release: 0.1.0:

Leiningen dependency information:

[java-jdbc/dsl "0.1.0"]

Maven dependency information:

<dependency>
  <groupId>java-jdbc</groupId>
  <artifactId>dsl</artifactId>
  <version>0.1.0</version>
</dependency>

Usage

Generate SQL and parameters that can be used in query and execute expressions:

(select * :person)
;;=> ("SELECT * FROM person")
(select [:id :name] :person
  (where {:email "user@domain.com"}))
;;=> ("SELECT id,name FROM person WHERE email = ?" "user@domain.com")
(select [{:p.id :userid} :p.name :a.city] {:person :p}
  (join {:address :a} {:p.id :a.personid})
  (where {:p.email "user@domain.com"}))
;;=> ("SELECT p.id AS userid,p.name,a.city FROM person p JOIN address a ON p.id = a.personid
;;     WHERE p.email = ?" "user@domain.com")
(select [:id :name] :person
  (order-by :name))
;;=> ("SELECT id,name FROM person ORDER BY name ASC")
(update :person {:status "active"})
;;=> ("UPDATE person SET status = ?" "active")
(update :person {:status "suspended"}
  (where {:country "NG"}))
;;=> ("UPDATE person SET status = ? WHERE country = ?" "suspended" "NG")
(delete :person (where {:id 42}))
;;=> ("DELETE FROM person WHERE id = ?" 42)

select expects a column-spec, a table-spec, optional join-clauses (as strings), an optional where-clause (sequence of SQL conditions as a string followed by parameters). It returns a sequence whose first element is a string containing a SQL SELECT statement and whose remaining elements are the values to be substituted for parameters (?) in that string.

A column-spec may be *, a single column or a sequence of columns. A column may be a string or keyword, or a map (of a single key to a single value, that specifies a column alias).

A table-spec may be a string or a keyword, or a map (of a single key to a single value, that specifies a table alias).

A join-clause is just a string containing a SQL JOIN clause. It can be generated by the join function.

A where-clause is a sequence whose first element is a string containing SQL conditions and whose remaining elements are the values to be substituted for parameters (?) in that string. It can be generated by the where function.

delete expects a table-spec and a where-clause (which is not optional). It returns a sequence whose first element is a string containing a SQL DELETE statement and whose remaining elements are the values to be substituted for parameters (?) in that string.

insert expects a table-spec and either column-names followed by values or value-maps representing rows.

join expects a table-spec and a join-map which is used to generate the ON clause. It returns a string containing a SQL JOIN/ON clause.

A join-map is a map whose keys and values represent columns in the two tables being joined.

where expects a value-map which is used to generate a string that contains the conditional part of a WHERE clause and the values to be substituted for parameters (?) within that string. It returns a sequence whose first element is the string and whose remaining elements are the parameter values.

order-by expects a sequence of col-sorts. A col-sort is either string or keyword representing a column to sort on (ascending), or a map with one key / value pair that specifies a column name and a sort direction (:asc, :desc).

update expects a table-spec, an update-map and an optional where-clause. It returns a sequence whose first element is a string containing a SQL UPDATE statement and whose remaining elements are the values to be substituted for parameters (?) in that string.

An update-map is a map whose keys represent columns to be set to the corresponding values.

All functions that generate SQL may have an optional :entities keyword argument (after the specified arguments) which specifies an identifier naming convention, e.g., (quoted \`) which would cause SQL entities to be quoted with ` (:id would become `id`). Other built-in naming conventions as as-is and lower-case.

The entities macro can be used to apply an identifier naming convention to a DSL expression. It expects a function representing the naming convention and a DSL expression. It post-walks the DSL expression and inserts the :entities keyword argument and naming convention at the end of each expression.

License

Copyright (c) 2012-2013 Sean Corfield

Distributed under the Eclipse Public License, the same as Clojure.

Something went wrong with that request. Please try again.