clojure.java.jdbc — org.clojure/java.jdbc 0.7.11

A Clojure interface to SQL databases via JDBC

clojure.java.jdbc provides a simple abstraction for CRUD (create, read, update, delete) operations on a SQL database, along with basic transaction support. Basic DDL operations are also supported (create table, drop table, access to table metadata).

Maps are used to represent records, making it easy to store and retrieve data. Results can be processed using any standard sequence operations.

For most operations, Java's PreparedStatement is used so your SQL and parameters can be represented as simple vectors where the first element is the SQL string, with ? for each parameter, and the remaining elements are the parameter values to be substituted.

For more documentation, see:

http://clojure-doc.org/articles/ecosystem/java_jdbc/home.html

as-sql-name clj

(as-sql-name f x)

Given a naming strategy function and a keyword or string, return a string per that naming strategy. A name of the form x.y is treated as multiple names, x, y, etc, and each are turned into strings via the naming strategy and then joined back together so x.y might become x.y if the naming strategy quotes identifiers with `.

Connectable clj protocol

add-connection clj

(add-connection db connection)

get-level clj

(get-level db)

create-table-ddl clj

(create-table-ddl table specs)

(create-table-ddl table specs opts)

Given a table name and a vector of column specs, return the DDL string for creating that table. Each column spec is, in turn, a vector of keywords or strings that is converted to strings and concatenated with spaces to form a single column description in DDL, e.g., [:cost :int "not null"] [:name "varchar(32)"] The first element of a column spec is treated as a SQL entity (so if you provide the :entities option, that will be used to transform it). The remaining elements are left as-is when converting them to strings. An options map may be provided that can contain: :table-spec -- a string that is appended to the DDL -- and/or :entities -- a function to specify how column names are transformed. :conditional? -- either a boolean, indicating whether to add 'IF NOT EXISTS', or a string, which is inserted literally before the table name, or a function of two arguments (table name and the create statement), that can manipulate the generated statement to better support other databases, e.g., MS SQL Server which need to wrap create table in an existence query.

db-connection clj

(db-connection db)

Returns the current database connection (or throws if there is none)

db-do-commands clj

(db-do-commands db sql-commands)

(db-do-commands db transaction? sql-commands)

Executes SQL commands on the specified database connection. Wraps the commands in a transaction if transaction? is true. transaction? can be omitted and it defaults to true. Accepts a single SQL command (string) or a vector of them. Uses executeBatch. This may affect what SQL you can run via db-do-commands.

db-do-prepared clj

(db-do-prepared db sql-params)

(db-do-prepared db transaction? sql-params)

(db-do-prepared db transaction? sql-params opts)

Executes an (optionally parameterized) SQL prepared statement on the open database connection. Each param-group is a seq of values for all of the parameters. transaction? can be omitted and defaults to true. The sql parameter can either be a SQL string or a PreparedStatement. Return a seq of update counts (one count for each param-group).

db-do-prepared-return-keys clj

(db-do-prepared-return-keys db sql-params)

(db-do-prepared-return-keys db transaction? sql-params)

(db-do-prepared-return-keys db transaction? sql-params opts)

Executes an (optionally parameterized) SQL prepared statement on the open database connection. The param-group is a seq of values for all of the parameters. transaction? can be omitted and will default to true. Return the generated keys for the (single) update/insert. A PreparedStatement may be passed in, instead of a SQL string, in which case :return-keys MUST BE SET on that PreparedStatement!

db-find-connection clj

(db-find-connection db)

Returns the current database connection (or nil if there is none)

db-is-rollback-only clj

(db-is-rollback-only db)

Returns true if the outermost transaction will rollback rather than commit when complete

db-query-with-resultset clj

(db-query-with-resultset db sql-params func)

(db-query-with-resultset db sql-params func opts)

Executes a query, then evaluates func passing in the raw ResultSet as an argument. The second argument is a vector containing either: [sql & params] - a SQL query, followed by any parameters it needs [stmt & params] - a PreparedStatement, followed by any parameters it needs (the PreparedStatement already contains the SQL query) The opts map is passed to prepare-statement. Uses executeQuery. This may affect what SQL you can run via query.

db-set-rollback-only! clj

(db-set-rollback-only! db)

Marks the outermost transaction such that it will rollback rather than commit when complete

db-transaction* clj

(db-transaction* db func)

(db-transaction* db func opts)

Evaluates func as a transaction on the open database connection. Any nested transactions are absorbed into the outermost transaction. By default, all database updates are committed together as a group after evaluating the outermost body, or rolled back on any uncaught exception. If rollback is set within scope of the outermost transaction, the entire transaction will be rolled back rather than committed when complete. The isolation option may be :none, :read-committed, :read-uncommitted, :repeatable-read, or :serializable. Note that not all databases support all of those isolation levels, and may either throw an exception or substitute another isolation level. The read-only? option puts the transaction in readonly mode (if supported).

db-unset-rollback-only! clj

(db-unset-rollback-only! db)

Marks the outermost transaction such that it will not rollback when complete

delete! clj

(delete! db table where-clause)

(delete! db table where-clause opts)

Given a database connection, a table name and a where clause of columns to match, perform a delete. The options may specify how to transform column names in the map (default 'as-is') and whether to run the delete in a transaction (default true). Example: (delete! db :person ["zip = ?" 94546]) is equivalent to: (execute! db ["DELETE FROM person WHERE zip = ?" 94546])

drop-table-ddl clj

(drop-table-ddl table)

(drop-table-ddl table {:keys [entities conditional?] :or {entities identity}})

Given a table name, return the DDL string for dropping that table. An options map may be provided that can contain: :entities -- a function to specify how column names are transformed. :conditional? -- either a boolean, indicating whether to add 'IF EXISTS', or a string, which is inserted literally before the table name, or a function of two arguments (table name and the create statement), that can manipulate the generated statement to better support other databases, e.g., MS SQL Server which need to wrap create table in an existence query.

execute! clj

(execute! db sql-params)

(execute! db sql-params opts)

Given a database connection and a vector containing SQL (or PreparedStatement) followed by optional parameters, perform a general (non-select) SQL operation.

The :transaction? option specifies whether to run the operation in a transaction or not (default true).

If the :multi? option is false (the default), the SQL statement should be followed by the parameters for that statement.

If the :multi? option is true, the SQL statement should be followed by one or more vectors of parameters, one for each application of the SQL statement.

If :return-keys is provided, db-do-prepared-return-keys will be called instead of db-do-prepared, and the result will be a sequence of maps containing the generated keys. If present, :row-fn will be applied. If :multi? then :result-set-fn will also be applied if present. :as-arrays? may also be specified (which will affect what :result-set-fn is passed).

If there are no parameters specified, executeUpdate will be used, otherwise executeBatch will be used. This may affect what SQL you can run via execute!

find-by-keys clj

(find-by-keys db table columns)

(find-by-keys db table columns opts)

Given a database connection, a table name, a map of column name/value pairs, and an optional options map, return any matching rows.

An :order-by option may be supplied to sort the rows, e.g.,

{:order-by [{:name :asc} {:age :desc} {:income :asc}]}
;; equivalent to:
{:order-by [:name {:age :desc} :income]}

The :order-by value is a sequence of column names (to sort in ascending order) and/or maps from column names to directions (:asc or :desc). The directions may be strings or keywords and are not case-sensitive. They are mapped to ASC or DESC in the generated SQL.

Note: if a ordering map has more than one key, the order of the columns in the generated SQL ORDER BY clause is unspecified (so such maps should only contain one key/value pair).

get-by-id clj

(get-by-id db table pk-value)

(get-by-id db table pk-value pk-name-or-opts)

(get-by-id db table pk-value pk-name opts)

Given a database connection, a table name, a primary key value, an optional primary key column name, and an optional options map, return a single matching row, or nil. The primary key column name defaults to :id.

get-connection clj

(get-connection db-spec)

(get-connection {:keys [connection factory connection-uri classname subprotocol
                        subname dbtype dbname host port datasource username
                        password user name environment]
                 :as db-spec}
                opts)

Creates a connection to a database. db-spec is usually a map containing connection parameters but can also be a URI or a String.

The only time you should call this function is when you need a Connection for prepare-statement -- no other public functions in clojure.java.jdbc accept a raw Connection object: they all expect a db-spec (either a raw db-spec or one obtained via with-db-connection or with-db-transaction).

The correct usage of get-connection for prepare-statement is:

(with-open [conn (jdbc/get-connection db-spec)]
  ... (jdbc/prepare-statement conn sql-statement options) ...)

Any connection obtained via calling get-connection directly must be closed explicitly (via with-open or a direct call to .close on the Connection object).

The various possibilities are described below:

DriverManager (preferred): :dbtype (required) a String, the type of the database (the jdbc subprotocol) :dbname (required) a String, the name of the database :classname (optional) a String, the jdbc driver class name :host (optional) a String, the host name/IP of the database (defaults to 127.0.0.1) :port (optional) a Long, the port of the database (defaults to 3306 for mysql, 1433 for mssql/jtds, else nil) (others) (optional) passed to the driver as properties (may include :user and :password)

Raw: :connection-uri (required) a String Passed directly to DriverManager/getConnection (both :user and :password may be specified as well, rather than passing them as part of the connection string)

Other formats accepted:

Existing Connection: :connection (required) an existing open connection that can be used but cannot be closed (only the parent connection can be closed)

DriverManager (alternative / legacy style): :subprotocol (required) a String, the jdbc subprotocol :subname (required) a String, the jdbc subname :classname (optional) a String, the jdbc driver class name (others) (optional) passed to the driver as properties (may include :user and :password)

Factory: :factory (required) a function of one argument, a map of params (others) (optional) passed to the factory function in a map

DataSource: :datasource (required) a javax.sql.DataSource :username (optional) a String - deprecated, use :user instead :user (optional) a String - preferred :password (optional) a String, required if :user is supplied

JNDI: :name (required) a String or javax.naming.Name :environment (optional) a java.util.Map

java.net.URI: Parsed JDBC connection string (see java.lang.String format next)

java.lang.String: subprotocol://user:password@host:post/subname An optional prefix of jdbc: is allowed.

get-isolation-level clj

(get-isolation-level db)

Given a db-spec (with an optional connection), return the current transaction isolation level, if known. Return nil if there is no active connection in the db-spec. Return :unknown if we do not recognize the isolation level.

insert! clj

(insert! db table row)

(insert! db table cols-or-row values-or-opts)

(insert! db table cols values opts)

Given a database connection, a table name and either a map representing a rows, or a list of column names followed by a list of column values also representing a single row, perform an insert. When inserting a row as a map, the result is the database-specific form of the generated keys, if available (note: PostgreSQL returns the whole row). When inserting a row as a list of column values, the result is the count of rows affected (1), if available (from getUpdateCount after executeBatch). The row map or column value vector may be followed by a map of options: The :transaction? option specifies whether to run in a transaction or not. The default is true (use a transaction). The :entities option specifies how to convert the table name and column names to SQL entities.

insert-multi! clj

(insert-multi! db table rows)

(insert-multi! db table cols-or-rows values-or-opts)

(insert-multi! db table cols values opts)

Given a database connection, a table name and either a sequence of maps (for rows) or a sequence of column names, followed by a sequence of vectors (for the values in each row), and possibly a map of options, insert that data into the database.

When inserting rows as a sequence of maps, the result is a sequence of the generated keys, if available (note: PostgreSQL returns the whole rows). A separate database operation is used for each row inserted. This may be slow for if a large sequence of maps is provided.

When inserting rows as a sequence of lists of column values, the result is a sequence of the counts of rows affected (a sequence of 1's), if available. Yes, that is singularly unhelpful. Thank you getUpdateCount and executeBatch! A single database operation should be used to insert all the rows at once. This may be much faster than inserting a sequence of rows (which performs an insert for each map in the sequence).

Note: some database drivers need to be told to rewrite the SQL for this to be performed as a single, batched operation. In particular, PostgreSQL requires :reWriteBatchedInserts true and My SQL requires :rewriteBatchedStatement true (both non-standard JDBC options, of course!). These options should be passed into the driver when the connection is created (however that is done in your program).

The :transaction? option specifies whether to run in a transaction or not. The default is true (use a transaction). The :entities option specifies how to convert the table name and column names to SQL entities.

IResultSetReadColumn clj protocol

Protocol for reading objects from the java.sql.ResultSet. Default implementations (for Object and nil) return the argument, and the Boolean implementation ensures a canonicalized true/false value, but it can be extended to provide custom behavior for special types.

result-set-read-column clj

(result-set-read-column val rsmeta idx)

Function for transforming values after reading them from the database

ISQLParameter clj protocol

Protocol for setting SQL parameters in statement objects, which can convert from Clojure values. The default implementation just delegates the conversion to ISQLValue's sql-value conversion and uses .setObject on the parameter. It can be extended to use other methods of PreparedStatement to convert and set parameter values.

set-parameter clj

(set-parameter val stmt ix)

Convert a Clojure value into a SQL value and store it as the ix'th parameter in the given SQL statement object.

ISQLValue clj protocol

Protocol for creating SQL values from Clojure values. Default implementations (for Object and nil) just return the argument, but it can be extended to provide custom behavior to support exotic types supported by different databases.

sql-value clj

(sql-value val)

Convert a Clojure value into a SQL value.

metadata-query clj macro

(metadata-query meta-query & opt-args)

Given a Java expression that extracts metadata (in the context of with-db-metadata), and a map of options like metadata-result, manage the connection for a single metadata-based query. Example usage:

(with-db-metadata [meta db-spec] (metadata-query (.getTables meta nil nil nil (into-array String ["TABLE"])) {:row-fn ... :result-set-fn ...}))

metadata-result clj

(metadata-result rs-or-value)

(metadata-result rs-or-value opts)

If the argument is a java.sql.ResultSet, turn it into a result-set-seq, else return it as-is. This makes working with metadata easier. Also accepts an option map containing :identifiers, :keywordize?, :qualifier, :as-arrays?, :row-fn, and :result-set-fn to control how the ResultSet is transformed and returned. See query for more details.

prepare-statement clj

(prepare-statement con sql)

(prepare-statement con
                   sql
                   {:keys [return-keys result-type concurrency cursors
                           fetch-size max-rows timeout]})

Create a prepared statement from a connection, a SQL string and a map of options: :return-keys truthy | nil - default nil for some drivers, this may be a vector of column names to identify the generated keys to return, otherwise it should just be true :result-type :forward-only | :scroll-insensitive | :scroll-sensitive :concurrency :read-only | :updatable :cursors :hold | :close :fetch-size n :max-rows n :timeout n Note that :result-type and :concurrency must be specified together as the underlying Java API expects both (or neither).

print-sql-exception clj

(print-sql-exception exception)

Prints the contents of an SQLException to out

print-sql-exception-chain clj

(print-sql-exception-chain exception)

Prints a chain of SQLExceptions to out

print-update-counts clj

(print-update-counts exception)

Prints the update counts from a BatchUpdateException to out

query clj

(query db sql-params)

(query db sql-params opts)

Given a database connection and a vector containing SQL and optional parameters, perform a simple database query. The options specify how to construct the result set (and are also passed to prepare-statement as needed): :as-arrays? - return the results as a set of arrays, default false. :identifiers - applied to each column name in the result set, default lower-case :keywordize? - defaults to true, can be false to opt-out of converting identifiers to keywords :qualifier - optionally provides the namespace qualifier for identifiers :result-set-fn - applied to the entire result set, default doall / vec if :as-arrays? true, :result-set-fn will default to vec if :as-arrays? false, :result-set-fn will default to doall :row-fn - applied to each row as the result set is constructed, default identity The second argument is a vector containing a SQL string or PreparedStatement, followed by any parameters it needs. See also prepare-statement for additional options.

quoted clj

(quoted q)

Given a (vector) pair of delimiters (characters or strings), return a naming strategy function that will quote SQL entities with them. Given a single delimiter, treat it as a (vector) pair of that delimiter. ((quoted [[ ]]) "foo") will return "[foo]" -- for MS SQL Server ((quoted `') "foo") will return "foo" -- for MySQL Intended to be used with :entities to provide a quoting (naming) strategy that is appropriate for your database.

