A low-level Clojure wrapper for JDBC-based access to databases.
For higher level DSLs and migration libraries that are compatible, see the documentation.
Formerly known as clojure.contrib.sql.
This library is mature and stable. It is widely used and its use is described in many books and tutorials. It will continue to get bug fixes and minor releases. Based on my experience using and maintaining this library, I've created a faster, more modern JDBC wrapper called next.jdbc. I consider it to be the "next generation" of clojure.java.jdbc but it exposes a different API -- a better API, I think.
API Reference (Autogenerated)
Latest stable release: 0.7.10 -- requires Clojure 1.7 or later!
Leiningen dependency information:
[org.clojure/java.jdbc "0.7.10"]
Maven dependency information:
<dependency>
<groupId>org.clojure</groupId>
<artifactId>java.jdbc</artifactId>
<version>0.7.10</version>
</dependency>
Note: Earlier versions of Clojure are supported by older versions of clojure.java.jdbc: e.g., version 0.6.1 supports Clojure 1.4 and later.
You will also need to add dependencies for the JDBC driver you intend to use. Here are links (to Maven Central) for each of the common database drivers that clojure.java.jdbc is known to be used with:
Note: different versions of various database drivers have different Java/JVM version requirements. In particular, recent versions of Apache Derby require at least Java 8 and recent versions of H2 require at least Java 7. Clojure's Continuous Integration system uses older versions so tests can be run on Java 6 (see pom.xml); local testing is done with more recent versions on Java 8.
(require '[clojure.java.jdbc :as j])
;; there are many ways to write a db-spec but the easiest way is to
;; use :dbtype and then provide the :dbname and any of :user, :password,
;; :host, :port, and other options as needed:
(def mysql-db {:dbtype "mysql"
:dbname "clojure_test"
:user "clojure_test"
:password "clojure_test"})
(def pg-db {:dbtype "postgresql"
:dbname "mypgdatabase"
:host "mydb.server.com"
:user "myuser"
:password "secret"
:ssl true
:sslfactory "org.postgresql.ssl.NonValidatingFactory"})
;; if the dbtype is not known to clojure.java.jdbc, or you want to override the
;; default choice of JDBC driver class name, you can provide :classname and the
;; name of the class to use:
(def redshift42 {:dbtype "redshift"
:dbname "myredstore"
:classname "com.amazon.redshift.jdbc42.Driver"
...})
;; you can also specify a full connection string if you'd prefer:
(def pg-uri
{:connection-uri (str "postgresql://myuser:secret@mydb.server.com:5432/mypgdatabase"
"?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory")})
(j/insert-multi! mysql-db :fruit
[{:name "Apple" :appearance "rosy" :cost 24}
{:name "Orange" :appearance "round" :cost 49}])
;; ({:generated_key 1} {:generated_key 2})
(j/query mysql-db
["select * from fruit where appearance = ?" "rosy"]
{:row-fn :cost})
;; (24)
For more detail see the API reference or documentation.
Testing:
To test against PostgreSQL, first create the user and database:
$ sudo -u postgres createuser clojure_test -P clojure_test
$ sudo -u postgres createdb clojure_test -O clojure_test
Or similarly with MySQL:
$ mysql -u root
mysql> create database clojure_test;
mysql> grant all on clojure_test.* to clojure_test identified by "clojure_test";
Then run the tests with the TEST_DBS environment variable:
$ TEST_DBS="mysql postgres" mvn test
Also see the run-tests.sh shell script which uses the clj CLI and deps.edn for multi-version testing!
Release 0.7.10 on 2019-08-24
lower-case function to avoid problems in certain locales (e.g., Turkish). A similar issue has been fixed recently in both HoneySQL and next.jdbc.db-spec options that are passed to the JDBC connection manager as properties JDBC-178.create-table-ddl column specs to allow numbers (as well as keywords and strings) JDBC-177.Release 0.7.9 on 2019-02-21
datafy/nav (in clojure.java.jdbc.datafy namespace). This includes a convention-based approach to foreign keys with some assistance from a :schema option. This is subject to change and is provided mostly for informational purposes, as an example of the new functionality in Clojure 1.10. This includes a fix for the conventions from JDBC-175.insert-multi! for some drivers JDBC-174.dbtype can be oracle:sid which maps to oracle:thin and uses : as the separator before the dbname value) JDBC-173.Release 0.7.8 on 2018-08-13
Release 0.7.7 on 2018-06-23
:as-arrays?, :result-set-fn, and :row-fn in operations that return generated keys as a result set (execute!, insert!, and insert-multi!) JDBC-169.get-connection provides much better feedback if you accidentally call a function that expects a db-spec but pass a java.sql.Connection object instead (which is only required for prepare-statement).Release 0.7.6 on 2018-04-24
execute! now supports :return-keys as a vector of column names, rather than just a simple Boolean value, for drivers that support that JDBC-166.:dbtype "h2:mem").db-spec being a java.net.URI object.add-connection handling of string db-spec (becomes :connection-uri, not :connection-string).with-db-* functions, to support options in the binding form JDBC-165.db-spec test databases.deps.edn (from Leiningen) as an example of multi-version testing without a "build tool".Release 0.7.5 on 2017-12-29
:return-keys in execute! and :multi? in db-do-prepared-return-keys JDBC-163.Release 0.7.4 on 2017-12-14
java.jdbc documentation JDBC-160.:keywordize? and :connection-uri changes from 0.7.2 and 0.7.3 releases.query and reducible-query.:raw? result set handling in reducible-query.modify-connection is more robust in the face of null connections and bad option values.Release 0.7.3 on 2017-10-05
:keywordize? option alongside :identifiers that defaults to true but can be set to false to opt-out of converting identifiers to keywords (so column names etc will only be processed by the function passed as :identifiers) JDBC-159.ex-info. Previously the rollback exception obscured the transaction exception JDBC-158.Release 0.7.2 on 2017-10-02
connection-uri was incorrectly spec'd as a java.net.URI but should be string? JDBC-156.:user and :password to be passed with :connection-uri, so credentials can be omitted from the connection string.get-connection to show where :user and :password can be passed.Release 0.7.1 on 2017-08-30
Release 0.7.0 on 2017-07-16
:conditional? option for create-table-ddl and drop-table-ddl to provide for existence check (or a function to manipulate the generated DDL).1521, support :dbtype "oracle" -- as "oracle:thin" -- and :dbtype "oracle:oci", with @ instead of // before host).Release 0.7.0-beta5 on 2017-07-05
get-connection now accepts an opts map with :auto-commit? and :read-only? options. If present, the appropriate methods will be called on the connection obtained. These options are valid in any function call that may call get-connection under the hood. This should allow for streaming results in a query for most databases JDBC-153.prepared-statement to avoid silently ignoring invalid combinations of :concurrency, :cursors, :result-type, and :return-keys.Release 0.7.0-beta4 on 2017-07-04
opts are now correctly passed from reducible-query to db-query-with-resultset.::query-options spec to make it clear that ::prepare-options are also acceptable there.Release 0.7.0-beta3 on 2017-07-04
reducible-result-set JDBC-152.Release 0.7.0-beta2 on 2017-06-30 (a.k.a The Reducible Saga, Part 2)
clojure.java.jdbc now requires Clojure 1.7 or later!clojure.java.jdbc.spec namespace (requires clojure.spec.alpha).reducible-query and reducible-result-set use IReduce and correctly support the no-init arity of reduce by using the first row of the ResultSet, if present, as the (missing) init value, and only calling f with no arguments if the ResultSet is empty. The init arity of reduce only ever calls f with two arguments.Release 0.7.0-beta1 on 2017-06-29
clojure.spec.alpha.reducible-query accepts a db-spec and a SQL/parameters vector and returns a reducible (IReduce on Clojure 1.7 or later; CollReduce on Clojure 1.5/1.6): when reduced, it runs the query, obtains a reducible result set, and then reduces that. A reducible query will run the query each time it is reduced. The helper function reducible-result-set is public: it accepts a ResultSet and produces a reducible that offers a single pass reduce over the rows. Both functions honor reduced values to short-circuit the process JDBC-99.Release 0.7.0-alpha3 on 2017-03-23
classname is now accepted with dbtype / dbname so you can easily specify a JDBC driver class name for a database type that is not known JDBC-151.redshift has been added as a dbtype with com.amazon.redshift.jdbc.Driver as the driver name.Release 0.7.0-alpha2 on 2017-03-01
pgsql and the Impossibl PostgresSQL 'NG' driver are now supported (note that :max-rows does not work with this driver!); also, providing unknown dbtype or subprotocol in a db-spec should now throw a better exception JDBC-150.quoted now accepts keywords for database / dialect (:ansi (including PostgresSQL), :mysql, :oracle, :sqlserver -- these match the keywords used in HoneySQL which is the recommended third party SQL DSL for java.jdbc) JDBC-149.get-connection clauses to make it easier to combine keys in a db-spec JDBC-148.DriverManager before classForName call on drivers to avoid potential race condition on initialization JDBC-145.Release 0.7.0-alpha1 on 2016-11-12 -- potentially breaking changes
as-sql-name and quoted have changed slightly: the former no longer has the curried (single argument) version, and the latter no longer has the two argument version. This change came out of a discussion on Slack which indicated curried functions are non-idiomatic. If you relied on the curried version of as-sql-name, you will not need to use partial. If you relied on the two argument version of quoted, you will need to add an extra ( ) for the one argument call. I'd be fairly surprised if anyone is using as-sql-name at all since it is really an implementation detail. I'd also be surprised if anyone was using the two argument version of quoted since the natural usage is :entities (quoted [\[ \]]) to create a naming strategy (that provides SQL entity quoting).insert-multi! with a sequence of row maps may be substantially slower than with a sequence of row value vectors (the former performs an insert for each row, the latter performs a single insert for all the data together) JDBC-147.insert! and update! JDBC-144.get-isolation-level to return the current transaction's isolation level, if any JDBC-141.read-columns option to allow more flexible customization of reading column values from a result set (particularly in a multi-database application). Also expands set-parameters support to options (previously it was just part of the db-spec) JDBC-137.clojure.spec coverage to almost the whole library API.Release 0.6.2-alpha3 on 2016-08-25
:qualifier and existing :identifiers functionality JDBC-140.:dbtype is the easiest / preferred way to write db-spec maps JDBC-139.Release 0.6.2-alpha2 on 2016-07-21
clojure.spec support to work with Clojure 1.9.0 Alpha 10.Release 0.6.2-alpha1 on 2016-07-05
clojure.spec via the new clojure.java.jdbc.spec namespace. Requires Clojure 1.9.0 Alpha 8 (or later).db-spec itself JDBC-136.query (and by extension find-by-keys and get-by-id) now support :explain? and :explain-fn options to help support basic performance analysis JDBC-135.insert! and insert-multi! now respect :identifiers and :qualifier because inserting rows on PostgreSQL returns full rows, not just the newly inserted keys JDBC-134.:identifiers option, you can now use :qualifier to specify a namespace qualifier (string) to be used when constructing keywords from SQL column names JDBC-133.Release 0.6.1 on 2016-05-12 -- IMPORTANT BUG FIX!
insert! and insert-multi! now default :transaction? to true (as they should have done in 0.6.0!) JDBC-128. These two functions also have improved docstrings to clarify the difference in behavior between inserting rows as maps compared to inserting rows as a series of column values.Release 0.6.0 on 2016-05-11 -- BREAKING RELEASE! DEPRECATED FUNCTIONALITY REMOVED!
find-by-keys now correctly handles nil values JDBC-126. 0.6.0 / 2016-05-11.find-by-keys calls seq on :order-by to treat [] as no ORDER BY clause. 0.6.0 / 2016-05-11.db-query-with-resultset now accepts an options map and passes it to prepare-statement JDBC-125. 0.6.0-rc2 / 2016-05-07.
prepare-statement options map as the first element of the [sql & params] vector is no longer supported and will throw an IllegalArgumentException. It was always very poorly documented and almost never used, as far as I can tell.db-query-with-resultset no longer requires the sql-params argument to be a vector: a sequence is acceptable. This is in line with other functions that accept a sequence. 0.6.0-rc2 / 2016-05-07.db-query-with-resultset now accepts a bare SQL string or PreparedStatement as the sql-params argument, when there are no parameters needed. This is in line with other functions that accept SQL or a PreparedStatement. 0.6.0-rc2 / 2016-05-07.query's options map now is passed to db-query-with-resultset and thus can contain options to be used to construct the PreparedStatement JDBC-125. 0.6.0-rc2 / 2016-05-07.get-by-id and find-by-keys convenience functions (these were easy to add after the API changes in 0.6.0 and we rely very heavily on them at World Singles so putting them in the core for everyone seemed reasonable). 0.6.0-rc1 / 2016-05-04.
find-by-keys accepts an :order-by option that expects a sequence of orderings; an ordering is a column name (keyword) or a map from column name (keyword) to direction (:asc or :desc). 0.6.0-rc2 / 2016-05-07.insert-multi! argument validation exception JDBC-123. 0.6.0-alpha2 / 2016-04-18.db-transaction (deprecated in version 0.3.0) has been removedjava.jdbc.deprecated namespace has been removedRelease 0.5.8 on 2016-04-12
db-do-commands now expects multiple commands to be be wrapped in a vector JDBC-122. The single command form is unchanged (but may be wrapped in a vector). Calling db-do-commands with multiple commands (not wrapped in a single vector) will produce a "DEPRECATED" warning printed to the console.db-do-prepared and db-do-prepared-return-keys now expect to receive a db-spec, an optional transaction? boolean, a sql-params argument, and an optional options map. sql-params is a vector containing a SQL string or PreparedStatement followed by parameters -- like other APIs in this library. In addition, like the :multi? true version of execute!, db-do-prepared can accept a vector that has parameter groups: multiple vectors containing groups of parameter values JDBC-122. Calling db-do-prepared with unrolled arguments -- the SQL string / statement followed by parameter groups -- is deprecated and will produce "DEPRECATED" warnings printed to the console.Release 0.5.7 on 2016-04-10
(insert! db table [:col] ["val"] {}) syntax, introduced in 0.5.6, threw an exception JDBC-121.Release 0.5.6 on 2016-04-10
create-table-ddl now expects the column specs to be wrapped in a single vector and no longer needs the :options delimiter to specify the options map JDBC-120.
insert! now supports only single row insertion; multi-row insertion is deprecated. insert-multi! has been added for multi-row insertion. :options is no longer needed as a delimiter for the options map JDBC-119.
insert! is called with multiple rows, or :options is specified, you will get a "DEPRECATED" warning printed to the console.Release 0.5.5 on 2016-04-09
Release 0.5.0 on 2016-03-27
Release 0.4.2 on 2015-09-15
.prepareStatement JDBC-112 - Michael Blume.metadata-query macro to make metadata query / results easier to work with for JDBC-107.prepare-statement :return-keys may now be a vector of (auto-generated) column names to return, in addition to just being truthy or falsey. This allows keys to be returned for more databases. JDBC-104.Release 0.4.0 / 0.4.1 on 2015-07-26
db-do-prepared now allows transaction? to be omitted when a PreparedStatement is passed as the second argument JDBC-111 - Stefan Kamphausen.Release 0.3.7 on 2015-05-18
Release 0.3.6 on 2014-10-28
Release 0.3.5 on 2014-08-01
Release 0.3.4 on 2014-06-30
Release 0.3.3 on 2014-01-30
Release 0.3.2 on 2013-12-30
Release 0.3.1 on 2013-12-29 (broken; use 0.3.2 instead)
Release 0.3.0 on 2013-12-16
Release 0.3.0-rc1 on 2013-12-12
Release 0.3.0-beta2 on 2013-11-24
clojure.java.jdbc.deprecated to help streamline the API for 0.3.0 and clean up the documentation.Release 0.3.0-beta1 on 2013-11-03
Release 0.3.0-alpha5 on 2013-09-15
Release 0.3.0-alpha4 on 2013-05-11
Release 0.3.0-alpha3 on 2013-05-04
Release 0.3.0-alpha2 on 2013-05-03
Release 0.3.0-alpha1 on 2013-04-07
Release 0.2.3 on 2012-06-18
Release 0.2.2 on 2012-06-10
Release 0.2.1 on 2012-05-10
Release 0.2.0 on 2012-04-23
Release 0.1.4 on 2012-04-15
Release 0.1.3 on 2012-02-29
Release 0.1.2 on 2012-02-29
Release 0.1.1 on 2011-11-02
Release 0.1.0 on 2011-10-16
Release 0.0.7 on 2011-10-11
Release 0.0.6 on 2011-08-04
Release 0.0.5 on 2011-07-18
Release 0.0.4 on 2011-07-17
Release 0.0.3 on 2011-07-01
Release 0.0.2 on 2011-06-07
Release 0.0.1 on 2011-05-07
Changes from clojure.contrib.sql:
Copyright (c) Sean Corfield, Stephen Gilardi, 2011-2014. All rights reserved. The use and distribution terms for this software are covered by the Eclipse Public License 1.0 (http://opensource.org/licenses/eclipse-1.0.php) which can be found in the file epl-v10.html at the root of this distribution. By using this software in any fashion, you are agreeing to be bound by the terms of this license. You must not remove this notice, or any other, from this software.
Can you improve this documentation? These fine people already did:
Sean Corfield, Shaun Mahood, Phil Hagelberg & Stuart HallowayEdit on GitHub
cljdoc builds & hosts documentation for Clojure/Script libraries
| Ctrl+k | Jump to recent docs |
| ← | Move to previous article |
| → | Move to next article |
| Ctrl+/ | Jump to the search field |