Question

what is the difference between statement ,Prepared Statement and callable statement .

In what situations do we use these three? in what situations they are preferred . Also please write the syntax of connection the database. Suppose i have database:url and datasource named C://databasesourceurl databasename and password:password@123

please tell how to execute statement and how the resultset statement is written .Also if i have to show/display all the records till the end of the database we will use result rs.next() methor result.hasnext()

please tell in what situations do we need to use rs.next() and rs.hasnext()

please tell in easy words without copy pasting . please write answer in your own words because i am not understanding.

Language is Core java .

Answer 1

statement:
Suppose in you java program,you want to run a particular sql query like "CREATE TABLE EMPLOYEE(ID NUMBER NOT NULL, NAME VARCHAR)" ,so this query is not going to change dynamically as its very constnat definiton for table creation,you can use like a statement

Below is sample code:

package com.test.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Test{

public static void main(String a[]){

Connection con = null;
PreparedStatement prSt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.
getConnection("jdbc:oracle:thin:@<hostname>:<port num>:<DB name>"
,"user","password");
Statement stmt = con.createStatement();
stmt.executeUpdate("CREATE TABLE EMPLOYEE(ID NUMBER NOT NULL, NAME VARCHAR)");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
try{
if( stmt!= null) stmt.close();
if(con != null) con.close();
} catch(Exception ex){}
}
}
}

Prepared Statement:
Now you have the table created and wanted to insert some data inside that employee table,so one way is like you can use statement
i.e insert into employee values(1,"A");
but in above the above statement is constant and consider a scenario where you want to populate data dynamically i.e get the value from
some login page and poulate in table in that case you cant simply hardcode it,you may need to use prepared statement like below:

String insertTableSQL = "INSERT INTO EMPLOYEE"
       + VALUES"
       + "(?,?)";
PreparedStatement preparedStatement = dbConnection.prepareStatement(insertTableSQL);
//this corresponds to emp_id column
preparedStatement.setInt(1, 2);
//this corresponds to name column
preparedStatement.setString(2, "B");
// execute insert SQL stetement
preparedStatement .executeUpdate();

CallableStatement:
This is totally used for stored procedure,CallableStatement is used to execute the stored procedures. CallableStatement extends PreparedStatement.
Below is the code:

package com.test.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Test{

public static void main(String a[]){

Connection con = null;
PreparedStatement prSt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.
getConnection("jdbc:oracle:thin:@<hostname>:<port num>:<DB name>"
,"user","password");
//Emp is a stored procedure here
String sql = "{call Emp(?, ?)}";
stmt = conn.prepareCall(sql);
  
//Bind IN parameter first, 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 method 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 (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
try{
if(stmt != null) stmt.close();
if(con != null) con.close();
} catch(Exception ex){}
}
}
}

rs.next() example:

package com.test.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Test{

public static void main(String a[]){

Connection con = null;
PreparedStatement prSt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.
getConnection("jdbc:oracle:thin:@<hostname>:<port num>:<DB name>"
,"user","password");
Statement stmt = connection.createStatement();
String selectquery = "select name from employee";
ResultSet rs = stmt.executeQuery(selectquery);
//using rs.next to ietrate over all records
while(rs.next()){
System.out.println("Emp Name :" + rs.getString("name"));
}
}
stmt.close();
conn.close();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally{
try{
if(stmt != null) stmt.close();
if(con != null) con.close();
} catch(Exception ex){}
}
}
}

hasnext() is used with iterators over collections,not specific to resultset.

You can see supported method by Resultset in below link:

https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html

Below is example of iterator:

import java.io.*;

import java.util.*;

  

class Test {

    public static void main(String[] args)

    {

        ArrayList<String> list = new ArrayList<String>();

  

        list.add("A");

        list.add("B");

        list.add("C");

        list.add("D");

        list.add("E");

  

        // Iterator to traverse the list

        Iterator iterator = list.iterator();

  

        System.out.println("List elements : ");

//checking if iterator has any more elements then see the content

//if you find any element then only fetch it using hasnext

        while (iterator.hasNext()){

            System.out.print(iterator.next() + " ");

        System.out.println();}

    }

}

Hope it helps.

Thanks