[Java] pooling

[TheAJ]

Hello

today i looked into MySQL database pooling and thought i'd start pooling my database, instead of it having to go though 200-300 queries a second.


Here is class:

/************************************************\
 * ############################################ *
 * ## RageScape 508 Server   ## *
 * ############################################ *
\************************************************/
package com.aj.ragescape.io;

import java.sql.*;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.aj.ragescape.Server;
import com.aj.ragescape.Engine;
import com.aj.ragescape.util.Misc;
import com.aj.ragescape.threads.databaseWorker;
import com.aj.ragescape.threads.socketWorker;
import com.aj.ragescape.threads.musWorker;

public class MySQL {

    public static int executeUpdate(String query) {
        int recordsUpdated = 0;
    Statement stmt = null;

        try {
            stmt = Server.DBConnection.createStatement();
            recordsUpdated = stmt.executeUpdate(query);

        stmt.close();
        stmt = null;

    } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
            e.printStackTrace();
                }
                stmt = null;
            }
        }
        return recordsUpdated;
    }

    public static ResultSet executeQuery(String query) {
        ResultSet RS = null;

        try {
            Statement stmt = Server.DBConnection.createStatement();
            RS = stmt.executeQuery(query);

        stmt.close();
        stmt = null;

    } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
            e.printStackTrace();
                }
                stmt = null;
            }
        }
        return RS;
    }
}

The problem, is that is gives me an error:

Apparently, the mysql exception catched, using printed / used. But i'm printing out the stacktrace of the error

Any ideas?
the stmt is inside the try statement, but can't think of an alt


Thank you

 

[Team Lion]

The first ones are not being caught, look at the error more closely, the statements you point out are in a try...finally block. They need to be in a try...catch block, or in your case a try...catch...finally block.

int recordsUpdated = 0;
Statement stmt = null;

try {
    stmt = Server.DBConnection.createStatement();
    recordsUpdated = stmt.executeUpdate(query);

    //stmt.close();  These are not needed, that's what the finally is for.
    //stmt = null; You're just wasting clock cycles otherwise, which if
    //                  pooling you're trying not to in the first place.
} catch(SQLException e) {
    System.out.print("SQL Error: \n\t");
    e.printStackTrace();
} finally {
    stmt.close(); // may need another try...catch etc but this is where they belong
    stmt = null;
}

Remember it can go try to finally or try to catch to finally.

 

[TheAJ]

This seems to be working:

    public static int executeUpdate(String query) {
        int recordsUpdated = 0;
        Statement stmt = null;

        try {
            stmt = Server.DBConnection.createStatement();
            recordsUpdated = stmt.executeUpdate(query);

        } catch (SQLException e) {
            System.out.print("SQL Error: \n\t");
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
        }
        return recordsUpdated;
    }

    public static ResultSet executeQuery(String query) {
        ResultSet RS = null;
        Statement stmt = null;

        try {
            stmt = Server.DBConnection.createStatement();
            RS = stmt.executeQuery(query);

        } catch (SQLException e) {
            System.out.print("SQL Error: \n\t");
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                stmt = null;
            }
        }
        return RS;
    }
}

Not sure if it's doing anything helpful though