CODEWITHSUNDEEP
javamysqlsqljdbcmysql-connector
ktm5124
ktm5124

Reputation: 12123

MySQL Connector/J - Mapping Java String to MySQL INT

The documentation for Connector/J says that it can easily convert a MySQL INT to a Java String type. (That is, mapping from a result set to a Java type.)

But does this work the other way around? In other words, can you use a Java String in a prepared statement where MySQL expects an INT? Is this considered good/bad practice?

# pseudo-code
table EMPLOYEES 
(
    NAME VARCHAR(50)
    EMPLOYEE_NO INT(11)
)

// Java code
PreparedStatement prepStmt = 
    con.prepareStatement("SELECT * from EMPLOYEES where EMPLOYEE_NO=?");
prepStmt.setString(1, str);
ResultSet rs = prepStmt.execute(); // ...

Upvotes: 1

Views: 443

Answers (1)

ddmps
ddmps

Reputation: 4380

According to javadoc of setString() (my emphasis):

Sets the designated parameter to the given Java String value. The driver converts this to an SQL VARCHAR or LONGVARCHAR value (depending on the argument's size relative to the driver's limits on VARCHAR values) when it sends it to the database.

Meaning this might not work, although it probably will (I suppose an int is represented just like a string when sent), but if things change that might not be the case. Either way, I do not regard it as good practice as there might be non-integer values in a String.

Instead, use:

prepStmt.setInt(1, Integer.parseInt(str));

and handle NumberFormatException as you see fit.

Additionally the following line is invalid as execute() returns a boolean value.:

ResultSet rs = prepStmt.execute();

Instead, you can use:

if (prepStmt.execute()) {
    ResultSet rs = prepStmt.getResultSet();
}

or

ResultSet rs = prepStmt.executeQuery();

Upvotes: 1

Related Questions