How to return resultset from web service in java

Question

Hi All,

i am writing an application in which i am creating web services. i am creating an operation(method) which retrieves database table values from database table in resultset. Hence we can't return resultset value directly in web services. i am creating a class which holds the values from resultset. instead of resultset i am returning object[] of newly created class as follows :

 public HistoryInfoByUser[] get_HistoryInfoByUser(@WebParam(name = "email_Id")
  String email_Id) throws Exception{

        HistoryInfoByUser[] historyIn = null;
        if (conn != null) {
        CallableStatement cst = conn.prepareCall("{call sp_xxxx(?)}",ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
            cst.setString(1, email_Id);
            ResultSet resultSet = cst.executeQuery();
             int rowCount = resultSet.getRow();

           historyIn = new HistoryInfoByUser[rowCount];

          while (resultSet.next())
             {

                historyIn[rowCounter].setId(rowCounter);                               
           historyIn[rowCounter].setStartTime((java.util.Date)resultSet.getObject(1));
                historyIn[rowCounter].setType((String) resultSet.getObject(2));


             rowCounter++;
            }
        }
        return historyIn;
    }

but while trying to access those values in web service client, it is giving java.lang.NullPointerException.

here is the code that i am using in web service client for accessing resultset values :

    public void get_HistoryInfoByUser(String email_Id) 
{      
               service = new DBService();

               port = service.getDBPort();

    try {

        List<HistoryInfoByUser> historyIn = port.getHistoryInfoByUser(email_Id);
      Iterator iterator = historyIn.iterator();
            while (iterator.hasNext()){
              System.out.print(iterator.next()+" ");  
            }
      } catch (Exception_Exception ex) {
        Logger.getLogger(DataBaseWSHelper.class.getName()).log(Level.SEVERE, null, ex);
    }

 }

I tried returning a singe row value by returning an object (HistoryInfoByUser) of newly created class instead of object[] (HistoryInfoByUser[] ). It works fine with single object but giving NullPointerException when i am using object[]. i am not getting any way which will help me to overcome with this problem of accessing resultset value.

I thank in advance to your all valuable suggestions which will help me to overcome with this problem.

Answer 1

To me its looks that you defined your method to return an array. But in your client you code, as the method would return a List.

Answer 2

Return a WebRowSet from your web service:

import javax.sql.rowset.WebRowSet;
import com.sun.rowset.WebRowSetImpl; // Reference implementation
...
// get ResultSet rs from db
WebRowSet wrs = new WebRowSetImpl();
wrs.populate(rs);
// return wrs from web service

Note that your JDBC provider might provide a vendor-specific WebRowSet implementation. You can use that instead of the reference implementation.

Answer 3

You are missing a

historyIn[rowCounter] = new HistoryInfoByUser();

at the top of your while loop. When you create the array, you only get an array of null pointers. So before you can access an array item, you need to create it.

But gpeche's answer might be a better idea, if that is what you really want to do.

Answer 4

You should not be returning a ResultSet from a web service or any other Java object.

Load the data into an object or data structure and close the ResultSet in the same scope in which it was created.

At best, your design will have the persistence tier leaking out; at worst, you'll have unclosed cursors.