Invalid Column Name in java but works on database

Question

Given the following (MYSQL-) Query:

SELECT view_match_metric.player_id, discord_id, view_match_metric.name, 
  view_match_metric.xp AS xpthis, player.xp AS xptotal, ranked, mode, mu, sigma, 
  IF(team = 'Alpha', goals_alpha > goals_beta, goals_beta > goals_alpha) as won 
FROM view_match_metric 
LEFT OUTER JOIN kl_idmap ON view_match_metric.player_id = kl_idmap.player_id 
LEFT OUTER JOIN kl_trueelo ON view_match_metric.player_id = kl_trueelo.player_id 
LEFT OUTER JOIN player ON view_match_metric.player_id = player.player_id 
WHERE match_id="169498"

When I execute it on my database program (Adminer), it returns the following result:

Please note, that it does contain int(10) unsigned entries for xpthis and xptotal

In java however this Query throws a

java.sql.SQLException: Invalid column name
at com.sun.rowset.CachedRowSetImpl.getColIdxByName(Unknown Source)
at com.sun.rowset.CachedRowSetImpl.getInt(Unknown Source)

for the xpthis and xptotal field. It does work without getting these fields. As the xp fields are ambiguous in multiple tables, I do specify them with those alias'.

the code for loading the data:

while (result.next()) {
    match.mode = result.getString("mode");
    match.ranked = result.getBoolean("ranked");
    TrueEloPlayerData player = new TrueEloPlayerData();
    player.id = result.getLong("player_id");
    player.discordID = result.getLong("discord_id");
    player.name = result.getString("name");
    //exception thrown in next line:
    player.xp = result.getInt("xpthis");
    player.level = Utility.getLevel(result.getInt("xptotal"));
    //some more
    match.players.add(player);
}

For parallel processing the usual ResultSet is copied into a CachedRowSet:

CachedRowSet res = RowSetProvider.newFactory().createCachedRowSet();
res.populate(resultSet);

I tried getting them with getDouble, getString, even with getObject, tried to direct access the fields without alias (getInt(tablename.xp)), however it never solved the issue.

Im completely clueless why it doesn't work in here as it does in the database.

This seems to be a known issue when using column alias. [CachedRowSetImpl getString based on column label throws “Invalid column name”](https://stackoverflow.com/questions/15184709/cachedrowsetimpl-getstring-based-on-column-label-throws-invalid-column-name) — Barns, Jan 20 '19 at 20:12
Ah that explains it.Thanks for the information, I will have to use a workaround then... — kleopi, Jan 20 '19 at 20:27

score 1 · Accepted Answer · answered Jan 20 '19 at 20:06

1

There is also another method with the same name based on index.

int getInt(int columnIndex) throws SQLException

Try to see if work.As i see from image is at 4 index (start numbering columns from 1)

player.xp = result.getInt(4);

answered Jan 20 '19 at 20:06

Traian GEICU

1,750
3
14
26

Seems to be the best (and pretty much only way) to do it. Didn't want to use it originally as adding a new field later may cause errors. Guess I have no other choice, tho. Thank you – kleopi Jan 20 '19 at 20:28
Yeah, it's seem so. Good luck with coding. – Traian GEICU Jan 20 '19 at 20:31

Invalid Column Name in java but works on database

1 Answers1