1、新建一个数据库stbdb,添加一个student表,为student表添加多条数据; 2、编写select.jsp、insert.jsp、update.jsp、delete.jsp页面,实现对student表进行增删改查的操作
时间: 2024-06-13 09:07:15 浏览: 23
以下是回答:
1、新建一个数据库stbdb,添加一个student表,为student表添加多条数据:
```sql
-- 新建数据库stbdb
CREATE DATABASE stbdb;
-- 选择数据库stbdb
USE stbdb;
-- 新建student表
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT NOT NULL,
gender VARCHAR(10) NOT NULL,
major VARCHAR(20) NOT NULL
);
-- 插入多条数据
INSERT INTO student (name, age, gender, major) VALUES
('张三', 20, '男', '计算机科学'),
('李四', 21, '女', '软件工程'),
('王五', 19, '男', '信息安全');
```
2、编写select.jsp、insert.jsp、update.jsp、delete.jsp页面,实现对student表进行增删改查的操作:
select.jsp:
```jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="java.sql.*" %>
<%
// 连接数据库
String url = "jdbc:mysql://localhost:3306/stbdb?useSSL=false&serverTimezone=UTC";
String user = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, user, password);
// 查询student表中的所有数据
String sql = "SELECT * FROM student";
PreparedStatement pstmt = conn.prepareStatement(sql);
ResultSet rs = pstmt.executeQuery();
// 输出查询结果
out.println("<table border='1'>");
out.println("<tr><th>ID</th><th>姓名</th><th>年龄</th><th>性别</th><th>专业</th></tr>");
while (rs.next()) {
out.println("<tr>");
out.println("<td>" + rs.getInt("id") + "</td>");
out.println("<td>" + rs.getString("name") + "</td>");
out.println("<td>" + rs.getInt("age") + "</td>");
out.println("<td>" + rs.getString("gender") + "</td>");
out.println("<td>" + rs.getString("major") + "</td>");
out.println("</tr>");
}
out.println("</table>");
// 关闭连接
rs.close();
pstmt.close();
conn.close();
%>
```
insert.jsp:
```jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="java.sql.*" %>
<%
// 获取表单提交的数据
String name = request.getParameter("name");
int age = Integer.parseInt(request.getParameter("age"));
String gender = request.getParameter("gender");
String major = request.getParameter("major");
// 连接数据库
String url = "jdbc:mysql://localhost:3306/stbdb?useSSL=false&serverTimezone=UTC";
String user = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, user, password);
// 插入数据到student表中
String sql = "INSERT INTO student (name, age, gender, major) VALUES (?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setInt(2, age);
pstmt.setString(3, gender);
pstmt.setString(4, major);
pstmt.executeUpdate();
// 输出插入成功信息
out.println("<h3>插入成功!</h3>");
// 关闭连接
pstmt.close();
conn.close();
%>
```
update.jsp:
```jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="java.sql.*" %>
<%
// 获取表单提交的数据
int id = Integer.parseInt(request.getParameter("id"));
String name = request.getParameter("name");
int age = Integer.parseInt(request.getParameter("age"));
String gender = request.getParameter("gender");
String major = request.getParameter("major");
// 连接数据库
String url = "jdbc:mysql://localhost:3306/stbdb?useSSL=false&serverTimezone=UTC";
String user = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, user, password);
// 更新student表中的数据
String sql = "UPDATE student SET name=?, age=?, gender=?, major=? WHERE id=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, name);
pstmt.setInt(2, age);
pstmt.setString(3, gender);
pstmt.setString(4, major);
pstmt.setInt(5, id);
pstmt.executeUpdate();
// 输出更新成功信息
out.println("<h3>更新成功!</h3>");
// 关闭连接
pstmt.close();
conn.close();
%>
```
delete.jsp:
```jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ page import="java.sql.*" %>
<%
// 获取表单提交的数据
int id = Integer.parseInt(request.getParameter("id"));
// 连接数据库
String url = "jdbc:mysql://localhost:3306/stbdb?useSSL=false&serverTimezone=UTC";
String user = "root";
String password = "123456";
Connection conn = DriverManager.getConnection(url, user, password);
// 删除student表中的数据
String sql = "DELETE FROM student WHERE id=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, id);
pstmt.executeUpdate();
// 输出删除成功信息
out.println("<h3>删除成功!</h3>");
// 关闭连接
pstmt.close();
conn.close();
%>
```
相关推荐
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![rar](https://img-home.csdnimg.cn/images/20210720083606.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)
![docx](https://img-home.csdnimg.cn/images/20210720083331.png)
![zip](https://img-home.csdnimg.cn/images/20210720083736.png)