JSP DataBase Result Display Problem

Question

I have a JSP MySQL query

<sql:query var="libraries" dataSource="jdbc/ArabiTagOnline"><br>
     SELECT l.LibraryId, v1.LAvalue AS "dbESTid", v2.LAValue AS "LibName", v3.LAValue AS "Desc" 
     FROM ((Library l LEFT JOIN LibAttrValue v1 ON l.LibraryId = v1.LibraryId AND v1.AttributeId = 1) 
     LEFT JOIN LibAttrValue v2 ON l.LibraryId = v2.LibraryId AND (v2.AttributeId = 2 OR v2.AttributeId IS NULL))
     LEFT JOIN LibAttrValue v3 ON l.LibraryId = v3.LibraryId AND (v3.AttributeId = 6 OR v3.AttributeId IS NULL)
<\sql:query

This returns four columns. I tried to rename the columns in the results with AS but when iterating over the results

<c:forEach var="libraryResults" items="${libraries.rows}">
     <tr>
      <td>${libraryResults.Libraryid}</td>
      <td>${libraryResults.dbESTid}</td>
      <td>${libraryResults.LibName}</td>
      <td>${libraryResults.Desc}</td>
     </tr>
</c:forEach>

When loading the page, the columns dbESTid, LibName and Desc are blank.

I asked ${libraries.columnNames} and found out the AS statement in my query didn't rename the columns, they are all still LAValue. ${libraryResults.LAValue} only returns Desc. Any help on how I can populate this table?

Answer 1

You don't need double quotes around column aliases in your SQL - that may be confusing the jdbc driver. Also, why the break tag within the <sql-query>?

Column aliasing should work. However, if the problem persists one possible workaround is to iterate over columns within each row:

<c:forEach var="libraryResults" items="${libraries.rows}">
  <tr>
    <c:forEach var="column" items="${libraryResults}">
      <td><c:out value="${column.value}"/></td>
    </c:forEach>
  </tr>
</c:forEach>

That said, the real solution is, of course, to use an MVC framework so you don't have to embed your queries in JSP.

Answer 2

I've also run into this problem (happened when I iterated over a Result object returned from a bean and also when I don't embed my queries in the JSP as shown above).

The strange thing is this happens when I run the code on our Linux development box, but doesn't happen when I run it locally on my machine (both point at the same database, have the same version of the mysql driver, same version of Java, and same version of Tomcat).

Anyone have any ideas why the difference between the two platforms?