A Serverless, Zero-Configuration Database Solution : SQLite

Most software need saving data. Sometimes that data is predicted to be small and hundreds or thousands of transactions on it will not be needed at the same time. But you will need some SQL-like operations on that data, because some modifications can be difficult and time consuming with regular file operations. At that time, SQLite becomes a very practical solution to this situation.

It started as a C/C++ library (on http://www.sqlite.org/) but it also has Xerial jdbc project for Java (on http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC and https://bitbucket.org/xerial/sqlite-jdbc lastly). We will tell some details of Java JDBC project here. Some critical properties are:

Download latest jar (from here: https://bitbucket.org/xerial/sqlite-jdbc/downloads) and add to the classpath.

• Needs one line of code to start using.

Connection con = DriverManager.getConnection("jdbc:sqlite:mydb.db"); line creates "mydb.db" file as database file on the root of your project and creates a connection for DB operations.

Some code examples are shown below (connection opening/closing statements are not included each time for simplifying statements):

    // opening connection
    Connection con = DriverManager.getConnection("jdbc:sqlite:person.db");
    Statement stat = con.createStatement();

    // closing connection
    con.close();
     
    // creating table
    stat.executeUpdate("create table person(id INT, name varchar(30));");
    // dropping table
    stat.executeUpdate("drop table if exists person");
    // inserting data
    PreparedStatement prep = con.prepareStatement("insert into person values(?,?);");
    prep.setString(1, "1");
    prep.setString(2, "andy brown");
    prep.execute();
    // selecting data
    ResultSet res = stat.executeQuery("select * from person");
    while (res.next()) {
         System.out.println(res.getString("id") + " " + res.getString("name"));
    }
    // updating data
    PreparedStatement prep = con.prepareStatement("update person set name = ? where id = ?;");
    prep.setInt(1, "andy black");
    prep.setString(2, 1);
    prep.execute();

For more detailed examples about SQL syntax, please take a look at:

http://docs.oracle.com/javase/tutorial/jdbc/index.html