CODEWITHSUNDEEP
javamysqljdbcconcurrency
Dmitry D
Dmitry D

Reputation: 760

JDBC Concurrency implementation

I am receiving a data from the socket. My ServerSocket creats a new thread for each new request. Then a need to upload the data to MySQL database. A database connection and all required prepared statemnts were created beforehand.

Well, I want to process all requests at the same time. Is that mean, that I need several connections to the database? I was considering pooled connections, but prepared statemens must be created only one time at application startup.

What is the best way to implement that to achive the best performance?

What if I replace my database with Oracle. Should I use several connections? Tom Kyte in the first chapter of his book appeals to use only one connection to the Oracle database, while MS SQLServer is used with several ones. Does it mean, that I can execute several statements using only one connection?

Upvotes: 2

Views: 1985

Answers (2)

Sergey Gazaryan
Sergey Gazaryan

Reputation: 1043

You need pool of connection with cached connections for more details you can see http://en.wikipedia.org/wiki/Connection_pool. You can have cached prepared statements and after each execute clear parameters. For best performance you need use Thread pool also not need create a new thread for each new request. I think you can create some pool of prepared statements , and retrieving those by some key (you can define keys for queries). Something like that :

public class StatementPool{

  HashMap<String, Queue<Statement>> statementsPoolMap = ...
  public Statement getStatementByKey(String key){ // you can define keys for queries

     Queue<Statement> queue = statementsPoolMap.get (key); 
     Statement statement = null;
     if(queue.isEmpty()){
       Statement st =  connectionPool.getConnection().prepareStatement();
       ... 
     }else{
       statement = queue.poll();
     }
     ...
     return statement
  }

}

Upvotes: 3

Sanjay T. Sharma
Sanjay T. Sharma

Reputation: 23208

Is that mean, that I need several connections to the database?

Yes, you'll need multiple connections to the database if you want to service multiple clients at the same time. Consider using a connection pool to get around the high initial cost of creating a connection.

Regarding performance, the best advice would be something which is tailored specifically to your application needs so we need more details here. What kind of data is passed by the client? Large amount of data less frequently or small chunks of data more frequently? How many queries executed for a given client? Are you using stored procedures or a single insert/update query for each client?

Upvotes: 0

Related Questions