Non-blocking ODBC calls in Java

Question

I use pretty standard Java ODBC functionality - grab a Connection from the pool, create a Statement and execute it.

Our use-case is a game, logging game progress - ODBC calls are largely calls to stored procedures and in most cases there are no return values. So the fact the ODBC call blocks is annoying - the game is already turn based but users can see longer pauses if the DB is slow.

If I don't need to check the result of an ODBC call, is there any built-in functionality to execute the statement asyncronously? If not, what is a good way to do this without writing lots of code? I DO still need to catch ODBC exceptions when and if they occur.

This question looks related although not identical... Is asynchronous jdbc call possible?

That's kind of the question. If you think that's the best approach then please provide an example how to run an existing block of JDBC code in a thread. — Mr. Boy, Feb 20 '13 at 13:21
Would like to reply, but first - are you using JDBC to connect to ODBC datasource (a type 1 JDBC driver, aka JDBC-ODBC bridge)? Is your datasource MS Access or similar? — Glen Best, Feb 26 '13 at 04:24
Data source is MySQL, I can't remember the different driver types but I use mysql-connector-java-5.1.18-bin.jar and the MySQL classes to get a `java.sql.Connection` — Mr. Boy, Feb 26 '13 at 09:18

score 10 · Accepted Answer · answered Feb 23 '13 at 16:31

Let's assume you have a OdbcCaller:

public class OdbcCaller {

public void callODBC() {
    // call ODBC directly
    // ...
}

You can wrap it in a runnable task and submit the task to a thread pool to make it executes asyncronously:

public void asyncCallODBC() {
    // wrap the call with a runnable task
    executor.execute(new Runnable() {

        @Override
        public void run() {
            callODBC();
        }
    });
    // the above line would return immediately.
}

The executor is a thread pool implementation provided by JDK, it could be defined as follows:

Executor executor = new ThreadPoolExecutor(/* core pool size */5,
        /* maximum pool size */10,
        /* keepAliveTime */1,
        /* time unit of keepAliveTime */TimeUnit.MINUTES,
        /* work queue */new ArrayBlockingQueue<Runnable>(10000),
        /* custom thread factory */new ThreadFactory() {
            private AtomicInteger counter = new AtomicInteger(0);

            @Override
            public Thread newThread(Runnable r) {
                Thread t = new Thread(r, "asyncCaller-" + (counter.incrementAndGet()));
                return t;
            }
        },
        /*
         * policy applied when all tasks are occupied and task
         * queue is full.
         */new ThreadPoolExecutor.CallerRunsPolicy());

The ThreadPoolExecutor is highly configurable and is welled documented in JavaDoc, you might want read it first.

Following are some sugguestion of thread pool configurations based on my experiences:

The proper thread pool size is depending on the scenario, you may need run some tests to tune it.
The work queue is used to cache the tasks when there are no available worker threads. A unbounded queue is not a good idea as you might run out your memory.
It is a good practice to provide a ThreadFactory and give the threads a meaningful name. It will be very useful when you need to inspect threads states(using jstack or other tools).
The reject policy is applied when no resources is available. You can choose one of the build-in policy(reject, discard, caller-run, discardOldest), or implement your own policy.

So every method on my DAO class has to be converted to a separate `OdbcCaller` class... no way to simply wrap a method call with a C#-like delegate or anything? — Mr. Boy, Feb 25 '13 at 08:45
You can use `AOP` to avoid creating class of every DAO classes, take a look at [this post](http://stackoverflow.com/questions/325640/aspect-oriented-programming-in-java) — ericson, Feb 25 '13 at 08:52

score 0 · Answer 2 · answered Feb 27 '13 at 13:45

public class Snippet
{
    static int i = 0;



    public static void main(String[] args) throws SQLException
    {
        ExecutorService eventExecutor = Executors.newSingleThreadExecutor();
        final Connection c = DriverManager.getConnection("jdbc:url", "user",
                "password");
        Runnable r = new Runnable()
        {

            public void run()
            {
                try
                {
                    CallableStatement s = c
                            .prepareCall("{ call your_procedure(?) }");
                    s.setInt(1, i);
                    s.execute();
                } catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }
        };
        for (; i < 100; i++)
            eventExecutor.submit(r);
    }
}

Runs the call to your_procedure 100 times with 100 different parameters.

score 0 · Answer 3 · answered Feb 27 '13 at 16:41

0

If you are using EJB 3.1, you can create a @Stateless EJB, and mark one of the methods @Asynchronous. This is an out-of-the-box solution for Enterprise Java environment (There the external management of Thread resources are not recommended).

answered Feb 27 '13 at 16:41

gaborsch

15,408
6
37
48

Plain old Java I'm afraid - it's a server app not a web app. – Mr. Boy Feb 27 '13 at 20:38
Whats the difference between an HTTP server and a webapp? – AnthonyJClink Feb 09 '15 at 21:58

score -2 · Answer 4 · answered Feb 19 '13 at 18:36

-2

Not sure about this but maybe you can use an aynsctask? That handles db stuff in a different thread

answered Feb 19 '13 at 18:36

user1538814

201
1
3
13

When I search for `aynsctask` I only get results for Android. Is it a Java-6 class or a generic term? – Mr. Boy Feb 20 '13 at 10:26

Non-blocking ODBC calls in Java

4 Answers4