Why does not work as it should?

Question

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>

score 2 · Accepted Answer · edited May 23 '17 at 10:28

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.

Thank you Luiggi. But I cannot see that it makes any difference. ${DL} is just to use a variable instead of a fixed string. And to place "jdbc" in front of DL does also not make a difference, because this is just a name given in contenxt.xml. So there is no change in the result as far as I can see. — Alex004, Mar 28 '13 at 10:49
@Alex004 it would help a lot if you post how you defined your datasource to begin with (since that could be the root of your problem). — Luiggi Mendoza, Mar 28 '13 at 12:54
@Alex004 please make a test, if it works, then keep it. As we say here in Peru: *no busques tres pies al gato*, which will be translated as *don't look three paws to a cat* and means *cats have four paws (normally), don't expect them to have just three (so don't try to give more problems)*. — Luiggi Mendoza, Mar 28 '13 at 13:20
I have just tested. It does not make any difference if I use "jdbc/DL" or just "DL". The ressource is found in both cases and the problem remais the same. — Alex004, Mar 28 '13 at 13:41
@Alex004 answer updated. I've made a test to reproduce the problem and solved. — Luiggi Mendoza, Mar 28 '13 at 14:40
Luiggi, thank you so far. The workaround is strange, but better than changing database entries. Best Regards to Peru! — Alex004, Mar 28 '13 at 15:08

score 0 · Answer 2 · answered Mar 27 '13 at 20:15

0

I'm not sure whether I remember correctly, but in SQL I believe you have to use the AS keyword to create a column alias.

I'm not sure I am right, but try this one:

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

If it doesn't work, then just create a view for your selection in the database and select the rows from that view or rename your duplicate columns.

answered Mar 27 '13 at 20:15

Lajos Arpad

64,414
37
100
175

Thank you. The "as" is optional, but anyway: both options do not work. Writing views is possible but I do not want to fill up my database with normally unneccessary things. So, is it really a bug? – Alex004 Mar 27 '13 at 21:07
I believe it is, but I am not sure. – Lajos Arpad Mar 27 '13 at 23:37

Why does not work as it should?

2 Answers2