基于Myeclipse与MySQL数据库表格的增删改查

注:本博客是基于myeclipse的静态访问自创表格的进一步完善。(连接到MySQL数据库,对数据库中的表格信息进行增删改查操作)其中做出修改的地方如下:

一、代码的改进

1、list.jsp中修改后的内容

<body>
   <table>
   <tr>
    <td>编号</td>
    <td>学号</td>
    <td>姓名</td>
    <td>性别</td>
    <td>年龄</td>
    <td>选择</td>
    </tr> 
    <%
    if(list != null){
	    for(Student s:list){
	     %>
	     <tr>
	     	 <td><%=s.getId() %></td>
		     <td><%=s.getStuno() %></td>
		     <td><%=s.getName() %></td>
		     <td><%=s.getGender() %></td>
		     <td><%=s.getAge() %></td>
		     <td>
		     	 <a href="Delservlet?id=<%=s.getId() %>"><button>删除</button></a>
		         <a href="updataservlet?id=<%=s.getId() %>"><button>修改</button></a>
		         <a href="viewservlet?id=<%=s.getId() %>"><button>查看</button></a>
	     </tr>
  <%
  }
  } %>
   </table>
   <br>
   <a href="add.jsp"><button>添加学生信息</button></a>
  </body>

2、增加(add.jsp)

(1)在jsp文件开头导入

<%@page import="day03_student.Student"%>
<%@ page import="day03_inport.StudentDao"%>

(2)添加信息

<body>
    <h2>编辑学生个人信息</h2>
      <form action="Addservlet" method="post" role="from">
            编号:<input type="text" name="id" placeholder="编号"><br>
            学号:<input type="text" name="stuno" placeholder="学号"><br>
            姓名:<input type="text" name="name" placeholder="姓名"><br>
            性别:<input type="text" name="gender" placeholder="性别"><br>
            年龄:<input type="text" name="age" placeholder="年龄"><br>
            <button type="submit" >提交</button>
      </form>
  </body>
3、修改信息(alter.jsp)

(1)在jsp文件开头导入

<%@page import="day03_student.Student"%>
<%@ page import="day03_inport.StudentDao"%>

(2)修改信息

 <body>
      <h2>修改学生个人信息</h2>  
      <form action="Alterservlet" method="post">
      <table>
           <tr>
           <%
		 Student s=(Student)request.getAttribute("students");
	     %>
           <td>编号</td>
           <td><input type="text" name="id" value="<%=s.getId() %>" ></td></tr>
           <tr><td>学号</td>
           <td><input type="text" name="stuno" value="<%=s.getStuno()%>"></td></tr>
           <tr><td>姓名</td>
           <td><input type="text" name="name" value="<%=s.getName() %>"></td></tr>
           <tr><td>性别</td>
           <td><input type="text" name="gender" value="<%=s.getGender()%>"></td></tr>
           <tr><td>年龄</td>
           <td><input type="text" name="age" value="<%=s.getAge()%>"></td></tr>
           <tr><td colspan="2">
           <input type="submit" value="提交"></td></tr>
           </table>
       </form>       
  </body>

4、查看信息(view.jsp)

(1)在jsp文件开头导入

<%@page import="day03_student.Student"%>

(2)查看样式设计

<style type="text/css">
	table{
	border:1px solid gray;
	border-collapse:collapse;
	width:50%
	}
	td{
	border:1px solid gray;
	}
</style>

(3)信息查看

 <body>
    <h2>学生个人基本信息</h2>
   <table>
   <tr>
    <td>编号</td>
    <td>学号</td>
    <td>姓名</td>
    <td>性别</td>
    <td>年龄</td>
    </tr> 
     <tr>
      <%
	Student s=(Student)request.getAttribute("students");
	 %>
     <td><%=s.getId()%></td>
     <td><%=s.getStuno()%></td>
     <td><%=s.getName()%></td>
     <td><%=s.getGender()%></td>
     <td><%=s.getAge() %></td>
     </tr>
   </table>
  </body>

5、Listservlet.java中的修改(修改后)

public class Listservlet extends HttpServlet {

	public StudentDao dao=new StudentDao();
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		//构造学生集合
//	   List<Student> list=new ArrayList<Student>();
//	   list.add(new Student(1,"stu001","张三","男",20));
//	   list.add(new Student(2,"stu002","李四","女",21));
//	   list.add(new Student(3,"stu003","王五","男",19));
//	   list.add(new Student(4,"stu004","王子","女",20));
//	   list.add(new Student(5,"stu005","天天辉","男",24));
	   //将数据放入request中,传递到页面
	   List<Student> list=dao.queryAll();
	   request.setAttribute("students", list);
	   request.getRequestDispatcher("list.jsp").forward(request, response);
	}

6、新建StudentDao.java

(1)查询全部数据

public List<Student> queryAll() {
		List<Student> list = new ArrayList<Student>(); 
		// 1.取得连接对象
		Connection conn = DBconnection.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("select * from student");
			// 执行
			rs = ps.executeQuery();
			while (rs.next()) {
				list.add(new Student(rs.getInt(1), rs.getString(2), rs
						.getString(3), rs.getString(4), rs.getInt(5)));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBconnection.close(rs, ps, conn);
		}
		return list;
	}

(2)根据id删除数据

public void delById(int id){
		Connection conn = DBconnection.getConn();
		String sql = "delete from student where id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			//给参数赋值
			ps.setInt(1, id);
			// 执行
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBconnection.close(null, ps, conn);
		}
	}

(3)根据id修改数据

public void alterById(int id,String stuno,String name,String gender,int age){
		Connection conn = DBconnection.getConn();
		String sql = "update student set stuno=?,name=?,gender=?,age=? where id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			//给参数赋值
			 ps.setString(1, stuno);  
	         ps.setString(2, name);  
	         ps.setString(3, gender);  
	         ps.setInt(4, age);  
	         ps.setInt(5, id); 
			// 执行
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBconnection.close(null, ps, conn);
		}
	}

(4)增加数据

public void addstudent(int id,String stuno,String name,String gender,int age){
		Connection conn = DBconnection.getConn();
		String sql = "insert into student values (?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			//给参数赋值
			ps.setInt(1, id);
			ps.setString(2, stuno);
			ps.setString(3, name);
			ps.setString(4, gender);
			ps.setInt(5, age);
			// 执行
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBconnection.close(null, ps, conn);
		}
	}

(5)根据id查询数据

public Student queryById(int id){  
        String sql = "select * from student where id = ?";  
        Connection conn = null;  
        PreparedStatement ps = null;      
        ResultSet res = null;  
        Student student = null;  
        try {  
            conn = DBconnection.getConn();  
            ps =(PreparedStatement) conn.prepareStatement(sql);  
            ps.setInt(1, id);  
            res = ps.executeQuery();              
            while(res.next()){  
                student = new Student(res.getInt(1),res.getString(2), res.getString(3), res.getString(4), res.getInt(5));  
            }               
        } catch (SQLException e) {  
            // TODO Auto-generated catch block  
            e.printStackTrace();  
        }finally{  
            try{  
                if(null!=res){  
                    res.close();                  
                }  
                if(null!=ps){  
                    ps.close();  
                }  
                if(null!=conn){  
                    conn.close();  
                }  
            }catch(SQLException e){  
                e.printStackTrace();  
            }  
        }  
        return student;  
    }  

7、连接数据库参考:Myeclipse连接mysql数据库

8、增加数据的servlet(Addservlet.java)

package day03;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

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

import day03_inport.StudentDao;
import day03_student.Student;

public class Addservlet extends HttpServlet {

	private StudentDao dao = new StudentDao();
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		//获取参数
		String idstr = request.getParameter("id");
		int id = Integer.parseInt(idstr);
		String stuno = request.getParameter("stuno");
		String name = request.getParameter("name");
		String gender = request.getParameter("gender");
		String agestr = request.getParameter("age");
		int age = Integer.parseInt(agestr);
		//调用dao方法修改
		dao.addstudent(id,stuno,name,gender,age);
		List<Student> list = dao.queryAll();
		request.setAttribute("students", list);
		//跳转回列表页面
		request.getRequestDispatcher("list.jsp").forward(request, response);	
		
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
        this.doGet(request, response);
	}

}

9、修改数据的servlet(Alterservlet.java与updataservlet.java)

(1)Alterservlet.java

package day03;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

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

import day03_inport.StudentDao;
import day03_student.Student;

public class Alterservlet extends HttpServlet {

	private StudentDao dao = new StudentDao();
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		//获取参数
		request.setCharacterEncoding("utf-8");
		String idstr = request.getParameter("id");
		int id = Integer.parseInt(idstr);
		String stuno = request.getParameter("stuno");
		String name = request.getParameter("name");
		String gender = request.getParameter("gender");
		String agestr = request.getParameter("age");
		int age = Integer.parseInt(agestr);	
		//调用dao方法修改
		dao.alterById(id,stuno,name,gender,age);
		List<Student> list = dao.queryAll();
		request.setAttribute("students", list);
		//跳转回列表页面
		request.getRequestDispatcher("list.jsp").forward(request, response);	
		
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		this.doGet(request, response);
	}


}

(2)updataservlet.java

package day03;


import java.io.IOException;
import java.io.PrintWriter;

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

import day03_inport.StudentDao;
import day03_student.Student;

public class updataservlet extends HttpServlet {

	private StudentDao dao=new StudentDao();
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		String idstr = request.getParameter("id");
        int id = Integer.parseInt(idstr);
        //将数据放入request中,传递到页面
		Student student=dao.queryById(id);
		request.setAttribute("students", student);
		request.getRequestDispatcher("alter.jsp").forward(request, response);
	}

	
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		
		this.doGet(request, response);
	}
}

10、删除数据的servlet(Delservlet.java)

package day03;
import java.io.IOException;
import java.util.List;

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

import day03_inport.StudentDao;
import day03_student.Student;

public class Delservlet extends HttpServlet {
	
	private StudentDao dao = new StudentDao();
	
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		//获取参数id
		String idstr = request.getParameter("id");
		int id = Integer.parseInt(idstr);
		//调用dao方法删除
		dao.delById(id);
		List<Student> list = dao.queryAll();
		request.setAttribute("students", list);
		//跳转回列表页面
		request.getRequestDispatcher("list.jsp").forward(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		this.doGet(request, response);
	}
}

11、查看数据的servlet(viewservlet.java)

package day03;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

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

import day03_inport.StudentDao;
import day03_student.Student;

public class viewservlet extends HttpServlet {

	private StudentDao dao=new StudentDao();
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		        String idstr = request.getParameter("id");
		        int id = Integer.parseInt(idstr);
		        //将数据放入request中,传递到页面
				Student student=dao.queryById(id);
				request.setAttribute("students", student);
				request.getRequestDispatcher("view.jsp").forward(request, response);	
	}
	
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		this.doGet(request, response);
		}
}

12、整个过程的框架


13、在浏览器中的完整演示过程:

(1)登录时的首界面


(2)点击“查看学生列表”即可进入

网页上显示表:


MySQL数据库中实时显示表:


(3)点击“添加学生信息”即可进行添加操作


(4)编辑完信息后点击“提交”即可增加一名学生信息

网页上显示表:


MySQL数据库中实时显示表:


(5)点击“删除”即可删除所选学生(这里选择删除编号为1的学生)

网页上显示表:


MySQL数据库中实时显示表:


由图可知编号为1的学生已经被删除了。

(6)点击“修改”即可修改学生个人信息(这里选择修改编号为4的学生的性别与年龄)


点击提交后的结果,网页上显示表:


MySQL数据库中实时显示表:


可以看到编号为4的学生性别与年龄已经成功修改。

(7)点击“查看”即可查看所选学生的个人基本信息(这里查看编号为3的学生)


到此连接数据库实现表格的增删改查功能基本完成。

注:后续完善请看基于Myeclipse与MySQL数据库表格的增删改查(后续完善)













发布了27 篇原创文章 · 获赞 41 · 访问量 3万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 技术黑板 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览