JDBC doesn't bring back all the results of a query in a ResultSet, because there may be too many of them to fetch them all eagerly. Instead it gives you something you can use to retrieve the results, but which goes away when the connection closes. So when you pass it back from your method after closing the database connection, nothing else can use it.
What you can do instead is to have this method use the resultSet to populate an object or a collection of objects, and pass that populated object back.
If you change your code to pass in a rowMapper (that takes a resultSet and passes back an object populated with the current row in the resultset), and use that to populate a container object that you pass back, then you'll have something as reusable as what what you've written, but which actually works because it doesn't depend on having the connection held open after the call is finished.
Here's your example code rewritten to use the rowmapper, get rid of some unnecessary exception-catching, and fix a bug that will prevent the connection from getting closed in some cases:
public static List<T> sqlquery (String query, RowMapper<T> rowMapper) throws SQLException
{
Connection connection=null;
Statement st=null;
ResultSet rs=null;
// don't need Class.forName anymore with type4 driver
connection = DriverManager.getConnection("databaseadress","username","password");
st = connection.createStatement();
rs = st.executeQuery(query);
List<T> list = new ArrayList<T>();
while (rs.next()) {
list.add(rowMapper.mapRow(rs));
}
// don't let exception thrown on close of
// statement or resultset prevent the
// connection from getting closed
if(rs != null)
try {rs.close()} catch (SQLException e){log.info(e);}
if(st!= null)
try {st.close()} catch (SQLException e){log.info(e);}
if(connection != null)
try {connection.close()} catch (SQLException e){log.info(e);}
return list;
}
If you don't catch each exception thrown on close individually, as shown above, you risk failing to close the connection if either the statement or the resultSet throw an exception on close.
This is similar to what spring-jdbc does, it defines a RowMapper as:
public interface RowMapper<T> {
T mapRow(ResultSet, int rowNum) throws SQLException;
}
The next step is going to be parameterizing your queries so you don't have to
surround parameter values in quotes or worry about sql injection. See this answer for an example of how spring-jdbc handles this. The long term answer here is, it would be better to adopt spring-jdbc or something similar to it than to reinvent it piecemeal.
