MySQL Connector/J - Mapping Java String to MySQL INT

Question

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(); // ...

ddmps · Accepted Answer · 2013-04-12 01:45:15Z

1

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();

edited Apr 12, 2013 at 1:45

answered Apr 12, 2013 at 1:01

ddmps

4,3901 gold badge23 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ktm5124 Over a year ago

Thanks, that is a good point. I guess the JavaDoc says it all. For some reason I was hunting for the Connector/J JavaDoc (I had an easier time finding its source code) but should have just looked at the specification interface instead.

ktm5124 Over a year ago

Yes, the code was just for demonstration purposes, I should have written executeQuery.

Collectives™ on Stack Overflow

MySQL Connector/J - Mapping Java String to MySQL INT

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related