Serverlet: How to Add, Delete, Modify and Query item code

Serverlet project code

Define database connection closure

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.*;

public class DBManager {
	
	private static final String DRIVERNAME = "com.mysql.jdbc.Driver";
	private static final String URL = "jdbc:mysql://127.0.0.1:3306/bbs?useUnicode=true&characterEncoding=utf8";
	private static final String USERNAME = "root";
	private static final String USERPWD = "123456";
	
	public Connection getconn() throws ClassNotFoundException, SQLException {
		Class.forName(DRIVERNAME);
		Connection conn=DriverManager.getConnection(URL,USERNAME,USERPWD);
		System.out.print("DB Success!!");
		return  conn;
		
	}
	
	public void closeAll(ResultSet rs,PreparedStatement pstmt,Connection conn) throws SQLException {
			if(rs!=null) {
				rs.close();
			}
			if(pstmt!=null) {
				pstmt.close();
			}
			if(conn!=null) {
				conn.close();
			}
	}	
}

Add, delete, change and search (BaseDao)

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * 
 * @author Administrator
 *General method of adding, deleting and checking
 */
public class BaseDao {

	public boolean insert_update_del(String sql,Object val[]) throws ClassNotFoundException, SQLException {
		boolean flag=false;
		Connection con=new DBManager().getconn();
		PreparedStatement pstmt=con.prepareStatement(sql);
		if(val!=null) {
			for(int i=0;i<val.length;i++) {
				pstmt.setObject(i+1, val[i]);
			}			
		}	
		if(pstmt.executeUpdate()>0) {
			flag=true;
		}
		return flag;
	}
	
	public ResultSet query(String sql,Object val[]) throws ClassNotFoundException, SQLException {
		boolean flag=false;
		Connection con=new DBManager().getconn();
		PreparedStatement pstmt=con.prepareStatement(sql);
		if(val!=null) {
			for(int i=0;i<val.length;i++) {
				pstmt.setObject(i+1, val[i]);
			}			
		}	
		ResultSet rs=pstmt.executeQuery();
		return rs;
	}
}

Inherit, add, delete, modify and query

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentsDao extends BaseDao{
	
	public boolean regeditStudent(Student stu) throws ClassNotFoundException, SQLException {
		String sql="insert into students values (null,?,?)";
		Object[] val= {stu.getSname(),stu.getSpwd()};
		return insert_update_del(sql,val);
	}
	
	public List<Student> quaryAll() throws ClassNotFoundException, SQLException{
		List<Student> list=new ArrayList<Student>();
		String sql="select * from students";
		Object[] val= {};
		ResultSet rs=query(sql,val);
		while(rs.next()) {
			Student stu=new  Student();
			stu.setSid(rs.getInt("sid"));
			stu.setSname(rs.getString("sname"));
			stu.setSpwd(rs.getString("spwd"));
			list.add(stu);
		}
		return list;
	}
	public Student queryById(int sid) throws ClassNotFoundException, SQLException {
		Student stu=new Student();
		String sql="select * from students where sid=?";
		Object[] val= {sid};
		ResultSet rs=query(sql,val);
		while(rs.next()) {
			stu.setSid(rs.getInt("sid"));
			stu.setSname(rs.getString("sname"));
			stu.setSpwd(rs.getString("spwd"));
		}
		return stu;
	}
	public boolean updateStudent(Student stu) throws ClassNotFoundException, SQLException {
//		Student stu=new Student();
		String sql="update students set sname=?,spwd=?where sid=?";
		Object[] val= {stu.getSname(),stu.getSpwd(),stu.getSid()};
		if(insert_update_del(sql,val)) {
			return true;
		}
		else {
			return false;
		}
	}
}

RegeditServlet

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

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class RegeditServlet
 */
@WebServlet("/RegeditServlet")
public class RegeditServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public RegeditServlet() {
        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
		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
//		doGet(request, response);
		System.out.println("Welcome……");
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		
		String sname=request.getParameter("uname");
		String spwd=request.getParameter("upwd");
		Student stu=new Student();
		stu.setSname(sname);
		stu.setSpwd(spwd);
		try {
			if(new StudentsDao().regeditStudent(stu)) {
//				response.sendRedirect("success.jsp");
				response.sendRedirect("queryAllServlet");
			}else {
				response.sendRedirect("error.jsp");
			}
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

}

Registration page (regedit)

<%@ page language="java" contentType="text/html; charset=utf-8"
    %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
<h1>User registration page</h1>
	<form action="regeditservlet" method="post">
		<p>ursename:<input type="text" name="uname"/></p>
		<p>password:<input type="password" name="upwd"/></p>
		<input type="submit" value="register"/>
		
	</form>

</body>
</html>

Error page

<%@ page language="java" contentType="text/html; charset=utf-8"
    %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
<h1>Failed to register</h1>
</body>
</html>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://xmlns.jcp.org/xml/ns/javaee" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/Demo4.xsd" id="WebApp_ID" version="3.1">
  <display-name>Demo4</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  <servlet>
    <servlet-name>regeditservlet</servlet-name>
    <servlet-class>aaa.com.RegeditServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>regeditservlet</servlet-name>
    <url-pattern>/regeditservlet</url-pattern>
  </servlet-mapping>
  
  <!--Add additional content here -->

</web-app>

EditServlet

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

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class EditServlet
 */
@WebServlet("/EditServlet")
public class EditServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public EditServlet() {
        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
		doPost(request, response);
//		response.getWriter().append("Served at: ").append(request.getContextPath());
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
//		doGet(request, response);
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		
		try {
			int sid=Integer.parseInt(request.getParameter("uid"));
			String sname=request.getParameter("uname");
			System.out.println(sname);
			String spwd=request.getParameter("upwd");
			System.out.println(spwd);
			Student stu=new Student();
			stu.setSid(sid);
			stu.setSname(sname);
			stu.setSpwd(spwd);
		
			if(new StudentsDao().updateStudent(stu)) {
				response.sendRedirect("queryallservlet");
			}else {
				response.sendRedirect("error.jsp");
			}
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

QueryAllServlet

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

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class QueryAllServlet
 */
@WebServlet("/queryAllServlet")
public class QueryAllServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public QueryAllServlet() {
        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
		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
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		try {
			List<Student> list=new StudentsDao().quaryAll();
			request.setAttribute("list", list);
			request.getRequestDispatcher("show.jsp").forward(request, response);
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

Show page

<%@ page language="java" contentType="text/html; charset=utf-8"
    %>
<%@ page import="java.util.*" %>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
<h1>User Information Details</h1>
<table>
<tr>
	<td>num</td>
	<td>name</td>
	<td>password</td>
	<td>Operation</td>
<tr>

<c:forEach var="stu" items="${list}" step="1">
<tr>
	<td>${stu.sid}</td>
	<td>${stu.sname}</td>
	<td>${stu.spwd}</td>
	<td><a href="updateservlet?sid=${stu.sid}">修改</a>|<a href="delserverlet?sid=${stu.sid}">删除</a></td>
<tr>
</c:forEach>


</table>
</body>
</html>

web.xml

  <servlet>
    <servlet-name>editservlet</servlet-name>
    <servlet-class>aaa.com.EditServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>editservlet</servlet-name>
    <url-pattern>/editservlet</url-pattern>
  </servlet-mapping>

web.xml

  <servlet>
    <servlet-name>queryallservlet</servlet-name>
    <servlet-class>aaa.com.QueryAllServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>queryallservlet</servlet-name>
    <url-pattern>/queryallservlet</url-pattern>
  </servlet-mapping>

UpdateServlet

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

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class UpdateServlet
 */
@WebServlet("/updateServlet")
public class UpdateServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public UpdateServlet() {
        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
//		response.getWriter().append("Served at: ").append(request.getContextPath());
		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
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		int sid=Integer.parseInt(request.getParameter("sid"));
		System.out.println(sid);
		Student stu=new Student();
		try {
			stu=new StudentsDao().queryById(sid);
			request.setAttribute("stu", stu);
			request.getRequestDispatcher("update.jsp").forward(request, response);
//			System.out.println(stu.sid);
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

}

Modify page

<%@ page language="java" contentType="text/html; charset=utf-8"
    %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>Insert title here</title>
</head>
<body>
<h1>user update</h1>
	<form action="editservlet" method="post">
		<p>number:<input type="text" name="uid" readonly="readonly" value="${stu.sid}"/></p>
		<p>username:<input type="text" name="uname" value="${stu.sname}"/></p>
		<p>password:<input type="password" name="upwd" value="${stu.spwd}"/></p>
		<input type="submit" value="modify"/>
		
	</form>
</body>
</html>

web.xml

  <servlet>
    <servlet-name>updateservlet</servlet-name>
    <servlet-class>aaa.com.UpdateServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>updateservlet</servlet-name>
    <url-pattern>/updateservlet</url-pattern>
  </servlet-mapping>

SubServer

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

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class DeleteServlet
 */
@WebServlet("/DeleteServlet")
public class DeleteServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public DeleteServlet() {
        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
//		response.getWriter().append("Served at: ").append(request.getContextPath());
		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
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		int sid = Integer.parseInt(request.getParameter("sid"));
		Student stu=new Student();
		stu.setSid(sid);
		try {
			if(new StudentsDao().deleteStudent(sid)) {
				response.sendRedirect("queryAllServlet");
			}else {
				response.sendRedirect("error.jsp");
			}
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

web.xml

  <servlet>
    <servlet-name> delserverlet</servlet-name>
    <servlet-class>aaa.com.DelServerlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>delserverlet</servlet-name>
    <url-pattern>/delserverlet</url-pattern>
  </servlet-mapping>


Read More: