Skip to content

Advanced catalog (spring) sample

Jump to bottom Edit New page
Vladimír Hudec edited this page Apr 8, 2017 · 21 revisions

The tutorial is updated for SQLP 3.1, SQLMEP/SQLMOP 2.6

The next control directives have been changed: pojo to is-pojo, table to is-table, procedure to is-procedure, function to is-function.

Table of Contents

Second level cache
SQL Processor execution statistics

This is a light modification of the simple web application introduced in Catalog (Spring) Sample. The next features are presented:

  • the second level cache usage

  • the SQL Processor execution statistics

Second level cache

We can utilize the Spring cache abstraction as is described at http://static.springsource.org/spring/docs/3.1.x/spring-framework-reference/html/cache.html. The modified DAO is the next one:

public class ItemDao {

  ...
  @Cacheable(value = "itemCache", key = "#id")
  public Item findById(SqlSession session, Long id) {
    Item item = sqlFactory.getCheckedCrudEngine("GET_ITEM").get(session, Item.class, new Item(id));
    return item;
  }

  @CacheEvict(value = "itemCache", key = "#item.itemid")
  public Item create(SqlSession session, Item item) {
    sqlFactory.getCheckedCrudEngine("INSERT_ITEM").insert(session, item);
    return item;
  }

  @CacheEvict(value = "itemCache", key = "#item.itemid")
  public Item update(SqlSession session, Item item) {
    sqlFactory.getCheckedCrudEngine("UPDATE_ITEM").update(session, item);
    return item;
  }

  @CacheEvict(value = "itemCache", key = "#id")
  public void delete(SqlSession session, Long id) {
    sqlFactory.getCheckedCrudEngine("DELETE_ITEM").delete(session, new Item(id));
  }
    ...
}

The simplistic approach is used. In all CRUD operations with the exception of RETRIEVE the cache is evicted. In the case of RETRIEVE operation the output value from the SQL Processor execution (findById) is seeded into the cache. It can be verified by displaying the detail form of some cat in the browser. The next time the same cat is depicted, the SQL Processor doesn't execute the META SQL GET_ITEM. To see it, the log4j.properties has to be change to active the SQL Processor DEBUG output.

We can use for example the OSS Ehcache. To integrate it, the applicationContext-business.xml has to be modified:

<beans ...>

  <context:annotation-config />

  <!-- define cache annotation driver - for DAO class proxy target class = true, for DAO interface false (default) -->
  <cache:annotation-driven proxy-target-class="true"/>
  <bean id="cacheFactory" class="org.springframework.cache.ehcache.EhCacheManagerFactoryBean" p:config-location="classpath:ehcache.xml"/>   
  <bean id="cacheManager" class="org.springframework.cache.ehcache.EhCacheCacheManager" p:cache-manager-ref="cacheFactory"/>

...    
</beans>

The complete application context can be found at https://github.com/hudec/sql-processor/blob/master/sql-samples/catalog-spring/business/src/main/resources/applicationContext-business.xml. Of course the pom.xml must include the Ehcache dependency.

SQL Processor execution statistics

In the production the monitoring of the SQL statements execution is a must. The SQL Processor has the interface SqlMonitor, which is devoted to these purposes. In the process of all the META SQL statements execution the simple implementation of this interface SqlEmptyMonitor is called by default. We can supply an enhanced implementation of this interface SimonSqlMonitor:

public class SimonSqlMonitor implements SqlMonitor {

    private final Logger logger = LoggerFactory.getLogger(getClass());

    private String sqlName;
    private MonitoringManager manager;
    private String monitorName;

    @SuppressWarnings("unchecked")
    @Override
    public <E> List<E> runList(Runner runner, Class<E> resultClass) {

        Split split = getSplit();

        try {
            return (List<E>) runner.run();
        } catch (RuntimeException ex) {
            String name = split.getStopwatch().getName() + "." + sqlName + ".ERROR";
            incCounter(name);
            throw ex;
        } finally {
            if (split != null) {
                split.stop();

                if (logger.isDebugEnabled()) {
                    logger.debug("SQL " + sqlName + ", running for " + SimonUtils.presentNanoTime(split.runningFor()));
                }

                String name = split.getStopwatch().getName() + "." + sqlName;
                incStopwatch(name, split.runningFor());
            }
        }
    }

    @SuppressWarnings("unchecked")
    @Override
    public <E> E run(Runner runner, Class<E> resultClass) {

        Split split = getSplit();

        try {
            return (E) runner.run();
        } catch (RuntimeException ex) {
            String name = split.getStopwatch().getName() + "." + sqlName + ".ERROR";
            incCounter(name);
            throw ex;
        } finally {
            if (split != null) {
                split.stop();

                if (logger.isDebugEnabled()) {
                    logger.debug("SQL " + sqlName + ", running for " + SimonUtils.presentNanoTime(split.runningFor()));
                }

                String name = split.getStopwatch().getName() + "." + sqlName;
                incStopwatch(name, split.runningFor());
            }
        }
    }

    public SimonSqlMonitor(String sqlName, MonitoringManager manager, String monitorName) {
        super();
        this.sqlName = sqlName;
        this.manager = manager;
        this.monitorName = monitorName;
    }

    private Split getSplit() {
        if (monitorName != null && manager != null) {
            try {
                return manager.getManager().getStopwatch(monitorName).start();
            } catch (SimonException se) {
                logger.error("It's not possible to create simon '" + monitorName + "'", se);
            }
        }
        return null;
    }

    private Stopwatch incStopwatch(String name, long runningFor) {
        try {
            Stopwatch stopwatch = manager.getManager().getStopwatch(name);
            return stopwatch.addTime(runningFor);
        } catch (SimonException se) {
            logger.error("It's not possible to create simon '" + name + "'", se);
            return null;
        }
    }

    private Counter incCounter(String name) {
        return incCounter(name, 1);
    }

    private Counter incCounter(String name, long inc) {
        try {
            Counter counter = manager.getManager().getCounter(name);
            return counter.increase(inc);
        } catch (SimonException se) {
            logger.error("It's not possible to create simon '" + name + "'", se);
            return null;
        }
    }
}

In this implementation the simple monitoring API Java Simon is used. More information regarding this OSS can be found at http://code.google.com/p/javasimon/. All statistics are collected in so called Simons. There are two kinds of Simons:

  • Stopwatch - it tracks the duration of the SQL statements execution
  • Counter - it tracks the number of failures (=exceptions)

Every Simon has a name. It's related to the name of the META SQL statement. So for every META SQL statement there's one Stopwatch created. In the case of runtime error there's also one Counter created.

To activate this SqlMonitor implementation, we have to implement the factory SqlMonitorFactory:

public class SimonSqlMonitorFactory implements SqlMonitorFactory {

    private final Logger logger = LoggerFactory.getLogger(getClass());

    private MonitoringManager manager;
    private String monitorName;

    @Override
    public SqlMonitor getSqlMonitor(String name, Map<String, Object> features) {
        if (logger.isDebugEnabled()) {
            logger.debug("SQLF " + name);
        }
        return new SimonSqlMonitor(name, manager, monitorName);
    }

    @Required
    public void setManager(MonitoringManager manager) {
        this.manager = manager;
    }

    @Required
    public void setMonitorName(String monitorName) {
        this.monitorName = monitorName;
    }

}

And finally we'd like to have a unique name prefix for all Simons and also we'd like to have all Simons accessible as JMX MBeans:

public class MonitoringManager {

    Manager manager = new SwitchingManager();

    JmxRegisterCallback jmxRegisterCallback;

    public Manager getManager() {
        return manager;
    }

    public void setManager(Manager manager) {
        this.manager = manager;
    }

    public JmxRegisterCallback getJmxRegisterCallback() {
        return jmxRegisterCallback;
    }

    public void setJmxRegisterCallback(JmxRegisterCallback jmxRegisterCallback) {
        this.jmxRegisterCallback = jmxRegisterCallback;
        if (jmxRegisterCallback != null) {
            manager.callback().addCallback(jmxRegisterCallback);
        }
    }
}

To put it all together in the applicationContext-business.xml:

...
<bean id="sqlJmxRegisterCallback" class="org.javasimon.jmx.JmxRegisterCallback">
    <constructor-arg name="domain" value="sql-processor"/>
</bean>

<bean id="sqlMonitoringManager" class="org.sqlproc.sample.catalog.monitor.MonitoringManager">
    <property name="jmxRegisterCallback" ref="sqlJmxRegisterCallback" />
</bean>

<bean id="sqlMonitorFactory" class="org.sqlproc.sample.catalog.monitor.SimonSqlMonitorFactory">
    <property name="manager" ref="sqlMonitoringManager" />
    <property name="monitorName" value="catalog" />
</bean>

<bean id="sqlFactory" class="org.sqlproc.engine.jdbc.JdbcEngineFactory">
    <property name="metaFilesNames">
        <list>
            <value>statements.qry</value>
        </list>
    </property>
    <property name="filter" value="${db.type}" />
    <property name="monitorFactory" ref="sqlMonitorFactory"/>
</bean>
...

The complete application context can be found at https://github.com/hudec/sql-processor/blob/master/sql-samples/catalog-spring/business/src/main/resources/applicationContext-business.xml. Next, we have to run the Catalog sample using the next commands:

export MAVEN_OPTS="-Dcom.sun.management.jmxremote -Djava.rmi.server.hostname=localhost -Dcom.sun.management.jmxremote.port=8090 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false"
cd catalog-spring/web
mvn jetty:run-war

And, at the end, we can utilize the Groovy script sql-stats.groovy to obtain the statistics for any META SQL command:

cd catalog-spring/scripts/
./sql-stats.groovy --stopwatch INSERT_ITEM

In this case the next statistics is presented for the INSERT_ITEM META SQL statement:

Statistics for catalog.INSERT_ITEM
  counter            : 29
  total              : 108ms 169µs 578ns
  mean               :   3ms 729µs 985ns

  max                :  62ms 970µs 424ns
  maxTimestamp       : 2012.02.28 AD at 21:08:31 CET
  min                : 981µs  61ns
  minTimestamp       : 2012.02.28 AD at 21:08:31 CET
  last               :   1ms   4µs 842ns

  active             : 0
  maxActive          : 0
  maxActiveTimestamp : 1970.01.01 AD at 01:00:00 CET

  note               : null
  standardDeviation  : 1.1204351858031606E7
  variance           : 1.3002098272138262E14
  varianceN          : 1.2553750055857633E14
Add a custom footer

Table of Contents

Clone this wiki locally