从jsp页面中连接数据库并进行相关操作（增删查改）

来源：互联网发布：社交网络可视化编辑：程序博客网时间：2024/05/21 22:44

1、在servlet中处理读取数据库中的内容；

2、编写一个java类封装对数据库的加载、连接以及各种查询、更新方法；

3、编写一个javaBean 其参数对应于数据库中的列名

4、servlet中读取数据完成后，交于jsp界面输出

数据库中的数据：

读取的数据：

具体如下：

一：java类，封装对数据库的加载、连接以及各种查询、更新方法；

package Tools;import java.io.InputStream;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.Properties;public class Tools {private static String driver = null;private static String url = null;private static String password = null;private static String user = null;private Connection con=null;private Statement state=null;private PreparedStatement prestate=null;private ResultSet rs=null;//加载驱动static{try {driver="com.microsoft.sqlserver.jdbc.SQLServerDriver";url="jdbc:sqlserver://127.0.0.1:1433;DatabaseName=book";password="123456";user="sa";Class.forName(driver).newInstance();} catch (Exception e) {e.printStackTrace();}}//创建连接public static Connection getConnection(){try {return DriverManager.getConnection(url, user, password);} catch (SQLException e) {e.printStackTrace();return null;}}//返回查询结果public  ResultSet getExecuteQuery(String sql) throws SQLException{  con = Tools.getConnection();  state = con.createStatement();  rs = state.executeQuery(sql);  return rs;}//返回执行(update delete add)public  int getExecuteUpdate(String sql) throws SQLException{  con = Tools.getConnection();  state = con.createStatement();  int num = state.executeUpdate(sql);  return num;}//释放资源public static void release(ResultSet rs,Statement sm, Connection con){if (rs!=null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}rs=null;}if (sm!=null) {try {sm.close();} catch (SQLException e) {e.printStackTrace();}}if (con!=null) {try {con.close();} catch (SQLException e) {e.printStackTrace();}}}public  void release(){if (rs!=null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}rs=null;}if (state!=null) {try {state.close();} catch (SQLException e) {e.printStackTrace();}}if (con!=null) {try {con.close();} catch (SQLException e) {e.printStackTrace();}}}public static void release(ResultSet rs,PreparedStatement psm, Connection con){if (rs!=null) {try {rs.close();} catch (SQLException e) {e.printStackTrace();}rs=null;}if (psm!=null) {try {psm.close();} catch (SQLException e) {e.printStackTrace();}}if (con!=null) {try {con.close();} catch (SQLException e) {e.printStackTrace();}}}}

二：javaBean 其参数对应于数据库中的列名

package beans;public class bookbean {   private int id;   private String name;   private float price;   private String author;   private int bookCount;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 float getPrice() {return price;}public void setPrice(float price) {this.price = price;}public String getAuthor() {return author;}public void setAuthor(String author) {this.author = author;}public int getBookCount() {return bookCount;}public void setBookCount(int bookCount) {this.bookCount = bookCount;}   }

三：开始的一个jsp界面，只有一个功能，实现跳转到servlet处理数据

<%@ 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>主界面</title></head><body><%RequestDispatcher rd =request.getRequestDispatcher("Page_go");  //跳转页面rd.forward(request, response);%></body></html>

四：servlet ，给出处理数据的代码

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {// TODO Auto-generated method stubList<bookbean> list= GetData("select * from bookStore");    request.setAttribute("list", list);    request.getRequestDispatcher("jdbc.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 stubdoGet(request, response);}private List<bookbean> GetData(String sql){List<bookbean> list = new ArrayList<bookbean>();   Tools t =  new Tools();   try   {         ResultSet rs = t.getExecuteQuery(sql);     while(rs.next())     {     bookbean l = new bookbean();      l.setId(rs.getInt("id"));     l.setAuthor(rs.getString("author"));     l.setBookCount(rs.getInt("bookCount"));     l.setName(rs.getString("name"));     l.setPrice(rs.getFloat("price"));     list.add(l);     }     t.release();     return list;      }catch(Exception e)   {      t.release();      e.printStackTrace();      return null;   }}

五：最后的输出jsp界面

<%@ page language="java" contentType="text/html; charset=UTF-8"    pageEncoding="UTF-8"%><%@page import ="java.sql.*,beans.*,java.util.*" %><!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 width="98%" border="0" align="center" cellpadding="0"cellspacing="1" bgcolor="#666666"><tr><th bgcolor="#FFF000">图书名称</th><th bgcolor="#FFF000">价格</th><th bgcolor="#FFF000">数量</th><th bgcolor="#FFF000">作者</th><th bgcolor="#FFF000">修改数量</th><th bgcolor="#FFF000">删除</th></tr>      <%       //获取图书集合      List<bookbean> list= (List<bookbean>)request.getAttribute("list");      //判断集合是否有效      if(list==null || list.size()<1){      out.print("<tr><td bgcolor='#FFFFF'>没有任何图书信息！</td></tr>");      }      else {      for(bookbean b:list){      %>  <tr align="center"><td bgcolor="#FFFFFF"><%=b.getName() %></td><td bgcolor="#FFFFFF"><%=b.getPrice() %></td><td bgcolor="#FFFFFF"><%=b.getBookCount() %></td><td bgcolor="#FFFFFF"><%=b.getAuthor() %></td><td bgcolor="#FFFFFF"><form action="Update.jsp" method="post" onsubmit="return check(this);">        <input type="hidden" name="id" value="<%=b.getId()%>">        <input type="text" name="bookCount" size="3">        <input type="submit" value="修　改">        </form>         </td>         <td bgcolor="#FFFFFF"><form action="Delete.jsp" method="post" >        <input type="hidden" name="id" value="<%=b.getId()%>">        <input type="submit" value="删   除">         </form>        </td>      </form></td></tr>  <%  } }  %></table><div width="98%" align="center" style="padding-top:10px;"></div></body></html>

2 0