JPA / JPQL Query error

Question

i am trying to retrieve a set of results from a database ( 13 marks ) there will only ever be 1 row called, i then want to average the 13 results in the query and then return the value

i have created the query :

public String markingAvg(Marking id) {
 System.out.println("id = " + id);
 Query m = em.createQuery("SELECT (m.markSectionOne + m.markSectionTwo + m.markSectionThree + m.markSectionFour + m.markSectionFive + m.markSectionSix + m.markSectionSeven + m.markSectionEight + m.markSectionNine + m.markSectionTen + m.markSectionEleven + m.markSectionTwelve + m.markSectionThirteen) FROM MARKING m WHERE m.id = :id", Double.class);
 m.setParameter("id", id);
 Object avg = m.getSingleResult();
 return null;
}

not sure if this is right so any advice would be welcome

i call it via a test button :

init

 String marking_id = FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap().get("edit_id");
 System.out.println(marking_id);
 if (marking_id != null) {
 this.markToEdit = this.markingFacade.find(Long.parseLong(marking_id));
 }
 marksByMarkingId = markingFacade.markingAvg(markToEdit);
}

the button calls :

public void markSearch() {
 searchAvgResults = markingFacade.markingAvg(markToEdit);
}

however on initalisation of ( the marksByMarkingId is done in init)

i get the error

java.lang.IllegalArgumentException: You have attempted to set a value of type class sws.entities.Marking for parameter id with expected type of class java.lang.Long from query string SELECT (m.markSectionOne + m.markSectionTwo + m.markSectionThree + m.markSectionFour + m.markSectionFive + m.markSectionSix + m.markSectionSeven + m.markSectionEight + m.markSectionNine + m.markSectionTen + m.markSectionEleven + m.markSectionTwelve + m.markSectionThirteen) FROM MARKING m WHERE m.id = :id.

any ideas guys or does anyone have a better way of doing this ?

Thanks Guys

Here is the marking entity

@Entity(name = "MARKING") public class Marking implements Serializable {

private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@ManyToOne
private Person marker;
@ManyToOne
private Project project;
// @ManyToOne
// private Project project;
//@ManyToMany(mappedBy = "title")
//private Project projectBeMarked;
;
private String markingCompleted, thirdMarker, plagorism, markSectionOne, markSectionTwo, markSectionThree, markSectionFour,
 markSectionFive, markSectionSix, markSectionSeven, markSectionEight,
 markSectionNine, markSectionTen, markSectionEleven, markSectionTwelve, markSectionThirteen, markAdjust, overalMark;

what i want to do is retrieve an id (which i do by passing it from the web address) and then want to average the markSectionOne - Thirteen

getter / setters for id

