序言
最近在整理自己的旧U盘的时候发现了几个贡献比较大对于整个系统比较熟悉的例子来给大家分享一下，因为该项目是17年大二的时候所作，稍有瑕疵请各位谅解。因为是鄙人第一个项目，所以不打算修改了，留作个纪念。
本系统框架利用了ligerUI的整体布局与样式，具体代码就不放出了，有需要的读者可自行去官网中的示例查看：ligerUI官网,项目源代码下载地址：CSDN资源下载地址
目录
3.统计查询（实现方式与前面几张表结构相似，不做赘述。只是数据库实现稍有难度，在此将贴出）
正文
-
本系统的功能模块图如下（各部分对数据库的增删改查均已省略）
-
用户分类
1.超级管理员--负责基本设置里面的修改
2.考勤员--负责考勤项的管理
-
超级管理员模块：
数据库连接代码（后不做赘述）：
（1）配置文件：（存在于src根目录下的db.properties）
DBName = Oracle
DRIVERNAME = oracle.jdbc.driver.OracleDriver
URL = jdbc:oracle:thin:@localhost:1521:ORCL
USERNAME = check_Work
USERPWD = 123（2）数据库连接：
/*
*@烟台大学 计算机与控制工程学院
*@获取数据库连接、关闭数据库连接方法、测试是否连接成功方法
*/
package com.ambow.DB;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class DBConnection {
private static String DRIVERNAME;
private static String URL;
private static String USERNAME;
private static String USERPWD;//以上为从db.propertie取得的数据库配置
static {
try {
InputStream fis = DBConnection.class.getClassLoader()
.getResourceAsStream("db.properties");
Properties pro = new Properties();
pro.load(fis);
URL = pro.getProperty("URL");
USERNAME = pro.getProperty("USERNAME");
USERPWD = pro.getProperty("USERPWD");
DRIVERNAME=pro.getProperty("DRIVERNAME");
fis.close();
fis = null;
} catch (Exception e) {
e.printStackTrace();
}
try {
Class.forName(DRIVERNAME);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @author TianZW
* @throws SQLException
* */
public static Connection getConnection() throws SQLException {
Connection conn = DriverManager.getConnection(URL, USERNAME, USERPWD);
return conn;
}
/**
* @author TianZW
* 关闭链接
* */
public static void closeConnection(Connection conn,PreparedStatement state,ResultSet rs){
if(rs != null)
try {
rs.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
if(state != null) {
try {
state.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static void main(String [] args) throws SQLException{//测试数据库是否链接成功
Connection conn=DBConnection.getConnection();
PreparedStatement state =
conn.prepareStatement("select * from att_admin");
ResultSet rs = state.executeQuery();
if( rs.next() )
{
System.out.println("success!");
}
DBConnection.closeConnection(conn,state,rs);
}
}数据库文件：
--一、建表
--0.总表
CREATE TABLE ALL_TABLE(
TABLE_NAME VARCHAR2(30),
NOTENAME VARCHAR2(100)
);
--插入说明
INSERT INTO ALL_TABLE VALUES('Att_Admin','用户表');
INSERT INTO ALL_TABLE VALUES('Att_AdminPopedom','用户权限表');
INSERT INTO ALL_TABLE VALUES('Att_AttendanceRecord','考勤记录表');
INSERT INTO ALL_TABLE VALUES('Att_AttendanceType','考勤状态表');
INSERT INTO ALL_TABLE VALUES('Att_Department','部门表');
INSERT INTO ALL_TABLE VALUES('Att_Employees','员工表');
INSERT INTO ALL_TABLE VALUES('Att_Notes','单据表');
INSERT INTO ALL_TABLE VALUES('Att_Position','职务表');
--1.用户表
CREATE TABLE Att_Admin(
AdminID NUMBER constraint pk_Att_Admin primary key,--ID
AdminAccount varchar2(50) not null,--账号
AdminPwd VARCHAR2(50) not null,--密码
AdminState NUMBER(1) not null,--是否启用此账户（是/否：1/0）
AdminRight NUMBER(1) not null,--是否为超级管理员（是/否：1/0）
AdminName VARCHAR2(50) not null--用户名
);
--创建序列
create sequence SELF_ADD
increment by 1
start with 1;
--创建触发器
create or replace trigger tr_self_add
before insert on ATT_ADMIN
for each row
begin
select SELF_ADD.nextval into :new.ADMINID from dual;
end;
--测试语句
INSERT into ATT_ADMIN(ADMINACCOUNT,ADMINPWD,ADMINSTATE,ADMINRIGHT,ADMINNAME)
VALUES('TIAN','123456','1','1','tian');
INSERT into ATT_ADMIN
VALUES('','WANG','123456','1','0','wang');
--2.用户权限表
CREATE TABLE Att_AdminPopedom(
PopedomID NUMBER CONSTRAINT PK_Att_AdminPopedom PRIMARY KEY,--ID
DepartmentID NUMBER NOT NULL,--部门编号
AdminID NUMBER NOT NULL--用户编号
);
--创建序列
create sequence SELF_ADD2
increment by 1
start with 1;
--创建触发器
create or replace trigger tr_self_add2
before insert on Att_AdminPopedom
for each row
begin
select SELF_ADD2.nextval into :new.PopedomID from dual;
end;
--测试语句
--3.考勤记录表
CREATE TABLE Att_AttendanceRecord(
AttendanceID NUMBER CONSTRAINT PK_Att_AttendanceRecord PRIMARY KEY,--ID
EmployeeID NUMBER NOT NULL,--员工编号
CardNumber VARCHAR2(50) NOT NULL,--员工卡号
AttendanceTIMESTAMP DATE NOT NULL,--考勤日期
AttendanceFlag NUMBER(1) NOT NULL,--考勤时段
AttendanceType NUMBER NOT NULL,--考勤类型
AttendanceMemo VARCHAR2(200),--备注
AdminID NUMBER NOT NULL,--考勤员编号
TempDepartmentId NUMBER NOT NULL,--部门编号
NoteId NUMBER NOT NULL--单据编号
);
--创建序列
create sequence SELF_ADD3
increment by 1
start with 1;
--创建触发器
create or replace trigger tr_self_add3
before insert on Att_AttendanceRecord
for each row
begin
select SELF_ADD3.nextval into :new.AttendanceID from dual;
end;
--测试语句
--4.考勤状态表
CREATE TABLE Att_AttendanceType(
TypeId NUMBER CONSTRAINT PK_Att_AttendanceType PRIMARY KEY,--ID
TypeName VARCHAR2(20) NOT NULL,--状态名称
TypeCategory NUMBER NOT NULL--是否请假（是/否：1/0）
);
--创建序列
create sequence SELF_ADD4
increment by 1
start with 1;
--创建触发器
create or replace trigger tr_self_add4
before insert on Att_AttendanceType
for each row
begin
select SELF_ADD4.nextval into :new.TypeId from dual;
end;
--加入固定数据
INSERT INTO Att_AttendanceType(TypeName,TypeCategory) values('出勤',0);
INSERT INTO Att_AttendanceType(TypeName,TypeCategory) values('公休',0);
INSERT INTO Att_AttendanceType(TypeName,TypeCategory) values('迟到',0);
INSERT INTO Att_AttendanceType(TypeName,TypeCategory) values('旷工',0);
INSERT INTO Att_AttendanceType(TypeName,TypeCategory) values('外出',0);
INSERT INTO Att_AttendanceType(TypeName,TypeCategory) values('出差',0);
INSERT INTO Att_AttendanceType(TypeName,TypeCategory) values('加班',0);
INSERT INTO Att_AttendanceType(TypeName,TypeCategory) values('倒休',0);
INSERT INTO Att_AttendanceType(TypeName,TypeCategory) values('事假',1);
INSERT INTO Att_AttendanceType(TypeName,TypeCategory) values('病假',1);
INSERT INTO Att_AttendanceType(TypeName,TypeCategory) values('婚假',1);
INSERT INTO Att_AttendanceType(TypeName,TypeCategory) values('丧假',1);
INSERT INTO Att_AttendanceType(TypeName,TypeCategory) values('产假',1);
--5.部门表数据
CREATE TABLE Att_Department(
DepartmentID NUMBER CONSTRAINT PK_Att_Department PRIMARY KEY,--ID
DepartmentName VARCHAR2(100) NOT NULL,--部门名称
StartTimeAM VARCHAR2(50) NOT NULL,--上午上班时间
EndTimeAM VARCHAR2(50) NOT NULL,--上午下班时间
StartTimePM VARCHAR2(50) NOT NULL,--下午上班时间
EndTimePM VARCHAR2(50) NOT NULL,--下午下班时间
ParentID NUMBER NOT NULL--父级部门编号
);
--创建序列
create sequence SELF_ADD5
increment by 1
start with 1;
--创建触发器
create or replace trigger tr_self_add5
before insert on Att_Department
for each row
begin
select SELF_ADD5.nextval into :new.DepartmentID from dual;
end;
--测试数据
--6.员工表
CREATE TABLE Att_Employees(
EmployeeID NUMBER CONSTRAINT PK_Att_Employees PRIMARY KEY,--ID
EmployeeName VARCHAR2(100) NOT NULL,--员工名称
EmployeeGender NUMBER(1) NOT NULL,--员工性别（女0，男1）
Position NUMBER NOT NULL,--职务编号
Department NUMBER NOT NULL,--部门编号
CardNumber VARCHAR2(50) NOT NULL,--员工卡号
EmployeState NUMBER(1) NOT NULL,--员工状态（正常1，停用0）
EmployeeMemo VARCHAR2(200)--备注
);
--创建序列
create sequence SELF_ADD6
increment by 1
start with 1;
--创建触发器
create or replace trigger tr_self_add6
before insert on Att_Employees
for each row
begin
select SELF_ADD6.nextval into :new.EmployeeID from dual;
end;
--7.单据表结构
CREATE TABLE Att_Notes(
NoteID NUMBER CONSTRAINT PK_Att_Notes PRIMARY KEY,--ID
DepartmentID NUMBER NOT NULL,--部门编号
EmployeeID NUMBER NOT NULL,--申请人
NoteType NUMBER NOT NULL,--单据类型
Cause VARCHAR2(1000) NOT NULL,--事由
FillInTime DATE NOT NULL,--填表日期
DirectorSign VARCHAR2(200),--主管意见
AdministrationSign VARCHAR2(200),--经理意见
PresidentSign VARCHAR2(200),--总裁意见
STARTDATE DATE NOT NULL,--开始日期
STARTTIME NUMBER(1) NOT NULL,--开始时段
ENDDATE DATE NOT NULL,--结束日期
ENDTIME NUMBER(1) NOT NULL,--结束时段
ProjectName VARCHAR2(200),--项目名称
AdminID NUMBER NOT NULL,--录入人
NoteMemo VARCHAR2(500),--备注
IsVerify NUMBER(1) NOT NULL--是否审核（否0，是1）
);
--创建序列
create sequence SELF_ADD7
increment by 1
start with 1;
--创建触发器
create or replace trigger tr_self_add7
before insert on Att_Notes
for each row
begin
select SELF_ADD7.nextval into :new.NoteID from dual;
end;
--8.职务表
CREATE TABLE Att_Position(
PositionID NUMBER CONSTRAINT PK_Att_Position PRIMARY KEY,--ID
PositionName VARCHAR2(50) NOT NULL--职务名称
);
--创建序列
create sequence SELF_ADD8
increment by 1
start with 1;
--创建触发器
create or replace trigger tr_self_add8
before insert on Att_Position
for each row
begin
select SELF_ADD8.nextval into :new.PositionID from dual;
end;
--二、加约束（主键非空约束除外）
--2.Att_AdminPopedom（用户权限表）
ALTER TABLE Att_AdminPopedom
ADD CONSTRAINT FK_DepartmentID_AdminPopedom FOREIGN KEY(DepartmentID)
REFERENCES Att_Department(DepartmentID);
ALTER TABLE Att_AdminPopedom
ADD CONSTRAINT FK_AdminID_AdminPopedom FOREIGN KEY(AdminID)
REFERENCES Att_Admin(AdminID);
--3.Att_AttendanceRecord（考勤记录表）
ALTER TABLE Att_AttendanceRecord
ADD CONSTRAINT FK_EmployeeID_AttendanceRecord FOREIGN KEY(EmployeeID)
REFERENCES Att_Employees(EmployeeID);
ALTER TABLE Att_AttendanceRecord
ADD CONSTRAINT FK_AttendanceType_ARD FOREIGN KEY(AttendanceType)
REFERENCES Att_AttendanceType(TypeId);
ALTER TABLE Att_AttendanceRecord
ADD CONSTRAINT FK_AdminID_AttendanceRecord FOREIGN KEY(AdminID)
REFERENCES Att_Admin(AdminID);
ALTER TABLE Att_AttendanceRecord
ADD CONSTRAINT FK_TempDepartmentId_ARD FOREIGN KEY(TempDepartmentId)
REFERENCES Att_Department(DepartmentID);
ALTER TABLE Att_AttendanceRecord
ADD CONSTRAINT FK_NoteId_ARD FOREIGN KEY(NoteId)
REFERENCES Att_Notes(NoteId);
--4.Att_Employees（员工表）
ALTER TABLE Att_Employees
ADD CONSTRAINT FK_Position_Employees FOREIGN KEY(Position)
REFERENCES Att_Position(PositionID);
ALTER TABLE Att_Employees
ADD CONSTRAINT FK_Department_Employees FOREIGN KEY(Department)
REFERENCES Att_Department(DepartmentID);
--5.Att_Notes（单据表）
ALTER TABLE Att_Notes
ADD CONSTRAINT FK_DepartmentID_Att_Notes FOREIGN KEY(DepartmentID)
REFERENCES Att_Department(DepartmentID);
ALTER TABLE Att_Notes
ADD CONSTRAINT FK_EmployeeID_Att_Notes FOREIGN KEY(EmployeeID)
REFERENCES Att_Employees(EmployeeID);
ALTER TABLE Att_Notes
ADD CONSTRAINT FK_NoteType_Att_Notes FOREIGN KEY(NoteType)
REFERENCES Att_AttendanceType(TypeId);
ALTER TABLE Att_Notes
ADD CONSTRAINT FK_AdminID_Att_Notes FOREIGN KEY(AdminID)
REFERENCES Att_Admin(AdminID);
（3）字符串转json对象工具
/*
*@烟台大学 计算机与控制工程学院
*@2017-8 田志伟
*/
package com.ambow.util;
import java.util.ArrayList;
import java.util.HashMap;
import com.alibaba.fastjson.JSON;
import com.ambow.entity.Admin;
import com.ambow.entity.AdminPopedom;
import com.ambow.entity.AttendanceRecord;
import com.ambow.entity.AttendanceType;
import com.ambow.entity.Department;
import com.ambow.entity.Employees;
import com.ambow.entity.Notes;
import com.ambow.entity.Position;
public class JSONUtil {
public static String DepartmenttoJSONString(ArrayList<Department> List) {
HashMap<String , Object> map = new HashMap<String , Object>();
map.put("Rows", List);
map.put("Total" ,List.size());
String json = JSON.toJSONString(map);
return json;
}
public static String PositiontoJSONString(ArrayList<Position> list) {
HashMap<String , Object> map = new HashMap<String , Object>();
map.put("Rows", list);
map.put("Total" ,list.size());
String json = JSON.toJSONString(map);
return json;
}
public static String AdminJSONString(ArrayList<Admin> list) {
HashMap<String , Object> map = new HashMap<String , Object>();
map.put("Rows", list);
map.put("Total" ,list.size());
String json = JSON.toJSONString(map);
return json;
}
public static String EmployeesJSONString(ArrayList<Employees> list) {
HashMap<String , Object> map = new HashMap<String , Object>();
map.put("Rows", list);
map.put("Total" ,list.size());
String json = JSON.toJSONString(map);
return json;
}
public static String PopedomtoJSONString(ArrayList<AdminPopedom> list) {
HashMap<String , Object> map = new HashMap<String , Object>();
map.put("Rows", list);
map.put("Total" ,list.size());
String json = JSON.toJSONString(map);
return json;
}
public static String RecordtoJSONString(ArrayList<AttendanceRecord> list) {
HashMap<String , Object> map = new HashMap<String , Object>();
map.put("Rows", list);
map.put("Total" ,list.size());
String json = JSON.toJSONString(map);
return json;
}
public static String AttendanceTypetoJSONString(ArrayList<AttendanceType> list) {
HashMap<String , Object> map = new HashMap<String , Object>();
map.put("Rows", list);
map.put("Total" ,list.size());
String json = JSON.toJSONString(map);
return json;
}
public static String NoteTypetoJSONString(ArrayList<Notes> list) {
HashMap<String , Object> map = new HashMap<String , Object>();
map.put("Rows", list);
map.put("Total" ,list.size());
String json = JSON.toJSONString(map);
return json;
}
}
（4）过滤器--字符转码器（防止网页中文乱码）
package com.ambow.filter;
import java.io.IOException;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.annotation.WebFilter;
import javax.servlet.annotation.WebInitParam;
import org.apache.catalina.connector.RequestFacade;
/**
* Servlet Filter implementation class Encoding
*/
@WebFilter(filterName = "EncodingFilter", urlPatterns = "*",initParams= {@WebInitParam(name = "encoding",value="utf-8")})
public class Encoding implements Filter {
private FilterConfig config = null;
public Encoding() {
// TODO Auto-generated constructor stub
}
public void destroy() {
}
/**
* @see Filter#doFilter(ServletRequest, ServletResponse, FilterChain)
* @author TianZW
* @throws ServletException
* @throws IOException
* @作用：适配中文字符
*/
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
String encoding = "";
String str = config.getInitParameter("encoding");
if(str == null || str.equals("")) {
encoding = "utf-8";
}else
encoding = str;
((RequestFacade) request).getSession();//增加这行代码用来解决问题
request.setCharacterEncoding(encoding);
response.setContentType("text/html;charset="+encoding);
response.setCharacterEncoding(encoding);
chain.doFilter(request, response);
}
/**
* @see Filter#init(FilterConfig)
*/
public void init(FilterConfig fConfig) throws ServletException {
this.config = fConfig;
}
}
（5）过滤器--权限管理以及访问过滤
/*
*烟台大学 计算机控制与工程学院
*@2017-8 田志伟
*/
package com.ambow.filter;
import java.io.IOException;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.annotation.WebFilter;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
@WebFilter(filterName="/login",urlPatterns={ "*.html","*.do"})
public class LoginFilter implements Filter {
public LoginFilter() {
}
public void destroy() {
}
public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
response.setContentType("text/html;charset=utf-8");
HttpServletRequest req = (HttpServletRequest)request;
String url = req.getRequestURI().toString();
HttpSession session=req.getSession();
if(url.equals("/checkWork/login.html")||url.equals("/checkWork/")) {
chain.doFilter(request, response);
}else {
if(session.getAttribute("loginname")==null){
// System.out.println( "拦截到了！" +url + "||username:" + session.getAttribute("loginname"));
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
HttpServletResponse rep = (HttpServletResponse)response;
rep.sendRedirect("/checkWork/login.html");
}else{
//System.out.println( "放行了用户！" +url + "||username:" + session.getAttribute("loginname"));
chain.doFilter(request, response);
}
}
}
public void init(FilterConfig fConfig) throws ServletException {
}
}
（6）操作工厂--以部门工厂为例，其他工厂不做赘述
package com.ambow.factory;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import com.ambow.DAO.userDao;
import com.ambow.DAOImp.userDAOImp;
import com.ambow.DB.DBConnection;
public class userFactory {
private userFactory() {}
public static userDao getUserInstance() {
InputStream fis = DBConnection.class.getClassLoader()
.getResourceAsStream("db.properties");
Properties pro = new Properties();
try {
pro.load(fis);
String drivername = pro.getProperty("DBName");
if( drivername.equals("Oracle") )
{
return new userDAOImp();
}else
{
//此处返回其他Imp对象
}
} catch (IOException e) {
e.printStackTrace();
}
return new userDAOImp();
}
}
（6）登陆登出界面
A.登陆：
界面：
前端代码：
<!DOCTYPE html>
<html lang="en">
<head>
<title>登陆</title>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" href="static/login/bootstrap.min.css" />
<link rel="stylesheet" href="static/login/css/camera.css" />
<link rel="stylesheet" href="static/login/bootstrap-responsive.min.css" />
<link rel="stylesheet" href="static/login/matrix-login.css" />
<link rel="stylesheet" href="static/login/font-awesome.css" />
<script type="text/javascript" src="static/login/js/jquery-1.5.1.min.js"></script>
<script src="static/js/jquery-1.7.2.js"></script>
<script src="static/login/js/camera.min.js"></script>
<script src="static/login/js/templatemo_script.js"></script>
<script src="static/login/js/ban.js"></script>
<script type="text/javascript" src="static/js/jQuery.md5.js"></script>
<script type="text/javascript" src="static/js/jquery.tips.js"></script>
<script type="text/javascript" src="static/js/jquery.cookie.js"></script>
<!-- 软键盘控件start -->
<script type="text/javascript" src="static/login/keypad/js/form/keypad.js"></script>
<script type="text/javascript" src="static/login/keypad/js/framework.js"></script>
<!-- 软键盘控件end -->
<!-- 软键盘控件start -->
<link href="static/login/keypad/css/framework/form.css" rel="stylesheet" type="text/css"/>
<!-- 软键盘控件end -->
<script type="text/javascript">
function judge(){
if(check()){
$("#loginForm").submit();
}else
return false;
}
function changeImg(){//自写方法
var img_login = document.getElementById("codeImg");
img_login.src = "/checkWork/VerifyCodeServlet?a="+ new Date().getTime();
}
function check() {
if ($("#loginname").val() == "") {
$("#loginname").tips({
side : 2,
msg : '用户名不得为空',
bg : '#AE81FF',
time : 3
});
showfh();
$("#loginname").focus();
return false;
} else {
$("#loginname").val(jQuery.trim($('#loginname').val()));
}
if ($("#password").val() == "") {
$("#password").tips({
side : 2,
msg : '密码不得为空',
bg : '#AE81FF',
time : 3
});
showfh();
$("#password").focus();
return false;
}
if ($("#code").val() == "") {
$("#code").tips({
side : 1,
msg : '验证码不得为空',
bg : '#AE81FF',
time : 3
});
showfh();
$("#code").focus();
return false;
}
$("#loginbox").tips({
side : 1,
msg : '正在登录 , 请稍后 ...',
bg : '#68B500',
time : 10
});
return true;
}
function savePaw() {
if (!$("#saveid").attr("checked")) {
$.cookie('loginname', '', {
expires : -1
});
$.cookie('password', '', {
expires : -1
});
$("#loginname").val('');
$("#password").val('');
}
}
jQuery(function() {
var loginname = $.cookie('loginname');
var password = $.cookie('password');
if (typeof(loginname) != "undefined"
&& typeof(password) != "undefined") {
$("#loginname").val(loginname);
$("#password").val(password);
$("#saveid").attr("checked", true);
$("#code").focus();
}
});
</script>
<style type="text/css">
.cavs{
z-index:1;
position: fixed;
width:95%;
margin-left: 20px;
margin-right: 20px;
}
</style>
<script>
//window.setTimeout(showfh,3000);
var timer;
function showfh(){
fhi = 1;
//关闭提示晃动屏幕，注释掉这句话即可
timer = setInterval(xzfh2, 10);
};
var current = 0;
function xzfh(){
current = (current)%360;
document.body.style.transform = 'rotate('+current+'deg)';
current ++;
if(current>360){current = 0;}
};
var fhi = 1;
var current2 = 1;
function xzfh2(){
if(fhi>50){
document.body.style.transform = 'rotate(0deg)';
clearInterval(timer);
return;
}
current = (current2)%360;
document.body.style.transform = 'rotate('+current+'deg)';
current ++;
if(current2 == 1){current2 = -1;}else{current2 = 1;}
fhi++;
};
</script>
</head>
<body>
<!--小键盘承载器-->
<canvas class="cavs"></canvas>
<div style="width:100%;text-align: center;margin: 0 auto;position: absolute;">
<!-- 登录 -->
<div id="windows1">
<div id="loginbox" >
<form action="login?opt=login" method="post" id="loginForm">
<div class="control-group normal_text">
<h3>
<img src="static/login/logo.png" alt="Logo" />
</h3>
</div>
<div class="control-group">
<div class="controls">
<div class="main_input_box">
<span class="add-on bg_lg">
<i><img height="37" src="static/login/user.png" /></i>
</span><input type="text" name="loginname" id="loginname" value="" placeholder="请输入用户名" />
</div>
</div>
</div>
<div class="control-group">
<div class="controls">
<div class="main_input_box">
<span class="add-on bg_ly">
<i><img height="37" src="static/login/suo.png" /></i>
</span><input type="password" name="password" id="password" placeholder="请输入密码" class="keypad" keypadMode="full" allowKeyboard="true" value=""/>
</div>
</div>
</div>
<div class="form-actions">
<div style="width:86%;padding-left:8%;">
<div style="float: left;padding-top:2px;">
<i><img src="static/login/yan.png" /></i>
</div>
<div style="float: left;" class="codediv">
<input type="text" name="code" id="code" class="login_code"
style="height:16px; padding-top:4px;" />
</div>
<div style="float: left;">
<a href="javascript:changeImg()"><img style="height:35px;width: 150px" id="codeImg" name="codeImg" alt="点击更换" title="点击更换" src="/checkWork/VerifyCodeServlet?"+new Date().getTime() /></a>
</div>
<span class="pull-right"><input type="button" value="登录" id="subBtn" onclick="judge()"/></span>
</div>
</div>
</form>
<div class="controls">
<div class="main_input_box">
<font color="white"><span id="nameerr">Copyright www.我没有网址.com</span></font>
</div>
</div>
</div>
</div>
</div>
<div id="templatemo_banner_slide" class="container_wapper">
<div class="camera_wrap camera_emboss" id="camera_slide">
<!-- 背景图片 -->
<div data-src="static/login/images/banner_slide_01.jpg"></div>
<div data-src="static/login/images/banner_slide_02.jpg"></div>
<div data-src="static/login/images/banner_slide_03.jpg"></div>
<div data-src="static/login/images/banner_slide_04.jpg"></div>
<div data-src="static/login/images/banner_slide_05.jpg"></div>
</div>
<!-- #camera_wrap_3 -->
</div>
</body>
</html>服务器端：
package com.ambow.controller;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import com.alibaba.fastjson.JSON;
import com.ambow.DAO.userDao;
import com.ambow.factory.userFactory;
@WebServlet("/login/*")
public class LoginController extends HttpServlet {
private static final long serialVersionUID = 1L;
public LoginController() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
PrintWriter out = response.getWriter();
String opt = request.getParameter("opt");
userDao userDao = userFactory.getUserInstance();
if(opt.equals("login")) {
try {
String loginname=request.getParameter("loginname");
String password=request.getParameter("password");
boolean judge=userDao.exits(loginname,password);
String code = request.getParameter("code");
String vcode = (String)request.getSession().getAttribute("vCode");
if(!code.equalsIgnoreCase(vcode)) {
out.write("<script type='text/javascript'>alert('验证码输入错误！');"
+ "window.location.href='/checkWork/login.html';</script>");
out.flush();
out.close();
return;
}
if(judge) {
HttpSession session=request.getSession();
int state = userDao.queryState(loginname);
session.setAttribute("loginname", loginname);
session.setAttribute("password", password);
session.setAttribute("state", state);
response.sendRedirect("/checkWork/main.html");
}else {
out.write("<script type='text/javascript'>alert('账号或密码错误！或者账号已停用！');"
+ "window.location.href='/checkWork/login.html';</script>");
}
} catch (Exception e) {
out.write("<script type='text/javascript'>alert('未知错误！');"
+ "window.location.href='/checkWork/login.html';</script>");
}
}else if(opt.equals("getState")) {
HttpSession session=request.getSession();
int state = (int)session.getAttribute("state");
out.write(JSON.toJSONString(state));
}else if(opt.equals("logout")) {
HttpSession session=request.getSession();
session.removeAttribute("loginname");
session.removeAttribute("password");
session.removeAttribute("state");
}else if(opt.equals("getUserName")) {
HttpSession session=request.getSession();
String name = (String)session.getAttribute("loginname");
out.write(name);
}
out.flush();
out.close();
}
}
使用了的关于验证码的jar包连接：密码：pou4。
B.登出界面：
代码同在登陆页面代码。
基本设置（服务器端均用web3.0的注解配置）
1.用户管理模块--此模块包含对用户表的增删改查操作
（1）前端功能演示图：
用户管理：
用户添加：
用户修改：
（2）核心代码概要：
A.实体层（model层）：
package com.ambow.entity;
/**
* @author TianZW
* @Admin用户表实体类
* */
public class Admin {
private int AdminID ;//用户ID
private String AdminAccount ;//账号
private String AdminPwd;//密码
private int AdminState;//是否启用此账户
private int AdminRight;//是否为超级管理员
private String AdminName;//用户名
}
此部分只粘贴了类中的字段摘要，还有该类的空参构造与满参构造函数和个字段的get/set方法。不过注意的是，getAdminPwd部分的get方法应该是需要屏蔽的，即便是超级管理员也应无权查看考勤员密码。
B.前端显示部分（view层）：
<!-- 烟台大学 计算机与控制工程学院 田志伟 -->
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>职位管理</title>
<link href="js/ligerUI/ligerUI/skins/Aqua/css/ligerui-all.css"
rel="stylesheet" type="text/css" />
<script src="js/ligerUI/jquery/jquery-1.9.0.min.js"
type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/core/base.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerGrid.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerDialog.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerTextBox.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerCheckBox.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerComboBox.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerGrid.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerDateEditor.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerSpinner.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerResizable.js" type="text/javascript"></script>
<style type="text/css">
.l-case-title
{
font-weight: bold;
margin-top: 20px;
margin-bottom: 20px;
}
</style>
<script type="text/javascript">
var win1;
function f_open()
{
if (win1) win1.show();
else win1 = $.ligerDialog.open({
height: 400,
url: '/checkWork/userAdd.html',
width: 400,
showMax: true,
showToggle: true,
showMin: true,
isResize: true,
slide: true });
setgrid(queryData);
}
</script>
<script type="text/javascript">
var grid = null;
var queryData = null;
var numReg ="^(0|[1-9][0-9]*)\$";//数字的正则表达式
$(function() {
$.ajax({
type : "POST",
url : "user.do?opt=queryAll&timeid="+new Date().getTime(),
success : function(list) {
setgrid(list);
},
dataType : "json"
});
//设置表格分页
$("#pageloading").hide();
$("#btnQuery").click(function(){
var num = $("#userno").val();
if(num.match(numReg)){
$.ajax({
type : "POST",
data : {'num' : num},
url : "user.do?opt=queryByPK&timeid="+new Date().getTime(),
success : function(result) {
setgrid(result);
},dataType : "json"
});
}else
window.location.reload();
});
$("#btnQueryByName").click(function(){
var Byname = $("#Byname").val();
$.ajax({
type : "POST",
data :{'Byname':Byname},
url : "user.do?opt=queryByName&timeid=" + new Date().getTime(),
success : function(result) {
setgrid(result);
},dataType : "json"
});
});
});
/***************************************************************************************************/
function setgrid(queryData) {
grid = $("#divBody").ligerGrid({
columns : [
{
display : '用户编号',
name : 'adminID',
minWidth : 60
},{
display : '用户账号',
editor: { type: 'text' },
name : 'adminAccount',
minWidth : 60
} ,{
display : '用户密码',
editor: { type: 'text' },
name : 'adminPwd',
minWidth : 60
} ,{
display : '账号状态',
render : function(rowdata, rowindex, value){
if(value==0)
return "停用";
else
return "启用";
},
editor: { type: 'select',
data:[{id:"1",text:"启用"},{id:"0",text:"停用"}],
},
name : 'adminState',
minWidth : 60
} ,{
display : '账号权限',
render : function(rowdata, rowindex, value){
if(value==0)
return "普通考勤员";
else
return "超级管理员";
},
editor: { type: 'select',
data:[{id:"1",text:"超级管理员"},{id:"0",text:"普通考勤员"}],
},
name : 'adminRight',
minWidth : 60
} ,{
display : '用户名称',
editor: { type: 'text' },
name : 'adminName',
minWidth : 60
} ,{ display: '操作',
minWidth : 140,
width: 140,
render: function (rowdata, rowindex, value)
{
var h = "";
if (!rowdata._editing)
{
h += "<a href='javascript:beginEdit(" + rowindex + ")'>修改</a> ";
h += "<a href='javascript:deleteRow(" + rowindex + ")'>删除</a> ";
}
else
{
h += "<a href='javascript:endEdit(" + rowindex + ")'>提交</a> ";
h += "<a href='javascript:cancelEdit(" + rowindex + ")'>取消</a> ";
}
return h;
}
}],
pageSize : 10,
enabledEdit: true,//是否允许编辑
clickToEdit:false,//是否允许点击单一表格编辑
isScroll: true,//是否固定底部
data : $.extend(true, {}, queryData),
width : '100%',
height : '100%'
});
}
function beginEdit(rowid) {
grid.beginEdit(rowid);
}
function cancelEdit(rowid) {
grid.cancelEdit(rowid);
}
function deleteRow(rowindex) {
var val = grid.getSelected(rowindex);
if (confirm('确定删除?')) {
$.ajax({
type : "POST",
data :val,
url : "user.do?opt=delete&timeid="+ new Date().getTime(),
success : function(msg) {
alert(msg);
window.location.reload();
}
});
}
}
function endEdit(rowindex) {
grid.endEdit(rowindex);
var val = grid.getSelected(rowindex);
if (confirm('确定修改？')) {
$.ajax({
type : "POST",
datatype : "text",
data : val,
url : "user.do?opt=update&timeid=" + new Date().getTime(),
success : function(msg) {
alert(msg);
window.location.reload();
}
});
}
}
</script>
</head>
<body style="padding: 6px; overflow: hidden;">
<div id="searchbar">
用户编号：<input id="userno" type="text" oninput ="value=value.replace(/[^\d]/g,'')"/>
<input id="btnQuery" type="button" value="查询" />
用户名称(模糊查询)：<input id="Byname" type="text" />
<input id="btnQueryByName" type="button" value="查询" />
<input type="button" onclick="f_open()" value="增加用户" style="float: right;width: 200px"/>
</div>
<div id="divBody" style="margin: 0; padding: 0"></div>
</body>
</html>此部分利用的ligerUI的前端框架与部分自己完成的js代码，利用ajax异步提交到服务器，获取在数据库中储存的用户数据，同时利用异步实现对数据的增删改查。
C.服务器端（controller层）
/**
*@烟台大学 计算机与控制工程学院
*@author 田志伟
*@2017-8
*/
package com.ambow.controller;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import com.alibaba.fastjson.JSON;
import com.ambow.DAO.userDao;
import com.ambow.entity.Admin;
import com.ambow.factory.userFactory;
import com.ambow.util.JSONUtil;
@WebServlet("/user.do")
public class UserController extends HttpServlet {
private static final long serialVersionUID = 1L;
public UserController() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
ArrayList<Admin> list = new ArrayList<Admin>();
PrintWriter out = response.getWriter();
String opt = request.getParameter("opt");
userDao userDao = userFactory.getUserInstance();
if(opt.equals("add")) {
String AdminAccount = request.getParameter("AdminAccount");
String AdminPwd = request.getParameter("AdminPwd");
int AdminState = Integer.parseInt(request.getParameter("AdminState"));
int AdminRight = Integer.parseInt(request.getParameter("AdminRight"));
String AdminName = request.getParameter("AdminName");
Admin user = new Admin(0, AdminAccount, AdminPwd, AdminState, AdminRight, AdminName);
try {
userDao.add(user);
out.write("success");
} catch (SQLException e) {
out.write("faied");
e.printStackTrace();
}
}else if(opt.equals("queryAll")) {
try {
list = userDao.queryAll();
String json = JSONUtil.AdminJSONString(list);
out.write(json);
} catch (Exception e) {
e.printStackTrace();
}
}else if(opt.equals("queryAllnotHashMap")) {
try {
list = userDao.queryAll();
String json = JSON.toJSONString(list);
out.write(json);
} catch (Exception e) {
e.printStackTrace();
}
}else if(opt.equals("queryByPK")) {
Admin user = new Admin();
int userID = Integer.parseInt(request.getParameter("num"));
try {
user = userDao.queryByPK(userID);
if(user.getAdminID()!=0) {
list.add(user);
}
String json = JSONUtil.AdminJSONString(list);
out.write(json);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else if(opt.equals("queryByName")) {
String name = request.getParameter("Byname");
try {
list = userDao.queryByName(name);
String json = JSONUtil.AdminJSONString(list);
out.write(json);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else if(opt.equals("queryByPK2")) {
Admin user = new Admin();
int userID = Integer.parseInt(request.getParameter("num"));
try {
user = userDao.queryByPK2(userID);
if(user.getAdminID()!=0) {
list.add(user);
}
String json = JSONUtil.AdminJSONString(list);
out.write(json);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else if(opt.equals("queryByName2")) {
String name = request.getParameter("Byname");
try {
list = userDao.queryByName2(name);
String json = JSONUtil.AdminJSONString(list);
out.write(json);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else if(opt.equals("update")) {
int userID = Integer.parseInt(request.getParameter("adminID"));
String AdminAccount = request.getParameter("adminAccount");
String AdminPwd = request.getParameter("adminPwd");
int AdminState = Integer.parseInt(request.getParameter("adminState"));
int AdminRight = Integer.parseInt(request.getParameter("adminRight"));
String AdminName = request.getParameter("adminName");
Admin user = new Admin(userID, AdminAccount, AdminPwd, AdminState, AdminRight, AdminName);
try {
userDao.update(user);
out.write("修改成功！");
} catch (SQLException e) {
out.write("修改失败！");
e.printStackTrace();
}
}else if(opt.equals("delete")) {
int userID = Integer.parseInt(request.getParameter("adminID"));
try {
userDao.delete(userID);
out.write("删除成功！");
} catch (SQLException e) {
out.write("删除失败！");
e.printStackTrace();
}
}else if(opt.equals("querySuperAdmin")) {
try {
list = userDao.querySuperAdmin();
String json = JSONUtil.AdminJSONString(list);
out.write(json);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else if(opt.equals("updatePwd")) {
String code = request.getParameter("code");
String vcode = (String)request.getSession().getAttribute("vCode");
String name = (String)request.getSession().getAttribute("loginname");
String pwd = (String)request.getSession().getAttribute("password");
String newPwd = request.getParameter("newPwd");
String oldPwd = request.getParameter("oldPwd");
if(code.equalsIgnoreCase(vcode)) {
if(oldPwd.equals(pwd)) {
try {
userDao.updatePwd(name,newPwd);
HttpSession session = request.getSession();
session.removeAttribute("password");
session.setAttribute("password", newPwd);
out.write("修改成功 ");
} catch (SQLException e) {
out.print("未知错误，修改失败！");
}
}else
out.print("原密码错误！修改失败！");
}else
out.print("验证码错误！修改失败！");
}else if(opt.equals("getUsername")) {
HttpSession session = request.getSession();
String username = (String)session.getAttribute("loginname");
out.write(username);
}
out.flush();
out.close();
}
}
本部分使用了fasterjson中的一些方法，需要的读者可去自行下载，本项目的其他jar包也在其中：密码：pou4。本servlet中还存在着部分对权限控制的代码，利用服务器端封装的session对象进行登录用户的类型进行判断，在view层（HTML端）进行控制显示。
（3）功能说明：
此模块的为对用户的增删改查，其中实体类的AdminID对应数据库中的主键，AdminAccount为用户登录账号，AdminRight为账户的权限（即为超级管理员与考勤员的区别），在过滤器部分限制权限也是靠的AdminRight字段。
（4）其他相关代码：
操作层接口
package com.ambow.DAO;
import java.sql.SQLException;
import java.util.ArrayList;
import com.ambow.entity.Admin;
public interface userDao {
public void add(Admin user) throws SQLException;
public ArrayList<Admin> queryAll() throws SQLException;
public Admin queryByPK(int userID) throws SQLException;
public ArrayList<Admin> queryByName(String name) throws SQLException;
public void update(Admin user) throws SQLException;
public void delete(int userID) throws SQLException;
public ArrayList<Admin> querySuperAdmin() throws SQLException;
public Admin queryByPK2(int userID) throws SQLException;
public ArrayList<Admin> queryByName2(String name)throws SQLException;
public boolean exits(String username, String password)throws SQLException;
public void updatePwd(String name, String newPwd)throws SQLException;
public int queryState(String name)throws SQLException;
}
Oracle实现层（MySQL与其大同小异，不再贴出）
package com.ambow.DAOImp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.ambow.DAO.userDao;
import com.ambow.DB.DBConnection;
import com.ambow.entity.Admin;
public class userDAOImp implements userDao{
/***
* @author TianZW
* @throws SQLException
* */
public void add(Admin user) throws SQLException {
Connection conn = DBConnection.getConnection();
String sql = "insert into att_Admin(AdminAccount,AdminPwd,AdminState,AdminRight,AdminName) values(?,?,?,?,?)";
PreparedStatement state =conn.prepareStatement(sql);
state.setString(1, user.getAdminAccount());
state.setString(2,user.getAdminPwd());
state.setInt(3,user.getAdminState());
state.setInt(4, user.getAdminRight());
state.setString(5, user.getAdminName());
state.execute();
DBConnection.closeConnection(conn, state, null);
}
/**
* @author TianZW
* @return ArrayList<Admin>
* @throws SQLException
* */
public ArrayList<Admin> queryAll() throws SQLException {
ArrayList<Admin> list = new ArrayList<Admin>();
Connection conn = DBConnection.getConnection();
String sql = "select AdminID,AdminAccount,"
+ "AdminPwd,AdminState,AdminRight,AdminName from att_Admin";
PreparedStatement state =conn.prepareStatement(sql);
ResultSet rs = state.executeQuery();
while(rs.next()) {
int AdminID = rs.getInt("AdminID");
String AdminAccount = rs.getString("AdminAccount");
String AdminPwd = rs.getString("AdminPwd");
int AdminState = rs.getInt("AdminState");
int AdminRight = rs.getInt("AdminRight");
String AdminName = rs.getString("AdminName");
Admin admin = new Admin(AdminID, AdminAccount, AdminPwd, AdminState, AdminRight, AdminName);
list.add(admin);
}
DBConnection.closeConnection(conn, state, rs);
return list;
}
/**
* @author TianZW
* @return Admin
* @throws SQLException
* */
public Admin queryByPK(int userID) throws SQLException {
Admin user = new Admin();
Connection conn = DBConnection.getConnection();
String sql = "select AdminAccount,"
+ "AdminPwd,AdminState,AdminRight,AdminName from att_Admin where AdminID = ?";
PreparedStatement state =conn.prepareStatement(sql);
state.setInt(1, userID);
ResultSet rs = state.executeQuery();
if(rs.next()) {
user.setAdminID(userID);
user.setAdminAccount(rs.getString("AdminAccount"));
user.setAdminPwd(rs.getString("AdminPwd"));
user.setAdminState(rs.getInt("AdminState"));
user.setAdminRight( rs.getInt("AdminRight"));
user.setAdminName(rs.getString("AdminName"));
}
DBConnection.closeConnection(conn, state, rs);
return user;
}
/**
* @author TianZW
* @return ArrayList<Admin>
* @throws SQLException
* */
public ArrayList<Admin> queryByName(String name) throws SQLException {
ArrayList<Admin> list = new ArrayList<Admin>();
Connection conn = DBConnection.getConnection();
String sql = "select AdminID,AdminAccount,"
+ "AdminPwd,AdminState,AdminRight,AdminName from att_Admin where AdminAccount like ?";
PreparedStatement state =conn.prepareStatement(sql);
state.setString(1, "%"+name+"%");
ResultSet rs = state.executeQuery();
while(rs.next()) {
int AdminID = rs.getInt("AdminID");
String AdminAccount = rs.getString("AdminAccount");
String AdminPwd = rs.getString("AdminPwd");
int AdminState = rs.getInt("AdminState");
int AdminRight = rs.getInt("AdminRight");
String AdminName = rs.getString("AdminName");
Admin admin = new Admin(AdminID, AdminAccount, AdminPwd, AdminState, AdminRight, AdminName);
list.add(admin);
}
DBConnection.closeConnection(conn, state, rs);
return list;
}
/***
* @author TianZW
* @throws SQLException
* */
public void update(Admin user) throws SQLException {
Connection conn = DBConnection.getConnection();
String sql = "update att_Admin SET adminAccount=?,adminPwd=?,adminState=?"
+ ",adminRight=?,adminName=? where AdminID = ?";
PreparedStatement state = conn.prepareStatement(sql);
state.setString(1, user.getAdminAccount());
state.setString(2,user.getAdminPwd());
state.setInt(3,user.getAdminState());
state.setInt(4, user.getAdminRight());
state.setString(5, user.getAdminName());
state.setInt(6, user.getAdminID());
state.executeUpdate();
DBConnection.closeConnection(conn, state, null);
}
/***
* @author TianZW
* @throws SQLException
* */
public void delete(int userID) throws SQLException {
Connection conn = DBConnection.getConnection();
String sql = "delete from att_Admin where AdminID = ?";
PreparedStatement state = conn.prepareStatement(sql);
state.setInt(1, userID);
state.execute();
DBConnection.closeConnection(conn, state, null);
}
/***
* @author TianZW
* @throws SQLException
* */
public ArrayList<Admin> querySuperAdmin() throws SQLException {
ArrayList<Admin> list = new ArrayList<Admin>();
Connection conn = DBConnection.getConnection();
String sql = "select AdminID,AdminAccount,"
+ "AdminState,AdminRight,AdminName from att_Admin where AdminRight = 0 and AdminState = 1";
PreparedStatement state =conn.prepareStatement(sql);
ResultSet rs = state.executeQuery();
while(rs.next()) {
int AdminID = rs.getInt("AdminID");
String AdminAccount = rs.getString("AdminAccount");
int AdminState = rs.getInt("AdminState");
int AdminRight = rs.getInt("AdminRight");
String AdminName = rs.getString("AdminName");
Admin admin = new Admin(AdminID, AdminAccount, AdminState, AdminRight, AdminName);
list.add(admin);
}
DBConnection.closeConnection(conn, state, rs);
return list;
}
public Admin queryByPK2(int userID) throws SQLException {
Admin user = new Admin();
Connection conn = DBConnection.getConnection();
String sql = "select AdminAccount,"
+ "AdminPwd,AdminState,AdminRight,AdminName from att_Admin where AdminID = ?"
+ " and AdminRight = 0 and AdminState = 1";
PreparedStatement state =conn.prepareStatement(sql);
state.setInt(1, userID);
ResultSet rs = state.executeQuery();
if(rs.next()) {
user.setAdminID(userID);
user.setAdminAccount(rs.getString("AdminAccount"));
user.setAdminPwd(rs.getString("AdminPwd"));
user.setAdminState(rs.getInt("AdminState"));
user.setAdminRight( rs.getInt("AdminRight"));
user.setAdminName(rs.getString("AdminName"));
}
DBConnection.closeConnection(conn, state, rs);
return user;
}
/**
* @author TianZW
* @return ArrayList<Admin>
* @throws SQLException
* */
public ArrayList<Admin> queryByName2(String name) throws SQLException {
ArrayList<Admin> list = new ArrayList<Admin>();
Connection conn = DBConnection.getConnection();
String sql = "select AdminID,AdminAccount,"
+ "AdminPwd,AdminState,AdminRight,AdminName from att_Admin where AdminAccount like ? "
+ "and AdminRight = 0 and AdminState = 1";
PreparedStatement state =conn.prepareStatement(sql);
state.setString(1, "%"+name+"%");
ResultSet rs = state.executeQuery();
while(rs.next()) {
int AdminID = rs.getInt("AdminID");
String AdminAccount = rs.getString("AdminAccount");
String AdminPwd = rs.getString("AdminPwd");
int AdminState = rs.getInt("AdminState");
int AdminRight = rs.getInt("AdminRight");
String AdminName = rs.getString("AdminName");
Admin admin = new Admin(AdminID, AdminAccount, AdminPwd, AdminState, AdminRight, AdminName);
list.add(admin);
}
DBConnection.closeConnection(conn, state, rs);
return list;
}
/**
* @author TianZW
* @return boolean
* @throws SQLException
* */
public boolean exits(String username, String password) throws SQLException {
boolean judge = false;
Connection conn = DBConnection.getConnection();
String sql = "select AdminID from att_Admin where AdminAccount = ? and AdminPwd = ? and AdminState!=0";
PreparedStatement state =conn.prepareStatement(sql);
state.setString(1, username);
state.setString(2, password);
ResultSet rs = state.executeQuery();
if(rs.next()) {
judge = true;
}
DBConnection.closeConnection(conn, state, rs);
return judge;
}
/**
* @author TianZW
* @throws SQLException
* */
public void updatePwd(String name, String newPwd) throws SQLException {
Connection conn = DBConnection.getConnection();
String sql = "update att_Admin SET adminPwd=? where adminAccount = ?";
PreparedStatement state = conn.prepareStatement(sql);
state.setString(2, name);
state.setString(1,newPwd);
state.executeUpdate();
DBConnection.closeConnection(conn, state, null);
}
@Override
public int queryState(String name) throws SQLException {
int num = 0;
Connection conn = DBConnection.getConnection();
String sql = "select AdminRight from att_Admin where AdminAccount = ?";
PreparedStatement state =conn.prepareStatement(sql);
state.setString(1, name);
ResultSet rs = state.executeQuery();
if(rs.next()) {
num = rs.getInt("AdminRight");
}
DBConnection.closeConnection(conn, state, rs);
return num;
}
}
用户的添加
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加职位</title>
<script src="js/ligerUI/jquery/jquery-1.9.0.min.js" type="text/javascript"></script>
<script type="text/javascript">
var dialog = frameElement.dialog; //调用页面的dialog对象(ligerui对象)
$(function(){
$("#confirm").click(function(){
var t1 = $("#AdminAccount").val();
var t2 = $("#AdminPwd").val();
var t3 = $("#AdminPwd2").val();
var t4 = $("#AdminName").val();
if(t1==""||t2==""||t4=="")
alert("各项均不能为空!");
else if(t2==t3){
$.ajax({
url:"user.do?opt=add&timeid="+new Date().getTime(),
type:"POST",
data : $("#bodyForm").serialize(),
success:function(msg){
alert(msg);
}
});
dialog.close();//关闭dialog
}else{
alert("两次密码输入不一致！");
}
window.location.reload();
});
});
/**********************************************************************/
</script>
</head>
<body>
<form id = "bodyForm">
<table style="text-align:center;background-color: #D1EEEE" border="1">
<tr style="text-align:center">
<td style="text-align:center">账号</td>
<td style="text-align:center"><input type="text" id="AdminAccount" name="AdminAccount" oninput="this.value=this.value.replace(/^\s+$/gi,'');" /></td>
</tr>
<tr style="text-align:center">
<td style="text-align:center">密码</td>
<td style="text-align:center"><input type="password" id="AdminPwd" name="AdminPwd" oninput="this.value=this.value.replace(/^\s+$/gi,'');"/></td>
</tr>
<tr style="text-align:center">
<td style="text-align:center">重复密码</td>
<td style="text-align:center"><input type="password" id="AdminPwd2" oninput="this.value=this.value.replace(/^\s+$/gi,'');"/></td>
</tr>
<tr style="text-align:center">
<td style="text-align:center">账号状态</td>
<td style="text-align:center">
<select id ="AdminState" name ="AdminState">
<option value = "0">停用</option>
<option value = "1">启用</option>
</select>
</td>
</tr>
<tr style="text-align:center">
<td style="text-align:center">管理权限</td>
<td style="text-align:center">
<select id = "AdminRight" name = "AdminRight">
<option value = "0">停用</option>
<option value = "1">启用</option>
</select>
</td>
</tr>
<tr style="text-align:center">
<td style="text-align:center">用户名</td>
<td style="text-align:center"><input type="text" id="AdminName" name="AdminName" oninput="this.value=this.value.replace(/^\s+$/gi,'');"/></td>
</tr>
<tr style="text-align:center">
<td style="text-align:center"><input type="button" value = "提交" id = "confirm"/></td>
<td style="text-align:center"><input type="reset" value = "清除"/></td>
</tr>
</table>
</form>
</body>
</html>用户的查询功能（可对考勤员开放，仅供查询用）
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>职位查询</title>
<link href="js/ligerUI/ligerUI/skins/Aqua/css/ligerui-all.css"
rel="stylesheet" type="text/css" />
<link href="js/ligerUI/ligerUI/skins/ligerui-icons.css" rel="stylesheet" type="text/css" />
<script src="js/ligerUI/jquery/jquery-1.9.0.min.js"
type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/core/base.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerGrid.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerDialog.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerTextBox.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerCheckBox.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerComboBox.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerGrid.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerDateEditor.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerSpinner.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerResizable.js" type="text/javascript"></script>
<script type="text/javascript">
var grid = null;
var numReg ="^(0|[1-9][0-9]*)\$";//数字的正则表达式
$(function() {
$.ajax({
type : "POST",
url : "user.do?opt=queryAll&timeid="+new Date().getTime(),
success : function(list) {
setgrid(list);
},
dataType : "json"
});
//设置表格分页
$("#pageloading").hide();
$("#btnQuery").click(function(){
var num = $("#userno").val();
if(num.match(numReg)){
$.ajax({
type : "POST",
data : {'num' : num},
url : "user.do?opt=queryByPK&timeid="+new Date().getTime(),
success : function(result) {
setgrid(result);
},dataType : "json"
});
}else
window.location.reload();
});
$("#btnQueryByName").click(function(){
var Byname = $("#Byname").val();
$.ajax({
type : "POST",
data :{'Byname':Byname},
url : "user.do?opt=queryByName&timeid=" + new Date().getTime(),
success : function(result) {
setgrid(result);
},dataType : "json"
});
});
});
/***************************************************************************************************/
function setgrid(queryData) {
grid = $("#divBody").ligerGrid({
columns : [
{
display : '用户编号',
name : 'adminID',
minWidth : 60
},{
display : '用户账号',
editor: { type: 'text' },
name : 'adminAccount',
minWidth : 60
} ,{
display : '账号状态',
render : function(rowdata, rowindex, value){
if(value==0)
return "停用";
else
return "启用";
},
editor: { type: 'text' },
name : 'adminState',
minWidth : 60
} ,{
display : '账号权限',
render : function(rowdata, rowindex, value){
if(value==0)
return "超级管理员";
else
return "普通考勤员";
},
editor: { type: 'int' },
name : 'adminRight',
minWidth : 60
} ,{
display : '用户名称',
editor: { type: 'text' },
name : 'adminName',
minWidth : 60
} ],
pageSize : 10,
enabledEdit: true,//是否允许编辑
clickToEdit:false,//是否允许点击单一表格编辑
isScroll: true,//是否固定底部
data : $.extend(true, {}, queryData),
width : '100%',
height : '100%'
});
}
</script>
</head>
<body style="padding: 6px; overflow: hidden;">
<div id="searchbar" style="height: 30px" >
用户编号：<input id="userno" type="text" oninput ="value=value.replace(/[^\d]/g,'')"/>
<input id="btnQuery" type="button" value="查询" />
用户名称(模糊查询)：<input id="Byname" type="text" />
<input id="btnQueryByName" type="button" value="查询" />
</div>
<div id="divBody" style="margin: 0; padding: 0 "></div>
</body>
</html>2.部门管理模块--对部门表的增删改查
(1)前端功能演示图
管理部门界面：
添加部门弹出页面：
删除部门：
修改部门界面同修改用户信息，在此不做赘述。
（2）核心代码：
A.实体层（Model）
同用户表，只给出了字段名称，不做赘述。
package com.ambow.entity;
/**
* @author TianZW
* @部门表
* */
public class Department {
private int DepartmentID ;//考勤状态表数据ID
private String DepartmentName ;//部门名称
private String StartTimeAM ;//上午上班时间
private String EndTimeAM ;//上午下班时间
private String StartTimePM ;//下午上班时间
private String EndTimePM ;//下午下班时间
private int ParentID ;//父级部门编号
}B.控制层（Controller）
/**
*@烟台大学 计算机与控制工程学院
*2017-8 田志伟
*/
package com.ambow.controller;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.alibaba.fastjson.JSON;
import com.ambow.DAO.deptDao;
import com.ambow.entity.Department;
import com.ambow.factory.deptFactory;
import com.ambow.util.JSONUtil;
@WebServlet("/dept.do")
public class DepartmentController extends HttpServlet {
private static final long serialVersionUID = 1L;
public DepartmentController() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
deptDao deptDao = deptFactory.getDeptInstance();
ArrayList<Department> deptList = new ArrayList<Department>();
PrintWriter out = response.getWriter();
String opt = request.getParameter("opt");
if(opt.equals("queryAll")) {
try {
deptList=deptDao.queryAll();
} catch (SQLException e) {
e.printStackTrace();
}
String json = JSONUtil.DepartmenttoJSONString(deptList);
out.write(json);
}else if(opt.equals("queryDept")) {
try {
deptList=deptDao.querySonDept();
} catch (SQLException e) {
e.printStackTrace();
}
String json = JSON.toJSONString(deptList);
out.write(json);
}else if(opt.equals("queryFirst")) {
try {
deptList=deptDao.queryFirst();
} catch (SQLException e) {
e.printStackTrace();
}
String json = JSON.toJSONString(deptList);
out.write(json);
}else if(opt.equals("queryFirst2")) {
try {
deptList=deptDao.queryFirst();
} catch (SQLException e) {
e.printStackTrace();
}
String json = JSONUtil.DepartmenttoJSONString(deptList);
out.write(json);
}else if(opt.equals("querySecond")) {
int parentID = Integer.parseInt(request.getParameter("parentID"));
try {
deptList=deptDao.querySecond(parentID);
} catch (SQLException e) {
e.printStackTrace();
}
String json = JSON.toJSONString(deptList);
out.write(json);
}else if(opt.equals("queryByPK")) {
Department dept = new Department();
int deptno = -1;
try{
deptno = Integer.parseInt(request.getParameter("deptno"));
}catch(Exception e) {
deptno = -1;
}
try {
dept = deptDao.queryByPK(deptno);
deptList.add(dept);
} catch (SQLException e) {
e.printStackTrace();
}
String json = JSONUtil.DepartmenttoJSONString(deptList);
out.write(json);
}else if(opt.equals("queryByName")) {
String name = request.getParameter("deptname");
try {
deptList = deptDao.queryByName(name);
String json = JSONUtil.DepartmenttoJSONString(deptList);
out.write(json);
} catch (SQLException e) {
e.printStackTrace();
}
}else if(opt.equals("delete")) {
int deptno = Integer.parseInt(request.getParameter("deptno"));
try {
if(deptDao.exitsSon(deptno)) {
if(deptDao.exitsEmp(deptno)) {
deptDao.delete(deptno);
out.write("删除成功！");
}
else
out.write("该部门有子员工，不可删除!");
}else {
out.write("该部门有子部门不可删除！");
}
} catch (Exception e) {
e.printStackTrace();
}
} else if(opt.equals("update")) {
int departmentID = Integer.parseInt(request.getParameter("departmentID"));
int parentID = Integer.parseInt(request.getParameter("parentID"));
try {
if(deptDao.exitsDept(parentID,departmentID)||parentID==0) {
String departmentName = request.getParameter("departmentName");
String startTimeAM = request.getParameter("startTimeAM");
String endTimeAM = request.getParameter("endTimeAM");
String startTimePM = request.getParameter("startTimePM");
String endTimePM = request.getParameter("endTimePM");
Department deptval = new Department(departmentID,departmentName,startTimeAM,
endTimeAM,startTimePM,endTimePM,parentID);
if(judgeTime(startTimeAM,endTimeAM,startTimePM,endTimePM)) {
try {
deptDao.update(deptval);
} catch (SQLException e) {
e.printStackTrace();
}
out.write("修改成功！");
}else {
out.write("时间有误！请检查格式（HH:MM）");
}
}else
out.write("父级部门编号错误！");
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}else if(opt.equals("add")) {
int departmentID = 0;
String departmentName = request.getParameter("departmentName");
String startTimeAM = request.getParameter("startTimeAM");
String endTimeAM = request.getParameter("endTimeAM");
String startTimePM = request.getParameter("startTimePM");
String endTimePM = request.getParameter("endTimePM");
int parentID;
try {
parentID = Integer.parseInt(request.getParameter("parentID"));
} catch (Exception e) {
parentID = 0;
}
Department deptval = new Department(departmentID,departmentName,startTimeAM,
endTimeAM,startTimePM,endTimePM,parentID);
try {
deptDao.add(deptval);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else if(opt.equals("manageSonDept")) {
int deptno = Integer.parseInt(request.getParameter("deptno"));
try {
deptList = deptDao.querySon(deptno);
String json = JSONUtil.DepartmenttoJSONString(deptList);
out.write(json);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
out.flush();
out.close();
}
/**
*@return boolean
*@function : judge time format
* */
private boolean judgeTime(String s1, String s2, String s3, String s4) {
//验证时间格式是否正确
String regTime = "(0\\d{1}|1\\d{1}|2[0-3]):([0-5]\\d{1})";
String regTime2 = "(0\\d{1}|1\\d{1}|2[0-3])：([0-5]\\d{1})";
ArrayList<String> list = new ArrayList<String>();
list.add(s1);
list.add(s2);
list.add(s3);
list.add(s4);
try {
for (String i : list) {
if (i .matches(regTime)) {
}
else if(i .matches(regTime2)) {
}else {
return false;
}
}
} catch (Exception e) {
return false;
}
return true;
}
}
（3）功能说明：
此模块是对部门表的增删改查，但是需要注意的一点就是在删除部门表时需要判定该部门是否含有子记录（是否有子部门或者员工等），具体实现可在其他相关代码的数据库实现代码中查看。
（4）其他相关代码
添加部门弹出框代码：
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>新增部门</title>
<link href="js/ligerUI/ligerUI/skins/Aqua/css/ligerui-all.css" rel="stylesheet"
type="text/css" />
<link rel="stylesheet" type="text/css" id="mylink" />
<script src="js/ligerUI/jquery/jquery-1.9.0.min.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/ligerui.all.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerPopupEdit.js"></script>
<script type="text/javascript">
var dialog = frameElement.dialog; //调用页面的dialog对象(ligerui对象)
$(function(){
$("#confirm").click(function(){
$.ajax({
url:"dept.do?opt=add&timeid="+new Date().getTime(),
type:"POST",
data : $("#formBody").serialize(),
complete:function(){
alert("新增成功!");
},dataType : "json"
});
dialog.close();//关闭dialog
});
$("#popTxt").ligerPopupEdit({
grid: getGridOptions(false),
valueField: 'departmentID',
textField: 'departmentID',
width: 133
});
function getGridOptions(checkbox) {
var options = {
columns: [
{
display : '部门编号',
name : 'departmentID',
minWidth : 60
},{
display : '部门名称',
editor: { type: 'text' },
name : 'departmentName',
minWidth : 60
}, {
display : '上午上班时间',
editor: { type: 'text' },
name : 'startTimeAM',
width : 150,
align : 'left'
}, {
display : '上午下班时间',
editor: { type: 'text' },
name : 'endTimeAM',
minWidth : 140
}, {
display : '下午上班时间',
editor: { type: 'text' },
name : 'startTimePM',
minWidth : 140
}, {
display : '下午下班时间',
editor: { type: 'text' },
name : 'endTimePM',
minWidth : 140
}, {
display : '父级部门账号',
editor: { type: 'int'},
name : 'parentID',
minWidth : 140
}
], switchPageSizeApplyComboBox: false,
url : "dept.do?opt=queryFirst2&&timeid="+new Date().getTime(),
pageSize: 10,
checkbox: false
};
return options;
}
});
</script>
</head>
<body style="padding:10px">
<form id = "formBody">
<table border="1" bgcolor="#D1EEEE">
<tr>
<td style="text-align:center">部门名称：</td>
<td style="text-align:center"><input type="text" id="deptname" name = "departmentName"/></td>
</tr>
<tr>
<td style="text-align:center">上午上班时间</td>
<td style="text-align:center"><input type="time" id="deptno" name = "startTimeAM"/></td>
</tr>
<tr>
<td style="text-align:center">上午下班时间</td>
<td style="text-align:center"><input type="time" id="deptno" name = "endTimeAM"/></td>
</tr>
<tr>
<td style="text-align:center">下午上班时间</td>
<td style="text-align:center"><input type="time" id="deptno" name = "startTimePM"/></td>
</tr>
<tr>
<td style="text-align:center">下午下班时间</td>
<td style="text-align:center"><input type="time" id="deptno" name = "endTimePM"/></td>
</tr>
<tr>
<td style="text-align:center">父级部门(不填写默认为一级部门)：</td>
<td style="text-align:center"><input type="text" id="popTxt" name = "parentID"/> </td>
</tr>
<tr>
<td style="text-align:center"><input type="button" value = "提交" id = "confirm"/></td>
<td style="text-align:center"><input type="reset" value = "清除"/></td>
</tr>
</table>
</form>
</body>
</html>
数据库实现代码：
package com.ambow.DAOImp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.ambow.DAO.userDao;
import com.ambow.DB.DBConnection;
import com.ambow.entity.Admin;
public class userDAOImp implements userDao{
/***
* @author TianZW
* @throws SQLException
* */
public void add(Admin user) throws SQLException {
Connection conn = DBConnection.getConnection();
String sql = "insert into att_Admin(AdminAccount,AdminPwd,AdminState,AdminRight,AdminName) values(?,?,?,?,?)";
PreparedStatement state =conn.prepareStatement(sql);
state.setString(1, user.getAdminAccount());
state.setString(2,user.getAdminPwd());
state.setInt(3,user.getAdminState());
state.setInt(4, user.getAdminRight());
state.setString(5, user.getAdminName());
state.execute();
DBConnection.closeConnection(conn, state, null);
}
/**
* @author TianZW
* @return ArrayList<Admin>
* @throws SQLException
* */
public ArrayList<Admin> queryAll() throws SQLException {
ArrayList<Admin> list = new ArrayList<Admin>();
Connection conn = DBConnection.getConnection();
String sql = "select AdminID,AdminAccount,"
+ "AdminPwd,AdminState,AdminRight,AdminName from att_Admin";
PreparedStatement state =conn.prepareStatement(sql);
ResultSet rs = state.executeQuery();
while(rs.next()) {
int AdminID = rs.getInt("AdminID");
String AdminAccount = rs.getString("AdminAccount");
String AdminPwd = rs.getString("AdminPwd");
int AdminState = rs.getInt("AdminState");
int AdminRight = rs.getInt("AdminRight");
String AdminName = rs.getString("AdminName");
Admin admin = new Admin(AdminID, AdminAccount, AdminPwd, AdminState, AdminRight, AdminName);
list.add(admin);
}
DBConnection.closeConnection(conn, state, rs);
return list;
}
/**
* @author TianZW
* @return Admin
* @throws SQLException
* */
public Admin queryByPK(int userID) throws SQLException {
Admin user = new Admin();
Connection conn = DBConnection.getConnection();
String sql = "select AdminAccount,"
+ "AdminPwd,AdminState,AdminRight,AdminName from att_Admin where AdminID = ?";
PreparedStatement state =conn.prepareStatement(sql);
state.setInt(1, userID);
ResultSet rs = state.executeQuery();
if(rs.next()) {
user.setAdminID(userID);
user.setAdminAccount(rs.getString("AdminAccount"));
user.setAdminPwd(rs.getString("AdminPwd"));
user.setAdminState(rs.getInt("AdminState"));
user.setAdminRight( rs.getInt("AdminRight"));
user.setAdminName(rs.getString("AdminName"));
}
DBConnection.closeConnection(conn, state, rs);
return user;
}
/**
* @author TianZW
* @return ArrayList<Admin>
* @throws SQLException
* */
public ArrayList<Admin> queryByName(String name) throws SQLException {
ArrayList<Admin> list = new ArrayList<Admin>();
Connection conn = DBConnection.getConnection();
String sql = "select AdminID,AdminAccount,"
+ "AdminPwd,AdminState,AdminRight,AdminName from att_Admin where AdminAccount like ?";
PreparedStatement state =conn.prepareStatement(sql);
state.setString(1, "%"+name+"%");
ResultSet rs = state.executeQuery();
while(rs.next()) {
int AdminID = rs.getInt("AdminID");
String AdminAccount = rs.getString("AdminAccount");
String AdminPwd = rs.getString("AdminPwd");
int AdminState = rs.getInt("AdminState");
int AdminRight = rs.getInt("AdminRight");
String AdminName = rs.getString("AdminName");
Admin admin = new Admin(AdminID, AdminAccount, AdminPwd, AdminState, AdminRight, AdminName);
list.add(admin);
}
DBConnection.closeConnection(conn, state, rs);
return list;
}
/***
* @author TianZW
* @throws SQLException
* */
public void update(Admin user) throws SQLException {
Connection conn = DBConnection.getConnection();
String sql = "update att_Admin SET adminAccount=?,adminPwd=?,adminState=?"
+ ",adminRight=?,adminName=? where AdminID = ?";
PreparedStatement state = conn.prepareStatement(sql);
state.setString(1, user.getAdminAccount());
state.setString(2,user.getAdminPwd());
state.setInt(3,user.getAdminState());
state.setInt(4, user.getAdminRight());
state.setString(5, user.getAdminName());
state.setInt(6, user.getAdminID());
state.executeUpdate();
DBConnection.closeConnection(conn, state, null);
}
/***
* @author TianZW
* @throws SQLException
* */
public void delete(int userID) throws SQLException {
Connection conn = DBConnection.getConnection();
String sql = "delete from att_Admin where AdminID = ?";
PreparedStatement state = conn.prepareStatement(sql);
state.setInt(1, userID);
state.execute();
DBConnection.closeConnection(conn, state, null);
}
/***
* @author TianZW
* @throws SQLException
* */
public ArrayList<Admin> querySuperAdmin() throws SQLException {
ArrayList<Admin> list = new ArrayList<Admin>();
Connection conn = DBConnection.getConnection();
String sql = "select AdminID,AdminAccount,"
+ "AdminState,AdminRight,AdminName from att_Admin where AdminRight = 0 and AdminState = 1";
PreparedStatement state =conn.prepareStatement(sql);
ResultSet rs = state.executeQuery();
while(rs.next()) {
int AdminID = rs.getInt("AdminID");
String AdminAccount = rs.getString("AdminAccount");
int AdminState = rs.getInt("AdminState");
int AdminRight = rs.getInt("AdminRight");
String AdminName = rs.getString("AdminName");
Admin admin = new Admin(AdminID, AdminAccount, AdminState, AdminRight, AdminName);
list.add(admin);
}
DBConnection.closeConnection(conn, state, rs);
return list;
}
public Admin queryByPK2(int userID) throws SQLException {
Admin user = new Admin();
Connection conn = DBConnection.getConnection();
String sql = "select AdminAccount,"
+ "AdminPwd,AdminState,AdminRight,AdminName from att_Admin where AdminID = ?"
+ " and AdminRight = 0 and AdminState = 1";
PreparedStatement state =conn.prepareStatement(sql);
state.setInt(1, userID);
ResultSet rs = state.executeQuery();
if(rs.next()) {
user.setAdminID(userID);
user.setAdminAccount(rs.getString("AdminAccount"));
user.setAdminPwd(rs.getString("AdminPwd"));
user.setAdminState(rs.getInt("AdminState"));
user.setAdminRight( rs.getInt("AdminRight"));
user.setAdminName(rs.getString("AdminName"));
}
DBConnection.closeConnection(conn, state, rs);
return user;
}
/**
* @author TianZW
* @return ArrayList<Admin>
* @throws SQLException
* */
public ArrayList<Admin> queryByName2(String name) throws SQLException {
ArrayList<Admin> list = new ArrayList<Admin>();
Connection conn = DBConnection.getConnection();
String sql = "select AdminID,AdminAccount,"
+ "AdminPwd,AdminState,AdminRight,AdminName from att_Admin where AdminAccount like ? "
+ "and AdminRight = 0 and AdminState = 1";
PreparedStatement state =conn.prepareStatement(sql);
state.setString(1, "%"+name+"%");
ResultSet rs = state.executeQuery();
while(rs.next()) {
int AdminID = rs.getInt("AdminID");
String AdminAccount = rs.getString("AdminAccount");
String AdminPwd = rs.getString("AdminPwd");
int AdminState = rs.getInt("AdminState");
int AdminRight = rs.getInt("AdminRight");
String AdminName = rs.getString("AdminName");
Admin admin = new Admin(AdminID, AdminAccount, AdminPwd, AdminState, AdminRight, AdminName);
list.add(admin);
}
DBConnection.closeConnection(conn, state, rs);
return list;
}
/**
* @author TianZW
* @return boolean
* @throws SQLException
* */
public boolean exits(String username, String password) throws SQLException {
boolean judge = false;
Connection conn = DBConnection.getConnection();
String sql = "select AdminID from att_Admin where AdminAccount = ? and AdminPwd = ? and AdminState!=0";
PreparedStatement state =conn.prepareStatement(sql);
state.setString(1, username);
state.setString(2, password);
ResultSet rs = state.executeQuery();
if(rs.next()) {
judge = true;
}
DBConnection.closeConnection(conn, state, rs);
return judge;
}
/**
* @author TianZW
* @throws SQLException
* */
public void updatePwd(String name, String newPwd) throws SQLException {
Connection conn = DBConnection.getConnection();
String sql = "update att_Admin SET adminPwd=? where adminAccount = ?";
PreparedStatement state = conn.prepareStatement(sql);
state.setString(2, name);
state.setString(1,newPwd);
state.executeUpdate();
DBConnection.closeConnection(conn, state, null);
}
@Override
public int queryState(String name) throws SQLException {
int num = 0;
Connection conn = DBConnection.getConnection();
String sql = "select AdminRight from att_Admin where AdminAccount = ?";
PreparedStatement state =conn.prepareStatement(sql);
state.setString(1, name);
ResultSet rs = state.executeQuery();
if(rs.next()) {
num = rs.getInt("AdminRight");
}
DBConnection.closeConnection(conn, state, rs);
return num;
}
}
3.职位管理模块--此块实现功能不复杂不多做赘述
（1）前端显示：
（2）实体层
package com.ambow.entity;
/**
* @author TianZW
* @职务表
* */
public class Position {
private int PositionID ;//职务表ID
private String PositionName ;//职务名称
}4.员工管理模块
（1）前端显示
A.修改员工：
B.增加员工弹出框：
C.删除员工（有考勤记录是会弹出提醒）：
（2）核心代码：
A.控制层：
/*
*@烟台大学 计算机与控制工程学院
*@2017-09 田志伟
*/
package com.ambow.controller;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.alibaba.fastjson.JSON;
import com.ambow.DAO.empDao;
import com.ambow.entity.Employees;
import com.ambow.factory.empFactory;
import com.ambow.util.JSONUtil;
@WebServlet("/emp.do")
public class EmployeesController extends HttpServlet {
private static final long serialVersionUID = 1L;
public EmployeesController() {
super();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
ArrayList<Employees> list = new ArrayList<Employees>();
String opt = request.getParameter("opt");
PrintWriter out = response.getWriter();
empDao dao = empFactory.getEmpInstance();
if(opt.equals("add")) {
int EmployeeID = 0;
String EmployeeName = request.getParameter("EmployeeName");
int EmployeeGender = Integer.parseInt(request.getParameter("EmployeeGender"));
int Position = Integer.parseInt(request.getParameter("Position"));
int Department = Integer.parseInt(request.getParameter("Department"));
String CardNumber = request.getParameter("CardNumber");
int EmployeState = Integer.parseInt(request.getParameter("EmployeState"));
String EmployeeMemo = request.getParameter("EmployeeMemo");
Employees emp = new Employees(EmployeeID, EmployeeName, EmployeeGender, Position, Department, CardNumber, EmployeState, EmployeeMemo);
try {
dao.add(emp);
out.write("新增成功!");
} catch (SQLException e) {
out.write("新增失败!");
e.printStackTrace();
}
}else if(opt.equals("queryAll")) {
try {
list = dao.queryAll();
String json = JSONUtil.EmployeesJSONString(list);
out.write(json);
} catch (Exception e) {
e.printStackTrace();
}
}else if(opt.equals("queryAllnotHashMap")) {
try {
list = dao.queryAll();
String json = JSON.toJSONString(list);
out.write(json);
} catch (Exception e) {
e.printStackTrace();
}
}else if(opt.equals("queryByPK")) {
Employees emp = new Employees();
int userID = Integer.parseInt(request.getParameter("num"));
try {
emp = dao.queryByPK(userID);
list.add(emp);
String json = JSONUtil.EmployeesJSONString(list);
out.write(json);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else if(opt.equals("queryByName")) {
String name = request.getParameter("Byname");
try {
list = dao.queryByName(name);
String json = JSONUtil.EmployeesJSONString(list);
out.write(json);
out.flush();
out.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else if(opt.equals("update")) {
int employeeID = Integer.parseInt(request.getParameter("employeeID"));
String employeeName = request.getParameter("employeeName");
int employeeGender = Integer.parseInt(request.getParameter("employeeGender"));
String position = request.getParameter("positionName");
String department = request.getParameter("departmentName");
String cardNumber = request.getParameter("cardNumber");
int employeState = Integer.parseInt(request.getParameter("employeState"));
String employeeMemo = request.getParameter("employeeMemo");
Employees emp = new Employees(employeeID, employeeName, employeeGender, position,department,cardNumber, employeState, employeeMemo);
try {
dao.update(emp);
out.write("修改成功！");
} catch (SQLException e) {
out.write("数据库异常！");
}
}else if(opt.equals("updatedept")) {
int employeeID = Integer.parseInt(request.getParameter("employeeID"));
String employeeName = request.getParameter("employeeName");
int employeeGender = Integer.parseInt(request.getParameter("employeeGender"));
String position = request.getParameter("positionName");
String department = request.getParameter("departmentName");
String cardNumber = request.getParameter("cardNumber");
int employeState = Integer.parseInt(request.getParameter("employeState"));
String employeeMemo = request.getParameter("employeeMemo");
Employees emp = new Employees(employeeID, employeeName, employeeGender, position,department,cardNumber, employeState, employeeMemo);
try {
dao.update(emp);
out.write("修改成功！");
} catch (SQLException e) {
out.write("数据库异常！");
}
}else if(opt.equals("delete")) {
int empID = Integer.parseInt(request.getParameter("employeeID"));
try {
dao.delete(empID);
out.write("删除成功！");
} catch (SQLException e) {
out.write("删除失败，该职员有考勤记录！");
}
}
out.flush();
out.close();
}
}
（3）功能说明：
本模块对应为员工表的增删改查，除去添加员工时的两个外键约束利用了弹窗从数据库直接获取数据防止填写出错外，还加了删除判定（详情参见其他代码）。
（4）其他相关代码：
添加员工弹窗代码：
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>新增员工</title>
<link href="js/ligerUI/ligerUI/skins/Aqua/css/ligerui-all.css" rel="stylesheet"
type="text/css" />
<link rel="stylesheet" type="text/css" id="mylink" />
<script src="js/ligerUI/jquery/jquery-1.9.0.min.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/ligerui.all.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerPopupEdit.js"></script>
<script type="text/javascript">
$(function(){
$("#popTxt2").ligerPopupEdit({
grid: getGridOptions2(false),
valueField: 'positionID',
textField: 'positionID',
width: 133
});
function getGridOptions2(checkbox) {
var options = {
columns: [
{
display : '职务编号',
name : 'positionID',
minWidth : 60
},{
display : '职务名称',
editor: { type: 'text' },
name : 'positionName',
minWidth : 60
} ], switchPageSizeApplyComboBox: false,
url : "Pos.do?opt=queryAll&timeid="+new Date().getTime(),
pageSize: 10,
checkbox: false
};
return options;
}
$("#popTxt").ligerPopupEdit({
grid: getGridOptions(false),
valueField: 'departmentID',
textField: 'departmentID',
width: 133
});
function getGridOptions(checkbox) {
var options = {
columns: [
{
display : '部门编号',
name : 'departmentID',
minWidth : 60
},{
display : '部门名称',
editor: { type: 'text' },
name : 'departmentName',
minWidth : 60
}, {
display : '上午上班时间',
editor: { type: 'text' },
name : 'startTimeAM',
width : 150,
align : 'left'
}, {
display : '上午下班时间',
editor: { type: 'text' },
name : 'endTimeAM',
minWidth : 140
}, {
display : '下午上班时间',
editor: { type: 'text' },
name : 'startTimePM',
minWidth : 140
}, {
display : '下午下班时间',
editor: { type: 'text' },
name : 'endTimePM',
minWidth : 140
}, {
display : '父级部门账号',
editor: { type: 'int'},
name : 'parentID',
minWidth : 140
}
], switchPageSizeApplyComboBox: false,
url : "dept.do?opt=queryAll&timeid="+new Date().getTime(),
pageSize: 10,
checkbox: false
};
return options;
}
/*******************************************************************************************************/
$("#confirm").click(function(){
var t1 = $("#EmployeeName").val();
var t2 = $("#popTxt2").val();
var t3 = $("#popTxt").val();
var t4 = $("#CardNumber").val();
if(t1==""||t2==""||t3==""||t4=="")
alert("除备注外各项均不能为空!");
else {
$.ajax({
url:"emp.do?opt=add&timeid="+new Date().getTime(),
type:"POST",
data : $("#formBody").serialize(),
success:function(msg){
alert(msg);
},datatype:"text"
});
window.location.reload();
}
});
});
</script>
</head>
<body style="padding:10px">
<form id = "formBody">
<table border="1" bgcolor="#D1EEEE">
<tr>
<td style="text-align:center">员工名称：</td>
<td style="text-align:center"><input type="text" id=EmployeeName name = "EmployeeName" oninput="this.value=this.value.replace(/^\s+$/gi,'');"/></td>
</tr>
<tr>
<td style="text-align:center">员工性别</td>
<td style="text-align:center">
<select id = "EmployeeGender" name = "EmployeeGender">
<option value = "1">男</option>
<option value = "0">女</option>
</select>
</td>
</tr>
<tr>
<td style="text-align:center">部门编号：</td>
<td style="text-align:center"><input type="text" id="popTxt" name = "Department"/> </td>
</tr>
<tr>
<td style="text-align:center">职务编号：</td>
<td style="text-align:center"><input type="text" id="popTxt2" name = "Position"/> </td>
</tr>
<tr>
<td style="text-align:center">员工卡号</td>
<td style="text-align:center"><input type="text" id="CardNumber" name = "CardNumber" oninput="this.value=this.value.replace(/^\s+$/gi,'');"/></td>
</tr>
<tr>
<td style="text-align:center">员工状态</td>
<td style="text-align:center">
<select id="EmployeState" name="EmployeState">
<option value="1">正常</option>
<option value="0">停用</option>
</select>
</td>
</tr>
<tr>
<td style="text-align:center">备注</td>
<td style="text-align:center">
<textarea name = "EmployeeMemo"></textarea>
</td>
</tr>
<tr>
<td style="text-align:center"><input type="button" value = "提交" id = "confirm"/></td>
<td style="text-align:center"><input type="reset" value = "清除"/></td>
</tr>
</table>
</form>
</body>
</html>
数据库操作接口：
package com.ambow.DAO;
import java.sql.SQLException;
import java.util.ArrayList;
import com.ambow.entity.Employees;
public interface empDao {
void add(Employees emp) throws SQLException;
ArrayList<Employees> queryAll() throws SQLException;
Employees queryByPK(int userID) throws SQLException;
ArrayList<Employees> queryByName(String name) throws SQLException;
void delete(int empID) throws SQLException;
void update(Employees emp) throws SQLException;
}
数据库实现（Oracle）：
package com.ambow.DAOImp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.ambow.DAO.empDao;
import com.ambow.DB.DBConnection;
import com.ambow.entity.Employees;
public class empDAOImp implements empDao{
/**
* @author TianZW
* @return
* @throws SQLException
* */
public void add(Employees emp) throws SQLException {
System.out.println(1);
Connection conn = DBConnection.getConnection();
String sql = "insert into att_Employees(EmployeeName,EmployeeGender,Position,Department,CardNumber,EmployeState,EmployeeMemo)"
+ "values(?,?,?,?,?,?,?)";
PreparedStatement state =conn.prepareStatement(sql);
state.setString(1, emp.getEmployeeName());
state.setInt(2,emp.getEmployeeGender());
state.setInt(3,emp.getPosition());
state.setInt(4, emp.getDepartment());
state.setString(5,emp.getCardNumber());
state.setInt(6, emp.getEmployeState());
state.setString(7,emp.getEmployeeMemo());
state.executeQuery();
DBConnection.closeConnection(conn, state, null);
}
/**
* @author TianZW
* @return ArrayList<Employees>
* @throws SQLException
* */
public ArrayList<Employees> queryAll() throws SQLException {
ArrayList<Employees> list = new ArrayList<Employees>();
Connection conn = DBConnection.getConnection();
String sql="SELECT ATT_EMPLOYEES.*,ATT_POSITION.POSITIONNAME,ATT_DEPARTMENT.DEPARTMENTNAME from ATT_EMPLOYEES "
+ "LEFT JOIN ATT_POSITION on ATT_EMPLOYEES.POSITION = ATT_POSITION.POSITIONID "
+ "LEFT JOIN ATT_DEPARTMENT on ATT_DEPARTMENT.DEPARTMENTID = ATT_EMPLOYEES.DEPARTMENT";
PreparedStatement state =conn.prepareStatement(sql);
ResultSet rs = state.executeQuery();
System.out.println();
while(rs.next()) {
int employeeID = rs.getInt("EmployeeID");
String employeeName = rs.getString("EmployeeName");
int employeeGender = rs.getInt("EmployeeGender");
/*暂时用不到的本表属性，用来做外键的值*/
int position = rs.getInt("Position");
int department = rs.getInt("Department");
/*暂时用不到的本表属性，用来做外键的值*/
String cardNumber = rs.getString("CardNumber");
int employeState = rs.getInt("EmployeState");
String employeeMemo = rs.getString("EmployeeMemo");
/*本表表中无此字段*/
String positionName = rs.getString("positionName");
String departmentName = rs.getString("departmentName");
/*本表表中无此字段*/
Employees emp = new Employees(employeeID, employeeName, employeeGender, position, department,
cardNumber, employeState, employeeMemo, positionName, departmentName);
list.add(emp);
}
DBConnection.closeConnection(conn, state, rs);
return list;
}
/**
* @author TianZW
* @return Employees
* @throws SQLException
* */
public Employees queryByPK(int empID) throws SQLException {
Employees emp = new Employees();
Connection conn = DBConnection.getConnection();
String sql="SELECT ATT_EMPLOYEES.*,ATT_POSITION.POSITIONNAME,ATT_DEPARTMENT.DEPARTMENTNAME from ATT_EMPLOYEES "
+ "LEFT JOIN ATT_POSITION on ATT_EMPLOYEES.POSITION = ATT_POSITION.POSITIONID "
+ "LEFT JOIN ATT_DEPARTMENT on ATT_DEPARTMENT.DEPARTMENTID = ATT_EMPLOYEES.DEPARTMENT where ATT_EMPLOYEES.EmployeeID = ?";
PreparedStatement state =conn.prepareStatement(sql);
state.setInt(1, empID);
ResultSet rs = state.executeQuery();
if(rs.next()) {
int employeeID = rs.getInt("EmployeeID");
String employeeName = rs.getString("EmployeeName");
int employeeGender = rs.getInt("EmployeeGender");
/*暂时用不到的本表属性，用来做外键的值*/
int position = rs.getInt("Position");
int department = rs.getInt("Department");
/*暂时用不到的本表属性，用来做外键的值*/
String cardNumber = rs.getString("CardNumber");
int employeState = rs.getInt("EmployeState");
String employeeMemo = rs.getString("EmployeeMemo");
/*本表表中无此字段*/
String positionName = rs.getString("positionName");
String departmentName = rs.getString("departmentName");
/*本表表中无此字段*/
emp = new Employees(employeeID, employeeName, employeeGender, position, department,
cardNumber, employeState, employeeMemo, positionName, departmentName);
}
DBConnection.closeConnection(conn, state, rs);
return emp;
}
/**
* @author TianZW
* @return ArrayList<Employees>
* @throws SQLException
* */
public ArrayList<Employees> queryByName(String name) throws SQLException {
ArrayList<Employees> list = new ArrayList<Employees>();
Connection conn = DBConnection.getConnection();
String sql="SELECT ATT_EMPLOYEES.*,ATT_POSITION.POSITIONNAME,ATT_DEPARTMENT.DEPARTMENTNAME from ATT_EMPLOYEES"
+ " LEFT JOIN ATT_POSITION on ATT_EMPLOYEES.POSITION = ATT_POSITION.POSITIONID "
+ "LEFT JOIN ATT_DEPARTMENT on ATT_DEPARTMENT.DEPARTMENTID = ATT_EMPLOYEES.DEPARTMENT where ATT_EMPLOYEES.employeeName like ?";
PreparedStatement state =conn.prepareStatement(sql);
state.setString(1, "%"+name+"%");
ResultSet rs = state.executeQuery();
while(rs.next()) {
int employeeID = rs.getInt("EmployeeID");
String employeeName = rs.getString("EmployeeName");
int employeeGender = rs.getInt("EmployeeGender");
/*暂时用不到的本表属性，用来做外键的值*/
int position = rs.getInt("Position");
int department = rs.getInt("Department");
/*暂时用不到的本表属性，用来做外键的值*/
String cardNumber = rs.getString("CardNumber");
int employeState = rs.getInt("EmployeState");
String employeeMemo = rs.getString("EmployeeMemo");
/*本表表中无此字段*/
String positionName = rs.getString("positionName");
String departmentName = rs.getString("departmentName");
/*本表表中无此字段*/
Employees emp = new Employees(employeeID, employeeName, employeeGender, position, department,
cardNumber, employeState, employeeMemo, positionName, departmentName);
list.add(emp);
}
DBConnection.closeConnection(conn, state, rs);
return list;
}
/**
* @author TianZW
* @throws SQLException
* */
public void delete(int empID) throws SQLException {
Connection conn = DBConnection.getConnection();
String sql="delete from att_Employees where EmployeeID = ?";
PreparedStatement state =conn.prepareStatement(sql);
state.setInt(1, empID);
state.execute();
DBConnection.closeConnection(conn, state, null);
}
/**
* @author TianZW
* @throws SQLException
* */
public void update(Employees emp) throws SQLException {
int posid = -1;
int deptid = -1;
Connection conn = DBConnection.getConnection();
String sql_t="select POSITIONID from ATT_POSITION where ATT_POSITION.POSITIONNAME = ?";
PreparedStatement state_t =conn.prepareStatement(sql_t);
state_t.setString(1, emp.getPositionName());
ResultSet rs = state_t.executeQuery();
if(rs.next()) {
posid = rs.getInt(1);
}
DBConnection.closeConnection(conn, state_t, rs);
conn = DBConnection.getConnection();
sql_t="select DEPARTMENTID from ATT_DEPARTMENT where ATT_DEPARTMENT.DEPARTMENTNAME = ?";
PreparedStatement state_t2 =conn.prepareStatement(sql_t);
state_t2.setString(1, emp.getDepartmentName());
ResultSet rs2 = state_t2.executeQuery();
if(rs2.next()) {
deptid = rs2.getInt(1);
}
DBConnection.closeConnection(conn, state_t2, rs2);
conn = DBConnection.getConnection();
String sql="update att_Employees SET EmployeeName=?,EmployeeGender=?,Position=?,Department=?,CardNumber=?,EmployeState=?,EmployeeMemo=? where EmployeeID = ?";
PreparedStatement state =conn.prepareStatement(sql);
state.setString(1, emp.getEmployeeName());
state.setInt(2,emp.getEmployeeGender());
state.setInt(3,posid);
state.setInt(4,deptid);
state.setString(5,emp.getCardNumber());
state.setInt(6, emp.getEmployeState());
state.setString(7,emp.getEmployeeMemo());
state.setInt(8,emp.getEmployeeID());
state.execute();
DBConnection.closeConnection(conn, state, null);
}
}
5.考勤授权模块
（1）前端显示页面
A.授权：
B.解除授权：
（2）核心代码：
A.前端显示代码（因为多了分区的组件，此处写一下前端代码）
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>考勤授权</title>
<link href="js/ligerUI/ligerUI/skins/Aqua/css/ligerui-all.css" rel="stylesheet" type="text/css" />
<script src="js/ligerUI/jquery/jquery-1.9.0.min.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/core/base.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerLayout.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerGrid.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerDialog.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerTextBox.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerCheckBox.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerComboBox.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerGrid.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerDateEditor.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerSpinner.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerResizable.js" type="text/javascript"></script>
<script type="text/javascript">
var grid = null;
var userID = null;
var numReg ="^(0|[1-9][0-9]*)\$";//数字的正则表达式
$(function() {
$.ajax({
type : "POST",
url : "user.do?opt=querySuperAdmin&timeid="+new Date().getTime(),
success : function(list) {
setgrid(list);
},
dataType : "json"
});
//设置表格分页
$("#pageloading").hide();
$("#btnQuery").click(function(){
var num = $("#userno").val();
if(num.match(numReg)){
$.ajax({
type : "POST",
data : {'num' : num},
url : "user.do?opt=queryByPK2&timeid="+new Date().getTime(),
success : function(result) {
setgrid(result);
},dataType : "json"
});
}else
window.location.reload();
});
$("#btnQueryByName").click(function(){
var Byname = $("#Byname").val();
$.ajax({
type : "POST",
data :{'Byname':Byname},
url : "user.do?opt=queryByName2&timeid=" + new Date().getTime(),
success : function(result) {
setgrid(result);
},dataType : "json"
});
});
});
</script>
<style type="text/css">
.l-case-title
{
font-weight: bold;
margin-top: 20px;
margin-bottom: 20px;
}
</style>
<script type="text/javascript">
function empowerDept(rowid){
$("#delete").attr('disabled',true);
$("#confirm").attr('disabled',false);
var val = grid.getSelected(rowid);
var num = val["adminID"];
userID = num;
$.ajax({
type : "POST",
data : {'num': num},
url : "Pop.do?opt=querySurplus&timeid="+new Date().getTime(),
success : function(list) {
grid2Show(list);
},
dataType : "json"
});
}
function grid2Show(list){
grid.reload();
grid2 = $("#divBody2").ligerGrid({
checkbox: true,
columns : [
{
display : '部门编号',
editor: { type: 'int' },
name : 'departmentID',
minWidth : 60
},{
display : '部门名称',
editor: { type: 'text' },
name : 'departmentName',
minWidth : 60
}],
pageSize : 10,
enabledEdit: true,//是否允许编辑
clickToEdit:false,//是否允许点击单一表格编辑
isScroll: true,//是否固定底部
data : $.extend(true, {}, list),
width : '100%',
height : '100%'
});
}
function impowerDept(rowid){
$("#delete").attr('disabled',false);
$("#confirm").attr('disabled',true);
var val = grid.getSelected(rowid);
var num = val["adminID"];
userID = num;
$.ajax({
type : "POST",
data : {'num': num},
url : "Pop.do?opt=queryHavepower&timeid="+new Date().getTime(),
success : function(list) {
grid2Show(list);
},
dataType : "json"
});
}
$(function() {
$("#layout1").ligerLayout({
leftWidth : 200,
centerBottomHeight : 600
});
});
function setgrid(queryData) {
grid = $("#divBody").ligerGrid({
columns : [
{
display : '用户编号',
name : 'adminID',
minWidth : 60
},{
display : '用户账号',
editor: { type: 'text' },
name : 'adminAccount',
minWidth : 60
} ,{
display : '账号状态',
editor: { type: 'select',
data:[{id:"1",text:"启用"},{id:"0",text:"停用"}],
},
name : 'adminState',
minWidth : 60
} ,{
display : '账号权限',
editor: { type: 'select',
data:[{id:"1",text:"超级管理员"},{id:"0",text:"普通考勤员"}],
},
name : 'adminRight',
minWidth : 60
} ,{
display : '用户名称',
editor: { type: 'text' },
name : 'adminName',
minWidth : 60
},{ display: '操作',
minWidth : 140,
width: 140,
render: function (rowdata, rowindex, value)
{
var h= "<a href='javascript:empowerDept(" + rowindex + ")'>部门考勤授权</a>";
h +="/";
h +="<a href='javascript:impowerDept(" + rowindex + ")'>解除授权</a>";
return h;
}
}],
pageSize : 10,
enabledEdit: true,//是否允许编辑
clickToEdit:false,//是否允许点击单一表格编辑
isScroll: true,//是否固定底部
data : $.extend(true, {}, queryData),
width : '100%',
height : '100%'
});
}
function confirm(){
var val = grid2.getSelecteds();
var param = "";
for( var i = 0 ; i < val.length ; i++ )
{
var id = val[i].departmentID;
param = param + "departmentID=" + id + "&";
}
$.ajax({
type : "POST",
data : {'userID': userID},
url : "Pop.do?" + param + "opt=add&timeid="+new Date().getTime(),
success : function(msg) {
alert(msg);
}
});
window.location.reload();
}
function deleteDept(){
var val = grid2.getSelecteds();
var param = "";
for( var i = 0 ; i < val.length ; i++ )
{
var id = val[i].departmentID;
param = param + "departmentID=" + id + "&";
}
$.ajax({
type : "POST",
data : {'userID': userID},
url : "Pop.do?" + param + "opt=delete&timeid="+new Date().getTime(),
success : function(msge) {
alert(msg);
}
});
window.location.reload();
}
</script>
<style type="text/css">
body {
padding: 5px;
margin: 0;
padding-bottom: 30px;
}
#layout1 {
width: 100%;
margin: 0;
padding: 0;
}
</style>
</head>
<body>
<div id="layout1">
<div position="center" title="查询考勤员">
<div id="searchbar">
考勤员编号：<input id="userno" type="text" oninput ="value=value.replace(/[^\d]/g,'')"/>
<input id="btnQuery"type="button" value="查询" />
考勤员账户名(模糊查询)：<input id="Byname" type="text" />
<input id="btnQueryByName" type="button" value="查询" />
<input type="button" id="delete" value="删除" style="float: right;" onclick="deleteDept()"/>
<input type="button" id="confirm" value="添加" style="float: right;" onclick="confirm()"/>
<div id="divBody" style="margin: 0; padding: 0"></div>
</div>
</div>
<div position="centerbottom" title="考勤授权">
<div style="margin: 0; padding: 0" id="operation">
</div>
<div id="divBody2" style="margin: 0; padding: 0"></div>
</div>
</div>
</body>
</html>B.控制层：
package com.ambow.controller;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.ambow.DAO.popDao;
import com.ambow.entity.Department;
import com.ambow.factory.popFactory;
import com.ambow.util.JSONUtil;
@WebServlet("/Pop.do")
public class PopedomController extends HttpServlet {
private static final long serialVersionUID = 1L;
public PopedomController() {
super();
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
popDao dao = popFactory.getPopInstance();
ArrayList<Department> deptlist = new ArrayList<Department>();
PrintWriter out = response.getWriter();
String opt = request.getParameter("opt");
if(opt.equals("querySurplus")) {
try {
int num = Integer.parseInt(request.getParameter("num"));
deptlist=dao.querySurplus(num);
} catch (SQLException e) {
e.printStackTrace();
}
String json = JSONUtil.DepartmenttoJSONString(deptlist);
out.write(json);
}else if(opt.equals("queryHavepower")){
try {
int num = Integer.parseInt(request.getParameter("num"));
deptlist = dao.queryHavepower(num);
} catch (SQLException e) {
e.printStackTrace();
}
String json = JSONUtil.DepartmenttoJSONString(deptlist);
out.write(json);
}else if(opt.equals("add")){
String[] departmentID = request.getParameterValues("departmentID");
int userID = Integer.parseInt(request.getParameter("userID"));
try {
dao.add(userID,departmentID);
out.write("修改考勤权限成功！");
} catch (Exception e) {
out.write("修改考勤权限失败！");
}
}else if(opt.equals("delete")){
String[] departmentID = request.getParameterValues("departmentID");
int userID = Integer.parseInt(request.getParameter("userID"));
try {
dao.delete(userID,departmentID);
out.write("修改考勤权限成功！");
} catch (Exception e) {
out.write("修改考勤权限失败！");
}
}
out.flush();
out.close();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
}
（3）功能说明
此模块针对于对考勤员的授权，结合权限系统，前端判断登陆用户类型，做出相应显示。
（4）其他代码
A.数据库接口：
package com.ambow.DAO;
import java.sql.SQLException;
import java.util.ArrayList;
import com.ambow.entity.Department;
public interface popDao {
ArrayList<Department> querySurplus(int num) throws SQLException;
ArrayList<Department> queryHavepower(int num) throws SQLException;
void add(int userID, String[] departmentID) throws SQLException;
void delete(int userID, String[] departmentID) throws SQLException;
}
B.数据库实现：
package com.ambow.DAOImp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.ambow.DAO.popDao;
import com.ambow.DB.DBConnection;
import com.ambow.entity.Department;
public class popDAOImp implements popDao{
/**
* @author TianZW
* @return ArrayList<AdminPopedom>
* @throws
* */
public ArrayList<Department> querySurplus(int num) throws SQLException {
ArrayList<Department> list = new ArrayList<Department>();
Connection conn = DBConnection.getConnection();
String sql = "SELECT ATT_DEPARTMENT.DEPARTMENTID,ATT_DEPARTMENT.DEPARTMENTNAME FROM ATT_DEPARTMENT "
+ "WHERE ATT_DEPARTMENT.DEPARTMENTID not IN (SELECT ATT_ADMINPOPEDOM.DEPARTMENTID FROM ATT_ADMINPOPEDOM "
+ "WHERE ATT_ADMINPOPEDOM.ADMINID IN (SELECT ATT_ADMIN.ADMINID FROM ATT_ADMIN WHERE ATT_ADMIN.ADMINID = ?)) and ATT_DEPARTMENT.parentID!=0";
PreparedStatement state = conn.prepareStatement(sql);
state.setInt(1, num);
ResultSet rs = state.executeQuery();
while (rs.next()) {
int DepartmentID = rs.getInt("DepartmentID");
String DepartmentName = rs.getString("DepartmentName");
Department dept = new Department(DepartmentID,DepartmentName);
list.add(dept);
}
DBConnection.closeConnection(conn, state, rs);
return list;
}
/**
* @author TianZW
* @return ArrayList<AdminPopedom>
* @throws
* */
public ArrayList<Department> queryHavepower(int num) throws SQLException {
ArrayList<Department> list = new ArrayList<Department>();
Connection conn = DBConnection.getConnection();
String sql = "SELECT ATT_DEPARTMENT.DEPARTMENTID,ATT_DEPARTMENT.DEPARTMENTNAME FROM ATT_DEPARTMENT "
+ "WHERE ATT_DEPARTMENT.DEPARTMENTID IN (SELECT ATT_ADMINPOPEDOM.DEPARTMENTID FROM ATT_ADMINPOPEDOM "
+ "WHERE ATT_ADMINPOPEDOM.ADMINID IN (SELECT ATT_ADMIN.ADMINID FROM ATT_ADMIN WHERE ATT_ADMIN.ADMINID = ?)) and ATT_DEPARTMENT.parentID!=0";
PreparedStatement state = conn.prepareStatement(sql);
state.setInt(1, num);
ResultSet rs = state.executeQuery();
while (rs.next()) {
int DepartmentID = rs.getInt("DepartmentID");
String DepartmentName = rs.getString("DepartmentName");
Department dept = new Department(DepartmentID,DepartmentName);
list.add(dept);
}
DBConnection.closeConnection(conn, state, rs);
return list;
}
/**
* @throws SQLException
* */
public void add(int userID, String[] departmentID) throws SQLException {
Connection conn = DBConnection.getConnection();
for (String i : departmentID) {
String sql = "INSERT INTO ATT_ADMINPOPEDOM(DEPARTMENTID,ADMINID) VALUES(?,?)";
PreparedStatement state = conn.prepareStatement(sql);
state.setString(1, i);
state.setInt(2, userID);
state.executeQuery();
state.close();
}
DBConnection.closeConnection(conn, null, null);
}
/**
* @throws SQLException
* */
public void delete(int userID, String[] departmentID) throws SQLException {
Connection conn = DBConnection.getConnection();
for (String i : departmentID) {
String sql = "DELETE FROM ATT_ADMINPOPEDOM where DEPARTMENTID=? and ADMINID=?";
PreparedStatement state = conn.prepareStatement(sql);
state.setString(1, i);
state.setInt(2, userID);
state.executeQuery();
state.close();
}
DBConnection.closeConnection(conn, null, null);
}
}
-
考勤员模块
1.员工考勤
（1）前端显示
（2）核心代码
A.前端代码：
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>新增考勤</title>
<link href="js/ligerUI/ligerUI/skins/Aqua/css/ligerui-all.css" rel="stylesheet" type="text/css">
<link href="js/ligerUI/ligerUI/skins/Gray2014/css/all.css" rel="stylesheet">
<script src="js/ligerUI/jquery/jquery-1.9.0.min.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/ligerui.all.js"></script>
<script src="js/ligerUI/jquery-validation/jquery.validate.min.js"></script>
<script src="js/ligerUI/jquery-validation/jquery.metadata.js"></script>
<script src="js/ligerUI/jquery-validation/messages_cn.js"></script>
<style type="text/css">
body
{
padding-left:10px;
font-size:13px;
}
h1
{
font-size:20px;
font-family:Verdana;
}
h4
{
font-size:16px;
margin-top:25px;
margin-bottom:10px;
}
.description
{
padding-bottom:30px;
font-family:Verdana;
}
.description h3
{
color:#CC0000;
font-size:16px;
margin:0 30px 10px 0px;
padding:45px 0 8px;
border-bottom:solid 1px #888;
}
td {
padding: 5px;
text-align: center;
}
</style>
<script type="text/javascript">
$.ajax({
url:"emp.do?opt=queryAllnotHashMap&timeid="+new Date().getTime(),
type:"post",
success : function(list) {
for(var i = 0,len = list.length;i<len;i++){
$("#EmployeeID").append("<option value='" + list[i].employeeID + "'>" + list[i].employeeName+ "</option>");
}
},dataType : "json"
});
$.ajax({
url:"att.do?opt=queryAll&timeid="+new Date().getTime(),
type:"post",
success : function(list) {
for(var i = 0,len = list.length;i<len;i++){
$("#AttendanceType").append("<option value='" + list[i].typeId + "'>" + list[i].typeName+ "</option>");
}
},dataType : "json"
});
$.ajax({
url:"note.do?opt=queryAllnotHashMap&timeid="+new Date().getTime(),
type:"post",
success : function(list) {
for(var i = 0,len = list.length;i<len;i++){
$("#NoteId").append("<option value='" + list[i].noteID + "'>" + list[i].noteID+ "</option>");
}
},dataType : "json"
});
</script>
</head>
<body style="padding:10px">
<h1>新增考勤</h1>
<form id="form1" action="Rec.do?opt=add" method="post">
<div align="center">
<table border="1">
<tr>
<td>考勤日期：</td>
<td><input type="date" id="AttendanceTIMESTAMP" name="AttendanceTIMESTAMP" required="required"/></td>
</tr>
<tr>
<td>考勤时段：</td>
<td>
<select id="AttendanceFlag" name="AttendanceFlag">
<option value="0">上午</option>
<option value="1">下午</option>
</select>
</td>
</tr>
<tr>
<td>员工姓名：</td>
<td>
<select id ="EmployeeID" name="EmployeeID" required="required"></select>
</td>
</tr>
<tr>
<td>考勤类型：</td>
<td>
<select id ="AttendanceType" name="AttendanceType" required="required"></select>
</td>
</tr>
<tr>
<td>单据编号：</td>
<td>
<select id ="NoteId" name="NoteId"></select>
</td>
</tr>
<tr>
<td>备注：</td>
<td><textarea id ="AttendanceMemo" name="AttendanceMemo"></textarea></td>
</tr>
<tr>
<td><input type="submit" value="提交"/></td>
<td><input type="reset" value="清除"/></td>
</tr>
</table>
</div>
</form>
</body>
</html>
B.服务器端：
package com.ambow.controller;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import com.ambow.DAO.recordDao;
import com.ambow.entity.AttendanceRecord;
import com.ambow.factory.recordFactory;
import com.ambow.util.JSONUtil;
@WebServlet("/Rec.do")
public class RecordController extends HttpServlet {
private static final long serialVersionUID = 1L;
public RecordController() {
super();
// TODO Auto-generated constructor stub
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String opt = request.getParameter("opt");
ArrayList<AttendanceRecord> list = new ArrayList<AttendanceRecord>();
PrintWriter out = response.getWriter();
recordDao dao = recordFactory.getRecordInstance();
if(opt.equals("queryAll")) {
try {
list = dao.queryAll();
String json = JSONUtil.RecordtoJSONString(list);
out.write(json);
} catch (Exception e) {
e.printStackTrace();
}
}else if (opt.equals("queryDate")) {
try {
int deptno = Integer.parseInt(request.getParameter("secondDept"));
String date = request.getParameter("date");
int time = Integer.parseInt(request.getParameter("time"));
String userName = request.getParameter("userName");
list = dao.queryDate(deptno,date,time,userName);
String json = JSONUtil.RecordtoJSONString(list);
System.out.println(json);
out.write(json);
} catch (Exception e) {
e.printStackTrace();
}
}else if (opt.equals("queryByPK")) {
int num = Integer.parseInt(request.getParameter("num"));
try {
list = dao.queryByPK(num);
String json = JSONUtil.RecordtoJSONString(list);
out.write(json);
} catch (Exception e) {
e.printStackTrace();
}
}else if (opt.equals("add")) {
try {
HttpSession session=request.getSession();
String adminName = (String)session.getAttribute("loginname");
System.out.println(adminName);
String attendanceTIMESTAMP = request.getParameter("AttendanceTIMESTAMP");
int attendanceFlag = Integer.parseInt(request.getParameter("AttendanceFlag"));
int employeeID = Integer.parseInt(request.getParameter("EmployeeID"));
int attendanceType = Integer.parseInt(request.getParameter("AttendanceType"));
int noteId = Integer.parseInt(request.getParameter("NoteId"));
String attendanceMemo = request.getParameter("AttendanceMemo");
AttendanceRecord rec = new AttendanceRecord(employeeID,attendanceTIMESTAMP,attendanceFlag,
attendanceType,attendanceMemo,noteId,adminName);
dao.add(rec);
out.write("新增成功！");
} catch (Exception e) {
e.printStackTrace();
out.write("新增失败！");
}
}
out.flush();
out.close();
}
}
C.实体层：
package com.ambow.entity;
import java.util.Date;
/**
* @author TianZW
* @考勤记录表
* */
public class AttendanceRecord {
private int AttendanceID;//考勤记录ID
private int EmployeeID;//员工编号
private String CardNumber;//员工卡号
private Date AttendanceTIMESTAMP ;//考勤日期
private int AttendanceFlag ;//考勤时段
private int AttendanceType;//考勤类型
private String AttendanceMemo ;//备注
private int AdminID ;//考勤员编号
private int TempDepartmentId ;//部门编号
private int NoteId ;//单据编号
/*数据库中不存在的字段*/
private String date;
private String EmployeeName;
private String departmentName;
private String AdminName;
private String AttendanceName;
}D.接口层：
package com.ambow.DAO;
import java.sql.SQLException;
import java.util.ArrayList;
import com.ambow.entity.AttendanceRecord;
public interface recordDao {
ArrayList<AttendanceRecord> queryAll() throws SQLException;
ArrayList<AttendanceRecord> queryDate(int deptno, String date, int time, String userName)throws SQLException;
ArrayList<AttendanceRecord> queryByPK(int num)throws SQLException;
void add(AttendanceRecord rec)throws SQLException;
}
E.实现层：
package com.ambow.DAOImp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import com.ambow.DAO.recordDao;
import com.ambow.DB.DBConnection;
import com.ambow.entity.AttendanceRecord;
public class recordDAOImp implements recordDao{
/**
* @author TianZW
* @return ArrayList<AttendanceRecord>
* @throws SQLException
* */
public ArrayList<AttendanceRecord> queryAll() throws SQLException {
ArrayList<AttendanceRecord> list = new ArrayList<AttendanceRecord>();
Connection conn = DBConnection.getConnection();
String sql = "select r.*,e.EMPLOYEENAME,d.DEPARTMENTNAME,a.TYPENAME,u.ADMINNAME from att_AttendanceRecord r LEFT JOIN "
+ "ATT_EMPLOYEES e on r.EMPLOYEEID=e.EMPLOYEEID LEFT JOIN ATT_DEPARTMENT d on d.DEPARTMENTID=r.TEMPDEPARTMENTID "
+ "LEFT JOIN ATT_ATTENDANCETYPE a on a.TYPEID = r.ATTENDANCETYPE LEFT JOIN ATT_ADMIN u on u.ADMINID = r.ADMINID";
PreparedStatement state = conn.prepareStatement(sql);
ResultSet rs = state.executeQuery();
while(rs.next()) {
int AttendanceID = rs.getInt("AttendanceID");
int EmployeeID = rs.getInt("EmployeeID");
String CardNumber = rs.getString("CardNumber");
Date AttendanceTIMESTAMP = rs.getDate("AttendanceTIMESTAMP");
String dateTime = AttendanceTIMESTAMP.toString();
int AttendanceFlag = rs.getInt("AttendanceFlag");
int AttendanceType = rs.getInt("AttendanceType");
String AttendanceMemo = rs.getString("AttendanceMemo");
int AdminID = rs.getInt("AdminID");
int TempDepartmentId = rs.getInt("TempDepartmentId");
int NoteId = rs.getInt("NoteId");
String EmployeeName=rs.getString(11);
String departmentName=rs.getString(12);
String AttendanceName=rs.getString(13);
String AdminName=rs.getString(14);
/*,EmployeeName,departmentName,AdminName,AttendanceName*/
AttendanceRecord r = new AttendanceRecord(AttendanceID, EmployeeID, CardNumber, AttendanceTIMESTAMP,
AttendanceFlag, AttendanceType, AttendanceMemo, AdminID, TempDepartmentId, NoteId,dateTime,
EmployeeName,departmentName,AdminName,AttendanceName);
list.add(r);
}
DBConnection.closeConnection(conn, state, rs);
return list;
}
/**
* @author TianZW
* @return ArrayList<AttendanceRecord>
* @throws SQLException
* */
public ArrayList<AttendanceRecord> queryDate(int deptno, String date, int time,String userName) throws SQLException {
String date2 = date.toString();
ArrayList<AttendanceRecord> list = new ArrayList<AttendanceRecord>();
Connection conn = DBConnection.getConnection();
String sql = "select att_AttendanceRecord.AttendanceID,att_AttendanceRecord.EmployeeID,att_AttendanceRecord.CardNumber,"
+ "att_AttendanceRecord.AttendanceTIMESTAMP,att_AttendanceRecord.AttendanceFlag,att_AttendanceRecord.AttendanceType,"
+ "att_AttendanceRecord.AttendanceMemo,att_AttendanceRecord.AdminID,att_AttendanceRecord.TempDepartmentId,"
+ "att_AttendanceRecord.NoteId from att_AttendanceRecord RIGHT JOIN ATT_ADMIN ON "
+ "att_AttendanceRecord.ADMINID=ATT_ADMIN.ADMINID where TempDepartmentId=? and"
+ " AttendanceTIMESTAMP=to_date(?,'yyyy-MM-dd') and AttendanceFlag=? AND ATT_ADMIN.ADMINACCOUNT=?";
PreparedStatement state = conn.prepareStatement(sql);
state.setInt(1, deptno);
state.setString(2, date2);
state.setInt(3, time);
state.setString(4, userName);
ResultSet rs = state.executeQuery();
while(rs.next()) {
int AttendanceID = rs.getInt("AttendanceID");
int EmployeeID = rs.getInt("EmployeeID");
String CardNumber = rs.getString("CardNumber");
Date AttendanceTIMESTAMP = rs.getDate("AttendanceTIMESTAMP");
String dateTime = AttendanceTIMESTAMP.toString();
int AttendanceFlag = rs.getInt("AttendanceFlag");
int AttendanceType = rs.getInt("AttendanceType");
String AttendanceMemo = rs.getString("AttendanceMemo");
int AdminID = rs.getInt("AdminID");
int TempDepartmentId = rs.getInt("TempDepartmentId");
int NoteId = rs.getInt("NoteId");
AttendanceRecord r = new AttendanceRecord(AttendanceID, EmployeeID, CardNumber,AttendanceTIMESTAMP, AttendanceFlag, AttendanceType, AttendanceMemo, AdminID, TempDepartmentId, NoteId,dateTime);
list.add(r);
}
DBConnection.closeConnection(conn, state, rs);
return list;
}
/**
* @author TianZW
* @return ArrayList<AttendanceRecord>
* @throws SQLException
* */
public ArrayList<AttendanceRecord> queryByPK(int num) throws SQLException {
ArrayList<AttendanceRecord> list = new ArrayList<AttendanceRecord>();
Connection conn = DBConnection.getConnection();
String sql = "select r.*,e.EMPLOYEENAME,d.DEPARTMENTNAME,a.TYPENAME,u.ADMINNAME from att_AttendanceRecord r LEFT JOIN "
+ "ATT_EMPLOYEES e on r.EMPLOYEEID=e.EMPLOYEEID LEFT JOIN ATT_DEPARTMENT d on d.DEPARTMENTID=r.TEMPDEPARTMENTID "
+ "LEFT JOIN ATT_ATTENDANCETYPE a on a.TYPEID = r.ATTENDANCETYPE LEFT JOIN ATT_ADMIN u on u.ADMINID = r.ADMINID "
+ "where r.AttendanceID=?";
PreparedStatement state = conn.prepareStatement(sql);
state.setInt(1, num);
ResultSet rs = state.executeQuery();
while(rs.next()) {
int AttendanceID = rs.getInt("AttendanceID");
int EmployeeID = rs.getInt("EmployeeID");
String CardNumber = rs.getString("CardNumber");
Date AttendanceTIMESTAMP = rs.getDate("AttendanceTIMESTAMP");
String dateTime = AttendanceTIMESTAMP.toString();
int AttendanceFlag = rs.getInt("AttendanceFlag");
int AttendanceType = rs.getInt("AttendanceType");
String AttendanceMemo = rs.getString("AttendanceMemo");
int AdminID = rs.getInt("AdminID");
int TempDepartmentId = rs.getInt("TempDepartmentId");
int NoteId = rs.getInt("NoteId");
String EmployeeName=rs.getString(11);
String departmentName=rs.getString(12);
String AdminName=rs.getString(14);
String AttendanceName=rs.getString(13);
/*,EmployeeName,departmentName,AdminName,AttendanceName*/
AttendanceRecord r = new AttendanceRecord(AttendanceID, EmployeeID, CardNumber, AttendanceTIMESTAMP,
AttendanceFlag, AttendanceType, AttendanceMemo, AdminID, TempDepartmentId, NoteId,dateTime,
EmployeeName,departmentName,AdminName,AttendanceName);
list.add(r);
}
DBConnection.closeConnection(conn, state, rs);
return list;
}
/**
* @author TianZW
* @throws SQLException
* */
public void add(AttendanceRecord rec) throws SQLException {
Connection conn = DBConnection.getConnection();
String sql = "SELECT CardNumber,DEPARTMENT from ATT_EMPLOYEES WHERE ATT_EMPLOYEES.EMPLOYEEID=?";
PreparedStatement state = conn.prepareStatement(sql);
state.setInt(1,rec.getEmployeeID());
ResultSet rs = state.executeQuery();
String CardNumber = "";
int DEPARTMENT = -1;
if(rs.next()) {
CardNumber = rs.getString("CardNumber");
DEPARTMENT = rs.getInt("DEPARTMENT");
}
DBConnection.closeConnection(null, state, rs);
int ADMINID = -1;
String sql2 = "SELECT ADMINID FROM ATT_ADMIN WHERE ATT_ADMIN.ADMINACCOUNT =?";
PreparedStatement state2 = conn.prepareStatement(sql2);
state2.setString(1, rec.getAdminName());
ResultSet rs2 = state2.executeQuery();
if(rs2.next()) {
ADMINID = rs2.getInt("ADMINID");
}
DBConnection.closeConnection(null, state2, rs2);
String sql3 = "INSERT into ATT_ATTENDANCERECORD(EMPLOYEEID,CARDNUMBER,ATTENDANCETIMESTAMP,"
+ "ATTENDANCEFLAG,ATTENDANCETYPE,ATTENDANCEMEMO,ADMINID,TEMPDEPARTMENTID,NOTEID)"
+ " VALUES(?,?,to_date(?,'yyyy-MM-dd'),?,?,?,?,?,?)";
PreparedStatement state3 = conn.prepareStatement(sql3);
state3.setInt(1, rec.getEmployeeID());
state3.setString(2, CardNumber);
state3.setString(3, rec.getDate());
state3.setInt(4, rec.getAttendanceFlag());
state3.setInt(5, rec.getAttendanceType());
state3.setString(6, rec.getAttendanceMemo());
state3.setInt(7, ADMINID);
state3.setInt(8, DEPARTMENT);
state3.setInt(9, rec.getNoteId());
state3.executeQuery();
DBConnection.closeConnection(conn, state3, null);
}
}
（3）功能简介
本模块是对于考勤表的增删改查，但是需要联合其他表，数据库实现层复杂度较高。
2.单据管理
（1）前端显示
（2）核心代码
A.前端：
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>员工考勤</title>
<link href="js/ligerUI/ligerUI/skins/Aqua/css/ligerui-all.css" rel="stylesheet"
type="text/css" />
<link rel="stylesheet" type="text/css" id="mylink" />
<script src="js/ligerUI/jquery/jquery-1.9.0.min.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/ligerui.all.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/plugins/ligerPopupEdit.js"></script>
<style type="text/css">
body {
padding: 5px;
margin: 0;
padding-bottom: 15px;
}
#layout1 {
width: 100%;
margin: 0;
padding: 0;
}
</style>
<script type="text/javascript">
var grid = null;
function deleteNote(){
var val = grid.getSelecteds();
var len = val.length;
if(len==0)
alert("您未选中任何记录！");
else if(confirm('您确定要删除这些单据吗？')){
var param="";
for( var i = 0; i < len ; i++ )
{
var id = val[i].noteID;
param = param + "noteID=" + id + "&";
}
$.ajax({
type : "POST",
url : "note.do?" + param + "opt=delete&timeid="+new Date().getTime(),
success : function(msg) {
alert(msg);
},error : function(msg) {
alert(msg);
},dataType:"text"
});
}
}
function showNote(rowindex){
var val = grid.getSelected(rowindex);
var noteId = val["noteId"];
f_open(noteId);
}
var win1,win2;
function f_open(noteId)
{
win1 = $.ligerDialog.open({
height: 500,
url: '/checkWork/noteUpdate.html',
width: 500,
showMax: true,
showToggle: true,
showMin: true,
isResize: true,
slide: true ,
data: {
noteId : noteId
}
});
}
function f_open2()
{
win1 = $.ligerDialog.open({
height: 500,
url: '/checkWork/noteAdd.html',
width: 500,
showMax: true,
showToggle: true,
showMin: true,
isResize: true,
slide: true
});
}
$(function(){
$.ajax({
type : "POST",
url : "note.do?opt=queryAll&timeid="+new Date().getTime(),
success : function(list) {
show(list);
},
dataType : "json"
});
$("#btnQuery").click(function(){
var num = $("#deptno").val();
$.ajax({
type : "POST",
url : "note.do?opt=queryByPK2&num="+num+"&timeid=" + new Date().getTime(),
success : function(result) {
show(result);
},dataType : "json"
});
});
});
/******************************************************************************/
function show(list){
grid = $("#divBody").ligerGrid({
columns : [
{
display : '单据编号',
name : 'noteID'
},{
display : '部门编号',
editor: { type: 'text' },
name : 'departmentID'
},{
display : '申请人',
editor: { type: 'text' },
name : 'employeeID',
}, {
display : '单据类型',
editor: { type: 'int' },
name : 'noteType',
align : 'left'
}, {
display : '事由',
editor: { type: 'text'},
name : 'cause'
}, {
display : '填表日期',
editor: { type: 'date' },
minWidth: 100,
name : 'fillInTime'
}, {
display : '主管意见',
editor: { type: 'int'},
name : 'directorSign'
}, {
display : '经理意见',
editor: { type: 'int'},
name : 'administrationSign'
},{
display : '总裁意见',
editor: { type: 'int'},
name : 'presidentSign'
}, {
display : '开始日期',
minWidth: 100,
editor: { type: 'int'},
name : 'startDate'
}, {
display : '开始时间',
editor: { type: 'int'},
render: function (rowdata, rowindex, value){
if(value==0)
return "上午";
else
return "下午";
},
name : 'startTime'
}, {
display : '结束日期',
minWidth: 100,
editor: { type: 'int'},
name : 'endDate'
}, {
display : '结束时间',
editor: { type: 'int'},
render: function (rowdata, rowindex, value){
if(value==0)
return "上午";
else
return "下午";
},
name : 'endTime'
}, {
display : '录入人',
editor: { type: 'int'},
name : 'adminID'
}, {
display : '备注',
editor: { type: 'int'},
name : 'noteMemo'
}, {
display : '是否审核',
editor: { type: 'int'},
render: function (rowdata, rowindex, value){
if(value==0)
return "未审核";
else
return "已审核";
},
name : 'isVerify'
}],
checkbox : true,
pageSize : 10,
enabledEdit: true,//是否允许编辑
clickToEdit:false,//是否允许点击单一表格编辑
isScroll: true,//是否固定底部
data : $.extend(true, {}, list),
width : '100%',
height : '100%'
});
}
</script>
</head>
<body>
<div id="searchbar">
单据编号：<input id="deptno" type="text" oninput ="value=value.replace(/[^\d]/g,'')"/>
<input id="btnQuery" type="button" value="查询" />
<input type="button" onclick="f_open2()" value="增加单据" style="float: right;width: 200px"/>
<input type="button" onclick="deleteNote()" value="删除单据" style="float: right;width: 200px"/>
</div>
<div id="divBody"></div>
</body>
</html>B.修改单据前端：
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>修改单据</title>
<link href="js/ligerUI/ligerUI/skins/Aqua/css/ligerui-all.css" rel="stylesheet" type="text/css">
<link href="js/ligerUI/ligerUI/skins/Gray2014/css/all.css" rel="stylesheet">
<script src="js/ligerUI/jquery/jquery-1.9.0.min.js" type="text/javascript"></script>
<script src="js/ligerUI/ligerUI/js/ligerui.all.js"></script>
<script src="js/ligerUI/jquery-validation/jquery.validate.min.js"></script>
<script src="js/ligerUI/jquery-validation/jquery.metadata.js"></script>
<script src="js/ligerUI/jquery-validation/messages_cn.js"></script>
<script type="text/javascript">
var dialog = frameElement.dialog; //调用页面的dialog对象(ligerui对象)
var dialogData = dialog.get('data');//获取data参数
var noteId = dialogData.noteId;
$(function(){
$.ajax({
url:"note.do?opt=queryByPK&timeid="+new Date().getTime(),
data : {noteId,noteId},
type:"post",
success : function(msg) {
$("#noteId").val(noteId);
$("#EmployeeID").val(msg.employeeID);
$("#NoteType").val(msg.noteType);
$("#DirectorSign").val(msg.directorSign);
$("#AdministrationSign").val(msg.administrationSign);
$("#PresidentSign").val(msg.presidentSign);
$("#NoteMemo").val(msg.noteMemo);
$("#Cause").val(msg.cause);
$("#IsVerify").val(msg.isVerify);
$("#NoteMemo").val(msg.noteMemo);
$("#EndDate").val(msg.endDate);
$("#StartDate").val(msg.startDate);
$("#FillInTime").val(msg.fillInTime);
$("#StartTime").val(msg.startTime);
$("#EndTime").val(msg.endTime);
},dataType : "json"
});
$.ajax({
url:"att.do?opt=queryAll&timeid="+new Date().getTime(),
type:"post",
success : function(list) {
for(var i = 0,len = list.length;i<len;i++){
$("#NoteType").append("<option value='" + list[i].typeId + "'>" + list[i].typeName+ "</option>");
}
},dataType : "json"
});
$.ajax({
url:"emp.do?opt=queryAllnotHashMap&timeid="+new Date().getTime(),
type:"post",
success : function(list) {
for(var i = 0,len = list.length;i<len;i++){
$("#EmployeeID").append("<option value='" + list[i].employeeID + "'>" + list[i].employeeName+ "</option>");
}
},dataType : "json"
});
$.ajax({
url:"user.do?opt=queryAllnotHashMap&timeid="+new Date().getTime(),
type:"post",
success : function(list) {
for(var i = 0,len = list.length;i<len;i++){
$("#AdminID").append("<option value='" + list[i].adminID + "'>" + list[i].adminAccount+ "</option>");
}
},dataType : "json"
});
});
</script>
<style type="text/css">
body
{
padding-left:10px;
font-size:13px;
}
h1
{
font-size:20px;
font-family:Verdana;
}
h4
{
font-size:16px;
margin-top:25px;
margin-bottom:10px;
}
.description
{
padding-bottom:30px;
font-family:Verdana;
}
.description h3
{
color:#CC0000;
font-size:16px;
margin:0 30px 10px 0px;
padding:45px 0 8px;
border-bottom:solid 1px #888;
}
td {
padding: 5px;
text-align: center;
}
</style>
</head>
<body style="padding:10px">
<h1>修改单据</h1>
<form id="form1" action="note.do?opt=update" method="post" >
<div align="center">
<table border="1">
<tr hidden="true">
<td>单据编号：</td>
<td>
<input type="text" readonly="readonly" id="noteId" name="noteId"/>
</td>
</tr>
<tr>
<td>申请人：</td>
<td>
<select id ="EmployeeID" name="EmployeeID"></select>
</td>
</tr>
<tr>
<td>代理人：</td>
<td>
<select id ="AdminID" name="AdminID"></select>
</td>
</tr>
<tr>
<td>请假类型：</td>
<td>
<select id ="NoteType" name="NoteType"></select>
</td>
</tr>
<tr>
<td>填表日期：</td>
<td><input type="date" id ="FillInTime" name="FillInTime" required="required"/></td>
</tr>
<tr>
<td>请假事由：</td>
<td><textarea id ="Cause" name="Cause" required="required"></textarea></td>
</tr>
<tr>
<td>请假开始日期：</td>
<td>
<input type="date" id ="StartDate" name="StartDate" required="required"/>
<select id ="StartTime" name="StartTime">
<option value="0">上午</option>
<option value="1">下午</option>
</select>
</td>
</tr>
<tr>
<td>请假结束日期：</td>
<td>
<input type="date" id ="EndDate" name="EndDate"/>
<select id ="EndTime" name="EndTime">
<option value="0">上午</option>
<option value="1">下午</option>
</select>
</td>
</tr>
<tr>
<td>主管意见：</td>
<td><textarea id ="DirectorSign" name="DirectorSign" required="required"></textarea></td>
</tr>
<tr>
<td>经理意见：</td>
<td><textarea id ="AdministrationSign" name="AdministrationSign" required="required"></textarea></td>
</tr>
<tr>
<td>总裁意见：</td>
<td><textarea id ="PresidentSign" name="PresidentSign" required="required"></textarea></td>
</tr>
<tr>
<td>备注：</td>
<td><textarea id ="NoteMemo" name="NoteMemo"></textarea></td>
</tr>
<tr>
<td>是否审核：</td>
<td>
<select id="IsVerify" name="IsVerify">
<option value="0">未审核</option>
<option value="1">已审核</option>
</select>
</td>
</tr>
<tr>
<td><input type="submit" value="提交"/></td>
<td><input type="reset" value="清除"/></td>
</tr>
</table>
</div>
</form>
</body>
</html>
C.前端增加
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>添加职位</title>
<script src="js/ligerUI/ligerUI/js/core/base.js" type="text/javascript"></script>
<script src="js/ligerUI/jquery/jquery-1.9.0.min.js" type="text/javascript"></script>
<script type="text/javascript">
var dialog = frameElement.dialog; //调用页面的dialog对象(ligerui对象)
$(function(){
$("#confirm").click(function(){
var t = $("#PositionName").val();
if(t=="")
alert("部门名称不能为空!");
else{
$.ajax({
url:"Pos.do?opt=add&timeid="+new Date().getTime(),
type:"POST",
data : $("#bodyForm").serialize(),
success:function(){
alert("新增成功!");
},dataType : "json"
});
dialog.close();//关闭dialog
}
});
});
</script>
</head>
<body>
<form id = "bodyForm">
<table style="text-align:center;background-color: #D1EEEE" border="1">
<tr style="text-align:center">
<td style="text-align:center">职位名称</td>
<td style="text-align:center"><input type="text" id="PositionName" name="PositionName" onkeyup="this.value=this.value.replace(/^\s+$/gi,'');"/></td>
</tr>
<tr style="text-align:center">
<td style="text-align:center"><input type="button" value = "提交" id = "confirm"/></td>
<td style="text-align:center"><input type="reset" value = "清除"/></td>
</tr>
</table>
</form>
</body>
</html>D.控制层
package com.ambow.controller;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.alibaba.fastjson.JSON;
import com.ambow.DAO.noteDao;
import com.ambow.entity.Notes;
import com.ambow.factory.noteFactory;
import com.ambow.util.JSONUtil;
@WebServlet("/note.do")
public class NoteController extends HttpServlet {
private static final long serialVersionUID = 1L;
public NoteController() {
super();
// TODO Auto-generated constructor stub
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doPost(request, response);
}
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
PrintWriter out = response.getWriter();
String opt = request.getParameter("opt");
ArrayList<Notes> list = new ArrayList<Notes>();
noteDao dao = noteFactory.getNoteInstance();
if(opt.equals("queryAll")) {
try {
list = dao.queryAll();
String json = JSONUtil.NoteTypetoJSONString(list);
out.write(json);
} catch (SQLException e) {
}
}else if(opt.equals("queryByPK")) {
int noteId = Integer.parseInt(request.getParameter("noteId"));
Notes note = new Notes();
try {
note = dao.queryByPK(noteId);
note.setStartDate(note.getStartDate().substring(0, 10));
note.setEndDate(note.getEndDate().substring(0, 10));
String json = JSON.toJSONString(note);
out.write(json);
} catch (SQLException e) {
}
}else if(opt.equals("queryByPK2")) {
int noteId = Integer.parseInt(request.getParameter("num"));
Notes note = new Notes();
try {
note = dao.queryByPK(noteId);
note.setStartDate(note.getStartDate().substring(0, 10));
note.setEndDate(note.getEndDate().substring(0, 10));
list.add(note);
String json = JSONUtil.NoteTypetoJSONString(list);
out.write(json);
} catch (SQLException e) {
}
}else if(opt.equals("queryAllnotHashMap")) {
try {
list = dao.queryAll();
String json = JSON.toJSONString(list);
out.write(json);
} catch (SQLException e) {
}
}else if(opt.equals("update")) {
int noteId = Integer.parseInt(request.getParameter("noteId"));
int employeeID = Integer.parseInt(request.getParameter("EmployeeID"));
int adminID = Integer.parseInt(request.getParameter("AdminID"));
int noteType = Integer.parseInt(request.getParameter("NoteType"));
String fillInTime = request.getParameter("FillInTime");
String cause = request.getParameter("Cause");
String startDate = request.getParameter("StartDate");
int startTime = Integer.parseInt(request.getParameter("StartTime"));
String endDate = request.getParameter("EndDate");
int endTime = Integer.parseInt(request.getParameter("EndTime"));
String directorSign = request.getParameter("DirectorSign");
String administrationSign = request.getParameter("AdministrationSign");
String presidentSign = request.getParameter("PresidentSign");
String noteMemo = request.getParameter("NoteMemo");
int isVerify = Integer.parseInt(request.getParameter("IsVerify"));
Notes note = new Notes(noteId, 0, employeeID, noteType, cause, fillInTime, directorSign,
administrationSign, presidentSign, startDate, startTime, endDate, endTime, adminID, noteMemo, isVerify);
note.setStartDate(note.getStartDate().substring(0, 10));
note.setEndDate(note.getEndDate().substring(0, 10));
note.setFillInTime(note.getFillInTime().substring(0, 10));
try {
dao.update(note);
} catch (SQLException e) {
e.printStackTrace();
}
}else if (opt.equals("add")) {
int employeeID = Integer.parseInt(request.getParameter("EmployeeID"));
int adminID = Integer.parseInt(request.getParameter("AdminID"));
int noteType = Integer.parseInt(request.getParameter("NoteType"));
String fillInTime = request.getParameter("FillInTime");
String cause = request.getParameter("Cause");
String startDate = request.getParameter("StartDate");
int startTime = Integer.parseInt(request.getParameter("StartTime"));
String endDate = request.getParameter("EndDate");
int endTime = Integer.parseInt(request.getParameter("EndTime"));
String directorSign = request.getParameter("DirectorSign");
String administrationSign = request.getParameter("AdministrationSign");
String presidentSign = request.getParameter("PresidentSign");
String noteMemo = request.getParameter("NoteMemo");
int isVerify = Integer.parseInt(request.getParameter("IsVerify"));
Notes note = new Notes(0, 0, employeeID, noteType, cause, fillInTime, directorSign,
administrationSign, presidentSign, startDate, startTime, endDate, endTime, adminID, noteMemo, isVerify);
try {
dao.add(note);
} catch (SQLException e) {
e.printStackTrace();
}
out.flush();
out.close();
}else if(opt.equals("delete")) {
String []noteID = request.getParameterValues("noteID");
int num = noteID.length;
try {
dao.delete(noteID);
out.write("删除成功！成功删除"+num+"个单据");
} catch (SQLException e) {
out.write("该单据下有考勤记录不可删除！");
}
out.flush();
out.close();
}
out.flush();
out.close();
}
}
E.实体层
package com.ambow.entity;
/**
* @author TianZW
* 单据表结构
* */
public class Notes {
private int noteID ;//单据ID
private int departmentID ;//部门编号
private int employeeID ;//申请人
private int noteType ;//单据类型
private String cause ;//事由
private String fillInTime ;//填表日期
private String directorSign ;//主管意见
private String administrationSign;//经理意见
private String presidentSign;//总裁意见
private String startDate;//开始日期
private int startTime;//开始时段
private String endDate ;//结束日期
private int endTime;//结束时段
private int adminID ;//录入人
private String noteMemo ;//备注
private int isVerify ;//是否审核
}F.接口层
package com.ambow.DAO;
import java.sql.SQLException;
import java.util.ArrayList;
import com.ambow.entity.Notes;
public interface noteDao {
ArrayList<Notes> queryAll() throws SQLException;
void add(Notes note)throws SQLException;
void delete(String[] noteID)throws SQLException;
Notes queryByPK(int noteId)throws SQLException;
void update(Notes note)throws SQLException;
}
G.实现层
package com.ambow.DAOImp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.ambow.DAO.noteDao;
import com.ambow.DB.DBConnection;
import com.ambow.entity.Notes;
public class noteDAOImp implements noteDao{
/**
* @author TianZW
* @throws SQLException
* @return ArrayList<Notes>
* */
public ArrayList<Notes> queryAll() throws SQLException {
Connection conn = DBConnection.getConnection();
ArrayList<Notes> list = new ArrayList<Notes>();
String sql = "select NOTEID,DEPARTMENTID,EMPLOYEEID,NOTETYPE,CAUSE,FILLINTIME,DIRECTORSIGN,ADMINISTRATIONSIGN,"
+ "PRESIDENTSIGN,STARTDATE,STARTTIME,ENDDATE,ENDTIME,ADMINID,NOTEMEMO,ISVERIFY from ATT_NOTES";
PreparedStatement state =conn.prepareStatement(sql);
ResultSet rs = state.executeQuery();
while(rs.next()) {
int noteID = rs.getInt("noteID");
int departmentID = rs.getInt("departmentID");
int employeeID = rs.getInt("employeeID");
int noteType = rs.getInt("noteType");
String cause = rs.getString("cause");
String fillInTime = (rs.getString("fillInTime")).substring(0, 10);
String directorSign = rs.getString("directorSign");
String administrationSign = rs.getString("administrationSign");
String presidentSign = rs.getString("presidentSign");
String startDate = rs.getString("startDate");
int startTime = rs.getInt("startTime");
String endDate = rs.getString("endDate");
int endTime = rs.getInt("endTime");
int adminID = rs.getInt("adminID");
String noteMemo = rs.getString("noteMemo");
int isVerify = rs.getInt("isVerify");
Notes note = new Notes(noteID, departmentID, employeeID, noteType, cause,
fillInTime, directorSign, administrationSign, presidentSign, startDate, startTime,
endDate, endTime, adminID, noteMemo, isVerify);
list.add(note);
}
DBConnection.closeConnection(conn, state, rs);
return list;
}
/**
* @author TianZW
* @throws SQLException
* */
public void add(Notes note) throws SQLException {
Connection conn = DBConnection.getConnection();
String sql = "select DEPARTMENT from ATT_EMPLOYEES where EMPLOYEEID = ?";
PreparedStatement state = conn.prepareStatement(sql);
state.setInt(1, note.getEmployeeID());
ResultSet rs = state.executeQuery();
int DEPARTMENTID=-1;
if(rs.next()) {
DEPARTMENTID = rs.getInt("DEPARTMENT");
}
DBConnection.closeConnection(null, state, rs);
String sql2 = "insert into ATT_NOTES(DEPARTMENTID,EMPLOYEEID,NOTETYPE,CAUSE,FILLINTIME,DIRECTORSIGN,ADMINISTRATIONSIGN,"
+ "PRESIDENTSIGN,STARTDATE,STARTTIME,ENDDATE,ENDTIME,ADMINID,NOTEMEMO,ISVERIFY) values(?,?,?,?,to_date(?,'yyyy-MM-dd'),"
+ "?,?,?,to_date(?,'yyyy-MM-dd'),?,to_date(?,'yyyy-MM-dd'),?,?,?,?)";
PreparedStatement state2 = conn.prepareStatement(sql2);
state2.setInt(1, DEPARTMENTID);
state2.setInt(2,note.getEmployeeID());
state2.setInt(3,note.getNoteType());
state2.setString(4, note.getCause());
state2.setString(5, note.getFillInTime());
state2.setString(6, note.getDirectorSign());
state2.setString(7, note.getAdministrationSign());
state2.setString(8, note.getPresidentSign());
state2.setString(9, note.getStartDate());
state2.setInt(10, note.getStartTime());
state2.setString(11, note.getEndDate());
state2.setInt(12, note.getEndTime());
state2.setInt(13, note.getAdminID());
state2.setString(14, note.getNoteMemo());
state2.setInt(15, note.getIsVerify());
state2.executeQuery();
DBConnection.closeConnection(conn, state2, null);
}
/**
* @author TianZW
* @throws SQLException
* */
public void delete(String[] noteID) throws SQLException {
Connection conn = DBConnection.getConnection();
for (String i : noteID) {
String sql = "DELETE FROM ATT_NOTES where NOTEID = ?";
PreparedStatement state = conn.prepareStatement(sql);
state.setString(1, i);
state.executeQuery();
state.close();
}
DBConnection.closeConnection(conn, null, null);
}
/**
* @author TianZW
* @throws SQLException
* @return ArrayList<Notes>
* */
public Notes queryByPK(int noteId) throws SQLException {
Notes note = new Notes();
Connection conn = DBConnection.getConnection();
String sql = "select NOTEID,DEPARTMENTID,EMPLOYEEID,NOTETYPE,CAUSE,FILLINTIME,DIRECTORSIGN,"
+ "ADMINISTRATIONSIGN,PRESIDENTSIGN,STARTDATE,STARTTIME,ENDDATE,ENDTIME,ADMINID,NOTEMEMO,"
+ "ISVERIFY from ATT_NOTES where NOTEID = ?";
PreparedStatement state =conn.prepareStatement(sql);
state.setInt(1, noteId);
ResultSet rs = state.executeQuery();
while(rs.next()) {
int noteID = rs.getInt("noteID");
int departmentID = rs.getInt("departmentID");
int employeeID = rs.getInt("employeeID");
int noteType = rs.getInt("noteType");
String cause = rs.getString("cause");
String fillInTime = (rs.getString("fillInTime")).substring(0, 10);
String directorSign = rs.getString("directorSign");
String administrationSign = rs.getString("administrationSign");
String presidentSign = rs.getString("presidentSign");
String startDate = rs.getString("startDate");
int startTime = rs.getInt("startTime");
String endDate = rs.getString("endDate");
int endTime = rs.getInt("endTime");
int adminID = rs.getInt("adminID");
String noteMemo = rs.getString("noteMemo");
int isVerify = rs.getInt("isVerify");
note = new Notes(noteID, departmentID, employeeID, noteType, cause,
fillInTime, directorSign, administrationSign, presidentSign, startDate, startTime,
endDate, endTime, adminID, noteMemo, isVerify);
}
DBConnection.closeConnection(conn, state, rs);
return note;
}
@Override
public void update(Notes note) throws SQLException {
Connection conn = DBConnection.getConnection();
String sql = "select DEPARTMENT from ATT_EMPLOYEES where EMPLOYEEID = ?";
PreparedStatement state = conn.prepareStatement(sql);
state.setInt(1, note.getEmployeeID());
ResultSet rs = state.executeQuery();
int DEPARTMENTID=-1;
if(rs.next()) {
DEPARTMENTID = rs.getInt("DEPARTMENT");
}
DBConnection.closeConnection(null, state, rs);
String sql2 = "UPDATE ATT_NOTES set DEPARTMENTID=?,EMPLOYEEID=?,NOTETYPE=?,CAUSE=?,"
+ "FILLINTIME=to_date(?,'yyyy-MM-dd'),DIRECTORSIGN=?,ADMINISTRATIONSIGN=?,PRESIDENTSIGN=?,"
+ "STARTDATE=to_date(?,'yyyy-MM-dd'),STARTTIME=?,ENDDATE=to_date(?,'yyyy-MM-dd'),ENDTIME=?,"
+ "ADMINID=?,NOTEMEMO=?,ISVERIFY=? where noteId = ?";
PreparedStatement state2 = conn.prepareStatement(sql2);
state2.setInt(1, DEPARTMENTID);
state2.setInt(2,note.getEmployeeID());
state2.setInt(3,note.getNoteType());
state2.setString(4, note.getCause());
state2.setString(5, note.getFillInTime());
state2.setString(6, note.getDirectorSign());
state2.setString(7, note.getAdministrationSign());
state2.setString(8, note.getPresidentSign());
state2.setString(9, note.getStartDate());
state2.setInt(10, note.getStartTime());
state2.setString(11, note.getEndDate());
state2.setInt(12, note.getEndTime());
state2.setInt(13, note.getAdminID());
state2.setString(14, note.getNoteMemo());
state2.setInt(15, note.getIsVerify());
state2.setInt(16, note.getNoteID());
state2.executeUpdate();
DBConnection.closeConnection(conn, state2, null);
}
}
（3）功能简介
本模块为添加单据，单据类型、员工编号和名称、部门编号和名称等都需要做外连接查出，后插入单据表，查询数据时也需要做长连接，详情请参见实现层代码。
3.统计查询（实现方式与前面几张表结构相似，不做赘述。只是数据库实现稍有难度，在此将贴出）
前端显示
数据库实现层：
package com.ambow.sumaryDAOImp;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import org.junit.Test;
import ytu.edu.bean.QueryBean;
import ytu.edu.bean.RecordBean;
import ytu.edu.dao.QueryDao;
import ytu.edu.untils.DBHelper;
public class QueryDaoImp implements QueryDao {
ResultSet set = null;
/**
* @param where
* @return ArrayList<QueryBean>
* @throws SQLException
*/
public ArrayList<QueryBean> QueryByDept(QueryBean where) throws SQLException {
String sql = "select employeename,e.employeeid,"
+ "count(case when attendancetypeid=1 then attendancetypeid end) attendancecount,"
+ "count(case when attendancetypeid=2 then attendancetypeid end) restcount,"
+ "count(case when attendancetypeid=3 then attendancetypeid end) latecount,"
+ "count(case when attendancetypeid=4 then attendancetypeid end) absenteeismcount,"
+ "count(case when attendancetypeid=5 then attendancetypeid end) outcount,"
+ "count(case when attendancetypeid=6 then attendancetypeid end) tripcount,"
+ "count(case when attendancetypeid in (select typeid from att_attendancetype where typecategory=1) then attendancetypeid end) leavecount "
+ "from att_attendancerecord r,att_employees e where r.employeeid=e.employeeid "
+ "and attendancedate between ? and ? and e.departmentid=? group by employeename,e.employeeid";
set = DBHelper.execQuery(sql, Date.valueOf(where.getBegindate()),Date.valueOf(where.getOverdate()),where.getDepartmentid());
ArrayList<QueryBean> list = new ArrayList<QueryBean>();
while(set.next())
{
int employeeid = set.getInt("employeeid");
String employeename = set.getString("employeename");
int attendancecount = set.getInt("attendancecount");
int restcount = set.getInt("restcount");
int latecount = set.getInt("latecount");
int absenteeismcount = set.getInt("absenteeismcount");
int outcount = set.getInt("outcount");
int tripcount = set.getInt("tripcount");
int leavecount = set.getInt("leavecount");
QueryBean query = new QueryBean(employeeid, employeename, attendancecount, restcount, latecount, absenteeismcount, outcount, tripcount, leavecount);
list.add(query);
}
DBHelper.closeAll();
return list;
}
/**
* @return 当日通告，查出迟到和旷工的人
* @throws SQLException
*/
public ArrayList<QueryBean> GetNotice(String date) throws SQLException{
String sql = "select p.departmentname parentname,d.departmentname,e.employeeid,employeename,"
+ "typename from att_attendancerecord r,att_attendancetype t,"
+ "att_employees e,att_department d,att_department p where d.parentid=p.departmentid "
+ "and e.departmentid=d.departmentid and e.employeeid=r.employeeid and "
+ "attendancetypeid=typeid and attendancedate=? and "
+ "(typename='迟到' or typename='旷工') order by p.departmentname";
ArrayList<QueryBean> list = new ArrayList<QueryBean>();
set = DBHelper.execQuery(sql, Date.valueOf(date));
while(set.next())
{
int employeeid = set.getInt("employeeid");
String parentname = set.getString("parentname");
String departmentname = set.getString("departmentname");
String employeename = set.getString("employeename");
String typename = set.getString("typename");
QueryBean query = new QueryBean(employeeid, employeename, parentname, departmentname, typename);
list.add(query);
}
return list;
}
/**
* @return通过员工编号，查看员工考勤详情
*/
public ArrayList<RecordBean> QueryByEmpid(QueryBean where) throws SQLException {
String sql = "select attendanceid,employeename,attendancedate,adminname,"
+ "noteid,typename,attendancememo from att_attendancerecord r,"
+ "att_employees e,att_attendancetype t,att_admin a where "
+ "r.adminid=a.adminid and typeid=attendancetypeid and "
+ "r.employeeid=e.employeeid and r.employeeid=? and attendancedate "
+ "between ? and ? order by attendancedate";
set = DBHelper.execQuery(sql, where.getEmployeeid(),Date.valueOf(where.getBegindate()),Date.valueOf(where.getOverdate()));
ArrayList<RecordBean> list = new ArrayList<RecordBean>();
while(set.next())
{
int attendanceid = set.getInt("attendanceid");
String employeename = set.getString("employeename");
String attendancedate = set.getString("attendancedate");
String adminname = set.getString("adminname");
String typename = set.getString("typename");
int noteid = set.getInt("noteid");
String attendancememo = set.getString("attendancememo");
RecordBean record = new RecordBean(attendanceid, attendancedate, noteid, attendancememo, employeename, typename, adminname);
list.add(record);
}
DBHelper.closeAll();
return list;
}
@Override
public ArrayList<QueryBean> QueryByParent(QueryBean where) throws SQLException {
String sql = "select employeename,departmentname,e.employeeid,count(case when attendancetypeid=1 then attendancetypeid end) attendancecount,count(case when attendancetypeid=2 then attendancetypeid end) restcount,count(case when attendancetypeid=3 then attendancetypeid end) latecount,count(case when attendancetypeid=4 then attendancetypeid end) absenteeismcount,count(case when attendancetypeid=5 then attendancetypeid end) outcount,count(case when attendancetypeid=6 then attendancetypeid end) tripcount,count(case when attendancetypeid in (select typeid from att_attendancetype where typecategory=1) then attendancetypeid end) leavecount from att_attendancerecord r,att_employees e,att_department d where d.departmentid=e.departmentid and r.employeeid=e.employeeid and attendancedate between ? and ? and e.departmentid in(select departmentid from att_department where parentid=?) group by employeename,e.employeeid,departmentname order by departmentname";
set = DBHelper.execQuery(sql,Date.valueOf(where.getBegindate()),Date.valueOf(where.getOverdate()),where.getDepartmentid());
ArrayList<QueryBean> list = new ArrayList<QueryBean>();
while(set.next())
{
int employeeid = set.getInt("employeeid");
String departmentname = set.getString("departmentname");
String employeename = set.getString("employeename");
int attendancecount = set.getInt("attendancecount");
int restcount = set.getInt("restcount");
int latecount = set.getInt("latecount");
int absenteeismcount = set.getInt("absenteeismcount");
int outcount = set.getInt("outcount");
int tripcount = set.getInt("tripcount");
int leavecount = set.getInt("leavecount");
QueryBean query = new QueryBean(employeeid, departmentname, employeename, attendancecount, restcount, latecount, absenteeismcount, outcount, tripcount, leavecount);
list.add(query);
}
DBHelper.closeAll();
return list;
}
}
1960
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
