使用jdbc实现简单的mvc模式的增删改查

比眉伴天荒 2022-09-24 15:17 112阅读 0赞

原文：http://www.cnblogs.com/zhhx/p/4378584.html?utm\_source=tuicool&utm\_medium=referral

**Mvc模式设计：**

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

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

模型：userbean

**数据库层**：DBBean

**总体设计：**

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

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

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

**详细设计：**

数据库层：DBBean

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

public class DBBean {
        private Connection con;
        private Statement stmt;
        private ResultSet rs;
        public DBBean() {
            
        }
        //获取数据库的连接
        public Connection getConnection() throws Exception
        {
            String url="jdbc:mysql://localhost:3306/bookstore";
            String dbuser="root";
            String dbpass="";
            if(con==null)
            {
                Class.forName("com.mysql.jdbc.Driver");
                con=DriverManager.getConnection(url, dbuser, dbpass);
            }
            return con;
        }
        
        //执行查询语句，返回结果集
        public ResultSet executeQuery(String sql) throws Exception
        {
            if(con==null)
            {
                throw new Exception("没有连接对象可用");
            }
            stmt=con.createStatement();
            rs=stmt.executeQuery(sql);
            return rs;
        }
        
        public int executeUpdate(String sql)throws Exception
        {
            if(con==null)
            {
                throw new Exception("没有连接对象可用");
            }
            stmt=con.createStatement();
            
            return stmt.executeUpdate(sql);
        }
        
