南昌航空大学实验报告二二0一四年三月十九日课程名称：JA V A WEB 程序设计实验名称：JavaBean和Servlet完成增删改查班级：11微软班姓名：林平君同组人：指导教师评定：林振荣签名：一、实验目的完成一个数据列表的增删改查功能的实现，以自己创建的表格为例，也可以自己设计数据库的表。

通过本实验能够熟悉和掌握jsp中的数据库CRUD操作。

二、实验要求本实验要求通过javabean来实现数据的存取和表示，通过servlet来进行访问的控制。

给出主要运行的界面和核心代码，并描述系统实现的核心思想。

三、实验步骤1．实现javaBean对数据库的操作。

2．创建jsp页面3．利用servlet对页面对业务流程就行控制。

四、参考源代码1.连接数据库public Connection getConnection() throwsSQLException,InstantiationException,IllegalAccessException,ClassNotFoundException{Connection connection=null;Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInst ance();StringurlString="jdbc:sqlserver://localhost:1433;DatabaseName=StudentManageSys" ;String passwordString="sa";String userString="sa";connection=DriverManager.getConnection(urlString,userString,passwordS tring);return connection;}解释：获取一个数据源，用于连接数据库。

2.利用传入的sql语句查找public ArrayList<student> objectSelect(String sql) throws Exception{ ArrayList<student> result=new ArrayList<student>();Connection connection=null;Statement statement=null;ResultSet resultSet=null;try{connection=getConnection();statement=connection.createStatement();resultSet=statement.executeQuery(sql);while(resultSet.next()){student stu=new student();stu.setSid(resultSet.getInt("sid"));stu.setSsno(resultSet.getString("ssno"));stu.setSname(resultSet.getString("sname"));stu.setSsex(resultSet.getString("ssex"));stu.setIsManage(resultSet.getInt("isManage"));stu.setLoginTimes(resultSet.getInt("loginTimes"));stu.setLoginLastTime(resultSet.getString("loginLastTime"));stu.setPassword(resultSet.getInt("password"));stu.setSage(resultSet.getInt("sage"));result.add(stu);}}catch (SQLException e) {throw new SQLException("select data exception:"+e.getMessage());}catch(Exception exception){throw new Exception("system eexception:"+exception.getMessage());}finally{try{if(resultSet!=null){resultSet.close();}}catch(Exception e){throw new Exception("statement closeexception:"+e.getMessage());}try{if(connection!=null){connection.close();}}catch(Exception e){throw new Exception("Connection closeexception:"+e.getMessage());}}return result;}3.向数据库中插入数据public void insert(student student)throws Exception{Connection connection=null;PreparedStatement pStatement=null;String sql="insert intostudent(ssno,sname,ssex,isManage,password,sage)values('"+student.getSsno()+"','"+student.getSname()+"','"+student.getSse x()+"',"+student.getIsManage()+","+student.getPassword()+","+student.getS age()+")";try{connection=getConnection();pStatement=connection.prepareStatement(sql);pStatement.execute();}catch(SQLException e){throw new Exception("sql a exception:"+e.getMessage());}catch(Exception e){throw new Exception("system a exception:"+e.getMessage());}finally{try{if(pStatement!=null){ pStatement.close(); }}catch(Exception exception){throw new Exception("PrepareStatement close exception："+exception.getMessage());}}try {if(connection!=null)connection.close();} catch ( Exception e) {// TODO: handle exceptionthrow new Exception("connection closeexception:"+e.getMessage());}}//通过传入的student对象删除表中的数据public void delete(student student) throws Exception{Connection connection=null;PreparedStatement pStatement=null;String sql="delete from student where sid="+student.getSid()+"";try{connection=getConnection();pStatement=connection.prepareStatement(sql);pStatement.execute();}catch (SQLException e) {// TODO: handle exceptionthrow new Exception("SQL a exception:"+e.getMessage());}catch(Exception e){throw new Exception("System a exception:"+e.getMessage());}finally{try{if(pStatement!=null){ pStatement.close(); }}catch(Exception exception){throw new Exception("PrepareStatement close exception："+exception.getMessage());}}try {if(connection!=null)connection.close();} catch ( Exception e) {// TODO: handle exceptionthrow new Exception("connection closeexception:"+e.getMessage());}}4.修改数据库中的数据public void update(student student)throws Exception{Connection connection=null;PreparedStatement pStatement=null;String sql="update student setssno="+student.getSsno()+",sname='"+student.getSname()+"',ssex='"+student .getSsex()+"'," +"isManage="+student.getIsManage()+",password="+student.getPassword()+ "," +"sage="+student.getSage()+",loginTimes='"+student.getLoginTimes()+"', " +"loginLastTime='"+student.getLoginLastTime()+"'wheresid="+student.getSid()+"";try{connection=getConnection();pStatement=connection.prepareStatement(sql);pStatement.execute();}catch (SQLException e) {// TODO: handle exceptionthrow new Exception("SQL a exception:"+e.getMessage());5.servlet对业务逻辑的控制public void doGet(HttpServletRequest request,HttpServletResponse response)throws IOException,ServletException{response.setContentType("text/html");request.setCharacterEncoding("gb2312");response.setCharacterEncoding("gb2312");PrintWriter out=response.getWriter();String name=request.getParameter("username");int password=Integer.parseInt(request.getParameter("password"));int loginTimes;String loginLastTime;HttpSession session = request.getSession();session.setAttribute("username", name);String sql = "select * from student where sname='"+name+"'";ORMDBUtil ormdbUtil=new ORMDBUtil();Date data=new Date();Format fm=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");try {student stu =new student();student student=new student();ArrayList<student> result=ormdbUtil.objectSelect(sql);if(result.isEmpty()==false){for(int i=0;i<result.size();i++){stu=result.get(i);}loginTimes=stu.getLoginTimes()+1;loginLastTime=fm.format(data);student.setLoginTimes(loginTimes);student.setLoginLastTime(loginLastTime);student.setIsManage(stu.getIsManage());student.setPassword(stu.getPassword());student.setSage(stu.getSage());student.setSname(stu.getSname());student.setSsex(stu.getSsex());student.setSsno(stu.getSsno());student.setSid(stu.getSid());ormdbUtil.update(student);if(password!=stu.getPassword()){out.println("<html>");out.println("<head>");out.println("<title>密码错误</title>");out.println("</head>");out.println("<body>");out.println("<h1>密码错误！</h1>");out.println("</body>");out.println("</html>");}else if(stu.getIsManage()==0){//跳转到普通用户界面//getServletConfig().getServletContext().getRequestDispatcher("/Ordin ary.jsp").forward(request, response);int userId=stu.getSid();request.setAttribute("id", userId);//为id赋值//session.setAttribute("loginTimes", loginTimes);//session.setAttribute("loginLastTime", loginLastTime);//RequestDispatcherde=request.getRequestDispatcher("/Ordinary.jsp");getServletContext().getRequestDispatcher("/Ordinary.jsp").forward(req uest,response);}else if(stu.getIsManage()==1){//重定向跳到管理员界面response.sendRedirect("Manager.jsp");//getServletContext().getRequestDispatcher("/Manager.jsp").forward(re quest,response);}}else//用户不存在{out.println("<html>");out.println("<head>");out.println("<title>用户不存在</title>");out.println("</head>");out.println("<body>");out.println("<h1>用户不存在！</h1>");out.println("</body>");out.println("</html>");}} catch (Exception e) {// TODO Auto-generated catch blocke.getStackTrace();}}五、实验结果1.以管理员身份登陆将跳转到：2.以普遍身份登陆：将跳转到：注册页面：在管理员界面可以看到新注册的：点击修改：六、实验体会本次实验对我的帮助很大，让我更加熟悉了jsp中的逻辑操作，对数据库以及逻辑方面的操作更加的熟练，熟悉了javaBean以及servlet的操作，初步的了解了MVC的框架。