Hi,
I have a problem with a SelectCase generated from the Criteria API. In the generated SQL query, the condition's expression in the "when" is used instead of the result's expression. The result's expression is therefore ignored.
Tested with
MySQL 5.1.41
EclipseLink 2.3.0 and 2.3.2
Thanks
CREATE TABLE IF NOT EXISTS `tbltest` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
`isSomething` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=3 ;
INSERT INTO `tbltest` (`id`, `name`, `isSomething`) VALUES
(1, 'John Doe', 1),
(2, 'Jane Doe', 0);
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.Table;
@Entity
@Table(name = "tbltest")
public class Test implements Serializable
{
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "id", nullable = false)
private Integer id;
@Basic(optional = false)
@Column(name = "name", nullable = false, length = 25)
private String name;
@Basic(optional = false)
@Column(name = "isSomething", nullable = false)
private boolean isSomething;
public Test()
{
}
public Test(Integer id)
{
this.id = id;
}
public Test(Integer id, String name, boolean isSomething)
{
this.id = id;
this.name = name;
this.isSomething = isSomething;
}
public Integer getId()
{
return id;
}
public void setId(Integer id)
{
this.id = id;
}
public String getName()
{
return name;
}
public void setName(String name)
{
this.name = name;
}
public boolean getIsSomething()
{
return isSomething;
}
public void setIsSomething(boolean isSomething)
{
this.isSomething = isSomething;
}
}
EntityManager em = getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery c = cb.createQuery();
Root<Test> t = c.from(Test.class);
Expression testcase = cb.selectCase()
.when(cb.isTrue(t.get(Test_.isSomething)), cb.concat(t.get(Test_.name), " C"))
.otherwise(t.get(Test_.name));
c.multiselect(testcase);
TypedQuery q1 = em.createQuery(c);
List result = q1.getResultList();
Generated SQL Query :
SELECT CASE WHEN (isSomething = ?) THEN (isSomething = ?) ELSE name END FROM tbltest
bind => [true, true]