创建测试数据
接下来我们在 MySQL 中创建 RUNOOB 数据库,并创建 websites 数据表,表结构如下:
CREATE TABLE websites
(
id
int(11) NOT NULL AUTO_INCREMENT,
name
char(20) NOT NULL DEFAULT ‘’ COMMENT ‘站点名称’,
url
varchar(255) NOT NULL DEFAULT ‘’,
alexa
int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Alexa 排名’,
country
char(10) NOT NULL DEFAULT ‘’ COMMENT ‘国家’,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
插入一些数据:
INSERT INTO websites
VALUES (‘1’, ‘Google’, ‘https://www.google.cm/’, ‘1’, ‘USA’), (‘2’, ‘淘宝’, ‘https://www.taobao.com/’, ‘13’, ‘CN’), (‘3’, ‘菜鸟教程’, ‘http://www.runoob.com’, ‘5892’, ‘’), (‘4’, ‘微博’, ‘http://weibo.com/’, ‘20’, ‘CN’), (‘5’, ‘Facebook’, ‘https://www.facebook.com/’, ‘3’, ‘USA’);
数据表显示如下:
SELECT操作
接下来的这个例子告诉我们如何使用JSTL SQL标签来运行SQL 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”%>
<sql:query dataSource="${snapshot}" var=“result”>
SELECT * from websites;
</sql:query>
JSP 数据库实例 - 菜鸟教程
ID | 站点名 | 站点地址 |
---|---|---|
INSERT操作
这个例子告诉我们如何使用JSTL SQL标签来运行SQL 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/core” prefix=“c”%>
<%@ taglib uri=“http://java.sun.com/jsp/jstl/sql” prefix=“sql”%>
JSP 数据库实例 - 菜鸟教程
ID | 站点名 | 站点地址 |
---|---|---|
DELETE操作
这个例子告诉我们如何使用JSTL SQL标签来运行SQL 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/core” prefix=“c”%>
<%@ taglib uri=“http://java.sun.com/jsp/jstl/sql” prefix=“sql”%>
<sql:update dataSource="
s
n
a
p
s
h
o
t
"
v
a
r
=
"
c
o
u
n
t
"
>
D
E
L
E
T
E
F
R
O
M
w
e
b
s
i
t
e
s
W
H
E
R
E
I
d
=
?
<
s
q
l
:
p
a
r
a
m
v
a
l
u
e
=
"
{snapshot}" var="count"> DELETE FROM websites WHERE Id = ? <sql:param value="
snapshot"var="count">DELETEFROMwebsitesWHEREId=?<sql:paramvalue="{11}" />
</sql:update>
<sql:query dataSource="${snapshot}" var=“result”>
SELECT * from websites;
</sql:query>
JSP 数据库实例 - 菜鸟教程
ID | 站点名 | 站点地址 |
---|---|---|
UPDATE操作
这个例子告诉我们如何使用JSTL SQL标签来运行SQL UPDATE语句:
<%@ 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”%>
<c:set var=“SiteId” value=“3”/>
<sql:update dataSource="
s
n
a
p
s
h
o
t
"
v
a
r
=
"
c
o
u
n
t
"
>
U
P
D
A
T
E
w
e
b
s
i
t
e
s
S
E
T
n
a
m
e
=
′
R
U
N
O
O
B
′
W
H
E
R
E
I
d
=
?
<
s
q
l
:
p
a
r
a
m
v
a
l
u
e
=
"
{snapshot}" var="count"> UPDATE websites SET name = 'RUNOOB' WHERE Id = ? <sql:param value="
snapshot"var="count">UPDATEwebsitesSETname=′RUNOOB′WHEREId=?<sql:paramvalue="{SiteId}" />
</sql:update>
<sql:query dataSource="${snapshot}" var=“result”>
SELECT * from websites;
</sql:query>
JSP 数据库实例 - 菜鸟教程
ID | 站点名 | 站点地址 |
---|---|---|