JDBC PreparedStatement in a multi-threaded environment

Discussion in 'Java' started by vk02720@gmail.com, Nov 15, 2008.

  1. Guest

    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
     
    , Nov 15, 2008
    #1
    1. Advertising

  2. Arne Vajhøj Guest

    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
     
    Arne Vajhøj, Nov 15, 2008
    #2
    1. Advertising

  3. Tom Anderson Guest

    On Sat, 15 Nov 2008, 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...
     
    Tom Anderson, Nov 15, 2008
    #3
  4. Arne Vajhøj Guest

    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
     
    Arne Vajhøj, Nov 15, 2008
    #4
  5. Tom Anderson Guest

    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...
     
    Tom Anderson, Nov 15, 2008
    #5
  6. Arne Vajhøj Guest

    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
     
    Arne Vajhøj, Nov 15, 2008
    #6
  7. Tom Anderson Guest

    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
     
    Tom Anderson, Nov 16, 2008
    #7
  8. Arne Vajhøj Guest

    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, Nov 16, 2008
    #8
  9. Arne Vajhøj Guest

    Arne Vajhøj wrote:
    > public class PooledConnection {


    public class PooledConnection implements Connection {

    Arne
     
    Arne Vajhøj, Nov 16, 2008
    #9
  10. Guest

    On Nov 16, 11:17 am, Arne Vajhøj <> 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?
     
    , Nov 17, 2008
    #10
  11. Arne Vajhøj Guest

    wrote:
    > On Nov 16, 11:17 am, Arne Vajhøj <> wrote:
    >> Arne Vajhøj wrote:
    >>
    >> > public class PooledConnection {

    >>
    >> public class PooledConnection implements Connection {

    >
    > 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 what I was outlining is a slightly smarter way than
    my naive implementation.

    I just suggested a simply adding to a map. DBCP has max number
    of statement to keep and apparently use a pooling mechanism, so
    they probably throw an exception in case the programmer try to
    prepare the same statement again without closing an open
    statement.

    The difference between real software quality and usenet posting
    quality.

    > 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.


    Yep.

    > Did not know that only one
    > prepared statement can be used at one time though.


    You can have multiple prepared statements associated with the
    same connection, but if you try to use them concurrently, then
    it messes up.

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


    I would expect a good ORM to do something similar - keep prepared
    statements around for reuse.

    > 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.


    Chose a design where all the data in your code are local variables,
    then your code will be thread safe.

    ThreadLocal is an ugly workaround that should be avoided if
    possible IMHO.

    Arne
     
    Arne Vajhøj, Nov 17, 2008
    #11
  12. Tom Anderson Guest

    On Sun, 16 Nov 2008, Arne Vajhøj wrote:

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


    Curses!

    > 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.


    Oh, i see. So you're using the map as a direct equivalent of my pile of
    fields. You still get a Connection out of a pool before you can use any of
    them.

    Okay, now i definitely maintain that my approach is no worse. Since at
    some point you have to write a method for each kind of statement that
    you're going to use, the extra textual overhead of putting those all in
    instance fields is negligible - you just turn the relevant local variable
    into a field.

    >> 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.


    Ignore me, i was thinking of something completely different.

    tom

    --
    Work alone does not suffice: the efforts must be intelligent. -- Charles
    B. Rogers
     
    Tom Anderson, Nov 17, 2008
    #12
  13. Tom Anderson Guest

    On Sun, 16 Nov 2008, wrote:

    > On Nov 16, 11:17 am, Arne Vajhøj <> wrote:
    >> Arne Vajhøj wrote:
    >>
    >>  > public class PooledConnection {
    >>
    >> public class PooledConnection implements Connection {

    >
    > 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?


    It can do it all itself. There are various ways to assemble the components
    it gives you to do it, but the simplest is to create a BasicDataSource and
    configure it appropriately. I think.

    > 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.


    'Used' as in actually called - you can prepare as many as you like, then
    use them later.

    > 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!


    Very true.

    > 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.


    Exactly. Then it basically becomes a Data Access Object.

    tom

    --
    Work alone does not suffice: the efforts must be intelligent. -- Charles
    B. Rogers
     
    Tom Anderson, Nov 17, 2008
    #13
  14. Guest

    On Nov 17, 3:09 pm, Tom Anderson <> wrote:
    > On Sun, 16 Nov 2008, wrote:
    > > On Nov 16, 11:17 am, Arne Vajhøj <> wrote:
    > >> Arne Vajhøj wrote:

    >
    > >>  > public class PooledConnection {

    >
    > >> public class PooledConnection implements Connection {

    >
    > > 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?

    >
    > It can do it all itself. There are various ways to assemble the components
    > it gives you to do it, but the simplest is to create a BasicDataSource and
    > configure it appropriately. I think.
    >
    > > 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.

    >
    > 'Used' as in actually called - you can prepare as many as you like, then
    > use them later.
    >
    > > 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!

    >
    > Very true.
    >
    > > 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.

    >
    > Exactly. Then it basically becomes a Data Access Object.
    >
    > tom
    >
    > --
    > Work alone does not suffice: the efforts must be intelligent. -- Charles
    > B. Rogers



    Does DBCP uses JDBC 3.0 connection pooling or is it an alternative or
    does it complements JDBC connection pooling..?
    Any ideas/examples if JDBC connection pooling can be used here or is
    that pretty much limited to app servers using that functionality?
    Could certain drivers be implementing connection pooling "under the
    covers" - meaning my code could look just like regular code using
    Connection interface using Connection conn =
    DriverManager.getConnection(dbURL, dbProps) and conn.close() but the
    driver actually does pooling?
     
    , Nov 27, 2008
    #14
  15. Tom Anderson Guest

    On Thu, 27 Nov 2008, wrote:

    > On Nov 17, 3:09 pm, Tom Anderson <> wrote:
    >> On Sun, 16 Nov 2008, wrote:
    >>> On Nov 16, 11:17 am, Arne Vajhøj <> wrote:
    >>>> Arne Vajhøj wrote:

    >>
    >>>>  > public class PooledConnection {

    >>
    >>>> public class PooledConnection implements Connection {

    >>
    >>> 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?

    >>
    >> It can do it all itself. There are various ways to assemble the components
    >> it gives you to do it, but the simplest is to create a BasicDataSource and
    >> configure it appropriately. I think.

    >
    > Does DBCP uses JDBC 3.0 connection pooling or is it an alternative or
    > does it complements JDBC connection pooling..?


    I think it's largely a way to *implement* JDBC connection pooling. Also,
    before pooling was a standard feature, it was a way of adding it on. And
    it's still useful - isn't connection pooling only supplied inside J2EE
    containers?

    > Could certain drivers be implementing connection pooling "under the
    > covers" - meaning my code could look just like regular code using
    > Connection interface using Connection conn =
    > DriverManager.getConnection(dbURL, dbProps) and conn.close() but the
    > driver actually does pooling?


    You mean could a vendor's driver be doing that? Yes, it could. I'd be
    surprised if it did, though - because that kind of stuff is done in the
    app server, there's no reason for a vendor to do it.

    tom

    --
    Any problem in computer science can be solved with another layer of
    indirection. -- David Wheeler
     
    Tom Anderson, Nov 27, 2008
    #15
  16. Guest

    On Nov 27, 4:39 pm, Tom Anderson <> wrote:
    > On Thu, 27 Nov 2008, wrote:
    > > On Nov 17, 3:09 pm, Tom Anderson <> wrote:
    > >> On Sun, 16 Nov 2008, wrote:
    > >>> On Nov 16, 11:17 am, Arne Vajhøj <> wrote:
    > >>>> Arne Vajhøj wrote:

    >
    > >>>>  > public class PooledConnection {

    >
    > >>>> public class PooledConnection implements Connection {

    >
    > >>> 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?

    >
    > >> It can do it all itself. There are various ways to assemble the components
    > >> it gives you to do it, but the simplest is to create a BasicDataSource and
    > >> configure it appropriately. I think.

    >
    > > Does DBCP uses JDBC 3.0 connection pooling or is it an alternative or
    > > does it complements JDBC connection pooling..?

    >
    > I think it's largely a way to *implement* JDBC connection pooling. Also,
    > before pooling was a standard feature, it was a way of adding it on. And
    > it's still useful - isn't connection pooling only supplied inside J2EE
    > containers?
    >
    > > Could certain drivers be implementing connection pooling "under the
    > > covers" - meaning my code could look just like regular code using
    > > Connection interface using Connection conn =
    > > DriverManager.getConnection(dbURL, dbProps)  and conn.close() but the
    > > driver actually does pooling?

    >
    > You mean could a vendor's driver be doing that? Yes, it could. I'd be
    > surprised if it did, though - because that kind of stuff is done in the
    > app server, there's no reason for a vendor to do it.
    >

    Agreed. Unless you are working in J2SE environment. I have to
    implement this in a stand alone executable where I am using an
    existing home grown multi-threading framework. I have to write a class
    which will just implement a method execute() and do all my stuff in
    this method which is going to be called by multiple threads.
    public int execute(Object myObject) {
    // open connection
    // do my thing
    // close connection

    return retStat;

    }
    So, in a way it is analogical to doing your business logic from a
    Servlet. execute() could be compared to service() or doGet() method.
    However this is in J2SE.
    Any recommendations on using any particular pattern or framework for
    stand alone programs for something like this? This is in JDK 1.4
    otherwise I could have looked at Executor framework - not sure if that
    it would have helped with pooled objects..

    > tom
    >
    > --
    > Any problem in computer science can be solved with another layer of
    > indirection. -- David Wheeler
     
    , Nov 28, 2008
    #16
  17. Lew Guest

    wrote:
    > Any recommendations on using any particular pattern or framework for
    > stand alone programs for something like this? This is in JDK 1.4
    > otherwise I could have looked at Executor framework - not sure if that
    > it would have helped with pooled objects..


    There is a Java 1.4 backport available for free of the java.util.concurrent
    classes. Or a pre-port, I forget which. Anyway, it works on that
    unsupported, obsolete, past its end-of-service-life, ancient version of Java.

    One presumes someone who writes the checks is foolishly insisting on such a
    risky requirement.

    --
    Lew
     
    Lew, Nov 28, 2008
    #17
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Rick
    Replies:
    1
    Views:
    581
    Michael Borgwardt
    Nov 17, 2003
  2. Eqbal Z
    Replies:
    3
    Views:
    4,434
    Sudsy
    May 18, 2004
  3. nukleus
    Replies:
    5
    Views:
    536
    Tom Hawtin
    Jan 19, 2007
  4. ian douglas
    Replies:
    2
    Views:
    1,007
    Randy Howard
    Jul 30, 2004
  5. Andreas Leitgeb
    Replies:
    18
    Views:
    1,888
    Andreas Leitgeb
    Jul 24, 2012
Loading...

Share This Page