reducible-query clj

(reducible-query db sql-params)

(reducible-query db sql-params opts)

Given a database connection, a vector containing SQL and optional parameters, return a reducible collection. When reduced, it will start the database query and reduce the result set, and then close the connection: (transduce (map :cost) + (reducible-query db sql-params))

The following options from query etc are not accepted here: :as-arrays? :explain :explain-fn :result-set-fn :row-fn See prepare-statement for additional options that may be passed through.

If :raw? true is specified, the rows of the result set are not converted to hash maps, and it as if the following options were specified: :identifiers identity :keywordize? false :qualifier nil In addition, the rows of the result set may only be read as if they were hash maps (get, keyword lookup, select-keys) but the sequence representation is not available (so, no keys, no vals, and no seq calls). This is much faster than converting each row to a hash map but it is also more restrictive.

reducible-result-set clj

(reducible-result-set
  rs
  {:keys [identifiers keywordize? qualifier read-columns]
   :or {identifiers lower-case keywordize? true read-columns dft-read-columns}})

Given a java.sql.ResultSet return a reducible collection. Compiled with Clojure 1.7 or later -- uses clojure.lang.IReduce Note: :as-arrays? is not accepted here.

result-set-seq clj

(result-set-seq rs)

(result-set-seq
  rs
  {:keys [as-arrays? identifiers keywordize? qualifier read-columns]
   :or {identifiers lower-case keywordize? true read-columns dft-read-columns}})

