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?
Statement closes previous ResultSetYou 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
ResultSetobject is automatically closed when theStatementobject 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.
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}