Hi,
just run into an H2 weird problem and as I'm not sure whether this should
be reported or not I thought I would try to get some advice in the group
before notifying it in the issue tracker.
The problems looks like it's related to the H2 jdbc driver, or at least is
what I concluded after 1,5 days of research ;P , seems that parametrized
subqueries when using the WITH statement don't get the parameters replaced
correctly and instead of failing these just run and return 0 results when
it should be returning something. Below I tried to put up a snippet showing
the problem. The "sqlFail" is used with JdbcOperations parametrization to
replace the "param.0" , this doesn't work, don't know why, and no there's
complaint. On the other side the query executes fine if there's no need of
parametrizing, the "sqlGood" statement gets executed and returns the
expected results.
Hope the explanation is understandable, sorry had a hard time figuring this
out and wanted to share with anyone getting into the same problem.
@Autowired
NamedParameterJdbcOperations namedParameterJdbcOperations;
String sqlFail = "WITH T(field) AS ( "
+ "SELECT field FROM table WHERE field1 = *:param.0* "
+ "UNION ALL "
+ "SELECT field FROM T "
+ "INNER JOIN table2 ON T.field = table2.field "
+ ") SELECT * FROM T";
String sqlGood = "WITH T(field) AS ( "
+ "SELECT field FROM table WHERE field1 = *%d* "
+ "UNION ALL "
+ "SELECT field FROM T "
+ "INNER JOIN table2 ON T.field = table2.field "
+ ") SELECT * FROM T";
private static class SimpleIdRowMapper implements RowMapper<Long>
{
@Override
public Long mapRow(final ResultSet rs, final int rowNum) throws
SQLException
{
return rs.getLong(1);
}
}
private void test()
{
final List<Long> good =
namedParameterJdbcOperations.query(String.format(sqlGood, 3),
new SimpleIdRowMapper()); // Works fine
final Map<String, Object> params = new HashMap<>();
params.put("accountid.0", 3);
final List<Long> fail = namedParameterJdbcOperations.query(sqlFail,
params, new SimpleIdRowMapper()); // Fails silently
System.out.println("Fail : " + fail.size() + " , Good : " +
good.size());
}
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
