Puzzling "Costly type conversion" warnings
I enabled useUsageAdvisor in my application, and noticed some questionable warnings in the torrent of output that ensued.
If the value of a DECIMAL column is read using either getObject() or getBigDecimal(), the result is a BigDecimal object, but a warning is issued (in either case):
WARNING: Profiler Event: [WARN] at xetus.test.DBSelectTest.main(DBSelectTest.java:62) duration: 0 ms, connection-id: 0, statement-id: 6, resultset-id: -1, message: Costly type conversion for prepared statement. Column number '1' is MySQL type 'FIELD_TYPE_DECIMAL' but needed to be converted to a String for further processing/conversion. It is more efficient to ensure that the datatype asked for via ResultSet.get*() matches what was returned from the server.
Am I to understand that the driver is converting the DECIMAL value to a String and then to a BigDecimal? Is there a less costly alternative?
A bigger surprise is reading a CHAR or VARCHAR column using either getObject() or getString():
WARNING: Profiler Event: [WARN] at xetus.test.DBSelectTest.main(DBSelectTest.java:64) duration: 0 ms, connection-id: 0, statement-id: 6, resultset-id: -1, message: Costly type conversion for prepared statement. Column number '3' is MySQL type 'FIELD_TYPE_VAR_STRING' but needed to be converted to a String for further processing/conversion. It is more efficient to ensure that the datatype asked for via ResultSet.get*() matches what was returned from the server.
I suspect that in fact there is no costly conversion being performed.
In general, I'm confused about why there would be a loss of efficiency in using getObject() -- doesn't the jdbc standard specify the most natural (and therefore efficient) mapping from SQL to Java types?
Finally, this warning is issued when a prepared statement is executed just once:
WARNING: Profiler Event: [WARN] at xetus.test.DBSelectTest.main(DBSelectTest.java:50) duration: 0 ms, connection-id: 0, statement-id: 6, resultset-id: -1, message: PreparedStatement created, but used 1 or fewer times. It is more efficient to prepare statements once, and re-use them many times
Is it not true than once a prepared statement is created, no other query can be executed on the connection without closing the prepared statement first? In my use case, a statement is prepared each time its executed because its interleaved with other queries using one connection. I know its more efficient if I could prepare it just once, but that's not the situation. Unless I misunderstand, this warning just clutters the output.