From b8ffe259a400c6b91c7570e7fef7fb7174245a39 Mon Sep 17 00:00:00 2001 From: "Brian S. O'Neill" Date: Mon, 14 Apr 2008 04:52:32 +0000 Subject: Fix Oracle slice fetch. --- .../carbonado/repo/jdbc/H2SupportStrategy.java | 8 ++++---- .../com/amazon/carbonado/repo/jdbc/JDBCStorage.java | 12 +++++++++--- .../carbonado/repo/jdbc/JDBCSupportStrategy.java | 8 +++++--- .../carbonado/repo/jdbc/MysqlSupportStrategy.java | 8 ++++---- .../carbonado/repo/jdbc/OracleSupportStrategy.java | 20 +++++++++++--------- .../repo/jdbc/PostgresqlSupportStrategy.java | 8 ++++---- 6 files changed, 37 insertions(+), 27 deletions(-) (limited to 'src/main/java') diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/H2SupportStrategy.java b/src/main/java/com/amazon/carbonado/repo/jdbc/H2SupportStrategy.java index 6850fbc..30a8810 100644 --- a/src/main/java/com/amazon/carbonado/repo/jdbc/H2SupportStrategy.java +++ b/src/main/java/com/amazon/carbonado/repo/jdbc/H2SupportStrategy.java @@ -44,14 +44,14 @@ class H2SupportStrategy extends JDBCSupportStrategy { } @Override - String buildSelectWithSlice(String select, boolean limit, boolean offset) { - if (limit) { - if (offset) { + String buildSelectWithSlice(String select, boolean from, boolean to) { + if (to) { + if (from) { return select.concat(" LIMIT ? OFFSET ?"); } else { return select.concat(" LIMIT ?"); } - } else if (offset) { + } else if (from) { return select.concat(" LIMIT 2147483647 OFFSET ?"); } else { return select; diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCStorage.java b/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCStorage.java index 8810c09..f9bac1f 100644 --- a/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCStorage.java +++ b/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCStorage.java @@ -666,19 +666,20 @@ class JDBCStorage extends StandardQueryFactory return super.fetch(values, from, to); } select = prepareSelect(values, false); - select = mSupportStrategy.buildSelectWithSlice(select, true, false); + select = mSupportStrategy.buildSelectWithSlice(select, false, true); break; case OFFSET_ONLY: if (from <= 0) { return super.fetch(values, from, to); } select = prepareSelect(values, false); - select = mSupportStrategy.buildSelectWithSlice(select, false, true); + select = mSupportStrategy.buildSelectWithSlice(select, true, false); break; case LIMIT_AND_OFFSET: case OFFSET_AND_LIMIT: + case FROM_AND_TO: select = prepareSelect(values, false); - select = mSupportStrategy.buildSelectWithSlice(select, to != null, from > 0); + select = mSupportStrategy.buildSelectWithSlice(select, from > 0, to != null); break; } @@ -711,6 +712,11 @@ class JDBCStorage extends StandardQueryFactory case OFFSET_AND_LIMIT: ps.setLong(psOrdinal, from); ps.setLong(psOrdinal + 1, to - from); + break; + case FROM_AND_TO: + ps.setLong(psOrdinal, from); + ps.setLong(psOrdinal + 1, to); + break; } } else { ps.setLong(psOrdinal, from); diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCSupportStrategy.java b/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCSupportStrategy.java index f3b0db5..89756c5 100644 --- a/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCSupportStrategy.java +++ b/src/main/java/com/amazon/carbonado/repo/jdbc/JDBCSupportStrategy.java @@ -302,6 +302,8 @@ class JDBCSupportStrategy { LIMIT_AND_OFFSET, // Slice is fully supported with offset parameter first OFFSET_AND_LIMIT, + // Slice is fully supported with from parameter first + FROM_AND_TO, } /** @@ -313,13 +315,13 @@ class JDBCSupportStrategy { /** * @param select base select statement - * @param limit when true, select must support limit parameter - * @param offset when true, select must support offset parameter + * @param from when true, select must support slice from bound + * @param to when true, select must support slice to bound * @return revised select statement * @throws UnsupportedOperationException * @since 1.2 */ - String buildSelectWithSlice(String select, boolean limit, boolean offset) { + String buildSelectWithSlice(String select, boolean from, boolean to) { throw new UnsupportedOperationException(); } } diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/MysqlSupportStrategy.java b/src/main/java/com/amazon/carbonado/repo/jdbc/MysqlSupportStrategy.java index bf15ec6..beaa69c 100644 --- a/src/main/java/com/amazon/carbonado/repo/jdbc/MysqlSupportStrategy.java +++ b/src/main/java/com/amazon/carbonado/repo/jdbc/MysqlSupportStrategy.java @@ -43,14 +43,14 @@ class MysqlSupportStrategy extends JDBCSupportStrategy { } @Override - String buildSelectWithSlice(String select, boolean limit, boolean offset) { - if (limit) { - if (offset) { + String buildSelectWithSlice(String select, boolean from, boolean to) { + if (to) { + if (from) { return select.concat(" LIMIT ?,?"); } else { return select.concat(" LIMIT ?"); } - } else if (offset) { + } else if (from) { return select.concat(" LIMIT ?,18446744073709551615"); } else { return select; diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/OracleSupportStrategy.java b/src/main/java/com/amazon/carbonado/repo/jdbc/OracleSupportStrategy.java index 582b299..4098933 100644 --- a/src/main/java/com/amazon/carbonado/repo/jdbc/OracleSupportStrategy.java +++ b/src/main/java/com/amazon/carbonado/repo/jdbc/OracleSupportStrategy.java @@ -222,21 +222,23 @@ class OracleSupportStrategy extends JDBCSupportStrategy { @Override SliceOption getSliceOption() { - return SliceOption.OFFSET_AND_LIMIT; + return SliceOption.FROM_AND_TO; } @Override - String buildSelectWithSlice(String select, boolean limit, boolean offset) { - if (limit) { - if (offset) { - return "SELECT * FROM (SELECT ROW_.*, ROWNUM ROWNUM_ FROM(" + - select + ") ROW_) WHERE ROWNUM_ > ? AND ROWNUM_ <= ?"; + String buildSelectWithSlice(String select, boolean from, boolean to) { + if (to) { + if (from) { + // Use quoted identifier with space to prevent clash with + // Storable property name. + return "SELECT * FROM (SELECT \"A ROW\".*, ROWNUM \"A ROWNUM\" FROM (" + + select + ") \"A ROW\") WHERE \"A ROWNUM\" > ? AND \"A ROWNUM\" <= ?"; } else { return "SELECT * FROM (" + select + ") WHERE ROWNUM <= ?"; } - } else if (offset) { - return "SELECT * FROM (SELECT ROW_.*, ROWNUM ROWNUM_ FROM(" + - select + ") ROW_) WHERE ROWNUM_ > ?"; + } else if (from) { + return "SELECT * FROM (SELECT \"A ROW\".*, ROWNUM \"A ROWNUM\" FROM (" + + select + ") \"A ROW\") WHERE \"A ROWNUM\" > ?"; } else { return select; } diff --git a/src/main/java/com/amazon/carbonado/repo/jdbc/PostgresqlSupportStrategy.java b/src/main/java/com/amazon/carbonado/repo/jdbc/PostgresqlSupportStrategy.java index 43d35c2..48a4ad0 100644 --- a/src/main/java/com/amazon/carbonado/repo/jdbc/PostgresqlSupportStrategy.java +++ b/src/main/java/com/amazon/carbonado/repo/jdbc/PostgresqlSupportStrategy.java @@ -39,14 +39,14 @@ class PostgresqlSupportStrategy extends JDBCSupportStrategy { } @Override - String buildSelectWithSlice(String select, boolean limit, boolean offset) { - if (limit) { - if (offset) { + String buildSelectWithSlice(String select, boolean from, boolean to) { + if (to) { + if (from) { return select.concat(" LIMIT ? OFFSET ?"); } else { return select.concat(" LIMIT ?"); } - } else if (offset) { + } else if (from) { return select.concat(" LIMIT ALL OFFSET ?"); } else { return select; -- cgit v1.2.3