File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Dynamic Vs Static SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Customer Requirements for Developers this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Dynamic Vs Static SQL" Watch "Dynamic Vs Static SQL" New topic

Dynamic Vs Static SQL

Tom Raj

Joined: Mar 03, 2005
Posts: 11
Can somebody elaborate the difference between Static and Dynamic SQL. I think even if we have parameter markers in the SQL, it can be considered static. Does this has to do somehting with compilation/binding of the SQL??

If any body could give some examples that could be great..

Shaan Shar
Ranch Hand

Joined: Dec 27, 2005
Posts: 1249

Tom this is a very simple issue in static query you are giving the parameters before compile time and then compile and run it whereas in Dynamic query which we used like PreparedStatement in JAVA is dynamically binded at the run time with the parameters. Ya offcourse it's a sort of Dynamic binding in PreparedStatement.

I hope this time you are clear in your mind about Static and Dynamic Query.

The Best way to predict your future is to create it - Every great individual common man
Bruno Boehr

Joined: Feb 15, 2006
Posts: 17

The answer largely depends on the context. Normally you would speak of static vs dynamic SQL as applied to stored procedures, in which case static SQL refers to valid SQL constructs as per the SP syntax of the database at hand. Static statements may have parameters to be replaced with bind variables at runtime, but they are still static in the sense that their validity against the schema can be ensured at compile time.

Dynamic SQL, on the other hand, is generated by the stored procedure at runtime by way of concatenation of some sort, and as you can guess, there is no way to tell upfront whether your dynamic query will be correct SQL or not.

In essence, dynamic vs static represents the same old freedom vs safety dilemma, so it's really up to you to decide which approach fits better with the problem you are trying to solve.

Now, if we observe these concepts in the context of Java and JDBC, they won't make much sense since there is no compile-time checking of SQL syntax. In a sense, however, we can regard prepared statements as some sort of "static" SQL, meaning that their underlying SQL strings are predetermined and won't change between invocations of execute().

Prepared statements have lots of advanteges over regular statements in terms of performance (cached execution plans), security (stronger protection from injection attacks), robustness of code (the driver takes care of proper formatting of parameters, including character escaping), etc.

As a general rule of thumb, if you need to use a query that has varying elements in it, but is still the same SQL construct if parameterized, you should use a prepared statement. Otherwise, compose the query on the fly and be prepared to deal with all the side effects that come along with freedom

<a href="" target="_blank" rel="nofollow">Your first website in Java: easier than you think</a>
I agree. Here's the link:
subject: Dynamic Vs Static SQL
jQuery in Action, 3rd edition