CODEWITHSUNDEEP
javamysql
locrizak
locrizak

Reputation: 12281

Proper way to return a ResultSet

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.

Upvotes: 7

Views: 19239

Answers (5)

havexz
havexz

Reputation: 9590

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

Upvotes: 0

parsifal
parsifal

Reputation: 116

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.

Upvotes: 1

Jignesh
Jignesh

Reputation: 471

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.

Upvotes: 2

John Smith
John Smith

Reputation: 2332

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.
}

Upvotes: 3

evg
evg

Reputation: 1338

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

Upvotes: 3

Related Questions