 /**
 *
 * @return
 */
 public Long getId() {
 return id;
 }
 /**
 *
 * @param id
 */
 public void setId(Long id) {
 this.id = id;
 }

Now fixed thanks to a great guy on here

however, when i press the test button to run the query i get the following error in the console

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: The '+' operator with a left operand type of 'VARCHAR' and a right operand type of 'VARCHAR' is not supported.
Error Code: -1
Call: SELECT (MARKSECTIONONE + (MARKSECTIONTWO + (MARKSECTIONTHREE + (MARKSECTIONFOUR + (MARKSECTIONFIVE + (MARKSECTIONSIX + (MARKSECTIONSEVEN + (MARKSECTIONEIGHT + (MARKSECTIONNINE + (MARKSECTIONTEN + (MARKSECTIONELEVEN + (MARKSECTIONTWELVE + MARKSECTIONTHIRTEEN)))))))))))) FROM MARKING WHERE (ID = 48)
Query: ReportQuery(referenceClass=Marking sql="SELECT (MARKSECTIONONE + (MARKSECTIONTWO + (MARKSECTIONTHREE + (MARKSECTIONFOUR + (MARKSECTIONFIVE + (MARKSECTIONSIX + (MARKSECTIONSEVEN + (MARKSECTIONEIGHT + (MARKSECTIONNINE + (MARKSECTIONTEN + (MARKSECTIONELEVEN + (MARKSECTIONTWELVE + MARKSECTIONTHIRTEEN)))))))))))) FROM MARKING WHERE (ID = ?)")

Currently getting the error

Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLSyntaxErrorException: TYPE 'UNSIGNED' does not exist.
Error Code: -1
Call: SELECT (CAST(MARKSECTIONONE AS UNSIGNED) + (CAST(MARKSECTIONTWO AS UNSIGNED) + (CAST(MARKSECTIONTHREE AS UNSIGNED) + (CAST(MARKSECTIONFOUR AS UNSIGNED) + (CAST(MARKSECTIONFIVE AS UNSIGNED) + (CAST(MARKSECTIONSIX AS UNSIGNED) + (CAST(MARKSECTIONSEVEN AS UNSIGNED) + (CAST(MARKSECTIONEIGHT AS UNSIGNED) + (CAST(MARKSECTIONNINE AS UNSIGNED) + (CAST(MARKSECTIONTEN AS UNSIGNED) + (CAST(MARKSECTIONELEVEN AS UNSIGNED) + (CAST(MARKSECTIONTWELVE AS UNSIGNED) + CAST(MARKSECTIONTHIRTEEN AS UNSIGNED))))))))))))) FROM MARKING WHERE (ID = 48)
Query: ReportQuery(referenceClass=Marking sql="SELECT (CAST(MARKSECTIONONE AS UNSIGNED) + (CAST(MARKSECTIONTWO AS UNSIGNED) + (CAST(MARKSECTIONTHREE AS UNSIGNED) + (CAST(MARKSECTIONFOUR AS UNSIGNED) + (CAST(MARKSECTIONFIVE AS UNSIGNED) + (CAST(MARKSECTIONSIX AS UNSIGNED) + (CAST(MARKSECTIONSEVEN AS UNSIGNED) + (CAST(MARKSECTIONEIGHT AS UNSIGNED) + (CAST(MARKSECTIONNINE AS UNSIGNED) + (CAST(MARKSECTIONTEN AS UNSIGNED) + (CAST(MARKSECTIONELEVEN AS UNSIGNED) + (CAST(MARKSECTIONTWELVE AS UNSIGNED) + CAST(MARKSECTIONTHIRTEEN AS UNSIGNED))))))))))))) FROM MARKING WHERE (ID = ?)")

on the query :

public String markingAvg(Marking id) {
 System.out.println("In MarkingAvg");
 System.out.println("id = " + id);
 System.out.println("id =" + id.getId());
 Query m = em.createQuery("SELECT (CAST(m.markSectionOne AS UNSIGNED) + CAST(m.markSectionTwo AS UNSIGNED) + CAST(m.markSectionThree AS UNSIGNED) + CAST(m.markSectionFour AS UNSIGNED) + CAST(m.markSectionFive AS UNSIGNED) + CAST(m.markSectionSix AS UNSIGNED) + CAST(m.markSectionSeven AS UNSIGNED) + CAST(m.markSectionEight AS UNSIGNED) + CAST(m.markSectionNine AS UNSIGNED) + CAST(m.markSectionTen AS UNSIGNED) + CAST(m.markSectionEleven AS UNSIGNED) + CAST(m.markSectionTwelve AS UNSIGNED) + CAST(m.markSectionThirteen AS UNSIGNED)) FROM MARKING m WHERE m.id = :id", Double.class);
 m.setParameter("id", id.getId()); // Note the getId()
 Object avg = m.getSingleResult();
 return null; // Do you really want to be returning null here?
}

• JonK: What fields does a Marking object contain that you can use? Your exception is telling you that your application was expecting you to pass in a Long object, but that you gave it an incompatible Marking object instead.
• user2061913: Ok thanks, ill post in the code in how i pass the value, but i have added the code above for the marking entity
• JonK: Do you have a getter for the id field? If not, you're going to need one.
• user2061913: Yep got one will post in above

Answer 1

Your application is giving you quite a useful error message there:

java.lang.IllegalArgumentException: You have attempted to set a value of type class sws.entities.Marking for parameter id with expected type of class java.lang.Long from query string

The important bit there is: expected type of class java.lang.Long.

The Long parameter that you need is contained within your Marking object's id attribute, so instead of setting the Marking object itself as the query parameter, set only its id attribute:

public String markingAvg(Marking id) {
 System.out.println("id = " + id);
 Query m = em.createQuery("SELECT (m.markSectionOne + m.markSectionTwo + m.markSectionThree + m.markSectionFour + m.markSectionFive + m.markSectionSix + m.markSectionSeven + m.markSectionEight + m.markSectionNine + m.markSectionTen + m.markSectionEleven + m.markSectionTwelve + m.markSectionThirteen) FROM MARKING m WHERE m.id = :id", Double.class);
 m.setParameter("id", id.getId()); // Note the getId()
 Object avg = m.getSingleResult();
 return null; // Do you really want to be returning null here?
}

To address your second issue; you're attempting to add the contents of VARCHAR columns together (or at least that's my assumption of your intent). If these columns need to be summed together, then you need to convert them to a datatype that supports addition. For each VARCHAR column, you'll need to do something like this:

CAST(COLUMN AS INTEGER)

Applied to your query, it would become:

"SELECT (CAST(m.markSectionOne AS INTEGER) + CAST(m.markSectionTwo AS INTEGER) + CAST(m.markSectionThree AS INTEGER) + CAST(m.markSectionFour AS INTEGER) + CAST(m.markSectionFive AS INTEGER) + CAST(m.markSectionSix AS INTEGER) + CAST(m.markSectionSeven AS INTEGER) + CAST(m.markSectionEight AS INTEGER) + CAST(m.markSectionNine AS INTEGER) + CAST(m.markSectionTen AS INTEGER) + CAST(m.markSectionEleven AS INTEGER) + CAST(m.markSectionTwelve AS INTEGER) + CAST(m.markSectionThirteen AS INTEGER)) FROM MARKING m WHERE m.id = :id"

As a final note on this, I personally don't like the idea of a SQL query that does something more than just bringing back raw data. The database doesn't need to know that you need to add the values of all of these columns up, it just needs to know that you need those values and to give them to you. If it were me, I'd do the addition in the Java layer, rather than getting the query to do it.

• user2061913: Thank you very much that has solved one issue, but i now have another !, any chance you could take a look at the error above and give me a hand ? thanks a lot for your time btw !
• JonK: @user2962401 I have updated my answer with a potential solution to your second problem.
• user2061913: thanks i have tired your solution however i am now getting the error type unsigned does not exsist, have added code abve
• JonK: @user2962401 Try NUMBER or INTEGER then - I'll update the answer to whichever one works.
• user2061913: Perfect, have used the integer and no errors :) now all i have to figure out is how to average these results and then pass that value instead of the null