Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Recommendations connecting to the database

 
Mikael Månsson
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi!

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
Posts: 45
Firefox Browser Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This should help JDBC Best Practices
 
Mikael Månsson
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic