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?

Answer 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;
}