HSQL HSQLDB column alias ignored NullPointerException JSTL
Problem:
I can't pin point where this change happened from the HSQLDB changelogs but somewhere between HSQL 1.8.0.7 and 2.2.5, the handling of column aliases has changed. This is particularly problematic if attempting to lookup via alias name in JSTL where failed lookup of results (due to wrong column names) come back as null.
This is a standalone JSP example:
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<sql:setDataSource var="dataSource"
url="jdbc:hsqldb:mem:example/aliasprob"
driver="org.hsqldb.jdbcDriver"/>
<sql:update dataSource="${dataSource}">
create table if not exists USERS (
fullname varchar(255),
telephone varchar(255)
);
</sql:update>
<sql:update dataSource="${dataSource}">
insert into USERS (fullname, telephone)
values ('Joe Bloggs', '123-5555');
</sql:update>
<sql:update dataSource="${dataSource}">
insert into USERS (fullname, telephone)
values ('Fred Twitters', '456-5555');
</sql:update>
<sql:query var="users" dataSource="${dataSource}">
select fullname as "name"
, telephone as "phone"
from USERS
</sql:query>
<!DOCTYPE html>
<html>
<head>
<title>Alias Problems</title>
</head>
<body>
<p>These are the users:</p>
<table border="1">
<thead>
<tr>
<th>Name</th>
<th>Phone</th>
</tr>
</thead>
<tbody>
<c:forEach var="row" items="${users.rows}">
<tr>
<td>${row['name']}</td>
<td>${row['phone']}</td>
</tr>
</c:forEach>
</tbody>
</table>
</body>
</html>
<sql:update dataSource="${dataSource}">
drop table USERS;
</sql:update>
With the above test, you get two rows in the result table with empty cells for the values. If the code above was changed to ignore the aliases used in the 'AS' statements and use the underlying column names the data would be visible. If I have code that uses column aliases in this way does the select statement have to be changed to work with HSQLDB?
Solution:
HSQLDB introduced the
get_column_name property which "returns the underlying column name" (despite an alias being used) and is true by default. The documentation also states this is for "compatibility with other JDBC driver implementations". I suspect there's some inconsistency with with way the JSTL SQL tags are using ResultSet.getColumnLabel(int) vs ResultSet.getColumnName(int). When using JSTL, there's no distinction between column labels or names when using the
row['column'] syntax so using get_column_name=false in the HSQL connection URL is a quick fix - making the JSTL behaviour at least, consistent with other JDBC drivers. In the example code above, this would look like:
<sql:setDataSource var="dataSource"
url="jdbc:hsqldb:mem:example/aliasprob;get_column_name=false"
driver="org.hsqldb.jdbcDriver"/>
