21 Feb'11

SQLite in Java

Small and portable databases are great in sense of reliability, portability and ease of use once created.

As many people use java, I’ve tried to investigate how easy is to create a simple java&sqlite application. After the stackOverflow)) it took just few minutes:

1. http://www.zentus.com/sqlitejdbc/ this jdbc driver has the sqlite embedded for any platform already - great choice for everyone except enterprise.

  1. Source is here - with some addions:
import java.sql.*;
public class Main {

/**
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn =
DriverManager.getConnection("jdbc:sqlite:sqliteBase.db");
Statement stat = conn.createStatement();
//stat.executeUpdate("drop table if exists people;");
try {
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);
} catch (Exception e) {
// table already created
//TODO: handle that better
}
ResultSet rs = stat.executeQuery("select * from people where occupation like \"poli%\";");
while (rs.next()) {
System.out.print("name = " + rs.getString("name"));
System.out.println(";\tjob = " + rs.getString("occupation"));
}
rs.close();
conn.close();
}
}

To see what are you doing, use this plugin: https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/

Good luck!

Comments