JDBC bind parameters only for one of them [message #1248499] |
Mon, 17 February 2014 10:27  |
Eclipse User |
|
|
|
I have built a query with Criteria API (for Oracle DB).
For performance reasons, I need set one parameter as "literal value", and the other parameters set as bind variable.
EclipseLink only let me to set all parameters as literal values or all parameters as bind variables.
A simple sample shows what is happening:
----------
ENTITY
----------
@Entity
@Table(name="TEST_ECLIPSELINK")
@NamedQuery(name="TestEclipselink.findAll", query="SELECT t FROM TestEclipselink t")
public class TestEclipselink implements Serializable {
private static final long serialVersionUID = 1L;
@Id
private long id;
private BigDecimal column1;
private BigDecimal column2;
public TestEclipselink() {
}
public long getId() {
return this.id;
}
public void setId(long id) {
this.id = id;
}
public BigDecimal getColumn1() {
return this.column1;
}
public void setColumn1(BigDecimal column1) {
this.column1 = column1;
}
public BigDecimal getColumn2() {
return this.column2;
}
public void setColumn2(BigDecimal column2) {
this.column2 = column2;
}
}
-------
EJB
-------
@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
@Override
public List<TestEclipselink> doTestEclipseLink(BigDecimal column1,BigDecimal column2) {
LogUtil.log(this.getClass(), LogUtil.DEBUG, " INIT");
List<Predicate> predicateList = new ArrayList<Predicate>();
CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery cq = builder.createQuery();
Root e = cq.from(TestEclipselink.class);
//AS PARAMETER TO BIND
predicateList.add(builder.equal(e.get("column1"), builder.parameter(String.class, "column1Parameter")));
//NOT BIND????
predicateList.add(builder.equal(e.get("column2"), column2));
Predicate[] predicates = new Predicate[predicateList.size()];
predicateList.toArray(predicates);
cq.where(predicates);
Query query = em.createQuery(cq);
//only one parameter to bind
query.setParameter("column1Parameter", column1);
//BIND / NOT BIND
query.setHint(QueryHints.BIND_PARAMETERS, HintValues.TRUE);
return query.getResultList();
}
---------------------------------------------------------------------------------------------
LOG TEST RESULT 1 ===> * WITH query.setHint(QueryHints.BIND_PARAMETERS, HintValues.TRUE);
---------------------------------------------------------------------------------------------
[EL Fine]: sql: Thread(Thread[Main Thread,5,main])--SELECT ID, COLUMN1, COLUMN2 FROM TEST_ECLIPSELINK WHERE ((COLUMN1 = ?) AND (COLUMN2 = ?))
bind => [1, 2]
---------------------------------------------------------------------------------------------
LOG TEST RESULT 1 ===> * WITH query.setHint(QueryHints.BIND_PARAMETERS, HintValues.FALSE);
---------------------------------------------------------------------------------------------
[EL Fine]: Thread(Thread[Main Thread,5,main])--SELECT ID, COLUMN1, COLUMN2 FROM TEST_ECLIPSELINK WHERE ((COLUMN1 = 1) AND (COLUMN2 = 2))
---------------------------------------------------------------------------------------------
EXPECTED TEST RESULT ===> WHY NOT? IS IT IMPOSIBLE?
---------------------------------------------------------------------------------------------
[EL Fine]: sql: Thread(Thread[Main Thread,5,main])--SELECT ID, COLUMN1, COLUMN2 FROM TEST_ECLIPSELINK WHERE ((COLUMN1 = ?) AND (COLUMN2 = 2))
bind => [1]
|
|
|
Re: JDBC bind parameters only for one of them [message #1862622 is a reply to message #1248499] |
Thu, 14 December 2023 22:03  |
Eclipse User |
|
|
|
Hi,
I know this is nearly 10 years old, but I have the same question. I have a query where a couple literal parameters could help me avoid some bad query plans, but the others need to remain bind variables.
Thanks for the reply if anyone has thoughts on this.
Mike
|
|
|
Powered by
FUDForum. Page generated in 0.26296 seconds