1 user表的增删改查及相关功能
1.1 admin下的权限:
可以查询所有的user信息(除去密码):
select user_id,mobile,name,gender,birth,nickname,email,province,city,
edu_degree,graduation,gra_year,major,dir_desire
from user
<where>
<if test="mobile !=null and mobile !=' '">
mobile = #{mobile}
</if>
<if test="name !=null and name !=' '">
name like concat('%', #{name}, '%')
</if>
<if test="gender !=null">
gender = #{gender}
</if>
<if test="birth !=null and birth !=' '">
birth = #{birth}
</if>
<if test="nickname !=null and nickname !=' '">
nickname like concat('%', #{nickname}, '%')
</if>
<if test="province !=null and province !=' '">
province = #{province}
</if>
<if test="city !=null and city !=' '">
city = #{city}
</if>
<if test="eduDegree !=null and eduDegree !=' '">
edu_degree = #{eduDegree}
</if>
<if test="graduation !=null and graduation !=' '">
graduation = #{graduation}
</if>
<if test="graYear !=null and graYear !=0">
gra_year = #{graYear}
</if>
<if test="major !=null and major !=' '">
major = #{major}
</if>
</where>
删除user:
delete from user where mobile=#{mobile}
修改user:
update user set password=#{password},name = #{name}, gender=#{gender},
birth=#{birth},nickname=#{nickname},email=#{email},province=#{province},
city=#{city},edu_degree=#{eduDegree},graduation=#{graduation},
gra_year=#{graYear},major=#{major},dir_desire=#{dirDesire}
WHERE mobile = #{mobile}
1.2 user权限:
1.2.1 对自己的user信息:
查询所有信息:
select * from user where mobile=#{mobile} and password=#{password}
更改所有信息:
update user set mobile = #{mobile},password=#{password},name = #{name},
gender=#{gender},birth=#{birth},nickname=#{nickname},email=#{email},
province=#{province},city=#{city},edu_degree=#{eduDegree},
graduation=#{graduation},gra_year=#{graYear},major=#{major},
dir_desire=#{dirDesire}
WHERE user_id=#{userId}
注销账号:
delete from user where mobile=#{mobile} and password={password}
注册账号:
insert into user(user_id,mobile,password) values (#{userId},#{mobile},#{password})
1.2.2 对其他人的user信息:
查看其他人的所有信息(除去密码):
select user_id,mobile,name,gender,birth,nickname,email,province,city,
edu_degree,graduation,gra_year,major,dir_desire
from user
<where>
<if test="mobile !=null and mobile !=' '">
mobile = #{mobile}
</if>
<if test="name !=null and name !=' '">
name like concat('%', #{name}, '%')
</if>
<if test="gender !=null">
gender = #{gender}
</if>
<if test="birth !=null and birth !=' '">
birth = #{birth}
</if>
<if test="nickname !=null and nickname !=' '">
nickname like concat('%', #{nickname}, '%')
</if>
<if test="province !=null and province !=' '">
province = #{province}
</if>
<if test="city !=null and city !=' '">
city = #{city}
</if>
<if test="eduDegree !=null and eduDegree !=' '">
edu_degree = #{eduDegree}
</if>
<if test="graduation !=null and graduation !=' '">
graduation = #{graduation}
</if>
<if test="graYear !=null and graYear !=0">
gra_year = #{graYear}
</if>
<if test="major !=null and major !=' '">
major = #{major}
</if>
</where>
1.3 hr权限:
可以查询所有的user信息(除去密码):
select user_id,mobile,name,gender,birth,nickname,email,province,city,
edu_degree,graduation,gra_year,major,dir_desire
from user
<where>
<if test="mobile !=null and mobile !=' '">
mobile = #{mobile}
</if>
<if test="name !=null and name !=' '">
name like concat('%', #{name}, '%')
</if>
<if test="gender !=null">
gender = #{gender}
</if>
<if test="birth !=null and birth !=' '">
birth = #{birth}
</if>
<if test="nickname !=null and nickname !=' '">
nickname like concat('%', #{nickname}, '%')
</if>
<if test="province !=null and province !=' '">
province = #{province}
</if>
<if test="city !=null and city !=' '">
city = #{city}
</if>
<if test="eduDegree !=null and eduDegree !=' '">
edu_degree = #{eduDegree}
</if>
<if test="graduation !=null and graduation !=' '">
graduation = #{graduation}
</if>
<if test="graYear !=null and graYear !=0">
gra_year = #{graYear}
</if>
<if test="major !=null and major !=' '">
major = #{major}
</if>
</where>
2. user_introduction表的增删改查及相关功能
2.1admin下的权限:
查询所有信息:
select * from user_introduction
where user_id in (select user_id from user where mobile = #{mobile} )
删除信息:
delete from user_introduction
where user_id in (select user_id from user where mobile = #{mobile} )
更改信息:
update user_introduction
set ability= #{ability},internship=#{internship},workExperience=#{workExperience},certificate=#{certificate},job_desire=#{jobDesire},
WHERE user_id in (select user_id from user where mobile = #{mobile}
2.2 user下的权限:
2.2.1 对自己的user_introduction信息:
查询所有信息:
select * from user_introduction
where user_id in (select user_id from user where mobile = #{mobile} )
更改所有信息:
update user_introduction
set ability= #{ability},internship=#{internship},workExperience=#{workExperience},certificate=#{certificate},job_desire=#{jobDesire}
WHERE user_id in (select user_id from user where mobile = #{mobile}
注销账号:
delete from user_introduction
where user_id in (select user_id from user where mobile = #{mobile} )
注册账号:
insert into user(introduction_id,user_id)
values (#{introductionId},#{userId})
2.2.2 对其他人的user_introduction信息:
查看其他人的信息:
select * from user_introduction
where user_id = #{userId}
2.3 hr权限:
可以查询相应的user_introduction信息:
select * from user_introduction
where user_id = #{userId}
3. industry表的增删改查及相关功能
3.1 admin下的权限:
查询所有信息:
select * from industry
删除信息:
delete from industry
where industry_name = #{industryName}
更改信息:
update industry
set industry_name = #{industryName}
WHERE industry_id
插入信息:
insert into industry(industry_name)
values (#{industryName})
3.2 user下的权限:
查询所有信息:
select * from industry
3.3 hr权限:
查询所有信息:
select * from industry
4. industry_category表的增删改查及相关功能
4.1 admin下的权限:
查询所有信息:
select * from industry_category
where industry_id = #{industryId}
删除信息:
delete from industry_category
where category_id = #{categoryId}
更改信息:
update industry_category
set category_name = #{categoryName},description=#{description}
WHERE category_id
插入信息:
insert into industry_category(category_name,description,industry_id)
values (#{categoryName},#{description},#{industryId})
4.2 user下的权限:
查询所有信息:
select * from industry_category
4.3 hr权限:
查询所有信息:
select * from industry_category
5. hr表的增删改查及相关功能
5.1 admin下的权限:
可以查询所有的hr信息(除去密码):
select hr_id,mobile,name,email,description,company_id
from hr
where 1=1
<if test="mobile !=null and mobile !=' '">
mobile = #{mobile}
</if>
<if test="name !=null and name !=' '">
name like concat('%', #{name}, '%')
</if>
<if test="description !=null and description !=' '">
description = #{description}
</if>
<if test="companyId !=null">
company_id = #{companyId}
</if>
删除hr:
delete from hr where mobile=#{mobile}
修改hr:
update hr set password=#{password},name = #{name},email=#{email},description=#{description}
WHERE mobile = #{mobile}
5.2 hr权限:
5.2.1 对自己的hr信息:
查询所有信息:
select * from hr where mobile=#{mobile} and password=#{password}
更改所有信息:
update hr set mobile = #{mobile},password=#{password},name = #{name},email=#{email},description=#{description},company_id=#{company_id}
WHERE hr_id=#{hrId}
注销账号:
delete from hr where mobile=#{mobile} and password={password}
注册账号:
insert into hr(hr_id,mobile,password,company_id)
values (#{hrId},#{mobile},#{password},#{company_id})
5.2.2对其他人的hr信息:
查看其他人的所有信息(除去密码):
select hr_id,mobile,name,email,description,company_id
from hr
<where>
<if test="mobile !=null and mobile !=' '">
mobile = #{mobile}
</if>
<if test="name !=null and name !=' '">
name like concat('%', #{name}, '%')
</if>
<if test="description !=null and description !=' '">
description = #{description}
</if>
<if test="company_id !=null">
company_id = #{companyId}
</if>
</where>
5.3 user权限:
可以查询所有的hr信息(除去密码):
select hr_id,mobile,name,email,description,company_id
from hr
<where>
<if test="mobile !=null and mobile !=' '">
mobile = #{mobile}
</if>
<if test="name !=null and name !=' '">
name like concat('%', #{name}, '%')
</if>
<if test="description !=null and description !=' '">
description = #{description}
</if>
<if test="company_id !=null and company_id !=' '">
company_id = #{companyId}
</if>
</where>
6. hr_position表的增删改查及相关功能
6.1 admin下的权限:
可以查询所有的hr_position信息:
select * from hr_position
<where>
<if test="hr_id !=null and hr_id !=' '">
hr_id in (select hr_id from hr where mobile=#{mobile} )
</if>
<if test="category_id !=null">
category_id in (select category_id from industry_category where category_name=#{categoryName} )
</if>
</where>
删除hr_position:
delete from hr_position where position_Id=#{positionId}
delete from hr_position where valid_date=#{valid_date}
修改hr_position:
update hr_position set title = #{title},requirement=#{requirement},
quantity=#{quantity},work_city=#{workCity},salary_up=#{salaryUp},
salary_down=#{salaryDown},release_date=#{releaseDate},
valid_date=#{validDate},category_id=#{categoryId}
WHERE position_id = #{positionId}
6.2 hr权限:
6.2.1对自己的hr_position信息:
可以查询自己所有的hr_position信息:
select * from hr_position
<where>
<if test="hr_id !=null and hr_id !=' '">
hr_id in (select hr_id from hr where mobile=#{mobile} )
</if>
<if test="category_id !=null">
category_id in (select category_id from industry_category where category_name=#{categoryName} )
</if>
</where>
删除hr_position:
delete from hr_position where title=#{title}
delete from hr_position where valid_date=#{valid_date}
修改hr_position:
update hr_position set title = #{title},requirement=#{requirement},
quantity=#{quantity},work_city=#{workCity},salary_up=#{salaryUp},
salary_down=#{salaryDown},release_date=#{releaseDate},
valid_date=#{validDate},category_id=#{categoryId}
WHERE position_id = #{positionId}
新建hr_position:
insert into hr_position(position_id,title,requirement,quantity,work_city,salary_up,salary_down,release_date,valid_date,category_id,hr_id)
values(#{positionId},#{title},#{requirement},#{quantity},#{workCity},#{salaryUp},#{salaryDown},#{releaseDate},#{validDate},#{categoryId},#{hrId})
6.2.2 对其他人的hr_position信息:
查看其他人的所有信息:
select * from hr_position
<where>
<if test="hr_id !=null and hr_id !=' '">
hr_id in (select hr_id from hr where mobile=#{mobile} )
</if>
<if test="category_id !=null and category_id !=' '">
category_id in (select category_id from industry_category where category_name=#{categoryName} )
</if>
</where>
6.3 user权限:
可以查询所有的hr信息(除去密码):
select * from hr_position
<where>
<if test="hr_id !=null and hr_id !=' '">
hr_id in (select hr_id from hr where mobile=#{mobile} )
</if>
<if test="category_id !=null and category_id !=' '">
category_id in (select category_id from industry_category where category_name=#{categoryName} )
</if>
</where>
7. favor表的增删改查及相关功能
查询信息:
select * from favor
<where>
<if test="favorId !=null">
favor_id=#{favorId}
</if>
<if test="user_id !=null and user_id !=' '">
user_id=#{userId}
</if>
<if test="position_id !=null and position_id !=' '">
position_id=#{positionId}
</if>
</where>
增加信息:
insert into favor(user_id,position_id)
values (#{userId},#{positionId})
删除信息:
delete from favor where favor_id = #{favorId}
更新信息:
update favor set user_id=#{userId},position_id=#{positionId}
WHERE favor_id = #{favorId}
8. company表的增删改查及相关功能
查询信息:
select * from company
<where>
<if test="companyId !=null and companyId !=' '">
company_id=#{companyId}
</if>
<if test="companyName !=null and companyName !=' '">
company_name=#{companyName}
</if>
</where>
增加信息:
insert into company(company_id,company_name,legal_person,registered_capital,established_date,type,region,state,company_code)
values(#{companyId},#{companyName},#{legalPerson},#{registeredCapital},#{establishedDate},#{type},#{region},#{state},#{companyCode})
删除信息:
delete from company where company_id=#{companyId}
更新信息:
update company set company_name=#{companyName},company_logo=#{companyLogo},legal_person=#{legalPerson},registered_capital=#{registeredCapital},established_date=#{establishedDate},type=#{type},region=#{region},mobil=#{mobil},description=#{description},website=#{website},state=#{state},company_code=#{companyCode}
where company_id=#{companyId}
9. chat表的增删改查及相关功能
查询信息:
select * from chat
<where>
<if test="userId !=null and userId !=' '">
user_id=#{userId}
</if>
<if test="hrId !=null and hrId !=' '">
hr_id=#{hrId}
</if>
</where>
增加信息:
insert into chat(user_id,hr_id)
values(#{userId},#{hrId})
删除信息:
delete from chat where chat_id=#{chatId}
更新信息:
update chat set user_id=#{userId},hr_id=#{hrId}
where chat_id=#{chatId}
10. chat_details表的增删改查及相关功能
查询信息:
select * from chat_details
<where>
<if test="chatId !=null">
chat_id=#{chatId}
</if>
<if test="user_id !=null and user_id !=' '">
user_id=#{userId}
</if>
<if test="times !=null">
times=#{times}
</if>
</where>
增加信息:
insert into chat_details(chat_id,user_id,content,times,type,is_latest)
values(#{chatId},#{userId},#{content},#{times,jdbcType=TIMESTAMP},#{type},#{isLatest})
删除信息(撤回消息):
delete from chat_details where chat_id=#{chatId}
delete from chat_details where content=#{content}
更新信息(重新编辑消息):
update chat_details set content=#{content},times=#{times,jdbcType=TIMESTAMP},type=#{type},is_latest=#{isLatest}
where chat_details_id=#{chatDetailsId}
11. chat_list表的增删改查及相关功能
查询信息:
select * from chat_list
<where>
<if test="chatId !=null">
chat_id=#{chatId}
</if>
<if test="hr_id !=null and hr_id !=' '">
times=#{times}
</if>
<if test="user_id !=null and user_id !=' '">
user_id=#{userId}
</if>
</where>
增加信息:
insert into chat_list(chat_id,hr_id,user_id,is_online,unread,status)
values(#{chatId},#{hrId},#{userId},#{isOnline},#{unread},#{status})
删除信息(撤回消息):
delete from chat_list where chat_list_id=#{chatListId}
更新信息(重新编辑消息):
update chat_details set is_online=#{isOnline},unread=#{unread},status=#{status},
where chat_list_id=#{chatListId}
12. admin表的增删改查及相关功能
只有admin才能使用:
查询信息:
select * from admin where id_name=#{idName} and pwd =#{pwd}
//增加信息(管理员去数据库增加):
insert into admin(id_name,name,pwd,mobil)
values(#{idName},#{name},#{pwd},#{mobil})
//删除信息(管理员去数据库删除):
delete from admin where admin_id=#{adminId}
更新信息:
update admin set name=#{name},pwd=#{pwd},mobil=#{mobil},
where admin_id=#{adminId}