raju raju - 3 months ago 23
MySQL Question

How to create db threadpool in Play java and make db queries with this pool

I am currently using play java and making db queries also with default threadpool but understand that doing db queries with db thread pool can make my system more efficient. Currently my code is

import play.libs.Akka;
import scala.concurrent.ExecutionContext;

String sqlQuery = "very long query with lot of joins"
SqlQuery query = Ebean.createSqlQuery(sqlQuery);
List<SqlRow> rows = query.findList();
// do some computation over the data obtained and send as response


I am trying to create threadpool in the following manner

ExecutionContext myExecutionContext = Akka.system().dispatchers().lookup("play.akka.actor.my-context");


Am I creating threadpool in the right manner and if yes how do I use this threadpool to change my code to following

SqlQuery query = Ebean.createSqlQuery(sqlQuery);
CompletionStage<List<SqlRow>> = //query.findList(myExecutionContext)
// do some computation over the data obtained and send as response


I am trying to do it in the right way?

Note; If I use java executors present in

import java.util.concurrent.CompletableFuture;
import java.util.concurrent.CompletionStage;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;


how do I set the settings like the max threads? I would like to use this pool to make calls to oracle db using blocking driver available. So max threads I want in this pool is equal to number of db connections I can make to my database.

Answer

First of all, you should take a look at the ThreadPoll Configuration docs, if you haven't done that yet. It should give you an overview of the thread pools Play uses by default and how to tweak them (you may not need a specific thread pool just to do queries...).

It contains a lot of tips, including tips to configure thread pools optimized to JDBC operations.

Now, regarding your question, you just have to use supplyAsync with a custom ExecutorService to do what you need. An example:

public CompletionStage<Result> getData() {
    CompletableFuture<List<SqlRow>> cf = new CompletableFuture<>();
    return cf.supplyAsync(() -> {
        return Ebean.createSqlQuery("SELECT * FROM Users").findList();
    }, ec) // <-- 'ec' is the ExecutorService you want to use
    .thenApply(rows -> {
       return ok(Json.toJson(rows));
    }); 
}

Notice that from your code, you are using Akka to get an ExecutionContext (Scala), and the supplyAsync expects an Executor/ExecutorService (Java). So you will have to create your ServiceExecutor by yourself and share it

// Thread pool with 10 threads
ExecutorService ec = Executors.newFixedThreadPool(10);

or you will have to convert between them. This gist should help you achieve that