Jsp技能入门指南【十三】基于 JSTL SQL 标签库实现 MySQL 数据库毗连与数据分页展示 - qidao123.com技术社区-IT企服评测·应用市场

<%
Class.forName("com.mysql.cj.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM users");
// 然后循环取数据...
rs.close(); stmt.close(); conn.close();
%>

复制代码

<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>

复制代码

<sql:setDataSource
driver="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://localhost:3306/test"
user="root"
password="123456"
var="dbConn"
/>

复制代码

<dependencies>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>javax.servlet.jsp-api</artifactId>
<version>2.3.3</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.33</version>
</dependency>
<dependency>
<groupId>javax.servlet.jsp.jstl</groupId>
<artifactId>jstl-api</artifactId>
<version>1.2</version>
</dependency>
<dependency>
<groupId>taglibs</groupId>
<artifactId>standard</artifactId>
<version>1.1.2</version>
</dependency>
</dependencies>

复制代码

<%--
Created by IntelliJ IDEA.
User: DELL
Date: 2025/5/13
Time: 20:34
To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1 , user-scalable=no">
<title>pha-Admin</title>
<link rel="stylesheet" href="css/bootstrap.min.css"/>
<link rel="stylesheet" href="css/bootstrap-maizi.css"/>
<link rel="stylesheet" href="css/content-style.css"/>
<link rel="stylesheet" href="css/mricode.pagination.css"/>
<link rel="stylesheet" href="css/jquery.fancybox.css"/>
<link rel="stylesheet" href="css/sweetalert.css"/>
</head>
<body>
<nav class="navbar navbar-inverse navbar-fixed-top">
<div class="container">
<div class="navbar-header">
<button class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
<a href="index.html" class="navbar-brand"> pha-Admin</a>
</div>
<div class="navbar-collapse collapse">
<ul class="nav navbar-nav">
<li><a href="index.html"><span class="glyphicon glyphicon-home"></span>  后台首页</a></li>
<li><a href="user.html"><span class="glyphicon glyphicon-user"></span>  用户管理</a></li>
<li class="active"><a href="machine.html"><span class="glyphicon glyphicon-expand"></span>  机器管理</a></li>
<li><a href="service.html"><span class="glyphicon glyphicon-tasks"></span>  服务管理</a></li>
<li><a href="log.html"><span class="glyphicon glyphicon-list-alt"></span>  日志管理</a></li>
<li><a href="wiki.html"><span class="glyphicon glyphicon-book"></span>  使用文档</a></li>
</ul>
<ul class="nav navbar-nav navbar-right">
<li class="dropdown">
<a id="dLabel" type="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">
admin
<span class="caret"></span>
</a>
<ul class="dropdown-menu" aria-labelledby="dLabel">
<li><a href="index.html"><span class="glyphicon glyphicon-cog"></span>  个人设置</a></li>
</ul>
</li>
<li><a href="#bbs"><span class="glyphicon glyphicon-off"></span>  退出</a></li>
</ul>
</div>
</div>
</nav>
<div class="container">
<div class="row">
<div class="col-md-12">
<div class="panel-body" style="padding: 15px 0;">
<ul class="nav nav-tabs">
<li class="active"><a href="#list" aria-controls="machine" role="tab" data-toggle="tab">机器列表</a></li>
<li><a href="#form" aria-controls="machine" role="tab" data-toggle="tab">添加机器</a></li>
</ul>
</div>
<div class="tab-content">
<div role="tabpanel" class="tab-pane active" id="list">
<div class="panel-body">
<div class="row">
<form>
<div class="col-md-3 col-lg-offset-9">
<div class="input-group">
<input class="form-control" type="text" value="" placeholder="用户名" name="keyword">
<span class="input-group-btn">
<button type="submit" class="btn btn-primary"><i class="glyphicon glyphicon-search"></i></button>
</span>
</div>
</div>
</form>
</div>
</div>
<div class="panel panel-default">
<div class="table-responsive">
<table class="table table-bordered">
<thead>
<tr>
<th class="w8p">id</th>
<th class="w18p">域名</th>
<th class="w18p">ip</th>
<th class="w13p">sphinx路径</th>
<th class="w10p">备注</th>
<th class="w15p">创建时间</th>
<th class="w15p">操作</th>
</tr>
</thead>
<tbody>
<tr>
<td class="center">1</td>
<td>sphinx.test.com.cn</td>
<td>192.168.128.12</td>
<td>/usr/local/sphinx</td>
<td class="center">主机</td>
<td class="center">2017-03-28 12:21:09</td>
<td class="center"><a name="remove" onclick="Common.confirm('确认要删除吗？')"><i class="glyphicon glyphicon-remove"></i>删除</a></td>
</tr>
<tr>
<td class="center">2</td>
<td>sphinx.test.com.cn</td>
<td>192.168.128.13</td>
<td>/usr/local/sphinx</td>
<td class="center">备机</td>
<td class="center">2017-03-28 12:21:09</td>
<td class="center"><a name="remove" onclick="Common.confirm('确认要屏蔽吗？')"><i class="glyphicon glyphicon-remove"></i>删除</a></td>
</tr>
<tr>
<td class="center">3</td>
<td>sphinx.video.com.cn</td>
<td>192.168.158.13</td>
<td>/usr/local/sphinx</td>
<td class="center">主机</td>
<td class="center">2017-03-28 12:21:09</td>
<td class="center"><a name="remove" onclick="Common.confirm('确认要屏蔽吗？')"><i class="glyphicon glyphicon-remove"></i>删除</a></td>
</tr>
<tr>
<td class="center">4</td>
<td>sphinx.test.com.cn</td>
<td>192.168.158.14</td>
<td>/usr/local/sphinx</td>
<td class="center">备机</td>
<td class="center">2017-03-28 12:21:09</td>
<td class="center"><a name="remove" onclick="Common.confirm('确认要屏蔽吗？')"><i class="glyphicon glyphicon-remove"></i>删除</a></td>
</tr>
</tbody>
</table>
</div>
<div class="panel-footer">
<div class="row">
<div class="col-md-8 m-pagination" id="paginator">
</div>
</div>
</div>
</div>
</div>
<div role="tabpanel" class="tab-pane" id="form">
<div class="panel-body">
<form class="form-horizontal">
<div class="form-group">
<label class="col-sm-1 control-label"><span class="text-danger"></span>域名</label>
<div class="col-sm-4">
<input type="text" name="username" class="form-control" placeholder="机器域名" required>
</div>
</div>
<div class="form-group">
<label class="col-sm-1 control-label"><span class="text-danger"> * </span>IP</label>
<div class="col-sm-4">
<input type="email" name="email" class="form-control" placeholder="机器ip" required>
</div>
</div>
<div class="form-group">
<label class="col-sm-1 control-label"><span class="text-danger"> * </span>Sphinx<br>安装目录</label>
<div class="col-sm-4">
<input type="text" name="sphinx_path" class="form-control" placeholder="sphinx安装目录">
</div>
</div>
<div class="form-group">
<label class="col-sm-1 control-label"><span class="text-danger"></span>备注信息</label>
<div class="col-sm-4">
<input type="text" class="form-control" placeholder="备注" required>
</div>
</div>
<div class="form-group">
<div class="col-sm-offset-1 col-sm-10">
<button type="submit" onclick="" class="btn btn-success">保存</button>
</div>
</div>
</form>
<hr>
</div>
</div>
</div>
</div>
</div>
</div>
<footer>
<div class="container">
<div class="row">
<div class="col-md-12">
<p class="text-muted center">
Copyright © 2017-2018  phachon@163.com  
</p>
</div>
</div>
</div>
</footer>
<script src="js/plugins/jquery/jquery.js"></script>
<script src="js/bootstrap.min.js"></script>
<script src="js/plugins/pagination/mricode.pagination.js"></script>
<script src="js/plugins/fancybox/jquery.fancybox.js"></script>
<script src="js/plugins/sweetalert/sweetalert.min.js"></script>
<script src="js/common/common.js"></script>
<script src="js/module/account.js"></script>
<script type="text/javascript">
var pageData = [];
pageData.push({'pageSize': '10', 'total': '1000', 'pageIndex': '2'});
Common.paginator("#paginator", pageData);
Account.bindFancyBox();
</script>
</body>
</html>

