aspose file tools*
The moose likes JDBC and the fly likes How to handle repeated connections to the database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to handle repeated connections to the database" Watch "How to handle repeated connections to the database" New topic
Author

How to handle repeated connections to the database

Mandar Khire
Ranch Hand

Joined: Sep 11, 2007
Posts: 501

question 1:- If in program there are 2-3 parameters entries & result-sets for 2-3 different tables then following code should write repeatedly?


or only one time declaration will sufficient?


Millions saw the apple fall, but Newton asked why.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

I am sorry but i am not sure what you mean, the code you have shown us is setting of variables, and how often they run depends on how you set up your code.
Maybe if you explain what you are trying to acheive maybe we can help you more.
Mandar Khire
Ranch Hand

Joined: Sep 11, 2007
Posts: 501

Wendy Gibbons,
i asked this question beacuse when i try to solve this,
I have to repeatedly open connection & close it. each for CRUD ( Create, Read,Update,Delete).
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

this thread i think answers your question perfectly.
http://www.coderanch.com/t/580923/JDBC/java/Connecting-DB-when-often

Bill Johnston
Ranch Hand

Joined: Nov 17, 2005
Posts: 201
Wendy,

I agree that we need more information. But form the short description that was posted, do you really think that he needs a Connection pool? ... or am I reading too much into your reply with the posted link?

It kind of sounds like the OP just want to know if he should open and close a Connection before and after, respectively, each query on each table. The answer to that I should think, in a simple case, is no.


~Bill
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

I am just going on the previous thread it sounded very similar to me and connection pools was the answer there
Bill Johnston
Ranch Hand

Joined: Nov 17, 2005
Posts: 201
Wendy Gibbons wrote:I am just going on the previous thread it sounded very similar to me and connection pools was the answer there


Yes I just recently noticed that his previous topic was "moved"? I saw your last reply to the previous topic as well and it sounded like you were addressing me but I couldn't be sure. Whether you were or weren't I do see your point. I had stopped posting on that topic because I never came to an understanding of what was required.
Mandar Khire
Ranch Hand

Joined: Sep 11, 2007
Posts: 501

Thanks Bill Johnston & Wendy Gibbons,
connection pools was the answer there...

Might be yes, but that pool as function which can be call whenever needed or separate class which can be import in source code?

I had stopped posting on that topic because I never came to an understanding of what was required.

There is no specific big requirement , just one liner requirement i have as follows
Array of jpanels can be flawlessly drag-able where ever want within its container(it might be jscollpane/jtabbedpane/jpanel), whenever program restart, all jpanels should locate on its previously dragged location.

& i am only know basic of java,swing,jdbc,mysql...
now i know little bit serialization & hibernate...each time i try to solve my requirement,i moved towards higher technology...
When all finish...then might be i have all possibilty of writing code for fulfill this requirement(with serialization, with simple jdbc, with hibernate,with something else...
But i need experts help because i know sometime(or always) i go to wrong path with foolishness...
Bill Johnston
Ranch Hand

Joined: Nov 17, 2005
Posts: 201
I don't want to speak for Wendy, but unless or until she corrects me, it is my understanding that she's speaking of a web-based application - at least it sounds like it to me. Usually, with desktop applications, each end-user stores the preferences and properties on a hard-drive (either their own or a private network drive) ... at least that's all I've ever noticed.

Now I don't know where I specifically I got the notion that you were speaking of a desktop application, but I did.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Mandar Khire wrote:There is no specific big requirement , just one liner requirement i have as follows
Array of jpanels can be flawlessly drag-able where ever want within its container(it might be jscollpane/jtabbedpane/jpanel), whenever program restart, all jpanels should locate on its previously dragged location.

& i am only know basic of java,swing,jdbc,mysql...

Given this and the other questions you've posted (but I haven't read them all carefully), I'd advice this:

1) Keep things simple. Start with the easiest program you can get (ie. no database connection, perhaps even no storing the data initially, just making the dragging work).

2) Don't move on while you've unresolved problems in your current code. On large projects, it is sometimes unavoidable, but I believe this is not the case. Always finish one functionality before starting a next one. That way, if you run into problems, you'll know there is only one issue to handle, and it will also make it much easier for you to ask questions (and for others to answer them).

Specifically, do not turn to Hibernate if you have problems implementing your functionality with JDBC. I believe you should know SQL and JDBC before you move to ORM frameworks. I'm not sure whether this is a universal opinion, though.

3) Do not mix things that are not related. For example, do not put JDBC code into Swing forms. Actually, it would be very good if you abstracted from the actual storage for your data. In this way, you could implement data storage in a simple text file, make sure it works and only then implement a database storage, and even support multiple types of storage. This principle is described by the DAO design pattern.

Similarly to this, you might separate the logic which checks that the components do not overlap, to make that logic independent on the user interface of the application. That way you might more easily modify the logic or rewrite the UI of the application to use a different framework, for example.

There are many useful design patterns which can greatly hep you with this kind of design decisions, unfortunately I'm no particularly knowledgeable about design patterns.

4) Generally make your methods shorter. You've got some gigantic methods in your code. Try to make every method do just one well defined thing, and choose name for the method which will well describe its purpose. Add a comment that will clearly specify the purpose of the method. For example, the mouseReleased method should be divided into somewhere between five and ten (maybe even more) separate methods. You'll be even able to reuse some code that way, and you'll also be more able to verify (by testing, or simply by looking at the code long enough - in some cases) that the methods do not contain bugs.

5) Use good, descriptive names for your variables and limit their scope as much as possible. You've got way too many badly named variables declared at the instance level (for example x, j, k and others in the Addp class).

6) A more specific point: if you decide to store the data into the database, don't do it from inside the dragging mechanism. Not even if you correctly separate JDBC and Swing code. Database response can be generally too long and the responsiveness of your application to mouse events could suffer because of that. Either provide the user with a Save button, or save the data into the database at the end of the program (yes, if it crashes, the data will be lost).

(Another possibility would be to store the data into the database (or any other storage) in a background thread. I'm not going to elaborate on this, as I think you need to get everything other right before you even start thinking about this possibility.)

7) Start using a version control system, if you don't do so yet. If you don't have experience with these systems yet, it is a good opportunity to starting learning them. You'll be also able to mark working versions of your code and easily return to working versions if needed.

8) You might want to learn about unit testing and employ it in your project. There are lots of possibilities to effectively use unit testing in your type of project.

9) Don't be afraid to rewrite the code often. Sometimes you get a better idea of how to do things only after completely implementing them in a different way. Do it. And if the new idea turns out to be worse than the old one - well, you've just learned a useful lesson. Apart from that, this is what the version control system is for.

There would be probably more things to say, but I don't want to overwhelm you (and I'm getting a bit tired too ). And although these points don't address your questions directly, I believe this is the best help I can offer you.

Hope it really helps.
Mandar Khire
Ranch Hand

Joined: Sep 11, 2007
Posts: 501

Thanks Martin Vajsar,
You whole reply like "Ten Commandments for program"
1.
Don't move on while you've unresolved problems in your current code.

You are right for this,but question arises in mind about how much time should spend for resolving one problem? If i need to develop more functionality with various technology,then i try to bring my code to certain point where i stop before...like in car race all cars stands on particular position before signal get green...
here cars like 1. simple swing prgram 2. Swing with simple jdbc 3.swing with complex jdbc 4 swing with hibernate
Green signal like unresolve problem.

2.
Specifically, do not turn to Hibernate if you have problems implementing your functionality with JDBC. I believe you should know SQL and JDBC before you move to ORM frameworks.

Here also you right...but as per my knowledge about Comparative research/study is better way to study 2 or more things when key factors are space and time...as Wikipedia says about it
Comparative research/study, simply put, is the act of comparing two or more things with a view to discovering something about one or all of the things being compared.


3.
Do not mix things that are not related.

Here i think of it...i need time for understand what i should mix & what should not...
....separate the logic...

I am currently trying to do so...
...many useful design patterns...

Here again i get opportunity to learn new things...

4. about Point 4 & 5
Surely this is needed to my program...

5.
Either provide the user with a Save button, or save the data into the database at the end of the program (yes, if it crashes, the data will be lost).

It quite confusing to me...my requirement is as i finish mousereleased event location should store in db...because if there are 99 jpanels & i change locations of them & reach till 50(i know no one do this much but theoretically possible ) & suddenly system (not db) crash..then whole efforts get lost...but if i do each time db save then...(This thinking is completely mine...might be wrong...)
...background thread.

By reading this, i got curiosity about it...Its really helpful?

6.
version control system...& ...unit testing...

these are new things to learn & implement...

7.
Don't be afraid to rewrite the code often.

This is i am doing every day..its really helpful technique for learning process.

Thanks Bill Johnston also...
Now I don't know where I specifically I got the notion that you were speaking of a desktop application, but I did.

Currently i am trying for Desktop application...if succeed then shift to learn web application...
but i think this connection pools useful for web applications too.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

I actually intended to give a short, three or four points reply, but it sort of grew over my head...

Just a few followups on some points:

Comparative research is a kind of scientific study, a research tool. I don't think the Wikipedia article speaks of it as a learning tool. Anyway, I don't have any background on theory of learning and have no idea whether it is in total more effective to learn things sequentially or in parallel. However, when one thing is a built over another - like Hibernate is built over SQL and (less significantly) JDBC - I believe it makes sense to learn the underlying technology first. But, as I've already stated, perhaps others will have different opinion on this specific case, or they will not find SQL and JDBC that important when using ORM frameworks; after all, the frameworks were built to shield users from using SQL and JDBC.


Saving the data: This is a complicated issue. You got a one-liner requirement. However, fully honoring that requirement could require an unreasonable amount of work. The job of a software designer/developer is - at least in part - to identify requirements that would be too costly to implement, and offer a reasonable alternative for them. It is also possible that your tutors in your company expect you to discuss the requirements with them (I can only guess - this is completely up to you).

Anyway, "Flawlessly draggable" requirement probably precludes doing any action that might take more than a fraction of second on the Event Dispatch Thread. (I'd expect that as soon as I click on an object, I can start dragging it, any action taking longer than a few tenths of a second would be noticeable and arguable might make the perception of dragging "not flawless". Of course, as no actual specific criteria were given, this is completely open to debate.)

Swing allows you to do work in background threads, which certainly would be one way to fulfill the requirement. Whether this is expected from you is another point. Besides that, if your application had thousands of users, storing the data into the database after every change might put unreasonably high strain on database resources.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

I agree with Martin, the simplest way of writing this program now is to create and disconnect your connection whenever you need it.

worry about other considerations when you have the simplest version working.
Bill Johnston
Ranch Hand

Joined: Nov 17, 2005
Posts: 201
I'd be curious as to what the DB table/s that hold this kind of information might look like, as admittedly I've never thought of storing this kind of information in a DB table unless it was a serialized state of an Object, but not just a bunch of coordinates. Note this is not an argument, but a question.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Bill Johnston wrote:I'd be curious as to what the DB table/s that hold this kind of information might look like, as admittedly I've never thought of storing this kind of information in a DB table unless it was a serialized state of an Object, but not just a bunch of coordinates. Note this is not an argument, but a question.

for us currently it is only a simple vbox panel the user has any control over, but the sections can be dragged and dropped
user id,panel id, subpanel id, collapsed, sequence
Mandar Khire
Ranch Hand

Joined: Sep 11, 2007
Posts: 501

for us currently it is only a simple vbox panel....

Here i have small question in java is there any vbox(this is new for me...)?
I think i am talking about jpanel throughout questions...

user id,panel id, subpanel id, collapsed, sequence

I am always try to save only
panel id,old location x & y co-ordinate like oldx,oldy, same for new location as new x, newy.

Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Mandar Khire wrote:
Wendy Gibbons wrote: for us currently it is only a simple vbox panel....

Here i have small question in java is there any vbox(this is new for me...)?
I think i am talking about jpanel throughout questions...

by a vbox panel i meant a jpanel with a box layout orientation vertical.

Mandar Khire wrote:
Wendy Gibbons wrote: user id,panel id, subpanel id, collapsed, sequence

I am always try to save only
panel id,old location x & y co-ordinate like oldx,oldy, same for new location as new x, newy.



you only really want to be storing the end of sesion status in the db, intermediate states should be stored in memory for <ctrl-z> purposes.
Mandar Khire
Ranch Hand

Joined: Sep 11, 2007
Posts: 501

you only really want to be storing the end of sesion status in the db, intermediate states should be stored in memory for <ctrl-z> purposes.

End of session..no i am trying to save values as i run mouse released event...this event runs many times..each time Update & read...
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to handle repeated connections to the database