470,827 Members | 1,722 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,827 developers. It's quick & easy.

Re: bind variables with jdbc

il**@bigpond.net.au (Ivan) wrote in message news:<90**************************@posting.google. com>...
Hi all,

I have an application that is written in servlets that connects to an
oracle database 8.1.7 via jdbc. My DBA has just notify me that I am
not using bind variables in my code and was wondering what i'll need
to change to utilise this.

At the moment i am just opening a connection, making a statement (sql
query) and executing it to return a resultset. Would i need to change
all my queries to stored procedures so that bind variables are used or
is there another way.

thanks all
Ivan -

I little more specific to the java syntax. Right now you are probably
using an implementation of the Statement interface...for instance,

Connection c = new Connection(....
Statement st = c.createStatement();
ResultSet rs = st.executeQuery('select xyz from table1 where a = 2 and
b = 3');

you'll want something more like....

PreparedStatement pt = c.prepareStatement('select xyz from table1
where a = ? and b = ?');
pt.setInt(1, 2);
pt.setInt(2, 3);
ResultSet rs = pt.executeQuery();

Depending on your application, you would either close the prepared
statement after use or leave it open. Probably close it to free up db
resources. But the point is, since you are using bind variables (?),
next time you prepare the same statement in java, the parsed sql
statement will likely still exist in Oracle memory which will allow
you to reuse it. This is much better for scalability.

Dave
Jun 27 '08 #1
2 4985

"Dave" <da**********@yahoo.comwrote in message
news:5e**************************@posting.google.c om...
il**@bigpond.net.au (Ivan) wrote in message
news:<90**************************@posting.google. com>...
Hi all,

I have an application that is written in servlets that connects to an
oracle database 8.1.7 via jdbc. My DBA has just notify me that I am
not using bind variables in my code and was wondering what i'll need
to change to utilise this.

At the moment i am just opening a connection, making a statement (sql
query) and executing it to return a resultset. Would i need to change
all my queries to stored procedures so that bind variables are used or
is there another way.

thanks all

Ivan -

I little more specific to the java syntax. Right now you are probably
using an implementation of the Statement interface...for instance,

Connection c = new Connection(....
Statement st = c.createStatement();
ResultSet rs = st.executeQuery('select xyz from table1 where a = 2 and
b = 3');

you'll want something more like....

PreparedStatement pt = c.prepareStatement('select xyz from table1
where a = ? and b = ?');
pt.setInt(1, 2);
pt.setInt(2, 3);
ResultSet rs = pt.executeQuery();

Depending on your application, you would either close the prepared
statement after use or leave it open. Probably close it to free up db
resources. But the point is, since you are using bind variables (?),
next time you prepare the same statement in java, the parsed sql
statement will likely still exist in Oracle memory which will allow
you to reuse it. This is much better for scalability.

Dave
Yes, it is. If you can leave it open and just change the bind variables and
reexecute you will scale even more and eliminate a soft parse. But the way
Dave has it is correct.
Jim
Jun 27 '08 #2
thanks everybody for all the help, the syntax was really helpful. I
didn't really mind changing all the code but i just wanted to get it
right this time.
Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Jack | last post: by
1 post views Thread by Achille Carette | last post: by
reply views Thread by ClimberBear | last post: by
1 post views Thread by rshome | last post: by
reply views Thread by mihailmihai484 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.