java servlet jdbc 连接sqlserver数据库查询和修改

效果图：

2、Java类

package shop.hh;public class Book {// 编号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;}}

package shop.hh;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;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.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;public class FindServlet extends HttpServlet {private static final long serialVersionUID = 1L;protected void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {try {// 加载数据库驱动，注册到驱动管理器Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");// 数据库连接字符串String url = "jdbc:sqlserver://DESKTOP-EO1BDL6;DatabaseName=hh";// 数据库用户名String username = "sa";// 数据库密码String password = "sa@12345";// 创建Connection连接Connection conn = DriverManager.getConnection(url, username, password);// 获取StatementStatement stmt = conn.createStatement();// 添加图书信息的SQL语句String sql = "select * from tb_books";// 执行查询ResultSet rs = stmt.executeQuery(sql);// 实例化List对象List<Book> list = new ArrayList<Book>();// 判断光标向后移动，并判断是否有效while (rs.next()) {// 实例化Book对象Book book = new Book();// 对id属性赋值book.setId(rs.getInt("id"));// 对name属性赋值book.setName(rs.getString("name"));// 对price属性赋值book.setPrice(rs.getDouble("price"));// 对bookCount属性赋值book.setBookCount(rs.getInt("bookCount"));// 对author属性赋值book.setAuthor(rs.getString("author"));// 将图书对象添加到集合中list.add(book);}// 将图书集合放置到request之中request.setAttribute("list", list);rs.close(); // 关闭ResultSetstmt.close(); // 关闭Statementconn.close(); // 关闭Connection} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}// 请求转发到book_list.jsprequest.getRequestDispatcher("book_list.jsp").forward(request, response);}protected void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {int id = Integer.valueOf(request.getParameter("id"));int bookCount=Integer.valueOf(request.getParameter("bookCount"));try {// 加载数据库驱动，注册到驱动管理器Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");// 数据库连接字符串String url = "jdbc:sqlserver://DESKTOP-EO1BDL6;DatabaseName=hh";// 数据库用户名String username = "sa";// 数据库密码String password = "sa@12345";Connection conn = DriverManager.getConnection(url, username, password);String sql="update tb_book set bookcount=? where id=?";PreparedStatement ps=conn.prepareStatement(sql);ps.setInt(1, bookCount);ps.setInt(2, id);ps.executeUpdate();ps.close();conn.close();} catch (Exception e) {e.printStackTrace();}response.sendRedirect("FindServlet");}}

package shop.hh;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;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.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;public class UpdateServlet extends HttpServlet {private static final long serialVersionUID = 1L;protected void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {try {// 加载数据库驱动，注册到驱动管理器Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");// 数据库连接字符串String url = "jdbc:sqlserver://DESKTOP-EO1BDL6;DatabaseName=hh";// 数据库用户名String username = "sa";// 数据库密码String password = "sa@12345";// 创建Connection连接Connection conn = DriverManager.getConnection(url, username, password);// 获取StatementStatement stmt = conn.createStatement();// 添加图书信息的SQL语句String sql = "select * from tb_books";// 执行查询ResultSet rs = stmt.executeQuery(sql);// 实例化List对象List<Book> list = new ArrayList<Book>();// 判断光标向后移动，并判断是否有效while (rs.next()) {// 实例化Book对象Book book = new Book();// 对id属性赋值book.setId(rs.getInt("id"));// 对name属性赋值book.setName(rs.getString("name"));// 对price属性赋值book.setPrice(rs.getDouble("price"));// 对bookCount属性赋值book.setBookCount(rs.getInt("bookCount"));// 对author属性赋值book.setAuthor(rs.getString("author"));// 将图书对象添加到集合中list.add(book);}// 将图书集合放置到request之中request.setAttribute("list", list);rs.close(); // 关闭ResultSetstmt.close(); // 关闭Statementconn.close(); // 关闭Connection} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}// 请求转发到book_list.jsprequest.getRequestDispatcher("book_list.jsp").forward(request, response);}protected void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {int id = Integer.valueOf(request.getParameter("id"));int bookCount=Integer.valueOf(request.getParameter("bookCount"));try {// 加载数据库驱动，注册到驱动管理器Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");// 数据库连接字符串String url = "jdbc:sqlserver://DESKTOP-EO1BDL6;DatabaseName=hh";// 数据库用户名String username = "sa";// 数据库密码String password = "sa@12345";Connection conn = DriverManager.getConnection(url, username, password);String sql="update tb_books set bookcount=? where id=?";PreparedStatement ps=conn.prepareStatement(sql);ps.setInt(1, bookCount);ps.setInt(2, id);ps.executeUpdate();ps.close();conn.close();} catch (Exception e) {e.printStackTrace();}response.sendRedirect("FindServlet");}}

3、 jsp页面

<%@ page language="java" contentType="text/html; charset=utf-8"    pageEncoding="utf-8"%><%@page import="java.util.List"%><%@page import="shop.hh.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=ISO-8859-1"><title>Insert title here</title></head><body><table align="center" width="450" border="1"><tr><td align="center" colspan="5"><h2>所有图书信息</h2></td></tr><tr align="center"><td><b>ID</b></td><td><b>图书名称</b></td><td><b>价格</b></td><td><b>数量</b></td><td><b>作者</b></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.getAuthor() %></td><td><form action="UpdateServlet" method="post" onsubmit="return check(this);"><input type="hidden" name="id" value=<%=book.getId() %>><input type="text" name="bookCount" size="3"><input type="submit" value="修 改"></form></td></tr><%}}%></table></body></html>

<%@ 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=ISO-8859-1"><title>Insert title here</title></head><body><a href="FindServlet">查看所有图书</a></body></html>

4、xml文件

<?xml version="1.0" encoding="UTF-8"?><web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">  <display-name>11.3</display-name>  <welcome-file-list>    <welcome-file>index.html</welcome-file>    <welcome-file>index.htm</welcome-file>    <welcome-file>index.jsp</welcome-file>    <welcome-file>default.html</welcome-file>    <welcome-file>default.htm</welcome-file>    <welcome-file>default.jsp</welcome-file>  </welcome-file-list>  <servlet>    <description></description>    <display-name>FindServlet</display-name>    <servlet-name>FindServlet</servlet-name>    <servlet-class>shop.hh.FindServlet</servlet-class>      <display-name>UpdateServlet</display-name>    <servlet-name>UpdateServlet</servlet-name>    <servlet-class>shop.hh.UpdateServlet</servlet-class>  </servlet>  <servlet-mapping>    <servlet-name>FindServlet</servlet-name>    <url-pattern>/FindServlet</url-pattern>    <servlet-name>UpdateServlet</servlet-name>    <url-pattern>/UpdateServlet</url-pattern>  </servlet-mapping></web-app>

5、需要引用servlet包和sqljdbc包