JDBC增删改查MVC

最新推荐文章于 2025-09-09 11:31:15 发布

原创最新推荐文章于 2025-09-09 11:31:15 发布 · 325 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#jdbc #mvc #string #list #bean #sql

JDBC 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了一个简单的用户管理系统实现，包括用户注册、登录、查看用户列表、修改和删除用户等功能。系统使用了JSP页面进行展示，并通过Servlet接收请求，调用Service层进行业务处理，最后由DAO层操作数据库。

<body>
<%if(session.getAttribute("uname")==null){ %>
<h2> 欢迎您！游客！</h2>
<h2><a href="regist.jsp">注册</a></h2><br/>
   <h2><a href="login.jsp">登录</a></h2>
   <%}else{ %>
   <h2> 欢迎您！<%=session.getAttribute("uname").toString() %></h2>
   <h2><a href="UserServlet?type=list">查看用户列表</a></h2>
   <h2><a href="UserServlet?type=logout">退出</a></h2>
   <%} %>

</body>

<body>
<h2><a href="index.jsp">返回首页</a></h2>
    <h2>用户信息列表 </h2>

    <table border=1 width=300>
    <tr><th>用户ID</th><th>用户名</th><th>密码</th><th>管理</th></tr>
    <%
    List list=(List)request.getAttribute("list");
    if(list!=null){
    for(int i=0;i<list.size();i++){
    UserBean u=(UserBean)list.get(i);
    %>
    <tr><td><%=u.getUid() %></td><td><%=u.getUname() %></td><td><%=u.getUpass() %></td><td><a href="UserServlet?type=update&uid=<%=u.getUid()%>">修改</a> <a href="UserServlet?type=delete&uid=<%=u.getUid()%>">删除</a></td></tr>
    <%}
    }
    %>
    </table>
</body>

<body>
<h2><a href="index.jsp">返回首页</a></h2>
success! <br>
</body>

package com.qrsx.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.qrsx.javabean.UserBean;
import com.qrsx.util.DBUtil;

public class UsersDao {
Connection conn=DBUtil.getConnection();
PreparedStatement pstate=null;
/**
* 增加功能
* @throws SQLException
*/
public void add(UserBean u) throws SQLException{
  String sql="insert into tb_users(uid,uname,password) values(?,?,?)";
  PreparedStatement pstmt=conn.prepareStatement(sql);
  pstmt.setInt(1, u.getUid());
  pstmt.setString(2, u.getUname());
  pstmt.setString(3, u.getUpass());
  pstmt.execute();
}
/**
* 查询用户信息
* @throws SQLException
*/
public boolean select(UserBean u) throws SQLException{
  String sql="select * from tb_users where uname=? and password=?";
  PreparedStatement pstmt=conn.prepareStatement(sql);
  pstmt.setString(1, u.getUname());
  pstmt.setString(2, u.getUpass());
  ResultSet rs=pstmt.executeQuery();
  if(rs.next())
   return true;
  else
   return false;

}
/**
* 查询所有用户列表
* @return
* @throws SQLException
*/
public List<UserBean> selectAll() throws SQLException {
  List<UserBean> list=new ArrayList<UserBean>();
  String sql="select * from tb_users";
  Statement stmt=conn.createStatement();
  ResultSet rs=stmt.executeQuery(sql);
  while(rs.next()){
   int uid=rs.getInt("uid");
   String uname=rs.getString("uname");
   String password=rs.getString("upassword");

   UserBean u=new UserBean();
   u.setUid(uid);
   u.setUname(uname);
   u.setUpass(password);

   list.add(u);
  }

  return list;
}
/**
* 根据用户uid查询出用户信息
* @param uid
* @return
* @throws SQLException
*/
public UserBean selectByUid(String uid) throws SQLException {
  String sql="select * from tb_users where uid="+uid;
  Statement stmt=conn.createStatement();
  ResultSet rs=stmt.executeQuery(sql);
  UserBean u=new UserBean();

  if(rs.next()){
   int uidint=rs.getInt("uid");
   String uname=rs.getString("uname");
   String password=rs.getString("password");

   u.setUid(uidint);
   u.setUname(uname);
   u.setUpass(password);
  }
  return u;
}
/**
* 修改用户信息
* @throws SQLException
*/
public void update(UserBean u) throws SQLException{
  String sql="update tb_users set uname=?,password=? where uid=?";
  System.out.println(sql+"uid:"+u.getUid()+"uname:"+u.getUname()+"pass:"+u.getUpass());

  PreparedStatement pstmt=conn.prepareStatement(sql);
  pstmt.setString(1, u.getUname());
  pstmt.setString(2, u.getUpass());
  pstmt.setInt(3, u.getUid());
  pstmt.execute();
}
/**
* 删除用户信息
* @throws SQLException
*/
public void delete(int uid) throws SQLException{
  String sql="delete from tb_users where uid="+uid;
  Statement stmt=conn.createStatement();
  stmt.execute(sql);
}
}

package com.qrsx.service;

import java.io.UnsupportedEncodingException;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.qrsx.dao.UsersDao;
import com.qrsx.javabean.UserBean;

