To JSON or not to JSON

How to simply convert a sqlite database to JSON

Last week my collegue Matteo (aka pupunzi) was porting an iPad application (booo!) to HTML5 (yeah!).

The iPad application was a virtual museum that was using a sqlite dabatase for reading data relative to rooms, biography, history and so on.

Even if sqlite is internally supported by some browsers (e.g. Chrome) the easiest way to read data in javascript is to have a JSON object.

I wrote a simple java code to dump the whole database into a JSON object.

import net.sf.json.JSONObject;
import net.sf.json.JSONArray;
import java.sql.*;
import java.util.List;
import java.util.ArrayList;
public class DB2JSON {

  public static void main(String[] args) {

    if (args.length&lt;1)
    Connection conn = null;

    try {
      String dbFile = args[0];
      JSONObject ret = new JSONObject();
      conn = DriverManager.getConnection(&quot;jdbc:sqlite:&quot; + dbFile);
      Statement stat = conn.createStatement();

      ResultSet tables = stat.executeQuery(&quot;SELECT name 
         FROM sqlite_master 
         WHERE type=&#039;table&#039; ORDER BY name;&quot;);
      List tableNames = new ArrayList();
      while ( {

      for (String tableName : tableNames) {
        JSONArray jsa = new JSONArray();

        ResultSet rows = stat.executeQuery("select * from " + 
           tableName + ";");

        while ( {
          JSONObject row = new JSONObject();
          ResultSetMetaData meta = rows.getMetaData();
          for (int i = 1; i &lt;= meta.getColumnCount(); i++) {
            row.element(meta.getColumnName(i), rows.getObject(i));

        ret.element(tableName, jsa);


      // result on console

      //result on file
      FileOutputStream fos = new FileOutputStream(dbFile+&quot;.json&quot;);

    } catch (Throwable e) {
      try {
        if (conn != null)
      } catch (SQLException s) {

The resulting JSON object will have a property for each table containing an array of records. Different types of properties are supported (String, int, long, dates etc.)

There are two dependencies to external libraries:



P.S.: the pupunzi’s porting result is an HTML5 application that runs everywhere and it is looks even more beautiful than the iOS one Smile !

Tagged as: , , ,

2 thoughts on “To JSON or not to JSON”

  1. what if I have foreign key relationships on the database? I need to get an object inside an object or list of objects inside an object depending on one-to-one or one-to-many relationship.

    1. I think your problem cannot be resolved with (a simple) general solution. Having a foreign key doesn’t mandatory means the best mapping is to have a collection inside the object (e.g.: your foreign-object could be referenced by another one); you could need a “link” object.

      This problem is known as “ORM” see

      In those cases I add a prototyped function to the object, that finds the foreign object by id.

Leave a Reply to sp Cancel reply
