Performance over Hibernate 4 (scroll) and JBoss EAP Datasource Tests

Hi all,
Thanks again for reading my post...

Now, i would like to show you the code above to scroll data using hibernate and how to see the results into JBoss EAP for Datasource jca component.

First of all, let's create the handler class...

public interface RowHibernateHandler<T> extends Serializable {

    /**
     * Do something with the data
     *
     * @param row
     */
    void doWithOneRow(T row);

    // dispose if you like this...
    //void dispose();
}

The handler implementation:

class TestRowHandler implements RowHibernateHandler<Object[]> {
    /**
     * serial
     */
    private static final long serialVersionUID = 1795300378307243234L;

    /**
     * Results
     */
    private List<Object[]> list = new ArrayList<>();
   
    /**
     * Index - removing
     */
    private List<Object[]> listIndexObjects = new ArrayList<>();

    /**
     * Total Count
     */
    private int count = 0;

    @Override
    public void doWithOneRow(Object[] row) {
        list.add(row);
        count++;
    }

    /**
     * Remove
     */
    public void removeIndexObjects() {
        Iterator<Object[]> ite = listIndexObjects.iterator();
        while (ite.hasNext()) {
            list.remove(ite.next());
        }

        listIndexObjects.clear();
    }

    /**
     * Index
     *
     * @param index
     */
    public void indexToRemove(Object[] obj) {
        listIndexObjects.add(obj);
    }

    /**
     * Empty ?
     *
     * @return
     */
    public boolean isEmpty() {
        return list.isEmpty();
    }

    /**
     * Clear
     *
     * @return
     */
    public void clear() {
        list.clear();
        listIndexObjects.clear();
    }

    /**
     * Remove
     *
     * @param i
     */
    public void remove(int i) {
        list.remove(i);
    }

    /**
     * GET
     *
     * @param index
     * @return
     */
    protected Object[] get(int index) {
        return list.get(index);
    }

    /**
     * List
     *
     * @return
     */
    public List<Object[]> getList() {
        return list;
    }

    /**
     * Original Size
     *
     * @return
     */
    public int size() {
        return count;
    }
}

Method using Hibernate/JPA classes (SLSB EJB 3):

    public void createScrollQueryResults(
            CriteriaQuery<Object[]> query, RowHibernateHandler<Object[]> handler) throws Exception {

        TypedQuery<Object[]> typedQuery = entityManager.createQuery(query);

        typedQuery.setFlushMode(FlushModeType.AUTO);
        typedQuery.setHint(org.hibernate.annotations.QueryHints.READ_ONLY, true);
        typedQuery.setHint(org.hibernate.annotations.QueryHints.CACHE_MODE, org.hibernate.CacheMode.IGNORE);
        typedQuery.setHint(org.hibernate.annotations.QueryHints.TIMEOUT_HIBERNATE, 180000); // 3 minutes
        typedQuery.setHint(org.hibernate.annotations.QueryHints.TIMEOUT_JPA, 180000); // 3 minutes
        // fetch with 1000 elements...sometimes it is a terrible choice!! Reduce the value...
        typedQuery.setHint(org.hibernate.annotations.QueryHints.FETCH_SIZE, 1000);

        org.hibernate.Query hibernateQuery =
                typedQuery.unwrap(org.hibernate.Query.class);

        // data will not be rendered over memory...thats nice!!!
        org.hibernate.ScrollableResults cursor =
             hibernateQuery.scroll(org.hibernate.ScrollMode.SCROLL_INSENSITIVE);
      
        // it is possible to see the last page - count objects result
        if (cursor.last()) {
            System.out.println("Total lines: " + cursor.getRowNumber() + 1);
        }
        cursor.first();
        scrollHandleResults(cursor, handler);
    }
   
    private <T extends Serializable> void scrollHandleResults(
            ScrollableResults results, RowHibernateHandler<T> handler) {

        try {
            while (results.next()) {
                T row = (T) results.get(0);
                handler.doWithOneRow(row);
            }
          
        } finally {
            if (results != null) {
                results.close();
            }
        }
    }

How to Monitor and see the results into JBoss EAP...

- Logger

            <logger category="org.hibernate.stat">
                <level name="DEBUG"/>
            </logger>
            <logger category="org.hibernate.loader.Loader"> <!-- warnings sobre lock -->
                <level name="ERROR" />
            </logger>
            <logger category="org.jboss.jca">
                <level name="TRACE"/>
             </logger>
             <logger category="jboss.jdbc.spy">
               <level name="TRACE"/>
            </logger>

- Datasource (Oracle XE driver ojdbc6)

 <datasource jta="true" jndi-name="java:/jdbc/test" pool-name="jdbc/test"
        enabled="true" use-ccm="false" statistics-enabled="true" spy="true">
                    <connection-url>jdbc:oracle:thin:@//localhost:1521/xe</connection-url>
                    <driver>oracle</driver>
                    <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
                    <pool>
                        <min-pool-size>25</min-pool-size>
                        <max-pool-size>40</max-pool-size>
                        <prefill>true</prefill>                       
                        <flush-strategy>IdleConnections</flush-strategy>
                    </pool>
                    <security>
                        <user-name>test</user-name>
                        <password>test</password>
                    </security>
                    <validation>
                        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
                        <stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>
                        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
                    </validation>
                    <statement>
                        <share-prepared-statements>false</share-prepared-statements>
                    </statement>
                </datasource>



Test it...
   
       CriteriaBuilder builder = entityManager.getCriteriaBuilder();
       CriteriaQuery<Object[]> query = builder.createQuery(Object[].class);
       // TODO fill the query - add predicates...
       final TestRowHandler handlerResults = new TestRowHandler();

       createScrollQueryResults(query, handlerResults);
  
        if (handlerResults.isEmpty()) {
            LOGGER.warning("empty!!");
        }

       // handlerResults.dispose();
 

That's it!!!
Thanks a lot...see you on the next post!
Regards...

"My dream... take an IT project outside Brazil to help people and enjoy with Java technologies (JEE middleware solutions)"