Java web 实现简单的与数据库的交互_javaweb交互读取数据库内容-优快云博客

本文介绍了一个基于JavaWeb的简单数据库交互案例，包括连接SQLServer数据库、查询学生信息及成绩的具体实现过程。通过JSP页面接收查询参数，利用Servlet处理业务逻辑，并在Java类中执行SQL语句获取数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Java web 实现简单的与数据库的交互

连接数据库，编写Conn类，该类是固定的对数据库的连接语句，这里使用的是SQL server数据库

package StudentGradeManage;

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

public class Conn {
	public static Connection getConnection()
	{
		Connection con=null;

		try {
			Class.forName("net.sourceforge.jtds.jdbc.Driver");
			String dbURL="jdbc:jtds:sqlserver://127.0.0.1:1433/StudentGrade";
			String db_User="sa";
			String db_pwd="123456";
			con = DriverManager.getConnection(dbURL, db_User, db_pwd);
		} catch (Exception e) {
	
			e.printStackTrace();
		}
		System.out.println(con);
		return con;		
	}
	
	public static void closeConnection(PreparedStatement ps,Connection con,ResultSet rs)
	{
		
			try {
				if(ps!=null)
				ps.close();
				if(con!=null)
					con.close();
				if(rs!=null)
					rs.close();
			} catch (Exception e) {
			
				e.printStackTrace();
			}			
	}
}

编写search.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>Insert title here</title>
</head>
<body>
<center>
<div>
	<form action="FindServlet" method="get" >	
			<p>姓名：<input type="text" name="sname"></p>
			<p>学号：<input type="text" name="sno"></p>
			<p><input type="submit" value="查询"></p>
	</form>
	<a href="index.jsp">返回</a>
</div>
</center>
</body>
</html>

编写FindServlet

package StudentGradeManage;

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

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


/**
 * Servlet implementation class FindServlet
 */
@WebServlet("/FindServlet")
public class FindServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public FindServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		StudentService studentService=new StudentService();
		List<Student> list=new ArrayList<>();		
		String sname=request.getParameter("sname");
		String sno=request.getParameter("sno");
		list=studentService.getGrade(sname,sno);
		request.setAttribute("list", list);	
		request.getRequestDispatcher("searchGrade.jsp").forward(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

编写StudentService类，在该类中编写getGrade方法

/**
	 * 查询
	 * 先判断如果输入的姓名为空就根据学号查询
	 * 如果姓名不为空，那就默认根据姓名查询，可以模糊查询，也可以精确查询			
	 */
	public List<Student> getGrade(String sname1,String sno1)
	{					
		List<Student> list=new ArrayList<>();										
		Connection con=Conn.getConnection();
		PreparedStatement ps=null;
		String sql="";
		if(sname1=="")
		{
			sql="select * from grade where sno='"+sno1+"'";
		}
		else
		{
			String sname2="%"+sname1+"%";
			sql="select * from grade where sname like '"+sname2+"'";
		}
		try {
			ps = con.prepareStatement(sql);
			System.out.println(sql);
//			ps.setString(1, sno1);
			ResultSet rs = ps.executeQuery();
											
			while(rs.next())
			{
				int id=rs.getInt(1);
				String sno=rs.getString(2);
				String sname=rs.getString(3);
				String cno=rs.getString(4);
				String cname=rs.getString(5);
				int grade=rs.getInt(6);

				Student student=new Student();
				student.setId(id);
				student.setSno(sno);
				student.setSname(sname);
				student.setCno(cno);
				student.setCname(cname);
				student.setGrade(grade);											
				list.add(student);												
			}
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}																								
		return list;					
	}

编写searchGrade.jsp，用于显示查询到的数据

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.*"  %>    
<%@ page import="StudentGradeManage.Student"  %>
<%@ page import="StudentGradeManage.ToolsBean" %>
<!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>Insert title here</title>
</head>
<body>
	<table  border="1">
		<tr>
			<th>学号</th>
			<th>姓名</th>
			<th>课程号</th>
			<th>课程名</th>
			<th>成绩</th>
			<th>操作</th>
		</tr>	
		<%
			int sum=0;
			double avg=0;
			List<Student> list=(List<Student>) request.getAttribute("list");
			if(list==null||list.size()<1)
			{
				out.print("<tr><td>没有成绩信息</td></tr>");	
			}
			else
			{
				for(int i=0;i<list.size();i++)
				{
					sum=sum+list.get(i).getGrade();
		%>									
		<tr>
			<td><%=ToolsBean.toChinese(list.get(i).getSno()) %></td>
			<td><%=(list.get(i).getSname()) %></td>
			<td><%=ToolsBean.toChinese(list.get(i).getCno()) %></td>
			<td><%=(list.get(i).getCname()) %></td>
			<td><%=list.get(i).getGrade() %></td>
			<td>
				<form action="delServlet" method="post" onsubmit="return check(this);">
					<input type="hidden" name="id" value="<%=list.get(i).getId()%>">
					<input type="hidden" name="sno" value="<%=list.get(i).getSno()%>">
					<input type="submit" value="删除">
				</form>
				
				<form action="findByIdServlet" method="post" onsubmit="return check(this);"s>
					<input type="hidden" name="id" value="<%=list.get(i).getId()%>">
					<input type="hidden" name="sno" value="<%=list.get(i).getSno()%>">
					<input type="submit" value="修改">
				</form>
			</td>
		</tr>
		<% }
			avg=sum/list.size();
		%>
		<%=sum %>
		<%=avg %>
		
		<%
		}
		%>											
	</table>

	<a href="search.jsp">返回</a>
</body>
</html>