Sunday, June 9, 2019

Clojure's Java Interop With JDBC

Clojure's ability to interoperate with legacy Java is perhaps more often seen as a hurdle to be overcome rather than as a benefit. Clojure's syntax for executing native Java is different but a little practice can overcome that unfamiliarity.

Note: Clojure offers better means of utilizing JDBC than the Java interop I'm using in this code. I coded this example to show how to set up a project and how to code for Java interop rather than to advocate its use.

The syntax of a Clojure execution of a Java method is (. obj method). Here are examples showing various means of executing Java with Clojure: 
(ns basic_interop.core
  (:import java.io.File java.util.Date java.util.Calendar))
(defn example [start end]
  (println start)
  (def dir (File. "/Users/gregh/.m2/repository"))
  (def path (.getAbsolutePath dir))
  (println path)
  (def date1 (Date.))
  (def date2 (new Date))
  (def date3 (.. Calendar getInstance getTime))
  (println (.getName (.getClass date1)) (.toString date1))
  (println (.. date3 getClass getName) (.toString date3))
  (println end))

(defn -main []
  (example "start..." "...end"))

Getting Clojure

To use Clojure on a laptop that didn't have Leiningen or Clojure, I simply did as instructed on https://leiningen.org/ and pasted the script copied from the leiningen web site into a file named lein that I saved in usr/local/bin folder and I then made the file executable.

Installing the Database

To perform the Clojure interaction with a database, I needed a database. I chose H2, the 'database in a jar'. The H2 database is simple to use and easy to setup. I downloaded the zip file containing the h2 database jar file from https://www.h2database.com and extracted the jar and ran it and, from the H2 console, ran the SQL to create the table:
create table Customers (id INT NOT NULL, cust_num INT NOT NULL,
first_name VARCHAR(64) NOT NULL, last_name VARCHAR(64) NOT NULL)

Creating the Clojure Project

Next, I ran lein new dbio to create the dbio project and cd dbio and then, to test the skeleton project, I ran lein run.

I modified the project.clj and the core.clj files as shown below. 
(defproject dbio "0.1.0-SNAPSHOT"
  :description ""
  :url "http://"
  :license {:name "none"
            :url "https://"}
  :dependencies [[org.clojure/clojure "1.10.0"]
                 [com.h2database/h2 "1.4.199"]]
  :main dbio.core
  :repl-options {:init-ns dbio.core})

(ns dbio.core
  (:gen-class)
  (:import org.h2.tools.Server 
      (java.sql Connection DriverManager Statement 
       SQLException ResultSet ResultSetMetaData)))

(defn foo
  [speaker]
  (println speaker " says \"Hello, World!\""))

(defn doJdbc []

  (def conn (DriverManager/getConnection "jdbc:h2:~/test", "sa", ""))

  (def stmt (.createStatement conn))

  (.executeUpdate stmt "DELETE Customers")

  (.executeUpdate stmt 
    "INSERT INTO Customers VALUES (1, 1001, 'Homer', 'Simpson')")

  (.executeUpdate stmt 
    "INSERT INTO Customers VALUES (2, 1002, 'Bart', 'Simpson')")

  (def rs (.executeQuery stmt "SELECT * FROM Customers"))

  (while (.next rs)
    (println (.getString rs 3))))

(defn -main []
  (foo "Greg")
  (doJdbc))

Running the Clojure

Executing the command in a bash terminal lein run I got the following output:
at