PreparedStatement setString (...) for everyone, even if the corresponding data type is an integer

Question

I came across the following codes, I feel this is doing the wrong thing:

(Note that this is JDK 1.4.2, so the list is not typed)

StringBuffer queryBuffer = new StringBuffer(); ArrayList temp = new ArrayList(); ... queryBuffer.append("and sb.POSTCODE = ? "); temp.add(postcode); ... conn = ConnectionManager.getConnection(); pstmt = conn.prepareStatement(queryBuffer.toString()); 

That worries me:

 for(int i=0; i<temp.size(); i++) { log.debug("setString("+ (i+1) + "," + (String)temp.get(i) + ")"); pstmt.setString(i+1, (String)temp.get(i)); } 

But I noticed that some of the corresponding data types (fields) in the database are integers and dates, will this be okay?

Answer 1

Consider using the setObject () method rather than setString () .

PreparedStatement setObject () will try to convert any java.lang types for you if the type is unknown at compile time.

so with an updated looping cycle (assuming you have Java 5.0) and general null handling:

 int i = 0; for(Object value : temp) { if (value == null) { // set null parameter if value type is null and type is unknown pstmt.setNull(++i, Integer.MIN_VALUE); } else { pstmt.setObject(++i, value); } } 

Note that setNull () can take type as the 2nd parameter, if known.

Answer 2

This did not help me. The following is a query built after adding the binding variables. select ACC_NO from ACC_TABLE, where ACC_NAME = 'java.lang.String';

It tries to convert to type java.lang.String and which results in the following java.sql.SQLException exception: Failed to execute sql command - Original message: null

Where is my ACC_NAME - "user01". So actually the request should be like this, select ACC_NO from ACC_TABLE, where ACC_NAME = 'user01';

So, if my understanding is not wrong, prepareStatement.setObject (index, object) converts the data to the appropriate data type and sets it.

prepareStatement.setObject (index, object) in MySQL works fine, no problem. Only problem is using Oracle. The version of Oracle DB I'm working with

Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production PL / SQL Release 11.2.0.2.0 - Production "CORE 11.2.0.2.0 Production" TNS for 32-bit Windows: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production