JS(jQuery+Ajax)+ResultSet指针移动实现无限滚动效果

清疚 2021-05-16 20:36 459阅读 0赞

**实现效果**

![在这里插入图片描述][20201214115556374.gif_pic_center]  
**项目结构**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1Njc1NDQ5_size_16_color_FFFFFF_t_70_pic_center]  
**代码部分**

**NewInfo.java部分**

package servlet;
    
    import java.io.IOException;
    import java.io.PrintWriter;
    import java.sql.*;
    
    import javax.servlet.ServletException;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    
    import util.SqlServerConnection;
    
    @WebServlet("/NewInfo")
    public class NewInfo extends HttpServlet { 
    	private static final long serialVersionUID = 1L;
           
        public NewInfo() { 
            super();
            // TODO Auto-generated constructor stub
        }
    
    	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 
    		response.setContentType("text/html;charset=utf-8");
    		Connection conn = null;
    		PreparedStatement pst = null;
    		ResultSet rst = null;
    		int row = 10;
    		int start = 0;
    		PrintWriter out = response.getWriter();
    		String startpoint = request.getParameter("start");
    		// 输出数据格式
    		String output = "<tr style='height:100px'><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>";
    		try { 
    			conn = SqlServerConnection.sql_connection();
    			// 要在ResultSet查询结果集中进行指针移动，那么在创建PreparedStatement对象时要加入参数
    			pst = conn.prepareStatement("select * from Product",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
    			rst = pst.executeQuery();
    			// 移动指针指向的位置
    			rst.absolute(Integer.valueOf(startpoint));
    			// 每个请求返回多少行数据(我定义的是10行)
    			while (rst.next() && start < row) { 
    				String a = rst.getString("p_type");
    				String b = rst.getString("p_id");
    				String c = rst.getString("p_name");
    				String d = rst.getString("p_price");
    				String e = rst.getString("p_quantity");
    				String f = rst.getString("p_image");
    				String g = rst.getString("p_description");
    				String h = rst.getString("p_time");
    				out.print(String.format(output, a,b,c,d,e,f,g,h));
    				start++;
    			}
    		} catch (SQLException e) { 
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}finally { 
    			SqlServerConnection.unconnect(rst, pst, conn);
    		}
    	}
    }

**SqlServerConnection.java部分**

package util;
    
    import java.sql.*;
    
    public class SqlServerConnection { 
    	static Connection conn;
    	static String drive = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
    	static String url = "jdbc:sqlserver://localhost:1433;DatabaseName=ShopSystem";
    	static String username = "sa";
    	static String password = "sa";
    	
    // 连接数据库方法
    	public static Connection sql_connection() { 
    		try { 
    // 注册驱动
    			Class.forName(drive);
    		} catch (ClassNotFoundException e) { 
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		
    		try { 
    // 创建连接对象
    			conn = DriverManager.getConnection(url,username,password);
    		} catch (SQLException e) { 
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    			System.out.println("连接失败");
    		}
    		return conn;
    	}
    
    // 关闭连接对象
    	public static void unconnect(ResultSet rst,PreparedStatement pst,Connection conn) { 
    		if (rst != null) { 
    			try { 
    				rst.close();
    			} catch (SQLException e) { 
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    		if (pst != null) { 
    			try { 
    				pst.close();
    			} catch (SQLException e) { 
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    		if (conn != null) { 
    			try { 
    				conn.close();
    			} catch (SQLException e) { 
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    	}
    
    }

**allinfo.jsp部分**

<%@ page import="java.sql.*" %>
    <%@ page language="java" contentType="text/html; charset=UTF-8"
        pageEncoding="UTF-8"%>
    <!DOCTYPE html>
    <html>
    <head>
    <meta charset="UTF-8">
    <title>Insert title here</title>
    </head>
    <body>
    <table border="1" id="t1">
    </table>
    <script type="text/javascript" src="jquery-3.5.1.js"></script>
    <script type="text/javascript"> var increase = 0; // 获取dom对象 table var table = $("#t1"); // 初始加载页 因为显示完第一页才有滚动条 所以从第二页开始使用事件 if(increase == 0){ // 使用jQuery封装的Ajax $.ajax({ url:"NewInfo", data:{ "start":increase}, // 获取到返回的数据后执行的函数 将获取的数据作为子标签添加到table中 success:function(rvalue){ table.append(rvalue); } }) } // 当有滚动条后开始使用滚动事件 滚动条滚动就会触发事件 $(window).scroll( function() { // 获取隐藏高度 var scrollTop = $(this).scrollTop(); // 获取总高度 var scrollHeight = $(document).height(); // 获取视窗高度 var windowHeight = $(this).height(); // 当隐藏高度+视窗高度=总高度时 说明滚动条到达最低端 在滚动条到达最低端时请求下一组数据 if (scrollTop + windowHeight == scrollHeight) { console.log(scrollTop + " " + scrollHeight + " " + windowHeight); increase = 0; // 获取当前页面中tr元素的个数(也就是多少行数据)作为下次指针指向的位置 $("tr").each(function(index,element){ increase++; }) // 滚动条到达最低端时触发的事件 使用Ajax发送请求获取下一组数据 $.ajax({ url:"NewInfo", data:{ "start":increase}, datatype: "text/html;charset=utf-8", success:function(rvalue){ table.append(rvalue); } }) } }); </script>
    </body>
    </html>

[20201214115556374.gif_pic_center]: /images/20210516/90ce6188f1d647deae91893e696ba4e1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1Njc1NDQ5_size_16_color_FFFFFF_t_70_pic_center]: /images/20210516/4ca67e9acf364ceaae13311cfa3fbcc8.png