H2 database ALIAS_COLUMN_NAME=TRUE does not appear to be working

Question

Adding ALIAS_COLUMN_NAME=TRUE to the JDBC url should mean that h2 allows 'aliases' in column names i.e. you could do:

resultSet.getString("p.first_name")

if p is an alias to some table. This does not appear to be working for me as illustrated by this code:

package com.example;

import junit.framework.TestCase;
import org.apache.commons.dbcp.BasicDataSource;
import org.h2.Driver;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;

/**
 * H2 Spring Test
 */
public class H2SelectTest extends TestCase {
    public void testQuery() throws Exception {
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName(Driver.class.getName());
        dataSource.setUrl("jdbc:h2:mem:test;ALIAS_COLUMN_NAME=TRUE");
        dataSource.setUsername("sa");
        dataSource.setPassword("");
        JdbcTemplate template = new JdbcTemplate(dataSource);
        template.afterPropertiesSet();
        template.execute("create table people(id int auto_increment, first_name varchar);");
        SimpleJdbcInsert insert = new SimpleJdbcInsert(template).withTableName("people");
        insert.setGeneratedKeyName("id");
        insert.compile();
        HashMap<String, Object> map = new HashMap<String, Object>();
        map.put("first_name", "Bob");
        insert.execute(map);
        template.query("select p.first_name from people p", new RowCallbackHandler() {
            public void processRow(ResultSet rs) throws SQLException {
                String name = rs.getString("p.first_name");
            }
        });
    }
}

Which results in this error:

Caused by: org.h2.jdbc.JdbcSQLException: Column "p.first_name" not found [42122-168]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.get(DbException.java:146)
    at org.h2.jdbc.JdbcResultSet.getColumnIndex(JdbcResultSet.java:2918)
    at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:2979)
    at org.h2.jdbc.JdbcResultSet.getString(JdbcResultSet.java:291)
    at org.apache.commons.dbcp.DelegatingResultSet.getString(DelegatingResultSet.java:225)
    at com.example.H2SelectTest$1.processRow(H2SelectTest.java:35)
    at org.springframework.jdbc.core.JdbcTemplate$RowCallbackHandlerResultSetExtractor.extractData(JdbcTemplate.java:1482)
    at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:446)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
    ... 19 more

Answer 1

Here's the answer you're looking for:

Turns out that ALIAS_COLUMN_NAME is for column names not for table aliases as per these docs:

 /**
  * System property <code>h2.aliasColumnName</code>.<br />
  * When enabled, aliased columns (as in SELECT ID AS I FROM TEST) return the
  * alias (I in this case) in ResultSetMetaData.getColumnName() and 'null' in
  * getTableName(). If disabled, the real column name (ID in this case) and
  * table name is returned. This setting only affects the default mode.
  **/

It also turns out that querying result sets using table aliases is only supported by MySQL and is not supported by the JDBC specification. You can disambiguate columns by using the full table name in H2 e.g. resultSet.getString("people.first_name") but I'm afraid, if you're using h2 to do internal tests against code that you normally run against MySQL, you'll have to find another way (e.g. don't alias and use the full table name).

If you need any other help, past me, just drop me a note.