Proper way to return a ResultSet

Question

I'm new to java but I'm picking it up pretty quickly. One thing that I keep running into is I end up having one function that is full of queries and just code in general and I would like to break it down into separate functions. Take this for example:

public ResultSet getApples (){
    ResultSet rs;
    try{
        PreparedStatement stmt = con.prepareStatement("SELECT * FROM fruit WHERE type='apples'");
        rs = stmt.executeQuery();
    } catch (SQLException e){
        e.printStackTrace();
    }
    return rs;  
}

Ideally this would be what I want to do, have all of try's and catches within one function, but this gives me the error: Local variable may not have been initilized

I do realize I could do this:


public function start(){
    try{
        ResultSet apples = getApples();
    catch (SQLException e){
        e.printStackTrace();
    }
}

public ResultSet getApples () throws SQLException { 
    PreparedStatement stmt = con.prepareStatement("SELECT * FROM fruit WHERE type='apples'");
    return stmt.executeQuery();
}

But I would really rather have the exceptions handled within the function as well as it return a result.

EDIT Alright so kinda a modififed answer to whats being provided. My whole goal on this question was to make the main functions of my script as clean as possible. Even the extra if ( _resultSet != null ) was something that I didn't really like. That being said I am pretty happy with this result:

public ResultSet getApples (){
    try{
        PreparedStatement stmt = con.prepareStatement("SELECT * FROM fruit WHERE type='apples'");
        return stmt.executeQuery();
    } catch (SQLException e){
        System.out.println("************************");
        System.out.println("Class.getApples null");
        System.out.println(e.getMessage());
        return null;
    }   
}

Everything is handled within the getApples function and when _resultSet.next() is called I get a NullPointerException and the prints in the getApples exception so I am able to find the error and debug quickly.

The problem in the first one is (as the compiler says) that the variable rs may not have been initialized (this would occur if an exception was thrown). Just set it to null when you declare it. — sshannin, Aug 10 '12 at 12:51
What should getApples() return in case there is an SQLException? — Frettman, Aug 10 '12 at 12:53
You should open and close a ResultSet within the same method. Return a List instead of a ResultSet. — GriffeyDog, Aug 10 '12 at 13:42
Remember to close your resources in a `finally` block or better the Java SE 7 try-with-resource (which is less likely to be used incorrectly). I suggest using the Execute Around idiom. http://stackoverflow.com/questions/341971/what-is-the-execute-around-idiom — Tom Hawtin - tackline, Aug 10 '12 at 13:43
@GriffeyDog Would that not create extra loops. One to create the `List` and then iterate through it again when I want to read all the values returned? — locrizak, Aug 10 '12 at 13:44
@TomHawtin-tackline Thanks a lot. This is the pattern I am trying to follow. Like I said I'm just getting used to Java. All that was a huge help :D — locrizak, Aug 10 '12 at 13:47

score 3 · Answer 1 · answered Aug 10 '12 at 12:51

3

Initialize rs to null first.

public ResultSet getApples (){
    ResultSet rs = null;
    try{
        PreparedStatement stmt = con.prepareStatement("SELECT * FROM fruit WHERE type='apples'");
        rs = stmt.executeQuery();
    } catch (SQLException e){
        e.printStackTrace();
    }
    return rs;  
}

answered Aug 10 '12 at 12:51

evg

1,308
1
7
12

4

Dou you really want to return null and print to System.out if there's an error? It's not very modular. – helios Aug 10 '12 at 12:53
Its a question about handling exception. I don't have enough info about his project and usage of this method. Author's original problem was the compiler error, and what to do with exception is his choice. – evg Aug 10 '12 at 13:00
You're right. I've deleted my answer because I was suggesting to do what he already told us he doesn't want to do. – helios Aug 10 '12 at 13:02
I forgot to put this option as well i just didn't know if it was the 'proper' way of doing things. Now is there a way to not return null an possibly an empty ResultSet? – locrizak Aug 10 '12 at 13:22

score 3 · Answer 2 · answered Aug 10 '12 at 12:52

3

You can declare your RS like this

ResultSet rs = null;

but where you call your function:

ResultSet apples = getApples ()

you have to check:

if(apples == null)
{
    //do something, because your query did not work.
}

answered Aug 10 '12 at 12:52

John Smith

2,282
1
14
22

score 2 · Answer 3 · answered Aug 10 '12 at 12:54

2

Because you are not setting ResultSet rs to anything initial value. and at the end you are returning it. What if any exception occurs and rs value does not have value set in it. In order to solve you need to assign null value to rs when you declare.

answered Aug 10 '12 at 12:54

Jignesh

471
6
17

score 1 · Answer 4 · answered Aug 10 '12 at 13:40

The biggest problem that I see with your first example (other than not initializing rs) is that you don't properly handle cleanup. You should have a finally block that closes stmt.

One very good way to make sure that all of this happens is to use Spring's JDBCTemplate (more documentation here). This handles all of the connection management details for you; you simply write your SQL and code to process the ResultSet. Better, it lets you use Spring's declarative transaction management.

score 0 · Answer 5 · edited May 23 '17 at 10:34

0

You can use CachedRowSet. For detailed answer you can look at my answer here

edited May 23 '17 at 10:34

Community

1
1

answered Jun 21 '14 at 18:24

havexz

9,550
2
33
29

Proper way to return a ResultSet

5 Answers5