Archive

Archive for the ‘JDBC’ Category

Difference between Statement and PreparedStatement in JDBC with examples

October 13, 2011 Leave a comment

The Prepared Statement is a slightly more powerful version of a Statement and it will take less time to execute when compared with Statement.
Because

* In Statement the SQL query will be compiled and executed every time.

* In PreparedStatement at the First time RDBMS comiple the SQL and PreparedStatement is executed then from the next time it doesn’t compile the SQL only execute the SQL within the connection live.

Here I have given the example using the statement and preparedstatement to insert customer details into database.

  
1. Queries used to create the customer table.

CREATE TABLE SAM_CUSTOMER(CUST_ID NUMBER PRIMARY KEY,
                          CUST_NAME VARCHAR2(25) NOT NULL,
                          CUST_ADDRESS VARCHAR2(45),
                          CUST_MOBILE NUMBER(10),
                          CUST_MAILID VARCHAR2(30)
                          );

-- The unique sequence id will be inserted while inserting record into table. For that we have created sequence.

CREATE SEQUENCE CUST_ID_SEQ START WITH 100 INCREMENT BY 1 NOCACHE NOCYCLE;

 
 
2. Insert Customer using JDBC Statement.

package com.room.sample.jdbc;

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

public class StatementTest {


	public void insertCustomer(Customer customer){
		Connection connection=null;
		Statement stmt=null;
		try {
			connection=getConnection();
			final String query="INSERT INTO SAM_CUSTOMER VALUES(CUST_ID_SEQ.nextval,"+"'"+customer.getName()+"','"+customer.getAddress()+"',"+customer.getMobile()+",'"+customer.getEmailid()+"')";
			
			long startTime=System.currentTimeMillis();
			stmt=connection.createStatement();
			int status=stmt.executeUpdate(query);
			System.out.println("Statement Execution Response :"+status);
			
			long timtaken=System.currentTimeMillis() - startTime;
			System.out.println("Time Taken for executing Statement :"+timtaken+" milli seconds");
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private Connection getConnection() throws Exception{
		Connection conn=null;
		 try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			 conn=DriverManager.getConnection("jdbc:oracle:thin:@SABA:1521:XE", "system", "password");
			 System.out.println("--- java.sql.Connection created ----");
		} catch (ClassNotFoundException e){
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		}
		return conn;
	}
	
	public static void main(String[] args) {
		Customer cust=new Customer();
		cust.setName("Sabapathy");
		cust.setAddress("NorthWales-AU");
		cust.setMobile(Long.valueOf("1234567891"));
		cust.setEmailid("test@hotmail.com");
		
		StatementTest test=new StatementTest();
		//executing Statement method
		test.insertCustomer(cust);
	}
}

 
 
3. Insert customer using JDBC PreparedStatement.

package com.room.sample.jdbc;

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

public class PreparedStatementTest {

	public void insertCustomer(Customer customer){
		Connection connection=null;
		PreparedStatement prepareStmt=null;
		try {
			connection=getConnection();
			final String query="INSERT INTO SAM_CUSTOMER(CUST_ID,CUST_NAME,CUST_ADDRESS,CUST_MOBILE,CUST_MAILID) VALUES (CUST_ID_SEQ.nextval,?,?,?,?)";
			long startTime=System.currentTimeMillis();
			
			prepareStmt=connection.prepareStatement(query);
			prepareStmt.setString(1, customer.getName());
			prepareStmt.setString(2, customer.getAddress());
			prepareStmt.setLong(3, customer.getMobile());
			prepareStmt.setString(4, customer.getEmailid());
			
			int status=prepareStmt.executeUpdate();
			System.out.println("PreparedStatement Execution Response :"+status);
			long timtaken=System.currentTimeMillis() - startTime;
			
			System.out.println("Time Taken for executing PreparedStatement :"+timtaken +" milli seconds");
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private Connection getConnection() throws Exception{
		Connection conn=null;
		 try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			 conn=DriverManager.getConnection("jdbc:oracle:thin:@SABA:1521:XE", "system", "password");
			 System.out.println("--- java.sql.Connection created ----");
		} catch (ClassNotFoundException e){
			e.printStackTrace();
			throw new Exception(e.getMessage());
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());
		}
		return conn;
	}
	
	
	public static void main(String[] args) {
		Customer cust=new Customer();
		cust.setName("Rathina");
		cust.setAddress("New Delhi");
		cust.setMobile(Long.valueOf("2233445566"));
		cust.setEmailid("test@rediffmail.com");
		
		PreparedStatementTest test=new PreparedStatementTest();
		//Executing Prepared Statement method
		test.insertCustomer(cust);

	}
}

Advertisements