Real Time Dashboards 3 – Backend

For a quick refresh, just go back to the intro post where we state what we intend to do.

Or do this instead and get up to speed faster.  All components are linked to for if you do not have the software we’re using…

1. Create a database named ‘genericmonitor’ in MySQL.

2. Run these two scripts (step.sql and log-revised-sql) to create both tables we have talked about so far.  Yes, I know, these are not sql files.  WordPress wouldn’t let me upload as is so I hanged extension.  Just open them however you please and run scripts in a query window of your preference.  Have you used Navicat Lite yet?

3. Do you have Eclipse yet?

4. Maven?

5. Lastly, update Eclipse with the m2eclipse maven plugin.

In this post, we are going to setup the back-end of our dashboard.  This back-end application will retrieve the status of our main operation (a 100 point car inspection) real time. I.E. – this app will read information from a table used as a log in the database.  Do not forget that there is a main process occurring that is writing ti this same database.  Our application monitors the main process independently.

Since I am working in a Java shop, it is convenient to write this application in Java. It would have been perfectly ok to do a Rails backend or, even better, a Coldfusion backend instead. ColdFusion and Flex integration does allow lots of common concerns to be leveraged by each other. For this post, rudentary Java (just wait) will suffice.

With minor initial complications, Hibernate eases managing database connectivity, querying databases and returning resultsets to our apps. Thes features are only scratching the surface on features Hibernate does. I like it most because it makes Java applications more lightweight than with JDBC. For what we are doing here, IBATIS may be better and is worth exploring.

Hibernate does require a number of dependencies be added to our Java project.  You can go to Hibernate’s Home Page and search for these to be included in your project.  After all these are downloaded, you can open Eclipse, create a new Java Project.  Next, right-click our project, select Properties, click Java Build Path on hte left list of options, then select Libraries.  In here, you can add all your jars.

Alternatively, and this is what I did, we can use Maven to manage our dependencies. Installation is brisk and the eclipse plugin is a no brainer either.

Afterwards, it is a matter of stating our project dependencies in our POM file. It is worth noting that Maven will manage dependmecies for not only hibernate but for any other libraries you may want to add to our project.

In Eclipse, choose to create a new project, then select Maven Project and follow the prompts as you would for a regular Java project.  For this post, it will suffice to choose ‘maven-archtype-quickstart’ at the prompt (it may be the default) and finish. You should end up with a standard looking project which includes a pom.xml file in the root of your project.

To add hibernate to our project, we need to add the hibernate and mysql artifacts.  This is what my file ends up looking like:

<project xmlns=http://maven.apache.org/POM/4.0.0
    xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
    http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>genericMonitor</groupId>
  <artifactId>genericMonitor</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <dependencies>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate</artifactId>
            <version>3.1.3</version>
            <exclusions>
                <exclusion>
                    <groupId>javax.transaction</groupId>
                    <artifactId>jta</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <dependency>
            <groupId>javax.transaction</groupId>
            <artifactId>jta</artifactId>
            <version>1.1</version>
        </dependency>
  </dependencies>
</project>

Feel free to copy paste my version into your file.

Please excuse my fantastic WordPress formating… In Eclipse right click pasted text and select code > format or format… it will make things decent enough for you to read.

Note that my project name in pom is genericMonitor.  You would want yours to match name you chose.  Saving this pom file will prompt the maven installation to fetch the artifacts stated and all the dependencies these require.

Notice that I am excluding javax.transaction for Hibernate and stating a specific version afterwards.  This is to resolve a conflict I was getting among jars downloaded.  Feel free to fiddle with these, ymmv.

When the plugin finishes (if you used my pom), you should have Hibernate dependencies added to your project as shown in the image that follows:

Not too bad eh? Next, we need to create a hibernate configuration file and place it wherever your Java packages are going to reside.  Create a new file and call it hibernate.cfg.xml. This file holds all the configuration information Hibernate needs to care for transactions, database connections, database credentials, etc.  Here is mine as a sample you can use as a starting point:

<?xml version="1.0" encoding="UTF-8"?>
  <!DOCTYPE hibernate-configuration PUBLIC
  "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
  "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
  <hibernate-configuration>
    <session-factory>
      <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
      <property name="connection.url">jdbc:mysql://localhost/genericmonitor</property>
      <property name="connection.username">root</property>
      <property name="connection.password"></property>
      <property name="connection.isolation">1</property>
      <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
      <property name="hbm2ddl.auto">update</property>
      <property name="show_sql">true</property>
      <property name="connection.pool_size">5</property>
      <property name="cache.use_second_level_cache">false</property>
    </session-factory>
  </hibernate-configuration>

Worth noting here that my database is named genericmonitor with a username of root and no password.  Revise as necessary.

Now to the Java part.  In the same location as the config file we just created, I create a package named monitor and added a class named MonitorDao to it like this:

This is as complicated an app as we will write for this.  This class will hold all methods we need to access information contained in our genericmonitor database.  Remember that another separate application is doing all the work.

We are going to need (at least) the following methods for our monitor:

  1. getSteps() – Gets all Process Steps and each processes parent process (if any).
  2. getStepsByParentId(Integer parentId) – Gets all the processes for a given parent step.
  3. getStepByLogId(Integer logId) – Gets a process by logId.
  4. getActiveStep() – Gets the active step (if any).
For each of these, we are going to perform a sql query to return the result-set implied by the method name.  These are simple statements as follows:
getSteps()

SELECT  b.id as 'detailId',
        a.label as 'section',
        b.label as 'detail',
        b.workEffort as 'workEffort'
FROM    step a INNER JOIN step b ON a.id = b.parentId
ORDER BY b.id

getStepsByParentId(parentId)

SELECT b.id as 'detailId',
       a.label as 'section',
       b.label as 'detail',
       b.workEffort as 'workEffort'
FROM   step a INNER JOIN step b ON a.id = b.parentId
WHERE  a.id = ?
ORDER BY b.id

getStepByLogId(logId)

SELECT a.id as 'sectionId',
       b.id as 'detailId',
       a.label as 'section',
       b.label as 'detail',
       b.workEffort as 'workEffort'
FROM   step a INNER JOIN step b ON a.id = b.parentId
       INNER JOIN log c ON b.id = c.id AND c.id = ?

getActiveStep()

SELECT a.id as 'sectionId',
       b.id as 'detailId',
       a.label as 'section',
       b.label as 'detail',
       b.workEffort as 'workEffort'
FROM   step a INNER JOIN step b ON a.id = b.parentId
       INNER JOIN log c ON b.id = c.id and c.active = 1

For each of these, we need to write a java method that will return the result-set of these queries.  Using  getStepsByParentId() illustrates how we will do this:

	public static List getStepsByParentId(Integer parentId)
	{
		Integer myParentId = 0;
		
		if (parentId != null)
		{
			myParentId = parentId;
		}
		
		Session thisSession = null;
		SessionFactory thiSessionFactory = 
			new Configuration().configure().buildSessionFactory();
		
		thisSession  = thiSessionFactory.openSession();
		List steps = null;
		String SQL_QUERY = 	
			"SELECT	" +
			"b.id as 'detailId', " +
			"a.label as 'section', " +
			"b.label as 'detail', " +
			"b.workEffort as 'workEffort' " +
			"FROM step a INNER JOIN step b ON a.id = b.parentId " +
			"WHERE a.id = ? " +
			"ORDER BY b.id";

		try
		{
			Query thisQuery = thisSession.createSQLQuery(SQL_QUERY)
				.setInteger(0,myParentId);
			steps = thisQuery.list();
		} 
		catch (HibernateException e)
		{
			e.printStackTrace();	
		}
		finally
		{
			if (thisSession != null)
			{
				try
				{
					thiSessionFactory.close();
					thisSession.close();
				}
				catch (HibernateException e)
				{
					e.printStackTrace();
				}
			}
		}
		return steps;
	}

See, that is what I was referring to as rudimentary.  Anyways,  I am including my code: MonitorDao.java. As previously, just change the file extension from .doc to .java this time and all will be well.  If you are still with me, right-click your java file and run it from Eclipse.  You should see the console window come up and go thru all the methods being called.

That is all there is to this part.  I am certain there are many ways of grabbing information from a database.  This is but one of the hopefully easier ones.  It all depends on what your shop works with.  Most importantly, it doesn’t matter as long as you can bring back result-sets from your database.  The Flex Dashboard will not know the difference.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s