jsp链接SQL实现增删改查
时间: 2023-07-07 21:43:27 浏览: 41
首先需要在 JSP 中导入 JDBC 驱动,然后连接数据库。以下是一个简单的 JSP 页面,展示了如何通过 SQL 实现增删改查操作:
```jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%
// 连接数据库
String url = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "mypassword";
Connection conn = DriverManager.getConnection(url, username, password);
// 查询数据
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
out.println("id: " + rs.getInt("id") + "<br>");
out.println("name: " + rs.getString("name") + "<br>");
out.println("age: " + rs.getInt("age") + "<br>");
out.println("<br>");
}
// 插入数据
String insertSql = "INSERT INTO mytable (name, age) VALUES (?, ?)";
PreparedStatement insertStmt = conn.prepareStatement(insertSql);
insertStmt.setString(1, "张三");
insertStmt.setInt(2, 20);
insertStmt.executeUpdate();
// 修改数据
String updateSql = "UPDATE mytable SET age = ? WHERE name = ?";
PreparedStatement updateStmt = conn.prepareStatement(updateSql);
updateStmt.setInt(1, 25);
updateStmt.setString(2, "张三");
updateStmt.executeUpdate();
// 删除数据
String deleteSql = "DELETE FROM mytable WHERE id = ?";
PreparedStatement deleteStmt = conn.prepareStatement(deleteSql);
deleteStmt.setInt(1, 1);
deleteStmt.executeUpdate();
// 关闭连接
rs.close();
stmt.close();
insertStmt.close();
updateStmt.close();
deleteStmt.close();
conn.close();
%>
```
在上面的示例中,首先连接了名为“mydatabase”的数据库,然后查询了“mytable”表中的所有数据,并输出到网页上。然后,插入了一条新数据(名为“张三”,年龄为20),修改了名为“张三”的记录的年龄为25,最后删除了id为1的记录。注意,在实际开发中,应该将 SQL 语句和连接数据库的代码封装在单独的类中,以提高代码的可维护性和可重用性。