Re: bind variables with jdbc

"Dave" <davidr212000_at_yahoo.com> wrote in message news:5e092a4e.0404010857.272068f5_at_posting.google.com...
> ilee_at_bigpond.net.au (Ivan) wrote in message
news:<90137e1b.0403301523.55aed707_at_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 Received on Fri Apr 02 2004 - 00:51:57 CEST