你的位置:首页 > Java教程

[Java教程]使用jdbc实现简单的mvc模式的增删改查


 

Mvc模式设计:

视图:添加界面(addUser.jsp),修改界面(updateUser.jsp),显示页面(allUser.jsp)

控制器:添加信息控制器(AddUserServlet),修改信息控制器(UpdateUserServlet),删除信息控制器(DeleteUserServlet),显示信息控制器(FindAllUserServlet)

模型:userbean

 

数据库层:DBBean

 

总体设计:

  添加信息模块:用户通过添加信息界面(addUser.jsp)提交表单,提交的信息有添加信息控制器(AddUserServlet)控制,控制器通过调用userBean的add方法添加信息,在request对象中添加成功与否的消息,成功则返回成功,跳转到显示界面,失败则返回失败消息,跳转到添加信息页面。

  修改信息模块:用户是通过点击显示页面相应项的修改按钮进入到修改页面中,从显示页面跳转过来时,request对象中封装的信息会显示在当前页面中的特定位置(通过表达式语言),提交表单之后交给修改信息控制器,通过调用更新方法更新,在request对象中封装成功与否消息,成功则返回成功,跳转到显示界面,失败则返回失败消息,跳转到添加信息页面。

  显示信息模块:显示当前页的所有用户信息,每一条信息都有修改和删除选项,修改则进入修改页面,删除则交给删除控制器,控制器通过调用删除方法。

 

详细设计:

数据库层:DBBean

实现功能:获得与数据库的连接,执行查询操作返回结果集,执行更新操作,关闭连接。

 1 public class DBBean { 2   private Connection con; 3   private Statement stmt; 4   private ResultSet rs; 5   public DBBean() { 6      7   } 8   //获取数据库的连接 9   public Connection getConnection() throws Exception10   {11     String url="jdbc:mysql://localhost:3306/bookstore";12     String dbuser="root";13     String dbpass="";14     if(con==null)15     {16       Class.forName("com.mysql.jdbc.Driver");17       con=DriverManager.getConnection(url, dbuser, dbpass);18     }19     return con;20   }21   22   //执行查询语句,返回结果集23   public ResultSet executeQuery(String sql) throws Exception24   {25     if(con==null)26     {27       throw new Exception("没有连接对象可用");28     }29     stmt=con.createStatement();30     rs=stmt.executeQuery(sql);31     return rs;32   }33   34   public int executeUpdate(String sql)throws Exception35   {36     if(con==null)37     {38       throw new Exception("没有连接对象可用");39     }40     stmt=con.createStatement();41     42     return stmt.executeUpdate(sql);43   }44   45   public void close()46   {47     if(rs!=null)48       try {49         rs.close();50       } catch (SQLException e) {51         e.printStackTrace();52       }53     try {54       stmt.close();55     } catch (SQLException e) {56       // TODO Auto-generated catch block57       e.printStackTrace();58     }59     try {60       con.close();61     } catch (SQLException e) {62       // TODO Auto-generated catch block63       e.printStackTrace();64     }65   }66 }

View Code

模型层设计:UserBean

1.add();添加用户信息

 1 public void add() throws Exception { 2     Connection con = null; 3     DBBean db = new DBBean(); 4     String sql = "insert into usertable values('"+userid+"','"+username+"','"+userpass+"','"+type+"','"+new java.sql.Date(birthday.getTime())+"','"+degree+"','"+local+"','"+email+"','"+address+"','"+comment+"')"; 5  6     try { 7       con = db.getConnection(); 8       db.executeUpdate(sql); 9     } catch (Exception e) {10       System.out.println(e.toString());11 12     } finally {13       db.close();14     }15   }

View Code

2.UserBean findUserById(String userid);根据主键查询用户

 1 public UserBean findUserById(String userid) throws Exception { 2     Connection con = null; 3     ResultSet rs=null; 4     DBBean db = new DBBean(); 5     String sql = "select * from usertable where userid='"+userid+"'"; 6  7     try { 8       con = db.getConnection(); 9       rs=db.executeQuery(sql);10       11       if(rs.next())12       {13         String tmpUserid=rs.getString(1);14         String tmpUsername=rs.getString(2);15         String tmpUserpass=rs.getString(3);16         String tmpType=rs.getString(4);17         java.util.Date tmpBirthday=rs.getDate(5);18         String tmpDegree=rs.getString(6);19         String tmpLocal =rs.getString(7);20         String tmpEmail=rs.getString(8);21         String tmpAddress=rs.getString(9);22         String tmpComment=rs.getString(10);23         UserBean user=new UserBean();24         user.setAddress(tmpAddress);25         user.setBirthday(tmpBirthday);26         user.setComment(tmpComment);27         user.setDegree(tmpDegree);28         user.setEmail(tmpEmail);29         user.setLocal(tmpLocal);30         user.setType(tmpType);31         user.setUserid(tmpUserid);32         user.setUsername(tmpUsername);33         user.setUserpass(tmpUserpass);34         return user;35       }36     } catch (Exception e) {37       System.out.println(e.toString());38     } finally {39       db.close();40     }  41     return null;42   }

View Code

3.int update();更新用户信息

 1 public int update(String userid) throws Exception { 2     Connection con=null; 3     DBBean db = new DBBean(); 4     String sql="update usertable set username='"+username+"',userpass='"+userpass+"',birthday='"+new java.sql.Date(birthday.getTime())+"',degree='"+degree+ 5         "',local='"+local+"',email='"+email+"',address='"+address+"',comment='"+comment+"' where userid='"+userid+"'"; 6     try 7     { 8       con=db.getConnection(); 9       return db.executeUpdate(sql);10     }catch(Exception e)11     {12       System.out.println(e.toString());13     }14     finally{db.close();}15     return 0;16   }

View Code

4.Int delete(String userid);//根据传入的用户id删除用户信息

 1 public int delete(String userid) throws Exception { 2     Connection con=null; 3     DBBean db = new DBBean(); 4     String sql="delete from usertable where userid='"+userid+"'"; 5     try 6     { 7       con=db.getConnection(); 8       return db.executeUpdate(sql); 9     }catch(Exception e)10     {11       System.out.println(e.toString());12     }13     finally{db.close();}14     return 0;15   }

View Code

5.boolean hasExist(String userid);//查询用户是否存在

 1 public boolean hasExist(String userid) throws Exception { 2     boolean find=false; 3     Connection con = null; 4     ResultSet rs=null; 5     DBBean db = new DBBean(); 6     String sql = "select * from usertable where userid='"+userid+"'"; 7     try 8     { 9       con=db.getConnection();10       rs= db.executeQuery(sql);11       if(rs.next())12       {13         find=true;14       }15       else16       {17         find =false;18       }19     }catch(Exception e)20     {21       System.out.println(e.toString());22     }23     finally{db.close();}24   return find;25     26   }

View Code

6.Integer getPageCount() 查询数据库中总数对应在页面显示的总页数(10/页)

 1 public Integer getPageCount() throws Exception { 2     int pageCount=1; 3     Connection con = null; 4     ResultSet rs=null; 5     DBBean db = new DBBean(); 6     String sql="select count(*) from usertable"; 7     try 8     { 9       con=db.getConnection();10       rs= db.executeQuery(sql);11       if(rs.next())12       {13         int n=rs.getInt(1);14         pageCount=(n-1)/10+1;15       }16       17     }catch(Exception e)18     {19       System.out.println(e.toString());20     }21     finally{db.close();}22     return new Integer(pageCount);23   }

View Code

7.ArrayList findAllUser(String pageNo)返回当前页面的所有数据

//计算当前页的开始和结束行数,从数据库中查询所有数据,循环遍历结果集,把在当前页的内容放在ArrayList中

 1 public ArrayList findAllUser(String pageNo) throws Exception { 2     ArrayList<UserBean> userlist=new ArrayList<UserBean>(); 3     Connection con = null; 4     ResultSet rs=null; 5     DBBean db = new DBBean(); 6     String sql="select * from usertable"; 7     try 8     { 9       con=db.getConnection();10       rs= db.executeQuery(sql);11       int iPageNo=1;12       try{13         iPageNo=Integer.parseInt(pageNo);14       }15       catch(Exception e){}16       int begin=(iPageNo-1)*10+1;//当前页面开始的记录17       int end=iPageNo*10;//当前页面结束的记录18       int index=1;19       UserBean user=null;20       while(rs.next())21       {22         if(begin>index)//遇到在当前页面之前的记录直接跳过23           continue;24         if(end<index)//遇到在当前页面之后的记录退出循环25           break;26         String tmpUserid=rs.getString(1);27         String tmpUsername=rs.getString(2);28         String tmpUserpass=rs.getString(3);29         String tmpType=rs.getString(4);30         java.util.Date tmpBirthday=rs.getDate(5);31         String tmpDegree=rs.getString(6);32         String tmpLocal =rs.getString(7);33         String tmpEmail=rs.getString(8);34         String tmpAddress=rs.getString(9);35         String tmpComment=rs.getString(10);36         user=new UserBean();37         user.setAddress(tmpAddress);38         user.setBirthday(tmpBirthday);39         user.setComment(tmpComment);40         user.setDegree(tmpDegree);41         user.setEmail(tmpEmail);42         user.setLocal(tmpLocal);43         user.setType(tmpType);44         user.setUserid(tmpUserid);45         user.setUsername(tmpUsername);46         user.setUserpass(tmpUserpass);47         userlist.add(user);//查找到的记录封装好放在userlist中48         index++;49       }50       51       52     }catch(Exception e)53     {}54     finally{db.close();}55     return userlist;56   }

View Code

 

控制器设计

1.添加用户控制器:从request对象中取出内容封装在UserBean对象中,判断用户id是否存在,调用add方法添加信息,跳转到显示页面

 1 public void doGet(HttpServletRequest request, HttpServletResponse response) 2       throws ServletException, IOException { 3     String tmpUserid=request.getParameter("userid"); 4     String tmpUsername=request.getParameter("username"); 5     String tmpUserpass=request.getParameter("userpass"); 6     String birthday=request.getParameter("birthday"); 7     DateFormat df=new SimpleDateFormat("yyyy-mm-dd"); 8     java.util.Date tmpBirthday=null; 9     try10     {tmpBirthday=df.parse(birthday);11       12     }catch(Exception e)13     {}14     15     String tmpDegree=request.getParameter("degree");16     tmpDegree=new String(tmpDegree.getBytes("8859_1"));17     String tmpLocal =request.getParameter("local");18     tmpLocal=new String(tmpLocal.getBytes("8859_1"));19     String tmpEmail=request.getParameter("email");20     String tmpAddress=request.getParameter("address");21     String tmpComment=request.getParameter("comment");22     UserBean user=new UserBean();23     user.setAddress(tmpAddress);24     user.setBirthday(tmpBirthday);25     user.setComment(tmpComment);26     user.setDegree(tmpDegree);27     user.setEmail(tmpEmail);28     user.setLocal(tmpLocal);29     30     user.setUserid(tmpUserid);31     user.setUsername(tmpUsername);32     user.setUserpass(tmpUserpass);33     String forward=null;34     String info=null;35     try {36       if(user.hasExist(tmpUserid))37       {38         info="用户已存在";39         forward="addUser.jsp";40       }41       else42       {43         try{44           user.add();45           forward="FindAllUser";46           info="添加成功";47         }catch(Exception e)48         {49           info="数据库异常";50           forward="FindAllUser";51         }52       }53     } catch (Exception e) {54       // TODO Auto-generated catch block55       e.printStackTrace();56     }57     58     request.setAttribute("info", info);59     RequestDispatcher rd=request.getRequestDispatcher(forward);//更新成功返回用户列表界面60     rd.forward(request, response);61   }

View Code

2.更新用户信息控制器:

 1 public void doGet(HttpServletRequest request, HttpServletResponse response) 2       throws ServletException, IOException { 3     String tmpUserid=request.getParameter("userid"); 4     String tmpUsername=request.getParameter("username"); 5     String tmpUserpass=request.getParameter("userpass"); 6     String birthday=request.getParameter("birthday"); 7     DateFormat df=new SimpleDateFormat("yyyy-mm-dd"); 8     java.util.Date tmpBirthday=null; 9     try10     {tmpBirthday=df.parse(birthday);11       12     }catch(Exception e)13     {}14     15     String tmpDegree=request.getParameter("degree");16     String tmpLocal =request.getParameter("local");17     String tmpEmail=request.getParameter("email");18     String tmpAddress=request.getParameter("address");19     String tmpComment=request.getParameter("comment");20     UserBean user=new UserBean();21     user.setAddress(tmpAddress);22     user.setBirthday(tmpBirthday);23     user.setComment(tmpComment);24     user.setDegree(tmpDegree);25     user.setEmail(tmpEmail);26     user.setLocal(tmpLocal);27     28     user.setUserid(tmpUserid);29     user.setUsername(tmpUsername);30     user.setUserpass(tmpUserpass);31     String info;32     try{33       if(user.update(tmpUserid)>0)34       {35         info="信息更新成功";36       }37       else38       {39         info="信息更新失败";40       }41       42     }catch(Exception e)43     {44       info="数据库异常";45     }46     request.setAttribute("info", info);47     RequestDispatcher rd=request.getRequestDispatcher("FindAllUser");//更新成功返回用户列表界面48     rd.forward(request, response);  49   }

View Code

3.显示信息控制器:

//先获取当前页码,根据当前页码调用UserBean中的方法返回userlist放在request对象中,跳转到显示页面。

 1 public void doGet(HttpServletRequest request, HttpServletResponse response) 2       throws ServletException, IOException { 3     int pageNo=1; 4     String strPageNo=request.getParameter("pageNo"); 5     if(strPageNo!=null) 6     { 7       pageNo=Integer.parseInt(strPageNo); 8     } 9     UserBean user=new UserBean();10     try{11       ArrayList<UserBean> userlist=user.findAllUser(String.valueOf(pageNo));12       request.setAttribute("userlist", userlist);13       Integer pageCount=user.getPageCount();14       request.setAttribute("pageCount", pageCount);15       request.setAttribute("pageNo", pageNo);16     }catch(Exception e)17     {18       19     }20     RequestDispatcher rd=request.getRequestDispatcher("userlist.jsp");21     rd.forward(request, response);22   }

View Code

4.删除用户信息控制器:

 1 public void doGet(HttpServletRequest request, HttpServletResponse response) 2       throws ServletException, IOException { 3     String userid=request.getParameter("userid"); 4     UserBean user=new UserBean(); 5     String info=null; 6     try 7     { 8       if(user.delete(userid)>0) 9       {10         info="删除成功";11       }12       else13       {14         info="删除失败 ";15       }16     }catch(Exception e)17     {18       info="数据异常";19     }20     request.setAttribute("info", info);21     RequestDispatcher rd=request.getRequestDispatcher("FindAllUser");22     rd.forward(request, response);23 24   }

View Code

视图设计

只简单介绍显示页面的设计:

1.javascript部分:

<script language="javascript">  function init() {    alert("${info}");   获取request对象中的info消息显示  }  </script><c:if test="${!empty info}"><script language="javascript">  window.onload=init;  </script></c:if>

2.

<!--分页显示-->  <table align="center">    <tr>      <td>共有${pageCount}页,这是第${pageNo}页</td><!--用表达式语言取出request对象中的消息-->      <c:if test="${pageNo==1 }"><!--第一页和最后一页要特别对待,第一页中的‘第一页’和‘上一页’不能显示为超链接,最后一页中的‘最后一页’和‘下一页’不能显示为超链接-->
<td>第一页</td> <td>上一页</td> </c:if> <!-- 如果不是第一页显示超链接 --> <c:if test="${pageNo!=1 }"> <td><a href="findAllUser?pageNo=1">第一页</a></td> <td><a href="findAllUser?pageNo=${pageNo-1 }">上一页</a></td> </c:if> <c:if test="${pageNo==pageCount }"> <td> 下一页</td> <td> 最后一页</td> </c:if> <!-- 如果不是第一页显示超链接 --> <c:if test="${pageNo!=1 }"> <td><a href="FindAllUser?pageNo=${pageNo+1 }">下一页</a></td> <td><a href="FindAllUser?pageNo=pageCount">最后一页</a></td> </c:if> <td> <form action="FindAllUser"> 跳转到<input type="text" name="pageNo">页 <input type="submit" value="跳转"> </form> </td> </tr> </table> <table align="center"> <tr><td>用户编号</td> <td>用户名</td> <td>生日</td> <td>学历</td> <td>地区</td> <td>Email</td> <td>地址</td> </tr> <c:forEach items="${userlist }" var="user"> <tr> <td>${user.userid }</td> <td>${user.username }</td> <td>${user.birthday }</td> <td>${user.degree }</td> <td>${user.local }</td> <td>${user.email }</td> <td>${user.address }</td> <td>...</td> <td>
        <!--两个表单分别用来处理删除和修改操作-->
<form action="DeleteUser" method="post" onSubmit="return confirm('真的要删除该用户吗?');"> <input type="hidden" name="userid" value="${user.userid }"> <input type="submit" value="删除"> </form> </td> <td> <form action="UpdateFindUser" method="post" > <input type="hidden" name="userid" value="${user.userid }"> <input type="submit" value="修改"> </form> </td> </tr> </c:forEach></table>

tips:添加信息和修改信息页面比较简单,在这里不赘述了。

 

  总结:

    从这个小例子中学到了什么:jdbc连接数据库实现增删改查,mvc模式的理解,表达式的使用,标签库的初步了解。