How to implement Java single Database thread

Question

I have made a Java program that connects to a SQLite database using SQLite4Java.

I read from the serial port and write values to the database. This worked fine in the beginning, but now my program has grown and I have several threads. I have tried to handle that with a SQLiteQueue-variable that execute database operations with something like this:

public void insertTempValue(final SQLiteStatement stmt, final long logTime, final double tempValue)
{
    if(checkQueue("insertTempValue(SQLiteStatement, long, double)", "Queue is not running!", false))
    {
        queue.execute(new SQLiteJob<Object>()
                {
                    protected Object job(SQLiteConnection connection) throws SQLiteException
                    {
                        stmt.bind(1, logTime);
                        stmt.bind(2, tempValue);

                        stmt.step();
                        stmt.reset(true);

                        return null;
                    }
                });
    }
} // end insertTempValue(SQLiteStatement, long, double)

But now my SQLite-class can't execute the statements reporting :

DB[1][U]: disposing [INSERT INTO Temperatures VALUES (?,?)]DB[1][U] from alien thread
SQLiteDB$6@8afbefd: job exception com.almworks.sqlite4java.SQLiteException: [-92] statement is disposed

So the execution does not happen.

I have tried to figure out what's wrong and I think I need a Java wrapper that makes all the database operations calls from a single thread that the other threads go through.

Here is my problem I don't know how to implement this in a good way. How can I make a method-call and ensure that it always runs from the same thread?

Do you need to ensure that the same thread is used, or that only one thread at time accesses the database? Most likely your `stmt` is being reused or disposed after this method is called. — Peter Lawrey, Apr 21 '14 at 20:07
I need to ensure that the same thread is used. I try to reuse the stmt, but it is then I get this. — John Master, Apr 21 '14 at 20:28
You're probably asking the wrong question. What you should really be looking into is how to use the database in multiple threads. That usually implies a connection per thread, and everything that flows from that. — user207421, Apr 22 '14 at 03:14
The JobQueue is a [single thread](http://code.google.com/p/sqlite4java/wiki/JobQueue) 'that other threads go through'. To use it, create 1 instance of it in the application (at startup) and re-use it everywhere. — vanOekel, Apr 22 '14 at 07:44

Warren Dew · Answer 1 · 2014-04-22T16:55:37.000

Put all your database access code into a package and make all the classes package private. Write one Runnable or Thread subclass with a run() method that runs a loop. The loop checks for queued information requests, and runs the appropriate database access code to find the information, putting the information into the request and marking the request complete before going back to the queue.

Client code queues data requests and waits for answers, perhaps by blocking until the request is marked complete.

Data requests would look something like this:

public class InsertTempValueRequest {

    // This method is called from client threads before queueing
    // Client thread queues this object after construction
    public InsertTempValueRequest(
        final long logTime,
        final double tempValue
    ) {
         this.logTime = logTime
         this.tempValue = tempValue
    }

    // This method is called from client threads after queueing to check for completion
    public isComplete() {
        return isComplete;
    }

    // This method is called from the database thread after dequeuing this object
    execute(
        SQLiteConnection connection,
        SQLiteStatement statement
    ) {
        // execute the statement using logTime and tempValue member data, and commit

        isComplete = true;
    }

    private volatile long logTime;
    private volatile double tempValue;
    private volatile boolean isComplete = false;
}

This will work, but I suspect there will be a lot of hassle in the implementation. I think you could also get by by using a lock that only permits one thread at a time to access the database, and also - this is the difference from your existing situation - beginning the access by creating the database resources - including statements - from scratch, and disposing of those resources before releasing the lock.

I think that the first solution you mentioned there sounds robust and good. But I don't really know how to solve communication between the subclass that checks for requests and the other threads. How do I make a request from another thread to that class? Can you supply a code-snippet? — John Master, Apr 22 '14 at 10:12
Thank you, I have now implemented my solution, but with the help of ExecutorService to make all database operations operating from one single thread. — John Master, Apr 27 '14 at 10:08

score 0 · Accepted Answer · answered Apr 27 '14 at 10:17

0

I found a solution to my problem. I have now implemented a wrapper-class that makes all operations with my older SQLite-class using an ExecutorService, inspired from Thread Executor Example and got the correct usage from Java Doc ExecutorService.

answered Apr 27 '14 at 10:17

John Master

309
1
4
18

How to implement Java single Database thread

2 Answers2