PreparedStatements

[shaf maff]

Hi Guys

Is is possible for me to use preparedstatements for selecting data from the db ? If yes, how ? I know how to do updates and inserts but not selects.


Thanks

[Ulf Dittmer]

That's what the PreparedStatement.executeQuery method is for.

[shaf maff]

Thanks. I need some help understanding bits in the followng code:

pstmt = con.prepareStatement("SELECT EMPNO, PHONENO FROM EMPLOYEE WHERE EMPNO=?"); 
pstmt.setString(1,"000010");
rs = pstmt.executeQuery();
pstmt.close();

What is the point in the "?" in the select statement ?
And what is "pstmt.setString(1,"000010");" doing ?





.
[ December 21, 2008: Message edited by: shaf maff ]

[Ulf Dittmer]

The question mark indicates that the statement has a parameter at that location (which is later supplied by the setString method).

setString (1, "XYZ") causes the first parameter of the statement to be replaced by the string "XYZ". Note that it also takes care of adding surrounding single quotes if necessary. That way you end up with

SELECT EMPNO, PHONENO FROM EMPLOYEE WHERE EMPNO='XYZ'

and not

SELECT EMPNO, PHONENO FROM EMPLOYEE WHERE EMPNO=XYZ

which would cause an error.

It's possible to have several parameters (in other words, question marks) in a statement, and then fill them in using setString(1,...), setString(2,...) etc.

The Java Tutorial talks about this at length: http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html

[shaf maff]

Thanks! One more question, can you do commits/rollbacks with preparedstatements?

[Ulf Dittmer]

Yes, there's nothing special about them in this regard - they work like any other Statement.

[shaf maff]

Ahha - So how would I implement them (a small example would be nice ) ?


.

[Ulf Dittmer]

The JDBC tutorial I linked to has a chapter on transactions; I'm sure it contains sample code.

[shaf maff]

Ive read many tutorials, they either miss out what I need to import or dont explain the code in detail or they are like over bloated and it feels like I have to read a book just to learn one functionality.

[Jeanne Boyarsky]

Originally posted by shaf maff:
Ahha - So how would I implement them (a small example would be nice ) ?

Your second post in this thread is the example. It creates a prepared statement, sets the parameters and executes it. What part of it are you having difficulty with that you are looking for more explanation?

[Bauke Scholtz]

His question was rather regarding committing and rollbacking.

Well, that's fairly obvious. If you have multiple queries on a single connection which depends on each other (a transaction), then you may need to rollback the complete transaction when executing a query fails. This is only useful if autocommit is set to false.

This is fairly important if you're using connection pooling, because the connection may be reused for other queries/transactions. If you're executing only a single query at a time, then you normally don't need to worry about rollbacking.
[ December 21, 2008: Message edited by: Bauke Scholtz ]