jsp+jdbc+servlet实现对数据库的操作

最新推荐文章于 2022-05-11 16:18:10 发布

spicyChickenJZ

最新推荐文章于 2022-05-11 16:18:10 发布

阅读量2.1k

点赞数 9

文章标签： jdbc servlet jsp 数据库 jsp页面传递数据

本文链接：https://blog.csdn.net/yjjjjz/article/details/84565357

版权

环境配置

tomcat+MySQL

如果还没有配置好的可以通过以下链接来配置：

tomcat：https://www.cnblogs.com/gifted35/p/9775273.html

MySQL：https://www.cnblogs.com/gifted35/p/9775689.html

在命令行或者navicat中新建一个学生表student

表中有3个属性：ID(String), name(String), age(int)

在目录 WebContent/WEB-INF/lib 下粘贴 jdbc的jar包mysql-connector-java-5.1.9.jar

（如果出现报错可能是版本和MySQL的版本不符）

jsp页面

addStudnet.jsp

用来输入要操作的数据

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>添加学生</title>
</head>
<body>
	<form action="servletInsert" method="POST">
		<table>
			<tr>
				<td>请输入要添加的学生的ID</td>
				<td> <input name="ID" id="ID" type="text" maxlength="20"/> </td>
				<td>姓名</td>
				<td> <input name="name" id="name" type="text" maxlength="20"/> </td>
				<td>年龄</td>
				<td> <input name="age" id="age" type="text" maxlength="20"/> </td>
				<td>
					<input name="Submit" type="submit" value="添加" onclick=getParameter>
				</td>
			</tr>
		</table>
	</form>
	
	<form action="servletSelect" method="POST">
		请输入要查询的学号:
		<input name="ID" id="ID" type="text" maxlength="20"/>
		<input name="submit" type="submit" value="查询">
	</form>
	
	<form action="servletDelete" method="POST">
		请输入要删除的学生的姓名:
		<input name="name" id="name" type="text" maxlength="20"/>
		<input name="submit" type="submit" value="删除">
	</form>
</body>
</html>

result.jsp

用来展示查询操作的结果

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@page import="jdbcTest.*"%>
<%@page import="java.util.ArrayList" %>
<%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<base href="<%=basePath%>">
<title>查询结果</title>
</head>
<body>
	<%
		ArrayList list = (ArrayList) request.getAttribute("list");
	%>
	<table>
		<tr>
			<td>学号</td>
			<td>姓名</td>
			<td>年龄</td>
		</tr>
		<% 
			for(int i=0;i<list.size();i++)
			{
				Student stu = (Student) list.get(i);
			
		%>
		<tr>
			<td><%= stu.getID()%></td>
			<td><%= stu.getName()%></td>
			<td><%= stu.getAge()%></td>
		</tr>
		<%
			}
		%>
	</table>
</body>
</html>

jdbc

Student.java

用来保存学生的信息

package jdbcTest;

public class Student {
	public static String id;
	public static String name;
	public static int age;
	
	public String getID()
	{
		return id;
	}
	public String getName()
	{
		return name;
	}
	public int getAge()
	{
		return age;
	}
	void setID(String myID)
	{
		id = myID;
	}
	void setName(String myName)
	{
		name = myName;
	}
	void setAge(int myAge)
	{
		age = myAge;
	}
}

jdbcInsert.java

实现连接数据库并插入数据

package jdbcTest;

import javax.servlet.http.HttpServlet;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class jdbcInsert extends HttpServlet
{
	public static String myID;
	private static String myName;
	private static int myAge;

	void setID(String id)
	{
		myID = id;
	}

	void setName(String name)
	{
		myName = name;
	}

	void setAge(int age)
	{
		myAge = age;
	}

	public static void insert()
	{
		try
		{
			// 加载数据库驱动，注册到驱动管理器
			Class.forName("com.mysql.jdbc.Driver");
			// 数据库连接字符串
			String url = "jdbc:mysql://127.0.0.1:3306/mysql_javaee";
			// 数据库用户名
			String username = "root";
			String password = "7890";
			Connection conn = DriverManager.getConnection(url, username, password);
			if (conn != null)
			{
				// 输出连接信息
				System.out.println("数据库连接成功1！");
				String sql = "INSERT INTO Student VALUE(?,?,?);";
				PreparedStatement ps = conn.prepareStatement(sql);

				ps.setString(1, myID);
				ps.setString(2, myName);
				ps.setInt(3, myAge);

				ps.executeUpdate();

				conn.close();
			} 
			else
			{
				// 输出连接信息
				System.out.println("数据库连接失败！");
			}

		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
}

jdbcSelect.java

实现连接数据库并查找数据

package jdbcTest;


import javax.servlet.http.HttpServlet;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class jdbcSelect extends HttpServlet {
	public static String myID;
	static int flag = 0;
	static ArrayList<Student> list = new ArrayList<Student>();

	void setID(String id)
	{
		myID = id;
	}
	
	//返回一个含有查询结果的列表
	ArrayList<Student> returnList()
	{
		return list;
	}
	
	public static void select() {
		try {
			// 加载数据库驱动，注册到驱动管理器
			Class.forName("com.mysql.jdbc.Driver");
			// 数据库连接字符串
			String url = "jdbc:mysql://127.0.0.1:3306/mysql_javaee";
			// 数据库用户名
			String username = "root";
			String password = "7890";
			Connection conn = DriverManager.getConnection(url, username, password);
			if (conn != null) {
				// 输出连接信息
				System.out.println("数据库连接成功2！");
				String sql = "SELECT * FROM student WHERE id=?;";
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1, myID);
				ResultSet rs = ps.executeQuery();		//不需要加参数
				
				
	            while(rs.next()){
	            	flag = 1;
	                //rs.get+数据库中对应的类型+(数据库中对应的列别名)
	                String id = rs.getString("id");
	                String name = rs.getString("name");
	                int age = rs.getInt("age");
	                Student stu = new Student();
	                stu.setID(id);
	                stu.setName(name);
	                stu.setAge(age);
	                list.add(stu);
	            }
	            
				conn.close();
			} else {
				// 输出连接信息
				System.out.println("数据库连接失败！");
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

jdbcDelete.java

实现连接数据库和删除数据

package jdbcTest;


import javax.servlet.http.HttpServlet;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class jdbcDelect extends HttpServlet
{
	public static String myName;

	void setName(String name)
	{
		myName = name;
	}

	public static void delect()
	{
		try
		{
			// 加载数据库驱动，注册到驱动管理器
			Class.forName("com.mysql.jdbc.Driver");
			// 数据库连接字符串
			String url = "jdbc:mysql://127.0.0.1:3306/mysql_javaee";
			// 数据库用户名
			String username = "root";
			String password = "7890";
			Connection conn = DriverManager.getConnection(url, username, password);
			if (conn != null)
			{
				// 输出连接信息
				System.out.println("数据库连接成功3！");
				//参数化的sql语句，？为参数位置
				String sql = "DELETE FROM student WHERE name=? ;";
				PreparedStatement ps = conn.prepareStatement(sql);
				//设第一个参数为myName
				ps.setString(1, myName);
				//执行sql语句
				int result = ps.executeUpdate(); // 不需要加参数

				conn.close();
			} else
			{
				// 输出连接信息
				System.out.println("数据库连接失败！");
			}

		} catch (ClassNotFoundException e)
		{
			e.printStackTrace();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
	}
}

servlet

servletInsert.java

获取表单servletInsert中的数据

package jdbcTest;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@WebServlet("/servletInsert")
public class servletInsert extends HttpServlet
{
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
	{
		// 设置响应内容类型
		response.setContentType("text/html;charset=UTF-8");
		String myID = new String(request.getParameter("ID").getBytes("ISO8859-1"), "UTF-8");
		String myName = new String(request.getParameter("name").getBytes("ISO8859-1"), "UTF-8");
		String myStrAge = request.getParameter("age");
		// 把 String型 的myStrAge1 转成 int型
		int myAge = Integer.parseInt(myStrAge);
		jdbcInsert test1 = new jdbcInsert();
		test1.setID(myID);
		test1.setName(myName);
		test1.setAge(myAge);
		test1.insert();

	}

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
	{
		doGet(request, response);
	}
}

servletSelect.java

获取表单servletSelect中的数据

package jdbcTest;

import java.io.IOException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


@WebServlet("/servletSelect")
public class servletSelect extends HttpServlet {
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String myID = new String(request.getParameter("ID").getBytes("ISO8859-1"), "UTF-8");
	
		jdbcSelect test = new jdbcSelect();
		test.setID(myID);
		test.select();
		ArrayList<Student> list = test.returnList();

		// 将list数据打包
		request.setAttribute("list", list);
		// 将list数据发送到.jap文件中,并跳转到result页面
		request.getRequestDispatcher("result.jsp").forward(request, response);
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}
}

servletDelete.java

获取表单servletDelete中的数据

package jdbcTest;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

//获取表单delete的数据
@WebServlet("/servletDelete")
public class servletDelete extends HttpServlet
{
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
	{
		// 解决中文乱码问题
		String myName = new String(request.getParameter("name").getBytes("ISO8859-1"), "UTF-8");
		jdbcDelect test = new jdbcDelect();
		test.setName(myName);
		test.delect();
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
	{
		doGet(request, response);
	}
}

配置web.xml文件

查看WebContent/WEB-INF 下有无xml文件，如果没有自己新建一个

在xml文件中加入以下内容，其中路径等改成自己的

  <servlet>
    <servlet-name>servletInsert</servlet-name>
    <servlet-class>jdbcTest.servletInsert</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>sertletInsert</servlet-name>
    <url-pattern>/test3/servletInsert</url-pattern>
  </servlet-mapping>
  
  <servlet>
    <servlet-name>servletSelect</servlet-name>
    <servlet-class>jdbcTest.servletSelect</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>servletSelect</servlet-name>
    <url-pattern>/test3/servletSelect</url-pattern>
  </servlet-mapping>
  
  <servlet>
    <servlet-name>servletDelect</servlet-name>
    <servlet-class>jdbcTest.servletDelect</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>srvletDelect</servlet-name>
    <url-pattern>/test3/servletDelect</url-pattern>
  </servlet-mapping>