Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Problem with select case from Criteria API
Problem with select case from Criteria API [message #879663] Thu, 31 May 2012 15:54 Go to next message
Patrice Lachance is currently offline Patrice Lachance
Messages: 6
Registered: June 2010
Junior Member
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]
Re: Problem with select case from Criteria API [message #879717 is a reply to message #879663] Thu, 31 May 2012 17:49 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1034
Registered: July 2009
Senior Member
Please file a bug and vote for it. A JPQL query can be used as a workaround.

Best Regards,
Chris
Re: Problem with select case from Criteria API [message #890516 is a reply to message #879663] Fri, 22 June 2012 13:30 Go to previous messageGo to next message
Everton Fujimoto is currently offline Everton Fujimoto
Messages: 6
Registered: June 2012
Junior Member
Any solution for this? I have the same problem...
Re: Problem with select case from Criteria API [message #890520 is a reply to message #890516] Fri, 22 June 2012 13:39 Go to previous messageGo to next message
Patrice Lachance is currently offline Patrice Lachance
Messages: 6
Registered: June 2010
Junior Member
Still no solution but you can use a native query, a JPQL query or manipulate the results manually afterward. Bug report #381250, vote for it.
Re: Problem with select case from Criteria API [message #890522 is a reply to message #890516] Fri, 22 June 2012 13:40 Go to previous messageGo to next message
Patrice Lachance is currently offline Patrice Lachance
Messages: 6
Registered: June 2010
Junior Member
Still no solution but you can use a native query, a JPQL query or manipulate the results manually afterward. Bug report #381250, vote for it.
Re: Problem with select case from Criteria API [message #890541 is a reply to message #890522] Fri, 22 June 2012 14:31 Go to previous messageGo to next message
Everton Fujimoto is currently offline Everton Fujimoto
Messages: 6
Registered: June 2012
Junior Member
how I vote in this bug?

I did not want to stop using the criteriabuilder... I'm considering changing JPA implementation, would have any recommendations?
Re: Problem with select case from Criteria API [message #890611 is a reply to message #890541] Fri, 22 June 2012 16:58 Go to previous message
Everton Fujimoto is currently offline Everton Fujimoto
Messages: 6
Registered: June 2012
Junior Member
The error:

In java class org.eclipse.persistence.internal.jpa.querydef.CriteriaBuilderImpl line 2287 has this:

        public Case<R> when(Expression<Boolean> condition, Expression<? extends R> result){
            org.eclipse.persistence.expressions.Expression conditionExp = ((InternalSelection)condition).getCurrentNode();
            conditionExp = org.eclipse.persistence.expressions.Expression.from(conditionExp, currentNode);
            ((FunctionExpression)currentNode).addChild(conditionExp);
            org.eclipse.persistence.expressions.Expression resultExp = ((InternalSelection)condition).getCurrentNode();
            resultExp = org.eclipse.persistence.expressions.Expression.from(resultExp, currentNode);
            ((FunctionExpression)currentNode).addChild(resultExp);
            return this;
        }

I think that problem is in line 2291:

org.eclipse.persistence.expressions.Expression resultExp = ((InternalSelection)condition).getCurrentNode();

And possible solution is:

public Case<R> when(Expression<Boolean> condition, Expression<? extends R> result){
            org.eclipse.persistence.expressions.Expression conditionExp = ((InternalSelection)condition).getCurrentNode();
            conditionExp = org.eclipse.persistence.expressions.Expression.from(conditionExp, currentNode);
            ((FunctionExpression)currentNode).addChild(conditionExp);
            org.eclipse.persistence.expressions.Expression resultExp = org.eclipse.persistence.expressions.Expression.from(result, currentNode);
            ((FunctionExpression)currentNode).addChild(resultExp);
            return this;
        }


I don't tested, I am finding the dependencies to compile eclipselink source.

[Updated on: Fri, 22 June 2012 17:00]

Report message to a moderator

Previous Topic:exclude-unlisted-classes=false in Equinox OSGI
Next Topic:Maven Test Fails with Custom Logger
Goto Forum:
  


Current Time: Thu Oct 23 05:21:44 GMT 2014

Powered by FUDForum. Page generated in 0.01752 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software