复制代码

<table class="table table-bordered">
<thead>
<tr>
<th class="w8p">id</th>
<th class="w18p">域名</th>
<th class="w18p">ip</th>
<th class="w13p">sphinx路径</th>
<th class="w10p">备注</th>
<th class="w15p">创建时间</th>
<th class="w15p">操作</th>
</tr>
</thead>
<tbody>
<tr>
<td class="center">1</td>
<td>sphinx.test.com.cn</td>
<td>192.168.128.12</td>
<td>/usr/local/sphinx</td>
<td class="center">主机</td>
<td class="center">2017-03-28 12:21:09</td>
<td class="center"><a name="remove" onclick="Common.confirm('确认要删除吗？')"><i class="glyphicon glyphicon-remove"></i>删除</a></td>
</tr>
<tr>
<td class="center">2</td>
<td>sphinx.test.com.cn</td>
<td>192.168.128.13</td>
<td>/usr/local/sphinx</td>
<td class="center">备机</td>
<td class="center">2017-03-28 12:21:09</td>
<td class="center"><a name="remove" onclick="Common.confirm('确认要屏蔽吗？')"><i class="glyphicon glyphicon-remove"></i>删除</a></td>
</tr>
<tr>
<td class="center">3</td>
<td>sphinx.video.com.cn</td>
<td>192.168.158.13</td>
<td>/usr/local/sphinx</td>
<td class="center">主机</td>
<td class="center">2017-03-28 12:21:09</td>
<td class="center"><a name="remove" onclick="Common.confirm('确认要屏蔽吗？')"><i class="glyphicon glyphicon-remove"></i>删除</a></td>
</tr>
<tr>
<td class="center">4</td>
<td>sphinx.test.com.cn</td>
<td>192.168.158.14</td>
<td>/usr/local/sphinx</td>
<td class="center">备机</td>
<td class="center">2017-03-28 12:21:09</td>
<td class="center"><a name="remove" onclick="Common.confirm('确认要屏蔽吗？')"><i class="glyphicon glyphicon-remove"></i>删除</a></td>
</tr>
</tbody>
</table>

