Hello,

In my web app I have Data Fetches in DAO Methods. Those are plain Select querries with different 'where' clause and values depending on input to the DAO Method. I build Where part depending on what input I get.( for example: if user wantes to get customer by giving ID then query becomes:
select fld1, fld2, fld3 from customer where id = "someID"

if user wantes to get customer by giving name then query becomes:
select fld1, fld2, fld3 from customer where name = "inName")

So each time this DAO method is executed, it may have different querry to execute. This query is not in a loop (unlike where multiple inserts or updates would be)

Each time I return from method, I close statement and then connection (which is thr' a connection pool).

In this situation, does it buy me performance (or any other thing) if I use PreparedStatement or I will be better off with simple Statement??

Thanks,
P.I.

Always use PreparedStatements to prevent SQL injection, particularly in a web application.
To ease your JDBC pain, have you tried O/R Broker, which provides you with externalized SQL, named parameters, and automatic mapping to your Java objects?

I am looking at it.Thanks for your suggestion.
P.I.

Your application server should have a cache for prepared statements, as well as the database might cache some optimizations itself when using prepared statements. So the advantage of using prepared statements is not only present when you do queries in a loop or something, but also when you use the same query several times in the lifetime of an application.

Having "the same query" is independent of the data ("someID"), it is just the sql execution order and stuff like this that is optimized.

So my suggestion is to always use a prepared statement unless you are sure that the query is only used once in the lifetime of an application.

Kind regards,

Andreas Berg, Triona

Hello,

In my web app I have Data Fetches in DAO Methods. Those are plain Select querries with different 'where' clause and values depending on input to the DAO Method. I build Where part depending on what input I get.( for example: if user wantes to get customer by giving ID then query becomes:
select fld1, fld2, fld3 from customer where id = "someID"

if user wantes to get customer by giving name then query becomes:
select fld1, fld2, fld3 from customer where name = "inName")

So each time this DAO method is executed, it may have different querry to execute. This query is not in a loop (unlike where multiple inserts or updates would be)

Each time I return from method, I close statement and then connection (which is thr' a connection pool).

In this situation, does it buy me performance (or any other thing) if I use PreparedStatement or I will be better off with simple Statement??

Thanks,
P.I.

With simple statement there is always danger of sql injection. always use preparedstatement. you can try out NamedParametersStatement