patternjavaMinor

Efficient use of MySQL Connections with Java

Submitted by: @import:stackexchange-codereview·Mar 10, 2026·

Viewed 0 times

withefficientjavamysqluseconnections

Problem

Background: I'm building a dynamic in-house on-call roster that will be displayed on a web page. The end user will be displayed a list of people who are currently (at the time of the database call) on-call for various organizations, departments within the organization, and offices within the department.

The basic rules are that there will be 1-n organizations to be displayed. An organization can contain 0-n departments and departments can contain 0-n offices. Organizations and department may or may not have a person on call for the entire org/dept, but an office will always have a person on call (understanding the limitations of people who might forget to designate someone on call). Organizations, departments, and offices can be added, edited, reorganized, and deleted as needs change.

The Issue: In the past, I've worked under the "one-connection" rule: You open a connection, you use it for a specific task, then you close it..not having more than one connection open at any given time. However, this causes more processing by having to loop through retrieved objects to get their subordinate data. I'd like to be more efficient, and this is my first try at it by eliminating unnecessary looping.

```
package oncall.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import data.Department;
import data.Office;
import data.OnCall;
import data.Organization;

public class DatabaseUtil {

private enum OrgLevel { // for use in getOnCall database call

ORG("organization"),
DEPT("department"),
OFF("office");

private final String orgLevel;

private OrgLevel(final String s) {
orgLevel = s;
}

public String getOrgLevel() {
return orgLevel;
}
}

public DatabaseUtil() {

Solution

It's good practice to always use prepared statements, as it increases security as well as readability.

You don't need empty constructors.

SQL statements get easier to read when you write the SQL keywords in all upper-case.

Don't use one-character variable names, it makes the code really hard to read. The same also goes for two and three letter variable names. So write organizationName instead of s, statement instead of s, resultSet instead of rs, organization instead of o, connection instead of conn, department instead of d, and so on.

On the other hand, you do not have to add the variable type to its name. id is just fine, no need for idInt.

I'm not sure why you can't use the same connection. You could either open it up in the constructor and use it in all methods, or you could construct it somewhere outside this class, and then pass it to each method as a parameter.

Context

StackExchange Code Review Q#84808, answer score: 4

Revisions (0)

No revisions yet.