JAVA的MVC-模式的数据库增删改查

攀枝花学院实验报告实验课程:JAVA应用开发实验项目：JSP操作数据库实验日期：2013.5系：计算机班级：10计本1班姓名：蒋志勇学号：201010801035指导老师：范胜波成绩：一．实验目的：1.掌握JSP，servlet的基本应用。2.掌握用JSP对数据库进行增，删，查，改操作。二．实验设备Eclipse，Mysql数据库，Tomact三．实验内容1.创建数据库javaee和表Users，Users的sql语句为：CREATETABLE`users`(`id`int(11)NOTNULLAUTO_INCREMENT,`UserName`varchar(30)NOTNULL,`Password`varchar(30)NOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=6DEFAULTCHARSET=utf8;2.编写数据库连接、增、删、查、改类UserDao，核心代码如下：publicclassUserDao{finalstaticStringurl=jdbc:mysql://localhost:3306/javaee?useUnicode=true&characterEncoding=UTF-8;finalstaticStringuser=root;finalstaticStringpassword=123456;static{try{Class.forName(com.mysql.jdbc.Driver);}catch(ClassNotFoundExceptione){e.printStackTrace();}}//数据库连接privateConnectiongetConnection()throwsSQLException{Connectionconn=DriverManager.getConnection(url,user,password);returnconn;}//根据ID删除用户publicbooleandelUser(intid){booleanflag=false;StringaddUserSQL=DELETEFROMUsersWHEREID=?;Connectionconn=null;PreparedStatementpst=null;try{conn=getConnection();pst=conn.prepareStatement(addUserSQL);pst.setInt(1,id);flag=(pst.executeUpdate()==1);}catch(SQLExceptione){e.printStackTrace();}finally{try{pst.close();conn.close();}catch(SQLExceptione){e.printStackTrace();}}returnflag;}//增加用户publicbooleanaddUser(UsernewUser){booleanflag=false;StringaddUserSQL=INSERTINTOUsers(UserName,Password)VALUES(?,?);Connectionconn=null;PreparedStatementpst=null;try{conn=getConnection();pst=conn.prepareStatement(addUserSQL);pst.setString(1,newUser.getUserName());pst.setString(2,newUser.getPassword());flag=(pst.executeUpdate()==1);}catch(SQLExceptione){e.printStackTrace();}finally{try{pst.close();conn.close();}catch(SQLExceptione){e.printStackTrace();}}returnflag;}//分页查询用户publicListUsergetUser(intstartIndex,intcount){ListUseruserList=newArrayListUser();StringgetUserSQL=SELECTID,UserName,PasswordFROMUsersORDERBYIDDescLIMIT?,?;Connectionconn=null;PreparedStatementpst=null;ResultSetrs=null;try{conn=getConnection();pst=conn.prepareStatement(getUserSQL);pst.setInt(1,startIndex);pst.setInt(2,count);rs=pst.executeQuery();while(rs.next()){Useru=newUser(rs.getInt(ID),rs.getString(UserName),rs.getString(Password));userList.add(u);}}catch(SQLExceptione){e.printStackTrace();}finally{try{rs.close();pst.close();conn.close();}catch(SQLExceptione){e.printStackTrace();}}returnuserList;}//更新用户数据publicbooleanupdateUser(StringuserName,Stringpassword,intid){booleanflag=false;StringSQL=updateUserssetUserName=?,Password=?whereid=?;Connectionconn=null;PreparedStatementpst=null;try{conn=getConnection();pst=conn.prepareStatement(SQL);System.out.println(userName);System.out.println(password);pst.setString(1,userName);pst.setString(2,password);pst.setInt(3,id);flag=(pst.executeUpdate()==1);System.out.println(flag);}catch(SQLExceptione){e.printStackTrace();}finally{try{pst.close();conn.close();}catch(SQLExceptione){e.printStackTrace();}}returnflag;}}3.编写控制类SaveUserServlet.java和查询雷ListUserServlet.java，代码如下。SaveUserServlet代码：publicclassSaveUserServletextendsHttpServlet{privatestaticfinallongserialVersionUID=-5350720307591869348L;@OverrideprotectedvoiddoPost(HttpServletRequestrequest,HttpServletResponseresponse)throwsServletException,IOException{doAction(request,response);}@OverrideprotectedvoiddoGet(HttpServletRequestrequest,HttpServletResponseresponse)throwsServletException,IOException{System.out.println(DoGet.....);doAction(request,response);}privatevoiddoAction(HttpServletRequestrequest,HttpServletResponseresponse)throwsServletException,IOException{Stringaction=request.getParameter(action);if(action==null){return;}if(action.equals(add)){add(request,response);}elseif(action.equals(update)){update(request,response);}elseif(action.equals(del)){del(request,response);}elseif(action.equals(load)){load(request,response);}}//增加用户privatevoidadd(HttpServletRequestrequest,HttpServletResponseresponse)throwsServletException,IOException{System.out.println(add);response.setCharacterEncoding(utf-8);StringuserName=newString(request.getParameter(UserName).getBytes(ISO-8859-1),utf-8)；Stringpassword=request.getParameter(Password);Useru=newUser(userName,password);UserDaodao=newUserDao();if(dao.addUser(u)){response.sendRedirect(listUser);}else{thrownewRuntimeException(AddUseERRO);}}//更新用户privatevoidupdate(HttpServletRequestrequest,HttpServletResponseresponse)throwsServletException,IOException{response.setCharacterEncoding(utf-8);response.setHeader(content-type,text/html;charset=UTF-8);System.out.println(update.....);StringidStr=request.getParameter(id);intselect=Integer.parseInt(request.getParameter(select));if(select==1){if(idStr!=null){intid=Integer.parseInt(idStr);UserDaodao=newUserDao();Useruser=dao.getUserById(id);request.setAttribute(idUser,user);RequestDispatcherupdate=request.getRequestDispatcher(updateUser.jsp);update.forward(request,response);}}elseif(select==2){StringuserName=newString(request.getParameter(userName).getBytes(ISO-8859-1),utf-8)；Stringpassword=request.getParameter(password);intid=Integer.parseInt(idStr);UserDaodao=newUserD