PreparedStatement, setString() and setInt()
Hi,
I am using PreparedStatement in my JDBC program and using the setString() method to replace the placeholders with values dynamically.
PreparedStatement pstmt = new PreparedStatement( "INSERT INTO MYTAB VALUES ( ?, ?, ? )" );
BufferedReader br = new BufferedReader( new InputStreamReader( System.in ) );
//code that takes input from console for the values
String name = br.readLine();
String salary = br.readLine();
String dept = br.readLine();
pstmt.setString( 1, name );
pstmt.setString( 2, salary ); //I could use pstmt.setInt( 2, Integer.parseInt( salary ) );
pstmt.setString( 3, dept );
I have a table called MYTAB and it has three columns: name (varachar2), salary( integer ) and dept (varchar2).
Now I have seen that even if the data type of salary column is integer I can insert data into it using the setString() method. It is true for all other data types.
Now my question is if setString() is sufficient to insert value into a column and if the rest is taken care of, then what is the need to have individual methods for individual data types ( like setInt(), setFloat() etc).
One possible answer could be that a String is an object and hence it occupies more memory that what a primitive type does. Is there any other reason for providing setter methods for different data types?
[1387 byte] By [
maitya] at [2007-11-27 10:57:21]
# 1
> Hi,
> I am using PreparedStatement in my JDBC program
> and using the setString() method to replace the
> placeholders with values dynamically.
>
> PreparedStatement pstmt = new PreparedStatement(
> "INSERT INTO MYTAB VALUES ( ?, ?, ? )" );
> BufferedReader br = new BufferedReader( new
> InputStreamReader( System.in ) );
> //code that takes input from console for the values
> String name = br.readLine();
> String salary = br.readLine();
> String dept = br.readLine();
>
> pstmt.setString( 1, name );
> pstmt.setString( 2, salary ); //I could use
> pstmt.setInt( 2, Integer.parseInt( salary ) );
> pstmt.setString( 3, dept );
>
> I have a table called MYTAB and it has three columns:
> name (varachar2), salary( integer ) and dept
> (varchar2).
> Now I have seen that even if the data type of salary
> column is integer I can insert data into it using the
> setString() method. It is true for all other data
> types.
No. try pstmt.setString( 2, "as23"); and you'll get an error
You better convert allways to the specified type
>
> Now my question is if setString() is sufficient to
> insert value into a column and if the rest is taken
> care of, then what is the need to have individual
> methods for individual data types ( like setInt(),
> setFloat() etc).
>
> One possible answer could be that a String is an
> object and hence it occupies more memory that what a
> primitive type does. Is there any other reason for
> providing setter methods for different data types?
# 2
Why is there a setInt() method? Well, consider the possibility that I might have an int value to put into a parameter. Wouldn't a setInt() method be the most reasonable way of doing that, instead of making me convert it to a String?
# 3
To Add to that, the setString method is the best example to describe what preparedStatements are good for. That method will take in a String and do all of the neccessary escaping of the String as well as adding the single quotes around the value before inserting, making the SQL you actually write in your class (or xml where ever you want to keep it) much more neater and presentable and meaning you don't need to worry about ensuring you haven't missed a quote somewhere and you get a faulty insert.
Wes
# 4
> Now my question is if setString() is sufficient to
> insert value into a column and if the rest is taken
> care of, then what is the need to have individual
> methods for individual data types ( like setInt(),
> setFloat() etc).
To try and provide a concise answer to your specific to your question:
For efficiency sake and to help ensure stability of your application you should always attempt to match your setter methods with your datatype.
Some thoughts behind that statement:
I believe it is more efficient to match up the appropriate setter methods with your datatype because you are avoiding an unnecessary implicit conversion (what Dr Clap was alluding to).
In addition I believe you are asking for trouble by allowing the various driver(s) to do an implicit conversion within your code. When I look at the setString API I do not see the type of conversion you are talking about documented in the API (although I think most JDBC programmers are aware that these implicit conversions are available).
Without the implicit conversions being documented in the API; which conversions work and how they work is up to the individual driver vendors meaning you cannot count on the same behavior between vendor offerings. That means using setString for all datatypes could break your code if you choose to use a different driver or different database in the future.
I dont know this for certain, but the datatype conversion may not always happen within the driver. It is possible that the datatype conversion is being handled by the database during the insert processing which would once again mean you are hoping for a consistent but undocumented conversion across all vendors. I try and avoid coding hopefully whenever I can. ;)
# 5
perhaps u might be able to insert into the column with string data type but its not necessary that u can perform the integer operations in the table since tats being stored as String object
# 6
> perhaps u might be able to insert into the column
> with string data type but its not necessary that u
> can perform the integer operations in the table since
> tats being stored as String object
Putting aside the sophomoric use of u which in English is spelled you, what the heck are attempting to say? Your comments appear to be complete gibberish. If you are going to share, why not take the time to spell out words, use complete sentences and form a coherent message? If you are unclear on what you want to say, think about for awhile and post it once you are sure. No reason to confuse the OP with incomplete or nonsensical responses.
# 7
I Am Sorry If i sounded so. I never wanted to put ambiguity into what i'd said.
What I meant is even if we put values using setString(1,string_value) into the column of a table with data type varchar it is not necessary that we should be able to perform integer operation on that column such as addition or multiplication. Hope i am pretty clear at this moment
# 8
> I Am Sorry If i sounded so. I never wanted to put
> ambiguity into what i'd said.
> What I meant is even if we put values using
> setString(1,string_value) into the column of a table
> with data type varchar it is not necessary that we
> should be able to perform integer operation on that
> column such as addition or multiplication. Hope i am
> pretty clear at this moment
Thanks for the clarification, yes, you were quite clear.
# 9
thanks for the Compliment...
