java的MVC模式的数据库增删改查

1、攀枝花学院实验报告实验课程:JAVA应用开发 实验项目：JSP操作数据库实验日期：2013.5 系：计算机 班级：10计本1班 姓名：蒋志勇 学号：201010801035指导老师：范胜波成绩：1 实验目的：1. 掌握JSP，servlet的基本应用。2. 掌握用JSP对数据库进行增，删，查，改操作。2 实验设备Eclipse，Mysql数据库，Tomact3 实验内容1. 创建数据库javaee和表Users，Users的sql语句为： CREATE TABLE users ( id int(11) NOT NULL AUTO_INCREMENT, UserName varchar(30)

2、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

3、user = "root"final static String password = "123456"static try Class.forName("com.mysql.jdbc.Driver"); catch (ClassNotFoundException e) e.printStackTrace();/数据库连接private Connection getConnection() throws SQLException Connection conn = DriverManager.getConnection(url, us

4、er, 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()

5、=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

6、;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

7、 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 =

8、 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");user

9、List.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=?,

10、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

11、.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 fina

12、l long serialVersionUID = -5350720307591869348L;Overrideprotected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException doAction(request, response);Overrideprotected void doGet(HttpServletRequest request,HttpServletResponse response) throws Servle

13、tException, 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 (actio

14、n.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,

15、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.

16、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 res

17、ponse)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(reque

18、st.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");updat

19、e.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

20、.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&q

21、uot;);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);i

22、nt 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代码

23、：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.getParamete

24、r("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

25、(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

26、("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">

27、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&g

28、t;<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</t

29、d><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>