0

Trying to use sql statement batchs to do the following: every 5 minutes, add a statement to batch (current counter) then every hour send, send the statements to the database.

I'm curious though, do I need to reinitialize the statement/connection whenever I add to it or send the batch?

here's how i think i would go about doing this, just need some clarification on how to do it smarter or if this is the best way

on program startup, initialize the following

    Connection connection = null;
    Statement statement = null;
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/youtube", "root", "root");
    statement = connection.createStatement();

then every 5 minutes.... addToBatch(connection, statement, counter, time, date);

then hour... statement.executeBatch();

am i missing anything? do i need to remake the connection?

any information is helpful, thank you!

krishnakumarp
  • 8,967
  • 3
  • 49
  • 55
Travs
  • 83
  • 1
  • 2
  • 9
  • You "could" maintain each request in some kind of `List` and simply build the batch every hour... – MadProgrammer Sep 24 '13 at 06:11
  • would that be necessary though? or would my theory work just fine? your idea is how i was going to go about doing it if my original idea didn't work out – Travs Sep 24 '13 at 06:14
  • It depends on the JDBC driver and Database server. They may clean up stale connections in order to reduce resource loads/cogs... – MadProgrammer Sep 24 '13 at 06:17
  • Holding system resources at the application level for an extended period is always a bad idea. I am curious as to why you only want to send data to the database on the hour, it will cause usage spikes. – BevynQ Sep 24 '13 at 06:28
  • seems like it would be smarter to only open the connection once an hour and just shoot queries instantly instead of having small spikes every few minutes – Travs Sep 24 '13 at 06:32
  • To answer your question, the connection has to be held open until you call executeBatch. The idea behind it is to save the time creating a statement you have just created, and it is intended to be used immediately not held and executed at some later date. Your idea would work better if statements could be created independently of a connection and could be attached later, however the way JDBC is architected at the moment that is not possible. – BevynQ Sep 24 '13 at 06:34
  • @Travis I tend towards the opposite view. `Batch processing` is required, when you have a `large system` with `limited resources`. Now most of the time resources are sufficient to do online processing. Which allows more flexible applications, stops the system being thrashed for short periods and idle for a long periods. – BevynQ Sep 24 '13 at 06:37

2 Answers2

0

It would be better to keep the query information in a simple list and then create batch and add statements when required.

  1. Your connection could get timed out at client side (connection pool settings) or server side. For e.g., in MySql wait_timeout parameter.
  2. Even if it is not closed, it is not a good idea to hold up db resources for such long periods unnecessarily.

Hope it helps.

krishnakumarp
  • 8,967
  • 3
  • 49
  • 55
  • so like a string List? public LinkedList gains = new LinkedList(); – Travs Sep 24 '13 at 06:28
  • Yes. You could use List gains = new ArrayList(); Also see http://stackoverflow.com/questions/393556/when-to-use-a-linked-list-over-an-array-array-list – krishnakumarp Sep 24 '13 at 06:36
0

You can add autoReconnect=true parameter to your connection string

mrcoder
  • 323
  • 4
  • 14