复制代码

CREATE TABLE sphinx_info (
id INT AUTO_INCREMENT PRIMARY KEY,
domain VARCHAR(255) NOT NULL,
ip VARCHAR(45) NOT NULL,
sphinx_path VARCHAR(255) NOT NULL,
remark VARCHAR(255) NOT NULL,
create_time DATETIME NOT NULL,
operation VARCHAR(20)
);

复制代码

INSERT INTO sphinx_info (domain, ip, sphinx_path, remark, create_time, operation)
VALUES
('sphinx.demo1.com', '192.168.100.1', '/usr/local/sphinx', '主机', '2025-05-13 09:30:00', '✖删除'),
('sphinx.demo2.net', '192.168.100.2', '/usr/local/sphinx', '备机', '2025-05-13 09:35:00', '✖删除'),
('sphinx.demo3.org', '192.168.100.3', '/usr/local/sphinx', '主机', '2025-05-13 09:40:00', '✖删除'),
('sphinx.demo4.cn', '192.168.100.4', '/usr/local/sphinx', '备机', '2025-05-13 09:45:00', '✖删除'),
('sphinx.demo5.io', '192.168.100.5', '/usr/local/sphinx', '主机', '2025-05-13 09:50:00', '✖删除');

复制代码

<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>//核心标签声明<c:forEach>属于 JSTL 核心标签库，必须显式声明才能使用。若缺少此声明，会导致标签无法剖析，数据无法遍历显示

