Attachment #132727 for bug #227588

This Bugzilla instance is a read-only archive of historic NetBeans bug reports. To report a bug in NetBeans please follow the project's instructions for reporting issues.

View | Details | Raw Unified | Return to bug 227588
Collapse All | Expand All




import java.util.concurrent.Future;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.table.TableModel;
import org.netbeans.modules.db.dataview.meta.DBColumn;
import org.netbeans.modules.db.dataview.meta.DBConnectionFactory;
import org.netbeans.modules.db.dataview.meta.DBException;

 * @author Ahimanikya Satapathy
 */
class SQLExecutionHelper {
    private static final Logger LOGGER = Logger.getLogger(SQLExecutionHelper.class.getName());
    
    private final DataView dataView;
    // the RequestProcessor used for executing statements.
    private final RequestProcessor rp = new RequestProcessor("SQLStatementExecution", 20, true); // NOI18N
    private int resultSetScrollType = ResultSet.TYPE_FORWARD_ONLY;
    public static final String OFFSET_CLAUSE = "OFFSET "; // NOI18N
    private static final Logger LOGGER = Logger.getLogger(SQLExecutionHelper.class.getName());
    private boolean limitSupported = false;

    SQLExecutionHelper(DataView dataView) {
        this.dataView = dataView;

                try {
                    Connection conn = DBConnectionFactory.getInstance()
                            .getConnection(dataView.getDatabaseConnection());
                    
                    String msg;
                    if (conn == null) {
                        Throwable t = DBConnectionFactory.getInstance()

                        LOGGER.log(Level.INFO, msg, t);
                        throw new SQLException(msg, t);
                    }

                    try {
                        if (conn.getMetaData().supportsResultSetType(
                                ResultSet.TYPE_SCROLL_INSENSITIVE)) {
                            resultSetScrollType = ResultSet.TYPE_SCROLL_INSENSITIVE;
                        } else if (conn.getMetaData().supportsResultSetType(
                                ResultSet.TYPE_SCROLL_SENSITIVE)) {
                            resultSetScrollType = ResultSet.TYPE_SCROLL_SENSITIVE;
                        }
                    } catch (Exception ex) {
                        LOGGER.log(Level.WARNING, "Exception while querying database for scrollable resultset support");
                    }
                    
                    DBMetaDataFactory dbMeta = new DBMetaDataFactory(conn);
                    limitSupported = dbMeta.supportsLimit();
                    String sql = dataView.getSQLString();
                    boolean isSelect = isSelectStatement(sql);

                    if (Thread.interrupted()) {
                        return;
                    }
                    stmt = prepareSQLStatement(conn, sql, true);

                    if (Thread.interrupted()) {
                        return;

                        return;
                    }
                    Collection<DBTable> tables = dbMeta.generateDBTables(
                            rs, sql, isSelectStatement(sql));
                    DataViewDBTable dvTable = new DataViewDBTable(tables);
                    dataView.getDataViewPageContext().getModel().setColumns(
                            dvTable.getColumns().toArray(new DBColumn[0]));

                        rs = stmt.getResultSet();
                    }
                    loadDataFrom(rs);
                    
                    if (Thread.interrupted()) {
                        return;
                    }
                    
                    Integer result = null;
                    
                    if (rs.getType() == ResultSet.TYPE_SCROLL_INSENSITIVE
                            || rs.getType() == ResultSet.TYPE_SCROLL_INSENSITIVE) {
                        try {
                            rs.last();
                            result = rs.getRow();
                        } catch (SQLException ex) {
                            LOGGER.log(Level.INFO,
                                    "Failed to jump to end of SQL Statement [{0}], cause: {1}",
                                    new Object[]{sql, ex});
                        }
                    }
                    
                    setTotalCount(result);
                    
                    DataViewUtils.closeResources(rs);
                } catch (SQLException sqlEx) {
                    this.ex = sqlEx;
                } finally {

                if (Thread.interrupted()) {
                    return;
                }
                
                boolean getTotal = false;
                
                // Get total row count
                if (dataView.getDataViewPageContext().getTotalRows() == -1) {
                    getTotal = true;
                }
                stmt = prepareSQLStatement(conn, sql, getTotal);

                // Execute the query
                try {

                    if (dataView.hasResultSet()) {
                        ResultSet rs = stmt.getResultSet();
                        loadDataFrom(rs);

                        if (getTotal) {
                            Integer result = null;

                            if (rs.getType() == ResultSet.TYPE_SCROLL_INSENSITIVE
                                    || rs.getType() == ResultSet.TYPE_SCROLL_INSENSITIVE) {
                                try {
                                    rs.last();
                                    result = rs.getRow();
                                } catch (SQLException ex) {
                                    LOGGER.log(Level.INFO,
                                            "Failed to jump to end of SQL Statement [{0}], cause: {1}",
                                            new Object[]{sql, ex});
                                }
                            }

                            setTotalCount(result);
                        }

                        DataViewUtils.closeResources(rs);
                    } else {
                        return;

                    throw sqlEx;
                }


                if (dataView.getDataViewPageContext().getTotalRows() == -1) {
                    getTotalCount(isSelectStatement(sql), sql, stmt);
                }
            }

            @Override

        }

        int pageSize = dataView.getDataViewPageContext().getPageSize();
        int startFrom = dataView.getDataViewPageContext().getCurrentPos();
        if (! limitSupported || isLimitUsedInSelect(dataView.getSQLString())) {
            startFrom = dataView.getDataViewPageContext().getCurrentPos() - 1;
        }

        DataViewDBTable tblMeta = dataView.getDataViewDBTable();
        List<Object[]> rows = new ArrayList<Object[]>();
        int colCnt = tblMeta.getColumnCount();
        try {
            boolean hasNext = false;
            boolean needSlowSkip = true;
            
            if(rs.getType() == ResultSet.TYPE_SCROLL_INSENSITIVE ||
                    rs.getType() == ResultSet.TYPE_SCROLL_SENSITIVE) {
                try {
                    hasNext = rs.absolute(startFrom);
                    needSlowSkip = false;
                } catch (SQLException ex) {
                    LOGGER.log(Level.FINE, "Absolute positioning failed", ex); // NOI18N
                }
            }
            
            if (needSlowSkip) {
                // Skip till current position
                hasNext = rs.next();
                int curRowPos = 1;
                while (hasNext && curRowPos < startFrom) {
                    if (Thread.currentThread().isInterrupted()) {
                        return;
                    }
                    hasNext = rs.next();
                    curRowPos++;
                }
            }

            // Get next page

        }
    }

    void setTotalCount(Integer count) {
        if (count == null) {
            dataView.getDataViewPageContext().setTotalRows(-1);
            dataView.setTotalRowCount(-1);
        } else {
            dataView.getDataViewPageContext().setTotalRows(count);
            dataView.setTotalRowCount(count);
                    int count = countresultSet.getInt(1);
                    dataView.getDataViewPageContext().setTotalRows(count);
                    dataView.setTotalRowCount(count);
                }
            }
        } catch (SQLException ex) {
            LOGGER.log(Level.SEVERE, "Could not get total row count ", ex); // NOI18N
        }
    }
    
    private Statement prepareSQLStatement(Connection conn, String sql, boolean needTotal) throws SQLException {
        if (limitSupported && isSelectStatement(sql)) {
            if (!isLimitUsedInSelect(sql)) {
                sql += ' ' + LIMIT_CLAUSE + dataView.getDataViewPageContext().getPageSize();
                sql += ' ' + OFFSET_CLAUSE + (dataView.getDataViewPageContext().getCurrentPos() - 1);
            }
        }

        return sql;
    }

    private Statement prepareSQLStatement(Connection conn, String sql) throws SQLException {
        Statement stmt = null;
        if (sql.startsWith("{")) { // NOI18N
            stmt = conn.prepareCall(sql, resultSetScrollType, ResultSet.CONCUR_READ_ONLY);
        } else if (isSelectStatement(sql)) {
            stmt = conn.createStatement(resultSetScrollType, ResultSet.CONCUR_READ_ONLY);
            int pageSize = dataView.getDataViewPageContext().getPageSize();

            // hint to fetch "pagesize" elements en-block
            try {
                stmt.setFetchSize(pageSize);
            } catch (SQLException e) {

                LOGGER.log(Level.WARNING, "Unable to set Fetch size", e); // NOI18N
            }

            // hint to only query a certain number of rows -> potentially 
            // improve performance for low page numbers
            // only usable for "non-total" resultsets
            if (!needTotal) {
                try {
                    stmt.setMaxRows(
                            dataView.getDataViewPageContext().getCurrentPos() + pageSize);
                } catch (SQLException exc) {
                    LOGGER.log(Level.WARNING, "Unable to set Max row count", exc); // NOI18N
                }
            } catch (SQLException exc) {
                LOGGER.log(Level.WARNING, "Unable to set Max row size", exc); // NOI18N
            }
        } else {
            stmt = conn.createStatement(resultSetScrollType, ResultSet.CONCUR_READ_ONLY);
        }
        return stmt;
    }

            isResultSet = ((PreparedStatement) stmt).execute();
        } else {
            try {
                isResultSet = stmt.execute(sql);
            } catch (NullPointerException ex) {
                LOGGER.log(Level.SEVERE, "Failed to execute SQL Statement [{0}], cause: {1}", new Object[] {sql, ex});
                throw new SQLException(ex);
            } catch (SQLException sqlExc) {
                LOGGER.log(Level.SEVERE, "Failed to execute SQL Statement [{0}], cause: {1}", new Object[]{sql, sqlExc});
                throw sqlExc;
                } else {
                    LOGGER.log(Level.SEVERE, "Failed to execute SQL Statement [{0}], cause: {1}", new Object[] {sql, sqlExc});
                    throw sqlExc;
                }
            }
        }


        }
    }

    private void getTotalCount(boolean isSelect, String sql, Statement stmt) {
        if (! isSelect) {
            setTotalCount(null);
            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]);
                dataView.getDataViewPageContext().setTotalRows(rCnt);
                return;
            } catch (NumberFormatException nex) {
            }
        }

        // SELECT COUNT(*) FROM (sqlquery) alias
        ResultSet cntResultSet = null;
        try {
            cntResultSet = stmt.executeQuery(SQLStatementGenerator.getCountAsSubQuery(sql));
            setTotalCount(cntResultSet);
            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);
                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 = dataView.getDataViewPageContext().getPageSize();
            try {
                fetchSize = stmt.getFetchSize();
                stmt.setFetchSize(20000);
            } catch (SQLException sqe) {
                // ignore
            }

            cntResultSet = stmt.executeQuery(sql);
            while (cntResultSet.next()) {
                totalRows++;
            }
            dataView.getDataViewPageContext().setTotalRows(totalRows);

            // set to old value
            try {
                stmt.setFetchSize(fetchSize);
            } catch (SQLException sqe) {
                // ignore
            }
            return;
        } catch (SQLException e) {
        } finally {
            DataViewUtils.closeResources(cntResultSet);
        }

        // Unable to compute the total rows
        setTotalCount(null);

    }

    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
    }

    static String millisecondsToSeconds(long ms) {
        NumberFormat fmt = NumberFormat.getInstance();
        fmt.setMaximumFractionDigits(3);

Return to bug 227588

Lines 60-66 Link Here

(-)a/db.dataview/src/org/netbeans/modules/db/dataview/output/SQLExecutionHelper.java (-157 / +114 lines)
60	import java.util.concurrent.Future;	60	import java.util.concurrent.Future;
61	import java.util.logging.Level;	61	import java.util.logging.Level;
62	import java.util.logging.Logger;	62	import java.util.logging.Logger;
63	import javax.swing.table.TableModel;
64	import org.netbeans.modules.db.dataview.meta.DBColumn;	63	import org.netbeans.modules.db.dataview.meta.DBColumn;
65	import org.netbeans.modules.db.dataview.meta.DBConnectionFactory;	64	import org.netbeans.modules.db.dataview.meta.DBConnectionFactory;
66	import org.netbeans.modules.db.dataview.meta.DBException;	65	import org.netbeans.modules.db.dataview.meta.DBException;
Lines 80-93 Link Here
80	* @author Ahimanikya Satapathy	79	* @author Ahimanikya Satapathy
81	*/	80	*/
82	class SQLExecutionHelper {	81	class SQLExecutionHelper {
83		82	private static final Logger LOGGER = Logger.getLogger(SQLExecutionHelper.class.getName());
		83
84	private final DataView dataView;	84	private final DataView dataView;
85	// the RequestProcessor used for executing statements.	85	// the RequestProcessor used for executing statements.
86	private final RequestProcessor rp = new RequestProcessor("SQLStatementExecution", 20, true); // NOI18N	86	private final RequestProcessor rp = new RequestProcessor("SQLStatementExecution", 20, true); // NOI18N
87	private static final String LIMIT_CLAUSE = "LIMIT "; // NOI18N	87	private int resultSetScrollType = ResultSet.TYPE_FORWARD_ONLY;
88	public static final String OFFSET_CLAUSE = "OFFSET "; // NOI18N
89	private static final Logger LOGGER = Logger.getLogger(SQLExecutionHelper.class.getName());
90	private boolean limitSupported = false;
91		88
92	SQLExecutionHelper(DataView dataView) {	89	SQLExecutionHelper(DataView dataView) {
93	this.dataView = dataView;	90	this.dataView = dataView;
Lines 117-122 Link Here
117	try {	114	try {
118	Connection conn = DBConnectionFactory.getInstance()	115	Connection conn = DBConnectionFactory.getInstance()
119	.getConnection(dataView.getDatabaseConnection());	116	.getConnection(dataView.getDatabaseConnection());
		117
120	String msg;	118	String msg;
121	if (conn == null) {	119	if (conn == null) {
122	Throwable t = DBConnectionFactory.getInstance()	120	Throwable t = DBConnectionFactory.getInstance()
Lines 134-148 Link Here
134	LOGGER.log(Level.INFO, msg, t);	132	LOGGER.log(Level.INFO, msg, t);
135	throw new SQLException(msg, t);	133	throw new SQLException(msg, t);
136	}	134	}
		135
		136	try {
		137	if (conn.getMetaData().supportsResultSetType(
		138	ResultSet.TYPE_SCROLL_INSENSITIVE)) {
		139	resultSetScrollType = ResultSet.TYPE_SCROLL_INSENSITIVE;
		140	} else if (conn.getMetaData().supportsResultSetType(
		141	ResultSet.TYPE_SCROLL_SENSITIVE)) {
		142	resultSetScrollType = ResultSet.TYPE_SCROLL_SENSITIVE;
		143	}
		144	} catch (Exception ex) {
		145	LOGGER.log(Level.WARNING, "Exception while querying database for scrollable resultset support");
		146	}
		147
137	DBMetaDataFactory dbMeta = new DBMetaDataFactory(conn);	148	DBMetaDataFactory dbMeta = new DBMetaDataFactory(conn);
138	limitSupported = dbMeta.supportsLimit();
139	String sql = dataView.getSQLString();	149	String sql = dataView.getSQLString();
140	boolean isSelect = isSelectStatement(sql);
141		150
142	if (Thread.interrupted()) {	151	if (Thread.interrupted()) {
143	return;	152	return;
144	}	153	}
145	stmt = prepareSQLStatement(conn, sql);	154	stmt = prepareSQLStatement(conn, sql, true);
146		155
147	if (Thread.interrupted()) {	156	if (Thread.interrupted()) {
148	return;	157	return;
Lines 173-179 Link Here
173	return;	182	return;
174	}	183	}
175	Collection<DBTable> tables = dbMeta.generateDBTables(	184	Collection<DBTable> tables = dbMeta.generateDBTables(
176	rs, sql, isSelect);	185	rs, sql, isSelectStatement(sql));
177	DataViewDBTable dvTable = new DataViewDBTable(tables);	186	DataViewDBTable dvTable = new DataViewDBTable(tables);
178	dataView.getDataViewPageContext().getModel().setColumns(	187	dataView.getDataViewPageContext().getModel().setColumns(
179	dvTable.getColumns().toArray(new DBColumn[0]));	188	dvTable.getColumns().toArray(new DBColumn[0]));
Lines 183-193 Link Here
183	rs = stmt.getResultSet();	192	rs = stmt.getResultSet();
184	}	193	}
185	loadDataFrom(rs);	194	loadDataFrom(rs);
186	DataViewUtils.closeResources(rs);	195
187	if (Thread.interrupted()) {	196	if (Thread.interrupted()) {
188	return;	197	return;
189	}	198	}
190	getTotalCount(isSelect, sql, stmt);	199
		200	Integer result = null;
		201
		202	if (rs.getType() == ResultSet.TYPE_SCROLL_INSENSITIVE
		203	\|\| rs.getType() == ResultSet.TYPE_SCROLL_INSENSITIVE) {
		204	try {
		205	rs.last();
		206	result = rs.getRow();
		207	} catch (SQLException ex) {
		208	LOGGER.log(Level.INFO,
		209	"Failed to jump to end of SQL Statement [{0}], cause: {1}",
		210	new Object[]{sql, ex});
		211	}
		212	}
		213
		214	setTotalCount(result);
		215
		216	DataViewUtils.closeResources(rs);
191	} catch (SQLException sqlEx) {	217	} catch (SQLException sqlEx) {
192	this.ex = sqlEx;	218	this.ex = sqlEx;
193	} finally {	219	} finally {
Lines 518-524 Link Here
518	if (Thread.interrupted()) {	544	if (Thread.interrupted()) {
519	return;	545	return;
520	}	546	}
521	stmt = prepareSQLStatement(conn, sql);	547
		548	boolean getTotal = false;
		549
		550	// Get total row count
		551	if (dataView.getDataViewPageContext().getTotalRows() == -1) {
		552	getTotal = true;
		553	}
		554	stmt = prepareSQLStatement(conn, sql, getTotal);
522		555
523	// Execute the query	556	// Execute the query
524	try {	557	try {
Lines 529-534 Link Here
529	if (dataView.hasResultSet()) {	562	if (dataView.hasResultSet()) {
530	ResultSet rs = stmt.getResultSet();	563	ResultSet rs = stmt.getResultSet();
531	loadDataFrom(rs);	564	loadDataFrom(rs);
		565
		566	if (getTotal) {
		567	Integer result = null;
		568
		569	if (rs.getType() == ResultSet.TYPE_SCROLL_INSENSITIVE
		570	\|\| rs.getType() == ResultSet.TYPE_SCROLL_INSENSITIVE) {
		571	try {
		572	rs.last();
		573	result = rs.getRow();
		574	} catch (SQLException ex) {
		575	LOGGER.log(Level.INFO,
		576	"Failed to jump to end of SQL Statement [{0}], cause: {1}",
		577	new Object[]{sql, ex});
		578	}
		579	}
		580
		581	setTotalCount(result);
		582	}
		583
532	DataViewUtils.closeResources(rs);	584	DataViewUtils.closeResources(rs);
533	} else {	585	} else {
534	return;	586	return;
Lines 545-554 Link Here
545	throw sqlEx;	597	throw sqlEx;
546	}	598	}
547		599
548	// Get total row count	600
549	if (dataView.getDataViewPageContext().getTotalRows() == -1) {
550	getTotalCount(isSelectStatement(sql), sql, stmt);
551	}
552	}	601	}
553		602
554	@Override	603	@Override
Lines 588-611 Link Here
588	}	637	}
589		638
590	int pageSize = dataView.getDataViewPageContext().getPageSize();	639	int pageSize = dataView.getDataViewPageContext().getPageSize();
591	int startFrom = 0;	640	int startFrom = dataView.getDataViewPageContext().getCurrentPos();
592	if (! limitSupported \|\| isLimitUsedInSelect(dataView.getSQLString())) {
593	startFrom = dataView.getDataViewPageContext().getCurrentPos() - 1;
594	}
595		641
596	DataViewDBTable tblMeta = dataView.getDataViewDBTable();	642	DataViewDBTable tblMeta = dataView.getDataViewDBTable();
597	List<Object[]> rows = new ArrayList<Object[]>();	643	List<Object[]> rows = new ArrayList<Object[]>();
598	int colCnt = tblMeta.getColumnCount();	644	int colCnt = tblMeta.getColumnCount();
599	try {	645	try {
600	// Skip till current position	646	boolean hasNext = false;
601	boolean hasNext = rs.next();	647	boolean needSlowSkip = true;
602	int curRowPos = 1;	648
603	while (hasNext && curRowPos < (startFrom + 1)) {	649	if(rs.getType() == ResultSet.TYPE_SCROLL_INSENSITIVE \|\|
604	if (Thread.currentThread().isInterrupted()) {	650	rs.getType() == ResultSet.TYPE_SCROLL_SENSITIVE) {
605	return;	651	try {
		652	hasNext = rs.absolute(startFrom);
		653	needSlowSkip = false;
		654	} catch (SQLException ex) {
		655	LOGGER.log(Level.FINE, "Absolute positioning failed", ex); // NOI18N
606	}	656	}
		657	}
		658
		659	if (needSlowSkip) {
		660	// Skip till current position
607	hasNext = rs.next();	661	hasNext = rs.next();
608	curRowPos++;	662	int curRowPos = 1;
		663	while (hasNext && curRowPos < startFrom) {
		664	if (Thread.currentThread().isInterrupted()) {
		665	return;
		666	}
		667	hasNext = rs.next();
		668	curRowPos++;
		669	}
609	}	670	}
610		671
611	// Get next page	672	// Get next page
Lines 636-677 Link Here
636	}	697	}
637	}	698	}
638		699
639	void setTotalCount(ResultSet countresultSet) {	700	void setTotalCount(Integer count) {
640	try {	701	if (count == null) {
641	if (countresultSet == null) {	702	dataView.getDataViewPageContext().setTotalRows(-1);
642	dataView.getDataViewPageContext().setTotalRows(-1);	703	dataView.setTotalRowCount(-1);
643	dataView.setTotalRowCount(-1);	704	} else {
644	} else {	705	dataView.getDataViewPageContext().setTotalRows(count);
645	if (countresultSet.next()) {	706	dataView.setTotalRowCount(count);
646	int count = countresultSet.getInt(1);
647	dataView.getDataViewPageContext().setTotalRows(count);
648	dataView.setTotalRowCount(count);
649	}
650	}
651	} catch (SQLException ex) {
652	LOGGER.log(Level.SEVERE, "Could not get total row count ", ex); // NOI18N
653	}	707	}
654	}	708	}
655		709
656	private String appendLimitIfRequired(String sql) {	710	private Statement prepareSQLStatement(Connection conn, String sql, boolean needTotal) throws SQLException {
657	if (limitSupported && isSelectStatement(sql)) {
658	if (!isLimitUsedInSelect(sql)) {
659	sql += ' ' + LIMIT_CLAUSE + dataView.getDataViewPageContext().getPageSize();
660	sql += ' ' + OFFSET_CLAUSE + (dataView.getDataViewPageContext().getCurrentPos() - 1);
661	}
662	}
663
664	return sql;
665	}
666
667	private Statement prepareSQLStatement(Connection conn, String sql) throws SQLException {
668	Statement stmt = null;	711	Statement stmt = null;
669	if (sql.startsWith("{")) { // NOI18N	712	if (sql.startsWith("{")) { // NOI18N
670	stmt = conn.prepareCall(sql);	713	stmt = conn.prepareCall(sql, resultSetScrollType, ResultSet.CONCUR_READ_ONLY);
671	} else if (isSelectStatement(sql)) {	714	} else if (isSelectStatement(sql)) {
672	stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);	715	stmt = conn.createStatement(resultSetScrollType, ResultSet.CONCUR_READ_ONLY);
673	int pageSize = dataView.getDataViewPageContext().getPageSize();	716	int pageSize = dataView.getDataViewPageContext().getPageSize();
674		717
		718	// hint to fetch "pagesize" elements en-block
675	try {	719	try {
676	stmt.setFetchSize(pageSize);	720	stmt.setFetchSize(pageSize);
677	} catch (SQLException e) {	721	} catch (SQLException e) {
Lines 679-695 Link Here
679	LOGGER.log(Level.WARNING, "Unable to set Fetch size", e); // NOI18N	723	LOGGER.log(Level.WARNING, "Unable to set Fetch size", e); // NOI18N
680	}	724	}
681		725
682	try {	726	// hint to only query a certain number of rows -> potentially
683	if ( limitSupported && ! isLimitUsedInSelect(sql)) {	727	// improve performance for low page numbers
684	stmt.setMaxRows(pageSize);	728	// only usable for "non-total" resultsets
685	} else {	729	if (!needTotal) {
686	stmt.setMaxRows(dataView.getDataViewPageContext().getCurrentPos() + pageSize);	730	try {
		731	stmt.setMaxRows(
		732	dataView.getDataViewPageContext().getCurrentPos() + pageSize);
		733	} catch (SQLException exc) {
		734	LOGGER.log(Level.WARNING, "Unable to set Max row count", exc); // NOI18N
687	}	735	}
688	} catch (SQLException exc) {
689	LOGGER.log(Level.WARNING, "Unable to set Max row size", exc); // NOI18N
690	}	736	}
691	} else {	737	} else {
692	stmt = conn.createStatement();	738	stmt = conn.createStatement(resultSetScrollType, ResultSet.CONCUR_READ_ONLY);
693	}	739	}
694	return stmt;	740	return stmt;
695	}	741	}
Lines 704-720 Link Here
704	isResultSet = ((PreparedStatement) stmt).execute();	750	isResultSet = ((PreparedStatement) stmt).execute();
705	} else {	751	} else {
706	try {	752	try {
707	isResultSet = stmt.execute(appendLimitIfRequired(sql));	753	isResultSet = stmt.execute(sql);
708	} catch (NullPointerException ex) {	754	} catch (NullPointerException ex) {
709	LOGGER.log(Level.SEVERE, "Failed to execute SQL Statement [{0}], cause: {1}", new Object[] {sql, ex});	755	LOGGER.log(Level.SEVERE, "Failed to execute SQL Statement [{0}], cause: {1}", new Object[] {sql, ex});
710	throw new SQLException(ex);	756	throw new SQLException(ex);
711	} catch (SQLException sqlExc) {	757	} catch (SQLException sqlExc) {
712	if (sqlExc.getErrorCode() == 1064 && sqlExc.getSQLState().equals("37000")) {	758	LOGGER.log(Level.SEVERE, "Failed to execute SQL Statement [{0}], cause: {1}", new Object[]{sql, sqlExc});
713	isResultSet = stmt.execute(sql);	759	throw sqlExc;
714	} else {
715	LOGGER.log(Level.SEVERE, "Failed to execute SQL Statement [{0}], cause: {1}", new Object[] {sql, sqlExc});
716	throw sqlExc;
717	}
718	}	760	}
719	}	761	}
720		762
Lines 742-836 Link Here
742	}	784	}
743	}	785	}
744		786
745	private void getTotalCount(boolean isSelect, String sql, Statement stmt) {
746	if (! isSelect) {
747	setTotalCount(null);
748	return ;
749	}
750
751	// Case for LIMIT n OFFSET m
752	if (isLimitUsedInSelect(sql)) {
753	try {
754	String lmtStr = sql.toUpperCase().split(LIMIT_CLAUSE)[1].trim();
755	int rCnt = Integer.parseInt(lmtStr.split(" ")[0]);
756	dataView.getDataViewPageContext().setTotalRows(rCnt);
757	return;
758	} catch (NumberFormatException nex) {
759	}
760	}
761
762	// SELECT COUNT(*) FROM (sqlquery) alias
763	ResultSet cntResultSet = null;
764	try {
765	cntResultSet = stmt.executeQuery(SQLStatementGenerator.getCountAsSubQuery(sql));
766	setTotalCount(cntResultSet);
767	return;
768	} catch (SQLException e) {
769	} finally {
770	DataViewUtils.closeResources(cntResultSet);
771	}
772
773	// Try spliting the query by FROM and use "SELECT COUNT(*) FROM" + "2nd part sql"
774	if (!isGroupByUsedInSelect(sql)) {
775	cntResultSet = null;
776	try {
777	cntResultSet = stmt.executeQuery(SQLStatementGenerator.getCountSQLQuery(sql));
778	setTotalCount(cntResultSet);
779	return;
780	} catch (SQLException e) {
781	} finally {
782	DataViewUtils.closeResources(cntResultSet);
783	}
784	}
785
786	// In worse case, get the count from resultset
787	cntResultSet = null;
788	int totalRows = 0;
789	try {
790	// reset fetch size
791	int fetchSize = dataView.getDataViewPageContext().getPageSize();
792	try {
793	fetchSize = stmt.getFetchSize();
794	stmt.setFetchSize(20000);
795	} catch (SQLException sqe) {
796	// ignore
797	}
798
799	cntResultSet = stmt.executeQuery(sql);
800	while (cntResultSet.next()) {
801	totalRows++;
802	}
803	dataView.getDataViewPageContext().setTotalRows(totalRows);
804
805	// set to old value
806	try {
807	stmt.setFetchSize(fetchSize);
808	} catch (SQLException sqe) {
809	// ignore
810	}
811	return;
812	} catch (SQLException e) {
813	} finally {
814	DataViewUtils.closeResources(cntResultSet);
815	}
816
817	// Unable to compute the total rows
818	setTotalCount(null);
819
820	}
821
822	private boolean isSelectStatement(String queryString) {	787	private boolean isSelectStatement(String queryString) {
823	return queryString.trim().toUpperCase().startsWith("SELECT") && queryString.trim().toUpperCase().indexOf("INTO") == -1; // NOI18N	788	return queryString.trim().toUpperCase().startsWith("SELECT") && queryString.trim().toUpperCase().indexOf("INTO") == -1; // NOI18N
824	}	789	}
825		790
826	private boolean isLimitUsedInSelect(String sql) {
827	return sql.toUpperCase().indexOf(LIMIT_CLAUSE) != -1;
828	}
829
830	private boolean isGroupByUsedInSelect(String sql) {
831	return sql.toUpperCase().indexOf(" GROUP BY ") != -1 \|\| sql.toUpperCase().indexOf(" COUNT(*) ") != -1; // NOI18N
832	}
833
834	static String millisecondsToSeconds(long ms) {	791	static String millisecondsToSeconds(long ms) {
835	NumberFormat fmt = NumberFormat.getInstance();	792	NumberFormat fmt = NumberFormat.getInstance();
836	fmt.setMaximumFractionDigits(3);	793	fmt.setMaximumFractionDigits(3);