Implementation of Adding, Deleting, Modifying and Checking the Java Web Connection Database

Realize user login. After successful login, basic operations such as
adding , deleting, modifying, and checking the student table (student) are realized. The table structure is as follows: Table name: userinfo
field name type remark
username varchar(10) username, primary key
password varchar(20) password

Table name: student
field name type remarks
sno char(10) student number, primary key
sname varchar(20) name
age int age

Insert picture description here


Insert picture description here


Running result:

Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


Insert picture description here


code part

Insert picture description here
//Student.java
package qust.jdbaexample.domain;

public class Student {
	private String sno;
	private String sname;
	private int age;
	private String username;
	private String password;
	public String getUsername(){
		return username;
	}
	public String getPassword(){
		return password;
	}
	public String getSno(){
		return sno;
	}
	public void setSno(String sno){
		this.sno=sno;
	}
	public String getSname(){
		return sname;
	}
	public void setSname(String sname){
		this.sname=sname;
	}
	public int getAge(){
		return age;
	}
	public void setAge(int age){
		this.age=age;
	}

}

Insert picture description here
package qust.jdbcexample.dao;

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





import qust.jdbaexample.domain.Student;
import qust.jdbcexample.util.JDBCUtil;

public class StudentDao {
	public List<Student> findAll(){
		List<Student> students=null;
		Connection conn=null;
		Statement stmt=null;
		ResultSet rs=null;
		try{
			conn=JDBCUtil.getConnection();
			stmt=conn.createStatement();
			String sql="select sno,sname,age from student";
			rs=stmt.executeQuery(sql);
			students=new ArrayList<Student>();
			while(rs.next()){
				Student stu=new Student();
				stu.setSno(rs.getString("sno"));
				stu.setSname(rs.getString("sname"));
				stu.setAge(rs.getInt("age"));
				students.add(stu);
				System.out.println(rs.getString("sno")+rs.getString(2)+rs.getInt(3));
			}
		}catch(Exception e){
			
		}finally{
			JDBCUtil.release(rs, stmt, conn);
		}
		return students;
	}
	public boolean insert(Student stu){
		boolean flag=false;
		Connection conn=null;
		PreparedStatement stmt=null;
		try{
			conn=JDBCUtil.getConnection();
			String sql="insert into student(sno,sname,age) values(?,?,?)";
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, stu.getSno());
			stmt.setString(2, stu.getSname());
			stmt.setInt(3, stu.getAge());
			int res=stmt.executeUpdate();
			System.out.println(res);
			if(res>0){
				flag=true;
			}
		}catch(Exception e){
			
		}finally{
			JDBCUtil.release(stmt, conn);
		}
		return flag;
	}
	public boolean del(String sno){
		boolean flag=false;
		PreparedStatement stmt=null;
		Connection conn=null;
		try{
			conn=JDBCUtil.getConnection();
			String sql="delete from student where sno=?";
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, sno);
			int res=stmt.executeUpdate();
			if(res>0){
				flag=true;
			}
		}catch(Exception e){
			
		}finally{
			JDBCUtil.release(stmt, conn);
		}
		return flag;
	}
	public Student findBySno(String sno){
		Student stu=null;
		Statement stmt=null;
		Connection conn=null;
		ResultSet rs=null;
		try{
			conn=JDBCUtil.getConnection();
			stmt=conn.createStatement();
			String sql="select sno,sname,age from student where sno='"+sno+"'";
			
			 rs=stmt.executeQuery(sql);
			if(rs.next()){
				stu=new Student();
				stu.setSno(rs.getString("sno"));
				stu.setSname(rs.getString("sname"));
				stu.setAge(rs.getInt("age"));
			}
		}catch(Exception e){
			
		}finally{
			JDBCUtil.release(rs, stmt, conn);
		}
		return stu;
	}
	public boolean update(Student stu){
		boolean flag=false;
		PreparedStatement stmt=null;
		Connection conn=null;
		try{
			conn=JDBCUtil.getConnection();
			String sql="update student set sname=?,age=? where sno=?";
			stmt=conn.prepareStatement(sql);
			stmt.setString(1, stu.getSname());
			stmt.setInt(2, stu.getAge());
			stmt.setString(3, stu.getSno());
			int res=stmt.executeUpdate();
			if(res>0){
				flag=true;
			}
		}catch(Exception e){
			
		}finally{
			JDBCUtil.release(stmt, conn);
		}
		return flag;
	}
	public boolean login(String username,String password) {
		// TODO 自动生成的方法存根
		boolean flag=false;
		Statement stmt=null;
		Connection conn=null;
		ResultSet rs=null;
		try{
			conn=JDBCUtil.getConnection();
			stmt=conn.createStatement();
			String sql="select * from userinfo where username='"+username+"' and password='"+password+"'";
			
			 rs=stmt.executeQuery(sql);
			if(rs.next()){
				flag=true;
			}
		}catch(Exception e){
			
		}finally{
			JDBCUtil.release(rs, stmt, conn);
		}
		return flag;
	}

}

Insert picture description here
package qust.jdbcexample.service;

import java.util.List;

import qust.jdbaexample.domain.Student;
import qust.jdbcexample.dao.StudentDao;

public class StudentService {
	StudentDao sd=new StudentDao();
	public List<Student> findAll(){
		return sd.findAll();
	}
	public boolean insert(Student stu){
		return sd.insert(stu);
	}
	public boolean del(String sno){
		return sd.del(sno);
	}
	public boolean update(Student stu){
		return sd.update(stu);
	}
	public Student findBySno(String sno){
		return sd.findBySno(sno);
	}
	public boolean login(String username,String password){
		return sd.login(username,password);
	}

}

Insert picture description here
package qust.jdbcexample.servlet;

import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
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;

import org.apache.commons.beanutils.BeanUtils;

import qust.jdbaexample.domain.Student;
import qust.jdbcexample.service.*;


@WebServlet("/StudentServlet")
public class StudentServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
 
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		String action=request.getParameter("action");
		switch(action){
		case "show":
			show(request,response);
			break;
		case "insert":
			insert(request,response);
			break;
		case "update":
			update(request,response);
			break;
        case "save":
        	save(request,response);
			break;
		case "del":
			del(request,response);
			break;
		case "login":
			login(request,response);
			
			break;
		}
	}
	private void login(HttpServletRequest request, HttpServletResponse response)throws ServletException,IOException {
		// TODO 自动生成的方法存根
		String username=request.getParameter("username");
		System.out.println(username);
		
		String password=request.getParameter("password");
		System.out.println(password);
		StudentService ss=new StudentService();
		
		boolean flag=ss.login(username, password);
		System.out.println("2:");
		System.out.println(flag);
		if(flag){
			request.getRequestDispatcher("demo.jsp").forward(request, response);
		}
		else{
			request.getRequestDispatcher("login.jsp").forward(request, response);
		}
		
		
	}
	private void insert(HttpServletRequest request,HttpServletResponse response)throws ServletException,IOException{
		Student stu=new Student();
		try{
			BeanUtils.populate(stu,request.getParameterMap());
			
		}catch(IllegalAccessException | InvocationTargetException e){
			e.printStackTrace();
		}
		StudentService ss=new StudentService();
		boolean flag=ss.insert(stu);
		if(flag){
			request.setAttribute("msg", "成功!");
		}else{
			request.setAttribute("msg", "失败!");
		}
		request.getRequestDispatcher("studentadd.jsp").forward(request, response);
	}
	private void show(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException{
		StudentService ss=new StudentService();
		List<Student> students=ss.findAll();
		request.setAttribute("students", students);
		request.getRequestDispatcher("show.jsp").forward(request, response);
	}
	private void save(HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException{
		Student stu=new Student();
		try{
			BeanUtils.populate(stu, request.getParameterMap());
			
		}catch(IllegalAccessException|InvocationTargetException e){
			e.printStackTrace();
		}
		StudentService ss=new StudentService();
		boolean flag=ss.update(stu);
		if(flag){
			request.setAttribute("msg","成功!");
		}else{
			request.setAttribute("msg","失败!");
		}
		request.getRequestDispatcher("StudentServlet?action=show").forward(request, response);
	}
	private void update(HttpServletRequest request,HttpServletResponse response)throws ServletException,IOException{
		String sno=request.getParameter("sno");
		StudentService ss=new StudentService();
		
		Student stu=ss.findBySno(sno);
		request.setAttribute("stu", stu);
		request.getRequestDispatcher("studentupdate.jsp").forward(request, response);
	}
	private void del(HttpServletRequest request,HttpServletResponse response)throws ServletException,IOException{
		String sno=request.getParameter("sno");
		StudentService ss=new StudentService();
		boolean flag=ss.del(sno);
		if(flag){
			request.setAttribute("msg","成功!");
		}else{
			request.setAttribute("msg","失败!");
		}
		request.getRequestDispatcher("StudentServlet?action=show").forward(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);
	}

}

Insert picture description here
package qust.jdbcexample.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtil {
	public static String url="jdbc:sqlserver://localhost:1433;DatabaseName=这里写数据库的名字";
	public static String user="sa";
	public static String pwd="这里写自己的数据库的密码";
	static{
		try{
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection(){
		Connection conn=null;
		try{
			conn=DriverManager.getConnection(url, user, pwd);
		}catch(SQLException e){
			e.printStackTrace();
		}
		return conn;
	}
	
	public static void release(ResultSet rs,Statement stmt,Connection conn){
		if(rs!=null){
			try{
				rs.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		
		rs=null;
	}
	if(stmt!=null){
		try{
			stmt.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		stmt=null;
	}
	if(conn!=null){
		try{
			conn.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		conn=null;
	}
}
	public static void release(Statement stmt,Connection conn){
		release(null, stmt, conn);
	}
}