复制代码

<tbody>
<tr>
<td class="center">1</td>
<td>sphinx.test.com.cn</td>
<td>192.168.128.12</td>
<td>/usr/local/sphinx</td>
<td class="center">主机</td>
<td class="center">2017-03-28 12:21:09</td>
<td class="center"><a name="remove" onclick="Common.confirm('确认要删除吗？')"><i class="glyphicon glyphicon-remove"></i>删除</a></td>
</tr>
<tr>
<td class="center">2</td>
<td>sphinx.test.com.cn</td>
<td>192.168.128.13</td>
<td>/usr/local/sphinx</td>
<td class="center">备机</td>
<td class="center">2017-03-28 12:21:09</td>
<td class="center"><a name="remove" onclick="Common.confirm('确认要屏蔽吗？')"><i class="glyphicon glyphicon-remove"></i>删除</a></td>
</tr>
<tr>
<td class="center">3</td>
<td>sphinx.video.com.cn</td>
<td>192.168.158.13</td>
<td>/usr/local/sphinx</td>
<td class="center">主机</td>
<td class="center">2017-03-28 12:21:09</td>
<td class="center"><a name="remove" onclick="Common.confirm('确认要屏蔽吗？')"><i class="glyphicon glyphicon-remove"></i>删除</a></td>
</tr>
<tr>
<td class="center">4</td>
<td>sphinx.test.com.cn</td>
<td>192.168.158.14</td>
<td>/usr/local/sphinx</td>
<td class="center">备机</td>
<td class="center">2017-03-28 12:21:09</td>
<td class="center"><a name="remove" onclick="Common.confirm('确认要屏蔽吗？')"><i class="glyphicon glyphicon-remove"></i>删除</a></td>
</tr>
</tbody>

复制代码

<tbody>
<sql:setDataSource
var="dbConn"
driver="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://localhost:3306/2023se3"
user="root"
password="123456"
/>
<sql:query dataSource="${dbConn}" var="sphinxRs">
SELECT id, domain, ip, sphinx_path, remark, create_time, operation
FROM sphinx_info
</sql:query>
<c:forEach items="${sphinxRs.rows}" var="row">
<tr>
<td class="center">${row.id}</td>
<td>${row.domain}</td>
<td>${row.ip}</td>
<td>${row.sphinx_path}</td>
<td class="center">${row.remark}</td>
<td class="center">${row.create_time}</td>
<td class="center">
<a href="deleteSphinx.jsp?id=${row.id}" onclick="return confirm('确认要删除吗？')">
<i class="glyphicon glyphicon-remove"></i>${row.operation}
</a>
</td>
</tr>
</c:forEach>
</tbody>

复制代码

<sql:setDataSource
var="dbConn"
driver="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://localhost:3306/2023se3"
user="root"
password="123456"
/>

复制代码

<sql:query dataSource="${dbConn}" var="sphinxRs">
SELECT id, domain, ip, sphinx_path, remark, create_time, operation
FROM sphinx_info
</sql:query>

复制代码

<c:forEach items="${sphinxRs.rows}" var="row">
<tr>
<td class="center">${row.id}</td>
<td>${row.domain}</td>
<td>${row.ip}</td>
<td>${row.sphinx_path}</td>
<td class="center">${row.remark}</td>
<td class="center">${row.create_time}</td>
<td class="center">
<a href="deleteSphinx.jsp?id=${row.id}" onclick="return confirm('确认要删除吗？')">
<i class="glyphicon glyphicon-remove"></i>${row.operation}
</a>
</td>
</tr>
</c:forEach>

复制代码