CODEWITHSUNDEEP
javamysqljdbcprepared-statement
anthonyms
anthonyms

Reputation: 948

Posting Column name as Integer

I have the having gameid, hometeamid, visitorteamid,winnerid all as int.

The query 

Update game set winnerid=hometeamid where gameid=1;

works fine on the Mysql workbench.

I am trying to run the same from a prepared statement without success throwing the error

java.sql.BatchUpdateException: Incorrect integer value: 'hometeamid' for column 'winnerId' at row 1

since "hometeamid" is passed as a string. the code is as follows

String query = "update `match` set winnerid= ? where  gameid=?";
Map<Integer, String> data = getMap();//gameid and either 'hometeamid' or 'awaytemid'
try {
    PreparedStatement ps = connection.prepareStatement(query);
for (Map.Entry<Integer, String> entry : data.entrySet()) {
    ps.setObject(1, entry.getValue());
    ps.setInt(2, entry.getKey());
    ps.addBatch();
}
ps.executeBatch();
} catch (SQLException e) {
        logger.error(e, e);
} finally {
    closeQuietly(connection);
}

How can I run the update correctly from a PreparedStatement?

Any other improvements recomendations are highly appreciated.

Upvotes: 1

Views: 323

Answers (2)

JB Nizet
JB Nizet

Reputation: 691865

You can't pass a column name as a parameter in a prepared statement. You can only pass values.

If you really need this column name to be dynamic, then you'll have to use String concatenation to build this part of the query.

Upvotes: 1

Michał Powaga
Michał Powaga

Reputation: 23183

Change your query to something like:

String query = "update `match` set winnerid = case when ? = 'hometeamid' then hometeamid else visitorteamid end where  gameid=?"

Upvotes: 1

Related Questions