jsp中使用Servlet查詢SQLSERVER資料庫中的表的資訊，并且列印在螢幕上
1、JavaBean的使用
package com.zheng;
public class BookBean {
private int id;// 編号
private String name;// 圖書名稱
private double price;// 定價
private int bookCount;// 數量
private String author;// 作者
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public double getPrice() {
return price;
}
public void setPrice(double price) {
this.price = price;
}
public int getBookCount() {
return bookCount;
}
public void setBookCount(int bookCount) {
this.bookCount = bookCount;
}
public String getAuthor() {
return author;
}
public void setAuthor(String author) {
this.author = author;
}
} Servlet的編寫（連接配接資料庫并且查詢）
package com.zheng;
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.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet實作類FindServlet
*/
@WebServlet("/FindServlet") //配置Servlet
public class FindServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
public FindServlet() {
super();
}
/**
* 執行POST請求的方法
*/
protected void doPostt(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doGet(request,response);
}
/**
* 執行GET請求的方法
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // 加載資料庫驅動，注冊到驅動管理器
String url = "jdbc:sqlserver://localhost:1433;databaseName=test";// 資料庫連接配接字元串
String username = "zheng"; // 資料庫使用者名
String password = "123"; // 資料庫密碼
// 建立Connection連接配接
Connection conn = DriverManager.getConnection(url,username,password);
Statement stmt = conn.createStatement(); // 擷取Statement
String sql = "select * from tb_book2"; // 添加圖書資訊的SQL語句
ResultSet rs = stmt.executeQuery(sql); // 執行查詢
List<BookBean> list = new ArrayList<>(); // 執行個體化List對象
while(rs.next()){ // 光标向後移動，并判斷是否有效
BookBean book = new BookBean(); // 執行個體化Book對象
book.setId(rs.getInt("id")); // 對id屬性指派
book.setName(rs.getString("name")); // 對name屬性指派
book.setPrice(rs.getDouble("price")); // 對price屬性指派
book.setBookCount(rs.getInt("bookCount")); // 對bookCount屬性指派
book.setAuthor(rs.getString("author")); // 對author屬性指派
list.add(book); // 将圖書對象添加到集合中
}
request.setAttribute("list", list); // 将圖書集合放置到request中
rs.close(); // 關閉ResultSet
stmt.close(); // 關閉Statement
conn.close(); // 關閉Connection
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
// 請求轉發到bookList.jsp
request.getRequestDispatcher("bookList.jsp").forward(request, response);
}
}
輸出資料庫中表的資訊
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.util.*"%>
<%@ page import="com.zheng.BookBean"%>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>顯示圖書清單</title>
<style type="text/css">
td,th {
padding: 5px;
}
</style>
</head>
<body>
<div width="98%" align="center">
<h2>所有圖書資訊</h2>
</div>
<table width="98%" border="0" align="center" cellpadding="0"
cellspacing="1" bgcolor="#666666">
<tr>
<th bgcolor="#FFFFFF">編号</th>
<th bgcolor="#FFFFFF">圖書名稱</th>
<th bgcolor="#FFFFFF">價格</th>
<th bgcolor="#FFFFFF">數量</th>
<th bgcolor="#FFFFFF">作者</th>
</tr>
<%
// 擷取圖書資訊集合
List<BookBean> list = (List<BookBean>) request.getAttribute("list");
// 判斷集合是否有效
if (list == null || list.size() < 1) {
out.print("<tr><td bgcolor='#FFFFFF' colspan='5'>沒有任何圖書資訊！</td></tr>");
} else {
// 周遊圖書集合中的資料
for (BookBean book : list) {
%>
<tr align="center">
<td bgcolor="#FFFFFF" ><%=book.getId()%></td>
<td bgcolor="#FFFFFF"><%=book.getName()%></td>
<td bgcolor="#FFFFFF"><%=book.getPrice()%></td>
<td bgcolor="#FFFFFF"><%=book.getBookCount()%></td>
<td bgcolor="#FFFFFF"><%=book.getAuthor()%></td>
</tr>
<%
}
}
%>
</table>
</body>
</html> 超連結跳轉
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="utf-8">
<title>首頁</title>
</head>
<body>
<a href="FindServlet">檢視圖書清單</a>
</body>
</html> ![Java String.format方法的簡單使用[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)