Answer:
In this example we perform the insert, update and delete operation on a table by using java program. Although the target database system is Oracle, but the same technique can be applied for other database systems as well because the query syntax used is standard SQL which is supported by all relational database systems.
Following are the steps to create this application.
1. Create “Emp” table
Create table emp (emp_id number, empname varchar2(10), email varchar2(30), city varchar2(10));
2. Develop the Java application
EmployeeRecord.java
import java.sql.*;
class EmployeeRecord
{
public static final String DBURL = "jdbc:oracle:thin:@localhost:1521:XE";
public static final String DBUSER = "local";
public static final String DBPASS = "test";
public static void main(String args[])
{
try
{
//Loading the driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//Cretae the connection object
Connection con = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
//Insert the record
String sql = "INSERT INTO emp (emp_id, empname, email, city) VALUES (?, ?, ?, ?)";
PreparedStatement statement = con.prepareStatement(sql);
statement.setInt(1, 100);
statement.setString(2, "Prashant");
statement.setString(3, "prasant@saxena.com");
statement.setString(4, "Pune");
int rowsInserted = statement.executeUpdate();
if (rowsInserted > 0)
{
System.out.println("A new employee was inserted successfully!\n");
}
// Display the record
String sql1 = "SELECT * FROM Emp";
Statement stmt = con.createStatement();
ResultSet result = stmt.executeQuery(sql1);
while (result.next())
{
System.out.println (result.getInt(1)+" "+
result.getString(2)+" "+
result.getString(3)+" "+
result.getString(4));
}
//Update the record
String sql2 = "Update Emp set email = ? where empname = ?";
PreparedStatement pstmt = con.prepareStatement(sql2);
pstmt.setString(1, "Jaya@gmail.com");
pstmt.setString(2, "Jaya");
int rowUpdate = pstmt.executeUpdate();
if (rowUpdate > 0)
{
System.out.println("\nRecord updated successfully!!\n");
}
//Delete the record
String sql3 = "DELETE FROM Emp WHERE empname=?";
PreparedStatement statement1 = con.prepareStatement(sql3);
statement1.setString(1, "Prashant");
int rowsDeleted = statement1.executeUpdate();
if (rowsDeleted > 0)
{
System.out.println("A Employee was deleted successfully!\n");
}
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
}
Output:
