servlet mysql json数据库_jsp+servlet+mysql+json+ajax开发的java权限管理系统，包括项目需求...

use company;

drop table if exists tt_dept;

create table tt_dept (

dept_id int not null auto_increment PRIMARY key,

dept_mgr_id int not null ,

dept_name VARCHAR(30) UNIQUE,

dept_addr VARCHAR(100),

tel VARCHAR(20),

fax VARCHAR(30),

add_time datetime

)auto_increment=100000;

insert into tt_dept(dept_mgr_id,dept_name,dept_addr,tel,fax,add_time)

values(100000,'软件部','广东省深圳市龙岗区坑梓镇龙湾路168号','0755-233556348','0755-233557366',NOW());

insert into tt_dept(dept_mgr_id,dept_name,dept_addr,tel,fax,add_time)

values(100001,'学习部','广东省深圳市龙岗区坑梓镇龙湾路168号','0755-233556348','0755-233557366',NOW());

insert into tt_dept(dept_mgr_id,dept_name,dept_addr,tel,fax,add_time)

values(100002,'小卖部','广东省深圳市龙岗区坑梓镇龙湾路168号','0755-233556348','0755-233557366',NOW());

insert into tt_dept(dept_mgr_id,dept_name,dept_addr,tel,fax,add_time)

values(100003,'中专部','广东省深圳市龙岗区坑梓镇龙湾路168号','0755-233556348','0755-233557366',NOW());

insert into tt_dept(dept_mgr_id,dept_name,dept_addr,tel,fax,add_time)

values(100004,'武装部','广东省深圳市龙岗区坑梓镇龙湾路168号','0755-233556348','0755-233557366',NOW());

insert into tt_dept(dept_mgr_id,dept_name,dept_addr,tel,fax,add_time)

values(100005,'体育部','广东省深圳市龙岗区坑梓镇龙湾路168号','0755-233556348','0755-233557366',NOW());

drop table if exists tt_dept_user;

