This program uses Methods to execute each CRUD operation. We have two types of methods, Class method and Object/Instance method. Class methods are static because they are binded during compilation process, whereas Object/Instance methods are dynamic and they are binded during run time, when objects/instances are created.
Question is, which one to use? That depends on purpose of the methods. Here, DBMS manipulation is dynamic, it involves read/write situations in a multi user environment. So, Object/Instance methods are preferred over Class methods to avoid complications that may arise.
Anyway, Class/Static methods (like 'Main') have their own significance in Java. Thumb rule is unless one is very sure, static methods are not preferred. Moreover, Java is OOP language, so defining a class(Template), creating an object/instance from it and working on it is default.
Another problem as far as this program is concerned is, 'Main' being static,
public static void main(String[] args)
we can call only Static methods. This constraint is overcome by creating an object of the class and using its Instance to call Object/Instance Methods.
Now to the menu driven program of CRUD with methods for CRUD operations.
Software used is NetBeans IDE 8.0.2.
Note : ClientId is unique and do not allow duplicates.
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.
No other checks were made.
Program -
import java.sql.Connection;// connection object
import java.sql.DriverManager; // Driver and Connection to DBimport java.sql.Statement; //Simple Statement object to send query.
import java.sql.PreparedStatement;// Statement Objects for SQL with parameters
import java.sql.ResultSet;
import java.sql.SQLException; // Error trapping
import java.util.Scanner; // for user input, not connected to JDBC/DB
public class CRUDmenu {
String host = "jdbc:derby://localhost:1527/ClientDB";
String UserName = "parthiban";
String UserPassword = "password";
String sql;
Connection con;
ResultSet rs;
PreparedStatement ps;// Prepared Statement object for SQL with parameters
Statement ss; // Simple Statement object for query
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
CRUDmenu Cmo = new CRUDmenu();
String OptionInput;
// ConnectMethod is called using Cmo instance
Cmo.ConnectMethod(); // Calling Connection Method
do { // do while is used to loop till user Exits
// Calling OptionMethod for user option
OptionInput = Cmo.OptionMethod();
// Switch control is used to call a Method based on user input
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 while
}// end main
//* Connection Method */
public void ConnectMethod(){
try{
con = DriverManager.getConnection(host,UserName,UserPassword);
System.out.println(" Connected..");// Message if connection is successful
}
catch (SQLException err){
System.out.println("ERROR:"+err.getMessage());// Error Message, if anything fails
}
}
/* Option Method */
public String OptionMethod(){
String OptionInput;
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();
return OptionInput; // User input is returned
}
/* Creation Method */
public void CreateRecord(){
System.out.println("RECORD CREATION");
WClientId = 1;
try {
/* INSERT sql statement with place holders ?,?,? */
sql ="INSERT INTO CLIENTEMAILLIST (ClientId,ClientName,ClientEmail)"
+ "values(?,?,?)";
/* preparedstatemnt object 'ps' is created through 'prepareStaement' method of connection object */
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- Back to Menu): ");
WClientId = AcceptInput.nextInt(); // nextInt() method for integer input
AcceptInput.nextLine(); // Dummy to avoid skipping NAME input
if (WClientId != 0){
System.out.println("Enter Client Name : ");
WClientName = AcceptInput.nextLine(); // nextLine() method for string value
System.out.println("Enter Client EMail : ");
WClientEmail = AcceptInput.nextLine();
/* Settler method of statement object is used to assign actual values
with respective column position in the sql statement */
ps.setInt(1,WClientId); //setInt() for integer column
ps.setString(2,WClientName); // setString() for varchar column
ps.setString(3,WClientEmail);
/* executeUpadate method of statement object is used to execute the statement */
ps.executeUpdate();
System.out.println("Record Created in the database");
}// if end
} // while end
}
catch (SQLException err){
System.out.println("ERROR:"+err.getMessage());// Error Message, if anything fails
}// try end
}
/* Retrieval Method */
public void RetrieveRecord(){
System.out.println("RECORD RETRIEVAL");
try {
/* 'createStatement' method of connection object to create statement object */
ss = con.createStatement();
/* Required SQL statement is assigned to sql string */
sql ="SELECT * FROM CLIENTEMAILLIST ";
/* executeQuery method of statement object to execute the statement
and the result is assigned to Result Set object rs */
rs = ss.executeQuery(sql);
while (rs.next()){
/* getInt or getString method of Result Set 'rs' is used accordingly
to get the value of the column */
WClientId = rs.getInt(1); // Column position in the 'sql' is used as parameter
WClientName = rs.getString(2);
WClientEmail = rs.getString("ClientEMail"); // Column Name can be used as parameter
System.out.println("CLIENT ID: "+ WClientId + " , NAME : " + WClientName + " , EMAIL : " + WClientEmail);
}
}
catch (SQLException err){
System.out.println("ERROR:"+err.getMessage());// Error Message, if anything fails
}
}
/* Update Method */
public void UpdateRecord(){
System.out.println("RECORD UPDATE");
WClientId = 1;
try {
while (WClientId != 0) {
System.out.println("Enter Client Id (0 to EXIT): ");
WClientId = AcceptInput.nextInt(); // nextInt() method for integer input
AcceptInput.nextLine();// Dummy to avoid skipping next input
if (WClientId !=0){
sql ="SELECT Clientname,clientemail from CLIENTEMAILLIST where CLIENTID = ? ";
ps = con.prepareStatement(sql);
ps.setInt(1, WClientId);
rs=ps.executeQuery(); // executeQuery method of ps to get resultset
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 + ", 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);
/* executeUpadate method of statement object */
ps.executeUpdate();
} // if end
} // while end
}
catch (SQLException err){
System.out.println("ERROR:"+err.getMessage());
}
}
/* Deletion Method /*
public void DeleteRecord(){
System.out.println("RECORD DELETION");
WClientId= 1;
try {
System.out.println("Enter Client Id of the record to Delete ('0' - Back to Menu ): ");
WClientId = AcceptInput.nextInt(); // nextInt() method for integer input
/*SQL select statement with placeholder for ClientId to display detail*/
sql ="SELECT Clientname,clientemail from CLIENTEMAILLIST where CLIENTID = ? ";
ps = con.prepareStatement(sql); // prepared statement with sql as parameter
ps.setInt(1, WClientId); // settler method of ps to assign value to
// ClientId placeholder
rs=ps.executeQuery(); // executequery method of ps to get resultset
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);
/* SQL statement to delete existing record */
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"); // Message after completion
}
catch (SQLException err){
System.out.println("ERROR:"+err.getMessage());
} // menu
}
No comments:
Post a Comment