i m a total newbie to JPQL ,so i m working on a Spring Boot app and i have this SQL query part :
select TOP 10 RFC_NUMBER, RECIPIENT_ID from [50004].SD_REQUEST S
INNER JOIN [50004].AM_EMPLOYEE E
--ON S.RECIPIENT_ID = E.EMPLOYEE_ID
WHERE E.AVAILABLE_FIELD_5 ='j.doe'
AND SD_REQUEST.STATUS_ID NOT IN (8,6,18,7,24)
AND SD_REQUEST.RFC_NUMBER like 'I%'
to JPQL.
i tried doing a @Query like this :
@Query("select x from Incident x Left join x.recipient recip where recip.login=:login and (x.rfcnumber like :I_% or :rfcnumber = null )"
+ " and x.status NOT IN (8,6,18,7,24)")
but it only returns ALL the rfcnumber of the that employee , i want it to extract only the rfc number starting with letter I , i tried doing CONCAT from searching around in then web, same thing. i m new to this so i figure it'll be something much simpler , i m thinking it's just syntax problem .
Thanks a bunch.
Edit (adding models):
import java.io.Serializable;
import java.sql.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
@Entity
@Table(name="SD_REQUEST")
public class Incident implements Serializable {
private static final long serialVersionUID = -8235081865121541091L;
@Id
@Column(name="REQUEST_ID")
private Integer inid;
@ManyToOne
@JoinColumn(name = "SUBMITTED_BY")
private Employee sender;
@Column(name="RFC_NUMBER")
private String rfcnumber;
@Column(name="CREATION_DATE_UT")
private Date date;
@Column(name="DESCRIPTION")
private String description;
@Column(name="COMMENT")
private String comment;
@Column(name="STATUS_ID")
private Integer status;
@ManyToOne
@JoinColumn(name = "RECIPIENT_ID")
private Employee recipient;
public Incident()
{
}
public Incident(int inid,String rfcnumber,Date date,String description,String comment,Integer status)
{
this.inid=inid;
this.rfcnumber= rfcnumber;
this.date=date;
this.description=description;
this.comment=comment;
this.status=status;
}
public int getInid() {
return inid;
}
public void setInid(int inid) {
this.inid = inid;
}
public String getRfcnumber() {
return rfcnumber;
}
public void setRfcnumber(String rfcnumber) {
this.rfcnumber = rfcnumber;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public String getComment() {
return comment;
}
public void setComment(String comment) {
this.comment = comment;
}
public Integer getStatus() {
return status;
}
public void setStatus(Integer status) {
this.status = status;
}
public Employee getSender() {
return sender;
}
public void setSender(Employee sender) {
this.sender = sender;
}
public Employee getRecipient() {
return recipient;
}
public void setRecipient(Employee recipient) {
this.recipient = recipient;
}
public void setInid(Integer inid) {
this.inid = inid;
}
}
And here's the model for Employee :
import javax.persistence.*;
import javax.persistence.Table;
import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.databind.annotation.JsonDeserialize;
@Entity
@JsonDeserialize(as =Employee.class)
@Table(name = "AM_EMPLOYEE")
public class Employee implements Serializable {
private static final long serialVersionUID = 5071617893593927440L;
@Id
@Column(name = "EMPLOYEE_ID" )
private Integer id;
@Column(name = "LAST_NAME")
private String lastName;
@Column(name = "AVAILABLE_FIELD_5")
private String login;
@OneToMany(mappedBy="sender")
@JsonIgnore
private List<Incident> myCreatedIncidents;
@OneToMany(mappedBy="recipient")
@JsonIgnore
private List<Incident> myOtherIncidents;
@Column(name = "PASSWD")
private String password;
public Employee() {
//super();
}
public Employee (String login,String password)
{
}
public Employee(Integer id, String lastName,String login, String password) {
this.id = id;
this.lastName = lastName;
this.login = login;
this.password = password;
}
/**
* @return the id
*/
public Integer getId() {
return id;
}
/**
* @param id
* the id to set
*/
public void setId(Integer id) {
this.id = id;
}
/**
* @return the lastName
*/
public String getLastName() {
return lastName;
}
/**
* @param lastName
* the lastName to set
*/
public void setLastName(String lastName) {
this.lastName = lastName;
}
/**
* @return the login
*/
public String getLogin() {
return login;
}
/**
* @param login
* the login to set
*/
public void setLogin(String login) {
this.login = login;
}
/**
* @return the password
*/
public String getPassword() {
return password;
}
/**
* @param password
* the password to set
*/
public void setPassword(String password) {
this.password = password;
}
public List<Incident> getMyCreatedIncidents() {
return myCreatedIncidents;
}
public void setMyCreatedIncidents(List<Incident> myCreatedIncidents) {
this.myCreatedIncidents = myCreatedIncidents;
}
public List<Incident> getMyOtherIncidents() {
return myOtherIncidents;
}
public void setMyOtherIncidents(List<Incident> myOtherIncidents) {
this.myOtherIncidents = myOtherIncidents;
}
}
