Stepan Stepan - 22 days ago 6
Java Question

SQL response is treated as column name, rather than content in Java

Method below gets result set from SQL database and populates a

Map<String,String>
. It works well in most cases, but with this particular query I get an exception saying:


Unable to execute query:
select RULEID, GRAPHRULE from GRAPHRULE3D WHERE GRAPHRULE LIKE '%1717-002%' AND GRAPHRULE NOT LIKE '%DRAWING%'

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The column name ('1717-002'); is not valid.


Query itself works on SQL Server. Why does it think that
('1717-002');
in second column is a column name, while it is actually a value?




Problem follows
String value = rs.getString(rs.getString(2).trim());
if I drop it I get a set of
String key =Integer.toString((Integer) rs.getObject(1));
without a problem.

//String query = "select RULEID, GRAPHRULE from GRAPHRULE3D WHERE GRAPHRULE LIKE '%1717-002%' AND GRAPHRULE NOT LIKE '%DRAWING%'"
public Map<String,String> getStrinfStringPairsFromIntegerStringSQL(String query) {

return processContent(query, (rs) -> {
Map<String,String> optionDescriptions = new TreeMap<>();

while (rs.next()) {

String key =Integer.toString((Integer) rs.getObject(1));
String value = rs.getString(rs.getString(2).trim());

optionDescriptions.put(key, value);
}
return optionDescriptions;
});
}


@FunctionalInterface
public static interface ResultSetHandler<S, T> {

Map<S, T> accept(ResultSet rs) throws SQLException;
}

public <S, T> Map<S, T> processContent(String query, ResultSetHandler handler) {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
try (Connection con = DriverManager.getConnection(connectionUrl)) {
try (Statement stmt = con.createStatement()) {
try (ResultSet resultSet = stmt.executeQuery(query)) {
return handler.accept(resultSet);
}
}
}
} catch (SQLException e) {
throw new IllegalStateException("Unable to execute query: " + query, e);
} catch (ClassNotFoundException ex) {
Logger.getLogger(EngineSQLUtils.class.getName()).log(Level.SEVERE, null, ex);
}

return null;
}

TT. TT.
Answer

You're taking a value from the resultset and use it as a column name in that same resultset. That will not work with the query you have.

If your resultset has 1717-002 as a value, that line rs.getString(rs.getString(2).trim()); will expand to rs.getString("1717-002");. Surely you didn't mean something like that? You only have columns RULEID and GRAPHRULE.

Comments