Monday, January 09, 2006

Using bind variables in generated SQLs

I noticed that the generated SQLs in Glassfish/Toplink implementation of EJB 3.0 persistence, use literal values instead of bind variables. Here are a few examples:

Here is the generated SQL when creating a new entity:

INSERT INTO TPCC.WAREHOUSE
(W_CITY, W_STREET_1, W_STREET_2, W_VERSION,
W_TAX, W_STATE, W_YTD, W_NAME, W_ZIP)
VALUES ('LONDON', NULL, NULL, 1, 0, NULL, 0,
'LONDON WAREHOUSE', NULL)
Now, look at the SQL generated for a query:

Code:

Warehouse w = (Warehouse) em.createQuery(
"SELECT w FROM Warehouse w WHERE w.name = :wname")
.setParameter("wname", "LONDON WAREHOUSE")
.getSingleResult();
Generated SQL:

SELECT W_ID, W_CITY, W_STREET_1, W_STREET_2, 
W_VERSION, W_TAX, W_STATE, W_YTD, W_NAME, W_ZIP
FROM TPCC.WAREHOUSE
WHERE (W_NAME = 'LONDON WAREHOUSE')
Finally, have a look at the UPDATE SQL:

UPDATE TPCC.WAREHOUSE 
SET W_STREET_1 = 'Braham Street', W_VERSION = 2
WHERE ((W_ID = 1) AND (W_VERSION = 1))
Clearly, the default implementation is always using literals in SQL statements. I haven't yet found a way to tell Glassfish/Toplink to use bind variables in SQLs.

Fortunately, since Glassfish and Toplink are both OpenSource, I can probably find out how to do this by looking at the source code.

No comments: