Enterprise Programming
CO-1: Java Database Connectivity
Java Database Connectivity
- Introduction
- Why JDBC
- JDBC Drivers
- JDBC Steps
- JDBC Classes
- JDBC Interfaces
- JDBC Examples
- JDBC CRUD
Operations Introduction:
- JDBC stands for Java Database Connectivity
- Java Database Connectivity (JDBC) is an API (Application
Programming Interface) that allows Java applications to interact with
databases.
Note:
API is a package which consists of many classes and interfaces that helps
for Java Database Connectivity
Package -> java.util
Why JDBC:
- JDBC is a fundamental tool for Java developers to interact with databases
- The reason why JDBC is widely used are
1. Platform Independent
Allows develops to write database applications that can run on
any platform
2. Versatility
JDBC supports various relational databases like MySQL, Oracle,
PostgreSQL etc
Note:
Java Application -------------------------- Database
(ODBC Drivers)
Java Application ------------------------- Database
(JDBC Drivers)
Use-cases for JDBC:
1. Web Applications
2. Enterprise Application
3. Desktop Applications
etc JDBC Drivers:
- JDBC drivers are critical components that enable Java applications
to communicate with databases.
- Types of JDBC Drivers:
1. Type-1 Driver
-Type-1 Driver is also known as JDBC-ODBC Bridge Driver
- Useful for connecting to databases where only ODBC drivers are
available
2. Type-2 Driver
-Type-2 Driver is also known as Native-API Driver
-Suitable for applications where performance is critical
3. Type-3 Driver
-Type-3 Driver is also known as Network Protocol Driver
-Useful in network environment where client doesn’t need to
understand database details
4. Type-4 Driver
-Type-4 is also known as thin deriver
-Commonly used in modern applications due the following reason
-Performance
-Efficiency
-Platform Independent
-Ease of use
JDBC Steps:
There are 7 steps to connect JAVA APPLICATION with DATABASE using JDBC
DRIVERS
Step-1:
import packages
Step-2:
Load
Drivers Step-3:
Connection Establishment
Step-4:
Create Statement
Step-5:
Create ResultSet
Object Step-6:
Execute Queries
Step-7:
Close the connection
Example:
Step-1: Import package
import java.sql.*;
Step-2: Load Drivers (2 ways)
- Driver Object
- forName() method
Driver Object:
Driver drv = new classdrivers-path
DriverManager.registerDriver(drv)
forName() method:
Class.forName("classdrivers-path")
Step-3: Connection Establishment
Connection conn = DriverManager.getConnection("database-connection-
path","username","password")
Step-4: Create a Statement
Statement stmt = conn.createStatement()
Step-5: Create ResultSet Object
ResultSet rs = stmt.executeQuery("only select")
Step-6: Execute Queries
stmt.executeUpdate("Any query other than
select") Sep-7: Close the connection conn.close()
Note:
Connectivity with Different Databases:
1. Oracle
Driver class: oracle.jdbc.driver.OracleDriver
Connection: jdbc:oracle:thin:@localhost:1521:xe
username: system
pwd: admin (which you will create at the time of installation)
2. MySQL
Driver class: com.mysql.jdbc.Driver
Connection: jdbc:mysql://localhost:3306/db
username: root
pwd: admin (which you will create at the time of installation)
Example-1: DB Connectivity (Driver Object) (oracle)
package com.klef;
import java.sql.*;
public class DBConnec {
public static void main(String[] args) throws Exception{
Driver drv = new oracle.jdbc.driver.OracleDriver();
DriverManager.registerDriver(drv);
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system",
"admin");
if(con!=null) {
System.out.print("Connection Success");
else {
System.out.print("Connection UnSuccess");
con.close();
Example-2: DB Connectivity (Driver Object) (mysql)
package com.klef;
import java.sql.*;
public class DBConnec {
public static void main(String[] args) throws Exception{
Driver drv = new com.mysql.cj.jdbc.Driver();
DriverManager.registerDriver(drv);
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/db","root","admin
");
if(con!=null) {
System.out.print("Connection Success");
else {
System.out.print("Connection UnSuccess");
con.close();
Example-3: DB Connectivity (forName method)(oracle)
package com.klef;
import java.sql.*;
public class DBConnec {
public static void main(String[] args) throws Exception{
Class.forName(“oracle.jdbc.Driver.OracleDriver”);
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system",
"admin");
if(con!=null) {
System.out.print("Connection Success");
else {
System.out.print("Connection UnSuccess");
con.close();
Example-4: DB Connectivity (forName method) (mysql)
package com.klef;
import java.sql.*;
public class DBConnec {
public static void main(String[] args) throws Exception{
Class.forName(“com.mysql.cj.jdbc.Driver”);
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/db","root","admin
");
if(con!=null) {
System.out.print("Connection Success");
else {
System.out.print("Connection UnSuccess");
con.close();
JDBC Classes:
1. DriverManager class:
- It is a class which acts an interface between user
and database. Methods:
a. registerDriver()
- to load the drivers
b. deregisterDriver()
- to remove all the drivers which are already loaded
c. getConnection()
- to create a connection between Java application and
database
Ex:
a)
Driver
drv=oracle.jdbc.driver.OracleDriver;
b)
Drivermanager.registerDriver(drv);
c)
Drivermanager.deregisterDriver();
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:
xe","sy stem","admin");
Connection con=
DriverManager.getConnection("jdbc:mysql://localhost:3306/db");
Example-5: To demonstrate DriverManager class
Refer Ex-1 to Ex-4 which demonstrates
DriverManager
class
2. Blob:
- Blob is used to store or retrieve image from a
database. store:
class -> FileInputStream
method -> setBinaryStream()
retrieve:
class -> FileOutputStream
method -> getBytes()
Example-6: To demonstrate Blob class (Store Image)
(Oracle
Database) c
(MySQL
Database)
import
java.io.*;
import
java.sql.*;
public class StoreImage {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/klu",
"root", "admin");
Statement stmt = con.createStatement();
stmt.execute("create table imagetab(id
INTEGER(5), name
VARCHAR(20),image BLOB)");
PreparedStatement ps = con.prepareStatement("insert
into imagetab
values(?, ?, ?)");
ps.setInt(1, 111);
ps.setString(2, "ABC");
FileInputStream fis =
new
FileInputStream("C:\\Users\\ashesh\\OneDrive\\Desktop\\
abc.png"); ps.setBinaryStream(3, fis, fis.available());
ps.execute();
System.out.println("Table Created and Image Inserted");
con.close();
}
}
Example-7: To demonstrate Blob class (Retrieve
Image) import java.io.*;
import java.sql.*;
public class RetImage {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:
xe", "system", "admin");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from
imagetab"); if (rs.next()) {
int id = rs.getInt(1);
String name =
rs.getString(2); Blob b =
rs.getBlob(3);
byte[] imageData = b.getBytes(1, (int) b.length());
FileOutputStream fos = new
FileOutputStream("C:\\Users\\ashesh\\OneDrive\\Desktop\\
ret_image.jpe g");
fos.write(imageDat
a); fos.close();
System.out.println("Image retrieved from database with
ID: " + id
+ ", Name: " + name);
}
else {
System.out.println("No image found in the database.");
}
con.close();
}
}
(MySQL
Database)
import
java.io.*;
import
java.sql.*;
public class RetImage {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Drive
r"); Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/klu",
"root", "admin");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from
imagetab"); if (rs.next()) {
int id = rs.getInt(1);
String name =
rs.getString(2); Blob b =
rs.getBlob(3);
byte imageData[ ] = b.getBytes(1, (int) b.length());
FileOutputStream fos = new
FileOutputStream("C:\\Users\\ashesh\\OneDrive\\Desktop\\
ret_image.jpe g");
fos.write(imageDat
a); fos.close();
System.out.println("Image retrieved from database with
ID: " + id
+ ", Name: " + name);
}
else {
System.out.println("No image found in the database.");
}
con.close();
}
}
3. Clob:
- Clob is used to store or retrieve a file from
database. store:
class -> FileReader
method ->
setCharacterStream() retrieve:
class -> FileWriter
method -> getCharacterStream()
Example-8: To demonstrate Clob class (Store File)
(Oracle Database)
import
java.io.*;
import
java.sql.*;
public class StoreFile {
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:
xe", "system", "admin");
Statement stmt = con.createStatement();
stmt.execute("create table filetab(id NUMBER(5),
name
VARCHAR(20),file CLOB)");
PreparedStatement ps = con.prepareStatement("insert into
filetab values(?, ?, ?)");
ps.setInt(1, 111);
ps.setString(2,
"ABC");
File f = new File("C:\\Users\\ashesh\\OneDrive\\Desktop\\
abc.txt"); FileReader fr = new FileReader(f);
ps.setCharacterStream(3, fr, (int)
f.length()); ps.execute();
System.out.println("Table Created and File Inserted");
con.close();
}
}
(MySQL
Database)
import
java.io.*;
import java.sql.*;
public class
StoreFile {
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Drive
r"); Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/klu",
"root", "admin");
Statement stmt = con.createStatement();
stmt.execute("create table filetab(id INTEGER(5),
name
VARCHAR(20), file CLOB)");
PreparedStatement ps = con.prepareStatement("insert into
filetab values(?, ?, ?)");
ps.setInt(1, 111);
ps.setString(2,
"ABC");
File f = new File("C:\\Users\\ashesh\\OneDrive\\Desktop\\
abc.txt"); FileReader fr = new FileReader(f);
ps.setCharacterStream(3, fr, (int)
f.length()); ps.execute();
System.out.println("Table Created and File Inserted");
con.close();
}
}
Example-9: To demonstrate Clob class (Retrieve
File) import java.io.*;
import java.sql.*;
public class RetFile
{
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:
xe", "system", "admin");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from
filetab"); if (rs.next()) {
int id = rs.getInt(1);
String name =
rs.getString(2); Clob c =
rs.getClob(3);
Char content[ ] = c.getCharacterStream(1, (int)
c.length());
FileWriter fw = new FileWriter("C:\\Users\\ashesh\\
OneDrive\\Desktop\\output.txt");
fw.write(content);
fw.close ();
System.out.println("File content retrieved and saved
successfully.");
}
else {
System.out.println("No data found");
}
con.close();
}
}
(MySQL Database)
import java.io.*;
import java.sql.*;
public class RetFile
{
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/klu",
"root", "admin");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from
imagetab"); if (rs.next()) {
int id = rs.getInt(1);
String name =
rs.getString(2); Clob c =
rs.getClob(3);
Char content[ ] = c.getCharacterStream(1, (int)
c.length());
FileWriter fw = new FileWriter("C:\\Users\\ashesh\\
OneDrive\\Desktop\\output.txt");
fw.write(content);
fw.close ();
System.out.println("File content retrieved and saved
successfully.");
}
else {
System.out.println("No data found");
}
con.close();
}
}
JDBC Interfaces:
1. Driver
- It is an interface which helps us to define the drivers
which you want to
load.
Ex:
Driver drv = oracle.jdbc.driver.OracleDriver (Oracle
Database)
= new com.mysql.jdbc.Driver (MySQL
Database)
Example-10: To demonstrate Driver interface
Refer to Ex-1 and Ex-2 which demonstrates
Driver
Interface
2. Connection
- It acts as a session between java application and
database. Methods:
a. createStatement()
b. setAutoCommit()
c. commit()
d. rollback()
e. close()
Ex:
a) Statement stmt = con. createStatement()
b) stmt.setAUtoCommit()
c) stmt.commit()
d) stmt.rollbac
k()
e)con.close()
Example-11: To demonstrate Connection Interface
Refer to Ex-1 to Ex-4 which demonstrates
Connection
Interface
3. Statement
- This interface provides methods to execute queries with
database Methods:
a. executeQuery()
b. executeUpdate() /
execute() Ex:
a) ResultSet rs = stmt.executeQuery("select query")
b) stmt.executeUpdate("other than select")
Example-12: To demonstrate Statement
Interface (Table Creation)
(Oracle Database)
package com.klef;
import java.sql.*;
public class TableCreation {
public static void main(String[] args)throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:
xe","sy stem","admin");
Statement stmt = con.createStatement();
stmt.execute("create table employee(eid
number(5), ename
varchar(20), esal number(10))");
System.out.print("Table Created");
con.close();
}
}
(MySQL Database)
package com.klef;
import java.sql.*;
public class TableCreation {
public static void main(String[] args)throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver"
); Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/
klu","root","a dmin");
Statement stmt = con.createStatement();
stmt.execute("create table employee(eid integer(5),
ename
varchar(20), esal integer(10))");
System.out.print("Table Created");
con.close();
}
}
Example-13: To demonstrate Statement Interface
(Insert)
(Oracle Database)
package
com.klef; import
java.sql.*; public
class Insert {
public static void main(String[] args)throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:
xe","sy stem","admin");
Statement stmt = con.createStatement();
stmt.execute("insert into employee
values(111,”ABC”,25000)");
System.out.print("Inserte
d"); con.close();
}
}
(MySQL Database)
package com.klef;
import java.sql.*;
public class Insert {
public static void main(String[] args)throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver"
); Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/
klu","root","a dmin");
Statement stmt = con.createStatement();
stmt.execute("insert into employee
values(111,”ABC”,25000)");
System.out.print("Inserte
d"); con.close();
}
}
4. PreparedStatement
- It is a sub interface of statement
interface Methods:
a. setInt()
b. setString()
c. setFloat()
d. setDouble()
e. executeQuery()
f. executeUpdate()
Ex:
PreparedStatement ps = conn.prepareStatement("query(?,
?, ?,
?)");
ps.setInt(1,123);
ps.setString(2,"abc")
;
ps.setFloat(3,8.9);
ps.setDouble(4,1000
0);
Example-14: To demonstrate PreapredStatement
Interface (Insert)
(Oracle Database)
package com.klef;
import java.sql.*;
public class
InsertPS {
public static void main(String[] args)throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:
xe","sy stem","admin");
PreparedStatement ps = con.prepareStatement("insert
into
employee
values(?, ?, ?)");
ps.setInt(1, 222);
ps.setString(2, "DEF");
ps.setDouble(3, 50000);
ps.execute();
System.out.print("Inserte
d"); con.close();
}
}
(MySQL Database)
package com.klef;
import java.sql.*;
public class
InsertPS {
public static void main(String[] args)throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver"
); Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/
klu","root","a dmin");
PreparedStatement ps = con.prepareStatement("insert
into
employee
values(?, ?, ?)");
ps.setInt(1, 222);
ps.setString(2, "DEF");
ps.setDouble(3, 50000);
ps.execute();
System.out.print("Inserte
d"); con.close();
}
}
5. ResultSet
- It is an interface provided by Java to retrieve data from a
database after
executing a query.
- It represents a set of rows returned by a database
query and maintains
a cursor pointing to its current row of data.
Methods
a. next()
b. previous()
c. first()
d. last()
e. getInt()
f. getString()
g. getDouble()
Ex:
ResultSet rs = stmt.executeQuery("select sid from
student") while(rs.next()){
}
Example-15: To demonstrate ResultSet Interface
(Retriev
e) (Oracle Database)
package com.klef;
import java.sql.*;
public class
Retrieve {
public static void main(String[] args)throws
Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:
xe","sy stem","admin");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from
employee"); while(rs.next()) {
int id = rs.getInt(1);
String s =
rs.getString(2);
Double d = rs.getDouble(3);
System.out.println(id);
}
con.close();
}}
(MySQL Database)
package com.klef;
import java.sql.*;
public class
Retrieve {
public static void main(String[] args)throws
Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/klu","
root","a dmin");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from
employee"); while(rs.next()) {
int id = rs.getInt(1);
String s =
rs.getString(2);
Double d = rs.getDouble(3);
System.out.println(id);
}
con.close();
}
}
Note:
Example-16: Delete
(Oracle Database)
package
com.klef; import
java.sql.*; public
class Delete {
public static void main(String[] args)throws
Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:
xe","sy stem","admin");
Statement stmt = con.createStatement();
stmt.execute(“delete from employee where
eid=111”);
System.out.println(“Deleted”);
con.close();
}
}
(MySQL Database)
package com.klef;
import java.sql.*;
public class
Delete {
public static void main(String[] args)throws
Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/klu","
root","a dmin");
Statement stmt = con.createStatement();
stmt.execute(“delete from employee where
eid=111”);
System.out.println(“Deleted”);
con.close();
}
}
Example-17: Update
(Oracle Database)
package com.klef;
import java.sql.*;
public class Update
{
public static void main(String[] args)throws
Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:
xe","sy stem","admin");
Statement stmt = con.createStatement();
stmt.execute(“update employee SET ename=’XYZ’
where
eid=222”);
System.out.println(“Updated”);
con.close();
}
}
(MySQL Database)
package com.klef;
import java.sql.*;
public class Update
{
public static void main(String[] args)throws
Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/klu","
root","a dmin");
Statement stmt = con.createStatement();
stmt.execute(“update employee SET ename=’XYZ’
where
eid=222”);
System.out.println(“Updated”);
con.close();
}
}
Note: JBDC CRUD operations
Refer Ex-12 to Ex-17 which demonstrates JDBC CRUD
operations
6. ResultSetMetadata
- Metadata refers to data of a data i.e where we can
get further information from existing or available
data
Methods:
a. getColumnCount()
b. getColumnname()
c. getColumnTypeName()
d. gettableNam
e() Ex:
ResultSetMetaData rsmd = rs.getMetaData();
System.out.println(rsmd.getColumnCount());
System.out.println(rsmd.getColumnTypeName(2));
System.out.println(rsmd.getTableName());
Example-18: To demonstrate ResultSetMetaData
interface (Oracle Database)
package com.klef;
import java.sql.*;
public class Update
{
public static void main(String[] args)throws
Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:
xe","sy stem","admin");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from
employee");
ResultSetMetaData rsmd =
rs.getMetaData();
System.out.println(rsmd.getColumnCount());
System.out.println(rsmd.getColumnName(2)
);
System.out.println(rsmd.getColumnTypeNa
me(1)); con.close();
}
}
(MySQL Database)
package
com.klef; import
java.sql.*; public
class Delete {
public static void main(String[] args)throws
Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/klu","
root","a dmin");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from
employee");
ResultSetMetaData rsmd = rs.getMetaData();
System.out.println(rsmd.getColumnCount());
System.out.println(rsmd.getColumnName(2));
System.out.println(rsmd.getColumnTypeName(1));
con.close();
}
}
7. DatabaseMetaData
- It we will provide information about
database Methods:
a. getDriverName()
b. getDatabaseProductName()
c. getDatabaseProductVersion()
d. getDriverVersion()
Ex:
DatabaseMetaData dbmd = con.getMetaData();
System.out.println(dbmd.getDriverName());
System.out.println(dbmd.getDatabaseProductName());
System.out.println(dbmd.getDatabaseProductVersion());
Example-19: To demonstrate DatabaseMetaData
interface (Oracle Database)
package com.klef;
import java.sql.*;
public class Update
{
public static void main(String[] args)throws
Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:
xe","sy stem","admin");
DatabaseMetaData dbmd = con.getMetaData();
System.out.println(dbmd.getDriverName());
System.out.println(dbmd.getDriverVersion());
System.out.println(dbmd.getDatabaseProductName());
con.close();
}
}
(MySQL Database)
package
com.klef; import
java.sql.*; public
class Delete {
public static void main(String[] args)throws
Exception {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/klu","
root","a dmin");
DatabaseMetaData dbmd = con.getMetaData();
System.out.println(dbmd.getDriverName());
System.out.println(dbmd.getDriverVersion());
System.out.println(dbmd.getDatabaseProductName());
con.close();
}
}
8. Callable Interface
- Callable interfaces are used to call stored
procedures Methods:
a. prepareCall()
Syntax: Stored Procedure
CREATE or REPLACE PROCEDURE PROCEDURE_NAME
IS
BEGIN
QUERY;
END PROCEDURE_NAME;
Example: Stored Procedure (Insert Values)
(Oracle Database)
CREATE or REPLACE PROCEDURE INSERT_EMP
(eid IN NUMBER, ename IN VARCHAR, esal IN
NUMBER) IS
BEGIN
INSERT INTO employee VALUES(eid, ename,
esal); END INSERT_EMP;
(MySQL Database)
CREATE or REPLACE PROCEDURE INSERT_EMP
(eid IN INTEGER, ename IN VARCHAR, esal IN
INTEGER) IS
BEGIN
INSERT INTO employee VALUES(eid, ename,
esal); END INSERT_EMP;
Example-20: To demonstrate callable
interface (Oracle Database)
package
com.klef import
java.sql.*;
public class InsertCall {
public static void main(String[] args)throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con =
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:
xe","sy stem","admin");
Statement stmt = con.createStatement();
stmt.execute("CREATE or REPLACE PROCEDURE
INSERT_EMP"
+" (eid IN NUMBER, ename IN VARCHAR, esal
IN
NUMBER)
" +" IS"
+" BEGIN"
+" INSERT INTO employee VALUES
(eid,ename,esal);"
+" END INSERT_EMP;"
+ "");
CallableStatement csmt = con.prepareCall("{CALL
INSERT_EMP(?, ?, ?)}");
csmt.setInt(1, 333);
csmt.setString(2, "ABC");
csmt.setDouble(3, 75000);
csmt.execute();
System.out.print("Inserted");
con.close();
}
}
(MySQL Database)
package
com.klef import
java.sql.*;
public class InsertCall {
public static void main(String[] args)throws Exception {
Class.forName("com.mysql.cj.jdbc.Driver"
); Connection con =
DriverManager.getConnection("jdbc:mysql://localhost:3306/
klu","root","a dmin");
Statement stmt = con.createStatement();
stmt.execute("CREATE or REPLACE PROCEDURE
INSERT_EMP"
+" (eid IN INTEGER, ename IN VARCHAR, esal
IN
INTEGER)"
+" IS"
+" BEGIN"
+" INSERT INTO employee VALUES
(eid,ename,esal);"
+" END INSERT_EMP;"
+ "");
CallableStatement csmt = con.prepareCall("{CALL
INSERT_EMP(?, ?, ?)}");
csmt.setInt(1, 333);
csmt.setString(2, "ABC");
csmt.setDouble(3, 75000);
csmt.execute();
System.out.print("Inserted");
con.close();
}
}
JDBC CRUD Operations:
- Refer Ex-12 to Ex-17 for JDBC CRUD Operations
