基于web的数据库源码.docx
《基于web的数据库源码.docx》由会员分享,可在线阅读,更多相关《基于web的数据库源码.docx(30页珍藏版)》请在冰豆网上搜索。
基于web的数据库源码
CREATEDATABASEch04;
#-----------------------------------------------------------------------
#user
#-----------------------------------------------------------------------
droptableifexistsuser;
CREATETABLEUSER(
USERNAMEVARCHAR(20)NOTNULL,
PASSWORDVARCHAR(20)NOTNULL,
REALNAMEVARCHAR(20)NOTNULL,
ROLECHAR
(1)NOTNULL,
PRIMARYKEY(USERNAME));
#-----------------------------------------------------------------------
#course
#-----------------------------------------------------------------------
droptableifexistscourse;
CREATETABLECOURSE(
COURSE_IDCHAR(6)NOTNULL,
COURSE_NAMEVARCHAR(40)NOTNULL,
TEACHERVARCHAR(40)NOTNULL,
POINTINTNOTNULL,
TIME_1CHAR
(2)NOTNULL,
TIME_2CHAR
(2)NOTNULL,
LIMITEDINTNOTNULL,
PRIMARYKEY(COURSE_ID));
#-----------------------------------------------------------------------
#elective
#-----------------------------------------------------------------------
droptableifexistselective;
CREATETABLEELECTIVE(
USERNAMEVARCHAR(20)NOTNULL,
COURSE_IDCHAR(6)NOTNULL,
PRIMARYKEY(USERNAME,COURSE_ID));
packagech04;
//针对课程的bean类
publicclassCourse
{
privateStringcourseId="";//课程ID
privateStringcourseName="";//课程名称
privateStringteacher="";//授课老师姓名
privateintpoint=0;//学分
privateStringtime1="";//第一节课时间
privateStringtime2="";//第二节课时间
privateStringtime1Express="";//第一节课时间表达式
privateStringtime2Express="";//第二节课时间表达式
privateintlimited=0;//选课人数
privateintamount=0;//已选课人数
//空的构造方法
publicCourse()
{
}
//get/set方法
publicStringgetCourseId()
{
returncourseId;
}
publicvoidsetCourseId(StringcourseId)
{
this.courseId=courseId;
}
publicStringgetCourseName()
{
returncourseName;
}
publicvoidsetCourseName(StringcourseName)
{
this.courseName=courseName;
}
publicStringgetTeacher()
{
returnteacher;
}
publicvoidsetTeacher(Stringteacher)
{
this.teacher=teacher;
}
publicintgetPoint()
{
returnpoint;
}
publicvoidsetPoint(intpoint)
{
this.point=point;
}
publicStringgetTime1()
{
returntime1;
}
publicvoidsetTime1(Stringtime1)
{
this.time1=time1;
this.time1Express=getExpress(time1);
}
publicStringgetTime1Express()
{
returntime1Express;
}
publicStringgetTime2()
{
returntime2;
}
publicvoidsetTime2(Stringtime2)
{
this.time2=time2;
this.time2Express=getExpress(time2);
}
publicStringgetTime2Express()
{
returntime2Express;
}
publicintgetLimited()
{
returnlimited;
}
publicvoidsetLimited(intlimited)
{
this.limited=limited;
}
publicintgetAmount()
{
returnamount;
}
publicvoidsetAmount(intamount)
{
this.amount=amount;
}
//根据上课时间拼出实际的时间
publicStringgetExpress(StringcourseTime)
{
if(courseTime.length()!
=2)
{
return"未知上课时间";
}
StringweekTime=courseTime.substring(0,1);
StringdayTime=courseTime.substring(1,2);
StringweekString="";
StringdayString="";
if(weekTime.equals("1"))
{
weekString="星期一";
}
elseif(weekTime.equals("2"))
{
weekString="星期二";
}
elseif(weekTime.equals("3"))
{
weekString="星期三";
}
elseif(weekTime.equals("4"))
{
weekString="星期四";
}
elseif(weekTime.equals("5"))
{
weekString="星期五";
}
else
{
weekString="";
}
if(dayTime.equals("1"))
{
dayString="08:
00~10:
00";
}
elseif(dayTime.equals("2"))
{
dayString="10:
00~12:
00";
}
elseif(dayTime.equals("3"))
{
dayString="14:
00~16:
00";
}
elseif(dayTime.equals("4"))
{
dayString="16:
00~18:
00";
}
elseif(dayTime.equals("5"))
{
dayString="19:
00~21:
00";
}
else
{
dayString="";
}
returnweekString+dayString;
}
}
/*
*Thisproductincludessoftwaredevelopedbythe
*ApacheSoftwareFoundation(http:
//www.apache.org/).
*/
packagech04.servlet;
importjava.io.*;
importjava.util.*;
importjava.sql.*;
importjavax.servlet.*;
importjavax.servlet.http.*;
importch04.*;
/**
*针对教师添加课程页面的Servlet
*@authorShenYK
*@version1.0
*/
publicclassAddCourseextendsCommon
{
publicvoiddoGet(HttpServletRequestrequest,
HttpServletResponseresponse)
throwsServletException,IOException
{
//设置提交表单的中文编码
request.setCharacterEncoding("GBK");
HttpSessionmySession=request.getSession(true);
//清空错误消息
mySession.setAttribute("errMsg","");
//是否进入默认页面
if(!
request.getParameterNames().hasMoreElements())
{
//如果是默认进入页面,则单纯显示一个空的页面
Coursecourse=newCourse();
mySession.setAttribute("course",course);
response.sendRedirect("../addCourse.jsp");
return;
}
else
{
//获取表单变量
StringsCourseId=request.getParameter("courseId");
StringsCourseName=request.getParameter("courseName");
StringsTeacher=request.getParameter("teacher");
intiPoint=newInteger(request.getParameter("point")).intValue();
StringsTime1=request.getParameter("time1D")+request.getParameter("time1T");
StringsTime2=request.getParameter("time2D")+request.getParameter("time2T");
intiLimited=newInteger(request.getParameter("limited")).intValue();
CoursenewCourse=newCourse();
newCourse.setCourseId(sCourseId);
newCourse.setCourseName(sCourseName);
newCourse.setTeacher(sTeacher);
newCourse.setPoint(iPoint);
newCourse.setTime1(sTime1);
newCourse.setTime2(sTime2);
newCourse.setLimited(iLimited);
//尝试进行登录
StringsErrMsg=addCourse(newCourse);
//如果登录成功,则跳转到课程一览页面
if(sErrMsg.equals(""))
{
response.sendRedirect("../servlet/CourseList");
return;
}
else
{
mySession.setAttribute("errMsg",sErrMsg);
mySession.setAttribute("course",newCourse);
response.sendRedirect("../addCourse.jsp");
return;
}
}
}
publicvoiddoPost(HttpServletRequestrequest,
HttpServletResponseresponse)
throwsServletException,IOException
{
doGet(request,response);
}
//为用户进行选课
privateStringaddCourse(CoursenewCourse)
{
//获得数据库连接
Connectionconn=this.getDBConnection();
if(conn==null)
{
return"获取数据库连接失败!
";
}
Statementstmt=null;
ResultSetrs=null;
try
{
stmt=conn.createStatement();
//执行SQL语句判断该ID是否已经被使用
StringsQuery="select*fromcoursewherecourse_id='"+newCourse.getCourseId()+"'";
rs=stmt.executeQuery(sQuery);
//检查是否选课人数已满
if(rs.next())
{
return"该课程编号已经存在,请重新输入!
";
}
//尝试进行登录
StringsUpdateQuery="insertintocourseset"
+"course_id='"+newCourse.getCourseId()+"',"
+"course_name='"+newCourse.getCourseName()+"',"
+"teacher='"+newCourse.getTeacher()+"',"
+"point="+newCourse.getPoint()+","
+"time_1='"+newCourse.getTime1()+"',"
+"time_2='"+newCourse.getTime2()+"',"
+"limited="+newCourse.getLimited();
stmt.executeUpdate(sUpdateQuery);
return"";
}
catch(Exceptione)
{
e.printStackTrace();
return"登录失败!
";
}
finally
{
try
{
rs.close();
stmt.close();
conn.close();
}catch(Exceptionex)
{
}
}
}
}
/*
*Thisproductincludessoftwaredevelopedbythe
*ApacheSoftwareFoundation(http:
//www.apache.org/).
*/
packagech04.servlet;
importjava.io.*;
importjava.util.*;
importjava.sql.*;
importjavax.servlet.*;
importjavax.servlet.http.*;
importch04.*;
/**
*针对学生选课登录页面的Servlet
*@authorShenYK
*@version1.0
*/
publicclassChooseCourseextendsCommon
{
publicvoiddoGet(HttpServletRequestrequest,
HttpServletResponseresponse)
throwsServletException,IOException
{
//设置提交表单的中文编码
request.setCharacterEncoding("GBK");
HttpSessionmySession=request.getSession(true);
//清空错误消息
mySession.setAttribute("errMsg","");
//是否进入默认页面
if(!
request.getParameterNames().hasMoreElements())
{
//如果是默认进入页面,则选出所有备选的课程
StringsUsername=(String)mySession.getAttribute("username");
VectorallCourses=getAllCourses(sUsername);
mySession.setAttribute("courses",allCourses);
response.sendRedirect("../chooseCourse.jsp");
return;
}
else
{
//获取表单变量
StringsCourseId=request.getParameter("courseId");
StringsUsername=(String)mySession.getAttribute("username");
//尝试进行选择
booleanbChoose=chooseCourse(sUsername,sCourseId);
//如果选课成功,则跳转到选课结果一览页面
if(bChoose)
{
response.sendRedirect("../servlet/ViewCourse");
return;
}
else
{
mySession.setAttribute("errMsg","你选择的课程"+sCourseId+"已经满员,请选择其他课程。
");
VectorallCourses=getAllCourses(sUsername);
mySession.setAttribute("courses",allCourses);
response.sendRedirect("../chooseCourse.jsp");
return;
}
}
}
publicvoiddoPost(HttpServletRequestrequest,
HttpServletResponseresponse)
throwsServletException,IOException
{
doGet(request,response);
}
//获取用户所有可以使用的课程列表
privateVectorgetAllCourses(StringsUsername)
{
//获得数据库连接
Connectionconn=this.getDBConnection();
if(conn==null)
{
returnnull;
}
Statementstmt=null;
ResultSetrs=null;
try
{
stmt=conn.createStatement();
//执行SQL语句
StringsQuery="selectcourse.*,IF(elective.course_id,count(*),0)asamount"
+"fromcourseleftjoinelective"
+"oncourse.course_id=elective.course_id"
+"wherecourse.course_idnotin"
+"(selectdistinctcourse_idfromelectivewhereusername='"+sUsername+"')"
+"groupbycourse.course_id";
rs=stmt.executeQuery(sQuery);
//留着在页面上显示的课程列表
Vectorcourses=newVector();
while(rs.next())
{
Coursecourse=newCourse();
course.setCourseId(rs.getString("course_id"));
course.setCourseName(rs.getString("course_name"));
course.setTeacher(rs.getString("teacher"));
course.setPoint(