预约系统(该项目并没有使用框架)
下面我将把项目内容结构做一个简单的划分(如有转载或引用请经过本人同意,十分感谢)
1. DAO层
package cn.hospitalRegister.dao;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import com.sun.org.apache.bcel.internal.generic.NEW;
import cn.hospitalRegister.JdbcUtils.JdbcUtils;
import cn.hospitalRegister.date.OrderDate;
import cn.hospitalRegister.domain.Count;
import cn.hospitalRegister.domain.DoctorTimeAndSite;
import cn.hospitalRegister.domain.DoctorTransmit;
import cn.hospitalRegister.domain.PandDUser;
import cn.hospitalRegister.domain.QRcode;
import cn.hospitalRegister.domain.Recode;
import cn.hospitalRegister.domain.violateNumber;
//dao-数据层(与数据库打交道)
public class HospitalRegisterDao {
//获取连接池对象
DataSource dataSource=JdbcUtils.getDataSource();
//创建QueryRunner对象
QueryRunner queryRunner=new QueryRunner(dataSource);
//本地时间
OrderDate orderDate=new OrderDate();
//添加用户
public void addUser(PandDUser pandDUser) throws Exception{
//sql模板
String sql="INSERT INTO hospitaluser(id,username,PASSWORD,email) " +
"VALUES(?,?,?,?);";
//用数组保存需要插入的数据
Object[] params={
pandDUser.getId(),pandDUser.getUsername(),pandDUser.getPassword(),
pandDUser.getEmail()
};
//执行模板
queryRunner.update(sql,params);
}
//用于判断用户名是否重复
public boolean judgeOneUser(String username) throws Exception{
//sql模板
String sql="SELECT username FROM hospitaluser WHERE username=?";
//用数组保存需要插入的数据
Object[] params={
username
};
//执行sql模板
List<Map<String,Object>> list =queryRunner.query(sql, params, new MapListHandler());
//判断
if(list.size()>0){
//存在返回 true
return true;
}else {
//不存在返回false
return false;
}
}
//用于判断用户密码是否正确(使用户能够登录)
public boolean judgeUserPass(String hosUsername,String hosPassword) throws Exception{
//sql模板
String sql="SELECT PASSWORD FROM hospitaluser WHERE username=?";
//用数组保存需要插入的数据
Object[] params={
hosUsername
};
//执行sql模板
PandDUser pandDUser=queryRunner.query(sql, params, new BeanHandler<PandDUser>(PandDUser.class));
//进行判断
if(pandDUser==null){
return false;
}else if(hosPassword.equalsIgnoreCase(pandDUser.getPassword())){
return true;
}else {
return false;
}
}
//查询医生对象
public PandDUser findDoctorBydoctor_user(String doctor_user) throws Exception{
//sql模板
String sql="SELECT id,username,PASSWORD FROM hospitaluser " +
"WHERE (id BETWEEN 1 AND 14) AND username=?";
//用数组保存需要插入的数据
Object[] params={
doctor_user
};
//执行sql模板
PandDUser dUser=queryRunner
.query(sql, params, new BeanHandler<PandDUser>(PandDUser.class));
return dUser;
}
//根据用户名查找邮箱
public String findEmail(String username) throws Exception{
//sql模板
String sql="SELECT email FROM hospitaluser WHERE username=?";
//用数组保存需要插入的数据
Object[] params={
username
};
//执行sql模板
PandDUser user=queryRunner.query(sql, params, new BeanHandler<PandDUser>(PandDUser.class));
return user.getEmail();
}
//根据用户名查找id
public String findId(String username) throws Exception{
//sql模板
String sql="SELECT id FROM hospitaluser WHERE username=?";
//用数组保存需要插入的数据
Object[] params={
username
};
//执行sql模板
PandDUser user=queryRunner
.query(sql, params, new BeanHandler<PandDUser>(PandDUser.class));
return user.getId();
}
//上午-->表timeandsite:获取本地时间后,将7天内可以预约的时间存入 表timeandsite
public void addOrderTime() throws Exception{
//sql模板
String sql="UPDATE timeandsite SET TIME =? WHERE id=?";
//用params数组保存需要插入的数据
//得到date数据(将list列表中的数据插入)
List<String> list=orderDate.TheOrderDay();
for (String listS : list) {
System.out.println(listS);
}
System.out.println("++++++++");
int count=list.size();// 插入记录的数目
Object[][] params = new Object[count][];
for (int i = 0; i < count; i++){
// 将每条记录的数据插入数组
params[i] = new Object[] { list.get(i),(i+1)};
}
for (int i = 0; i < params.length; i++) {
System.out.println(params[i]);
}
//执行sql模板
queryRunner.batch(sql,params);
}
//下午-->表timeandsite:获取本地时间后,将7天内可以预约的时间存入 表timeandsite
public void addOrderTimeAfter() throws Exception{
//sql模板
String sql="UPDATE timeandsite SET TIME =? WHERE id=?";
//用params数组保存需要插入的数据
//得到date数据(将list列表中的数据插入)
List<String> list=orderDate.TheOrderDay();
for (String listS : list) {
System.out.println(listS);
}
System.out.println("++++++++");
int count=list.size();// 插入记录的数目
Object[][] params = new Object[count][];
for (int i = 0; i < count; i++){
// 将每条记录的数据插入数组
params[i] = new Object[] { list.get(i),(i+8)};
}
for (int i = 0; i < params.length; i++) {
System.out.println(params[i]);
}
//执行sql模板
queryRunner.batch(sql,params);
}
//从表timeandsite 查找数据
public List<DoctorTimeAndSite> findAllTimeAndSite(String period) throws Exception{
//sql模板
String sql="SELECT * FROM timeandsite WHERE tperiod=?";
//用数组保存需要插入的数据
Object[] params={
period
};
//执行sql模板
List<DoctorTimeAndSite> list=queryRunner
.query(sql, params,new BeanListHandler<DoctorTimeAndSite>(DoctorTimeAndSite.class));
return list;
}
//根据提交的预约请求-查找预约时间与地址
//时间
public String findTime(String doctor) throws Exception{
//sql模板
String sql="SELECT * FROM timeAndSite WHERE seqid=?";
//用数组保存需要插入的数据
Object[] params={
doctor
};
//执行sql模板
DoctorTimeAndSite doctorTimeAndSite=queryRunner
.query(sql, params, new BeanHandler<DoctorTimeAndSite>(DoctorTimeAndSite.class));
return doctorTimeAndSite.getTime();
}
//地址
public String findSite(String doctor) throws Exception{
//sql模板
String sql="SELECT * FROM timeAndSite WHERE seqid=?";
//用数组保存需要插入的数据
Object[] params={
doctor
};
//执行sql模板
DoctorTimeAndSite doctorTimeAndSite=queryRunner
.query(sql, params, new BeanHandler<DoctorTimeAndSite>(DoctorTimeAndSite.class));
return doctorTimeAndSite.getSite();
}
//医生名称
public String findDoctor(String doctor) throws Exception{
//sql模板
String sql="SELECT * FROM timeAndSite WHERE seqid=?";
//用数组保存需要插入的数据
Object[] params={
doctor
};
//执行sql模板
DoctorTimeAndSite doctorTimeAndSite=queryRunner
.query(sql, params, new BeanHandler<DoctorTimeAndSite>(DoctorTimeAndSite.class));
return doctorTimeAndSite.getDoctor();
}
//预约具体时间(上午/下午)
public String findTperiod(String doctor) throws Exception{
//sql模板
String sql="SELECT * FROM timeAndSite WHERE seqid=?";
//用数组保存需要插入的数据
Object[] params={
doctor
};
//执行sql模板
DoctorTimeAndSite doctorTimeAndSite=queryRunner
.query(sql, params, new BeanHandler<DoctorTimeAndSite>(DoctorTimeAndSite.class));
return doctorTimeAndSite.getTperiod();
}
//将预约成功的信息从 hospitaluser表与timeAndSite表 中传递至doctormessage表
public void transmitToDoctor(String doctor,String username) throws Exception{
//sql模板
String sql="INSERT INTO doctorMessage (PatientUsername,TIME,site,doctor,tperiod,email) " +
"SELECT username,TIME,site,doctor,tperiod,email " +
"FROM hospitaluser,timeandsite doctormessage " +
"WHERE seqid=? AND username=?";
//将需要插入的数据用数组保存
Object[] params={
doctor,username
};
//执行语句
queryRunner.update(sql, params);
}
//判断doctormessage表中有无某条预约信息的存在
public boolean judgeSubmitExist(String PatientUsername) throws Exception{
//sql模板
String sql="SELECT * FROM doctormessage WHERE PatientUsername=?";
//将需要插入的数据用数组保存
Object[] params={
PatientUsername
};
//执行语句
List<Map<String,Object>> list =queryRunner.query(sql, params, new MapListHandler());
//进行判断
//判断
if(list.size()>0){
//存在返回 true
return true;
}else {
//不存在返回false
return false;
}
}
//对doctormessage表中 预约请求的更改
public void submitTransChange(String time,String site,String doctor,String tperiod,String email,String username) throws Exception{
//sql模板
String sql="UPDATE doctorMessage SET TIME=?,site=?," +
"doctor=?,tperiod=?,email=? " +
"WHERE PatientUsername=?";
//将需要插入的数据用数组保存
Object[] params={
time,site,doctor,tperiod,email,username
};
//执行语句
queryRunner.update(sql, params);
}
//从doctormessage表中根据用户名 查找预约信息
public DoctorTransmit findSubmit(String PatientUsername) throws Exception{
//sql模板
String sql="SELECT * FROM doctormessage WHERE PatientUsername=?";
//将需要插入的数据用数组保存
Object[] params={
PatientUsername
};
//执行语句
DoctorTransmit doctorTransmit=queryRunner
.query(sql, params, new BeanHandler<DoctorTransmit>(DoctorTransmit.class));
return doctorTransmit;
}
//从doctormessage表中查找所有信息
public List<DoctorTransmit> findAllFromDM() throws Exception{
//sql模板
String sql="SELECT * FROM doctormessage";
//执行语句
List<DoctorTransmit> list=queryRunner
.query(sql,new BeanListHandler<DoctorTransmit>(DoctorTransmit.class));
return list;
}
//根据doctor名称,从doctormassage表中查找数据
public List<DoctorTransmit> findMassageByDoctor(String doctor) throws Exception{
//sql模板
String sql="SELECT * FROM doctormessage WHERE doctor=?";
//将需要输入的数据用数组存储
Object[] params={
doctor
};
//执行
List<DoctorTransmit> list=queryRunner
.query(sql,params,new BeanListHandler<DoctorTransmit>(DoctorTransmit.class));
return list;
}
//从doctormassage表中查找所有email数据
public List<DoctorTransmit> findAllEmaillByDoc() throws Exception{
//sql模板
String sql="SELECT email FROM doctormessage";
//将需要输入的数据用数组存储
//执行
List<DoctorTransmit> list=queryRunner
.query(sql,new BeanListHandler<DoctorTransmit>(DoctorTransmit.class));
return list;
}
//取消预约,从doctormessage表中删除信息
public void CancelSubmit(String PatientUsername) throws Exception{
//sql模板
String sql="DELETE FROM doctormessage WHERE PatientUsername=?";
//将需要插入的数据保存在数组中
Object[] params={
PatientUsername
};
//执行语句
queryRunner.update(sql, params);
}
//更新doctormessage表中的recodeTime
public void updateDocRecode(String time,String PaUsername) throws Exception{
//sql模板
String sql="UPDATE doctormessage SET recodeTime=? " +
"WHERE PatientUsername=?";
//将需要插入的数据保存在数组中
Object []params={
time,PaUsername
};
//执行语句
queryRunner.update(sql, params);
}
//查询doctormessage表中的具体某预约日期的预约人数
public Count countTime(String time,String tperiod) throws Exception{
//sql模板
String sql="SELECT TIME,tperiod,COUNT(TIME) AS countTime " +
"FROM doctormessage " +
"WHERE TIME=? AND tperiod=?";
//将需要插入的数据保存在数组中
Object[] params={
time,tperiod
};
//执行sql语句
Count count=queryRunner
.query(sql, params, new BeanHandler<Count>(Count.class));
return count;
}
//将数据插入violateNumber表中
public void ViolateAdd(String violatePatientName) throws Exception{
//sql模板
String sql="INSERT INTO violateNumber VALUES (?,0)";
//将需要插入的数据保存在数组中
Object[] params={
violatePatientName
};
//执行sql语句
queryRunner.update(sql, params);
}
//根据用户名查询Violate表中是否存在这条信息
public boolean ViolateFind(String violatePatientName) throws Exception{
//sql模板
String sql="SELECT * FROM violateNumber WHERE NumPatientName=?";
//将需要插入的数据保存在数组中
Object[] params={
violatePatientName
};
//执行sql语句
Map<String,Object> map=queryRunner.query(sql, params, new MapHandler());
//判断
if(map==null){
//不存在此条信息返回false
return false;
}else {
return true;
}
}
//根据用户名 update更新 Violate表中的次数number
public void ViolateUpdateNum(String violatePatientName) throws Exception{
//sql模板
String sql="UPDATE violateNumber SET number=number-1 WHERE NumPatientName=?;";
//将需要插入的数据保存在数组中
Object[] params={
violatePatientName
};
//执行sql语句
queryRunner.update(sql, params);
}
//根据用户名 find查询 Violate表中的次数number
public Integer ViolateFindNum(String violatePatientName) throws Exception{
//sql模板
String sql="SELECT number FROM violatenumber WHERE NumPatientName=?";
//将需要插入的数据保存在数组中
Object[] params={
violatePatientName
};
//执行sql语句
List<Object> listNum=queryRunner.query(sql, params, new ColumnListHandler());
//获取执行后list中的number数据
if (listNum.size()==0){
//如果没有返回null
return null;
}else {
//否则返回具体的值
return (Integer) listNum.get(0);
}
}
//根据QRid查找QRchar
public String findQRchar(String QRchar) throws Exception{
//sql模板
String sql="SELECT QRchar FROM qrcode WHERE QRchar=?";
//用数组保存需要插入的数据
Object[] params={
QRchar
};
//执行sql模板
QRcode qRcode=queryRunner.query(sql, params, new BeanHandler<QRcode>(QRcode.class));
return qRcode.getQRchar();
}
//将预约记录插入recode表中
public void insertRecode(String username,String doctor) throws Exception{
//sql模板
String sql="INSERT INTO recode (recodePaName,TIME,site,doctor,tperiod,email)" +
" SELECT PatientUsername,TIME,site,doctor,tperiod,email " +
"FROM doctormessage " +
"WHERE PatientUsername=? AND doctor=?";
//插入数据
Object []params={
username,doctor
};
//执行
queryRunner.update(sql, params);
}
//recode表中预约记录的状态设置
public void recodeStatus(String status,String username) throws Exception{
//sql模板
String sql="UPDATE recode SET STATUS=? WHERE id IN " +
"(SELECT a.* FROM(SELECT MAX(id) AS id FROM recode r) a)" +
" AND recodePaName=?";
//插入数据
Object []params={
status,username
};
//执行
queryRunner.update(sql, params);
}
//recode表中预约的时间记录
public void recodeTime(String recodeTime,String username) throws Exception{
//sql模板
String sql="UPDATE recode SET recodeTime=? WHERE id IN " +
"(SELECT a.* FROM(SELECT MAX(id) AS id FROM recode r) a)" +
" AND recodePaName=?";
//插入数据
Object []params={
recodeTime,username
};
//执行
queryRunner.update(sql, params);
}
//recode表中id记录
public void recodeId(String recodeCoding,String username) throws Exception{
//sql模板
String sql="UPDATE recode SET " +
"recodeCoding=? WHERE id IN " +
"(SELECT a.* FROM(SELECT MAX(id) AS id FROM recode r) a) " +
"AND recodePaName=?";
//插入数据
Object[] params={
recodeCoding,username
};
//执行
queryRunner.update(sql, params);
}
//管理员根据用户名从recode表中查询数据
public List<Recode> findRecodeByName(String recodePaName) throws Exception{
//sql模板
String sql="SELECT * FROM recode WHERE recodePaName=?";
//插入数据
Object []params={
recodePaName
};
//执行
List<Recode> list=queryRunner
.query(sql, params, new BeanListHandler<Recode>(Recode.class));
return list;
}
//医生根据用户名从recode表中查询数据
public List<Recode> findRecodeWithDoctor(String recodePaName,String doctor) throws Exception{
//sql模板
String sql="SELECT * FROM recode WHERE recodePaName=? AND doctor=?";
//插入数据
Object []params={
recodePaName,doctor
};
//执行
List<Recode> list=queryRunner
.query(sql, params, new BeanListHandler<Recode>(Recode.class));
return list;
}
}