Clojure Lein can't find Oracle JDBC Driver

Question

I am trying to connect to an Oracle DB using Clojure/Lein.

I downloaded the ojdbc.jar from here: http://mirrors.ibiblio.org/maven/mule/dependencies/maven1/oracle-jdbc/jars/

I then installed it to my local repo using:

mvn install:install-file -Dfile=C:\dev\tools\ojdbc14.jar -DgroupId=com.oracle -DartifactId=ojdbc14 -Dversion=9.2.0.5 -Dpackaging=jar -DgeneratePom=true

My project.clj looks like this:

(defproject spike "0.1.0-SNAPSHOT"
  :description "spike"
  :url "http://example.com/FIXME"
  :license {:name "Eclipse Public License"
            :url "http://www.eclipse.org/legal/epl-v10.html"}
  :dependencies [[org.clojure/clojure "1.5.1"]
                [org.clojure/java.jdbc "0.3.0-alpha4"]
                [compojure "1.1.5"]
                [ring/ring-json "0.1.2"]
                [oracle/classes12dms "9.0.4"]
                [com.oracle/ojdbc14 "9.2.0.5"]
        ]

  :plugins [[lein-ring "0.8.5"] [lein-deps-tree "0.1.2"]]
  :ring {:handler spike.handler/app}
   :repositories [["internal" "http://xxx:8080/artifactory/repo"] 
                 ["jboss" "https://repository.jboss.org/nexus/content/groups/public"]
                 ["jboss-dep" "https://repository.jboss.org/nexus/content/repositories/deprecated"] ]

  )

And the accessing spikeDb.clj looks like this:

(ns spike.spikeDb
  (:require [clojure.java.jdbc :as jdbc]
            [clojure.java.jdbc.sql :as sql])
  (:import  java.sql.Types))

(def devdb {:classname "oracle.jdbc.driver.OracleDriver"
            :subprotocol "oracle"
            :subname "thin:xxx/xxx@xxx:1528/xxx"})

(defn get-xxx [id]
 (jdbc/query devdb
   (sql/select [:TITLE :DESCRIPTION]
             :XXX (sql/where {:CODE id}))))

I load the server using

lein ring server

But when I navigate to that page I get this:

java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:xxx/xxx@xxx:1528/xxx
DriverManager.java:604  java.sql.DriverManager.getConnection
DriverManager.java:190  java.sql.DriverManager.getConnection
jdbc.clj:206    clojure.java.jdbc/get-connection
jdbc.clj:628    clojure.java.jdbc/db-with-query-results*
jdbc.clj:646    clojure.java.jdbc/query
RestFn.java:425 clojure.lang.RestFn.invoke
spikeDb.clj:13  spike.spikeDb/get-xxx

What am I doing wrong??!

Answer 1

I've been struggling with the same error. The only way I've found to make this work is to not use a maven groupId of com.oracle, but instead something else like local.

Try this (note the new groupId):

mvn install:install-file -Dfile=C:\dev\tools\ojdbc14.jar -DgroupId=local -DartifactId=ojdbc14 -Dversion=9.2.0.5 -Dpackaging=jar -DgeneratePom=true

And then reference the ojdbc dependency in project.clj like so:

[local/ojdbc14 "9.2.0.5"]

Answer 2

The easiest way to download the latest Oracle driver and install it with lein 2 in your local repository, is using the lein-localrepo plugin:

To use it, edit your ~/.lein/profiles.clj and add {:user {:plugins [[lein-localrepo "0.5.3"]]}}

Now from the commandline use it to install the driver into your local repository

lein localrepo install -r D:\Path\To\Repo\
                          D:\Path\To\ojdbc6.jar
                          oracle.jdbc/oracledriver "12.1.0.1"

Now you can reference it in your project.clj, together with clojure.java.jdbc.

(defproject spike "0.1.0-SNAPSHOT"
  :dependencies [[org.clojure/java.jdbc "0.3.3"]
                 [oracle.jdbc/oracledriver "12.1.0.1"]])

Looking at your current db-spec, I notice that the driver class, subprotocol and subname are incorrect

(ns spike.spikedb
  (:require [clojure.java.jdbc :as jdbc]))

(def db
  {:classname    "oracle.jdbc.OracleDriver"
   :subprotocol  "oracle:thin"
   :subname      "//@hostname:port:sid"
   :user         "username"
   :password     "password"}))

(jdbc/query db ["select ? as one from dual" 1])