I'm an amateur programmer and I'm having trouble with the java eclipse persistence named queries. I have an entity class. In this class I have created named queries. With the ClientInformation.getList I have two parameters. One on active (Boolean) and one on type (int). When I remove either one it works like a charm, but when I try them both it transforms the boolean value to an integer.
Entity class
package datamodel;
import java.io.Serializable;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Lob;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.xml.bind.annotation.XmlRootElement;
@Entity
@Table(name = "CLIENT_INFORMATION")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "ClientInformation.getList", query =
"SELECT c.ciName FROM ClientInformation c WHERE "
+ "(c.ciActive = true or c.ciActive = :ciActive) AND "
+ "(:ciType = 0 OR c.ciType = :ciType)"),
@NamedQuery(name = "ClientInformation.getID", query = "SELECT c.ciId FROM ClientInformation c WHERE c.ciName like :ciName"),
@NamedQuery(name = "ClientInformation.findAll", query = "SELECT c FROM ClientInformation c"),
@NamedQuery(name = "ClientInformation.findByCiId", query = "SELECT c FROM ClientInformation c WHERE c.ciId = :ciId"),
@NamedQuery(name = "ClientInformation.findByCiType", query = "SELECT c FROM ClientInformation c WHERE c.ciType = :ciType")})
public class ClientInformation implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "CI_ID")
private Integer ciId;
@Lob
@Column(name = "CI_NAME", unique=true)
private String ciName;
@Lob
@Column(name = "CI_ADDRESS")
private String ciAddress;
@Lob
@Column(name = "CI_AREACODE")
private String ciAreacode;
@Lob
@Column(name = "CI_CITY")
private String ciCity;
@Lob
@Column(name = "CI_PHONE")
private String ciPhone;
@Lob
@Column(name = "CI_PHONE2")
private String ciPhone2;
@Lob
@Column(name = "CI_EMAIL")
private String ciEmail;
@Column(name = "CI_ACTIVE")
private Boolean ciActive;
@Lob
@Column(name = "CI_NOTE")
private String ciNote;
@Column(name = "CI_TYPE")
private Integer ciType;
public ClientInformation() {
}
public ClientInformation(Integer ciId) {
this.ciId = ciId;
}
public Integer getCiId() {
return ciId;
}
public void setCiId(Integer ciId) {
this.ciId = ciId;
}
public String getCiName() {
return ciName;
}
public void setCiName(String ciName) {
this.ciName = ciName;
}
public String getCiAddress() {
return ciAddress;
}
public void setCiAddress(String ciAddress) {
this.ciAddress = ciAddress;
}
public String getCiAreacode() {
return ciAreacode;
}
public void setCiAreacode(String ciAreacode) {
this.ciAreacode = ciAreacode;
}
public String getCiCity() {
return ciCity;
}
public void setCiCity(String ciCity) {
this.ciCity = ciCity;
}
public String getCiPhone() {
return ciPhone;
}
public void setCiPhone(String ciPhone) {
this.ciPhone = ciPhone;
}
public String getCiPhone2() {
return ciPhone2;
}
public void setCiPhone2(String ciPhone2) {
this.ciPhone2 = ciPhone2;
}
public String getCiEmail() {
return ciEmail;
}
public void setCiEmail(String ciEmail) {
this.ciEmail = ciEmail;
}
public Boolean getCiActive() {
return ciActive;
}
public void setCiActive(Boolean ciActive) {
this.ciActive = ciActive;
}
public String getCiNote() {
return ciNote;
}
public void setCiNote(String ciNote) {
this.ciNote = ciNote;
}
public Integer getCiType() {
return ciType;
}
public void setCiType(Integer ciType) {
this.ciType = ciType;
}
@Override
public int hashCode() {
int hash = 0;
hash += (ciId != null ? ciId.hashCode() : 0);
return hash;
}
@Override
public boolean equals(Object object) {
// TODO: Warning - this method won't work in the case the id fields are not set
if (!(object instanceof ClientInformation)) {
return false;
}
ClientInformation other = (ClientInformation) object;
if ((this.ciId == null && other.ciId != null) || (this.ciId != null && !this.ciId.equals(other.ciId))) {
return false;
}
return true;
}
@Override
public String toString() {
return "manager.ClientInformation[ ciId=" + ciId + " ]";
}
}
Code snip where I call the named query.
EntityManagerFactory emf = Persistence.createEntityManagerFactory("ManagerPU");
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
List clientList = em.createNamedQuery("ClientInformation.getList")
.setParameter("ciActive", this.inactiveClientSelected)
.setParameter("ciType", this.typeClientSelected)
.getResultList();
clientList_ComboBox.addItem("Kies klant...");
if (clientList.isEmpty() == false) {
for (Object clientList1 : clientList) {
clientList_ComboBox.addItem(clientList1);
}
}
em.close();
emf.close();
This is the message I'm getting.
Call: SELECT CI_NAME FROM CLIENT_INFORMATION WHERE (((CI_ACTIVE = 1) OR (CI_ACTIVE = 1)) AND ((0 = 0) OR (CI_TYPE = 0))) Internal Exception: java.sql.SQLSyntaxErrorException: Comparisons between 'BOOLEAN' and 'INTEGER' are not supported. Types must be comparable. String types must also have matching collation. If collation does not match, a possible solution is to cast operands to force them to the default collation (e.g. SELECT tablename FROM sys.systables WHERE CAST(tablename AS VARCHAR(128)) = 'T1') Query: ReportQuery(name="ClientInformation.getList" referenceClass=ClientInformation sql="SELECT CI_NAME FROM CLIENT_INFORMATION WHERE (((CI_ACTIVE = ?) OR (CI_ACTIVE = ?)) AND ((? = ?) OR (CI_TYPE = ?)))") Error Code: 30000 Call: SELECT CI_NAME FROM CLIENT_INFORMATION WHERE (((CI_ACTIVE = 1) OR (CI_ACTIVE = 1)) AND ((0 = 0) OR (CI_TYPE = 0))) Query: ReportQuery(name="ClientInformation.getList" referenceClass=ClientInformation sql="SELECT CI_NAME FROM CLIENT_INFORMATION WHERE (((CI_ACTIVE = ?) OR (CI_ACTIVE = ?)) AND ((? = ?) OR (CI_TYPE = ?)))")
Any suggestions or do I need to give more information?
Edit: When I remove the last part from the query ("... AND (:ciType = ...etc) the query works fine, so I think I ruled out the actual parameter passing/ the code passes a boolean. Also, when I remove the first parameter (the Boolean) it works as well. So It's (the combination of) the two parameters.