How to execute multiple queries and get them under three resultset in JDBC

Question

I want to create a program which will execute three different queries and their result would be mailed along with the user name in a tabular format. Below is the code I am designing,

public class SendNotification {
    public static void main(String[] args) throws SQLException, AddressException, MessagingException {
        // Database connection details
        String url = "jdbc:oracle:thin:@localhost:1521:XE";
        String user = "localuser";
        String password = "xxxxxx";
        //InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("config.properties");
        
        //User Details
        String user1 = "abUser";
        String user2 = "xyUser";
        String user3 = "cvUser";
        
        // SQL queries for each user
        String query1 = "SELECT COUNT(*) FROM dbtest WHERE AUTH = '" +user1+"' AND outdate >= SYSDATE - 7";
        String query2 = "SELECT COUNT(*) FROM dbtest WHERE AUTH = '" +user2+"' AND outdate >= SYSDATE - 7";
        String query3 = "SELECT COUNT(*) FROM dbtest WHERE AUTH = '" +user3+"' AND outdate >= SYSDATE - 7";
        
        // Create a connection to the database
        Connection connection = DriverManager.getConnection(url, user, password);
        
        // Create a statement and execute each query
        Statement statement = connection.createStatement();
        ResultSet resultSet1 = statement.executeQuery(query1);
        ResultSet resultSet2 = statement.executeQuery(query2);
        ResultSet resultSet3 = statement.executeQuery(query3);
        
        // Get the results and store them in a table
        Object[][] data = {
            {"User ID", "Document Count"},
            {user1, resultSet1.getInt(1)},
            {user2, resultSet2.getInt(1)},
            {user3, resultSet3.getInt(1)}
        };
        
     // Send an email with the results in tabular format
        String from = "devtest@ugc.local";
        String to = "PBxyz@gmail.com";
        
        String host = "mail.test.vb.xcv";
        
        Properties props = new Properties();
        props.put("mail.smtp.host", host);
        props.put("mail.smtp.port", "25");
        props.put("mail.debug", "true");
        
        Session session = Session.getDefaultInstance(props);
        MimeMessage message = new MimeMessage(session);
        message.setFrom(new InternetAddress(from));
        message.addRecipient(Message.RecipientType.TO, new InternetAddress(to));
        message.setSubject("Checked-In Document Counts");
        
        StringBuilder table = new StringBuilder();
        for (Object[] row : data) {
            table.append("<tr><td>").append(row[0]).append("</td><td>").append(row[1]).append("</td></tr>");
        }
        message.setText("<html><body><table>" + table.toString() + "</table></body></html>", "utf-8", "html");
        Transport.send(message);
        
        // Close the statement, result sets, and connection
        resultSet1.close();
        resultSet2.close();
        resultSet3.close();
        statement.close();
        connection.close();
    }
        


}

But, I am encountering with the below error everytime.

Exception in thread "main" java.sql.SQLException: Closed Resultset: getInt at oracle.jdbc.driver.GeneratedScrollableResultSet.getInt(GeneratedScrollableResultSet.java:237) at com.ura.SendNotification.main(SendTINNotification.java:48)

How shall i proceed to get the desired output?

@Reporter- In this line {user1, resultSet1.getInt(1)}. It seems the resultsets are getting closed. — Cris-123, May 12 '23 at 11:12
You need to call next on the resultset. see https://stackoverflow.com/q/59689090 — Nathan Hughes, May 12 '23 at 11:22
Note that this code has a major security leak (SQL injection), and has resource leaks (not using try-with). If it's anything more than a toy project you might want to look into that :) — rzwitserloot, May 12 '23 at 12:25

score 2 · Answer 1 · answered May 12 '23 at 11:26

you are accessing the resultset from the same statements. This will not work. You can create multiple instances of Statements statement1, 2 and 3. Then you access the resultset from each instance accordingly. At the end close all connections resultsets and statements.

Basil Bourque · Accepted Answer · 2023-05-15T06:25:40.647

Re-executing `Statement` closes previous `ResultSet`

You said:

java.sql.SQLException: Closed Resultset

A Statement can produce only one open ResultSet at a time. Executing that statement again automatically closes the previous ResultSet while producing the successive ResultSet.

To quote the Javadoc for ResultSet (emphasis mine):

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

Note the re-executed part in that quote.

Revised code

Here is a complete example app using the H2 Database Engine.

Rather than hard-coding for 3 inputs, soft-code using collections.

Notice how we separate the reporting from the database query. Generally best to maintain such a separation of concerns. This allows us to escape your problem where you trying to juggle all the result sets at the same time. In this code below we gather the results of each ResultSet with a put to our Map. We return the complete map to the calling code. The calling code then passes that map to a method dedicating to reporting (or to sending emails, as seen in your Question).

By the way, I suggest making a habit of using a DataSource to contain your database login credentials. This interface makes your deployment more flexible, with the option to externalize this info outside your codebase.

Notice how we use try-with-resources to automatically close each Connection, Statement, PreparedStatement, and ResultSet.

package work.basil.example.db;

import org.h2.jdbcx.JdbcDataSource;

import javax.sql.DataSource;
import java.sql.*;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ThreadLocalRandom;

