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

一时失言乱红尘 2024-02-17 21:17 41阅读 0赞

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

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

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

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

数据库中的数据：

![Center][]

读取的数据：

![Center 1][]

具体如下：

一：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 stub
    		List<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 stub
    		doGet(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" οnsubmit="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>

[Center]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/9bb9f49092fb4bc79cdb5798ef46f725.png
[Center 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/537dec4123b44a6893d290defac17448.png