How to execute multiple database queries with single connection in Java

Data and Databases
1

I need help with running several SQL queries using one database connection in Java. I’m getting errors when trying to fetch data from related tables.

I want to query a main table first, then use those results to query another table. But I keep getting “Before start of result set” exceptions.

Here’s my current approach:

String mainQuery = "SELECT * FROM restaurant";
String locationName = "";
String phoneNumber = "";
String website = "";
int restaurantId = 0;

String detailQuery = "SELECT * FROM restaurant_details";
float avgPrice = 0;
String cuisine = "";
String openTime = "";
String closeTime = "";
String specialDish = "";
int weekday = 0;

String htmlContent = "";

Calendar cal = Calendar.getInstance();

try {
    DatabaseHelper helper = new DatabaseHelper();
    ResultSet mainResults = helper.runQuery(mainQuery);
    weekday = cal.get(Calendar.DAY_OF_WEEK);
    
    htmlContent = "<html><body><table border='1'>";
    htmlContent += "<tr><th>Name</th><th>Phone</th><th>Website</th><th>Price</th><th>Cuisine</th></tr>";
    
    while(mainResults.next()) {
        restaurantId = mainResults.getInt("restaurant_id");
        locationName = mainResults.getString("location_name");
        phoneNumber = mainResults.getString("phone_number");
        website = mainResults.getString("website");
        
        detailQuery = detailQuery + " WHERE restaurant_id = " + restaurantId + " AND weekday = " + weekday;
        DatabaseHelper detailHelper = new DatabaseHelper();
        ResultSet detailResults = detailHelper.runQuery(detailQuery);
        
        avgPrice = detailResults.getFloat("avg_price");
        cuisine = detailResults.getString("cuisine");
        openTime = detailResults.getString("open_time");
        closeTime = detailResults.getString("close_time");
        specialDish = detailResults.getString("special_dish");
        
        htmlContent += "<tr><td>" + locationName + "</td><td>" + phoneNumber + "</td>";
        htmlContent += "<td>" + website + "</td><td>" + avgPrice + "</td><td>" + cuisine + "</td></tr>";
        
        detailQuery = "SELECT * FROM restaurant_details";
    }
} catch (Exception ex) {
    ex.printStackTrace();
}

My database connection class:

public class DatabaseHelper {
    private Connection dbConnection;
    private Statement statement;
    
    public DatabaseHelper() throws Exception {
        String username = "dbuser123";
        String password = "mypass456";
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        dbConnection = DriverManager.getConnection(
            "jdbc:mysql://localhost/myapp_database?user=" + username + "&password=" + password
        );
        statement = dbConnection.createStatement();
    }
    
    public ResultSet runQuery(String sqlQuery) throws SQLException {
        return statement.executeQuery(sqlQuery);
    }
    
    public void closeConnection() throws SQLException {
        statement.close();
        dbConnection.close();
    }
}

What am I doing wrong here? The error happens when I try to get data from the second ResultSet. Any suggestions would be really helpful!

2

Ah, I see the issue! Your detailQuery string gets modified in the loop but never resets properly. Even though you set it back at the end, the WHERE clause keeps getting appended multiple times. Move the detailQuery reset to the beginning of the while loop instead. And yeah, you definitely need that detailResults.next() call before accessing the columns.

3

that’s tricky! you might be hitting a connection limit - creating all those DatabaseHelper instances in the loop is risky. have you checked if detailResults.next() actually returns true before accessing the data? sometimes queries don’t return rows and that’ll cause the same error.

4

Your problem is simple - you’re trying to read from the ResultSet before calling next(). When detailHelper.runQuery(detailQuery) runs, the cursor sits before the first row. You need to call detailResults.next() before grabbing any column values, or you’ll get that “Before start of result set” error. Also, don’t create new DatabaseHelper instances inside your loop. That’s wasteful and you’ll run out of connections fast. Just reuse your existing connection and make a second Statement for the detail query. Way better for resource management and fixes your cursor issue.