public class Service {
HttpServletRequest request=null;
HttpServletResponse response=null;
UserBean bean=null;

UsersDao dao=new UsersDao();

/**
* service的构造方法
* @param request
* @param response
* @param bean
*/
public Service(HttpServletRequest request,HttpServletResponse response,UserBean bean){
  this.request=request;
  this.response=response;
  this.bean=bean;
}

/**
* 设置编码方式
* @throws UnsupportedEncodingException
*/
public void setEncoding() throws UnsupportedEncodingException{
  request.setCharacterEncoding("utf-8");
  response.setCharacterEncoding("utf-8");
  response.setContentType("text/html;charset=utf-8");
}
/**
* 得到用户提交的信息保存到javabean中
*/
public void getInformation(){
  String uid=request.getParameter("uid").toString();
  String uname=request.getParameter("uname").toString();
  String upass=request.getParameter("upass").toString();
  bean.setUid(Integer.parseInt(uid));
  bean.setUname(uname);
  bean.setUpass(upass);
}
/**
* 将javabean中的信息保存到数据库
* @throws SQLException
*/
public void addThing() throws SQLException{
  dao.add(bean);
}
/**
* 判断用户名密码是否正确
* @param u
* @return
* @throws SQLException
*/
public boolean isExistUser() throws SQLException {
  return dao.select(bean);
}
/**
* 获得所有用户列表
* @return
* @throws SQLException
*/
public List<UserBean> getUserList() throws SQLException {
  List<UserBean> list=dao.selectAll();
  return list;
}
/**
* 修改某个用户信息的预查询功能
* @param uid
* @return
* @throws SQLException
*/
public UserBean updateSelect(String uid) throws SQLException {

UserBean u=dao.selectByUid(uid);
return u;
}

/**
* 更改某个用户的信息
* @param u
* @throws SQLException
*/
public void update(UserBean u) throws SQLException {
dao.update(u);
}

/**
* 删除某个用户
* @param uid
* @throws SQLException
*/
public void delete(String uid) throws SQLException {
int uidint=Integer.parseInt(uid);
dao.delete(uidint);
}
}

package com.qrsx.servlet;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import com.qrsx.javabean.UserBean;
import com.qrsx.service.Service;

/**
* Servlet implementation class userservlet
*/
public class userservlet extends HttpServlet {
private static final long serialVersionUID = 1L;

/**
* @see HttpServlet#HttpServlet()
*/
public userservlet() {
super();
// TODO Auto-generated constructor stub
}

/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
*      response)
*/
protected void doGet(HttpServletRequest request,
   HttpServletResponse response) throws ServletException, IOException {
  // TODO Auto-generated method stub
  this.doPost(request, response);
}

/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
*      response)
*/
protected void doPost(HttpServletRequest request,
   HttpServletResponse response) throws ServletException, IOException {
  // TODO Auto-generated method stub
  HttpSession session=request.getSession(true);
  UserBean bean = new UserBean();
  Service service = new Service(request, response, bean);
  // 设置编码方式
  service.setEncoding();
  // 获得前台页面的变量
  String type = request.getParameter("type").toString();

  if ("regist".equals(type)) {
   service.getInformation();
   try {
    service.addThing();
   } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    response.sendRedirect("error.jsp");
   }
   response.sendRedirect("login.jsp");
  }else if("login".equals(type)){
   //查询数据库中是否存在此用户
   try {
    boolean flag=service.isExistUser();
    if(flag){
     session.setAttribute("uname", bean.getUname());
     response.sendRedirect("index.jsp");
    }
    else
     response.sendRedirect("login.jsp");
   } catch (SQLException e) {
    e.printStackTrace();
    response.sendRedirect("error.jsp");
   }
  }else if("logout".equals(type)){
   //用户退出网站
   session.invalidate();
   response.sendRedirect("index.jsp");
  }else if("list".equals(type)){
   //查看所有用户列表
   try {
    List<UserBean> list=service.getUserList();
    request.setAttribute("list", list);
    request.getRequestDispatcher("list.jsp").forward(request, response);
   } catch (SQLException e) {
    e.printStackTrace();
    response.sendRedirect("error.jsp");
   }
  }else if("update".equals(type)){
   String uid=request.getParameter("uid");
   //修改某个用户前的预查询
   try {
    bean=service.updateSelect(uid);
    request.setAttribute("user", bean);
    request.getRequestDispatcher("updateSelect.jsp").forward(request, response);
   } catch (SQLException e) {
    e.printStackTrace();
    response.sendRedirect("error.jsp");
   }

  }else if("updateConfirm".equals(type)){
   //真正修改用户的信息

   try {
    service.update(bean);
    List<UserBean> list=service.getUserList();
    request.setAttribute("list", list);
    request.getRequestDispatcher("list.jsp").forward(request, response);
   } catch (SQLException e) {
    e.printStackTrace();
    response.sendRedirect("error.jsp");
   }
  }else if("delete".equals(type)){
   //删除某个用户
   String uid=request.getParameter("uid");
   try {
    service.delete(uid);
    List<UserBean> list=service.getUserList();
    request.setAttribute("list", list);
    request.getRequestDispatcher("list.jsp").forward(request, response);
   } catch (SQLException e) {
    e.printStackTrace();
    response.sendRedirect("error.jsp");
   }

}
}

}