z22 z22 - 19 days ago 6
Java Question

Dynamic JPA query not working

I am using join query through createQuery in JPA. I have 2 tables MasterScrip and OrderMaster. Entity code is given below. The dynamic query is returning collection object. I debugged and found that the query executes and returns collection object correctly; but, after the object returned an error, shown below:

[javax.xml.bind.JAXBException: class [Ljava.lang.Object; nor any of its super class is known to this context.]
javax.xml.ws.WebServiceException: javax.xml.bind.MarshalException...

SEVERE: Error Rendering View[/ClientTemplate/orderReport.xhtml]
javax.el.ELException: /ClientTemplate/orderReport.xhtml @14,142 value="#{stockOrderBean.scripLst}": com.sun.xml.ws.streaming.XMLStreamReaderException: unexpected XML tag. expected: {http://service/}getOrderScripByUserNameResponse but found: {http://schemas.xmlsoap.org/soap/envelope/}Envelope
at com.sun.faces.facelets.el.TagValueExpression.getValue(TagValueExpression.java:114)


Stateless bean method:

public Collection<MasterScrip> getOrderScripByUserName(String userName)
{
try
{
String squery = "select DISTINCT(s.scripSymbol),s.scripID from MasterScrip s,OrderStock o where o.scripID.scripID = s.scripID and o.userName.userName = '" + userName + "'";
Collection<MasterScrip> c = em.createQuery(squery).getResultList();
//UserMaster um = em.find(UserMaster.class,userName);
return c;
} catch(Exception e) {
System.out.println(e);
return null;
}
}


What is the cause of this error? How do I solve it?

Answer

First, as noted in the comments, you should always use parameters instead of concatenating parameter values:

select DISTINCT(s.scripSymbol), s.scripID from MasterScrip s, OrderStock o 
where o.scripID.scripID = s.scripID and o.userName.userName = :userName

This would prevent SQL injection attacks, or simply incorrect queries in case of a user name like O'Reilly for example.

Your query returns two different columns. There is no way for such a query to magically return instances of MasterScrip. It returns a List<Object[]>, where each Object[] contains two values: the scripSymbol and the scripID.

The query would return instances of MasterScrip if it was

select distinct s from MasterScrip s, OrderStock o 
where o.scripID.scripID = s.scripID and o.userName.userName = :userName