JDBC Tutorial
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
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
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();
java.sql.Date hireDate = java.sql.Date.valueOf(LocalDate.now());
BigDecimal bgSalary = new BigDecimal("9888.99");
sql="INSERT INTO EMP(EMPNO,ENAME,SAL,HIREDATE,DEPTNO) VALUES(?,?,?,?,?)";
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);
stmt.executeUpdate();
ADS