Creates and returns a lazy sequence of maps corresponding to the rows in the java.sql.ResultSet rs. Loosely based on clojure.core/resultset-seq but it respects the specified naming strategy. Duplicate column names are made unique by appending _N before applying the naming strategy (where N is a unique integer), unless the :as-arrays? option is :cols-as-is, in which case the column names are untouched (the result set maintains column name/value order). The :identifiers option specifies how SQL column names are converted to Clojure keywords. The default is to convert them to lower case. The :keywordize? option can be specified as false to opt-out of the conversion to keywords. The :qualifier option specifies the namespace qualifier for those identifiers (and this may not be specified when :keywordize? is false).

string-array clj

(string-array return-keys)

update! clj

(update! db table set-map where-clause)

(update! db table set-map where-clause opts)

Given a database connection, a table name, a map of column values to set and a where clause of columns to match, perform an update. The options may specify how column names (in the set / match maps) should be transformed (default 'as-is') and whether to run the update in a transaction (default true). Example: (update! db :person {:zip 94540} ["zip = ?" 94546]) is equivalent to: (execute! db ["UPDATE person SET zip = ? WHERE zip = ?" 94540 94546])

when-available clj macro

(when-available sym & body)

with-db-connection clj macro

(with-db-connection binding & body)

Evaluates body in the context of an active connection to the database. (with-db-connection [con-db db-spec opts] ... con-db ...)

with-db-metadata clj macro

(with-db-metadata binding & body)

Evaluates body in the context of an active connection with metadata bound to the specified name. See also metadata-result for dealing with the results of operations that retrieve information from the metadata. (with-db-metadata [md db-spec opts] ... md ...)

with-db-transaction clj macro

(with-db-transaction binding & body)

Evaluates body in the context of a transaction on the specified database connection. The binding provides the database connection for the transaction and the name to which that is bound for evaluation of the body. The binding may also specify the isolation level for the transaction, via the :isolation option and/or set the transaction to readonly via the :read-only? option. (with-db-transaction [t-con db-spec {:isolation level :read-only? true}] ... t-con ...) See db-transaction* for more details.