JAVA高级面试进阶训练营视频教程

admin

当使用jdbc从mysql中查询大量数据时，有可能会导致内存溢出。为了避免这种情况的发生可以对数据库进行分页查询。

 

public static void main(String[] args){
    String url = "jdbc:mysql://localhost:3306/test";
    String username = "username";
    String password = "password";

    int data_num = 0;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection(url, username,
                password);

        String sql = "SELECT ID,NAME FROM table_name limit ?,?";
        PreparedStatement pst = con.prepareStatement(sql);

        int pageSize = 10000;
        int pageId = 0;
        do {
            pst.setInt(1, pageId * pageSize);
            pst.setInt(2, pageSize);
            ResultSet rs = pst.executeQuery();

            boolean isEmpty = true;
            while (rs.next()) {
                 isEmpty = false;
                 id = rs.getLong(1);
                 name = rs.getString(2);
                 data_num++;
            }
            if (isEmpty) {
                break;
            }
            pageId++;
        } while (true);
        con.close();
    } catch (SQLException se) {
        se.printStackTrace();
    }

利用上述的代码可以对数据库表进行遍历处理。