public class ExH2 {
    public static void main ( String[] args ) {

        ExH2 app = new ExH2 ( );
        DataSource dataSource = app.obtainDataSource ( );

        app.prepareDatabase ( dataSource );
        List < String > users = List.of ( "Alice" , "Bob" , "Carol" , "Davis" );
        app.populateDatabase ( dataSource , users );
        // app.dumpToConsole ( dataSource );
        Map < String, Integer > countPerUserForCurrentWeek = app.countPerUserForCurrentWeek ( dataSource , users );
        app.report ( countPerUserForCurrentWeek );
    }

    private DataSource obtainDataSource ( ) {
        org.h2.jdbcx.JdbcDataSource ds = new JdbcDataSource ( );  // An implementation of `javax.sql.DataSource` bundled with H2.
        ds.setURL ( "jdbc:h2:mem:history_db;DB_CLOSE_DELAY=-1" );
        ds.setUser ( "scott" );
        ds.setPassword ( "tiger" );
        ds.setDescription ( "An example database showing multiple result sets." );
        return ds;
    }

    private void prepareDatabase ( final DataSource dataSource ) {
        String sql =
                """
                        CREATE TABLE IF NOT EXISTS history_
                        (
                            user_ TEXT NOT NULL ,
                            when_ TIMESTAMP WITH TIME ZONE NOT NULL ,
                            id_ UUID NOT NULL DEFAULT RANDOM_UUID() ,
                            CONSTRAINT history_pkey_ PRIMARY KEY ( id_ )
                        )
                        ;
                        """;
        try (
                Connection conn = dataSource.getConnection ( ) ;
                Statement stmt = conn.createStatement ( ) ;
        ) {
            System.out.println ( "INFO - Running `prepareDatabase` method." );
            stmt.executeUpdate ( sql );
        } catch ( SQLException e ) {
            e.printStackTrace ( );
        }
    }

    private void populateDatabase ( final DataSource dataSource , List < String > users ) {
        String sql = """
                INSERT INTO history_ ( user_ , when_ )
                VALUES ( ? , ?)
                ;
                """;
        try
                (
                        Connection conn = dataSource.getConnection ( ) ;
                        PreparedStatement preparedStatement = conn.prepareStatement ( sql ) ;
                ) {
            for ( String user : users ) {
                int limit = ThreadLocalRandom.current ( ).nextInt ( 7 , 42 );
                System.out.println ( "user = " + user + " | limit = " + limit );
                for ( int i = 0 ; i < limit ; i++ ) {
                    preparedStatement.setString ( 1 , user );
                    preparedStatement.setObject ( 2 , OffsetDateTime.now ( ZoneOffset.UTC ) );
                    preparedStatement.executeUpdate ( );
                }
            }

        } catch ( SQLException e ) {
            e.printStackTrace ( );
        }
    }

    private void dumpToConsole ( DataSource dataSource ) {
        String sql = """
                SELECT *
                FROM history_
                ;
                """;
        try (
                Connection conn = dataSource.getConnection ( ) ;
                PreparedStatement preparedStatement = conn.prepareStatement ( sql ) ;
        ) {
//            preparedStatement.setString ( 1, "Carol" );
            try (
                    ResultSet resultSet = preparedStatement.executeQuery ( )
            ) {
                while ( resultSet.next ( ) ) {  // We expect a single row in each result set, for the count.
                    System.out.println (
                            resultSet.getString ( 1 ) + " | " +
                                    resultSet.getString ( 2 ) + " | " +
                                    resultSet.getString ( 3 ) + " | "
                    );
                }
            }
        } catch ( SQLException e ) {
            throw new RuntimeException ( e );
        }
    }

    private Map < String, Integer > countPerUserForCurrentWeek ( final DataSource dataSource , List < String > users ) {
        Map < String, Integer > map = new HashMap <> ( );
        String sql = """
                SELECT COUNT(*) 
                FROM history_
                WHERE user_ = ?
                AND when_ >= ? 
                AND when_ < ?
                ;
                """;
        try
                (
                        Connection conn = dataSource.getConnection ( ) ;
                        PreparedStatement preparedStatement = conn.prepareStatement ( sql ) ;
                ) {
            OffsetDateTime now = OffsetDateTime.now ( ZoneOffset.UTC );
            OffsetDateTime weekAgo = now.minusDays ( 7 );
            preparedStatement.setObject ( 2 , weekAgo );
            preparedStatement.setObject ( 3 , now );
            for ( String user : users ) {
                preparedStatement.setString ( 1 , user );
                try ( ResultSet resultSet = preparedStatement.executeQuery ( ) ) {
                    if ( resultSet.next ( ) ) {  // We expect a single row in each result set, for the count.
                        int count = resultSet.getInt ( 1 );
                        map.put ( user , count );
                    }
                }
            }
        } catch ( SQLException e ) {
            e.printStackTrace ( );
        }
        return Map.copyOf ( map );
    }

    private void report ( Map < String, Integer > countPerUserForCurrentWeek ) {
        System.out.println ( "countPerUserForCurrentWeek = " + countPerUserForCurrentWeek );
    }
}

When run:

user = Alice | limit = 32
user = Bob | limit = 7
user = Carol | limit = 13
user = Davis | limit = 24
countPerUserForCurrentWeek = {Carol=13, Alice=32, Bob=7, Davis=24}

How to execute multiple queries and get them under three resultset in JDBC

2 Answers2

Re-executing Statement closes previous ResultSet

Revised code

Re-executing `Statement` closes previous `ResultSet`