Java Use Statements Program

Use of prepared statement

import java.sql.*;

public class jdbcConn 

{

   public static void main(String[] args) throws Exception

   {

      Class.forName("org.apache.derby.jdbc.ClientDriver");

      Connection con = DriverManager.getConnection

      ("jdbc:derby://localhost:1527/testDb","name","pass");

      PreparedStatement updateemp = con.prepareStatement

      ("insert into emp values(?,?,?)");

      updateemp.setInt(1,23);

      updateemp.setString(2,"Roshan");

      updateemp.setString(3, "CEO");

      updateemp.executeUpdate();

      Statement stmt = con.createStatement();

      String query = "select * from emp";

      ResultSet rs =  stmt.executeQuery(query);

      System.out.println("Id Name    Job");

      while (rs.next()) 

      {

         int id = rs.getInt("id");

         String name = rs.getString("name");

         String job = rs.getString("job");

         System.out.println(id + "  " + name+"   "+job);

      }      

   }

}

Use of save point & rollback

import java.sql.*;

public class jdbcConn 

{

   public static void main(String[] args) throws Exception

   {

      Class.forName("org.apache.derby.jdbc.ClientDriver");

      Connection con = DriverManager.getConnection

      ("jdbc:derby://localhost:1527/testDb","name","pass");

      Statement stmt = con.createStatement();

      String query1 = "insert into emp values(5,'name','job')";

      String query2 = "select * from emp";

      con.setAutoCommit(false);

      Savepoint spt1 = con.setSavepoint("svpt1");

      stmt.execute(query1);

      ResultSet rs = stmt.executeQuery(query2);

      int no_of_rows = 0;

      while (rs.next()) 

      {

         no_of_rows++;

      }

      System.out.println("rows before rollback statement = "+ no_of_rows);

      con.rollback(spt1);

      con.commit();

      no_of_rows = 0;

      rs = stmt.executeQuery(query2);

      while (rs.next()) 

      {

         no_of_rows++;

      }

      System.out.println("rows after rollback statement = "+ no_of_rows);

   }

}

Execute multiple SQL statements

import java.sql.*;

public class jdbcConn 

 {

   public static void main(String[] args) throws Exception

   {

      Class.forName("org.apache.derby.jdbc.ClientDriver");

      Connection con = DriverManager.getConnection

      ("jdbc:derby://localhost:1527/testDb","name","pass");

      Statement stmt = con.createStatement

      (ResultSet.type_scroll_sensitive,

      ResultSet.concur_updatable);

      String insertEmp1 = "insert into emp values

      (10,'jay','trainee')";

      String insertEmp2 = "insert into emp values

      (11,'jayes','trainee')";

      String insertEmp3 = "insert into emp values

      (12,'shail','trainee')";

      con.setAutoCommit(false);

      stmt.addBatch(insertEmp1);

      stmt.addBatch(insertEmp2);

      stmt.addBatch(insertEmp3);

      ResultSet rs = stmt.executeQuery("select * from emp");

      rs.last();

      System.out.println("rows before batch execution= "+ rs.getRow());

      stmt.executeBatch();

      con.commit();

      System.out.println("Batch executed");

      rs = stmt.executeQuery("select * from emp");

      rs.last();

      System.out.println("rows after batch execution=  + rs.getRow());

   }

} 

Use row methods

import java.sql.*;

public class jdbcConn 

{

   public static void main(String[] args) throws Exception

  {

      Class.forName("org.apache.derby.jdbc.ClientDriver");

      Connection con = DriverManager.getConnection

      ("jdbc:derby://localhost:1527/testDb","name","pass");

      Statement stmt = con.createStatement

      (ResultSet.type_scroll_sensitive, 

      ResultSet.concur_updatable);

      String query = "select * from emp";

      ResultSet rs = stmt.executeQuery(query);

      rs.last();

      System.out.println("No of rows in table="+rs.getRow());

      rs.moveToInsertRow();

      rs.updateInt("id", 9);

      rs.updateString("name","sujay");

      rs.updateString("job", "trainee");

      rs.insertRow();

      System.out.println("Row added");

      rs.first();

      rs.deleteRow();

      System.out.println("first row deleted");

   }

}