INSERT INTO DUFUY."user"
(ID, CUSTOMER_NO, CREATEDATE, NAME1, NAME2, NAME3, NAME4, NAME5, COUNTRY)
VALUES(1, '', '', '', '', '', '', '', '');
方式一:
SELECT * FROM (
SELECT ROW_NUMBER() over(ORDER BY id asc) AS row_number, ID, CUSTOMER_NO, CREATEDATE
FROM DUFUY."user"
--WHERE COUNTRY = 'HK'
--AND CREATEDATE >='2023-06-11 00:00:00' AND CREATEDATE <= '2023-06-11 23:59:59'
) t
WHERE t.ROW_NUMBER > 0 AND t.ROW_NUMBER <= 10
方式2
SELECT * FROM (
SELECT ROW_NUMBER() over(ORDER BY id asc) AS row_number, ID, CUSTOMER_NO, CREATEDATE
FROM DUFUY."user"
--WHERE COUNTRY = 'HK'
--AND CREATEDATE >='2023-06-11 00:00:00' AND CREATEDATE <= '2023-06-11 23:59:59'
) t
WHERE t.ROW_NUMBER > 0
FETCH FIRST 10 ROWS ONLY
package spring.batch.controller; import java.sql.*; import java.util.ArrayList; import java.util.Date; import java.util.List; public class Test1 { public static void main(String[] args) { // insertDB(); findListByPage(1,10).stream().forEach((u) -> System.out.println(u.getId()+", "+ u.getCustomerNo())); findListByPage2(1,10).stream().forEach((u) -> System.out.println(u.getId()+", "+ u.getCustomerNo())); } public static void insertDB() { String insertSql = "INSERT INTO DUFUY.\"user\" (ID, CUSTOMER_NO, CREATEDATE, NAME1, NAME2, NAME3, NAME4, NAME5, COUNTRY) VALUES(?,?,?,?,?,?,?,?,?)"; Connection con = null; PreparedStatement ps = null; try { con = getConnection(); ps = con.prepareStatement(insertSql); con.setAutoCommit(false); for (int i=51;i<=51;i++) { ps.setLong(1, i); ps.setString(2, 1000000+i+""); Date date = new Date(); Timestamp ts=new Timestamp(date.getTime()); ps.setTimestamp(3, ts); ps.setString(4, "测试数据看性能NAME1:"+i); ps.setString(5, "测试数据看性能NAME2:"+i); ps.setString(6, "测试数据看性能NAME3:"+i); ps.setString(7, "测试数据看性能NAME4:"+i); ps.setString(8, "测试数据看性能NAME5:"+i); if (i%5==0){ ps.setString(9, "SG"); } else { ps.setString(9, "HK"); } ps.addBatch(); } ps.executeBatch(); con.commit(); System.out.println("finished"); } catch (SQLException e) { e.printStackTrace(); } finally { if(ps != null){ try { ps.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(con != null){ try { con.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } } public static List<User> findListByPage(int pageNo, int pageSize) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; List<User> list = new ArrayList<>(); int count = 0; int tes = 2147483647; System.out.println(tes); try { String countSql = "SELECT count(*) count FROM ("+ " SELECT ROW_NUMBER() over(ORDER BY Id asc) AS row_number, ID, CUSTOMER_NO, CREATEDATE"+ " FROM DUFUY.\"user\""+ " WHERE 1=1"+ " AND COUNTRY = 'HK'"+ " AND CREATEDATE >='2023-06-11 00:00:00' AND CREATEDATE <= '2023-06-11 23:59:59'"+ ") t"; conn = getConnection(); ps = conn.prepareStatement(countSql); rs = ps.executeQuery(); if (rs.next()) { count = rs.getInt("count"); } System.out.println("总条数:" + count); int totalPageSize = count % pageSize == 0 ? count / pageSize : count / pageSize + 1; System.out.println("总页数:" + totalPageSize); // 页数最小为1 if (pageNo < 1) { pageNo = 1; } // 传入的页数大于总页数 if (pageNo > totalPageSize) { pageNo = totalPageSize; } int beginIndex = (pageNo - 1) * pageSize; int endIndex = pageNo * pageSize; String sql = "SELECT * FROM ("+ " SELECT ROW_NUMBER() over(ORDER BY Id asc) AS row_number, ID, CUSTOMER_NO, CREATEDATE"+ " FROM DUFUY.\"user\""+ " WHERE 1=1"+ " AND COUNTRY = 'HK'"+ " AND CREATEDATE >='2023-06-11 00:00:00' AND CREATEDATE <= '2023-06-11 23:59:59'"+ ") t "+ // WHERE t.ROW_NUMBER > 30 AND t.ROW_NUMBER <= 40 " WHERE t.ROW_NUMBER > ? AND t.ROW_NUMBER <= ?"; System.out.println("exe sql: "+ sql); ps = conn.prepareStatement(sql); System.out.println("beginIndex: " + beginIndex + ", endIndex: " + endIndex); ps.setInt(1, beginIndex); ps.setInt(2, endIndex); rs = ps.executeQuery(); while (rs.next()) { User user = new User(); user.setId(rs.getLong("ID")); user.setCustomerNo(rs.getString("CUSTOMER_NO")); user.setCreateDate(rs.getString("CREATEDATE")); list.add(user); } } catch (Exception e) { e.printStackTrace(); } finally { if(rs != null){ try { rs.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(ps != null){ try { ps.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } return list; } public static List<User> findListByPage2(int pageNo, int pageSize) { Connection conn = null; PreparedStatement ps = null; ResultSet rs = null; List<User> list = new ArrayList<>(); int count = 0; // int tes = 2147483647; // System.out.println(tes); try { String countSql = "SELECT count(*) count FROM ("+ " SELECT ROW_NUMBER() over(ORDER BY Id asc) AS row_number, ID, CUSTOMER_NO, CREATEDATE"+ " FROM DUFUY.\"user\""+ " WHERE 1=1"+ " AND COUNTRY = 'HK'"+ " AND CREATEDATE >='2023-06-11 00:00:00' AND CREATEDATE <= '2023-06-11 23:59:59'"+ ") t"; conn = getConnection(); ps = conn.prepareStatement(countSql); rs = ps.executeQuery(); if (rs.next()) { count = rs.getInt("count"); } System.out.println("总条数:" + count); int totalPageSize = count % pageSize == 0 ? count / pageSize : count / pageSize + 1; System.out.println("总页数:" + totalPageSize); // 页数最小为1 if (pageNo < 1) { pageNo = 1; } // 传入的页数大于总页数 if (pageNo > totalPageSize) { pageNo = totalPageSize; } int beginIndex = (pageNo - 1) * pageSize; int endIndex = pageNo * pageSize; String sql = "SELECT * FROM ("+ " SELECT ROW_NUMBER() over(ORDER BY Id asc) AS row_number, ID, CUSTOMER_NO, CREATEDATE"+ " FROM DUFUY.\"user\""+ " WHERE 1=1"+ " AND COUNTRY = 'HK'"+ " AND CREATEDATE >='2023-06-11 00:00:00' AND CREATEDATE <= '2023-06-11 23:59:59'"+ ") t "+ " WHERE t.ROW_NUMBER > ? "+ // 此处不能用?填充,要用+号拼接 " FETCH FIRST " + pageSize + " ROWS ONLY "; System.out.println("exe sql: "+ sql); ps = conn.prepareStatement(sql); System.out.println("beginIndex: " + beginIndex + ", endIndex: " + endIndex); ps.setInt(1, beginIndex); rs = ps.executeQuery(); while (rs.next()) { User user = new User(); user.setId(rs.getLong("ID")); user.setCustomerNo(rs.getString("CUSTOMER_NO")); user.setCreateDate(rs.getString("CREATEDATE")); list.add(user); } } catch (Exception e) { e.printStackTrace(); } finally { if(rs != null){ try { rs.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(ps != null){ try { ps.close(); } catch (SQLException e) { throw new RuntimeException(e); } } if(conn != null){ try { conn.close(); } catch (SQLException e) { throw new RuntimeException(e); } } } return list; } public static Connection getConnection() { try { // 需要引入db2jcc4.jar Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance(); } catch (InstantiationException e) { throw new RuntimeException(e); } catch (IllegalAccessException e) { throw new RuntimeException(e); } catch (ClassNotFoundException e) { throw new RuntimeException(e); } // 必须使用IP String url = "jdbc:db2://192.168.5.5:50000/SAMPLE"; Connection conn = null; try { conn = DriverManager.getConnection(url, "用户", "你自己的密码"); } catch (SQLException e) { e.printStackTrace(); } return conn; } }
public class User { private Long id; private String customerNo; private String createDate; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getCustomerNo() { return customerNo; } public void setCustomerNo(String customerNo) { this.customerNo = customerNo; } public String getCreateDate() { return createDate; } public void setCreateDate(String createDate) { this.createDate = createDate; } @Override public String toString() { return "User{" + "id=" + id + ", customerNo='" + customerNo + '\'' + ", createDate='" + createDate + '\'' + '}'; } }