• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Recommendations connecting to the database

 
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
Ranch Hand
Posts: 45
Firefox Browser Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This should help JDBC Best Practices
 
Mikael Månsson
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic