java的MVC-模式的数据库增删改查(共9页).doc
精选优质文档-倾情为你奉上攀枝花学院实验报告实验课程:JAVA应用开发 实验项目：JSP操作数据库实验日期：2013.5 系：计算机 班级：10计本1班 姓名：蒋志勇 学号：5指导老师：范胜波成绩：一 实验目的：1. 掌握JSP，servlet的基本应用。2. 掌握用JSP对数据库进行增，删，查，改操作。二 实验设备Eclipse，Mysql数据库，Tomact三 实验内容1. 创建数据库javaee和表Users，Users的sql语句为： CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, UserName varchar(30) NOT NULL, Password varchar(30) NOT NULL, PRIMARY KEY (id) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;2. 编写数据库连接、增、删、查、改类UserDao，核心代码如下：public class UserDao final static String url = "jdbc:mysql:/localhost:3306/javaee?useUnicode=true&characterEncoding=UTF-8"final static String user = "root"final static String password = ""static try Class.forName("com.mysql.jdbc.Driver"); catch (ClassNotFoundException e) e.printStackTrace();/数据库连接private Connection getConnection() throws SQLException Connection conn = DriverManager.getConnection(url, user, password);return conn;/根据ID删除用户public boolean delUser(int id)boolean flag=false;String addUserSQL="DELETE FROM Users WHERE ID=?"Connection conn = null;PreparedStatement pst = null;try conn=getConnection();pst=conn.prepareStatement(addUserSQL);pst.setInt(1, id);flag=(pst.executeUpdate()=1); catch (SQLException e) e.printStackTrace();finallytry pst.close();conn.close(); catch (SQLException e) e.printStackTrace();return flag;/增加用户public boolean addUser(User newUser)boolean flag=false;String addUserSQL="INSERT INTO Users(UserName,Password) VALUES(?,?)"Connection conn = null;PreparedStatement pst = null;try conn=getConnection();pst=conn.prepareStatement(addUserSQL);pst.setString(1, newUser.getUserName();pst.setString(2, newUser.getPassword();flag=(pst.executeUpdate()=1); catch (SQLException e) e.printStackTrace();finallytry pst.close();conn.close(); catch (SQLException e) e.printStackTrace();return flag;/分页查询用户public List<User> getUser(int startIndex,int count) List<User> userList = new ArrayList<User>();String getUserSQL = "SELECT ID,UserName,Password FROM Users ORDER BY ID Desc LIMIT ?,?"Connection conn = null;PreparedStatement pst = null;ResultSet rs = null;try conn = getConnection();pst = conn.prepareStatement(getUserSQL);pst.setInt(1, startIndex);pst.setInt(2, count);rs = pst.executeQuery();while (rs.next() User u = new User(rs.getInt("ID"), rs.getString("UserName"),rs.getString("Password");userList.add(u); catch (SQLException e) e.printStackTrace();finallytry rs.close();pst.close();conn.close(); catch (SQLException e) e.printStackTrace();return userList;/更新用户数据public boolean updateUser(String userName,String password,int id)boolean flag=false;String SQL="update Users set UserName=?, Password=? where id=?"Connection conn = null;PreparedStatement pst = 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 (SQLException e) e.printStackTrace();finallytry pst.close();conn.close(); catch (SQLException e) e.printStackTrace();return flag; 3. 编写控制类SaveUserServlet.java和查询雷ListUserServlet.java，代码如下。SaveUserServlet代码：public class SaveUserServlet extends HttpServlet private static final long serialVersionUID = -L;Overrideprotected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException doAction(request, response);Overrideprotected void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException System.out.println("Do Get.");doAction(request, response);private void doAction(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException String action = request.getParameter("action");if (action = null) return;if (action.equals("add") add(request, response); else if (action.equals("update") update(request, response); else if (action.equals("del") del(request, response); else if (action.equals("load") load(request, response);/ 增加用户private void add(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException System.out.println("add");response.setCharacterEncoding("utf-8");String userName =new String(request.getParameter("UserName").getBytes("ISO-8859-1"),"utf-8")；String password = request.getParameter("Password");User u = new User(userName, password);UserDao dao = new UserDao();if (dao.addUser(u) response.sendRedirect("listUser"); else throw new RuntimeException("Add Use ERRO");/ 更新用户private void update(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException response.setCharacterEncoding("utf-8");response.setHeader("content-type","text/html;charset=UTF-8");System.out.println("update.");String idStr = request.getParameter("id");int select = Integer.parseInt(request.getParameter("select");if (select = 1) if (idStr != null) int id = Integer.parseInt(idStr);UserDao dao = new UserDao();User user = dao.getUserById(id);request.setAttribute("idUser", user);RequestDispatcher update = request.getRequestDispatcher("updateUser.jsp");update.forward(request, response);else if(select=2)String userName=new String(request.getParameter("userName").getBytes("ISO-8859-1"),"utf-8")；String password=request.getParameter("password");int id=Integer.parseInt(idStr);UserDao dao=new UserDao();boolean flag=dao.updateUser(userName, password, id);if(flag=true)response.sendRedirect("listUser");elsethrow new RuntimeException("update Use ERRO");/ 删除用户private void del(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException System.out.println("del");String idStr = request.getParameter("id");if (idStr != null) int id = Integer.parseInt(idStr);UserDao dao = new UserDao();if (dao.delUser(id) int curPage = 1;String curPageStr = request.getParameter("curpage");if (curPageStr != null) curPage = Integer.parseInt(curPageStr);int count = 3;String countStr = request.getParameter("count");if (countStr != null) count = Integer.parseInt(countStr);response.sendRedirect("listUser?curpage=" + curPage + "&count="+ count); else throw new RuntimeException("Add Use ERRO");ListUserServlet代码：public class ListUserServlet extends HttpServlet private static final long serialVersionUID = 1L; public ListUserServlet() super(); protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException int curPage=1;String curPageStr=request.getParameter("curpage");if(curPageStr!=null)curPage=Integer.parseInt(curPageStr);int count=3; /每页显示数目String countStr=request.getParameter("count");if(countStr!=null)count=Integer.parseInt(countStr);int startIndex=(curPage-1)*count;UserDao dao=new UserDao();List<User> users=dao.getUser(startIndex, count);request.setAttribute("USERS", users);int userCount=dao.countUser();/数据库总共记录数int pageCount=userCount%count=0?userCount/count:userCount/count+1;request.setAttribute("curpage", curPage);/当前页request.setAttribute("count", count);/每页记录数request.setAttribute("PAGECOUNT", pageCount);/request.getRequestDispatcher("User.jsp").forward(request, response);4. 编写AddUser.jsp添加用户信息，User.jsp查看和删除用户信息，updateUser.jsp修改用户信息。（1） AddUser.jsp核心代码：<h1>Add User</h1><form action="saveUser?action=add" method="post">UserName:<input type="text" name="UserName"><br/>Password:<input type="password" name="Password"><br/><input type="submit" value="Add New User"></form>界面为：（2） User.jsp核心代码<body><h1>用户</h1><table border="1"><tr><td>ID</td><td>UserName</td><td>Password</td><td>Operation</td></tr><c:forEach items="$USERS" var="user"><tr><td>$user.id</td><td>$user.userName</td><td>$user.password</td><td><ahref="saveUser?action=del&curpage=$curpage&count=$count&id=$user.id">Del</a><a href="saveUser?action=update&id=$user.id &select=1">Update</a><a href="AddUser.jsp">Add</a></td></tr></c:forEach></table><c:forEach var="p" begin="1" end="$PAGECOUNT"><a href="listUser?curpage=$p&count=3">$p</a> </c:forEach></body>界面如下所示（3） updateUser.jsp核心代码：<body><h1>更新用户</h1><form action="saveUser?action=update&select=2&id=$idUser.id " method="post">UserName:<input type="text" name="userName" value="$idUser.userName "><br/>Password:<input type="text" name="password" value="$idUser.password "><br/><input type="reset" value="重置"><input type="submit" value="更新"></form></body>四 实验总结这次实验中学会了用MVC设计模式，分别编写JSP，Servlet，Dao类对数据库表的增、删、查、改。学会了怎样配置Servlet的web.xml文件，jstl和el表达式的使用。知道了一种新的数据分页分页方式通过SQL语句的“LIMIT ?,?”方法。在实验中存在的问题是Servlet中sendRedirect和forward的两种用法没弄清楚，因此在编写update（）方法时很久都没把查询出的数据显示出来。专心-专注-专业
