H2 database crud examples in Clojure

In this example we will create an in memory database in H2 database and use Clojure to create a table in the database. And insert some records to the database then query it.

Step 1. Get dependencies

We need H2 database and the Clojure wrapper to the JDBC driver. H2 database already contains the JDBC driver.

 
com.h2database/h2 "1.3.148"
org.clojure/java.jdbc "0.3.6"
 

Step 2. Configure the database

The settings we will use

 
(def demo-settings
   {
    :classname   "org.h2.Driver"
    :subprotocol "h2:mem"
    :subname     "demo;DB_CLOSE_DELAY=-1"
    :user        "sa"
    :password    ""
   }
)
 

The mem means create the database in memory, demo is the name of database, it's optional, if no provided, use the default one.

The final connection string may be something like this

 
jdbc:h2:mem:demo;DB_CLOSE_DELAY=-1
 

Step 3. Insert data and query

Use insert! to update or insert the database, for select, use query

 
(require '[clojure.java.jdbc :as j])
(j/query demo-settings ["show databases"])
 
(j/execute! demo-settings ["create table csvdata (id int primary key, name varchar(100), age int)"])
(j/execute! demo-settings ["insert into csvdata values(1, 'hello', 20)"])
(j/execute! demo-settings ["insert into csvdata values(2, 'world', 21)"])
(j/query demo-settings ["select * from csvdata"])
(j/query demo-settings ["select * from csvdata where name = 'hello'"])