I am currently adding h2 to improve integration testing around an existing application which previously relied on a local MySQL installation (MySQL is used in production and local environments).
All of the SELECT queries throughout the application use table aliases for both conciseness of queries and mapping of data to objects, especially as usually there is one or more join involved with these queries.
A typical SELECT query could be as follows.
SELECT c.id, c.name, c.content, cg.id, cg.name
FROM mydb.content c
LEFT JOIN mydb.`group` cg
ON c.group_id=cg.id
WHERE c.id = 1
LIMIT 1;
h2 will execute the SELECT query ok, and whilst debugging I can see the result returned in a JdbcResultSet object. I can see the column names, without the table alias, when I do a get of the metadata. Using the console the result is the name, no problem executing the query, returns the correct data, but again, no table alias when showing column names.
However when I call resultSet.getInt("c.id") (with resultSet being an instance of JdbcResultSet) an Exception is thrown:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [SELECT * FROM `mydb`.content c LEFT JOIN `mydb`.`group` cg ON c.group_id=cg.id WHERE c.id = ? LIMIT 1]; nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "c.id" not found [42122-200]
If I reference the table rather than the table prefix, there is no issue, BUT there are some self referencing joins in some queries where I don't think this is going to be an option (hierarchies).
I can't use column index due to columns being added and removed to various tables regularly, and personally I never find that as readable (I'm not even going to list it as an option of what I can do)
Ultimate question: Can table aliases be used in h2, or am I going to either:
- Replace all uses of getting data from a resultset by key with table aliases with references to the table name, and then refactor the self referencing queries.
- Not use h2.
- An alternative option.