Re: bind variables with jdbc

From: Dave <>
Date: 1 Apr 2004 08:57:42 -0800
Message-ID: <> (Ivan) wrote in message news:<>...
> 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 Received on Thu Apr 01 2004 - 18:57:42 CEST

Original text of this message