JDBC Tutorial

JDBC Prepared Statement

     PreparesStament interface extendes Statement Interface to add methods that allow you to execute SQL statements using Bind Variables. It represents Pre-Compiled SQL statement that can be executed multiple times. In this Bind Variables are parameter markers represented by ? in the SQL string. and they are used to specify input values to the statement that may vary at runtime

Example of Using PreparedStatement to Query Data

       Oracle scott schema, Query Emp table, employees who joined after 1981-10-10, display their details like empno, ename and hiredate. Here Bind Variable is Date. Programmer has to convert LocalDate to java.sql.Date object. for this reason, used Date class' ValueOf(LocalDate) static method to convert localdate to java.sql.Date object. Finally ExecuteQuery called on Prepared Statement. If successful ResultSet object has employees whose hiredate is greater than 1981-10-10.

	    String sql="select EMPNO,ENAME,HIREDATE from emp\n"
	    	+ "                            where HIREDATE >  ?  order by HIREDATE";
	    PreparedStatement stmt = conn.prepareStatement(sql);
	    stmt.setDate(1, Date.valueOf(LocalDate.of(1981, 10,10)));

	    ResultSet rs = stmt.executeQuery();

	    while(rs.next()) {
		System.out.println(rs.getInt(1)+" "+rs.getString(2)+" "+rs.getDate(3));

Here is the program output
	7839 KING 1981-11-17
	7902 FORD 1981-12-03
	7900 JAMES 1981-12-03
	7934 MILLER 1982-01-23
	7788 SCOTT 1987-04-19
	7876 ADAMS 1987-05-23

Above example can be executed using OraclePreparedStatement

      PreparedStatement does not support set columns values using Named parameters. so for that reason, you can use OraclePreparedStatement as shown below. setDateAtName function accepts name of the Bind parameter and value. Here Bind Parameter is : followed by name, i.e ":hiredate". In set function specify wthout colon i.e "horedate", or some other name ...

		import oracle.jdbc.OraclePreparedStatement;

	    OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement("select EMPNO,ENAME,HIREDATE from emp\n"
	    	+ "                            where HIREDATE >  :hiredate  order by HIREDATE");
	    stmt.setDateAtName("hiredate", Date.valueOf(LocalDate.of(1981, 10,10)));

	    ResultSet rs = stmt.executeQuery();


How to INSERT employee record using Prepared Statement

      	    java.sql.Date hireDate = java.sql.Date.valueOf(LocalDate.now());
	    BigDecimal bgSalary = new BigDecimal("9888.99");
 	    PreparedStatement stmt = conn.prepareStatement(sql);
 	    stmt.setInt(1, 3334);
 	    stmt.setString(2, "SIVA");
 	    stmt.setBigDecimal(3, bgSalary);//stmt.setFloat(3,9999.90f);
 	    stmt.setDate(4, hireDate);
 	    stmt.setInt(5, 20);


UPDATE using PreparedStatement interface

DELETE using PreparedStatement interface