Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What are bind variables? How to execute a query with bind variables using JDBC?
A bind variable is an SQL statement with a temporary variable as place holders which are later replaced with appropriate values. For example, if you have a table named employee in the database created as shown below:
+---------+--------+----------------+ | Name | Salary | Location | +---------+--------+----------------+ | Amit | 30000 | Hyderabad | | Kalyan | 40000 | Vishakhapatnam | | Renuka | 50000 | Delhi | | Archana | 15000 | Mumbai | +---------+--------+----------------+
if you need to retrieve records from the employee table with a salary greater than 20000 then you can write a query as:
SELECT Name, Salary FROM Employee WHERE Salary > 30000;
Here, instead of the value 30000, you can use a variable as a place holder and pass a value to it later.
SELECT Name, Salary FROM Employee WHERE Salary > sal_val;
Executing query with bind variables in JDBC
PreparedStatement interface in JDBC API represents the precompiled statement and you can create a precompiled statement by passing the query with bind variables to the prepareStatement() method of the Connection interface. But instead of bind variables, you need to use question marks (?) as place holders.
//Creating a Prepared Statement String query = "SELECT Name, Salary FROM Employee WHERE Salary > ?"; PreparedStatement pstmt = con.prepareStatement(query);
Once you create a PreparedStatement you can set the values to its place holders using the setter methods provided by the PreparedStatement interface. According to the datatype of the bind variable, you need to select the respective setter method. Here, in this case, the data type of the bind variable is integer therefore, we need to use setInt() method.
To this method, you need to pass an integer representing the placement index of the place holder and value you want to set to it. Since there is only place holder, the index of the place holder will be 1 and the value we need to set to it is 30000.
pstmt.setInt(1, 30000);
Finally, you can execute the prepared statement using one of the execute methods (execute(), executeUpdate(), executeQuery()). Since this query returns result set we need to use the executeQuery() method.
ResultSet rs = pstmt.executeQuery();
Example
Following JDBC program demonstrates the execution of Query with bind variables.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BindVariableExample {
public static void main(String args[]) throws SQLException {
//Registering the Driver
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//Getting the connection
String mysqlUrl = "jdbc:mysql://localhost/testdb";
Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
System.out.println("Connection established......");
//Creating a Prepared Statement
String query = "SELECT Name, Salary FROM Employee WHERE Salary > ?";
PreparedStatement pstmt = con.prepareStatement(query);
pstmt.setInt(1, 30000);
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
System.out.print("Name: "+rs.getString("Name")+", ");
System.out.print("Salary: "+rs.getInt("Salary"));
System.out.println();
}
}
}Output
Connection established...... Name: Kalyan, Salary: 40000 Name: Renuka, Salary: 50000
5K+ Views
