Hi:

I use criteria builder to build queries including filtering and sorting. All works fine, but using filtering on numeric columns, criteria builder create queries with LITERAL EXPRESSIONS. I have test on 4 databases: oracle xe, mysql 5, hsqldb (in memory) and derby 10.5 (in memory). For example, in oracle XE, using this code:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CreteriaQuery<TestEntity> criteriaQuery = criteriaBuilder.createQuery(TestEntity.class);
Root<TestEntity> root = criteriaQuery.from(TestEntity.class);
criteriaQuery.select(root);
Expression<Comparable<Number>> expression = root.get("testId");
Predicate predicate = criteriaBuilder.lessThanOrEqualTo(expression, 7);
criteriaQuery.where(new Predicate[] { predicate });

The entity:

@Entity
@Table(name = "TEST_ENTITY")
public class TestEntity implements Serializable
{
public TestEntity()
{
super();
}
public Integer getTestId()
{
return testId;
}
public void setTestId(Integer testId)
{
this.testId = testId;
}
....
....
....
....
@Id
@Basic(optional = false)
@Column(name = "TEST_ID")
private Integer testId;
@Column(name = "TEST_ENUM", nullable = false)
@Enumerated(EnumType.STRING)
private TestEnum testEnum;
@Column(name = "TEST_STRING", nullable = false, length = 50)
private String testString;
@Column(name = "TEST_DATE", nullable = false)
@Temporal(TemporalType.TIMESTAMP)
private Date testDate;
@Column(name = "TEST_NUMBER", nullable = false)
private Integer testNumber;
private static final long serialVersionUID = -7814836991173865095L;
}

on this table:

CREATE TABLE TEST_ENTITY
(
TEST_ID NUMBER(10) NOT NULL,
TEST_DATE TIMESTAMP NOT NULL,
TEST_ENUM VARCHAR2(255) NOT NULL,
TEST_NUMBER NUMBER(10) NOT NULL,
TEST_STRING VARCHAR2(50) NOT NULL,
PRIMARY KEY (TEST_ID)
);

The output:

DEBUG [] 20 mar 2011 20:34:53,494 org.hibernate.hql.ast.AST ( 258 ): --- SQL AST ---
\-[SELECT] QueryNode: 'SELECT' querySpaces (TEST_ENTITY)
+-[SELECT_CLAUSE] SelectClause: '{select clause}'
| +-[ALIAS_REF] IdentNode: 'annotatedt0_.TEST_ID as TEST1_1_' {alias=generatedAlias0, className=xxxxx.TestEntity, tableAlias=annotatedt0_}
| \-[SQL_TOKEN] SqlFragment: 'annotatedt0_.TEST_DATE as TEST2_1_, annotatedt0_.TEST_ENUM as TEST3_1_, annotatedt0_.TEST_NUMBER as TEST4_1_, annotatedt0_.TEST_STRING as TEST5_1_'
+-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[generatedAlias0], fromElementByTableAlias=[annotatedt0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
| \-[FROM_FRAGMENT] FromElement: 'TEST_ENTITY annotatedt0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=generatedAlias0,role=null,tableName=TEST_ENTITY,tableAlias=annotatedt0_,origin=null,columns={,className=xxxxx.TestEntity}}
\-[WHERE] SqlNode: 'where'
\-[LE] BinaryLogicOperatorNode: '<='
+-[DOT] DotNode: 'annotatedt0_.TEST_ID' {propertyName=testId,dereferenceType=ALL,propertyPath=testId,path=generatedAlias0.testId,tableAlias=annotatedt0_,className=xxxxx.TestEntity,classAlias=generatedAlias0}
| +-[ALIAS_REF] IdentNode: 'annotatedt0_.TEST_ID' {alias=generatedAlias0, className=xxxxx.TestEntity, tableAlias=annotatedt0_}
| \-[IDENT] IdentNode: 'testId' {originalText=testId}
\-[NUM_INT] LiteralNode: '7'

DEBUG [] 20 mar 2011 20:34:53,498 org.hibernate.hql.ast.ErrorCounter ( 91 ): throwQueryException() : no errors
DEBUG [] 20 mar 2011 20:34:53,498 org.hibernate.hql.ast.QueryTranslatorImpl ( 241 ): HQL: select generatedAlias0 from xxxxx.TestEntity as generatedAlias0 where generatedAlias0.testId<=7
DEBUG [] 20 mar 2011 20:34:53,499 org.hibernate.hql.ast.QueryTranslatorImpl ( 242 ): SQL: select annotatedt0_.TEST_ID as TEST1_1_, annotatedt0_.TEST_DATE as TEST2_1_, annotatedt0_.TEST_ENUM as TEST3_1_, annotatedt0_.TEST_NUMBER as TEST4_1_, annotatedt0_.TEST_STRING as TEST5_1_ from TEST_ENTITY annotatedt0_ where annotatedt0_.TEST_ID<=7
DEBUG [] 20 mar 2011 20:34:53,500 org.hibernate.hql.ast.ErrorCounter ( 91 ): throwQueryException() : no errors
DEBUG [] 20 mar 2011 20:34:53,501 org.hibernate.jdbc.AbstractBatcher ( 410 ): about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
DEBUG [] 20 mar 2011 20:34:53,502 org.hibernate.SQL ( 111 ):
select
*
from
( select
annotatedt0_.TEST_ID as TEST1_1_,
annotatedt0_.TEST_DATE as TEST2_1_,
annotatedt0_.TEST_ENUM as TEST3_1_,
annotatedt0_.TEST_NUMBER as TEST4_1_,
annotatedt0_.TEST_STRING as TEST5_1_
from
TEST_ENTITY annotatedt0_
where
annotatedt0_.TEST_ID<=7 )
where
rownum <= ?
Hibernate:
select
*
from
( select
annotatedt0_.TEST_ID as TEST1_1_,
annotatedt0_.TEST_DATE as TEST2_1_,
annotatedt0_.TEST_ENUM as TEST3_1_,
annotatedt0_.TEST_NUMBER as TEST4_1_,
annotatedt0_.TEST_STRING as TEST5_1_
from
TEST_ENTITY annotatedt0_
where
annotatedt0_.TEST_ID<=7 )
where
rownum <= ?

Any ideas?

Bye.