梦入琼楼寒有月，行过石树冻无烟

JSP JDBC MySQL 数据库访问与操作

2022-04-21

在Java Server Pages语言之中，可通过各种关系数据库Oracle、SQL Server、MySQL来与Java Server pages进行访问与操作。

什么是 JDBC

JDBC（Java DataBase Connectivity）是Java平台核心类库的一个部分，他主要由一组标准接口和类组成，通常可使用JBDC执行与指定的数据库建立连接、向以连接的数据库建立连接、处理SQL命令的返回结果，本文主要使用JDBC进行对数据库进行操作即访问。

添加数据库驱动程序

由于本书使用的是MySQL8数据库，所以需要添加相应的MySQL8数据库驱动程序，而流程就是http://www.jiangxue.org.cn/mysql-connector-java-8.0.19.jar进行下载，之后将下载后的jar文件添加到项目工程中的WEB-INF/bin目录下即可。

导入数据库驱动程序语法

1
2
3
4
5
try {
	Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
	e.printStackTrace();
}

连接数据库前期操作

新建数据库

在Java Server pages中，连接数据库前提是指定一个数据库进行连接，之后在进行一些相关的操作，本章我们需要新建一个testsp库，其MySQL语法为：

1
2
mysql> CREATE DATABASE testsp;
Query OK, 1 row affected (0.01 sec)

查看数据库列表

在新建完数据库后，可使用以下MySQL语法进行查询：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| message_tal        |
| mysql              |
| performance_schema |
| sys                |
| testsp             |
| user_zsun          |
+--------------------+
7 rows in set (0.00 sec)

新建数据库表

进入数据库

1
2
mysql> use testsp
Database changed

新建数据库表

1
2
3
4
5
mysql> CREATE TABLE testuser (
    -> name char(15),
    -> age int(20)
    -> );
Query OK, 0 rows affected, 1 warning (0.03 sec)

插入表数据

sunone

1
2
mysql> INSERT INTO testuser(name,age) VALUES('sunone',21);
Query OK, 1 row affected (0.01 sec)

kuntwo

1
2
mysql> INSERT INTO testuser(name,age) VALUES('kuntwo',22);
Query OK, 1 row affected (0.00 sec)

JSP数据库操作

在Java Server Pages之中，可通过Java Server Pages 中的指令标记（page）以及动作标记（include）或者使用局部变量（<%）进行连接，本书主要使用include动作标记进行连接即操作：

1
2
3
<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
	url="jdbc:mysql://localhost:3306/数据库?characterEncoding=utf-8"
	 user="数据库帐号" password="数据库密码"/>
ID DA FA
sql:DataSource 连接数据库
sql:query 执行数据库语句
forEach 定义迭代标记
out 输出数据

连接MySQL数据库

普通连接

添加外部驱动

在连接数据库之前，需要引入jstl.jar，以及mysql-connector-java-8.0.22.jar外部jar包，可通过分别访问http://www.jiangxue.org.cn/jstl-1.2.jar以及````http://www.jiangxue.org.cn/mysql-connector-java-8.0.22.jar```进行下载，后移动添加到```WEB-INF/lib```之中即可，如缺少jstl.jar包则会在运行过程中出现错误500的错误。

引入import taglib

Java Server Pages之中可通过分别引入Java 对应包以及外部驱动可进行MySQL Jsp&Java类及页面的编写，需要引入的包如下：

1
2
3
4
<%@ 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" %>

连接池链接

在所有的开发语言之中，使当前页面或程序连接数据库都是一个消耗资源的活动，需要及分钟或几秒中的时间来执行连接数据库在最后操作数据库这一时间消耗，如对一个大型的动态网站，在几百人甚至几千人、几万人频繁的分别请求或连接数据库则会导致服务器崩溃或服务器连接较慢的问题。
从上图可以看出，用户1通过请求数据库中的线程，然后在请求到连接池（也即缓冲池）来取得一个链接（首先连接池中有一定的数量连接），然后使用完毕之后将当前的连接放回到连接池之中。
通常，在第一次连接池时，需要花费几秒或几分的连接时间，```导致这种问题的原因则是连接池中没有连接，而第二次连接中连接池就有了可用的连接，可直接从连接池中获得连接来访问目标数据库。

NewFile.jsp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ page import="java.sql.*"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<jsp:useBean id="conpool" class="bean.SqlLink" scope="application" />
<body bgcolor="AliceBule">
	<%
		Connection connection = null;
		Statement statement = null;
		ResultSet resultset = null;
		
		try {
			// 使用在SqlLink中定义的构造方法getOneCon连接池中获取一个连接对象
			connection = conpool.getOneCon();
			if (connection == null) {
			    // // 如数据库连接池不足或为空则输出
			 	out.print("No!");			
			 	return;
			}
			statement = connection.createStatement();
			resultset = statement.executeQuery("SELECT * FROM testuser");
			out.print("<table border=1>");
				out.print("<tr>");
					out.print("<th>name</th>");
					out.print("<th>age</th>");
				out.print("</tr>");
			while (resultset.next()) {
				out.print("<tr>");
					out.print("<td>"+ resultset.getString(1) + "</td>");
					out.print("<td>"+ resultset.getString(2) + "</td>");
				out.print("</tr>");
			}
			out.print("</table>");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultset != null) {
					resultset.close();
				}
				if (statement != null) {
					statement.close();
				}
				if (resultset != null) {
					// 通过调用releaseCon方法将连接返回至连接池中
					conpool.releaseCon(connection);
				} 
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	%>
</body>
</body>
</html>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
package bean;
import java.sql.*;
import java.util.ArrayList;
public class SqlLink {
	ArrayList<Connection> list = new ArrayList<Connection>();
	
	//Set SqlLink 15
	public SqlLink() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		// Sql Link set up 15
		for (int link=0;link<15;link++) {
			try {
				Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/testsp?characterEncoding=utf-8","root","password");
				list.add(connection);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	// Link SQL
	public synchronized Connection getOneCon() {
		if (list.size() > 0) {
			// 移除连接池中的一个连接
			return list.remove(0);
		} else {
			// 如连接对象用完
			return null;
		}
	}
	// 将连接返还连接池中
	public synchronized void releaseCon(Connection connection) {
		list.add(connection);
	}
}

数据库查询、插入、删除

数据库查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
<%@ 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" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<sql:setDataSource var="snapshot" driver="com.mysql.jdbc.Driver"
	url="jdbc:mysql://localhost:3306/testsp?characterEncoding=utf-8"
	 user="username" password="password"/>
<sql:query var="result" dataSource="${snapshot}">SELECT * FROM testuser;</sql:query>

<table border="1" width="100%">
	<tr>
		<th>name</th>
		<th>age</th>
	</tr>
<c:forEach var="row" items="${result.rows}">
	<tr>
		<td><c:out value="${row.name}"/></td>
		<td><c:out value="${row.age}"/></td>
	</tr>
</c:forEach>
</table>
</body>
</html>

数据库插入

在Java Server pages之中，可使用executeUpdate(String sql)方法主要用于执行INSERT\UPDATE的操作，即插入和删除等数据库语句，本次主要使用局部语句进行编写：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<%@ 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" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
	Connection connection = null;	
	Class.forName("com.mysql.jdbc.Driver");
	connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/testsp","root","password");
	
	try {
		Statement stringdata = connection.createStatement();
		String myData = "INSERT INTO testuser(name,age) VALUES('kunkun',10)";
		stringdata.executeUpdate(myData);
		out.print("Yes!");
	} catch(SQLException e) {
		out.print("No!");
	}
%>
</body>
</html>

数据库删除

于数据库插入语句一样，都是使用executeUpdate(String sql)方法来执行INSERT\UPDATE的插入和删除的数据库语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<%@ 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" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
	Connection connection = null;	
	Class.forName("com.mysql.jdbc.Driver");
	connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/testsp","root","password");
	
	try {
		Statement stringdata = connection.createStatement();
		String myData = "DELETE FROM testuser WHERE name = 'kunkun'";
		stringdata.executeUpdate(myData);
		out.print("Yes!");
	} catch(SQLException e) {
		out.print("No!");
	}
%>
</body>
</html>
⬅️ Go back