Java service with db connection pooling

[S....@stud.uni-heidelberg.de]

Hi!

I'm relatively new to Tuscany and the JEE world, so I have a few  
questions on to correctly provide a service in a production environment.

What I'm generally want to do is to provide the output of certain  
computational simulations stored in a database to a php-based web  
frontend, the output of my service is basically a (rather  
sophisticated) function of what the php frontend does to the database.  
I made a small dummy service in java along the sample shopping cart  
service included with the examples and connected it to php by jsonrpc.  
So far, it works great and provides exactly what I had in mind.

However, I'm wondering if I could improve performance by using  
database connection pooling and what happens when the service is  
invoked. As it looks to me, when the service is invoked, a new  
instance of my ..Impl class is created, a database connection is  
created and afterwards destroyed together with the ..Impl instance. Is  
that correct? What I'd prefer is just one instance for the whole time  
and one thread per user that can read all my private variables that  
are initialized when the service is started and that all the database  
connections are recycled. I'm not really sure if this is possible with  
the Tuscany distribution (currently using Tuscany 1.5.1 with Jetty,  
the current eclipse version doesn't install the Tuscany 1.6 plugin) or  
whether I need a JEE application server to do this (i.e. Geronimo).

If I do, how can I configure the eclipse Tuscany plugin, so that it  
runs the Geronimo version instead of the non-geronmio version that  
comes when the plugin is installed?

What would be the correct way of to provide connection pooling? As  
I've read somewhere, both Tomcat and Jetty provide a way to do this,  
then there's apache commons DBCP, and the javax.sql DataSource. I  
guess, I would prefer the javax.sql DataSource as it appears to me to  
provide the cleanest approach, extensibility and allows to configure  
details about the database driver outside of the code by JNDI, but I  
read that looking up a JNDI context can be slow and should be cached  
(i.e. I'd require that there's just one instance with worker threads)  
and I have no idea on where to configure that database resource so  
that my java service finds it.

I hope these questions are not too much newbie, I'd be really happy if  
you could give me some hints on how to configure my enviroment and if  
you might have a few pointers in which way to go.

Thanks a lot,

Stefan

Re: Java service with db connection pooling

[S....@stud.uni-heidelberg.de]

Hi Simon,


> Try adding @Scope("COMPOSITE") to your component implementation, for example,
>
> @Scope("COMPOSITE")
> class MyComponentImplementation implements MyServiceInterface {
>  ...
>
> }
>
> This means that only one instance of the component is created. From
> that instance you can do what you need at init/destroy time by marking
> operations with @Init and @Destroy. All other operations will be
> re-entrant so it's up to you to implement your data access strategy
> taking this into account.

Great! Exactly what I was looking for. I also found the reference for  
SCA annotations on osoa.org, however, that kind of detail is missing  
somewhat, is there any good document on that topic available somewhere?


> We have host extensions to fire up Jetty and Tomcat automatically. We
> don't have a host-geronimo. There are two ways you could run Tuscany
> in Geronimo. Firstly, and most straighforwardly, you could package
> Tuscany inside a WAR and then install that. Secondly we have a Tuscany
> plugin for Geronimo in development but it's not done yet. There is
> code in the Geronimo code base so you could play with it but I
> wouldn't suggest you start there.

OK, I'll keep that in mind in case I want geronimo.


>> What would be the correct way of to provide connection pooling? As I've read
>> somewhere, both Tomcat and Jetty provide a way to do this, then there's
>> apache commons DBCP, and the javax.sql DataSource. I guess, I would prefer
>> the javax.sql DataSource as it appears to me to provide the cleanest
>> approach, extensibility and allows to configure details about the database
>> driver outside of the code by JNDI, but I read that looking up a JNDI
>> context can be slow and should be cached (i.e. I'd require that there's just
>> one instance with worker threads) and I have no idea on where to configure
>> that database resource so that my java service finds it.
>
> I'm hoping someone who knows about connection pooling will pitch in here.

Well, if the connection survives long enough (or reconnects are  
automatically handled), I'd be happy with one connection for my class  
instance, that all the threads use to execute queries, so no need to  
recycle connections. Yet, I'm not sure if this would be safe and I'd  
still want to define my database driver and location outside of the  
java code.


> All good questions Stefan and if we can come to some conclusions we
> should add it to the web page/blog as it seems like a common enough
> scenario.

I'm happy to provide my insights (if I should have any).


Best regards

Stefan

Re: Java service with db connection pooling

[Simon Laws <si...@googlemail.com>]

Hi Stefan

Some initial comments in line....

On Wed, May 12, 2010 at 8:57 AM,  <S....@stud.uni-heidelberg.de> wrote:
> Hi!
>
> I'm relatively new to Tuscany and the JEE world, so I have a few questions
> on to correctly provide a service in a production environment.
>
> What I'm generally want to do is to provide the output of certain
> computational simulations stored in a database to a php-based web frontend,
> the output of my service is basically a (rather sophisticated) function of
> what the php frontend does to the database. I made a small dummy service in
> java along the sample shopping cart service included with the examples and
> connected it to php by jsonrpc. So far, it works great and provides exactly
> what I had in mind.
>
> However, I'm wondering if I could improve performance by using database
> connection pooling and what happens when the service is invoked. As it looks
> to me, when the service is invoked, a new instance of my ..Impl class is
> created, a database connection is created and afterwards destroyed together
> with the ..Impl instance. Is that correct?

That sounds correct if the scope of your service is stateless, which
it will be by default.

> What I'd prefer is just one
> instance for the whole time and one thread per user that can read all my
> private variables that are initialized when the service is started and that
> all the database connections are recycled. I'm not really sure if this is
> possible with the Tuscany distribution (currently using Tuscany 1.5.1 with
> Jetty, the current eclipse version doesn't install the Tuscany 1.6 plugin)

Try adding @Scope("COMPOSITE") to your component implementation, for example,

@Scope("COMPOSITE")
class MyComponentImplementation implements MyServiceInterface {
 ...

}

This means that only one instance of the component is created. From
that instance you can do what you need at init/destroy time by marking
operations with @Init and @Destroy. All other operations will be
re-entrant so it's up to you to implement your data access strategy
taking this into account.

> or whether I need a JEE application server to do this (i.e. Geronimo).

You could of course use a container to do connection pooling etc. but
I've not tried it and so not sure how to exploit the various
connection factories and the container context. I'd have to have a
play.

>
> If I do, how can I configure the eclipse Tuscany plugin, so that it runs the
> Geronimo version instead of the non-geronmio version that comes when the
> plugin is installed?

We have host extensions to fire up Jetty and Tomcat automatically. We
don't have a host-geronimo. There are two ways you could run Tuscany
in Geronimo. Firstly, and most straighforwardly, you could package
Tuscany inside a WAR and then install that. Secondly we have a Tuscany
plugin for Geronimo in development but it's not done yet. There is
code in the Geronimo code base so you could play with it but I
wouldn't suggest you start there.

>
> What would be the correct way of to provide connection pooling? As I've read
> somewhere, both Tomcat and Jetty provide a way to do this, then there's
> apache commons DBCP, and the javax.sql DataSource. I guess, I would prefer
> the javax.sql DataSource as it appears to me to provide the cleanest
> approach, extensibility and allows to configure details about the database
> driver outside of the code by JNDI, but I read that looking up a JNDI
> context can be slow and should be cached (i.e. I'd require that there's just
> one instance with worker threads) and I have no idea on where to configure
> that database resource so that my java service finds it.

I'm hoping someone who knows about connection pooling will pitch in here.

>
> I hope these questions are not too much newbie, I'd be really happy if you
> could give me some hints on how to configure my enviroment and if you might
> have a few pointers in which way to go.

All good questions Stefan and if we can come to some conclusions we
should add it to the web page/blog as it seems like a common enough
scenario.

>
> Thanks a lot,
>
> Stefan
>
>
>
>

Simon

-- 
Apache Tuscany committer: tuscany.apache.org
Co-author of a book about Tuscany and SCA: tuscanyinaction.com