First I've made a singleton object to connect to SQLite, but also I've made a few methods to make even more easier to use sqlite4java.
import com.almworks.sqlite4java.*;
import java.io.File;
import java.util.Stack;
public class SQLiteDB {
private static final SQLiteDB INSTANCE = new SQLiteDB();
SQLiteConnection db;
SQLiteStatement st;
// Private constructor prevents instantiation from other classes
private SQLiteDB() {
try{
db = new SQLiteConnection(new File("default.ndb"));
db.open(true);
} catch (SQLiteException ex){
System.out.println("Instantiation SQLiteException: " + ex.getMessage());
}
}
public static SQLiteDB getInstance() {
return INSTANCE;
}
public boolean exec(String str){
try{
st = db.prepare(str);
st.stepThrough();
st.dispose();
return true;
} catch (SQLiteException ex){
System.out.println("Query Execution SQLiteException: " + ex.getMessage());
return false;
}
}
public boolean prepareQuery(String str){
try{
st = db.prepare(str);
} catch (SQLiteException ex){
System.out.println("Prepare Query SQLiteException: " + ex.getMessage());
return false;
}
return true;
}
public Object[] fetch(){
try{
if(!st.step()){
st.dispose();
return null;
}
else{
if(st.hasRow()) {
int columns = st.columnCount();
Stack stack = new Stack();
for(int column = 0 ; column < columns ; column++)
stack.push(st.columnValue(column));
return stack.toArray();
} else {
st.dispose();
return null;
}
}
} catch (SQLiteException ex){
System.out.println("Fetch SQLiteException: " + ex.getMessage());
}
st.dispose();
return null;
}
public void close(){
db.dispose();
}
}
Then the test class:
public class SQLiteTest {
public static void main(String[] args) {
SQLiteDB db = SQLiteDB.getInstance();
db.exec("CREATE TABLE t1 (t1key INTEGER PRIMARY KEY,data TEXT,num double,timeEnter DATE);");
db.exec("INSERT INTO t1 VALUES(1, 'This is sample data', 3, NULL);");
db.exec("INSERT INTO t1 VALUES(2, 'More sample data', 6, NULL);");
db.exec("INSERT INTO t1 VALUES(3, 'And a little more', 9, NULL);");
Object[] res;
db.prepareQuery("select * from t1");
while((res=db.fetch()) != null){
System.out.println("Results: " + res[0] + " : " + res[1] + " : " + res[2] + " : " + res[3]);
}
db.close();
}
}
