Finding data that falls within a date range for a date range

The title of this is horribly confusing, but hopefully this is helpful for some people. The problem I was trying to solve is I have a report that shows where travelers are based on certain dates. For example a client requests a report to know where all of their travelers are that are traveling 9/1 – 9/2. Given that we know the departure and arrival dates of their trip this is pretty straightforward.

Given that their departure date is stored in depDateTime and their return date is stored in arvDateTime and startDate and endDate are timestamps then this will return all users who are traveling during 9/1 – 9/2

WHERE ((depDateTime BETWEEN #startDate# AND #endDate#)
    OR (arvDateTime BETWEEN #startDate# AND #endDate#))

This works great for most trips, but what if the person departs on 8/31 and returns on 9/3? The current WHERE statement will not return them as a result. I fiddled with dozens of scenarios until I realized it’s pretty simple. For the user to not show with the current logic their trip needs to begin before the startDate and end after the endDate, so it required one additional OR statement

WHERE ((depDateTime BETWEEN #startDate# AND #endDate#)
    OR (arvDateTime BETWEEN #startDate# AND #endDate#)
    OR (depDateTime < #startDate# AND arvDateTime > #endDate#))

Using hibernate default schema as a variable

Given you have set up a default_schema in your hibernate configuration

      <property name="hibernate.default_schema">mySchema</property>

Rather than writing a native SQL query like SELECT x FROM mySchema.tableName you can write your query as SELECT x FROM {h-schema}tableName rather than trying to do a find/replace when your schema name inevitably changes. Note that the . after the schema name is not only not required it will not work if it is added.

Using CSRF with Spring Security and AJAX calls

I’m fairly new to Spring and especially Spring Security, so I had quite the time figuring out why ajax calls were failing after enabling CSRF (to allow ClearTrust)

First we allowed CSRF which is a strange syntax in my opinion

<security:csrf disabled="false" />

After we did that we were getting “405: Method Not Allowed” responses on all of our ajax requests. After some googling we discovered this is a security precaution to prevent cross site attacks, which makes a lot of sense. The biggest problem was we had probably 25 ajax calls and didn’t want to spend all day updating them individually, so we ended up adding in the csrf token and header name into the head meta tags in our application. This meant they were available on every page automatically and since they stay valid as long as the users’ session this made the most sense.

Added to the header template

<meta name="_csrf" content="${_csrf.token}"/>
<meta name="_csrf_header" content="${_csrf.headerName}"/>

Added to each JavaScript template (or could be added to a global JS file as well)

var token = $("meta[name='_csrf']").attr("content");
var header = $("meta[name='_csrf_header']").attr("content");

Added to each ajax call

, beforeSend: function( xhr ) {
  xhr.setRequestHeader(header, token);

Detecting duplicate JavaScript includes on a page

I started working on a new app recently with some people mostly new to front end development and noticed that on many pages we were including the same JavaScript file multiple times, either on the same page or within an included page, so I wrote up some JavaScript (using jQuery) to detect those on the page.

The script itself is pretty straightforward.

function findDupes() {
  var allScripts = [];
  var dupe = false;
  $.each(document.getElementsByTagName("script"), function(index, value) {
    if ($.inArray(value.src, allScripts) !== -1 && value.src !== '') {
      console.log('already loaded :: ' + value.src);
    console.log('no dupes');

I turned this into a bookmarklet as well if you want to add it as a bookmark to easily run on the page

javascript: (function() {var a = []; var b = false;$.each(document.getElementsByTagName("script"), function(index, value) {if ($.inArray(value.src, a) !== -1 && value.src !== '') {console.log('already loaded :: ' + value.src);b=true;}a.push(value.src);});if(!b){console.log('no dupes');}})();

Hibernate returning BigDecimal instead of Long

I was trying to run a query to get a list of IDs from a database table and then compare that list to individual Ids on the page. The logic was pretty straightforward. Get IDs via a query and then check if other IDs are found in that list of IDs. The query itself was working fine (I’ve reduced the complexity of the query a lot), but my contains statement wasn’t finding any matches (even though they were mostly all matches). It turns out that Hibernate was returning a List of BigDecimal rather than a list of Long and my comparison was a Long value. What I needed to do was force the column to return the datatype I wanted.

addScalar(columnName, dataType)

public List<Long> retrieveOrgTypeWithoutParent() {
 Session session = getHibernateTemplate().getSessionFactory().openSession();

 List<Long> orgTypeIds = session.createSQLQuery(
  "SELECT typeId FROM org_type_cd").addScalar("typeId", StandardBasicTypes.LONG).list();

 return orgTypeIds;

Mocking session in a Spock test for a java class

Spock is an excellent test framework that is built on top of Groovy and can be used to replace JUnit for testing Java classes. The majority of spock syntax is simple, but sometimes we run into issues where it is difficult to mock a class. Session is one of those examples. Given we want to do something simple, say get the name from Spring Security in session we’d have a method call like this

private String getUserId() {
Authentication auth = SecurityContextHolder.getContext().getAuthentication();
return auth.getName();

To test this we need to mock session for this method and also for any method that calls this private method. To do so you need to mock the Authentication class and the SecurityContext class. Since this mocking is needed in multiple methods I’ve created a separate method in my spock class to mock the security context. The full test and method look like below

void "test getUserId"() {
def response = service.getUserId()
response == "hello"
def setSecurityContext() {
def authentication = Mock(Authentication)
authentication.getName() >> "hello"
def securityContext = Mock(SecurityContext)
securityContext.getAuthentication() >> authentication

Has anyone found an easier way to do this? Or is this the way it needs to be?

Moving a file from a local computer to a server through a jump server

This is a problem I’ve been routinely facing at work because we have new firewall rules and can only access new servers through a jump box. Accessing the server through ssh isn’t a problem after I’ve ssh’d into the jump server, but moving a file from my local computer to the other server is a pain. What you need to do is move the file from your local computer to an accesible directory on the jump server. For me this involves using WinSCP to drag and drop the file to my /home/mbusche directory.

Once the file has been moved to the jump server you need to use the scp command to move the file from the jump server to the other server. In my case the command looks something like this.

scp /home/mubusche/sonar.jar mbusche@cvms1255:/home/mbusche/

The syntax is scp fileLocationOnCurrentServer username@servername:folderLocationToMoveFile/

Now generally you won’t have write access to all folders under your username and may need to sudo in as another user to move the file to the directory you need. To do that you need to login to the destination server via ssh, sign in as a user with permissions and then move the folder

ssh serverName
sudo su - userWithAccess
sudo mv /home/mbusche/sonar.jar /webdata/plugins/

I’m speaking at dev.Objective() 2016

I’m happy to announce I’ve been selected to speak at dev.Objective() 2016! I will speaking on “Delivering Responsibly”.

Session Description
I’ll cover how to deliver fast, flexible and accessible websites. How you can deliver a consistent user experience to all users whether they’re on a small screen device or a 4k monitor. Whether they have a 2G connection or Google Fiber.

I’ll discuss some of the challenges we as developers face when trying to deliver responsibly and how to overcome some of those challenges. New specifications will make some of these challenges easier and I’ll cover how you can implement some of these features in non greenfield browsers.

I will discuss how http/2 mitigates some of the challenges we face, but how it doesn’t change as much as we might think when it comes to delivering responsibly.

Lastly I’ll cover the new Service Worker Specification and what it brings to the table.

Multiple insert statement without counter

I often have a need to write an INSERT statement that adds multiple rows to a database. Obviously you can run any  number of inserts separately, but that’s innefficient, so I loop over a list with a counter to know when to end. Recently I learned that the counter is unnecessary and you can create a fake SELECT statement to avoid needing a counter

INSERT INTO Attendees (Schedule_ID, Attendee_ID, Meeting_ID, User_ID)
<cfloop list="#variables[eventlocation]#" index="i">
  SELECT #Schedule_ID#, #i#, #Meeting_ID#, 1
<!--- returns no rows --->
SELECT 0, 0, 0, 0
WHERE 1 = 0

For the sake of brevity I’ve avoided omitted the cfqueryparam’s that should always be used in queries.