aspose file tools*
The moose likes JDBC and the fly likes does OracleDriver really precompile a PreparedStatement? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "does OracleDriver really precompile a PreparedStatement?" Watch "does OracleDriver really precompile a PreparedStatement?" New topic
Author

does OracleDriver really precompile a PreparedStatement?

Abhishek Bharti
Greenhorn

Joined: May 18, 2006
Posts: 6
Hi,

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("URL","USER","PASS");
PreparedStatement statement = conn.prepareStatement(" Selec * from temp ");
SQLWarning warning = statement.getWarnings();
System.out.println("warning : "+warning);
ResultSet set = statement.executeQuery();

I am using the above code to use a PreparedStatement. Here we can see that the Query has the syntax error. But the code throws the exception after i execute the statement. I have idea about the PreparedStatement that it precompiles the SQL query string passed, if it does then it should throw the exception at the time when we call prepareStatement method from connection. Or this precompilation depends upon the driver?

Abhishek


Abhishek Bharti
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

The driver does not compile any sql. The database does.
Your query will get compiled at first use.

The advantage of PreparedStatement is, that your database knows you're using the same query the second time, even when you use different values for your parameters.
If you use a Statement, Oracle will compile the query again if you change the where clause values.

Example:

preparedstatement
query select cust_name from cust where cust_id = ?
bind parameter cust_id with value 6
execute query -> Oracle compiles the statement
bind parameter cust_id with value 8
execute query -> Oracle can reuse the statement because it knows it's the same as the previous one.


non-prepared statement
query select cust_name from cust where cust_id = 6
execute query -> Oracle compiles the statement
query select cust_name from cust where cust_id = 8
execute query -> Oracle compiles the statement

Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
Abhishek Bharti
Greenhorn

Joined: May 18, 2006
Posts: 6
Thanks a lot Jan,

i still have a confusion why no exception was thrown at this line

PreparedStatement statement = conn.prepareStatement(" Selec * from temp ");

as query string has error it should throw some exception, or the query string is compiled at once only a PreparedStatement is executed for the first time....

Thanks,

Abhishek
[ April 27, 2007: Message edited by: Abhishek ]
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

Your last remark is correct.
prepareStatement() does not validate your sql. It's not passed to the database at that time. (note: the driver does not validate your sql. The database does)

The database will receive, validate, compile and execute your query in your line ResultSet set = statement.executeQuery();.

Regards, Jan
Abhishek Bharti
Greenhorn

Joined: May 18, 2006
Posts: 6
Thank Jan,

I really got the idea that even in case of prepared statements everything happens at runtime , only the compilation takes place for one time , but in simple statements validation, compilation and execution takes place each time we execute the statement.


How can we deal with the dynamic queries in case of prepared statements as the query string will be compiled only once.


Thanks,

Abhishek
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2503
    
    8

How can we deal with the dynamic queries in case of prepared statements as the query string will be compiled only once.
If pass a new sql statement to your PreparedStatement (example: statement = conn.prepareStatement(" Select * from temp where temp_name is not null ");), Oracle will not find a match in it's cached sql statements, and will compile the new one.

(small asside: Oracle pools a number of sql statements. If the query you pass to Oracle exactly matches one of the pooled statement, Oracle can use the pooled statement. See ALL ABOUT SHARED POOL by Tapas Kumar).

Regards, Jan
Abhishek Bharti
Greenhorn

Joined: May 18, 2006
Posts: 6
Thanks a lot Jan,

I will study about the connection caching and pooling as i do not have much idea about it.


Thanks again,

Abhishek
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

"Abhishek",
Welcome to the JavaRanch.

We're a friendly group, but we do require members to have valid display names.

Display names must be two words: your first name, a space, then your last name. Fictitious names are not allowed.

Please edit your profile and correct your display name since accounts with invalid display names get deleted, often without warning

thanks,
Dave
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: does OracleDriver really precompile a PreparedStatement?