Here's the Java code (from http://www.zentus.com/sqlitejdbc/)
import java.sql.*;And here's the two Clojure versions of it.
public class Test {
public static void main(String[] args) throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists people;");
stat.executeUpdate("create table people (name, occupation);");
PreparedStatement prep = conn.prepareStatement(
"insert into people values (?, ?);");
prep.setString(1, "Gandhi");
prep.setString(2, "politics");
prep.addBatch();
prep.setString(1, "Turing");
prep.setString(2, "computers");
prep.addBatch();
prep.setString(1, "Wittgenstein");
prep.setString(2, "smartypants");
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
ResultSet rs = stat.executeQuery("select * from people;");
while (rs.next()) {
System.out.println("name = " + rs.getString("name"));
System.out.println("job = " + rs.getString("occupation"));
}
rs.close();
conn.close();
}
}
(import '(java.sql DriverManager SQLException))Note that you have to have the SQLite driver installed for these examples to work - you can find some form of it and instructions on how to install it here.
(use 'clojure.contrib.sql)
(defn direct-port []
(Class/forName "org.sqlite.JDBC")
(let [conn (DriverManager/getConnection "jdbc:sqlite:test.db")
stat (doto (.createStatement conn)
(.executeUpdate "drop table if exists people")
(.executeUpdate "create table people (name, occupation);"))
prep (doto (.prepareStatement conn "insert into people values (?, ?);")
(.setString 1 "Gandhi")
(.setString 2 "politics")
(.addBatch)
(.setString 1 "Turing")
(.setString 2 "computers")
(.addBatch)
(.setString 1, "Wittgenstein")
(.setString 2, "smartypants")
(.addBatch))]
(.setAutoCommit conn false)
(.executeBatch prep)
(.setAutoCommit conn true)
(let [results (.executeQuery stat "select * from people")]
(loop []
(when (.next results)
(println "name =" (.getString results "name"))
(println "job =" (.getString results "occupation"))
(recur))))
(.close conn)))
(defn contrib-sql []
(with-connection {:classname "org.sqlite.JDBC"
:subprotocol "sqlite"
:subname "test.db"}
(try
(drop-table :people)
(catch SQLException e))
(create-table :people [:name :text] [:occupation :text])
(insert-values :people
[:name :occupation]
["Gandhi" "politics"]
["Turing" "computers"]
["Wittgenstein" "smartypants"])
(with-results results "select * from people"
(doseq [record results]
(println "name =" (:name record))
(println "job =" (:occupation record))))))
(println "Java-y version") (direct-port)
(println "Lispy version") (contrib-sql)