0

Hello, I created a web service to access oracle database. This is my code:

package mypack;

import java.util.List;
import javax.jws.WebService;
import javax.jws.WebMethod;
import javax.jws.WebParam;
import java.sql.*;
import java.util.ArrayList;

@WebService()
public class AutoRepairWS {


    @WebMethod
    public List getCustomerId(@WebParam(name = "Cust_Id") String Cust_Id){
        String user = "****";
        String password = "****";
        String url = "jdbc:oracle:thin:***:1521:xe";
        String OracleDriver = "oracle.jdbc.OracleDriver";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        List value = new ArrayList();

        try{
            
            conn = DriverManager.getConnection(url, user, password);
            pstmt = conn.prepareStatement("select Cust_fname from Customers where Cust_Id = '" + Cust_Id + " '");

            pstmt.setString(1, Cust_Id);

            rs = pstmt.executeQuery();

            while(rs.next()){
                value.add(rs.getString("Cust_fname"));
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
        return value;
    }
}

The problem is that it's supposed to return the value of Cust_fname, but i'm getting following message when I test web service:

java.util.List : "[]"

Can you help to make my program! Thanks in advance!

4
Contributors
5
Replies
22
Views
8 Years
Discussion Span
Last Post by ~s.o.s~
0

Provide the full stack trace please. That message doesn't help at all, and it doesn't even seem to be produced by this piece of code.

0

First of all learn the difference between PreparedStatements and Statements.

You initialize the prepareStatement with a fixed query (wrong) and then you call this: pstmt.setString(1, Cust_Id); ????
You don't have an argument at index 1 at the query. Where the (1, Cust_Id) will be applied????

And if we assume that you keep your query the way it is and you don't assign a parameter since you already have one: Cust_Id = '" + Cust_Id + " '" Did you notice the space at the last set of quotes: Cust_Id = '" + Cust_Id + [B]"[/B] '[B]"[/B] PS. The question marks at the first part of the post aren't there for fun. They are a hint.

0

Thank you guys for your reply, and I'm very beginner so I don't know much about this. I fixed my code and now I have following:

package mypack;

import java.util.List;
import javax.jws.WebService;
import javax.jws.WebMethod;
import javax.jws.WebParam;
import java.sql.*;
import java.util.ArrayList;

@WebService()
public class AutoRepairWS {


    @WebMethod
    public List getCustomerName(@WebParam(name = "Cust_Id") String Cust_Id){
        String user = "****";
        String password = "****";
        String url = "****:1521:xe";
        String OracleDriver = "oracle.jdbc.OracleDriver";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        List value = new ArrayList();

        try{

            conn = DriverManager.getConnection(url, user, password);
            stmt = conn.createStatement();
            String sql = "select Cust_fname from Customers where Cust_Id=" + Cust_Id;

            rs = stmt.executeQuery(sql);

            while(rs.next()){
                value.add(rs.getString("Cust_fname"));
            }

        }catch(SQLException e){
            e.printStackTrace();
        }
        return value;
    }
}
0

Oh and BTW, once you are done with understanding how web services work, try splitting your logic in layers like the business[model] layer, database[persistence] layer etc. Database access from a web method is err... bad. :)

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.