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

63 阅读 0 评论 42 点赞

我是靠谱客的博主追寻流沙，最近开发中收集的这篇文章主要介绍基于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、整个过程的框架