The correct way to return a ResultSet

Question

I am new to java, but I quickly pick it up. One thing that I continue to encounter is that I have one function full of queries and all the 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 will be what I want to do, all attempts and catches within the same function, but it gives me an error: Local variable may not have been initilized

I understand that I can 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 prefer the exceptions to be thrown inside the function, and also return the result.

EDIT Well, as curious as possible. My goal in this matter was to make the main functions of my script as clean as possible. Even the extra if ( _resultSet != null ) was something I didn't like. However, I am very pleased 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 in the getApples function, and when _resultSet.next() is called, I get a NullPointerException and fingerprints in the getApples exception, so I can quickly find the error and debug it.

Answer 1

Initialize rs to zero.

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

Answer 2

You can declare your RS in this way

 ResultSet rs = null; 

but where you call your function:

 ResultSet apples = getApples () 

you need to check:

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

Answer 3

Because you are not setting ResultSet rs to any initial value. and in the end you return it. What to do if an exception occurs and the rs value does not have the value set in it. To solve, you need to assign null rs when declaring.

Answer 4

The biggest problem that I see in the first example (other than rs initialization) is that you are not processing cleanup properly. You should have a finally block that closes stmt .

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

Answer 5

You can use CachedRowSet . You can find a detailed answer here.