Bind Variables in Oracle and Java

What are Bind Variables and why do we need them?

Bind variables are used to replace values in SQL queries that are considered to be variable.  When the oracle database first receives a query it checks in its shared pool to see if it already has a compiled version of that query.  If the query is already in the shared pool then the database will then execute that query using the information it has stored in the shared pool.  If the query is not in the shared pool, then the database has to parse the query, figure out the proper execution plan based on the query, and then store the compiled execution plan and query in the shared pool.  This procedure is very costly to the both the CPU and uses shared memory that the database needs.  

When to use Bind Variables?

Bind variables should be used to replace values that can be considered variable in your query.  Essentially all bind variables do is create a query that has variables in it, instead of actual values.  Bind variables should be used when the same query is going to be executed multiple times.  

Example:

To demonstrate the importance of bind variables we will look at an example of queries that would be used to search for real estate.  We will assume the following table definition for searching for real estate:

House
Id: number
Number_of_rooms: number
Number_of_baths: number
Address: varchar2
City: varchar2
State: varchar2
Price: number

Based on what the user is searching for many queries would show up quite frequently.  An example would be many users would search for real estate based on the city, the number of rooms and the price. Listed below are many combinations of what people may be searching for.

Select * from House where number_of_rooms = 2 and city = ‘NEW YORK’ and price <= 150000;
                      Select * from House where number_of_rooms = 3 and city = ‘NEW YORK’ and price <= 900000;
                      Select * from House where number_of_rooms = 2 and city = ‘LOS ANGELES’ and price <= 500000;
                      Select * from House where number_of_rooms = 5 and city = ‘LAS VEGAS’ and price <= 90000;

Select all Open in new window

Essentially all the queries above are all the same query except they have different values.  Oracle however will compile a new query for each of the queries above wasting a lot of resources.  This is why we use bind variables to make sure that this query is only compiled once.  With bind variables the query would look as follows:

Select * from House where number_of_rooms = ? and city = ? and price <= ?;

Select all Open in new window

Now, no matter how many times this query format is ran, the same compiled query will be grabbed out of the shared pool.

How to use Bind Variables in Java?

Using bind variables in Java is very easy.  To use bind variables you want to make sure you are using the PreparedStatement class to create and execute your query.  In java all your dynamic inputs will be represented by '?' in the query as we demonstrated above.  Within the PreparedStatement class you will be able to pass the variables that you want to replace the “?” in your queries as you are creating the statement.  The second parameter of the setInt and the setString functions take the value that you want to set.  Our example above would look something like this:

private void loadForRoomsCityAndPrice(int numberOfRooms, String city, int price)
                      {   
                         String query = “Select id, from House where number_of_rooms = ? and city = ? and price <= ?”
                         PreparedStatement statement = connection.prepareStatement(query);
                         statement.setInt( 1, numberOfRooms);
                         statement.setString( 2, city );
                         statement.setString( 3, price ); 
                         ResultSet rset = pstat.executeQuery();
                         while(rset.hasNext())
                         {
                            //load in the data
                         }
                      }

Select all Open in new window

Once you have your result set you can iterate through the returned data to do what you need to do with it.  To do this code more properly I would suggest a utility that handles all your prepared statements and connection code so you would just pass a query and the values you wanted the bind variables to be set to.