false row-name in sql-query
haschibaschi Nov 4, 2005 4:48 AMI have following initial situation
- JBoss Version 4.0.2
- A Database with a table "article" column-name "artno"
- This is mapped to the attribute "number" in the EJB (2.1)
- the table "article" has 3 relations to the tables: articleattribute, pricing, availability.
- the relations are mapped inside the EJB.
- following query gets the article and the related data: SELECT OBJECT(o) FROM Article AS o WHERE o.number = ?1
- when I log the executed SQL I see 4 querys. 1 for the "article" and 3 for the relations.
Ok this is all well-known stuff and documented like this.
Now I would boost the performance and add following xml-snippet to the jbosscmp-jdbc.xml:
<query-method>
<method-name>findByCompleteArticleNumber</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<read-ahead>
on-find
<page-size>4</page-size>
<left-join cmr-field="articleAttributes"/>
<left-join cmr-field="pricings"/>
<left-join cmr-field="availabilities"/>
</read-ahead>
Here is the ejb-jar.xml-snippet:
[CDATA[Finds an article with the specified number]]
<query-method>
<method-name>findByCompleteArticleNumber</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>[CDATA[SELECT OBJECT(o) FROM Article AS o WHERE o.number = ?1]]</ejb-ql>
But now the SQL-Query fails, cause it searches not for "artno" in the table "article" but for "number", which is the name used in the EJB-QL-Query.
Here is the failed-SQL-Query:
Executing SQL: SELECT t0_o.seq, t0_o.number, t1_o_articleAttributes.seq, t1_o_articleAttributes.artno, t1_o_articleAttributes.name, t1_o_articleAttributes.value, t1_o_articleAttributes.country, t1_o_articleAttributes.language, t1_o_articleAttributes.fk_article, t2_o_pricings.seq, t2_o_pricings.artno, t2_o_pricings.qty, t2_o_pricings.type, t2_o_pricings.pricelist, t2_o_pricings.valuetype, t2_o_pricings.valuedecimal, t2_o_pricings.valuelong, t2_o_pricings.currency, t2_o_pricings.fk_article, t3_o_availabilities.seq, t3_o_availabilities.artno, t3_o_availabilities.qty, t3_o_availabilities.whid, t3_o_availabilities.date, t3_o_availabilities.type, t3_o_availabilities.fk_article FROM article t0_o LEFT OUTER JOIN articleattribute t1_o_articleAttributes ON t0_o.seq=t1_o_articleAttributes.fk_article LEFT OUTER JOIN pricing
t2_o_pricings ON t0_o.seq=t2_o_pricings.fk_article LEFT OUTER JOIN availability t3_o_availabilities ON t0_o.seq=t3_o_availabilities.fk_article WHERE (t0_o.number = ?)
Here is the Exception:
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Invalid column name 'number'.