Statement and CallableStatement object instances in Java JDBC _java

JDBC Statement Object instance
Here are examples of the following three types of queries and the opening and closing instructions:

Boolean execute (String SQL): Returns a Boolean value of true, or False if the ResultSet object can be retrieved. Use this method to execute SQL DDL statements, or when you need to use real dynamic SQL.

int executeupdate (String SQL): Returns the number of rows executed by the affected SQL statement. Use this method to execute an SQL statement that wants to get some of the affected rows-for example, a insert,update or DELETE statement.

ResultSet executequery (String SQL): Returns the ResultSet object. When you want to get a result set using this method, it's like using a SELECT statement.

This sample code has been written based on the installation of the environment and the database in the previous section.

Copy Jdbcexample.java in the following example, compile and run as follows:

Step 1.

Import required packages import java.sql.*; public class Jdbcexample {//JDBC driver name and database URL static final String jdbc_driver = "Com.mysql.jdbc.Driv 
  ER ";

  Static final String Db_url = "Jdbc:mysql://localhost/emp";
  The Database credentials static final String USER = "username";
  
  Static final String pass = "password";
  public static void Main (string[] args) {Connection conn = null;
  Statement stmt = null;

   try{//step 2:register JDBC driver Class.forName ("Com.mysql.jdbc.Driver");
   Step 3:open A connection System.out.println ("Connecting to Database ...");

   conn = Drivermanager.getconnection (Db_url,user,pass);
   Step 4:execute a query System.out.println ("creating statement ...");
   stmt = Conn.createstatement ();
   
   String sql = "UPDATE Employees set age=30 WHERE id=103";
   Let us check if it returns a true result Set or not.
   Boolean ret = stmt.execute (SQL); System.out.println ("Return value is:" + ret.tostRing ());
   Let us update is the record with ID = 103;
   int rows = stmt.executeupdate (SQL);

   SYSTEM.OUT.PRINTLN ("Rows impacted:" + rows);
   Let us select the records and display them.
   sql = "SELECT ID, Employees";

   ResultSet rs = stmt.executequery (SQL);
     Step 5:extract The data from the result set while (Rs.next ()) {//retrieve by column name int id = rs.getint ("id");
     int age = Rs.getint (' age ');
     String a = rs.getstring ("a");

     String last = rs.getstring ("last");
     Display values System.out.print ("ID:" + ID);
     System.out.print (", Age:" + age);
     System.out.print (", A:" + a);
   System.out.println (", Last:" + last);
   }//step 6:clean-up Environment Rs.close ();
   Stmt.close ();
  Conn.close ();
  }catch (SQLException se) {//handle errors for JDBC se.printstacktrace ();
  }catch (Exception e) {//handle errors for class.forname e.printstacktrace ();
}finally{   Finally blocks used to the try{if (stmt!=null) stmt.close ();
   }catch (SQLException se2) {}//Nothing we can do try{if (conn!=null) conn.close ();
   }catch (SQLException se) {se.printstacktrace ();
}//end finally try}//end try System.out.println ("goodbye!");

 }//end Main}//end Jdbcexample

Now compile the above example as follows:

C:>javac Jdbcexample.java

When you run Jdbcexample, it produces the following results:

C:>java Jdbcexample

Connecting to Database ...
Creating Statement
... return value Is:false
Rows impacted:1
id:100, age:18, First:zara, Last:ali id:101, age:25, First:ma
Hnaz, Last:fatma
id:102, age:30, First:zaid, Last:khan id:103,
age:30, First:sumit, Last:mittal
go odbye!

JDBC CallableStatement Object instance
The following are examples of MySQL stored procedures that take advantage of CallableStatement together with the following Getempname ():

Make sure that the stored procedure is already created in the EMP database. You can use the MySQL query browser to complete it.

DELIMITER $$

DROP PROCEDURE IF EXISTS ' emp '. ' Getempname ' $$
CREATE PROCEDURE ' emp '. ' Getempname ' 
  (in emp_id INT, out Emp_first VARCHAR (255))
BEGIN
  SELECT in Emp_first from
  Employees
  WHERE ID = emp_id;
End $$

DELIMITER;

This example code has been written based on the installation of the environment and the database in the previous section.

Copy Jdbcexample.java in the following example, compile and run as follows:

Step 1.

Import required packages import java.sql.*; public class Jdbcexample {//JDBC driver name and database URL static final String jdbc_driver = "Com.mysql.jdbc.Driv 
  ER ";

  Static final String Db_url = "Jdbc:mysql://localhost/emp";
  The Database credentials static final String USER = "username";
  
  Static final String pass = "password";
  public static void Main (string[] args) {Connection conn = null;
  CallableStatement stmt = null;

   try{//step 2:register JDBC driver Class.forName ("Com.mysql.jdbc.Driver");
   Step 3:open A connection System.out.println ("Connecting to Database ...");

   conn = Drivermanager.getconnection (Db_url,user,pass);
   Step 4:execute a query System.out.println ("creating statement ...");
   String sql = "{call Getempname (?,?)}";
   
   stmt = Conn.preparecall (sql);
   Bind in parameter-then bind out parameter int empID = 102; Stmt.setint (1, EmpID); This would set ID as 102//because second ParameteR is out so register it stmt.registeroutparameter (2, Java.sql.Types.VARCHAR);
   Use execute to run stored procedure.
   SYSTEM.OUT.PRINTLN ("Executing stored procedure ...");

   Stmt.execute ();
   Retrieve Employee Name with getxxx method String EmpName = stmt.getstring (2);
   System.out.println ("Emp Name with ID:" + EmpID + "is" + empname);
   Stmt.close ();
  Conn.close ();
  }catch (SQLException se) {//handle errors for JDBC se.printstacktrace ();
  }catch (Exception e) {//handle errors for class.forname e.printstacktrace ();
   }finally{//finally block used to close resources try{if (stmt!=null) stmt.close ();
   }catch (SQLException se2) {}//Nothing we can do try{if (conn!=null) conn.close ();
   }catch (SQLException se) {se.printstacktrace ();
}//end finally try}//end try System.out.println ("goodbye!");

 }//end Main}//end Jdbcexample

Now compile the above example as follows:

C:>javac Jdbcexample.java

When you run Jdbcexample, it produces the following results:

C:>java Jdbcexample

Connecting to Database ...
Creating Statement
... Executing stored procedure
... EMP Name with id:102 is Zaid
goodbye!