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

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 var

2、char(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=truefinal static String user = “root“;final static Strin

3、g 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, user, password);return conn;/根据ID删除用户public boolean delUser(i

4、nt 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

5、();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.prepareState

6、ment(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 getUser(int startInd

7、ex,int count) List userList = new ArrayList();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);p

8、st.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();ret

9、urn 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(user

10、Name);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.编

11、写控制类 SaveUserServlet.java 和查询雷 ListUserServlet.java，代码如下。SaveUserServlet 代码：public class SaveUserServlet extends HttpServlet private static final long serialVersionUID = -5350720307591869348L;Overrideprotected void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletExcepti

12、on, 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 respon

13、se) 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(

14、“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“),“

15、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, HttpServletRespons

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

17、(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(re

18、quest.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(“

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

20、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=“ + cu

21、rPage + “ 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 Servlet

22、Exception, 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;UserDa

23、o dao=new UserDao();List 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); /每页记录数requ

24、est.setAttribute(“PAGECOUNT“, pageCount); /request.getRequestDispatcher(“User.jsp“).forward(request, response);4.编写 AddUser.jsp 添加用户信息，User.jsp 查看和删除用户信息，updateUser.jsp 修改用户信息。（1）AddUser.jsp 核心代码：Add UserUserName:Password:界面为：（2）User.jsp 核心代码用户IDUserNamePasswordOperation$user.id$user.userName$user.p

25、asswordDelUpdateAdd$p界面如下所示（3）updateUser.jsp 核心代码：更新用户 UserName:Password:4实验总结这次实验中学会了用 MVC 设计模式，分别编写 JSP，Servlet，Dao 类对数据库表的增、删、查、改。学会了怎样配置 Servlet 的 web.xml 文件，jstl 和 el 表达式的使用。知道了一种新的数据分页分页方式通过 SQL 语句的“LIMIT ?,?”方法。在实验中存在的问题是Servlet 中 sendRedirect 和 forward 的两种用法没弄清楚，因此在编写 update（）方法时很久都没把查询出的数据显示出来。