Velocity Reviews

Velocity Reviews (http://www.velocityreviews.com/forums/index.php)
-   Java (http://www.velocityreviews.com/forums/f30-java.html)
-   -   JDBC PreparedStatement in a multi-threaded environment (http://www.velocityreviews.com/forums/t644638-jdbc-preparedstatement-in-a-multi-threaded-environment.html)

vk02720@gmail.com 11-15-2008 04:26 PM

JDBC PreparedStatement in a multi-threaded environment
 
How to use a PreparedStatement in a multi-threaded environment to
take advantage of precompilation? Is a statement once prepared usable
across a different thread?

For example in the following method, would it matter if I use a
PreparedStatement vs Statement assuming updateStat() can be called my
multiple threads and I execute only once in this method.

public void updateStat() {
Connection con = getConnection(); // this is retrieved in thread safe
manner
PreparedStatement stmt = con.prepareStatement(
"UPDATE MYTABLE SET STATUS= ? WHERE ID = ? ");
...
...
}

Appreciate any insights and/or useful usage patterns.

TIA

Arne Vajh°j 11-15-2008 05:17 PM

Re: JDBC PreparedStatement in a multi-threaded environment
 
vk02720@gmail.com wrote:
> How to use a PreparedStatement in a multi-threaded environment to
> take advantage of precompilation? Is a statement once prepared usable
> across a different thread?
>
> For example in the following method, would it matter if I use a
> PreparedStatement vs Statement assuming updateStat() can be called my
> multiple threads and I execute only once in this method.
>
> public void updateStat() {
> Connection con = getConnection(); // this is retrieved in thread safe
> manner
> PreparedStatement stmt = con.prepareStatement(
> "UPDATE MYTABLE SET STATUS= ? WHERE ID = ? ");
> ..
> ..
> }
>
> Appreciate any insights and/or useful usage patterns.


It depends !

It is up to the Connection implementation whether it will have
some PreparedStatement pool behind the scene.

My guess would be that:
* most JDBC driver Connection will not
* most connection pool Connection will have an option to do so

I know that Jakarta DBCP has the option.

Note that there are other reasons than performance to prefer
PreparedStatement over Statement like robustness and security.

It is also implementation specific whether PreparedStatement
actually is faster than Statement even for repeated usage (I would
expect it to be in most cases though).

Arne

Tom Anderson 11-15-2008 07:37 PM

Re: JDBC PreparedStatement in a multi-threaded environment
 
On Sat, 15 Nov 2008, vk02720@gmail.com wrote:

> How to use a PreparedStatement in a multi-threaded environment to take
> advantage of precompilation? Is a statement once prepared usable across
> a different thread?


I don't know.

> For example in the following method, would it matter if I use a
> PreparedStatement vs Statement assuming updateStat() can be called my
> multiple threads and I execute only once in this method.
>
> public void updateStat() {
> Connection con = getConnection(); // this is retrieved in thread safe
> manner
> PreparedStatement stmt = con.prepareStatement(
> "UPDATE MYTABLE SET STATUS= ? WHERE ID = ? ");
> ..
> ..
> }


I'd pessimistically assume that this would recompile the statement every
time.

I'd be tempted to take charge of my own destiny and do something like:

public class StatusUpdater {
private Connection conn ;
private PreparedStatement updateStmt ;

public StatusUpdater(Connection conn) throws SQLException {
this.conn = conn ;
conn.setAutoCommit(true) ;
updateStmt = conn.prepareStatement("UPDATE MYTABLE SET STATUS = ? WHERE ID = ? ") ;
}
public void updateStatus(String id, String status) throws SQLException {
updateStmt.setString(1, status) ;
updateStmt.setString(2, id) ;
int rows = updateStmt.executeUpdate() ;
if (rows != 1) throw new SQLException("bad row count: " + rows) ;
}
public boolean isValid() {
try {
return conn.isValid() ;
}
catch (SQLException e) {
return false ;
}
}
public void close() throws SQLException {
conn.close() ;
}
}

import org.apache.commons.pool.PoolableObjectFactory ;

public class StatusUpdaterFactory implements PoolableObjectFactory {
private String dbURL ;
private Properties dbProps ;

public Object makeObject() throws SQLException {
Connection conn = DriverManager.getConnection(dbURL, dbProps) ;
return new StatusUpdater(conn) ;
}
public boolean validateObject(Object obj) {
return ((StatusUpdater)obj).isValid() ;
}
public void destroyObject(Object obj) throws SQLException {
((StatusUpdater)obj).close() ;
}
public void activateObject(Object obj) {}
public void passivateObject(Object obj) {}
}

import org.apache.commons.pool.impl.StackObjectPool ;
import org.apache.commons.pool.impl.ObjectPool ;

String dbURL ;
Properties dbProps ;
ObjectPool statusUpdaterPool = new StackObjectPool(new StatusUpdaterFactory(dbURL, dbProps), 10) ;

And then:

public void updateStat(String id, String status) {
StatusUpdater updater = (StatusUpdater)statusUpdaterPool.borrowObject() ;
try {
updater.updateStatus(id, status) ;
}
finally {
statusUpdaterPool.returnObject(updater) ;
}
}

Hopefully, the Apache Commons guys will get round to making the pool stuff
generic at some point. Should be pretty trivial.

tom

--
You are in a twisty maze of directories, all alike. In front of you is
a broken pipe...

Arne Vajh°j 11-15-2008 07:46 PM

Re: JDBC PreparedStatement in a multi-threaded environment
 
Tom Anderson wrote:
> I'd be tempted to take charge of my own destiny and do something like:
>
> public class StatusUpdater {
> private Connection conn ;
> private PreparedStatement updateStmt ;
>
> public StatusUpdater(Connection conn) throws SQLException {
> this.conn = conn ;
> conn.setAutoCommit(true) ;
> updateStmt = conn.prepareStatement("UPDATE MYTABLE SET STATUS =
> ? WHERE ID = ? ") ;
> }
> public void updateStatus(String id, String status) throws
> SQLException {
> updateStmt.setString(1, status) ;
> updateStmt.setString(2, id) ;
> int rows = updateStmt.executeUpdate() ;
> if (rows != 1) throw new SQLException("bad row count: " + rows) ;
> }
> public boolean isValid() {
> try {
> return conn.isValid() ;
> }
> catch (SQLException e) {
> return false ;
> }
> }
> public void close() throws SQLException {
> conn.close() ;
> }
> }
>
> import org.apache.commons.pool.PoolableObjectFactory ;
>
> public class StatusUpdaterFactory implements PoolableObjectFactory {
> private String dbURL ;
> private Properties dbProps ;
>
> public Object makeObject() throws SQLException {
> Connection conn = DriverManager.getConnection(dbURL, dbProps) ;
> return new StatusUpdater(conn) ;
> }
> public boolean validateObject(Object obj) {
> return ((StatusUpdater)obj).isValid() ;
> }
> public void destroyObject(Object obj) throws SQLException {
> ((StatusUpdater)obj).close() ;
> }
> public void activateObject(Object obj) {}
> public void passivateObject(Object obj) {}
> }
>
> import org.apache.commons.pool.impl.StackObjectPool ;
> import org.apache.commons.pool.impl.ObjectPool ;
>
> String dbURL ;
> Properties dbProps ;
> ObjectPool statusUpdaterPool = new StackObjectPool(new
> StatusUpdaterFactory(dbURL, dbProps), 10) ;
>
> And then:
>
> public void updateStat(String id, String status) {
> StatusUpdater updater =
> (StatusUpdater)statusUpdaterPool.borrowObject() ;
> try {
> updater.updateStatus(id, status) ;
> }
> finally {
> statusUpdaterPool.returnObject(updater) ;
> }
> }


That is a very specific solution.

> Hopefully, the Apache Commons guys will get round to making the pool
> stuff generic at some point. Should be pretty trivial.


They have.

http://commons.apache.org/dbcp/configuration.html

<quote>
Parameter Default Description
poolPreparedStatements false Enable prepared statement pooling for
this pool.
maxOpenPreparedStatements unlimited The maximum number of open
statements that can be allocated from the statement pool at the same
time, or zero for no limit.

This component has also the ability to pool PreparedStatements. When
enabled a statement pool will be created for each Connection and
PreparedStatements created by one of the following methods will be pooled:

* public PreparedStatement prepareStatement(String sql)
* public PreparedStatement prepareStatement(String sql, int
resultSetType, int resultSetConcurrency)
</quote>

which I assume does what we are talking about.

Arne


Tom Anderson 11-15-2008 08:23 PM

Re: JDBC PreparedStatement in a multi-threaded environment
 
On Sat, 15 Nov 2008, Arne Vajh°j wrote:

> Tom Anderson wrote:
>> I'd be tempted to take charge of my own destiny and do something like:

>
> That is a very specific solution.


It's a very specific problem!

>> Hopefully, the Apache Commons guys will get round to making the pool stuff
>> generic at some point. Should be pretty trivial.

>
> They have.
>
> http://commons.apache.org/dbcp/configuration.html


Actually, i meant replace Object with a type parameter in the existing
multipurpose pool code.

But ...

> <quote>
> Parameter Default Description
> poolPreparedStatements false Enable prepared statement pooling for this
> pool.
> maxOpenPreparedStatements unlimited The maximum number of open
> statements that can be allocated from the statement pool at the same time, or
> zero for no limit.
>
> This component has also the ability to pool PreparedStatements. When enabled
> a statement pool will be created for each Connection and PreparedStatements
> created by one of the following methods will be pooled:
>
> * public PreparedStatement prepareStatement(String sql)
> * public PreparedStatement prepareStatement(String sql, int
> resultSetType, int resultSetConcurrency)
> </quote>
>
> which I assume does what we are talking about.


That sounds perfect!

tom

--
You are in a twisty maze of directories, all alike. In front of you is
a broken pipe...

Arne Vajh°j 11-15-2008 08:46 PM

Re: JDBC PreparedStatement in a multi-threaded environment
 
Tom Anderson wrote:
> On Sat, 15 Nov 2008, Arne Vajh°j wrote:
>> Tom Anderson wrote:
>>> I'd be tempted to take charge of my own destiny and do something like:

>>
>> That is a very specific solution.

>
> It's a very specific problem!


Is it ?

I am pretty sure that they will have more than one PreparedStatement,
that they will add more over time and that they may have more web apps.

Which is why I believe that:

private Map<String,PreparedStatement> ps;

is better than:

private PreparedStatement ps1;
private PreparedStatement ps2;
private PreparedStatement ps3;

Arne

Tom Anderson 11-16-2008 03:44 PM

Re: JDBC PreparedStatement in a multi-threaded environment
 
On Sat, 15 Nov 2008, Arne Vajh°j wrote:

> Tom Anderson wrote:
>> On Sat, 15 Nov 2008, Arne Vajh°j wrote:
>>> Tom Anderson wrote:
>>>> I'd be tempted to take charge of my own destiny and do something like:
>>>
>>> That is a very specific solution.

>>
>> It's a very specific problem!

>
> Is it ?
>
> I am pretty sure that they will have more than one PreparedStatement,
> that they will add more over time and that they may have more web apps.


True. You could easily enough add those to the StatusUpdater, and rename
it ManyThingsDoer or StatementBattery or something.

> Which is why I believe that:
>
> private Map<String,PreparedStatement> ps;
>
> is better than:
>
> private PreparedStatement ps1;
> private PreparedStatement ps2;
> private PreparedStatement ps3;


I disagree. On the face of it, that looks like it would require less
case-specific boilerplate code, but unless you have some way of setting
the parameters on your statements without case-specific code, it doesn't.
If you had a map, you'd still be writing code like:

private Map<String,PreparedStatement> ps;

public void updateFavouriteColour(String id, String colour) {
PreparedStatement ufcStmt =
ps.get("UPDATE user_profile SET favourite_colour = ? WHERE id = ?") ;
ufcStmt.setString(1, colour) ;
ufcStmt.setString(2, id) ;
ufcStmt.executeUpdate() ;
}

In which case you might as well write:

private PreparedStatement ufcStmt =
conn.prepareStatement("UPDATE user_profile SET favourite_colour = ? WHERE id = ?") ;

public void updateFavouriteColour(String id, String colour) {
ufcStmt.setString(1, colour) ;
ufcStmt.setString(2, id) ;
ufcStmt.executeUpdate() ;
}

If you use the PreparedStatements directly, rather than wrapping them in a
method, then this argument doesn't apply. But why would you do that?

Question: my understanding is that you can only have one PreparedStatement
per Connection in use at a time. That is, if i make two PreparedStatements
from one Connection, and run them both at the same time from two separate
threads, i'll get into trouble. Is that right?

If it is, i think that implementing the map approach so that it doesn't
waste Connections is going to make it more complicated than the
StatusUpdater-on-steroids approach.

If not, the map approach starts to look a *lot* better to me.

tom

--
Understanding the universe is the final purpose, as far as I'm
concerned. -- Ian York

Arne Vajh°j 11-16-2008 04:07 PM

Re: JDBC PreparedStatement in a multi-threaded environment
 
Tom Anderson wrote:
> On Sat, 15 Nov 2008, Arne Vajh°j wrote:
>> Which is why I believe that:
>>
>> private Map<String,PreparedStatement> ps;
>>
>> is better than:
>>
>> private PreparedStatement ps1;
>> private PreparedStatement ps2;
>> private PreparedStatement ps3;

>
> I disagree. On the face of it, that looks like it would require less
> case-specific boilerplate code, but unless you have some way of setting
> the parameters on your statements without case-specific code, it
> doesn't. If you had a map, you'd still be writing code like:
>
> private Map<String,PreparedStatement> ps;
>
> public void updateFavouriteColour(String id, String colour) {
> PreparedStatement ufcStmt =
> ps.get("UPDATE user_profile SET favourite_colour = ? WHERE id =
> ?") ;
> ufcStmt.setString(1, colour) ;
> ufcStmt.setString(2, id) ;
> ufcStmt.executeUpdate() ;
> }
>
> In which case you might as well write:
>
> private PreparedStatement ufcStmt =
> conn.prepareStatement("UPDATE user_profile SET favourite_colour = ?
> WHERE id = ?") ;
>
> public void updateFavouriteColour(String id, String colour) {
> ufcStmt.setString(1, colour) ;
> ufcStmt.setString(2, id) ;
> ufcStmt.executeUpdate() ;
> }


The Map will not be where you think it will be.

The trick is:

public class PooledConnection {
private Connection nonpooledcon;
private Map<String,PreparedStatement> ps;
...
public PreparedStatement prepareStatement(String sql) {
if(ps.containsKey(sql)) {
return ps.get(sql);
} else {
PreparedStatement temp = nonpooledcon.prepareStatement(sql);
ps.put(sql, temp);
return temp;
}
}
}

The cached prepared statements are completely transparent to the
user code no matter how many prepared statements they need.

> Question: my understanding is that you can only have one
> PreparedStatement per Connection in use at a time. That is, if i make
> two PreparedStatements from one Connection, and run them both at the
> same time from two separate threads, i'll get into trouble. Is that right?
>
> If it is, i think that implementing the map approach so that it doesn't
> waste Connections is going to make it more complicated than the
> StatusUpdater-on-steroids approach.


That is correct.

But I can not see the point.

If you do anything on the same connection from two threads then
you will end up in problems, so you will not do that no matter
what you do regarding prepared statements.

And the map is per connection !

Arne



Arne Vajh°j 11-16-2008 04:17 PM

Re: JDBC PreparedStatement in a multi-threaded environment
 
Arne Vajh°j wrote:
> public class PooledConnection {


public class PooledConnection implements Connection {

Arne

vk02720@gmail.com 11-17-2008 01:33 AM

Re: JDBC PreparedStatement in a multi-threaded environment
 
On Nov 16, 11:17*am, Arne Vajh°j <a...@vajhoej.dk> wrote:
> Arne Vajh°j wrote:
>
> *> public class PooledConnection {
>
> public class PooledConnection implements Connection {
>
> Arne



So, are you saying that even if DBCP is used, you would still need to
code your own PooledConnection or the DBCP PoolableConnection does
what you are trying to show using PooledConnection?

DBCP does seem to solve this problem in a generic way. Agree that 2
threads will not use the same Connection - well, thats the idea behind
connection pools in a way so each thread can get their own Connection
without creating/closing every time. Did not know that only one
prepared statement can be used at one time though.

Having a pooled object (StatusUpdater - rather than more specifically
Connection) also is a good idea if StatusUpdater does a lot more than
just invoke one update statment. Otherwise it seems too much
scaffolding code behind just one udate! Actually, in my real
application I do have to do little more than 1 update so this could be
a usable idea where I need one such object per thread.

Thanks - these were both useful ideas. Would it be any safer/different
if I use Hiberate or any other ORM?

That brings another concern - is there any other good pattern / API
etc. also for doing per-thread stuff in Java? Or ThreadLocal is the
only way to go? Basically, wrapping your resource in a class and
internally using ThreadLocals. I did not see the DBCP impl code etc
but guessing ThreadLocal must be used internally?


All times are GMT. The time now is 07:48 AM.

Powered by vBulletin®. Copyright ©2000 - 2014, vBulletin Solutions, Inc.
SEO by vBSEO ©2010, Crawlability, Inc.