JDBC操作数据库之查询数据库

最新推荐文章于 2023-08-28 11:33:04 发布

转载最新推荐文章于 2023-08-28 11:33:04 发布 · 355 阅读

1、实现客户端请求的服务器的业务处理类FindServlet.java

package com.Connection.Test;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

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

import com.Bean.Book;


/**
 * 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 {
		this.doPost(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/test";
			String username = "root";
			String password = "12345";
			Connection conn = DriverManager.getConnection(url, username, password);

			String sql = "select * from books";
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			List<Book> list= new ArrayList<Book>();
			while(rs.next()) {
				Book book = new Book();
				book.setId(rs.getInt("id"));
				book.setName(rs.getString("bookname"));
				book.setPrice(rs.getDouble("price"));
				book.setBookCount(rs.getInt("bookCount"));
				book.setAuth(rs.getString("author"));
				list.add(book);
			}
			request.setAttribute("list", list);
			rs.close();
			stmt.close();
			conn.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		request.getRequestDispatcher("showBook.jsp").forward(request, response);
	}

}

2、创建showBook.jsp页面，显示数据库中对应表的所有图书信息。

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.List" %>
<%@ page import="com.Bean.Book" %>
<!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>
<table align="center" border="1">
	<caption>所有图书信息</caption>
	<tr align="center">
		<td>ID</td>
		<td>图书名称</td>
		<td>价格</td>
		<td>数量</td>
		<td>作者</td>
	</tr>
	<%
		List<Book> list = (List<Book>)request.getAttribute("list");
		if(list==null||list.size()<1){
			out.print("没有数据！");
		}else{
			for(Book book:list){
	%>
	<tr align="center">
		<td><%=book.getId() %></td>
		<td><%=book.getName() %></td>
		<td><%=book.getPrice() %></td>
		<td><%=book.getBookCount() %></td>
		<td><%=book.getAuth() %></td>
	</tr>
	<%
			}
		}
	%>
</table>
</body>
</html>