Is it advisable to pass a ResultSet object from a method to a Servlet?

Question

Suppose I have a method with return type as ResultSet. Can I pass this object to a Servlet? I am facing that object to be null.

package sqlPackage;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class SqlExample {
    public  ResultSet playerCountry() throws SQLException {
        
        ResultSet rs2 = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            
            String url2="jdbc:mysql://localhost:3306/mxplayerdb";
            
            String userName="root";
            String password="root";
            
            Connection connection2=DriverManager.getConnection(url2,userName,password);
            
            String query2="Select * from myplayers";
            
            Statement statement2=connection2.createStatement();
            ResultSet rs2Clone=statement2.executeQuery(query2);
      
            while(rs2Clone.next()) {
                String name=rs2Clone.getString("userName");
                System.out.println(name);//**this displays the relevant values**
            }
            rs2=rs2Clone;//**I had to do this as I was not able to return rs2CLone inside the try block**
            //return rs2Clone;
            
        }
        catch(Exception e) {
            e.printStackTrace();
        }
        
        return rs2;// ***At this stage will this variable be null or referring to an object having data***
    }   
}

When I instantiate this class in a Servlet and invoke ths method, it receives a null ResultSet object. What am I doing wrong? Also can we not return from a try block only?

You have already consumed the result set within the method, so doing this is pointless. Not to mention that you are leaking connection and statement handles doing this. And if calling this method returns null, then it likely means an exception occurred which you caught and printed, and then returned `rs2` which was still set to `null`. Check your logs (and consider not swallowing exceptions like this, but instead just throw the exception (optionally wrapped in a business exception)). — Mark Rotteveel, Apr 17 '22 at 12:49
Related: [Returning a ResultSet](https://stackoverflow.com/questions/14853508/returning-a-resultset) — Mark Rotteveel, Apr 17 '22 at 12:55
You might be interested in [learning](https://docs.oracle.com/javase/tutorial/jdbc/basics/rowset.html) about [`RowSet`](https://docs.oracle.com/en/java/javase/18/docs/api/java.sql.rowset/javax/sql/rowset/package-summary.html) (an extension of `ResultSet`). — Basil Bourque, Apr 17 '22 at 23:20
@MarkRotteveel Thanks for pointing out. I am very new to these things like " leaking connection and statement handles". Could you please tell which line causes this? — krr, Apr 18 '22 at 04:12
You never close the statement and connection (and if you would do that, you would close the result set, making it useless to return anyway). — Mark Rotteveel, Apr 18 '22 at 11:16

Kai-Sheng Yang · Accepted Answer · 2022-04-18T12:11:41.913

1

You'd better not return a ResultSet from a method.

This is prone to resource leaking because you're forced to keep the statement and the connection open.

A better approach is using try-with-resources. The purpose of try-with-resources is to make sure the resources are closed reliably.

Change your method like below:

public List<String> readPlayerNameList() {
    List<String> nameList = new ArrayList<>();
    String userName = "root";
    String password = "root";
    String url = "jdbc:mysql://localhost:3306/mxplayerdb";
    try (Connection connection = DriverManager.getConnection(url, userName, password);
        Statement statement = connection.createStatement();) {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String query = "Select * from myplayers";
        try (ResultSet rs = statement.executeQuery(query)) {
            while (rs.next()) {
                String name = rs.getString("userName");
                System.out.println(name);
                nameList.add(name);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return nameList;
}

edited Apr 18 '22 at 12:11

answered Apr 17 '22 at 12:55

Kai-Sheng Yang

1,535
4
15
21

2

This code can be simplified considerably by using try-with-resources. Also, as it stands, your code is still leaking the connection and statement. – Mark Rotteveel Apr 17 '22 at 12:57
I have improved my answer. Thank you @MarkRotteveel – Kai-Sheng Yang Apr 17 '22 at 13:15
@Kai-ShengYang Thanks for your help. Could you please tell me with the return statement. Can we not return from a try block? – krr Apr 18 '22 at 11:27
1

@krr Yes, you can. But when you return from a try block, you also have to write another return statement in catch block, otherwise it will lead to compile fail. So I prefer to return at the end of method. – Kai-Sheng Yang Apr 18 '22 at 12:09
By the way, I just rename the method to `readPlayerNameList` which is more clear and readable. – Kai-Sheng Yang Apr 18 '22 at 12:12
One could argue that the exception shouldn't be swallowed like this, so returning from the try-block, with a business exception thrown from the catch block could be a very good alternative. – Mark Rotteveel Apr 19 '22 at 12:39

Is it advisable to pass a ResultSet object from a method to a Servlet?

1 Answers1