JPA EclipseLink 'Characters' in IN clause

Question

How can I use a character in JPA's IN Clause in JPQL? For Example In oracle DB I have this Query:

select * from channel o where channel_mode IN ('O','R')

Channel mode is a char column in DB.

I get a error from JPQL that invalid IN argument[o], it is a character.

@Chris This is the JPQL:

typeMasterList = em.createQuery("select object(o) from ChannelRequest as o where     o.crqState IN ('O','R') and o.crqMode = 'B' ").getResultList();

And the Entity ChannelRequest is:

@Entity
@Table(name = "CHANNEL_REQUEST")
@XmlRootElement

public class ChannelRequest implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@Basic(optional = false)
@NotNull
@Column(name = "CRQ_ID")
private Long crqId;
@Size(max = 100)
@Column(name = "CRQ_QUEUE")
private String crqQueue;
@Size(max = 160)
@Column(name = "CRQ_DATA")
private String crqData;
@Column(name = "CRQ_STATE")
private Character crqState;
@Column(name = "ACTIVE_FLAG")
private Short activeFlag;
@Column(name = "ENABLED")
private Short enabled;
@Size(max = 100)
@Column(name = "CRQ_ADDRESS")
private String crqAddress;
@Column(name = "CRQ_MODE")
private Character crqMode;
@Column(name = "DCO")
@Temporal(TemporalType.TIMESTAMP)
private Date dco;
@Column(name = "DLUO")
@Temporal(TemporalType.TIMESTAMP)
private Date dluo;
@OneToMany(mappedBy = "rpmrqCrId")
private Collection<RpmRequest> rpmRequestCollection;
@JoinColumn(name = "MM_ID", referencedColumnName = "MM_ID")
@ManyToOne
private MediumMaster mmId;
@JoinColumn(name = "MC_ID", referencedColumnName = "MC_ID")
@ManyToOne
private MediumChannel mcId;`enter code here`

This is the error I get: invalid IN expression argument [O], expected argument of type [java.lang.Character]

Can you show the actual JPQL query you are using, and the code you are using to execute it, and possibly the entity you are querying over? — Chris, Sep 03 '13 at 13:29
Dear Chris, I have edited my question and included JPQL query and Entity to it. — Kuldeep S Chauhan, Sep 04 '13 at 06:34

score 1 · Accepted Answer · answered Sep 05 '13 at 16:38

The JPA specification states that a string literal is enclosed in single quotes (e.g. 'X' is a string of length 1 not the character X).

It looks like in your case, JPQL is expecting a list of characters, not strings. You can work around the issue by passing in the character list as a parameter instead:

typeMasterList = em.createQuery("... o.crqState IN :states ...")
                   .setParameter("states", Arrays.asList('O', 'R'))
                   .getResultList();

_{Side note: you could make your life a lot easier by using an enum instead of characters. You could then reference the enum values directly in the query without issue. At the database-level, the enums could still map to a single character.}

score 0 · Answer 2 · answered Sep 03 '13 at 14:20

0

Did you try using a string instead of a char, i.e. "O", "R".

answered Sep 03 '13 at 14:20

James

17,965
11
91
146

I did try string instead of character but got the same error: invalid IN expression argument [O], expected argument of type [java.lang.Character] – Kuldeep S Chauhan Sep 04 '13 at 06:40

JPA EclipseLink 'Characters' in IN clause

2 Answers2