Take the 2-minute tour ×

I am developing an application in java for processing data and uploading it to a MySQL database. With this application, I iterate through all of the files that need processed in a directory. With each row of the file, I store the data from that row into a variety of variables and do some transformations to get all of the data ready for uploading to my database. Then I plan to upload that row's data to the database with a PreparedStatement and INSERT query.

I am unfamiliar with the best practices for my situation. I'm in the process of teaching this to myself so I'm looking for a little guidance to make sure I do it correctly the first time.

Would it be inappropriate simply to open a connection at the beginning of the application's runtime (ie. open it once)? For each insert query I would create and close the PreparedStatement, but I would simply leave the connection open until I finished processing all of the files.

I've read about connection pools elsewhere but I can't figure out if it is applicable in my situation. I know its expensive to open and close connections frequently, but shouldn't I be able to just open it once and run all of my queries under that connection?

If you would recommend using a connection pool, what service would you recommend for my situation. Preferably it will be easy to get a handle on quickly. My project is somewhat time sensitive. Thanks.

share|improve this question
Take a look at c3p0. –  dasblinkenlight Jun 8 '12 at 3:37
How long is this application supposed to run? 1 minute? 15 minutes? 1 hour? How many users will use it concurrently? Only 1? Or multiple? MySQL by default reclaims the connection when it's been opened for 8 hours, so if your app runs more than 8 hours, it will break if you keep a single connection open that long. Connection pools are particularly useful for "lifetime long" running applications which are used by multiple concurrent users, such as Java EE web applications. –  BalusC Jun 8 '12 at 3:41
It hopefully should take around 15 minutes or less. It will depend mostly on how many files I need to process. Only one will use it concurrently. From what you've said it sounds like I might be alright with just opening the connection, processing the data, inserting it, then closing the connection. Is this correct? –  Mike Jun 8 '12 at 3:47

2 Answers 2

up vote 2 down vote accepted

If you are developing a desktop Java application, you won't be using connection pools. Connection pools are managed by Java Application Servers(Tomcat, JBoss, Glassfish) and will not be available in a desktop Java application.

Keeping a connection open is plausible if you are going to be doing just one update.

Also, a batch update would be a great idea for you:

String connectionURL = "jdbc:mysql://localhost:3306/database";
Connection con = null;
PreparedStatement stmt = null;

try {
    con = DriverManager.getConnection(connectionURL);
    stmt = con.prepareStatement(statement);

    for (Object o : list) {    // this is a list of your Java entity class
        stmt.setString(1, "foo");    // this is to update the parameters in the PreparedStatement
        stmt.setString(2, "bar");

         stmt.addBatch();     //  this adds the PreparedStatement to the batch of statements to execute


} catch (SQLException e) {
} catch (Exception e) {
} finally {
    if (stmt != null) {
    if (con != null) {

The idea of a batch update is to have one prepared statement, where you can simply change the parameters, and add the "new" statement to a batch of statements. Then, you can execute them all at one go when you invoke stmt.executeBatch()

I would advise you however, to execute statements probably 30 at a time if you have many such statements. Otherwise, you would be stranded if the program crashes/fails.

share|improve this answer
this would simply a command line program more than a fully interactive application. Every night it would be set to process the files and insert to the database. thanks for your suggestions –  Mike Jun 8 '12 at 3:56
You won't be able to use Connection pools then. The code I suggested above will do fine for you :) –  Lai Xin Chu Jun 8 '12 at 4:00
awesome. thank you very much. –  Mike Jun 8 '12 at 4:21
You can also use connection pools in standalone applications if you use a third party connection pool like for example BoneCP, DBCP or c3p0. –  Mark Rotteveel Jun 8 '12 at 13:29

This sounds like a batch proces, that you are implementing. In that case a dedicated connection to the database is the better solution.

share|improve this answer
As in it would be better to collect all of my data once and then upload it as a batch? What makes something a dedicated connection compared to the connection that I'm already using? –  Mike Jun 8 '12 at 3:41
What you are using currently is called a dedicated connection. –  steve Jun 8 '12 at 4:28

Your Answer


