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>
查询结果
