An Architect's View

CFML, Clojure, Software Design, Frameworks and more...

An Architect's View

Connecting Clojure and MySQL

September 3, 2011 ·

A step-by-step guide. Assumes you have Leiningen installed!

> lein new mysql
Created new project in: .../mysql
> cd mysql
> vi project.clj

Add dependencies for clojure.java.jdbc and the MySQL connector:

(defproject mysql "1.0.0-SNAPSHOT"
  :description "FIXME: write description"
  :dependencies [[org.clojure/clojure "1.2.1"]
                 [org.clojure/java.jdbc "0.0.6"]
                 [mysql/mysql-connector-java "5.1.6"]])

Use Leiningen to fetch the dependencies:

> lein deps

Let's add a little code to exercise that:

> vi src/mysql/core.clj

Add a require of clojure.java.jdbc, define your database spec, define a function to list the contents of a known table:

(ns mysql.core
  (:require [clojure.java.jdbc :as sql]))

(def db {:classname "com.mysql.jdbc.Driver"
         :subprotocol "mysql"
         :subname "//localhost:3306/dbname"
         :user "dbuser"
         :password "secret"})

(defn list-users []
  (sql/with-connection db
    (sql/with-query-results rows
      ["select * from user"]
      (println rows))))

This assumes: MySQL is running locally (localhost) and you have a database called dbname which the user dbuser can access (via the password secret). The dbname database has a table called user.

Now let's run that:

> lein repl
REPL started; server listening on localhost port 31305
user=> (load "/mysql/core")
nil
user=> (mysql.core/list-users)
...

You should get the contents of the user table displayed as a vector of maps.

Tags: clojure

3 responses

  • 1 jim // Dec 25, 2011 at 6:18 PM

    I tried the code and get to (load "/mysql/core")
    I get nil
    then with (mysql.core/list-users)
    I don't see any output
    Is there another command to show table and count of records??
    Thanks for the guide!!
  • 2 jim // Dec 26, 2011 at 10:46 AM

    got code to work. Great tutorial

    thsi command doesn't show data
    mysql.core/list-users

    What is a command to show table or record count??
  • 3 Sean Corfield // Dec 30, 2011 at 3:44 PM

    @Jim, do you actually have any records in your user table?

    For more information, refer to the clojure.java.jdbc documentation:

    http://clojure.github.com/java.jdbc/