JSP中连接数据库MySQL

一、软件环境

下载并安装MySQL，Tomacat，JDBC、IDEA或其他IDE，本文使用IDEA

二、环境配置

将其环境变量配置好之后，下载Java 专用的连接MySQL的驱动包JDBC。
下载链接：https://dev.mysql.com/downloads/connector/j/

在这里插入图片描述
我准备好的：https://wwz.lanzouo.com/iOysf218491g 密码:1up0
下载好后解压，找到mysql-connector-j-8.x.xx-bin.jar文件，将其拷贝到Tomcat的安装lib目录下。

三、JSP连接MySQL

在IDEA中建立数据库School，建立表score，下面是建表语句。

drop table if exists score;
create table score (
    student_id int primary key,
    name varchar(20),
    class varchar(20),
    grade int
);
INSERT INTO score (student_id, name, class, grade) VALUES (1, '张三', '数学', 85);
INSERT INTO score (student_id, name, class, grade) VALUES (2, '徐华', '数学', 80);
INSERT INTO score (student_id, name, class, grade) VALUES (3, '李四', '英语', 90);
INSERT INTO score (student_id, name, class, grade) VALUES (4, '王五', '化学', 90);
INSERT INTO score (student_id, name, class, grade) VALUES (5, '王一', '语文', 80);
INSERT INTO score (student_id, name, class, grade) VALUES (6, '黄虎', '物理', 80);
INSERT INTO score (student_id, name, class, grade) VALUES (7, '黄虎', '英语', 90);

现在就是尝试用jsp连接mysql了。
建立测试页面test.jsp，下面代码中
conn=DriverManager.getConnection(“jdbc:mysql://localhost:3306/your_database”, “username”, “password”);里面需要填自己MySQL中对应的数据库名，用户名，密码

<%@ page import="java.sql.*" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
	<title>test</title>
</head>
<body>
<%
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	try {
		Class.forName("com.mysql.jdbc.Driver");
		conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database", "username", "password");
		stmt = conn.createStatement();
		
		String sql = "SELECT * FROM score ";
		rs = stmt.executeQuery(sql);
		if(!rs.isBeforeFirst()){
			out.println("没有查询到数据");
            return;
		}
		while (rs.next()) {
			out.println("学号: " + rs.getInt("student_id") + "<br>");
			out.println("姓名: " + rs.getString("name") + "<br>");
			out.println("课程名: " + rs.getString("class") + "<br>");
			out.println("成绩: " + rs.getInt("grade") + "<br>");
			out.println("<hr>");
		}
	} catch (Exception e) {
		out.println("Error: " + e.getMessage());
	} finally {
		if (rs != null) try { rs.close(); } catch (SQLException ignore) {}
		if (stmt != null) try { stmt.close(); } catch (SQLException ignore) {}
		if (conn != null) try { conn.close(); } catch (SQLException ignore) {}
	}
%>
</body>
</html>