April 19th, 2001, 12:59 PM
I am strictly a java programmer that connects to an oracle database to do basic query jobs. I was reading in an Oracle tuning book that it would be much more efficient for me to use bind variables in my queries. All of my queries are written in JAVA and passed to the database through either a bean or JDBC connection and all queries are exactly the same with the exception of the variables in the where clause (i.e. where customer= 77447). I read that my query should read (i.e. where customer = :custNO) or something. How do I tell or pass what the custNO is from my java program to the oracle database? Does anyone have experience in this? I would need to somehow set the bind variable in JAVA and pass it the same way i pass the rest of the query, but i have no idea what to call it or how to pass it.... Sorry for the long question, I just want to make sure my problem is clear.... thanks to whoever has thoughts on this.
April 19th, 2001, 06:29 PM
What you need is called a 'prepared statement'. This way you can have a generic SQL statement and pass a parameter to it. For example...
// Load driver
// Get connection
Connection con = DriverManager.getConnection("jdbc:odbc:yourdatabase", "", "");
// Setup PreparedStatement
PreparedStatement ps = con.prepareStatement("SELECT firstname,lastname FROM customers WHERE customer = ?");
// Setup ResultSet
// Now this is where we setup the parameter to be passed to the statement
ps.setInt(77447); // Of course this could be some other variable
// Now get the results
rs = ps.executeQuery();
This is basically it. The "?" in the statement is the variable you pass in. You can have several question marks in the statement, but you must set their value in the order they appear in the statement. Also the value does not have to be an Int but it can be String,Float,Blob,etc. I hope this helps.