Skip to main content

Posts

Showing posts from April, 2016

Comparison Operators and Between in Relational Databases

Sometimes our techie-brains convert a concept clearly articulated in plain English into unnecessarily mathematically expressed code.

For example, "I want all records with an amount between 1000 and 1999" can become "x >= 1000 AND x <= 1999"

But modern RDBMS database systems give us an English equivalent that saves translating the concept "between" into "greater than or equal to this, and less than or equal to that."

It's the BETWEEN function, and it is the same across SQL Server, PostgreSQL, MySQL, and Oracle. Maybe others, too, but those are the ones that I have used in the past.

BETWEEN is of the form:
MyTestValue BETWEEN StartValue AND EndValue
It is an inclusive comparison, equivalent to using >= and <= So these generate the same results:
SELECT * FROM MyTable WHERE MyValue BETWEEN 100 AND 1000
vs
SELECT * FROM MyTable WHERE MyValue >= 100 AND MyValue <= 1000

What are the advantages of BETWEEN? It's a little less typing…

Using Java 8 to Refactor an Iteration over a Collection

On a recent project, I came across an ideal algorithm to (re-)write using Java 8's Streams API and Lambda expressions. Here is the original code, modified to protect the client:

public String extractAllToAccountsAsCSV(List<AccountPair> collectionOfPairedAccounts)
{
String result = "";
for (AccountPair pair : collectionOfPairedAccounts)
{
if (result.length() > 0)
result += ",";
result += String.valueOf(pair.getToAccount());
}
return result;
}

This simple algorithm iterates over a collection of AccountPairs, which are objects of a data structure that associates two accounts. Its goal is to produce a comma-separated String output of all values of one kind of account in the AccountPair, a CSV that the calling class will consume in some way.

The description sounds right in the wheelhouse of Java 8. It would not be a refactoring that alters the structure of the class, per se, but it does change the detailed design inside the public-facing API, makes it cleaner and more…

How to do a Case-Sensitive SQL Server Query

When selecting data in SQL Server, the WHERE-clause has, in my experience, ignored the case and done a case-insensitive comparison. For example, my Individuals table may have a mix of upper and lower cases, depending on how people entered their names:

SELECT firstname, lastname 
FROM Individuals
WHERE lastname = 'Timmins'

firstnamelastname
-----------------
TammyTIMMINS
Tellietimmins
TommyTimMinS
TubbyTimmins

My query selected all four people with the last name "Timmins" without caring if it was all capitals, all lower case, or a mixture. I rely on that behavior all the time.

But once in a while, we need to do a case-sensitive query. How can we, for example, find Tommy TimMinS and correct it to Tommy Timmins?

Testing-in-the-Trenches: Spin-off a New Class

Testing in the Trenches (TinT) is an occasional series based on my experiences promoting and coaching Unit Testing on real projects, ones where the team or management do not always embrace the philosophy or practices of Unit-Testing.
Technique: Spinning-off a New Class Imagine you believe in the principles of automated unit testing. You want to write tests to prove the correctness of your code.
But imagine that you are working in a code base with pockets, sections, even whole packages of code that has no tests, possibly even code whose design is hostile to being put into a test harness.
Imagine that the enhancement or bug fix on your plate involves changing existing code that has no current test coverage, or not very much, or maybe some tests but they are poor quality. Maybe they have a lot of dependencies, or it takes a lot of work to set them up, or they do too many things on construction.
This imaginary scenario is very common on some projects.

Faced with such a scenario, as practi…