THREE DIFFERENCE TYPES OF JDBC SQL STATEMENTS

If you have any queries please leave a message here
Your Message
×


java.sql.Statement

It can be used for general-purpose access to the database. It is useful when you are using static SQL statements at runtime.

try
{
. . . . .//Database Connection Code
Statement stmt = con.createStatement( );
. . . . .
}
catch (SQLException e)
{
. . . . .
}
finally
{
stmt.close();
}



Example
Write a program to update salary of a particular employee

//UpdateEmployeeSalary.java
import java.sql.*;
import oracle.jdbc.driver.*;
public class UpdateEmployeeSalary
{
public void updateOperation(String id,double salary)
{
try
{
String url="jdbc:oracle:thin:@localhost:1521:xe";
String user="system";
String password="12345";
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection(url,user,password);
String SQL="update employee set salary="+salary+" where empid='"+id+"'";
Statement stmt=con.createStatement();
stmt.executeUpdate(SQL);
con.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}



java.sql.PreparedStatement

It can be used when you plan to use the same SQL statement many times. The PreparedStatement interface accepts input parameters at runtime.

try
{
//Database Connection Code
. . . . .
String SQL = "Update Employees SET salary = ? WHERE empid = ?";
PreparedStatement pstmt = conn.prepareStatement(SQL);
. . . . .
}
catch (SQLException e)
{
. . . . .
}
finally
{
pstmt.close();
}



Example
Write a program to update salary of a particular employee

//UpdateEmployeeSalary.java
import java.sql.*;
import oracle.jdbc.driver.*;
public class UpdateEmployeeSalary
{
public void updateOperation(String id,double salary)
{
try
{
String url="jdbc:oracle:thin:@localhost:1521:xe";
String user="system";
String password="12345";
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection(url,user,password);
String SQL="update employee set salary = ? where empid = ?";
PreparedStatement pstmt = con.prepareStatement(SQL);
//Specifies the first parameter in the query
pstmt.setDouble(1,salary);
//Specifies the second parameter in the query
pstmt.setString(2,id);
pstmt.executeUpdate();
con.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}



java.sql.CallableStatement

CallableStatement can be used when you want to access database stored procedures.

Procedure
CREATE OR REPLACE PROCEDURE salaryIncrement
(id IN VARCHAR, increment IN NUMBER) AS
salary NUMBER(8,2);
BEGIN
SELECT salary INTO salary FROM employee WHERE empid=id;
UPDATE employee SET salary=salary+increment WHERE empid=id;
END;
/
Procedure Call
try
{
//Database Connection Code
. . . . .
String SQL = "{call salaryIncrement (?, ?)}";
CallableStatement cstmt = conn.prepareCall (SQL);
. . . . .
}
catch (SQLException e)
{
. . . . .
}
finally
{
cstmt.close();
}



Example
Write a program to update salary of a particular employee

//UpdateEmployeeSalary.java
import java.sql.*;
import oracle.jdbc.driver.*;
public class UpdateEmployeeSalary
{
public void updateOperation(String id,double amount)
{
try
{
String url="jdbc:oracle:thin:@localhost:1521:xe";
String user="system";
String password="12345";
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection(url,user,password);
String SQL="{call salaryIncrement(?, ?)}";
CallableStatement cstmt=con.prepareCall(SQL);
//Specifies the first parameter in the query
cstmt.setString(1,id);
//Specifies the second parameter in the query
cstmt.setDouble(2,amount);
cstmt.execute();
con.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
}


ABOUT US

QuestionSolves.com is an educational website that helps worldwide students in solving computer education related queries.

Also, different software like Visual Studio, SQL Server, Oracle etc. are available to download in different versions.

Moreover, QuestionSolves.com provides solutions to your questions and assignments also.


MORE TOPIC


Windows Command

UNIX Command

IGNOU Assignment Solution

IGNOU Question Paper Solution

Solutions of Different Questions


WHAT WE DO


Website Devlopment

Training

Home Learning

Provide BCA, MCA Projects

Provide Assignment & Question Paper Solution


CONTACT US


Follow Us