Results 1 to 11 of 11
  1. #1
    anandjain1984 is offline Member
    Join Date
    Dec 2009
    Posts
    3
    Rep Power
    0

    Default Difference between JDBC Statement and PreparedStatement

    What is the difference between Statement and PreparedStatement in JDBC ? Explain it with some example.
    When to use either of these?

  2. #2
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Any time you don't have a single, complete literal String, use PreparedStatement. Concatenating variables (especially when their values come from user data) is just begging for problems, and inviting SQL injection attacks. The only exception is when those "variables" contain column/table names (or other DDL type data) as PreparedStatement can use placeholders only for values.

    For the rest see the API docs and the Tutorials.

  3. #3
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,036
    Rep Power
    20

    Default

    Quote Originally Posted by masijade View Post
    The only exception is when those "variables" contain column/table names (or other DDL type data) as PreparedStatement can use placeholders only for values.
    Not necessarily the only exception, though you do have to be careful. Columns that are essentially flags (eg a SUCCESS or FAILURE flag say), and that are lopsided to one of the values, might be better served (as far as SQL execution times) as two separate execution plans, rather than the single one generated if the column was bound.

  4. #4
    Steve11235's Avatar
    Steve11235 is offline Senior Member
    Join Date
    Dec 2008
    Posts
    1,046
    Rep Power
    7

    Default

    The difference is the PreparedStatement is a kind of Statement. PreparedStatement allows you to define parameters that you set, which allows you to reuse the Statement many times with "preparing" over and over. Note that all Statement require "preparation", and it takes a long time; it's noticeable even if you just watch in debug. PreparedStatment parameters also project you against SQL injection attacks, where a parameter value contains a ";" followed by SQL statements which are then executed.

  5. #5
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,036
    Rep Power
    20

    Default

    In addition, using statements for commonly run queries can result in you hitting (in Oracle, though others have similar concepts) a shared pool memory error (ORA-4031). This is because the db will have to create an execution plan for every statement you create, even though the only difference is the particular query filter values. ie

    SELECT * FROM my_table WHERE id = 123
    and
    SELECT * FROM my_table WHERE id = 321

    would each create a unique execution plan in the db. WIth a prepared statement a single plan would be created for the query -
    SELECT * FROM my_table WHERE id = ?

    So, the shared pool doesn't get flooded with hundreds and hundreds of little, similar, plans...and you don't have to hard parse (that is defined an execution plan) for every call to what is, essentially, the same statement. It's a win-win situation...:)

  6. #6
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Okay, I think some clarification is in order here. When I said the only exception, I meant the only exception to not cobbling together a statement. If table and or column names are contained in variables, then the statement must be cobbled together (or, even better, replaced in with MessageFormat or something). However, I only ever use Statement (and I would suggest this as well) when 1. the statement does not variable values (i.e. a "static" statement), and 2. it is, essentially, a one-off statement (i.e. there will be a considerable amount of time between uses of the statement). All statements with variable (i.e. non-hardcoded) values and all statements that are performed repeatedly, are PreparedStatements.

  7. #7
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,036
    Rep Power
    20

    Default

    Still say in some situations with lopsided flags you don't want a common SELECT using the same execution plan for both possibilities.

    For example:
    SELECT * FROM employees WHERE dept_id = ? AND active = ?

    If your table is seriously skewed such that the active field (a 'Y' or an 'N') contains far more inactive users, you don't want the same plan. One will do a full table scan, the other will use an index (I think). And whichever one gets in first is the one that'll be used for all.
    So concatenating the active flag in can be preferable to binding it.

  8. #8
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Yes, there are always "corner-cases".

  9. #9
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,036
    Rep Power
    20

    Default

    Oh yes. And it's them we get paid the dosh for...:D

  10. #10
    sampath3033 is offline Member
    Join Date
    Dec 2009
    Posts
    1
    Rep Power
    0

    Default

    how to send alerts to MSN messanger from java

  11. #11
    Tolls is online now Moderator
    Join Date
    Apr 2009
    Posts
    12,036
    Rep Power
    20

Similar Threads

  1. JDBC Prepared Statement
    By Floetic in forum JDBC
    Replies: 4
    Last Post: 05-20-2009, 11:53 PM
  2. MySQL/JDBC Prepared Statement Select query
    By thelinuxguy in forum Advanced Java
    Replies: 4
    Last Post: 02-12-2009, 05:29 PM
  3. MySQL/JDBC prepared statement problem
    By thelinuxguy in forum Advanced Java
    Replies: 3
    Last Post: 02-11-2009, 11:21 PM
  4. JDBC statement question
    By nick2price in forum Advanced Java
    Replies: 21
    Last Post: 09-29-2008, 02:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •