JAX-WS @WebMethod Return User-Defined Data Type

Question

I'm trying to create a jax-ws web service that would query a mysql database and return user-defined datatype from the @WebMethod. I first tried returning a sql.ResultSet which is provided by the sql import. However, JAXB couldn't bind it. So I tried returning a String[][], but when reading the result in the client it shows all null fields.

I then tried creating my own data-type and i understand that the class i create should have a default constructor and public sets and gets to all variables defined in that class. But i would like to return a resultset instead. The code to my web service is as follows:

@WebMethod
public String[][] getSummary(int month, int year){
    // register jdbc driver
    try
    {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
    }
    catch(Exception ex)
    {
        System.out.print(ex.getMessage());
    }

    // declare connection, statement and result set
    Connection connection = null;
    Statement statement = null;
    ResultSet resultset = null;

    String[][] result = new String[30][8];
    int counter = 0;

    try
    {
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/Livestock?user=root");
        statement = connection.createStatement();
        resultset = statement.executeQuery("SELECT Category, Particulars, Nos, OriginalCost, AccuDep, WDV, NRV, CapLoss FROM TB_SUMMARY WHERE Month=" + month + " AND Year=" + year);

        // loop over result set
        while(resultset.next())
        {
            result[counter][0] = resultset.getString("Category");
            result[counter][1] = resultset.getString("Particulars");
            result[counter][2] = resultset.getString("Nos");
            result[counter][3] = resultset.getString("OriginalCost");
            result[counter][4] = resultset.getString("AccuDep");
            result[counter][5] = resultset.getString("WDV");
            result[counter][6] = resultset.getString("NRV");
            result[counter][7] = resultset.getString("CapLoss");

            counter++;
        }           
    }
    catch(SQLException ex)
    {
        System.out.println("SQLException: " + ex.getMessage());
        System.out.println("SQLState: " + ex.getSQLState());
        System.out.println("VendorError: " + ex.getErrorCode());
    }
    finally
    {
        // it is a good idea to release
        // resources in a finally{} block
        // in reverse-order of their creation
        // if they are no-longer needed
        if(resultset != null)
            try 
            {
                resultset.close();
            } 
            catch (SQLException e) 
            {
                e.printStackTrace();
            }

        resultset = null;

        if(statement != null)
            try 
            {
                statement.close();
            }
            catch (SQLException e) 
            {
                e.printStackTrace();
            }

        statement = null;
    }

    return result;
}

I'm not sure if the reason why all cells in the String[][] are null are because the code doesn't even reach the executeQuery(); or maybe its the jaxb binding that doesn't work.

I still don't understand how jaxb works. If anybody out there has a good example of how to use jaxws with jaxb, I would really appreciate it. The documentations i found in the internet are overwhelming and complicated.

SimonSez SimonSez · Accepted Answer · 2012-03-19T09:10:57

In your scenario you won't be able to return a ResultSet because JAX-WS (JAX-B) is only able to bind simple datatypes. Have a look at this.

Your approach of returning an array is the most common to get over this problem and schould be sufficient in your case.

You should try to debug your application to make sure that your query gets fired and a result gets populated.

My best bet is that you get errors in one of the following lines:

Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/Livestock?user=root");

But your code looks fine to me so I'm only speculating. Make sure that your DB is online and try to debug your code.

Have Fun!

JAX-WS @WebMethod Return User-Defined Data Type

1 Answers