jsp中的mysql数据库操作

最新推荐文章于 2024-06-08 12:26:07 发布

易水凌波

最新推荐文章于 2024-06-08 12:26:07 发布

阅读量164

点赞数

分类专栏： javaweb

本文链接：https://blog.csdn.net/qq_42482815/article/details/109392210

版权

javaweb 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

总是Class.forName(“com.mysql.cj.jdbc.Driver”);这一行报错
解决方法：在tomcat目录下的libwen文件夹中导入mysql的jar包，就是复制过去。

一、数据库的查询

<%@ page import="java.sql.*" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <title>表格示例</title>
</head>

<body>

<%
		Connection connection=null;
		Statement statement=null;
		ResultSet resultSet=null;
		PreparedStatement preparedStatement=null;
		Class.forName("com.mysql.cj.jdbc.Driver");
		String url="jdbc:mysql://127.0.0.1:3306?serverTimezone=UTC";
		String user="root";
		String password="123456";
		connection=DriverManager.getConnection(url, user, password);
		out.println("成功创建数据库连接");
		statement=connection.createStatement();
		statement.execute("use world");//如果url中指明要访问world这个数据库则不用use.
		//String url="jdbc:mysql://127.0.0.1:3306/world?serverTimezone=UTC";
		String str="select * from city";
		resultSet=statement.executeQuery(str);
%>

<h3>城市</h3>
<table border="1">
    <tr>
        <td colspan="1" align="center">ID</td>
        <td colspan="1" align="center">Name</td>
        <td colspan="1" align="center">CountryCode</td>
        <td colspan="1" align="center">District</td>
        <td colspan="1" align="center">Population</td>
    </tr>
    <%
    int i=1;
    while(resultSet.next()){
    	
    %>
    <tr>
    	<td><%=resultSet.getInt(1) %></td>
    	<td><%=resultSet.getString(2) %></td>
    	<td><%=resultSet.getString(3) %></td>
    	<td><%=resultSet.getString(4) %></td>
    	<td><%=resultSet.getInt(5) %></td>
    	<%} %>
    </tr>
</table>

</body>
</html>
</body>
</html>

二、数据库的插入

插入的重点在于html表单中的数据要如何传递到java处理代码中去。
index.jsp

<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <title>表格示例</title>
</head>

<body>

<form action="insert.jsp">
	输入ID：<input type="text" name="ID"><br>
	输入Name：<input type="text" name="Name"><br>
	输入CountryCode：<input type="CountryCode" name="CountryCode"><br>
	输入District：<input type="text" name="District"><br>
	输入Population：<input type="Population" name="Population"><br>
	<input type="submit" value="提交">
</form>


<%
		Connection connection=null;
		Statement statement=null;
		ResultSet resultSet=null;
		PreparedStatement preparedStatement=null;
		Class.forName("com.mysql.cj.jdbc.Driver");
		String url="jdbc:mysql://127.0.0.1:3306?serverTimezone=UTC";
		String user="root";
		String password="197009";
		connection=DriverManager.getConnection(url, user, password);
		out.println("成功创建数据库连接");
		statement=connection.createStatement();
		statement.execute("use world2");
		String str="select * from city";
		resultSet=statement.executeQuery(str);
%>

<h3>城市</h3>
<table border="1">
    <tr>
        <td colspan="1" align="center">ID</td>
        <td colspan="1" align="center">Name</td>
        <td colspan="1" align="center">CountryCode</td>
        <td colspan="1" align="center">District</td>
        <td colspan="1" align="center">Population</td>
    </tr>
    <%
    int i=1;
    while(resultSet.next()){
    	
    %>
    <tr>
    	<td><%=resultSet.getInt(1) %></td>
    	<td><%=resultSet.getString(2) %></td>
    	<td><%=resultSet.getString(3) %></td>
    	<td><%=resultSet.getString(4) %></td>
    	<td><%=resultSet.getInt(5) %></td>
    	<%} %>
    </tr>
</table>

</body>
</html>
</body>
</html>

insert.jsp

<%@ page import="java.sql.*" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%
	Connection connection=null;
	Statement statement=null;
	ResultSet resultSet=null;
	PreparedStatement preparedStatement=null;
	Class.forName("com.mysql.cj.jdbc.Driver");
	String url="jdbc:mysql://127.0.0.1:3306/world2?serverTimezone=UTC";
	String user="root";
	String password="197009";
	connection=DriverManager.getConnection(url, user, password);
	out.println("成功创建数据库连接");
	int ID=Integer.parseInt(request.getParameter("ID"));
	String Name=request.getParameter("Name");
	String CountryCode=request.getParameter("CountryCode");
	String District=request.getParameter("District");
	int Population=Integer.parseInt(request.getParameter("Population"));
	String str="insert into city values(?,?,?,?,?)";
				preparedStatement=connection.prepareStatement(str);
				preparedStatement.setInt(1,ID);
				preparedStatement.setString(2,Name);
				preparedStatement.setString(3,CountryCode);
				preparedStatement.setString(4,District);
				preparedStatement.setInt(5,Population);
				preparedStatement.execute();
	
	out.print("成功添加数据");
	%>
</body>
</html>