CODEWITHSUNDEEP
javasql-serverdatabasejdbcconnection
asok Buzz
asok Buzz

Reputation: 1894

Optimizing database performance for java server

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?

Upvotes: 2

Views: 186

Answers (1)

Guillaume
Guillaume

Reputation: 14656

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.

Upvotes: 2

Related Questions