create table tt_dept_user(

user_id int not null auto_increment PRIMARY key,

user_name VARCHAR(30) UNIQUE,

first_name varchar(10),

last_name varchar(20),

password VARCHAR(30),

dept_id int not null,

add_time datetime,

sex int,

email varchar(50),

phone varchar(11),

tel VARCHAR(20),

birthday date,

address varchar(100),

id_card varchar(18),

image varchar (100),

remark varchar(200),

status int

)auto_increment=100000;

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('admin','周','海波','123456',100000,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('jeremy','张','三','123456',100001,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('john','王','五','123456',100002,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('jelly','赵','六','123456',100003,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('tom','周','三','123456',100004,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('mary','钱','六','123456',100001,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('sam','小','五','123456',100002,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('summer','小','张','123456',100003,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('mine','小','朱','123456',100004,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('sunny','杨','康','123456',100003,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('only','朱','名','123456',100005,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('user','中','国','123456',100003,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('summy','王','强','123456',100004,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('hony','赵','找','123456',100004,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('apple','宋','家','123456',100004,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('sunshine','小','成','123456',100002,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

insert into tt_dept_user(user_name,first_name,last_name,password,dept_id,add_time,sex,email,phone,tel,birthday,address,id_card,image,remark,status)

VALUES ('tony','小','阳','123456',100004,NOW(),1,'[email protected]','15207442209','075584351234',NOW(),'深圳','430525199902262738','D:/2.jpg','呵呵',1);

drop TABLE if EXISTS tt_role;

create table tt_role(

role_id int not null auto_increment primary key,

role_name varchar(20) UNIQUE,

role_desc VARCHAR(200)

)auto_increment=100000;

insert into tt_role(role_name,role_desc) values('部门经理','部门事务管理');

insert into tt_role(role_name,role_desc) values('人事经理','人员事务管理');

insert into tt_role(role_name,role_desc) values('财务经理','财务事务管理');

insert into tt_role(role_name,role_desc) values('市场经理','市场事务管理');

insert into tt_role(role_name,role_desc) values('股东经理','股东事务管理');

insert into tt_role(role_name,role_desc) values('学习部经理','股东事务管理');

drop table if exists tt_menu;

create table tt_menu(

menu_id int not null auto_increment PRIMARY key,

menu_name VARCHAR(30) UNIQUE,

menu_desc varchar(200),

menu_value varchar(100) unique,

father_menu_id int

)auto_increment=100000;

insert into tt_menu(menu_name,menu_desc,menu_value,father_menu_id) values('部门管理','所有部门信息','http://localhost:8080/RBAC/loginConfirm#dept',1);

insert into tt_menu(menu_name,menu_desc,menu_value,father_menu_id) values('用户管理','所有用户信息','http://localhost:8080/RBAC/loginConfirm#user',1);

insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id)values('角色管理','所有角色信息','http://localhost:8080/RBAC/loginConfirm#role',1);

insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) values('菜单管理','所有菜单信息','http://localhost:8080/RBAC/loginConfirm#menu',1);

insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) values('角色人员管理','所有角色人员信息','http://localhost:8080/RBAC/loginConfirm#roleMem',1);

insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) values('权限管理','所有权限信息','http://localhost:8080/RBAC/loginConfirm#authority',1);

insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('部门增加','部门信息增加','http://localhost:8080/RBAC/Department/Department_add.jsp',100000);

insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('部门维护','部门信息维护','http://localhost:8080/RBAC/selectDepartment',100000);

insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('用户增加','用户信息增加','http://localhost:8080/RBAC/getDeptNameList',100001);

insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('用户维护','用户信息维护','http://localhost:8080/RBAC/selectUser',100001);

insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('角色增加','角色信息增加','http://localhost:8080/RBAC/Role/role_add.jsp',100002);

insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('角色维护','角色信息维护','http://localhost:8080/RBAC/selectRole',100002);

insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('菜单增加','菜单信息增加','http://localhost:8080/RBAC/selectFatherMenu',100003);

insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('菜单维护','菜单信息维护','http://localhost:8080/RBAC/selectMenu',100003);

insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('角色人员维护','角色人员信息维护','http://localhost:8080/RBAC/selectRoleUser',100004);

insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('用户权限设置','用户菜单信息维护','http://localhost:8080/RBAC/selectUserMenu',100005);

insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('部门权限设置','部门菜单信息维护','http://localhost:8080/RBAC/selectDeptMenu',100005);

insert into tt_menu (menu_name,menu_desc,menu_value,father_menu_id) VALUES ('角色权限设置','角色菜单信息维护','http://localhost:8080/RBAC/selectRoleMenu',100005);

select* from tt_menu;

drop table if exists tt_role_user;

create table tt_role_user(

role_id int not null ,

user_id int not null

);

insert into tt_role_user (role_id,user_id) values(100000,100001);

insert into tt_role_user (role_id,user_id) values(100000,100002);

insert into tt_role_user (role_id,user_id) values(100001,100003);

insert into tt_role_user (role_id,user_id) values(100001,100004);

insert into tt_role_user (role_id,user_id) values(100002,100005);

insert into tt_role_user (role_id,user_id) values(100004,100006);

insert into tt_role_user (role_id,user_id) values(100003,100007);

insert into tt_role_user (role_id,user_id) values(100004,100008);

insert into tt_role_user (role_id,user_id) values(100004,100009);

insert into tt_role_user (role_id,user_id) values(100002,100011);

drop table if exists tt_user_menu;

create table tt_user_menu(

user_id int not null,

menu_id int not null

);

insert into tt_user_menu(user_id,menu_id) values(100000,100006);

insert into tt_user_menu (user_id,menu_id) values(100000,100007);

insert into tt_user_menu (user_id,menu_id) values(100000,100008);

insert into tt_user_menu (user_id,menu_id) values(100000,100009);

insert into tt_user_menu (user_id,menu_id) values(100000,1000010);

insert into tt_user_menu (user_id,menu_id) values(100000,100011);

insert into tt_user_menu (user_id,menu_id) values(100000,100012);

insert into tt_user_menu (user_id,menu_id) values(100000,100013);

insert into tt_user_menu (user_id,menu_id) values(100000,100014);

insert into tt_user_menu (user_id,menu_id) values(100000,100015);

insert into tt_user_menu (user_id,menu_id) values(100000,100016);

insert into tt_user_menu (user_id,menu_id) values(100000,100017);

insert into tt_user_menu (user_id,menu_id) values(100000,100018);

insert into tt_user_menu (user_id,menu_id) values(100001,100003);

insert into tt_user_menu (user_id,menu_id) values(100001,100004);

insert into tt_user_menu (user_id,menu_id) values(100002,100005);

insert into tt_user_menu (user_id,menu_id) values(100004,100006);

insert into tt_user_menu (user_id,menu_id) values(100003,100007);

insert into tt_role_user (role_id,user_id) values(100004,100008);

insert into tt_role_user (role_id,user_id) values(100004,100009);

insert into tt_role_user (role_id,user_id) values(100002,100011);

drop table if EXISTS tt_dept_menu;

create table tt_dept_menu(

dept_id int not null,

menu_id int not null

);

insert into tt_dept_menu (dept_id,menu_id) values(100000,100002);

insert into tt_dept_menu (dept_id,menu_id) values(100001,100003);

insert into tt_dept_menu (dept_id,menu_id) values(100001,100004);

insert into tt_dept_menu (dept_id,menu_id) values(100002,100005);

insert into tt_dept_menu (dept_id,menu_id) values(100004,100006);

insert into tt_dept_menu (dept_id,menu_id) values(100003,100007);

drop table if exists tt_role_menu;

create table tt_role_menu(

role_id int not null,

menu_id int not null

);

insert into tt_role_menu (role_id,menu_id) values(100000,100002);

insert into tt_role_menu (role_id,menu_id) values(100001,100003);

insert into tt_role_menu (role_id,menu_id) values(100001,100004);

insert into tt_role_menu (role_id,menu_id) values(100002,100005);

insert into tt_role_menu (role_id,menu_id) values(100004,100006);

insert into tt_role_menu (role_id,menu_id) values(100003,100007);

select * from tt_dept;

select * from tt_dept_user;

select * from tt_role;

select * from tt_role_user;

select * from tt_menu;

select * from tt_user_menu;

select * from tt_dept where 1=1 and dept_name like '%嘘唏%';

select * from tt_user_menu where user_id=100000 AND menu_id=100008;

select role_id,role_name,(select COUNT(*) from tt_role_user as t1 where t1.role_id =t2.role_id)as count from tt_role as t2 and t2.role_name like '%经理%'

select menu_id,menu_name from tt_menu where father_menu_id=1;

select menu_id,menu_name from tt_menu where father_menu_id=10000;

select * from tt_menu as t1, tt_user_menu as t2 where t1.father_menu_id=100000 and user_id =100000 and t1.menu_id=t2.menu_id;

select * from tt_menu as t1 where t1.father_menu_id=100000;

select t2.dept_id,t2.dept_name,(select COUNT(*) from tt_dept_menu as t1 where t1.dept_id=t2.dept_id) as count from tt_dept as t2 ORDER BY t2.dept_id;

select t2.role_id,t2.role_name,t2.role_desc,(select COUNT(*) from tt_role_menu as t1 where t1.role_id=t2.role_id) as count from tt_role as t2 ORDER BY t2.role_id;

select t1.menu_name,t2.user_name from tt_menu as t1,tt_dept_user as t2,tt_user_menu as t3 where t1.menu_id=t3.menu_id and t2.user_id=t3.user_id and t2.user_name='admin';

select m.menu_id,m.menu_name from tt_menu m where menu_id in(select menu_id from tt_user_menu where user_id=(select user_id from tt_dept_user where user_name='admin'))

or menu_id in (select menu_id from tt_dept_menu where dept_id=(select dept_id from tt_dept_user where user_id=(select user_id from tt_dept_user where user_name='admin')))

or menu_id in (select menu_id from tt_role_menu where role_id in (select role_id from tt_role_user where user_id=(select user_id from tt_dept_user where user_name='admin')))

select t2.* ,(select CONCAT(first_name,last_name) from tt_dept_user as t1 where t1.user_id=t2.dept_mgr_id) as dept_manager from tt_dept as t2

1.简单实用的html模板框架

2.只采用简单的jsp+servlet+mysql+json+ajax技术，适用简单初学者来参考学习

3.表单提交使用js，validateForm校验

4.输入框采用了时间日期控件，省市区三级联动的基本控件。

5.基本实现mvc模式，实现数据业务页面分层