In a multiuser environment same row/record may be accessed/updated concurrently
by more than one user.
This could cause data inconsistency and anomaly.
For instance,
If someone is accessing a record to see balance amount and it is under update
by another user. Instead of current balance, old balance would be retrieved,
called as dirty read.
There can be more serious situations which would adversely affect the
integrity of data.
To avoid such situations and maintain data integrity, locking concept is applied.
Locking as such is a wide concept and can be implemented in many ways.
It differs according to the DBMS, language and complexity of the project.
What is shown in this example program is simple method of locking.
It has its limitations but stable one to begin with.
One has to look into that particular DBMS to ascertain effectiveness
and limitations of such locks.
Here,
locking is done through the DML (Select,Update,Delete) statement.
DML statements with FOR UPDATE clause locks the record.
The row/record remain locked till the transaction is completed
(committed /rollback),then the lock is released and the row
is made available for the next transactions and so on.
Software used is NetBeans IDE 8.0.2.
Note : ClientId is unique and do not allow duplicates.
Database is created in JavaDB which is part of JDK package.
Database ClientDB and table ClientEmailList are created through the wizard.
Programs also created using Netbeans IDE 8.0.2.
Same code holds good for other DBMS with the corresponding driver program.
Database Name : ClientDB
Table Name : ClientContact
Table Attributes : ClientId N
ClientName A/N
ClientEmail A/N
No other checks were made.
Now the example.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class CRUDandLOCK {
String host = "jdbc:derby://localhost:1527/ClientDB";
String UserName = "parthiban";
String UserPassword = "password";
String sql;
Connection con;
ResultSet rs;
PreparedStatement ps;
Statement ss;
Scanner AcceptInput = new Scanner(System.in);
int WClientId;
// Work Variables
String WClientName, WClientEmail;
public static void main(String[] args) {
// Cmo is created to enable non static method calling from static Main
CRUDandLOCK Cmo = new CRUDandLOCK();
Scanner AcceptInput = new Scanner(System.in);
String OptionInput;
// ConnectMethod is called using Cmo instance
Cmo.ConnectMethod();
do {
System.out.println( " ** Select an Operation **");
System.out.println( " Creation of Record - 1");
System.out.println( " Retrieval of Record - 2");
System.out.println( " Update of Record - 3");
System.out.println( " Deletion of Record - 4");
System.out.println( " Exit - 0");
System.out.println( " ENTER OPTION - ");
OptionInput = AcceptInput.next();
// Based on user input switch control is used to call relevant method
switch (OptionInput) {
case "1":
Cmo.CreateRecord();
break;
case "2":
Cmo.RetrieveRecord();
break;
case "3":
Cmo.UpdateRecord();
break;
case "4":
Cmo.DeleteRecord();
break;
case "0":
// For input 0, program is terminated
System.out.println("Ending Session");
System.exit(0);
break;
default:
System.out.println("Invalid selection - Reenter");
break;
}
// end switch
// Note, how string value is checked for not = "0"
} while (!OptionInput.equals("0"));
}
// end main
/* CONNECTION METHOD */
public void ConnectMethod(){
try{
con = DriverManager.getConnection(host,UserName,UserPassword);
System.out.println(" Connected..");
}
catch (SQLException err){
System.out.println("ERROR:"+err.getMessage());
}
}
/* RECORD CREATION METHOD */
public void CreateRecord(){
System.out.println("RECORD CREATION");
Scanner Accept = new Scanner(System.in);
int WclientId = 1;
try {
/* INSERT sql statement with place holders ?,?,? */
sql ="INSERT INTO CLIENTEMAILLIST (ClientId,ClientName,ClientEmail)"
+ "values(?,?,?)";
ps = con.prepareStatement(sql);
/* Following code accepts user input for Client Id, Name and Email */
while (WclientId != 0) {
System.out.println("Enter Client Code (0-QUIT): ");
WclientId = Accept.nextInt();
Accept.nextLine(); // Dummy to avoid skipping NAME input
if (WclientId != 0){
System.out.println("Enter Client Name : ");
String WclientName = Accept.nextLine();
System.out.println("Enter Client EMail : ");
String WclientEmail = Accept.nextLine();
// Settler method to assign actual values
ps.setInt(1,WclientId);
ps.setString(2,WclientName);
ps.setString(3,WclientEmail);
ps.executeUpdate();
System.out.println("Record Created in the database");
}// if end
} // while end
}
catch (SQLException err){
System.out.println("ERROR:"+err.getMessage());
}
// try end
}
/* RECORD RETRIEVAL METHOD */
public void RetrieveRecord(){
System.out.println("RECORD RETRIEVAL");
WClientId =1;
try {
while (WClientId != 0) {
System.out.println("Enter Client Id (0 to EXIT): ");
WClientId = AcceptInput.nextInt();
AcceptInput.nextLine();
// to avoid skipping next input
con.setAutoCommit(false);
if (WClientId !=0){
sql =
"SELECT Clientname,clientemail from CLIENTEMAILLIST where CLIENTID = ? for update";
ps = con.prepareStatement(sql);
ps.setInt(1, WClientId);
rs=ps.executeQuery();
rs.next();
WClientName = rs.getString(1);
WClientEmail = rs.getString(2);
System.out.println("Client Id : " + WClientId + ", Name : "
+ WClientName + ", Current EMail : " + WClientEmail);
con.setAutoCommit(true);
}
//if end
}
// while end
}
catch (SQLException err){
System.out.println("ERROR:"+err.getMessage());
}
}
/* RECORD UPDATE METHOD */
public void UpdateRecord(){
System.out.println("RECORD UPDATE");
WClientId = 1;
try {
while (WClientId != 0) {
con.setAutoCommit(false); // Set auto-commit off
System.out.println("Enter Client Id (0 to EXIT): ");
WClientId = AcceptInput.nextInt(); // nextInt() method for integer input
AcceptInput.nextLine();// to avoid skipping next input
if (WClientId !=0){
sql =
"SELECT Clientname,clientemail from CLIENTEMAILLIST where CLIENTID = ? for update of clientemail";
ps = con.prepareStatement(sql);
ps.setInt(1, WClientId);
rs=ps.executeQuery();
rs.next();
WClientName = rs.getString(1);
WClientEmail = rs.getString(2);
System.out.println("Client Id : " + WClientId + ", Name : "
+ WClientName + ", Current EMail : " + WClientEmail);
System.out.println("Enter New Email or press ENTER for no chnage):");
WClientEmail = AcceptInput.nextLine();// New Email
sql =
"UPDATE CLIENTEMAILLIST SET ClientEmail = ? where ClientId = ? ";
ps = con.prepareStatement(sql);
ps.setInt(2, WClientId);
ps.setString(1,WClientEmail);
ps.executeUpdate();
con.commit();
con.setAutoCommit(true);
// Set auto-commit on
} // if end
} // while end
}
catch (SQLException err){
System.out.println("ERROR:"+err.getMessage());
}
}
/* RECORD DELETION METHOD */
public void DeleteRecord(){
System.out.println("RECORD DELETION");
Scanner Accept = new Scanner(System.in);
WClientId= 1;
try {
while (WClientId!=0){
con.setAutoCommit(false);
System.out.println("Enter Client Id of the record to be Deleted (Numeric): ");
WClientId = Accept.nextInt();
/*SQL select statement with placeholder for ClientId to display detail*/
sql =
"SELECT Clientname,clientemail from CLIENTEMAILLIST where CLIENTID = ? for update of clientemail";
ps = con.prepareStatement(sql);
ps.setInt(1, WClientId);
// settler method assigns actual value
rs=ps.executeQuery();
// to get result set
rs.next();
// move cursor to first record in the result set
WClientName = rs.getString(1);
WClientEmail = rs.getString(2);
System.out.println("Client Id : " + WClientId + ", Name : "
+ WClientName + ", Old EMail : " + WClientEmail);
System.out.println("Confirm DELETION (y/n)");
String confirm = Accept.next();
/* to confirm DELETION */
if (confirm.equals("y")){
sql =
"DELETE FROM CLIENTEMAILLIST where ClientId = ?";
ps = con.prepareStatement(sql);
ps.setInt(1,WClientId);
ps.executeUpdate();
System.out.println("Above record is deleted from the database");
} //if end
System.out.println(" Record not deleted");
con.setAutoCommit(true);
} // if end
} //while end
catch (SQLException err){
System.out.println("ERROR:"+err.getMessage());
} //try end
} // menu
}// class
One can test the program by running two instances in NetBeans by
NetBeans -> Projects -> Application ->Right Click -> Run