        public void close()
        {
            if(rs!=null)
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            try {
                stmt.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            try {
                con.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

模型层设计：UserBean

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

public void add() throws Exception {
            Connection con = null;
            DBBean db = new DBBean();
            String sql = "insert into usertable values('"+userid+"','"+username+"','"+userpass+"','"+type+"','"+new java.sql.Date(birthday.getTime())+"','"+degree+"','"+local+"','"+email+"','"+address+"','"+comment+"')";
    
            try {
                con = db.getConnection();
                db.executeUpdate(sql);
            } catch (Exception e) {
                System.out.println(e.toString());
    
            } finally {
                db.close();
            }
        }

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

public UserBean findUserById(String userid) throws Exception {
            Connection con = null;
            ResultSet rs=null;
            DBBean db = new DBBean();
            String sql = "select * from usertable where userid='"+userid+"'";
    
            try {
                con = db.getConnection();
                rs=db.executeQuery(sql);
                
                if(rs.next())
                {
                    String tmpUserid=rs.getString(1);
                    String tmpUsername=rs.getString(2);
                    String tmpUserpass=rs.getString(3);
                    String tmpType=rs.getString(4);
                    java.util.Date tmpBirthday=rs.getDate(5);
                    String tmpDegree=rs.getString(6);
                    String tmpLocal =rs.getString(7);
                    String tmpEmail=rs.getString(8);
                    String tmpAddress=rs.getString(9);
                    String tmpComment=rs.getString(10);
                    UserBean user=new UserBean();
                    user.setAddress(tmpAddress);
                    user.setBirthday(tmpBirthday);
                    user.setComment(tmpComment);
                    user.setDegree(tmpDegree);
                    user.setEmail(tmpEmail);
                    user.setLocal(tmpLocal);
                    user.setType(tmpType);
                    user.setUserid(tmpUserid);
                    user.setUsername(tmpUsername);
                    user.setUserpass(tmpUserpass);
                    return user;
                }
            } catch (Exception e) {
                System.out.println(e.toString());
            } finally {
                db.close();
            }    
            return null;
        }

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

public int update(String userid) throws Exception {
            Connection con=null;
            DBBean db = new DBBean();
            String sql="update usertable set username='"+username+"',userpass='"+userpass+"',birthday='"+new java.sql.Date(birthday.getTime())+"',degree='"+degree+
                    "',local='"+local+"',email='"+email+"',address='"+address+"',comment='"+comment+"' where userid='"+userid+"'";
            try
            {
                con=db.getConnection();
                return db.executeUpdate(sql);
            }catch(Exception e)
            {
                System.out.println(e.toString());
            }
            finally{db.close();}
            return 0;
        }

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

public int delete(String userid) throws Exception {
            Connection con=null;
            DBBean db = new DBBean();
            String sql="delete  from usertable where userid='"+userid+"'";
            try
            {
                con=db.getConnection();
                return db.executeUpdate(sql);
            }catch(Exception e)
            {
                System.out.println(e.toString());
            }
            finally{db.close();}
            return 0;
        }

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

public boolean hasExist(String userid) throws Exception {
            boolean find=false;
            Connection con = null;
            ResultSet rs=null;
            DBBean db = new DBBean();
            String sql = "select * from usertable where userid='"+userid+"'";
            try
            {
                con=db.getConnection();
                rs= db.executeQuery(sql);
                if(rs.next())
                {
                    find=true;
                }
                else
                {
                    find =false;
                }
            }catch(Exception e)
            {
                System.out.println(e.toString());
            }
            finally{db.close();}
        return find;
            
        }

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

public Integer getPageCount() throws Exception {
            int pageCount=1;
            Connection con = null;
            ResultSet rs=null;
            DBBean db = new DBBean();
            String sql="select count(*) from usertable";
            try
            {
                con=db.getConnection();
                rs= db.executeQuery(sql);
                if(rs.next())
                {
                    int n=rs.getInt(1);
                    pageCount=(n-1)/10+1;
                }
                
            }catch(Exception e)
            {
                System.out.println(e.toString());
            }
            finally{db.close();}
            return new Integer(pageCount);
        }

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

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

public ArrayList findAllUser(String pageNo) throws Exception {
            ArrayList<UserBean> userlist=new ArrayList<UserBean>();
            Connection con = null;
            ResultSet rs=null;
            DBBean db = new DBBean();
            String sql="select * from usertable";
            try
            {
                con=db.getConnection();
                rs= db.executeQuery(sql);
                int iPageNo=1;
                try{
                    iPageNo=Integer.parseInt(pageNo);
                }
                catch(Exception e){}
                int begin=(iPageNo-1)*10+1;//当前页面开始的记录
                int end=iPageNo*10;//当前页面结束的记录
                int index=1;
                UserBean user=null;
                while(rs.next())
                {
                    if(begin>index)//遇到在当前页面之前的记录直接跳过
                        continue;
                    if(end<index)//遇到在当前页面之后的记录退出循环
                        break;
                    String tmpUserid=rs.getString(1);
                    String tmpUsername=rs.getString(2);
                    String tmpUserpass=rs.getString(3);
                    String tmpType=rs.getString(4);
                    java.util.Date tmpBirthday=rs.getDate(5);
                    String tmpDegree=rs.getString(6);
                    String tmpLocal =rs.getString(7);
                    String tmpEmail=rs.getString(8);
                    String tmpAddress=rs.getString(9);
                    String tmpComment=rs.getString(10);
                    user=new UserBean();
                    user.setAddress(tmpAddress);
                    user.setBirthday(tmpBirthday);
                    user.setComment(tmpComment);
                    user.setDegree(tmpDegree);
                    user.setEmail(tmpEmail);
                    user.setLocal(tmpLocal);
                    user.setType(tmpType);
                    user.setUserid(tmpUserid);
                    user.setUsername(tmpUsername);
                    user.setUserpass(tmpUserpass);
                    userlist.add(user);//查找到的记录封装好放在userlist中
                    index++;
                }
                
                
            }catch(Exception e)
            {}
            finally{db.close();}
            return userlist;
        }

控制器设计

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

public void doGet(HttpServletRequest request, HttpServletResponse response)
                throws ServletException, IOException {
            String tmpUserid=request.getParameter("userid");
            String tmpUsername=request.getParameter("username");
            String tmpUserpass=request.getParameter("userpass");
            String birthday=request.getParameter("birthday");
            DateFormat df=new SimpleDateFormat("yyyy-mm-dd");
            java.util.Date tmpBirthday=null;
            try
            {tmpBirthday=df.parse(birthday);
                
            }catch(Exception e)
            {}
            
            String tmpDegree=request.getParameter("degree");
            tmpDegree=new String(tmpDegree.getBytes("8859_1"));
            String tmpLocal =request.getParameter("local");
            tmpLocal=new String(tmpLocal.getBytes("8859_1"));
            String tmpEmail=request.getParameter("email");
            String tmpAddress=request.getParameter("address");
            String tmpComment=request.getParameter("comment");
            UserBean user=new UserBean();
            user.setAddress(tmpAddress);
            user.setBirthday(tmpBirthday);
            user.setComment(tmpComment);
            user.setDegree(tmpDegree);
            user.setEmail(tmpEmail);
            user.setLocal(tmpLocal);
            
            user.setUserid(tmpUserid);
            user.setUsername(tmpUsername);
            user.setUserpass(tmpUserpass);
            String forward=null;
            String info=null;
            try {
                if(user.hasExist(tmpUserid))
                {
                    info="用户已存在";
                    forward="addUser.jsp";
                }
                else
                {
                    try{
                        user.add();
                        forward="FindAllUser";
                        info="添加成功";
                    }catch(Exception e)
                    {
                        info="数据库异常";
                        forward="FindAllUser";
                    }
                }
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            
            request.setAttribute("info", info);
            RequestDispatcher rd=request.getRequestDispatcher(forward);//更新成功返回用户列表界面
            rd.forward(request, response);
        }

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

public void doGet(HttpServletRequest request, HttpServletResponse response)
                throws ServletException, IOException {
            String tmpUserid=request.getParameter("userid");
            String tmpUsername=request.getParameter("username");
            String tmpUserpass=request.getParameter("userpass");
            String birthday=request.getParameter("birthday");
            DateFormat df=new SimpleDateFormat("yyyy-mm-dd");
            java.util.Date tmpBirthday=null;
            try
            {tmpBirthday=df.parse(birthday);
                
            }catch(Exception e)
            {}
            
            String tmpDegree=request.getParameter("degree");
            String tmpLocal =request.getParameter("local");
            String tmpEmail=request.getParameter("email");
            String tmpAddress=request.getParameter("address");
            String tmpComment=request.getParameter("comment");
            UserBean user=new UserBean();
            user.setAddress(tmpAddress);
            user.setBirthday(tmpBirthday);
            user.setComment(tmpComment);
            user.setDegree(tmpDegree);
            user.setEmail(tmpEmail);
            user.setLocal(tmpLocal);
            
            user.setUserid(tmpUserid);
            user.setUsername(tmpUsername);
            user.setUserpass(tmpUserpass);
            String info;
            try{
                if(user.update(tmpUserid)>0)
                {
                    info="信息更新成功";
                }
                else
                {
                    info="信息更新失败";
                }
                
            }catch(Exception e)
            {
                info="数据库异常";
            }
            request.setAttribute("info", info);
            RequestDispatcher rd=request.getRequestDispatcher("FindAllUser");//更新成功返回用户列表界面
            rd.forward(request, response);    
        }

3.显示信息控制器：

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

public void doGet(HttpServletRequest request, HttpServletResponse response)
                throws ServletException, IOException {
            String tmpUserid=request.getParameter("userid");
            String tmpUsername=request.getParameter("username");
            String tmpUserpass=request.getParameter("userpass");
            String birthday=request.getParameter("birthday");
            DateFormat df=new SimpleDateFormat("yyyy-mm-dd");
            java.util.Date tmpBirthday=null;
            try
            {tmpBirthday=df.parse(birthday);
                
            }catch(Exception e)
            {}
            
            String tmpDegree=request.getParameter("degree");
            String tmpLocal =request.getParameter("local");
            String tmpEmail=request.getParameter("email");
            String tmpAddress=request.getParameter("address");
            String tmpComment=request.getParameter("comment");
            UserBean user=new UserBean();
            user.setAddress(tmpAddress);
            user.setBirthday(tmpBirthday);
            user.setComment(tmpComment);
            user.setDegree(tmpDegree);
            user.setEmail(tmpEmail);
            user.setLocal(tmpLocal);
            
            user.setUserid(tmpUserid);
            user.setUsername(tmpUsername);
            user.setUserpass(tmpUserpass);
            String info;
            try{
                if(user.update(tmpUserid)>0)
                {
                    info="信息更新成功";
                }
                else
                {
                    info="信息更新失败";
                }
                
            }catch(Exception e)
            {
                info="数据库异常";
            }
            request.setAttribute("info", info);
            RequestDispatcher rd=request.getRequestDispatcher("FindAllUser");//更新成功返回用户列表界面
            rd.forward(request, response);    
        }

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

public void doGet(HttpServletRequest request, HttpServletResponse response)
                throws ServletException, IOException {
            String tmpUserid=request.getParameter("userid");
            String tmpUsername=request.getParameter("username");
            String tmpUserpass=request.getParameter("userpass");
            String birthday=request.getParameter("birthday");
            DateFormat df=new SimpleDateFormat("yyyy-mm-dd");
            java.util.Date tmpBirthday=null;
            try
            {tmpBirthday=df.parse(birthday);
                
            }catch(Exception e)
            {}
            
            String tmpDegree=request.getParameter("degree");
            String tmpLocal =request.getParameter("local");
            String tmpEmail=request.getParameter("email");
            String tmpAddress=request.getParameter("address");
            String tmpComment=request.getParameter("comment");
            UserBean user=new UserBean();
            user.setAddress(tmpAddress);
            user.setBirthday(tmpBirthday);
            user.setComment(tmpComment);
            user.setDegree(tmpDegree);
            user.setEmail(tmpEmail);
            user.setLocal(tmpLocal);
            
            user.setUserid(tmpUserid);
            user.setUsername(tmpUsername);
            user.setUserpass(tmpUserpass);
            String info;
            try{
                if(user.update(tmpUserid)>0)
                {
                    info="信息更新成功";
                }
                else
                {
                    info="信息更新失败";
                }
                
            }catch(Exception e)
            {
                info="数据库异常";
            }
            request.setAttribute("info", info);
            RequestDispatcher rd=request.getRequestDispatcher("FindAllUser");//更新成功返回用户列表界面
            rd.forward(request, response);    
        }

视图设计

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

1.javascript部分：

<table align="center">
            <tr>
                <td>共有${pageCount}页，这是第${pageNo}页</td>
    
                <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模式的理解，表达式的使用，标签库的初步了解。