[hg] main-silver: #231030: Make usage of scrollable cursors conf...

  • From: Jaroslav Havlin < >
  • To:
  • Subject: [hg] main-silver: #231030: Make usage of scrollable cursors conf...
  • Date: Mon, 10 Jun 2013 17:01:49 -0700

changeset 832e3500091e in main-silver ((none))
details: http://hg.netbeans.org/main-silver/rev/832e3500091e
description:
        #231030: Make usage of scrollable cursors configurable

diffstat:

 
db.dataview/src/org/netbeans/modules/db/dataview/output/SQLExecutionHelper.java
    |  442 +++++++--
 
db.dataview/src/org/netbeans/modules/db/dataview/output/SQLStatementGenerator.java
 |   16 +
 2 files changed, 330 insertions(+), 128 deletions(-)

diffs (580 lines):

diff --git 
a/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLExecutionHelper.java
 
b/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLExecutionHelper.java
--- 
a/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLExecutionHelper.java
+++ 
b/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLExecutionHelper.java
@@ -86,6 +86,10 @@
     private final DataView dataView;
     // the RequestProcessor used for executing statements.
     private final RequestProcessor rp = new 
RequestProcessor("SQLStatementExecution", 20, true); // NOI18N
+    private static final String LIMIT_CLAUSE = "LIMIT ";               // 
NOI18N
+    public static final String OFFSET_CLAUSE = "OFFSET ";              // 
NOI18N
+    private boolean limitSupported = false;
+    private boolean useScrollableCursors = false;
     private int resultSetScrollType = ResultSet.TYPE_FORWARD_ONLY;
     private boolean supportesMultipleResultSets = false;
 
@@ -94,7 +98,7 @@
     }
 
     void initialDataLoad() throws SQLException {
-        assert (! SwingUtilities.isEventDispatchThread()) : "Must be called 
of the EDT!";
+        assert (! SwingUtilities.isEventDispatchThread()) : "Must be called 
off the EDT!";
 
         /**
          * Wrap initializing the SQL result into a runnable. This makes it
@@ -118,9 +122,130 @@
                 try {
                     DatabaseConnection dc = dataView.getDatabaseConnection();
                     Connection conn = 
DBConnectionFactory.getInstance().getConnection(dc);
+                    checkNonNullConnection(conn);
+                    checkSupportForMultipleResultSets(conn);
+                    DBMetaDataFactory dbMeta = new DBMetaDataFactory(conn);
+                    limitSupported = dbMeta.supportsLimit();
+                    String sql = dataView.getSQLString();
+                    boolean isSelect = isSelectStatement(sql);
 
+                    updateScrollableSupport(conn, dc, sql);
+
+                    if (Thread.interrupted()) {
+                        return;
+                    }
+                    stmt = prepareSQLStatement(conn, sql, true);
+
+                    if (Thread.interrupted()) {
+                        return;
+                    }
+                    // Read multiple Resultsets
+                    boolean isResultSet = executeSQLStatement(stmt, sql);
+
+                    // @todo: This needs clearing up => in light of request 
for
+                    // the ability to disable autocommit, this need to go
+                    if (!isResultSet || dataView.getUpdateCount() != -1) {
+                        if (!conn.getAutoCommit()) {
+                            conn.commit();
+                        }
+                        return;
+                    }
+                    if (Thread.interrupted()) {
+                        return;
+                    }
+                    boolean needReread = false;
+                    ResultSet rs = null;
+
+                    while (true) {
+                        if (isResultSet) {
+                            rs = stmt.getResultSet();
+
+                            Collection<DBTable> tables = 
dbMeta.generateDBTables(
+                                    rs, sql, isSelect);
+                            DataViewDBTable dvTable = new 
DataViewDBTable(tables);
+                            DataViewPageContext pageContext = 
dataView.addPageContext(
+                                    dvTable);
+                            needReread |= resultSetNeedsReloading(dvTable);
+
+                            if (!needReread) {
+                                loadDataFrom(pageContext, rs, 
useScrollableCursors);
+                            }
+                        }
+                        if (supportesMultipleResultSets) {
+                            isResultSet = stmt.getMoreResults();
+                            // @todo: Do somethink intelligent with the 
updatecounts
+                            int updateCount = stmt.getUpdateCount();
+                            if (isResultSet == false && updateCount == -1) {
+                                break;
+                            }
+                        } else {
+                            break;
+                        }
+                    }
+
+                    if (needReread) {
+                        isResultSet = executeSQLStatement(stmt, sql);
+                        int res = -1;
+                        while (true) {
+                            if (isResultSet) {
+                                res++;
+                                rs = stmt.getResultSet();
+                                DataViewPageContext pageContext = 
dataView.getPageContext(
+                                        res);
+                                loadDataFrom(pageContext, rs, 
useScrollableCursors);
+                            }
+                            if (supportesMultipleResultSets) {
+                                isResultSet = stmt.getMoreResults();
+                                // @todo: Do somethink intelligent with the 
updatecounts
+                                int updateCount = stmt.getUpdateCount();
+                                if (isResultSet == false && updateCount == 
-1) {
+                                    break;
+                                }
+                            } else {
+                                break;
+                            }
+                        }
+                    }
+                    // If total count was not retrieved using scrollable 
cursors,
+                    // compute it now.
+                    if (!useScrollableCursors && 
dataView.getPageContexts().size() > 0) {
+                        getTotalCount(isSelect, sql, stmt, 
dataView.getPageContext(0));
+                    }
+                    DataViewUtils.closeResources(rs);
+                } catch (SQLException sqlEx) {
+                    this.ex = sqlEx;
+                } catch (Exception e) {
+                  LOGGER.log(Level.WARNING, null, e);
+                } finally {
+                    DataViewUtils.closeResources(stmt);
+                    synchronized (Loader.this) {
+                        finished = true;
+                        this.notifyAll();
+                    }
+                }
+            }
+
+            @Override
+            public boolean cancel() {
+                if (stmt != null) {
+                    try {
+                        stmt.cancel();
+                    } catch (SQLException sqlEx) {
+                        LOGGER.log(Level.FINE, null, sqlEx);
+                        // Ok! The DBMS might not support Statement-Canceling
+                    }
+                }
+                return true;
+            }
+
+            /**
+             * Check that the connection is not null. If it is null, try to 
find
+             * cause of the failure and throw an exception.
+             */
+            private void checkNonNullConnection(Connection conn) throws
+                    SQLException {
+                if (conn == null) {
                     String msg;
-                    if (conn == null) {
                         Throwable t = DBConnectionFactory.getInstance()
                                 .getLastException();
                         if (t != null) {
@@ -136,125 +261,15 @@
                         LOGGER.log(Level.INFO, msg, t);
                         throw new SQLException(msg, t);
                     }
+            }
+
+            private void checkSupportForMultipleResultSets(Connection conn) {
                     try {
                         supportesMultipleResultSets = 
conn.getMetaData().supportsMultipleResultSets();
                     } catch (SQLException ex) {
                         LOGGER.log(Level.INFO, "Database driver throws 
exception "  //NOI18N
                                 + "when checking for multiple resultset 
support."); //NOI18N
                     }
-                    DBMetaDataFactory dbMeta = new DBMetaDataFactory(conn);
-                    String sql = dataView.getSQLString();
-
-                    updateScrollableSupport(conn, dc, sql);
-
-                    if (Thread.interrupted()) {
-                        return;
-                    }
-                    stmt = prepareSQLStatement(conn, sql, true);
-
-                    if (Thread.interrupted()) {
-                        return;
-                    }
-                    // Read multiple Resultsets
-                    boolean resultSet = executeSQLStatement(stmt, sql);
-
-                    // @todo: This needs clearing up => in light of request 
for
-                    // the ability to disable autocommit, this need to go
-                    if (dataView.getUpdateCount() != -1) {
-                        if (!conn.getAutoCommit()) {
-                            conn.commit();
-                        }
-                        return;
-                    }
-                    if (Thread.interrupted()) {
-                        return;
-                    }
-                    // @todo: This needs clearing up => in light of request 
for
-                    // the ability to disable autocommit, this need to go
-                    if (!resultSet) {
-                        if (!conn.getAutoCommit()) {
-                            conn.commit();
-                        }
-                        return;
-                    }
-                    boolean needReread = false;
-                    ResultSet rs = null;
-
-                    while (true) {
-                        if (resultSet) {
-                            rs = stmt.getResultSet();
-
-                            Collection<DBTable> tables = 
dbMeta.generateDBTables(
-                                    rs, sql, isSelectStatement(sql));
-                            DataViewDBTable dvTable = new 
DataViewDBTable(tables);
-                            DataViewPageContext pageContext = 
dataView.addPageContext(
-                                    dvTable);
-                            needReread |= resultSetNeedsReloading(dvTable);
-
-                            if (!needReread) {
-                                loadDataFrom(pageContext, rs, true);
-                            }
-                        }
-                        if (supportesMultipleResultSets) {
-                            resultSet = stmt.getMoreResults();
-                            // @todo: Do somethink intelligent with the 
updatecounts
-                            int updateCount = stmt.getUpdateCount();
-                            if (resultSet == false && updateCount == -1) {
-                                break;
-                            }
-                        } else {
-                            break;
-                        }
-                    }
-
-                    if (needReread) {
-                        resultSet = executeSQLStatement(stmt, sql);
-                        int res = -1;
-                        while (true) {
-                            if (resultSet) {
-                                res++;
-                                rs = stmt.getResultSet();
-                                DataViewPageContext pageContext = 
dataView.getPageContext(
-                                        res);
-                                loadDataFrom(pageContext, rs, true);
-                            }
-                            if (supportesMultipleResultSets) {
-                                resultSet = stmt.getMoreResults();
-                                // @todo: Do somethink intelligent with the 
updatecounts
-                                int updateCount = stmt.getUpdateCount();
-                                if (resultSet == false && updateCount == -1) 
{
-                                    break;
-                                }
-                            } else {
-                                break;
-                            }
-                        }
-                    }
-                    DataViewUtils.closeResources(rs);
-                } catch (SQLException sqlEx) {
-                    this.ex = sqlEx;
-                } catch (Exception e) {
-                  LOGGER.log(Level.WARNING, null, e);
-                } finally {
-                    DataViewUtils.closeResources(stmt);
-                    synchronized (Loader.this) {
-                        finished = true;
-                        this.notifyAll();
-                    }
-                }
-            }
-
-            @Override
-            public boolean cancel() {
-                if (stmt != null) {
-                    try {
-                        stmt.cancel();
-                    } catch (SQLException sqlEx) {
-                        LOGGER.log(Level.FINE, null, sqlEx);
-                        // Ok! The DBMS might not support Statement-Canceling
-                    }
-                }
-                return true;
             }
         }
         Loader l = new Loader();
@@ -635,7 +650,7 @@
                             DataViewPageContext pageContext = 
dataView.getPageContext(
                                     res);
                             rs = stmt.getResultSet();
-                            loadDataFrom(pageContext, rs, getTotal);
+                            loadDataFrom(pageContext, rs, getTotal && 
useScrollableCursors);
                         }
                         if (supportesMultipleResultSets) {
                             resultSet = stmt.getMoreResults();
@@ -649,6 +664,10 @@
                         }
                     }
 
+                    // Get total count using the old-fashioned method.
+                    if (!useScrollableCursors && getTotal && 
dataView.getPageContexts().size() > 0) {
+                        getTotalCount(isSelectStatement(sql), sql, stmt, 
dataView.getPageContext(0));
+                    }
                     DataViewUtils.closeResources(rs);
                 } catch (SQLException sqlEx) {
                     String title = 
NbBundle.getMessage(SQLExecutionHelper.class, "MSG_error");
@@ -708,8 +727,9 @@
             boolean hasNext = false;
             boolean needSlowSkip = true;
 
-            if (rs.getType() == ResultSet.TYPE_SCROLL_INSENSITIVE
-                    || rs.getType() == ResultSet.TYPE_SCROLL_SENSITIVE) {
+            if (useScrollableCursors
+                    && (rs.getType() == ResultSet.TYPE_SCROLL_INSENSITIVE
+                    || rs.getType() == ResultSet.TYPE_SCROLL_SENSITIVE)) {
                 try {
                     hasNext = rs.absolute(startFrom);
                     needSlowSkip = false;
@@ -787,9 +807,13 @@
     private Statement prepareSQLStatement(Connection conn, String sql, 
boolean needTotal) throws SQLException {
         Statement stmt = null;
         if (sql.startsWith("{")) { // NOI18N
-            stmt = conn.prepareCall(sql, resultSetScrollType, 
ResultSet.CONCUR_READ_ONLY);
+            stmt = useScrollableCursors
+                    ? conn.prepareCall(sql, resultSetScrollType, 
ResultSet.CONCUR_READ_ONLY)
+                    : conn.prepareCall(sql);
         } else if (isSelectStatement(sql)) {
-            stmt = conn.createStatement(resultSetScrollType, 
ResultSet.CONCUR_READ_ONLY);
+            stmt = useScrollableCursors
+                    ? conn.createStatement(resultSetScrollType, 
ResultSet.CONCUR_READ_ONLY)
+                    : conn.createStatement();
 
             // set a reasonable fetchsize
             setFetchSize(stmt, 50);
@@ -819,7 +843,9 @@
                 }
             }
         } else {
-            stmt = conn.createStatement(resultSetScrollType, 
ResultSet.CONCUR_READ_ONLY);
+            stmt = useScrollableCursors
+                    ? conn.createStatement(resultSetScrollType, 
ResultSet.CONCUR_READ_ONLY)
+                    : conn.createStatement();
         }
         return stmt;
     }
@@ -834,7 +860,9 @@
             isResultSet = ((PreparedStatement) stmt).execute();
         } else {
             try {
-                isResultSet = stmt.execute(sql);
+                DataViewPageContext pc = dataView.getPageContexts().size() > 0
+                        ? dataView.getPageContext(0) : null;
+                isResultSet = stmt.execute(appendLimitIfRequired(pc, sql));
             } catch (NullPointerException ex) {
                 LOGGER.log(Level.SEVERE, "Failed to execute SQL Statement 
[{0}], cause: {1}", new Object[] {sql, ex});
                 throw new SQLException(ex);
@@ -867,10 +895,135 @@
         return isResultSet;
     }
 
+    private void getTotalCount(boolean isSelect, String sql, Statement stmt,
+            DataViewPageContext pageContext) {
+        if (!isSelect) {
+            setTotalCount(null, pageContext);
+            return;
+        }
+
+        // Case for LIMIT n OFFSET m
+        if (isLimitUsedInSelect(sql)) {
+            try {
+                String lmtStr = 
sql.toUpperCase().split(LIMIT_CLAUSE)[1].trim();
+                int rCnt = Integer.parseInt(lmtStr.split(" ")[0]);
+                pageContext.setTotalRows(rCnt);
+                return;
+            } catch (NumberFormatException nex) {
+                LOGGER.log(Level.FINE, null, nex);
+            }
+        }
+
+        // SELECT COUNT(*) FROM (sqlquery) alias
+        ResultSet cntResultSet = null;
+        try {
+            cntResultSet = stmt.executeQuery(
+                    SQLStatementGenerator.getCountAsSubQuery(sql));
+            setTotalCount(cntResultSet, pageContext);
+            return;
+        } catch (SQLException e) {
+        } finally {
+            DataViewUtils.closeResources(cntResultSet);
+        }
+
+        // Try spliting the query by FROM and use "SELECT COUNT(*) FROM"  + 
"2nd part sql"
+        if (!isGroupByUsedInSelect(sql)) {
+            cntResultSet = null;
+            try {
+                cntResultSet = stmt.executeQuery(
+                        SQLStatementGenerator.getCountSQLQuery(sql));
+                setTotalCount(cntResultSet, pageContext);
+                return;
+            } catch (SQLException e) {
+            } finally {
+                DataViewUtils.closeResources(cntResultSet);
+            }
+        }
+
+        // In worse case, get the count from resultset
+        cntResultSet = null;
+        int totalRows = 0;
+        try {
+            // reset fetch size
+            int fetchSize = pageContext.getPageSize();
+            try {
+                fetchSize = stmt.getFetchSize();
+                stmt.setFetchSize(20000);
+            } catch (SQLException sqe) {
+                // ignore
+            }
+
+            cntResultSet = stmt.executeQuery(sql);
+            while (cntResultSet.next()) {
+                totalRows++;
+            }
+            pageContext.setTotalRows(totalRows);
+
+            // set to old value
+            try {
+                stmt.setFetchSize(fetchSize);
+            } catch (SQLException sqe) {
+                // ignore
+            }
+            return;
+        } catch (SQLException e) {
+            LOGGER.log(Level.FINE, null, e);
+        } finally {
+            DataViewUtils.closeResources(cntResultSet);
+        }
+
+        // Unable to compute the total rows
+        setTotalCount(null, pageContext);
+    }
+
     private boolean isSelectStatement(String queryString) {
         return queryString.trim().toUpperCase().startsWith("SELECT") && 
queryString.trim().toUpperCase().indexOf("INTO") == -1; // NOI18N
     }
 
+    private boolean isLimitUsedInSelect(String sql) {
+        return sql.toUpperCase().indexOf(LIMIT_CLAUSE) != -1;
+    }
+
+    private boolean isGroupByUsedInSelect(String sql) {
+        return sql.toUpperCase().indexOf(" GROUP BY ") != -1 || 
sql.toUpperCase().indexOf(
+                " COUNT(*) ") != -1; // NOI18N
+    }
+
+    void setTotalCount(ResultSet countresultSet, DataViewPageContext 
pageContext) {
+        try {
+            if (countresultSet == null) {
+                pageContext.setTotalRows(-1);
+                pageContext.setTotalRows(-1);
+            } else {
+                if (countresultSet.next()) {
+                    int count = countresultSet.getInt(1);
+                    pageContext.setTotalRows(count);
+                    pageContext.setTotalRows(count);
+                }
+            }
+        } catch (SQLException ex) {
+            LOGGER.log(Level.SEVERE, "Could not get total row count ", ex); 
// NOI18N
+        }
+    }
+
+    private String appendLimitIfRequired(DataViewPageContext pageContext,
+            String sql) {
+        if (useScrollableCursors) {
+            return sql;
+        } else if (limitSupported && isSelectStatement(sql)
+                && !isLimitUsedInSelect(sql)) {
+
+            int pageSize = pageContext == null ? dataView.getPageSize()
+                    : pageContext.getPageSize();
+            int currentPos = pageContext == null ? 1
+                    : pageContext.getCurrentPos();
+            return sql + ' ' + LIMIT_CLAUSE + pageSize
+                    + ' ' + OFFSET_CLAUSE + (currentPos - 1);
+        } else {
+            return sql;
+        }
+    }
+
     static String millisecondsToSeconds(long ms) {
         NumberFormat fmt = NumberFormat.getInstance();
         fmt.setMaximumFractionDigits(3);
@@ -931,6 +1084,10 @@
      */
     private void updateScrollableSupport(Connection conn, DatabaseConnection 
dc,
             String sql) {
+        useScrollableCursors = checkDriverSupportsScrollableCursors(dc);
+        if (!useScrollableCursors) {
+            return;
+        }
         String driverName = dc.getDriverClass();
         /* Derby fails to support scrollable cursors when invoking 'stored 
procedures'
          which return resultsets - it fails hard: not throwing a 
SQLException,
@@ -941,12 +1098,6 @@
                 resultSetScrollType = ResultSet.TYPE_FORWARD_ONLY;
                 return;
             }
-        } else if (driverName != null
-                && driverName.startsWith("com.informix.jdbc.IfxDriver")) { 
//NOI18N
-            // Informix failes scrollable result sets if blob columns are 
part
-            // of the resultset -> disable ...
-            resultSetScrollType = ResultSet.TYPE_FORWARD_ONLY;
-            return;
         }
         /* Try to get a "good" scrollable ResultSet and follow the DBs 
support */
         try {
@@ -962,4 +1113,39 @@
                     + " database for scrollable resultset support"); //NOI18N
         }
     }
+
+    /**
+     * Decide whether scrollable cursors should be used by the connection.
+     */
+    private boolean checkDriverSupportsScrollableCursors(DatabaseConnection 
dc) {
+        String drv = dc == null ? null : dc.getDriverClass();
+        if (drv == null) {
+            return false;
+        } else {
+            String[] whiteListPrefixes;
+            String customWhiteList = System.getProperty(
+                    "db.scrollable.cursors.drivers");                   
//NOI18N
+            if (customWhiteList != null) {
+                LOGGER.log(Level.INFO,
+                        "Using custom list for scrollable cursors: 
{0}",//NOI18N
+                        customWhiteList);
+                whiteListPrefixes = customWhiteList.isEmpty()
+                        ? new String[0]
+                        : customWhiteList.split(",");                   
//NOI18N
+            } else {
+                LOGGER.log(Level.FINE, "Using built-in list of drivers 
"//NOI18N
+                        + " with support for scrollable cursors.");     
//NOI18N
+                whiteListPrefixes = new String[]{
+                    "org.apache.derby", "com.mysql", "oracle", //NOI18N
+                    "org.postgresql" //NOI18N
+                };
 }
+            for (String allowedPrefix : whiteListPrefixes) {
+                if (drv.startsWith(allowedPrefix)) {
+                    return true;
+                }
+            }
+            return false;
+        }
+    }
+}
diff --git 
a/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLStatementGenerator.java
 
b/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLStatementGenerator.java
--- 
a/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLStatementGenerator.java
+++ 
b/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLStatementGenerator.java
@@ -335,6 +335,22 @@
         return sql.toString();
     }
 
+    static String getCountSQLQuery(String queryString) {
+        // User may type "FROM" in either lower, upper or mixed case
+        String[] splitByFrom = queryString.toUpperCase().split("FROM"); // 
NOI18N
+        queryString = queryString.substring(splitByFrom[0].length());
+
+        String[] splitByOrderBy = queryString.toUpperCase().split("ORDER 
BY"); // NOI18N
+        queryString = queryString.substring(0, splitByOrderBy[0].length());
+        return "SELECT COUNT(*) " + queryString; // NOI18N
+    }
+
+    static String getCountAsSubQuery(String queryString) {
+        String[] splitByOrderBy = queryString.toUpperCase().split("ORDER 
BY"); // NOI18N
+        queryString = queryString.substring(0, splitByOrderBy[0].length());
+        return "SELECT COUNT(*) FROM (" + queryString + ") C2668"; // NOI18N
+    }
+
     private boolean addSeparator(boolean and, StringBuilder sql, String sep) 
{
         if (and) {
             sql.append(sep);

[hg] main-silver: #231030: Make usage of scrollable cursors conf...

Jaroslav Havlin 06/11/2013

<Possible follow-up(s)>

[hg] main-silver: #231030: Make usage of scrollable cursors conf...

Jaroslav Havlin 06/26/2013

Project Features

About this Project

DB was started in November 2009, is owned by Antonin Nebuzelsky, and has 113 members.
By use of this website, you agree to the NetBeans Policies and Terms of Use (revision 20131025.e7cbc9d). © 2013, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo
 
 
Close
loading
Please Confirm
Close