Optimizing database performance for java server

Question

I have a live java server. It has a DatabaseManager class for database connection, operation and closing. But there are a lot of database activities going on (even more than 50 clients simultaneously connected and voting). The executeQuery method returns a ResultSet, so not every opened connections are closed because closing will empty ResultSet before returning. Is there a better way to address this?

public class DatabaseManager {
    private Connection conn;
    public static Logger logger;
    public static String user = "root";
    public static String pass = "";
    public DatabaseManager() {
        logger = LoggerFactory.getLogger("Utilities.DatabaseManager");

        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            logger.error(e.getMessage());
        }
    }   
    public void startConnection() {
        try {
            conn = DriverManager.getConnection("jdbc:mysql:///youspinvotes",
                    user, pass);
            logger.debug("Database connected");
        } catch (SQLException e) {
            logger.error("Unable to connect to Database");
            logger.error(e.getMessage());
        }
    }
    public ResultSet exececuteQuery(final String query) {
        logger.debug("Executing query:" + query);
        ResultSet resultSet = null;
        try {
            final Statement stmt = conn.createStatement();
            resultSet = stmt.executeQuery(query);
            logger.debug("Query executed");
        } catch (Exception e) {
            logger.error("Unable to execute query."+e.getLocalizedMessage());
            resultSet = null;
        }
        return resultSet;
    }

    public int executeUpdate(final String query) {
        logger.debug("Executing update query:" + query);
        int rowsAffected = -1;
        try {
            final Statement stmt = conn.createStatement();
            rowsAffected = stmt.executeUpdate(query);
            stmt.close();
            logger.debug("Update performed with "+rowsAffected+" rows changed.");
        } catch (Exception e) {
            logger.error(query + ": Unable to execute query. \n"+ e.getMessage());
        }
        return rowsAffected;
    }
    public void endConnection() {
        logger.debug("Closing database connection");

        try {
            conn.close();
        } catch (Exception e) {
            logger.error("There was a problem closing the database connection.");
        }
    }
}

Since there are lot of creating and closing connections, resource and time is consumed. Is there any way I can enhance the performance? Is connection pooling good choice for this server? What is the most efficient way to implement this kind of servers?

Arjan Tijms · Accepted Answer · 2013-05-15 21:51:55Z

2

Pooling connexions is definitely a good idea if you're trying to improve performance. Opening connections can be costly so it's better to keep them alive.

I'm not sure what you mean by "live java server", if you're in a Java EE server there should already by a connection pool implemented in it and you could use it.

There are many for plain JDBC pools, for instance DBCP and C3P0 are good options.

Not really related but I see that you only pass a String to your query methods, using prepared statements with query parameters might also improve performance (depending on the driver implementation and the underlying database), plus it's safer against SQL injection attacks.

edited May 15, 2013 at 21:51

Arjan Tijms

38.2k12 gold badges112 silver badges144 bronze badges

answered May 15, 2013 at 5:29

Guillaume

14.7k3 gold badges46 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

asok Buzz Over a year ago

Its not J2EE server, I'm using org.restlet.jar for RESTful server. By live I mean server runs 24 hours, so I'll not be able to close connection pool. Is pooling safe for multi-threaded environment, where multiple clients will acquire connection simultaneously? Thanks :)

Collectives™ on Stack Overflow

Optimizing database performance for java server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related