• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Setting up a Session Attribute for a Data Connection

 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to set up a data connection that I can use through out my application without having to reconnect every time I need a piece of data.

I have a connection class:



I would expect to get this message in console "System.out.println("DataConnection: Direct Connection: ");" When I call the new DataConnection() constructor, which I call one time from a servlet and assign the result to a session attribute.
But why do I get the message when I do a dConn.getAs400conn? Why would the constructor get called every time?
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The constructor won't get called every time.
So something else is going on.

I would debug this by sticking a breakpoint in the constructor at the start and then seeing where it is getting called.
 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:The constructor won't get called every time.
So something else is going on.

I would debug this by sticking a breakpoint in the constructor at the start and then seeing where it is getting called.


I think I just figured it out, what a dummy I am. The new DataConnection() constructor is part of my InitialControllerServlet which I make request using ajax. So for every request it insatiates another connection.
 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:The constructor won't get called every time.
So something else is going on.

I would debug this by sticking a breakpoint in the constructor at the start and then seeing where it is getting called.


In your opinion is a persistent connection better than one for every sql call? Also what is a good way to make sure my session attribute has not gone out of scope.

Should I check in my filter?
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65335
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
No! In fact, keeping a connection open for a microsecond longer than it needs to be is an extremely bad practice. Open the connection,  get your data, copy it  to model constructs, then close the connection as soon as possible.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65335
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
More: your connection should not be created in a servlet, stored in a session, or exist anywhere else in the control or View layers. It should only exist in the model.

Look into container managed connection pools; that's the best practice.
 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bear Bibeault wrote: No! In fact, keeping a connection open for a microsecond longer than it needs to be is an extremely bad practice. Open the connection,  get your data, copy it  to model constructs, then close the connection as soon as possible.


Are there any acceptations to this rule?
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Steve Dyke wrote:
Are there any acceptations to this rule?


Not for a webapp.
How many people use your web application?

How are you going to handle connection timeouts?
How about session timeouts?  What happens to the connection then?

And so on...
 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bear Bibeault wrote:More: your connection should not be created in a servlet, stored in a session, or exist anywhere else in the control or View layers. It should only exist in the model.

Look into container managed connection pools; that's the best practice.


Here is my DataConnection class:



Here is how I use the class:



My question is the finally block. How can I reference the connection without calling the get method again? Seems like a wasted call. But I don't know how else to close the connection.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 65335
97
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use container-managed connection pooling. Anything else is a hack by comparison.
 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Bear Bibeault wrote:Use container-managed connection pooling. Anything else is a hack by comparison.


I was under the impression that this was container managed connection pooling. Connection pooling is set up on app server and my app references the JNDI name of the connection pool.

I have changed the sql code to :



This works a lot faster but how can I or do I not need a finally block?
 
Paul Clapham
Sheriff
Posts: 21554
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Steve Dyke wrote:I was under the impression that this was container managed connection pooling. Connection pooling is set up on app server and my app references the JNDI name of the connection pool.


No. Lines 10, 11, and 64 are what you need to use container-managed connection pooling. Connect to the container, get a data source, get its Connection object. All the rest of your code should be replaced by data managed by the container. And yes, you can put all of those parameters into the connection pool's configuration.
 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:
Steve Dyke wrote:I was under the impression that this was container managed connection pooling. Connection pooling is set up on app server and my app references the JNDI name of the connection pool.


No. Lines 10, 11, and 64 are what you need to use container-managed connection pooling. Connect to the container, get a data source, get its Connection object. All the rest of your code should be replaced by data managed by the container. And yes, you can put all of those parameters into the connection pool's configuration.


I am not sure I am understanding.

We have connection pooling set up on our web app server. Do you mean I need to set up container-managed pooling in the application itself?

Is there an example of using the suggested method you are describing?
 
Paul Clapham
Sheriff
Posts: 21554
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This code:



