数据库中大量数据(GB)的读取

公司中要求从数据库读取GB级别的文件,在CSDN中查询得到了这个用jdbc查询并通过NIO写入本地文件的方法,与大家交流.

如有侵权请作者尽快联系.

public class TestPL {
    public static long importData(String sql) {
        System.out.println("开始");
        long time1 = System.currentTimeMillis();
        String url = "jdbc:mysql://127.0.0.1:3306/testdb?user=root&password=root";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e1) {
            e1.printStackTrace();
        }
        long count = 0;
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            con = DriverManager.getConnection(url);
            ps = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,
                    ResultSet.CONCUR_READ_ONLY);
            ps.setFetchSize(Integer.MIN_VALUE);
            ps.setFetchDirection(ResultSet.FETCH_REVERSE);
            System.out.println("中期");
            rs = ps.executeQuery();
            System.out.println("结束");
            File file = new File("D:\\data.txt");
            FileOutputStream outputStream = null;
            ByteBuffer buffer =null;
            try {
                outputStream = new FileOutputStream(file,true);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            }
            FileChannel channel = outputStream.getChannel();
            while (rs.next()) {
                buffer = ByteBuffer.allocate(1024);
                //业务
                String s1 = rs.getString(1);
                String s2 = rs.getString(2);
                String s3 = rs.getString(3);
                String sth=s1+","+s2+","+s3+"\r\n";
                buffer.put(sth.getBytes());
                //调用buffer的flip方法以从前读取
                buffer.flip();
                try {
                    channel.write(buffer);
                } catch (IOException e) {
                    e.printStackTrace();
                }
                count++;
            }
            try {
                channel.close();
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
            System.out.println("取回数据量为  " + count + " 行！");
            long time2 = System.currentTimeMillis();
            System.out.println("共用时:"+(time2-time1)/1000+"秒.");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return count;
    }

}