A Clojure library for JDBC access.
Clojars coordinates: [asphalt "0.6.7"]
(requires Java 7 or higher, Clojure 1.6 or higher)
(require '[asphalt.core :as a]) ; for most common operations
(require '[asphalt.transaction :as t]) ; for transactions
You need a valid JDBC connection source (instance of asphalt.type.IConnectionSource
protocol) to work with Asphalt.
The following are supported by default:
:connection
(java.sql.Connection
instance):factory
(fn that accepts a map and returns a JDBC connection):classname
(JDBC driver classname), :connection-uri
(JDBC URL string):subprotocol
(sub-protocol portion of JDBC URL string), :subname
(rest of the JDBC URL string):datasource
(javax.sql.DataSource
instance) with following optional attributes
:username
or :user
(database user name):password
(database password):name
(JNDI name) with optional attributes
:context
(javax.naming.Context
):environment
(environment map)javax.sql.DataSource
instance)java.sql.Connection
instance)For development you may define a map based connection source:
(def conn-source {:subprotocol "mysql"
:subname "//localhost/testdb"
:username "testdb_user"
:password "secret"})
Typically one would create a connection-pooled datasource as connection source for production use:
This section covers the minimal examples only. Advanced features are covered in subsequent sections.
;; insert row, returning auto-generated keys
(a/genkey conn-source
"INSERT INTO emp (name, salary, dept) VALUES (?, ?, ?)"
["Joe Coder" 100000 "Accounts"])
;; update rows, returning the number of rows updated
;; used for `INSERT`, `UPDATE`, `DELETE` statements, or DDL statements such as `ALTER TABLE`, `CREATE INDEX` etc.
(a/update conn-source "UPDATE emp SET salary = ? WHERE dept = ?" [110000 "Accounts"])
;; query zero (nil) or more rows (vector of rows)
(a/query a/fetch-rows
conn-source
"SELECT name, salary, dept FROM emp" [])
;; query zero (nil) or one row (exception is thrown if result-set has more than one row)
(a/query a/fetch-optional-row
conn-source
"SELECT name, salary, dept FROM emp" [])
;; query one row, and work with column values via de-structuring
(let [[name salary dept] (a/query a/fetch-single-row ...)]
;; work with the column values
...)
Ordinary SQL with ?
place-holders may be boring and tedious to work with. Asphalt uses SQL-templates to fix that.
(a/defsql sql-insert "INSERT INTO emp (name, salary, dept) VALUES ($name, $salary, $dept)")
(a/defsql sql-update "UPDATE emp SET salary = $new-salary WHERE dept = $dept")
With SQL-templates, you can pass param maps with keys as param names:
(a/genkey conn-source sql-insert
{:name "Joe Coder" :salary 100000 :dept "Accounts"})
(a/update conn-source sql-update {:new-salary 110000 :dept "Accounts"})
SQL-templates defined with defsql
are invokable as functions:
;; defsql infers connection worker as either update or query
(sql-update conn-source {:new-salary 110000 :dept "Accounts"})
;; for genkey we need to specify the :conn-worker option as such
(a/defsql sql-insert "INSERT INTO emp (name, salary, dept) VALUES ($name, $salary, $dept)"
{:conn-worker a/genkey})
(sql-insert conn-source {:name "Joe Coder" :salary 100000 :dept "Accounts"})
;; same as above, but using positional params
(sql-insert conn-source ["Joe Coder" 100000 "Accounts"])
The examples we saw above read and write values as objects, which means we depend on the JDBC driver for the conversion. SQL-templates let you optionally specify the types of params and also the result columns in a query:
(a/defsql sql-insert
"INSERT INTO emp (name, salary, dept) VALUES (^string $name, ^int $salary, ^string $dept)")
(a/defsql sql-select "SELECT ^string name, ^int salary, ^string dept FROM emp")
;; multi-value param
(a/defsql sql-update "UPDATE emp SET salary = ^int $new-salary WHERE dept IN (^strings $depts)")
The operations on the type-hinted SQL-templates remain the same as non type-hinted SQL templates, but internally the appropriate types are used when communicating with the JDBC driver.
Starting with version 0.6.6
, dynamic non-parameter variable substitution is supported. See the snippet below:
;; notice the ^sql type hint
(a/defsql insert-into
"INSERT INTO ^sql $table (id, dept) VALUES ($id, $dept)")
;; value is passed like normal parameters
(insert-into conn-source {:table "emp_697"
:id "F69-2239-AX"
:dept "Accounts"})
;; the above SQL is treated as: INSERT INTO emp_697 (id, dept) VALUES ($id, $dept)
The following types are supported as type hints:
Type | Comments | Multi-value | Result on NULL |
---|---|---|---|
sql | Non-param substitution | none | not applicable |
nil | Dynamic/slow discovery | none | nil |
array | arrays | nil | |
ascii-stream | ascii-streams | nil | |
big-decimal | big-decimals | nil | |
binary-stream | binary-streams | nil | |
blob | blobs | nil | |
bool | Duplicate of boolean | bools | false |
boolean | booleans | false | |
byte | bytes | 0 | |
byte-array | byte-arrays | nil | |
character-stream | character-stream | nil | |
clob | clobs | nil | |
date | dates | nil | |
double | doubles | 0.0 | |
float | floats | 0.0 | |
int | ints | 0 | |
integer | Duplicate of int | integers | 0 |
long | longs | 0 | |
ncharacter-stream | ncharacter-streams | nil | |
nclob | nclobs | nil | |
nstring | nstrings | nil | |
object | Catch-all type | objects | nil |
ref | refs | nil | |
row-id | row-ids | nil | |
string | strings | nil | |
sql-xml | sql-xmls | nil | |
time | times | nil | |
timestamp | timestamps | nil | |
url | urls | nil |
Note on type hints in result columns:
NULL
value as primitive default values as shown in the table.^^
(shortcut) as type hint to imply default or no type hint, e.g.
SELECT ^^ name, ^^ age, ^string join_date FROM emp WHERE id = ^int $id
Note on multi-value types:
?
placeholdersSELECT *
) in return columns are tricky to use with return column type hints. You should hint
every return column type as in SELECT * ^int ^string ^int ^date
if the return columns are of that type.UNION
are also tricky to use with return column type hints. You should hint only one set of
return columns, not in every UNION
sub-query.Simple example:
(t/with-transaction [txn conn-source] {}
(a/update txn sql-insert ["Joe Coder" 100000 "accounts"])
(a/update txn sql-update {:new-salary new-salary :id id}))
By default, if the code doesn't throw any exception the transaction would be committed and on all exceptions the transaction would be rolled back.
(a/with-transaction [txn data-source] {:isolation :read-committed
:propagation t/tp-mandatory}
(let [[id salary dept] (a/query a/fetch-single-row txn sql-select-with-id [])
new-salary (compute-new-salary salary dept)]
(a/update txn sql-update {:new-salary new-salary :id id})))
Supported isolation levels:
:none
:read-committed
:read-uncommitted
:repeatable-read
:serializable
Supported transaction propagation types:
t/tp-mandatory
t/tp-nested
t/tp-never
t/tp-not-supported
t/tp-required
t/tp-requires-new
t/tp-supports
Given a fn that accepts a connection source as its first argument, it is possible to wrap it with transaction options such that the fn is invoked in a transaction.
(defn foo
[conn-source emp-id]
..)
(def bar (t/wrap-transaction-options foo {:isolation :read-committed
:propagation t/tp-requires-new}))
;; call foo in a transaction as per the transaction options
(bar conn-source emp-id)
Running tests: lein do clean, test
or lein with-profile c18,dev,dbcp test
Running performance benchmarks: lein with-profile c18,dev,dbcp,perf test
Copyright © 2015-2018 Shantanu Kumar (kumar.shantanu@gmail.com, shantanu.kumar@concur.com)
Distributed under the Eclipse Public License either version 1.0 or (at your option) any later version.
Can you improve this documentation?Edit on GitHub
cljdoc is a website building & hosting documentation for Clojure/Script libraries
× close