How to return result set from method in jdbc conne

问题:

This is my connetion class. i need to return resultset to specific class. but i found resultset is closed in that class. i use connectio pooling in my connection. i want to create general connection class that manages all operations for database in my application.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class OpenTestConnection {
    private DataSource dataSource=null;
    private Connection connection=null;
    private Statement statement=null;

    public OpenTestConnection()
    {
        System.out.println("come in Openconnection....");
        try {
            // Get DataSource
            Context initContext  = new InitialContext();
            Context envContext  = (Context)initContext.lookup("java:/comp/env");
            dataSource = (DataSource)envContext.lookup("jdbc/ietddb");   
        } catch (NamingException e) {
            e.printStackTrace();
        }
    }
    private Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    public ResultSet selectfromtable(String sql)
    {
        System.out.println("come here....");
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try { if(null!=resultSet)resultSet.close();} catch (SQLException e) 
            {e.printStackTrace();}
            try { if(null!=statement)statement.close();} catch (SQLException e) 
            {e.printStackTrace();}
            try { if(null!=connection)connection.close();} catch (SQLException e) 
            {e.printStackTrace();}
        }
        return resultSet;
    }
}

回答1:

Well Shree has answered your issue. You have closed the ResultSet before returning it.

However, to add to what you are trying to achieve.

1) surround your return dataSource.getConnection in try catch.

2) Create seperate query function and connection close function like below

protected Connection connection = null;
protected Statement statement = null;
protected PreparedStatement prepared = null;

 // executeQuery
 protected ResultSet execute(String sql) throws SQLException {
    connection = getConnection();
    statement = connection.createStatement();
    return statement.executeQuery(sql);
}

//now have a close function
protected void commitAndClose() {
    if (connection != null) {
        try {
            connection.commit();
        } catch (SQLException ex) {
            // your code for handling ex
        } finally {
            try {
                resultSet.close();   //do not know why you are closing resultset
                statement.close();
                connection.close();
            } catch (SQLException ex) {
                LOGGER.log(Level.SEVERE, null, ex);
            }
            connection = null;
        }
    }

}

This will give you more flexibilty when your code expands.