0

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.

Mohr
  • 1
  • 2
  • 1) Does the query still work, or is it now broken? 2) [MySQL uses `TINYINT(1)` internally to represent `BOOLEAN`](http://stackoverflow.com/a/289759/1079354), since `1 = 1` is still true. – Makoto Aug 27 '14 at 15:23
  • The query throws an error. While 1 = 1 is true, CI_Active = 1 will throw an error because CI_Active holds an boolean. What I do not understand is why it transforms it into an integer. If I remove the last part ("... AND(:ciType = ...etc) it works/ the code passes a Boolean. – Mohr Aug 28 '14 at 07:31
  • I will look at the TinyInt remark. – Mohr Aug 28 '14 at 08:23

0 Answers0