SQLite4Java Select, Update Example

Recently I started to work on some small Java project where I need to read and update data on SQLite data base. I searched for some good SQLite wrapper for Java and finally decided to use SQLiteJava wrapper. I found it more intuitive and faster than JDBC driver. Today I'll show how to use it in your Java application. 

First of all you'll need to download SQLite4Java wrapper files from here. 

Once you're done do this:

sqlite4java.jar put it on your CLASSPATH

sqlite4java-win32-x64.dll put in the same directory with the jar file

sqlite4java-win32-x86.dll put in the same directory with the jar file

I created SQLite4Java.java class which handles all my activities via SQLite file:

import java.io.File;
import java.util.ArrayList;
import com.almworks.sqlite4java.SQLiteConnection;
import com.almworks.sqlite4java.SQLiteConstants;
import com.almworks.sqlite4java.SQLiteException;
import com.almworks.sqlite4java.SQLiteStatement;

public class SQLite4Java {
    private SQLiteConnection _db;
    private String _delimiter = "[,]";

    public SQLite4Java(String inputFile) throws SQLiteException {
        _db = new SQLiteConnection(new File(inputFile));
        _db.open(true);
    }

    public String selectSingle(String query, String args) throws SQLiteException {
        SQLiteStatement st = _db.prepare(query);
        String[] parameters = args.split(_delimiter);
        String value = null;
        try {
            for (int i = 0; i < parameters.length; i++) {
                st.bind(i + 1, parameters[i]);
            }
            int columnsCount = st.columnCount();
            if (columnsCount > 1) {
                System.err.println("Error: More than a single column!");
                return null;
            }

            if (st.step()) {
                int type = st.columnType(0);

                switch (type) {
                    case SQLiteConstants.SQLITE_INTEGER:
                        value = String.valueOf(st.columnInt(0));

                    case SQLiteConstants.SQLITE_TEXT:
                        value = st.columnString(0);

                    default:
                        break;
                }
            }
        }
        catch (Exception e) {
            System.err.println(e.getMessage());
        }
        finally {
            st.dispose();
        }
        return value;
    }

    public ArrayList selectMany(String query, String args) throws SQLiteException {
        SQLiteStatement st = _db.prepare(query);
        String[] parameters;
        if (!args.isEmpty() && args != null) {
            parameters = args.split(_delimiter);
            for (int i = 0; i < parameters.length; i++) {
                st.bind(i + 1, parameters[i]);
            }
        }
        ArrayList list = new ArrayList();
        try {
            int columnsCount = st.columnCount();
            if (columnsCount > 1) {
                System.err.println("Error: More than a single column!");
                return null;
            }
            while (st.step()) {
                int type = st.columnType(0);

                switch (type) {
                    case SQLiteConstants.SQLITE_INTEGER:
                        list.add(String.valueOf(st.columnInt(0)));
                        break;

                    case SQLiteConstants.SQLITE_TEXT:
                        list.add(st.columnString(0));
                        break;

                    default:
                        break;
                }
            }
        }
        catch (Exception e) {
            System.err.println(e.getMessage());
        }
        finally {

            st.dispose();
        }
        return list;
    }

    public boolean updateSingleField(String query, String args) 
            throws SQLiteException {
        _db.exec("BEGIN TRANSACTION; ");
        SQLiteStatement st = _db.prepare(query);
        String[] parameters = args.split(_delimiter);
        try {
            for (int i = 0; i < parameters.length; i++) {
                st.bind(i + 1, parameters[i]);
            }
            st.step();
            _db.exec("COMMIT;");
        }
        catch (SQLiteException e) {
            System.err.println(e.getMessage());
        }
        finally {
            st.dispose();
        }
        return success;
    }
}

So what we got here?

• Constructor receives a path to the SQLite data base file and opens connection.
• selectSingle method receives query and arguments separated by comma delimiter and returns single value (In my project I need to work only with single fields, but you can edit the method in order to be able to handle data with multiple columns)
• selectMany method is similar to previous with only difference that it returns a list of strings. I also check the value type and always cast it to String
• updateSingleField updates single field in data base.

Here how you use it:

SQLite4Java sqlite = new SQLite4Java("c:\\db\\db");
String value = sqlite.selectSingle("SELECT _title FROM _issue WHERE id = ?", 4);
sqlite.updateSingleField("update _issue " +
                          "set _indicator = strftime('%Y%m%d%H%M%f', 'now') " +
                          "where _issue.id = ?", 3);

You can customize this class for your purposes and use it in your application. Good luck!