1 5 package org.h2.test.cases; 6 7 import java.sql.Connection ; 8 import java.sql.DriverManager ; 9 import java.sql.PreparedStatement ; 10 import java.sql.ResultSet ; 11 import java.sql.SQLException ; 12 import java.sql.Statement ; 13 14 public class TestLimitOffset { 15 16 public static void main(String [] args) throws Exception { 17 Class.forName("org.h2.Driver"); 18 Connection conn = DriverManager.getConnection("jdbc:h2:test", "sa", "sa"); 19 Statement stat = conn.createStatement(); 20 try { 21 stat.execute("DROP TABLE file"); 22 } catch (SQLException e) { 23 } 25 stat.execute("create table IF NOT EXISTS file (id IDENTITY primary key, path nvarchar(1000) not null, name nvarchar(100), date timestamp not null, size int , is_dir char(1) not null, unique (path))"); 26 PreparedStatement prep = conn.prepareStatement(" insert into file (path, is_dir, date) values(?, 'N', NOW()+? )"); 27 for(int i=0; i<60000; i++) { 28 prep.setInt(1, i); 29 prep.setInt(2, i); 30 prep.execute(); 31 } 32 ResultSet rs; 33 prep = conn.prepareStatement("select path from file where path like ? and is_dir <> 'Y' order by date desc LIMIT 25 OFFSET 0"); 34 prep.setString(1, "%"); 35 rs = prep.executeQuery(); 36 rs.next(); 37 System.out.println(rs.getString(1)); 38 prep = conn.prepareStatement("select path from file where path like ? and is_dir <> 'Y' order by date desc LIMIT 25 OFFSET 25"); 39 prep.setString(1, "%"); 40 rs = prep.executeQuery(); 41 rs.next(); 42 System.out.println(rs.getString(1)); 43 prep = conn.prepareStatement("select path from file where path like ? and is_dir <> 'Y' order by date desc LIMIT 25 OFFSET 50"); 44 prep.setString(1, "%"); 45 rs = prep.executeQuery(); 46 rs.next(); 47 System.out.println(rs.getString(1)); 48 conn.close(); 49 } 50 } 51 | Popular Tags |