JSP 数据库操作
JSP 提供了一系列的 JSTL 标签对数据库进行操作,这些标签是基于 JDBC 引擎实现的;
以下以JSP对MySQL数据库的操作进行示例,首先到向项目的 WEB-INF/lib 中导入mysql-connector-java-bin.jar 包;
SELECT 操作
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<html>
<head>
<title>SELECT 操作</title>
</head>
<body>
<!--设置数据连接-->
<sql:setDataSource var="snapshot"
driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/Test?useUnicode=true&characterEncoding=utf-8"
user="root"
password="123456"/>
<!--执行Select操作-->
<sql:query dataSource="${snapshot}" var="result">
SELECT * from websites;
</sql:query>
<!--解析查询结果-->
<h1>Show Database</h1>
<table>
<tr>
<th>ID</th>
<th>站点名</th>
<th>站点地址</th>
</tr>
<c:forEach var="row" items="${result.rows}">
<tr>
<td><c:out value="${row.id}"/></td>
<td><c:out value="${row.name}"/></td>
<td><c:out value="${row.url}"/></td>
</tr>
</c:forEach>
</table>
</body>
</html>
INSERT 操作
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<html>
<head>
<title>SELECT 操作</title>
</head>
<body>
<!--设置数据连接-->
<sql:setDataSource var="snapshot"
driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/Test?useUnicode=true&characterEncoding=utf-8"
user="root"
password="123456"/>
<!--执行Insert操作-->
<sql:update dataSource="${snapshot}" var="result">
INSERT INTO websites (name,url,alexa,country) VALUES ('百度', 'https://www。baidu.com', 5093, 'CN');
</sql:update>
</body>
</html>
DELETE 操作
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<html>
<head>
<title>SELECT 操作</title>
</head>
<body>
<!--设置数据连接-->
<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/RUNOOB?useUnicode=true&characterEncoding=utf-8"
user="root" password="123456"/>
<!-- 执行删除操作-->
<sql:update dataSource="${snapshot}" var="count">
DELETE FROM websites WHERE Id = ?
<sql:param value="${11}" />
</sql:update>
</body>
</html>
UPDTAE 操作
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.io.*,java.util.*,java.sql.*"%>
<%@ page import="javax.servlet.http.*,javax.servlet.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>
<html>
<head>
<title>SELECT 操作</title>
</head>
<body>
<!--设置数据连接-->
<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/RUNOOB?useUnicode=true&characterEncoding=utf-8"
user="root" password="123456"/>
<!-- 执行更新操作-->
<c:set var="SiteId" value="3"/>
<sql:update dataSource="${snapshot}" var="count">
UPDATE websites SET name = 'baidu' WHERE Id = ?
<sql:param value="${SiteId}" />
</sql:update>
</body>
</html>