567 SXTOA 项目原型简介
RBAC权限设计!
568 数据库设计分析
create table Employee
(
empid VARCHAR(10) not null,
password VARCHAR(10),
deptno int(3),
posid int(5),
mgrid VARCHAR(10),
realname VARCHAR(12),
sex CHAR(3),
birthdate DATE,
hiredate DATE,
leavedate DATE,
onduty int(1) comment '0-离职 1-在职',
emptype int(1) comment '1.普通员工 2.管理人员 含经理、总监、总裁等 3.管理员',
phone CHAR(11),
qq VARCHAR(10),
emercontactperson VARCHAR(200),
idcard CHAR(18),
constraint PK_EMPLOYEE primary key (empid)
);
create table ExpImage
(
imgid int(10) auto_increment not null,
expid int(10),
realname VARCHAR(100),
filename VARCHAR(100),
filetype VARCHAR(20),
constraint PK_EXPIMAGE primary key (imgid)
);
create table auditing
(
auditid int(10) auto_increment not null,
expid int(10),
empid VARCHAR(10),
result VARCHAR(10),
auditdesc VARCHAR(50),
time datetime,
constraint PK_AUDITING primary key (auditid)
);
create table dept
(
deptno int(3) not null,
deptname VARCHAR(15),
location VARCHAR(5),
constraint PK_DEPT primary key (deptno)
);
create table duty
(
dtid int(10) auto_increment not null,
emprid VARCHAR(10),
dtdate date,
signintime time,
signouttime time,
constraint PK_DUTY primary key (dtid)
);
create table expense
(
expid int(10) auto_increment not null,
empid VARCHAR(10),
totalamount double(10,2),
exptime datetime,
expdesc VARCHAR(100),
nextauditor VARCHAR(10),
lastResult VARCHAR(20),
status CHAR(1) COMMENT '1:审核中 2 审核结束通过 3 审核结束驳回',
constraint PK_EXPENSE primary key (expid)
);
create table expenseitem
(
itemid int(10) auto_increment not null,
expid int(10),
type VARCHAR(10),
amount double(7,2),
itemdesc VARCHAR(50),
constraint PK_EXPENSEITEM primary key (itemid)
);
create table income
(
icid int(10) auto_increment not null,
amount int(10),
icdate DATETIME,
ictype VARCHAR(10),
indesc VARCHAR(100),
userid VARCHAR(10),
constraint PK_INCOME primary key (icid)
);
create table payment
(
pid int(10) auto_increment not null,
payempid VARCHAR(10),
amount double(10,2),
paytime DATETIME,
expid int(10),
empid VARCHAR(10),
constraint PK_PAYMENT primary key (pid)
);
create table position
(
posid int(5) not null,
pname VARCHAR(15),
pdesc VARCHAR(100),
constraint PK_POSITION primary key (posid)
);
alter table Employee
add constraint FK_EMPLOYEE_REFERENCE_DEPT foreign key (deptno)
references dept (deptno);
alter table Employee
add constraint FK_EMPLOYEE_REFERENCE_POSITION foreign key (posid)
references position (posid);
alter table Employee
add constraint FK_EMPLOYEE_REFERENCE_EMPLOYEE foreign key (mgrid)
references Employee (empid);
alter table ExpImage
add constraint FK_EXPIMAGE_REFERENCE_EXPENSE foreign key (expid)
references expense (expid);
alter table auditing
add constraint FK_AUDITING_REFERENCE_EMPLOYEE foreign key (empid)
references Employee (empid);
alter table auditing
add constraint FK_AUDITING_REFERENCE_EXPENSE foreign key (expid)
references expense (expid);
alter table duty
add constraint FK_DUTY_REFERENCE_EMPLOYEE foreign key (emprid)
references Employee (empid);
alter table expense
add constraint FK_EXPENSE_REFERENCE_EMPLOYEE foreign key (empid)
references Employee (empid);
alter table expenseitem
add constraint FK_EXPENSEI_REFERENCE_EXPENSE foreign key (expid)
references expense (expid);
alter table income
add constraint FK_INCOME_REFERENCE_EMPLOYEE foreign key (userid)
references Employee (empid);
alter table payment
add constraint FK_PAYMENT_REFERENCE_EXPENSE foreign key (expid)
references expense (expid);
569 项目结构搭建
1.导包
2 拷贝sxtoa界面原型到web下!
3 src下创建结构
570 添加部门
1 Dept DBUtil
public class DBUtil {
private static SqlSessionFactory factory;
private static ThreadLocal<SqlSession> tl=new ThreadLocal<>();
static {
InputStream inputStream = null;
try {
//[1]解析myBatis.xml文件
inputStream = Resources.getResourceAsStream("mybatis.xml");
//[2]获得sqlsession工厂
factory=new SqlSessionFactoryBuilder().build(inputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
//获得sqlsession对象
public static SqlSession getSqlSession(){
//获得ThreadLoacl中的sqlsession对象
SqlSession sqlSession = tl.get();
if(sqlSession==null){
sqlSession = factory.openSession(true);
//把创建好的对象放到ThreadLoacl
tl.set(sqlSession);
}
return tl.get();
}
//关闭sqlsession
public static void closeAll(){
SqlSession sqlSession = tl.get();
if(sqlSession!=null){
sqlSession.close();
}
tl.set(null);
}
}
public class Dept {
private Integer deptno;
private String deptname;
private String location;
}
2 DeptMapper DeptMapper.xml
public interface DeptMapper {
//添加部门操作
int insert(Dept dept);
}
<mapper namespace="com.bjsxt.mapper.DeptMapper">
<insert id="insert">
insert into Dept values(#{deptno} ,#{deptname},#{location})
</insert>
</mapper>
3 DeptService DeptServiceImpl
public interface DeptService {
//添加部门
int save(Dept dept);
}
package com.bjsxt.service.imp;
public class DeptServiceImpl implements DeptService {
@Override
public int save(Dept dept) {
DeptMapper mapper = DBUtil.getSqlSession().getMapper(DeptMapper.class);
int i = mapper.insert(dept);
DBUtil.closeAll();
return i; }}
4 DeptServlet
package com.bjsxt.servlet;
//两级目录 接下来有过滤器
@WebServlet("/sxt/DeptServlet")
public class DeptServlet extends HttpServlet {
private DeptService deptService= new DeptServiceImpl();
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
req.setCharacterEncoding("utf-8");
resp.setContentType("text/html;charset=utf-8");
String method = req.getParameter("method");
if("deptSave".equals(method)){
deptSave(req,resp);
}
}
private void deptSave(HttpServletRequest req, HttpServletResponse resp) throws IOException, ServletException {
String d = req.getParameter("deptno");
int deptno = Integer.parseInt(d);
String deptname = req.getParameter("deptname");
String location = req.getParameter("location");
Dept dept = new Dept(deptno,deptname,location);
//数据处理
int save = deptService.save(dept);
//给客户作响应
if(save>0){
resp.sendRedirect(req.getContextPath()+"/deptList.html");
}else{
req.setAttribute("msg","添加失败");
req.getRequestDispatcher("/deptAdd.html").forward(req,resp);
}
}
}
deptAdd.html
<form action="sxt/DeptServlet?method=deptSave" method="post">
<ul class="forminfo">
<li><label>部门编号</label><input name="deptno" type="text"
class="dfinput" /> </li>
<li><label>部门名称</label><input name="deptname" type="text"
class="dfinput" /> </li>
<li><label>办公地点</label><input name="location" type="text"
class="dfinput" /></li>
<li><label> </label><input name="" type="submit" class="btn"
value="确认保存"/></li>
</ul>
</form>
571 查询部门
1 DeptMapper DeptMapper.xml
//查询部门操作
List<Dept> selectAll();
<select id="selectAll" resultType="dept">
select * from dept;
</select>
2 DeptService DeptServiceImpl
//查询所有部门
List<Dept> findAll();
@Override
public List<Dept> findAll() {
DeptMapper mapper = DBUtil.getSqlSession().getMapper(DeptMapper.class);
List<Dept> list = mapper.selectAll();
DBUtil.closeAll();
return list;
}
3 DeptServlet
//查询部门信息
private void deptFindAll(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//接收页面数据
req.setCharacterEncoding("utf-8");
resp.setContentType("text/html;charset=utf-8");
//处理数据
List<Dept> list = deptService.findAll();
//作出响应
req.setAttribute("list",list);
req.getRequestDispatcher("/deptList.jsp").forward(req,resp);
}
4 deptList.jsp
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
...
<base href="<%=request.getContextPath()+"/"%>">
...
<c:forEach items="${requestScope.list}" var="dept">
<tr>
<td><input name="" type="checkbox" value="" /></td>
<td>${dept.deptno}</td>
<td>${dept.deptname}</td>
<td>${dept.location}</td>
<td><a href="deptUpdate.html" class="tablelink">修改</a> <a href="#" class="tablelink click"> 删除</a></td>
</tr>
</c:forEach>
...
aaaa
1 DeptMapper DeptMapper.xml
2 DeptService DeptServiceImpl
3 DeptServlet
572 修改部门A
1 DeptMapper DeptMapper.xml
//修改部门操作
int update(Dept dept);
//查询部门的操作
Dept selectOne(Integer deptno);
<update id="update">
update dept set deptname=#{deptname},location=#{location} where
deptno=#{deptno}
</update>
<select id="selectOne" resultType="dept">
select * from dept where deptno=#{param1}
</select>
2 DeptService DeptServiceImpl
//修改部门的操作
int change(Dept dept);
//查询部门操作
Dept findOne(int deptno);<