clojure.java.jdbc — org.clojure/java.jdbc 0.4.2

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. In general, operations return the number of rows affected, except for a single record insert where any generated keys are returned (as a map).

For more documentation, see:

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

As of release 0.3.0, the API has undergone a major overhaul and most of the original API has been deprecated in favor of a more idiomatic API. The original API has been moved to java.jdbc.deprecated for backward compatibility but it will be removed before a 1.0.0 release.

as-sql-name clj

(as-sql-name f)

(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)

Given a table name and column specs with an optional table-spec return the DDL string for creating that table.

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-spec sql-command & sql-commands)

(db-do-commands db-spec transaction? sql-command & sql-commands)

Executes SQL commands on the specified database connection. Wraps the commands in a transaction if transaction? is true. transaction? can be ommitted and it defaults to true. Uses executeBatch. This may affect what SQL you can run via db-do-commands.

db-do-prepared clj

(db-do-prepared db-spec sql & param-groups)

(db-do-prepared db-spec transaction? sql & param-groups)

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 param-group)

(db-do-prepared-return-keys db transaction? sql param-group)

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 ommitted and will default to true. Return the generated keys for the (single) update/insert.

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-spec [sql-string & params] func)

(db-query-with-resultset db-spec [stmt & params] func)

(db-query-with-resultset db-spec [options-map sql-string & params] func)

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) [options sql & params] - options and a SQL query for creating a PreparedStatement, followed by any parameters it needs See prepare-statement for supported options. 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 macro deprecated

(db-transaction binding & body)

Original name for with-db-transaction. Use that instead.

db-transaction* clj

(db-transaction* db func & {:keys [isolation read-only?]})

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
         &
         {:keys [entities transaction?]
          :or {entities identity transaction? true}})

Given a database connection, a table name and a where clause of columns to match, perform a delete. The optional keyword arguments 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 name & {:keys [entities] :or {entities identity}})

Given a table name, return the DDL string for dropping that table.

execute! clj

(execute! db-spec [sql & params] :multi? false :transaction? true)

(execute! db-spec [sql & param-groups] :multi? true :transaction? true)

Given a database connection and a vector containing SQL and optional parameters, perform a general (non-select) SQL operation. The optional keyword argument specifies whether to run the operation in a transaction or not (default true). 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!

get-connection clj

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

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 various possibilities are described below:

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

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

DriverManager: :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.

DriverManager (alternative): :dbtype (required) a String, the type of the database (the jdbc subprotocol) :dbname (required) a String, the name of the database :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.

DataSource: :datasource (required) a javax.sql.DataSource :username (optional) a String :user (optional) a String - an alternate alias for :username (added after 0.3.0-beta2 for consistency JDBC-74) :password (optional) a String, required if :username is supplied

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

Raw: :connection-uri (required) a String Passed directly to DriverManager/getConnection

URI: Parsed JDBC connection string - see below

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

insert! clj

(insert! db-spec table row-map :transaction? true :entities identity)

(insert! db-spec table row-map & row-maps :transaction? true :entities identity)

(insert! db-spec
         table
         col-name-vec
         col-val-vec
         &
         col-val-vecs
         :transaction? true
         :entities identity)

Given a database connection, a table name and either maps representing rows or a list of column names followed by lists of column values, perform an insert. Use :transaction? argument to specify whether to run in a transaction or not. The default is true (use a transaction). Use :entities to specify 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 additional optional arguments 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
                 &
                 {:keys [identifiers as-arrays? row-fn result-set-fn]
                  :or {identifiers str/lower-case row-fn identity}})

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 :identifiers, :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
                   &
                   {:keys [return-keys result-type concurrency cursors
                           fetch-size max-rows timeout]})

Create a prepared statement from a connection, a SQL string and an optional list of parameters: :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 :fetch-size n :max-rows n :timeout n

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-spec [sql-string & params])

(query db-spec [stmt & params])

(query db-spec [option-map sql-string & params])

(query db-spec
       sql-and-params
       :as-arrays? false
       :identifiers clojure.string/lower-case
       :result-set-fn doall
       :row-fn identity)

(query db-spec
       sql-and-params
       :as-arrays? true
       :identifiers clojure.string/lower-case
       :result-set-fn vec
       :row-fn identity)

Given a database connection and a vector containing SQL and optional parameters, perform a simple database query. The optional keyword arguments specify how to construct the result set: :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 :identifiers - applied to each column name in the result set, default lower-case :as-arrays? - return the results as a set of arrays, default false. The second argument is a vector containing a SQL string or PreparedStatement, followed by any parameters it needs. See db-query-with-resultset for details.

quoted clj

(quoted q)

(quoted q x)

With a single argument, returns a naming strategy function that quotes names. The single argument can either be a single character or a vector pair of characters. Can also be called with two arguments - a quoting argument and a name - and returns the fully quoted string: (quoted ` "foo") will return "foo" (quoted [[ ]] "foo") will return "[foo]"

result-set-seq clj

(result-set-seq rs
                &
                {:keys [identifiers as-arrays?]
                 :or {identifiers str/lower-case}})

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).

string-array clj

(string-array return-keys)

update! clj

(update! db
         table
         set-map
         where-clause
         &
         {:keys [entities transaction?]
          :or {entities identity transaction? true}})

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 optional keyword arguments 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] ... 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] ... 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.