GETTING JAVA
Create an application to maintain employee details. This application should support the following functionalities:
1. Create new employees.
1.1 Must Validate the mandatory fields
1.2 Employee code should be unique
1.3 Date format should be dd/mm/yyyy format
2. View/Modify the added employees
2.1 Employee code should be unique
3. Delete employees
4. Change priority of employees
The employee information includes the following:
No
|
Name of the field
|
Size
|
Details
|
1
|
EMPLOYEE CODE
|
10
|
§ This field is mandatory
§ This field cannot be repeated
|
2
|
NAME
|
256
|
§ This field is mandatory
|
3
|
DOB
|
§ This field is mandatory
§ The format should be dd/mm/yyyy
| |
4
|
ADDRESS
|
§ This field is mandatory
| |
5
|
STATE
|
100
| |
6
|
LANGUAGE
|
256
| |
7
|
SEX
|
2
| |
8
|
PUBLISH
|
2
|
§ This field is to determine whether data should be published in the public part
|
Screenshots
1 Add Employee Details
2 Edit Employee Details
3 Employee List
4 Delete / Edit Employee
Technologies
1) Languages : JSP, HTML, Ajax, JQuery, JavaScript
2) Database : MySql 5
DATABASE : training
USERNAME : root
PASSWORD : root
URL : 192.168.0.11:3306/training
3) IDE : Eclipse 3.4, SQLyog
Screen Database Relation
NO:
|
Screen Field
|
Table Name
|
FIELD
|
TYPE
|
1
|
tab_employee
|
emp_id
|
int
| |
2
|
EMPLOYEE CODE
|
tab_employee
|
emp_code
|
varchar
|
3
|
NAME
|
tab_employee
|
emp_name
|
varchar
|
4
|
DOB
|
tab_employee
|
date_of_birth
|
date
|
5
|
ADDRESS
|
tab_employee
|
emp_address
|
text
|
6
|
STATE
|
tab_employee
|
emp_state
|
varchar
|
7
|
LANGUAGE
|
tab_employee
|
emp_language
|
varchar
|
8
|
SEX
|
tab_employee
|
emp_sex
|
smallint
|
9
|
tab_employee
|
emp_priority
|
int
| |
10
|
PUBLISH
|
tab_employee
|
emp_publish
|
smallint
|
Database Model
TABLE : tab_employee
NO:
|
FIELD
|
TYPE
|
SIZE
|
1
|
emp_id
|
int
|
11
|
2
|
emp_code
|
varchar
|
10
|
3
|
emp_name
|
varchar
|
256
|
4
|
date_of_birth
|
date
| |
5
|
emp_address
|
text
| |
6
|
emp_state
|
varchar
|
100
|
7
|
emp_language
|
varchar
|
256
|
8
|
emp_sex
|
smallint
|
2
|
9
|
emp_priority
|
int
|
11
|
10
|
emp_publish
|
smallint
|
2
|
JSP PAGES
The main jsp pages used for employee operations are addemployees.jsp , addemployeesdb.jsp, deleteemployees.jsp , ajaxemployees .jsp and employeeslist.jsp
addemployees.jsp
This page is used for both adding and editing the employee’s details. The details entered by the user in this page will be validated first. If the user didn’t enter valid data, then error message will be shown at the top of the page. If all the fields entered are valid, then page is submitted to the addemployeesdb page . if the emp_id is 0, then the mode is ‘add‘ otherwise the page is in edit mode.
only logged in users can view this page .so we check it at the beginning of the page it is checked whether the user had logged in or not
//checking session for logged in user
if(session.getAttribute("logged_in_user") == null) response.sendRedirect("index?ct=" + System.currentTimeMillis());
If the logged in user is null. Then the page will be redirected to the index page.
Validation
In the employee add page employee code, name, date of birth, address, state are mandatory field, the user must enter it. The employee code cannot be repeated and the date must be entered in the specified format. Jquery validations is used to validate this.
The validation is taking place when the user click the submit button .when clicking the submit button function submitForm will be executed
function submitForm(){
-------
-------
} In this first five rules are added to validate the fields employee code, name, dob, address, state as
jQ("#frmAddemply").find(":input[name='emp_code']").rules("add", {required: true});
jQ("#frmAddemply").find(":input[name='emp_name']").rules("add", {required: true});
jQ("#frmAddemply").find(":input[name='emp_dob']").rules("add", {required: true,datecheck: true});
jQ("#frmAddemply").find("#emp_address").rules("add", {required: true});
jQ("#frmAddemply").find(":input[name='emp_state']").rules("add", {required: true});
frmAddemply is the id of the form and ‘required : true’ means this field should not be empty. In the third rule there is ‘required:true, datecheck: true’ which means the field is required and datecheck should be true. We have added datecheck as another function to check the entered date format as dd/mm/yyyy.
jQuery.validator.addMethod("datecheck", function(value, element) {
var vDT="(([012]?[1-9])|10|20|30|31)[/](([0]?[1-9])|10|11|12)[/](19|20)?[0-9]{2}";
var regex = new RegExp(vDT);
return (regex.test(value));
});
If the entered format is correct it will return true otherwise false.
if(jQ("#frmAddemply").validate().element(jQ("#frmAddemply").find(":input[name='emp_code']"))){
if(jQ("#frmAddemply").validate().element(jQ("#frmAddemply").find(":input[name='emp_name']"))) {
if(jQ("#frmAddemply").validate().element(jQ("#frmAddemply").find(":input[name='emp_dob']"))) {
if(jQ("#frmAddemply").validate().element(jQ("#frmAddemply").find("#emp_address"))){
if(jQ("#frmAddemply").validate().element(jQ("#frmAddemply").find(":input[name='emp_state']"))) {
checkDuplicateCode();
}else{
jQ("#emp_state").addClass("invalid_form_element");
}
}else{
jQ("#emp_address").addClass("invalid_form_element");
}
}else{
jQ("#emp_dob").addClass("invalid_form_element");
}
}else{
jQ("#emp_name").addClass("invalid_form_element");
}
}else{
jQ("#emp_code").addClass("invalid_form_element");
}
Based on the added rules all four fields are checked to see if it return true. If it does not return true then else condition will execute and a red line will appear the border of that field to indicate error condition.
jQ('#frmAddemply').validate({
messages: {
"emp_code" : {
required: "Please Enter Code"
},
"emp_name" : {
required: "Please Enter Name"
},
"emp_dob" : {
required: "Please Enter DOB",
datecheck: "Enter Correct Format (dd/mm/yyyy)"
},
"emp_address" : {
required: "Please Enter Address"
},
"emp_state" : {
required: "Please Enter State"
}
},
errorPlacement : function(error, element) {
viewMessageText(error.text());
}
});
The above code is included in the jquery ready function. This is for displaying the error message on top of the page. frmAddemply is the id of the form
Eg: if emp_code is empty, then the message ‘please Enter code’ will be displayed on top of the page through the function
Function viewMessageText(msgText) {
if(msgText != '') {
jQ('#notifyDiv').css("display", "block");
jQ('#notifyPar').html(msgText);
}
}
If all data entered are valid, then the function checkDuplicateCode() is executed to check the added employee code already exist or not .
function checkDuplicateCode(){
var value = document.frmAddemply.emp_code.value;
var empId = '<%=emp_id%>'
jQ.ajax({
type: "POST",
url : "ajaxemploy",
data:"fun=checkEmpcode&code="+value+"&emp_id="+empId ,
success: function(data, status) {
var responseHtml = data.trim();
if(responseHtml.indexOf("OK") != -1) {
jQ('#frmAddemply').submit();
}
else {
viewMessage(1);
}
},
error: function(xml, status, e){queryError(xml, status, e)},
global: false
});
}
This will make an ajax call to the ajaxemploy.jsp page and there checking is done. if value already exist KO will be returned otherwise OK will be returned . If the returned value is OK, then the form will be submitted otherwise viewMessage(1) is called
var messages = new Array();
messages[1] = "Employee Code already exist";
and this message will be displayed
If the emp_id is not 0, then the form will be in edit mode. Then the save button will be changed to a modify one.
if(!emp_id.equals("0")){
psEmp=conn.prepareStatement("SELECT emp_name, date_of_birth, emp_address, emp_state, emp_language, emp_sex, emp_publish, emp_code FROM tab_employee WHERE emp_id=?");
psEmp.clearParameters();
psEmp.setInt(1,Integer.parseInt(emp_id));
rsEmp=psEmp.executeQuery();
if(rsEmp.next()){
emp_name=rsEmp.getString(1);
emp_dob=sdf.format(rsEmp.getDate(2));
emp_address=rsEmp.getString(3);
emp_state=rsEmp.getString(4);
emp_language=rsEmp.getString(5);
emp_sex=rsEmp.getString(6);
emp_publish=rsEmp.getString(7);
emp_code=rsEmp.getString(8);
}
mode="edit";
}
The query will be executed and the corresponding data will be stored in fields like emp_name, emp_code, emp_address, emp_state etc. These fields will be given as values in the corresponding form elements. When the modify button is clicked same validation explained above will happen and the form will be submitted.
Ready function
In javascript the first function executed will be the ready function
jQ(document).ready(function() {
----
---
}
jQ("#btnSubmit").click(function(){
submitForm();
return false;
}); when the id btnSubmit of button is clicked, function submitForm is called.
jQ('#emp_code').focus(); // for focusing the field
addemployesdb.jsp
In this page the data entered in the addemployee will be stored in the database. The two operations done in this page are adding data and modifying it.
if(mode.equals("add")){
int priority;
rsPriority=dbMgr.executeQuery("SELECT MAX(emp_priority) from tab_employee ");
if(rsPriority.next()){
priority=(rsPriority.getInt(1)+1);
}else{
priority= 1;
}
psEmp=conn.prepareStatement("INSERT INTO tab_employee (emp_code, emp_name, date_of_birth, emp_address, emp_state, emp_language, emp_sex, emp_priority, emp_publish) values( ?, ?, ?, ?, ?, ?, ?, ?,?)");
psEmp.setString(1,emp_code);
psEmp.setString(2,emp_name);
psEmp.setDate(3,sqlDate);
psEmp.setString(4,emp_address);
psEmp.setString(5,emp_state);
psEmp.setString(6,emp_language);
psEmp.setInt(7,Integer.parseInt(emp_sex));
psEmp.setInt(8,priority);
psEmp.setInt(9,Integer.parseInt(emp_publish));
psEmp.executeUpdate();
errorCode=1;
}
In this page the data is inserted to the database. The priority inserted will be the maximum priority in that column. The errorCode is used for showing the message
if(mode.equals("edit")){
psEmp=conn.prepareStatement("UPDATE tab_employee SET emp_code=?, emp_name=?,date_of_birth=?,emp_address=?,emp_state=?,emp_language=?,emp_sex=?,emp_publish=? WHERE emp_id=?");
psEmp.setString(1,emp_code);
psEmp.setString(2,emp_name);
psEmp.setDate(3,sqlDate);
psEmp.setString(4,emp_address);
psEmp.setString(5,emp_state);
psEmp.setString(6,emp_language);
psEmp.setInt(7,Integer.parseInt(emp_sex));
psEmp.setInt(8,Integer.parseInt(emp_publish));
psEmp.setInt(9,Integer.parseInt(emp_id));
psEmp.executeUpdate();
errorCode=2;
}
This code is used for updating the details, for this the update query is used. Here priority cannot be updated. This can be done with drag and drop only.
employeeslist.jsp
This page will show the list of employees with details name, dob and code. Different operations like sort, search, modify, delete, change priority can be done.
sqlTopic="SELECT emp_id, emp_name, date_of_birth, emp_priority, emp_publish FROM tab_employee";
rsEmp=dbMgr.executeQuery(sqlTopic+sqlCondition+sqlOrder);
This query will fetch the details of employees and this will displayed on the page by iterating it like
<% while(rsEmp.next()){
-----
-----
<% } %>
Change Priority
Priority can be changed using drag and drop. For this functionality we have to add the script library tablednd.js.
The listing loop should be entered in the div called list border and the table should be given id as ‘employ_table’ and each tr id should be unique values. We also keep a hidden value for identifying each row
<div id="list_border" style="border: none">
----
<table width="100%" border="0" cellspacing="0" cellpadding="0" id="employ_table"> <% while(rsEmp.next()){ <tr id=<%=prty%>>
---------------------------------
---------------------------------
<input type="hidden" name="emp_id_<%=prty%>" id="emp_id_<%=prty %>" value="<%=rsEmp.getString("emp_id")%>" />
</tr> <% } %>
</table>
</div>
For the first time when the page loads we will call the js functions as
<script>
<%if(boolview){ %>
arrangeList(2,"");
<%}%>
</script> If searching or sorting is done drag and drop should not work. So boolview is there to check that.
Function arrangeList(flag, responseHtml) {
if(flag == 1) {
jQ("#list_border").html(responseHtml);
table1= document.getElementById("employ_table");
if(table1.rows.length > 1) {
tableDnD1 = new TableDnD();
tableDnD1.init(table1);
tableDnD1.onDrop = function (table, row) {
dragMediaUpdate(1, table, row);
}
}
}
else if(flag == 2) {
table1= document.getElementById("employ_table");
if(table1.rows.length > 1) {
tableDnD1 = new TableDnD();
tableDnD1.init(table1);
tableDnD1.onDrop = function (table, row) {
dragMediaUpdate(1, table, row);
}
}
<%}%>
}
}
For the first time else condition is executed and if the table has more than one rows dragMediaUpdate function is called.
function dragMediaUpdate(flag, table, row) {
var hidObj;
var fromOrder;
var toOrder;
var fun;
if(flag == 1) {
hidObj = document.getElementById("emp_id_"+row.id);
fromOrder = row.id;
toOrder = whichRow(hidObj)+1;
fun = "chngPrty";
}
if(fromOrder != toOrder) {
updateRelatedMediaContent(fun, fromOrder, toOrder)
}
}
Here the function whichRow is passed the hidden emp_id.
function whichRow(obj){
var par=obj.parentNode;
while(par.nodeName.toLowerCase()!='tr'){
par=par.parentNode;
}
return par.rowIndex;
}
Then if the from order is not equal to the to order function updateRelatedMediaContent() will be called
function updateRelatedMediaContent(fun, fromOrder, toOrder) {
jQ.ajax({
type: "POST",
url: "ajaxemploy",
data: "fun="+fun+"&fromOrder="+fromOrder+"&toOrder="+toOrder+"&ct="+new Date().valueOf(),
success: function(data, status) {
var responseHtml = jQuery.trim(data.trim());
if(responseHtml == 'OK') {
window.location.href = "employeeslist?ct="+new Date().valueOf();
}
},
error: function(xml, status, e){queryError(xml, status, e)},
global: false
});
}
Here an ajax call is made to the ajaxemploy.jsp with from order and to order are passed as parameters. There the priorities are updated in the database. If the priorities are set correctly, then a response OK is returned otherwise KO is returned. If OK is returned then the page will be refreshed.
The priority can also be changed by clicking the up and down arrow in the list page. In this, the function called is
function moveList(fun, fromOrder, toOrder) {
if(fromOrder != toOrder) {
updateRelatedMediaContent(fun, fromOrder, toOrder)
}
}
Sorting
The listed fields can sorted in ascending or descending order. This is done with the javascript
function orderBy(orderid, order_field){
jQ('#boolview').attr('value', 'false');
if(jQ('#orderid').attr('value') == orderid) {
if(jQ('#by').attr('value') == 'ASC') {
jQ('#by').attr('value', 'DESC');
} else {
jQ('#by').attr('value', 'ASC');
}
} else {
jQ('#by').attr('value', 'ASC');
}
jQ('#sselValue').attr('value', jQ('#selValue').attr('value'));
jQ('#orderid').attr('value', orderid);
jQ('#orderfield').attr('value', order_field);
jQ('#frm1').attr('action', 'employeeslist?ct=' + new Date().valueOf());
jQ('#frm1').submit();
}
Here the attribute by is set with the value ASC or DESC. Order field will set the name of the column to be ordered. Already the string order by is set as
sqlOrder = " ORDER BY " + orderField + " " + by;
so when query is executed the correct order is executed depending on the situation.
Searching
We can search the employees name in the list when a name is entered in the search field and search button is clicked. The following function is called.
function doSearch() {
jQ('#stitle').attr('value', jQ('#searchtitle').attr('value'));
jQ('#frmlistEmp').attr('action', 'employeeslist?usrType=<%=userType%>&ct=' + new Date().valueOf());
jQ('#frmlistEmp').submit();
}
<input type="hidden" id="stitle" name="searchtitle" value="<%=exParams.getParameter("searchtitle", "")%>" />
The search title attribute will keep the value of the search term.
if(!"".equals(exParams.getParameter("searchtitle", ""))) {
sqlCondition += " WHERE UPPER(emp_name) LIKE '%" + dbMgr.parseString(exParams.getParameter("searchtitle", "")).toUpperCase().replaceAll("_", java.util.regex.Matcher.quoteReplacement("\\_")) + "%'";
}
rsEmp=dbMgr.executeQuery(sqlTopic+sqlCondition+sqlOrder);
when this sql condition is executed , we can search the name of the employee.
Modify
An employ data can be modified by clicking the modify link in the employee list. Then page will be redirected to the add employee page. The corresponding employee id is also passed to the page.
Delete
An employee can be deleted by clicking the delete link. When clicked the following javascript function is executed
function doDelete(id,prt){
if(confirm("Do you want to delete the selected item")){
jQ('#emp_id').attr('value', id);
jQ('#prty').attr('value', prt);
jQ('#frmlistEmp').attr('action', 'deleteemploy?ct=' + new Date().valueOf());
jQ('#frmlistEmp').submit();
}
}
I
In this script priority and employee id is passed to the deleteemploy .jsp page.
deleteemploy.jsp
In this page the selected employee is deleted and the corresponding priorities are updated.
conn.setAutoCommit(false);// for transaction, either both query will execute or none will execute
psDel=conn.prepareStatement("UPDATE tab_employee SET emp_priority=emp_priority-1 WHERE emp_priority > ? ");
psDel.clearParameters();
psDel.setInt(1,Integer.parseInt(priority));
psDel.executeUpdate();
psDel=conn.prepareStatement("DELETE from tab_employee WHERE emp_id=? ");
psDel.clearParameters();
psDel.setInt(1,Integer.parseInt(emp_id));
psDel.executeUpdate();
conn.commit();
The first query will update the employee and the second query will delete the employee.
ajaxemploy.jsp
In this page ajax related operations are done ie change priority and check duplicate employee code
if(strFun.equals("checkEmpcode")){
String emp_id = exParams.getParameter("emp_id", "0");
String code = exParams.getParameter("code", "0");
int count = 0;
if(emp_id.equals("0")){
String query = "SELECT count(*) FROM tab_employee WHERE emp_code = '"+code+"'";
rsContent = conn.createStatement().executeQuery(query);
if(rsContent.next() ) {
count = rsContent.getInt(1);
}
if(count == 0) {
strBuffer.append("OK");
} else {
strBuffer.append("KO");
}
}else{// For checking code while in edit mode
String query = "SELECT count(*) FROM tab_employee WHERE emp_code = '"+code+"' AND emp_id !="+emp_id;
rsContent = conn.createStatement().executeQuery(query);
if(rsContent.next() ) {
count = rsContent.getInt(1);
}
if(count == 0) {
strBuffer.append("OK");
} else {
strBuffer.append("KO");
}
}
}
The above code is used for checking duplicate employee code. If the page is in edit mode , it will check whether any duplicate code exist other than this employee id. If no other code exist OK will be returned otherwise KO will be returned.
change priority
if(strFun.equals("chngPrty")){
String fromOrder = exParams.getParameter("fromOrder", "0");
String toOrder = exParams.getParameter("toOrder", "0");
String sqlQuery = "";
int ok1 = 0;
int ok2 = 0;
int intFrom = Integer.parseInt(fromOrder);
int intTo = Integer.parseInt(toOrder);
selecting the employee id (Employee id and priority will be unique)
String emplyId = "0";
sqlQuery = "SELECT emp_id FROM tab_employee WHERE emp_priority = "+fromOrder +" ";
rsContent = dbMgr.executeQuery(sqlQuery);
if(rsContent.next()) {
emplyId = rsContent.getString(1);
}
if(rsContent != null) rsContent.close();
This code will execute if the from priority is greater than the to priorirty
if(intFrom > intTo) {
String modifyIds = "";
sqlQuery = "SELECT emp_id FROM tab_employee WHERE emp_priority >= "+toOrder+" AND emp_priority < "+fromOrder ;
rsContent = dbMgr.executeQuery(sqlQuery);
while(rsContent.next()) {
if(modifyIds.equals("")) modifyIds = rsContent.getString(1);
else modifyIds += ","+rsContent.getString(1);
}
if(rsContent != null) rsContent.close();
updating all priority by adding one
if(!"".equals(modifyIds)) {
sqlQuery = "UPDATE tab_employee SET emp_priority = emp_priority + 1 WHERE emp_id IN(" + modifyIds + ") ";
ok1 = dbMgr.executeUpdate(sqlQuery);
}
else {
ok1 = 1;
}
}
This code will execute if the from priority is less than the to priorirty
else if(intFrom < intTo) {
String modifyIds = "";
sqlQuery = "SELECT emp_id FROM tab_employee WHERE emp_priority <= "+toOrder+" AND emp_priority > "+fromOrder ;
rsContent = dbMgr.executeQuery(sqlQuery);
while(rsContent.next()) {
if(modifyIds.equals("")) modifyIds = rsContent.getString(1);
else modifyIds += ","+rsContent.getString(1);
}
if(rsContent != null) rsContent.close();
updating all priority by substracting one
if(!"".equals(modifyIds)) {
sqlQuery = "UPDATE tab_employee SET emp_priority = emp_priority - 1 WHERE emp_id IN(" + modifyIds + ") ";
ok1 = dbMgr.executeUpdate(sqlQuery);
}
else {
ok1 = 1;
}
}
The to order priority is setting the corresponding employee
sqlQuery = "UPDATE tab_employee SET emp_priority = "+toOrder+" WHERE emp_id = " + emplyId;
ok2 = dbMgr.executeUpdate(sqlQuery);
if(ok1 > 0 && ok2 > 0) {
strBuffer.append("OK");
}
else {
strBuffer.append("KO");
}}
No comments:
Post a Comment
You can enter queries here...