What's that all about? Is there some possibility that somebody might sneak in and break your container's JNDI configuration? What you've got in the directConnect() method is totally unnecessary in my opinion and I would get rid of it. Just have that catch-block do something which will make sure that responsible people in the back room get notified and make sure that the user gets told that there's something wrong with the system.
 
Paul Clapham
Sheriff
Posts: 21554
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Proposed code to get database connection:

 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:Proposed code to get database connection:



I already have this shown in previous post(lines 7 - 17):

 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:This code:



What's that all about? Is there some possibility that somebody might sneak in and break your container's JNDI configuration? What you've got in the directConnect() method is totally unnecessary in my opinion and I would get rid of it. Just have that catch-block do something which will make sure that responsible people in the back room get notified and make sure that the user gets told that there's something wrong with the system.


When we first stated developing our web application we were not using connection pooling. Then when we stared using it on the production side we still did not have it set up in the development environment(running on local host). That is just code that was carried over from previous connection methods.
 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:Proposed code to get database connection:



A question about connection pooling.

I start the app and it needs remote data so it makes a connection to get the data.

I suppose at this point a connection is created in the connection pool.

After the data is gathered and I close the connection it appears that the created connection remains in the pool. And in my iSeries job log a job is present with the name of the data file the app accessed.

What constitutes a new connection in the pool? all connections in the pool are busy? any connection call? or any call to a different database file?
 
Paul Clapham
Sheriff
Posts: 21554
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You should be able to configure your connection pool with various options. You can configure a minimum number of connections, for example. Then when the pool starts up it will immediately create that number of connections, which are available for use by your web app. When your app "closes" a connection -- which you will of course make sure to do in a finally block or with try-with-resources -- the connection is returned to the pool and made available to other requesters. It isn't actually closed though. The point of all of this is to avoid creating new connections to your iSeries all the time. (I remember the bad old days when it took 45 seconds to open a database connection to our AS/400. That was a long time ago though, but it still takes enough time to be a potential issue.)

And yeah, if you said you wanted a minimum of three available connections in the pool and you have four requests which have all asked the pool for connections and haven't returned them yet, the pool is going to create a fourth connection for the last request to ask for one. You can also configure a maximum number of connections, so if you have a high volume of requests and they hang onto the connections for too long then some request is going to be told that there are no connections available. Then you've got an error showing up to the user and the guys in the back room. The back room guys at that point should then spring into action and find out whether connections aren't being closed, or whether the code could be fixed to close them sooner, or whether the maximum should be raised, or whatever.

There's other configuration options too. Supposed you asked for a minimum of three connections and at some point the pool finds itself with seven available connections. Should it close four of them? Should it do that immediately? There should be options to tell it how to behave in that case.

And your connection should be able to access any database file in the library or libraries which the connection is configured to use, without any special action in between. It's not unlikely that your queries are going to use joins anyway.
 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:Proposed code to get database connection:



Is this the same?

 
Paul Clapham
Sheriff
Posts: 21554
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, that's pretty close. Only I wouldn't catch the Exception and essentially ignore it, since then a NullPointerException is going to be thrown immediately afterwards. Just let your constructor throw the Exception and let your application's error-handling deal with it.
 
Steve Dyke
Ranch Hand
Posts: 1706
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Clapham wrote:Yes, that's pretty close. Only I wouldn't catch the Exception and essentially ignore it, since then a NullPointerException is going to be thrown immediately afterwards. Just let your constructor throw the Exception and let your application's error-handling deal with it.


Thanks for the quick response. I got you on the exception part.

I was just concerned that the "new DataSource().getConnection()" was wrong. I wanted to make sure I was utilizing connection pooling and not just creating a new set of connections with each instance of the class.

Still got a lot to learn.
 
Paul Clapham
Sheriff
Posts: 21554
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Steve Dyke wrote:I was just concerned that the "new DataSource().getConnection()" was wrong. I wanted to make sure I was utilizing connection pooling and not just creating a new set of connections with each instance of the class.


Yup. The connection pool is in your application server configuration. Then "new DataConnection()" gets a DataSource from the pool and "getConnection()" gets the Connection from that DataSource.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic