File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Recommendations connecting to the database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Recommendations connecting to the database" Watch "Recommendations connecting to the database" New topic

Recommendations connecting to the database

Mikael Månsson

Joined: Sep 20, 2009
Posts: 17

I am going to use java and mysql for my application, but I'm not really sure how to connect for the best performance.

A short background to what the application will do: capture ip packages and insert extracted data into a database with up to 100 packets per second peak time. there might be the need of some ip -> int, mac -> long conversions when inserting..if that is more effective than storing just strings. Basically one package would consist of timestamp,ip,mac1,mac2.

So..I have been thinking, since I would use a consumer / producer pattern, with one queue, and only one thread doing database inserts I was thinking that no connection pooling would help..I would just open a connection once and keep it open, since i am capturing and inserting live. Also..since I would be using some conversions maybe stored procedures would be the best...and then just JDBC, and no extra layer above it.

Does it sound reasonable using JDBC (and mysql myisam) using stored procedures for this kind of application? Would it be better to use batch updates instead of stored procedures and take 1000 at a time or just do on the fly with stored procedures?

Thankful for all advices!
Smitesh Shinde
Ranch Hand

Joined: Feb 16, 2008
Posts: 45

This should help JDBC Best Practices
Mikael Månsson

Joined: Sep 20, 2009
Posts: 17
Thanks, good stuff, so according to the best pracitices I do not really need store procedures, prepared statements are good enough for my application. However, on all examples of preparedstatements they are always prepared and closed just before and after the magic happens. Wouldn't it be better to prepare the statement once when the class is loaded, and then reuse this statement all over without closing it?

Also, it didn't help me answering whether I should keep my database connection open all the time or close it every time I have done something with the database. I ahve only one database client and it needs to enter information continuesly.
jQuery in Action, 3rd edition
subject: Recommendations connecting to the database
It's not a secret anymore!