Why does <sql:query> not work as it should?

I want to use a valid SQL statement in my JSP like this:

<sql:query var="test" dataSource="DL">
select p.name name, j.description job_description, d.description department_description
from person as p, job as j, department as d
where ...
</sql:query>

Since I join 3 tables I have 2 duplicate names: "description", which I normally solve in SQL using aliases for those field names. In JSP this is not handled correctly, the resultset is not accessible via the aliases and only "description" is available, but not "job_description" or "department_description".

this works insufficient:

<c:forEach var="row" items="${test.rows}">
${row.description}
</c:forEach>

this does not works at all:

<c:forEach var="row" items="${test.rows}">
${row.job_description}
</c:forEach>

Is there any solution on this problem (which is IMHO a bug)?

This is my context.xml:

<Context path="/test" docBase="test" reloadable="true" crossContext="true">
  <Resource 
  name="DL" 
  auth="Container" 
  type="javax.sql.DataSource"
  maxActive="100" 
  maxIdle="30"
  maxWait="10000"
  username="xxx" 
  password="xxx" 
  driverClassName="com.mysql.jdbc.Driver"
  url="jdbc:mysql://localhost:3306/test"/>
</Context>

Solution

The problem is the dataSource attribute in <sql:query>. Assuming you've declared the datasource in the same page using

<!-- rest of attributes omitted here -->
<sql:setDataSource var="DL">

It should be

<sql:query var="test" dataSource="${DL}">
    your query...
</sql:query>

If your data source is configured using JNDI, then you should use the JNDI name:

<sql:query var="test" dataSource="jdbc/DL">
    your query...
</sql:query>

JSTL sql:query variable

Based on your <Resorce> definition, looks like JNDI can't find your datasource at all. Change the name from name="DL" to name="jdbc/DL" and make sure your context.xml file is inside META-INF folder of your web project. Having this, then your <sql:query> should be defined as stated in second example (using JNDI).

After doing a test case based on this, looks like a bug in <sql:query> when having the same column name several times. I solved using the solution in this question: How to access duplicate column names with JSTL sql:query?

So, assuming your data source is well configured, you just have to change the query to

<!-- I came up with the where part, it could be different in your case -->
<sql:query var="test" dataSource="jdbc/DL">
    select p.name name,
                concat(j.description, '') 'jdescription',
                concat(d.description, '') 'ddescription'
    from person p, job j, department d
    where p.jobID = j.id AND p.departmentID = d.id
</sql:query>

Descriptions:
<br />
<table>
<c:forEach var="row" items="${test.rows}">
    <tr>
        <td>${row.name}</td>
        <td>${row.jdescription}</td>
        <td>${row.ddescription}</td>
    </tr>
</c:forEach>

Yes, it is an ugly way to handle it, but it seems there's no other solution using JSTL. As recommendation, it would be better to get the data from a Servlet and set the results as part of the request attribute in order to display it.