Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Prepared Statement in Oracle

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 554
    Comment on it

    Prepared statement is used to execute same database statements repeatedly with high performance. For example, if you want to insert multiple employee records you can use same prepared statement again and again.

    For below illustration it is assumed that you have dbName database in Oracle with password dbPass. Change the corresponding value if you have different setting in below code.

    You can use following query to create the database table for employee:

    create table Employee (
      id    number  primary key,
      name  varchar2(30),
      role  varchar2(30),
      age   number(2)
    );
    

    Following code illustrate the use of prepared statement:

    import java.sql.*;
    
    public class TestPreparedStatement {
       public static void main(String[] args) throws Exception{
          Class.forName("oracle.jdbc.driver.OracleDriver");
          Connection con = DriverManager.getConnection
          ("jdbc:oracle:thin:@//localhost:1521/dbName","dbName","dbPass");
          PreparedStatement psEmployee = con.prepareStatement("insert into Employee values(?,?,?,?)");
    
          int [] employeeIds={1, 2, 3};
          String [] employeeNames={"Chandan Ahluwalia", "Sumit Vyas", "Aditya Raturi"};
          String [] employeeRole={"SSE", "SE", "SE"};
          int [] employeeAge={34, 27, 23};
    
          for(int i=0; i<employeeIds.length; i++){
              psEmployee.setInt(1, employeeIds[i]);
              psEmployee.setString(2, employeeNames[i]);
              psEmployee.setString(3, employeeRole[i]);
              psEmployee.setInt(4, employeeAge[i]);
              psEmployee.executeUpdate();
          }
    
          Statement stmt = con.createStatement();
          String query = "select * from Employee";
          ResultSet rs =  stmt.executeQuery(query);
          System.out.println("Id EmployeeName Role Age");
          while (rs.next()) {
             int id = rs.getInt("id");
             String name = rs.getString("name");
             String role = rs.getString("role");
             int age = rs.getInt("age");
             System.out.println(id + "  " + name+"   "+role+"   "+age);
          }      
       }
    }
    

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: