Tuesday, April 14, 2015

SQL: Getting the Previous and Next Records

Every so often it's necessary to compare a record with a previous or next record, based on some kind of ordering.  Many newer versions of databases support this with LAG and LEAD functions.  That seems like the ideal way to handle the problem,  but us lucky few we don't have that option so we have to do it in a round about sort of way.

Let's assume we have this table:

  system_name varchar(256),
  event_date datetime

And let's say we want to know the previous and next entries for each record based on the system_name. Here is the query we could use:

  select ROW_NUMBER() OVER (order by system_name, event_date) row_num
  , system_name, event_date
SELECT n.system_name, prev.event_date prev,  n.event_date, nex.event_date next
  ON prev.system_name = n.system_name and prev.row_num = n.row_num -1
  ON nex.system_name = n.system_name and nex.row_num = n.row_num +1
order by n.system_name, n.event_date desc;

Or feel free to play around with this SQLFiddle.

Here is a good reference here as well: http://blog.sqlauthority.com/2013/09/22/sql-server-how-to-access-the-previous-row-and-next-row-value-in-select-statement/

Also, note I ran a very similar query as above on an older version of DB2 just fine.

Wednesday, February 18, 2015

Spring Data Custom Batch Save Repository

We use Spring Data JPA for our data access framework and generally we use JpaRepository to create our repos, which is a really handy way of doing it.  Spring uses the SimpleJpaRepository for its implementation and it does have a save that allows an Iterable, but if you look at the code it just loops over the entities and calls the save method on each one.

So, if we want to save, or update, in a batch we need to use something else.  Luckily, Spring JDBC has a pretty handy way of doing it and you can add custom repos to your repository interface.

Like so:

To see the collection util that splits out our collection into batches see Split a Java List Into a List of Sublists

Wednesday, February 4, 2015

Dynamic Where Clause in JPA

Every once in a while you'll come across a scenario where you want to dynamically add parameters to a query's where clause.  For dynamic filtering I created a query generator that will simply add a segment to the where clause and the parameter to the query, and it works pretty well.

However, there are times when the scenario is a bit more complex and you'll need some more advanced logic.  For example given this entity:

Let's say we're given a list of similar data objects that have an optional userId, the last name, and the last 4 digits of the ssn. We want to match these to our User object. The rules to match up to the User is thus: use the userId if there is one, but if not match with the lastName and the last 4 digits of the ssn.

The SQL Query would look something like this:
In order to generate a query we need some conditional logic to decide if we should add the userId to a list to query the :userIds parameter or if we should add an OR clause to find a match of lastname and ssn.

The Criteria API

I've generally tried to avoid using the criteria API for my dynamic queries because it is fairly verbose, but it is an effective tool for the complex logic we're looking at. Below is an example of using the criteria API to generate the query we need.

As you can see it is pretty verbose but the flexibility to basically do whatever we want is pretty powerful.

Friday, January 30, 2015

The Bane of Continuous Improvement: Bystander Effect

This last week I heard a story about bystander effect or bystander apathy.  I've heard of this concept before, but never before thought of the correlation to corporate life.

The story I heard was about Kitty Genovese.  It's really a tragic event, but the worst part is that reportedly 38 people witnessed the attack but did nothing to help.  There are other well known examples, see wikipedia,  that demonstrate that it isn't all that uncommon for people to ignore others' problems, but generally only when there are others nearby.

Research has shown that if there is only one person to witness someone's problems then they're much more likely to help.  But, the more people there are the less likely someone is to help.  Everyone assumes that someone else will do it.

So what does bystander effect have to do with continuous improvement?

Think back through your career.  Think of a time when you, or someone you know, spotted a problem.  Did you do something about it? If so, why?  If not, why not?

I'll give you a common example in the software world.  Let's say you logged into your continuous integration server and you noticed that some tests were failing.  The customers aren't complaining and you weren't the person to break them.  What would you do, especially if you were pressed for time working towards your own deadlines?

Experience has shown that most of the time they're ignored.  And once one test is failing, then other tests start to fail.  Developers notice that failing tests aren't really that important and start to be less diligent in their own tests.  (See the broken window theory)

The more problems we don't do anything about the worse off our organization will be, and indirectly ourselves.  If we're not improving we're degrading.  So how do we stop this backward slide?

Taking Action

I'm of the opinion that in order to avoid bystander effect in an organization we need to first start with ourselves.  We need to ask ourselves how am I adding to the problem?  Do I ignore problems hoping someone else is going to take care of them even when I know that no one probably will?

I brought up our failing tests in our retrospective the other day and another developer said something that gave me pause.  "You can write up tasks for those tests," he said.  As I thought about why I didn't write up a task I guess I figured that someone else would do something about it.  A simple action could have led to the desired outcome of passing tests.  Maybe not right away, but eventually.

So the answer to avoid bystander effect is simply to take action.  However small, any action is better than doing nothing and letting things fall apart.  

Monday, January 26, 2015

Some Useful Tomcat VM Arguments

Here are some common VM arguments for Tomcat that I find myself looking up again and again.  Especially when setting up a new dev environment.  This is mostly based on Tomcat 7 but should also work for Tomcat 8, for the most part.

Another note: The configuration I'm using here is for my eclipse dev environment, but it could be adapted for any server.

More Memory

-Xms (Initial Heap size)
-Xmx (Maximum Heap Size)
-XX:MaxPermSize (Max Perm Size; tomcat 7 only)

Example:  -Xms512m -Xmx1024m -XX:MaxPermSize=512m

JMX Remote Access


Then you can then connect to your local process with JConsole with localhost:1234

Some useful links:

Tuesday, January 13, 2015

Passing Knowledge with Code

As a programmer, sometime or another you will be needed to dig into a legacy system.  Whether you're making changes or maybe trying to rewrite it, you'll need to understand what's going on.

One of the biggest problems with legacy systems is that there are few to no people who truly understand that system anymore.  The original creators have more than likely moved on leaving you to interpret what variable 'x' is supposed to mean.

As you can imagine it can be very time consuming dissecting a system that doesn't follow modern coding conventions, but we need to understand that the code we write will also someday be a legacy system.  Someone is going to inherit the system you are writing right now.  So, the question is, are they going to curse you for it or thank you for passing on your knowledge?

Self Documenting Code

One of the biggest things we can do as programmers is make sure our code describes itself.  We can do this by careful naming conventions that describe what our intent is.  A variable named 'x' gives no context or meaning, but variable 'numberOfSandwhiches'  describes pretty well what is being stored.  

Self documenting code, while verbose, is the best way to have your code describe what it is doing.  It also goes hand in hand with refactoring, or rewriting a piece of logic without changing the contract.  For example, lets say we have a code block like this:

if(hunger > 0 && bread > 2 && meat > 1 && lettuce > 0 && tomatoes > 0) {
  sandwhich = new Sandwhich(bread, meat, lettuce, tomatoes);

At first glance it may not be perfectly clear what's going on with the large list of ingredients in the if statement. But, the idea of refactoring is to make the code easier to understand so we can make it a little clearer by writing it like this.

if(hunger > 0 && ingredientsAvailable()) {
  sandwhich = new Sandwhich(bread, meat, lettuce, tomatoes);

Hopefully the refactored code is easier to understand at a glance so that whenever future me, or whoever else is going to be looking at this in the future, can quickly understand the intent.


There is always a big debate about how many comments are appropriate.  Some purists will say that if you need comments your code is not self documenting enough and needs to be refactored.  

I prefer the practical approach.  Leave comments if you need to, but make sure they actually communicate meaning and context.  People in the future might not have access to the bug tracking tools you use today so comments like '// Fixed bug 43453'  are less than useful.  

But having said comments are okay, I feel I should also give a warning.  If you find yourself leaving a lot of comments that could be a code smell and maybe some refactoring would do your code some good.  While comments are useful, most of the code should describe itself.  


To close I want to reiterate on the importance of passing your knowledge in your code.  You may not realize how much you time you'll be saving some future programmer by writing good documented code.

To help us remember to do this I recommend using fear-driven development: Always code as if the next person to maintain your code is a homicidal maniac who knows where you live.

Tuesday, December 30, 2014

Kaizen: The Key to Continous Improvement

Below are some notes from a presentation I watched about continuous improvement, which should be at the core of any organization that's serious about becoming better.

Learn more about Kaizen here:

Watch the presentation here:

  • Small and continuous improvement
  • Quick win
  • Accept Failures
  • Improve Quality
  • Empower people
  • Develop People
  • Shared long term purpose
  • Reduce waste
  • Visualize problems
  • Stop and fixing
Resistance to change
  • Use small changes
  • Influencing people over time
    • Celebrate wins
  • Sense of urgency
Plan - Do - Check - Act
  • Create a feedback loop
  • Plan
    • Clarify the problem
    • Break it down
    • Set the target to achieve
    • Analyze root causes & get metrics
    • Develop countermeasures
  • Do
    • See countermeasures through
  • Check
    • Check process and result
  • Act/Adapt
    • Standardize success
Why Kaizen Succeeds?
  •  Establish a sense of urgency
  • Create a vision
  • Empower others to act on the vision
  • Plan for and create short-term wins
  • Consolidate improvements
  • Embrace changes and new approaches
Kaizen Event
  • Improve producitivy
  • Cross functional team
  • Provide immediate value
  • Duration between 3-5 days
    • Pre Kaizen
      • ID the problem and collect metrics
    • Day 1-2 
      • document current state of the problem
    • Day 2-3
      • Root cause analysis and set goals to achieve
    • Day 3-5
      • Improve process
    • Post Kaizen
      • Analyze every month if the improvement benefits
  • Daily Kaizen
    • Focus in short term goals and quick win
    • Individual or small teams
    • hour/daily review cycle
    • reinforces the learning and development through practice of problem solving
    • create new standards improving people and the organization
    • reduce waste and learning by doing
Top 10 reasons why Kaizen fails
  • Absence of real culture - "this is the way we've always don it"
  • Politics and blame games
  • Fear culture - resistance to change
  • No follow up after a kaizen event
  • No sense of ownership/empowerment
  • Short term vision - No sense of urgency
  • Failure to identify problems - no problem is a problem
  • Failure to see root causes
  • Failure to plan and execute
  • Lack of resources -  time, knowledge
Small changes to start
  • Coach people
  • Empower people
  • Boost the team morale
  • Lead by example
  • Improve your standards
  • Visualize your flow and identify the problems
  • Always start from 1 or 2 teams
  • Celebrate success
  • Use metrics to identify improvements
  • Accept failure and learn from it
  • Agile Kaizen by Angel Mednilla
    • ISBN 978-3-642-54991-5
  • The Spirit of Kaizen by Robert Maurer
    • ISBN 978-0071796170
  • Creating a Kaizen Culture by Jon Miller, et al
    • ISBN 978-0071826853