mrbusche.com

I was tasked with creating an excel spreadsheet that mimics a template from a vendor and after a couple hours of struggling I realized it was fruitless and it’d be easier to modify the existing file. The existing file had macros that were ran to validate certain things and all I was adding was names, date of birth, weight and gender.

The following is the gist of what I ended up doing. This is 100% using the poi built into ColdFusion and does not require extra jars or adding anything to the classpath.

<cfscript>
  currentTemplate = 'filename.xlsx';
  currentFilePath = getDirectoryFromPath(getCurrentTemplatePath());
  javaFile = createObject('java', 'java.io.File').init(currentFilePath & currentTemplate);
  excelFile = createObject('java', 'java.io.FileInputStream').init(javaFile);
  xssfWorkbook = createObject('java', 'org.apache.poi.xssf.usermodel.XSSFWorkbook').init(excelFile);

  // get the first sheet index is 0 based in Java
  sheet1 = xssfWorkbook.getSheetAt(0);
  rowIterator = sheet1.iterator();
  while (rowIterator.hasNext()) {
    currentRow = rowIterator.next();

    // row 8 is where we want to start writing the names
    if (currentRow.getRowNum() > 7) {
      cellIterator = currentRow.iterator();

      while (cellIterator.hasNext()) {
        //1 - last name, 2 first name, 3 middle, 4 DOB, 5 Gender, 6 Weight
        currentCell = cellIterator.next();
        //when in doubt of methods dump out the whole java object
        //writeDump(currentCell); abort;
        currentCellNumber = currentCell.getColumnIndex();
        currentQueryRow = currentRow.getRowNum() - 7;
        // writing an empty row made the macro think I was adding a user
        if (len(trim(getTravelers['lastName'][currentQueryRow]))) {
          if (currentCellNumber == 1) {
            currentCell.setCellValue(getTravelers['lastName'][currentQueryRow]);
          }
          if (currentCellNumber == 2) {
            currentCell.setCellValue(getTravelers['firstName'][currentQueryRow]);
          }
          if (currentCellNumber == 3) {
            currentCell.setCellValue(getTravelers['middle'][currentQueryRow]);
          }
          if (currentCellNumber == 4) {
            currentCell.setCellValue(dateFormat(getTravelers['dob'][currentQueryRow], 'mm/dd/yyyy'));
          }
          if (currentCellNumber == 5) {
            currentCell.setCellValue(left(getTravelers['gender'][currentQueryRow], 1));
          }
          if (currentCellNumber == 6) {
            currentCell.setCellValue(getTravelers['weight'][currentQueryRow]);
          }
        }
      }
    }
  }

  // need to force formulas to be recalculated once the file is written
  formulaEvaluator = createObject('java', 'org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator');
  formulaEvaluator.evaluateAllFormulaCells(xssfWorkbook);
  // and once the workbook is opened
  xssfWorkbook.setForceFormulaRecalculation(true);

  // close the file input stream
  excelFile.close();
  // create a new excel file
  newFileName = 'roster.xlsx';
  newFile = createObject('java', 'java.io.File').init(currentFilePath & newFileName);
  newExcelFile = createObject('java', 'java.io.FileOutputStream').init(newFile);
  xssfWorkbook.write(newExcelFile);
  newExcelFile.close();
</cfscript>

read more

TL;DR – If you think the context-param isn’t working, make sure you’re not outputting the value on the page somewhere not inside a spring form.

I ran into an issue recently where after a security scan was ran we were told when you enter a variable into the URL i.e. ?endDate=someJavaScript it was being executed on the page. Assumed it was an easy enough fix, so googled around and found, this solution for Spring Framework

<context-param>
  <param-name>defaultHtmlEscape</param-name>
  <param-value>true</param-value>
</context-param>

I put that into the web.xml, restarted and it didn’t work, so I tried adding the page level and form level tags, but those didn’t work either. After messing around for a few hours I realized there was another place on the page where we were outputting the variable endDate, and it wasn’t inside a spring form.

What defaultHtmlEscape does is add that parameter to every spring tag in your application, pretty obvious in hindsight, but what I needed to do was make sure everywhere those values were displayed that they were displayed using a jstl c:out tag, i.e. <c:out value="${endDate}"></c:out> which also defaults to not allowing HTML to be rendered.

read more

Once again ESPN FantasyCast is broken in Chrome. If you’re using Chrome and are seeing the game score and stats on two separate lines. Hit F12 and copy the following into the Console and press enter

$('#real .progame').css('width', '101%')

This increases the container for the game by 1% and allows the page to be fully functional again.

read more

The title of this is wrong as far as terminology, but that’s what I was googling when trying to figure out my issue, so hopefully someone is helped by my struggles. I had a date 2016-06-01 13:20:24.60807 that I was trying to format into mm/dd/yyyy hh:mm:ss aa and the time was not accurate. I was using yyyy-MM-dd HH:mm:ss.SSSSSS as the format for SimpleDateFormat.

Date date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSSSS").parse(2019-06-01 13:20:24.60807);

Was my exact Code and it kept outputting 06/01/2016 01:21:24 PM which was mostly accurate but not rounding up. I tried every variation of .* including just a dot and 1-6 S’s, but couldn’t get the value to work. The fewer S I tried the more off the result was. It wasn’t until I stumbled upon this StackOverflow question that I found out the issue. In Java 7 and below Date does not have enough precision to handle nanoseconds which is what the 4th, 5th and/or 6th digit after the period was, so rather than just truncating nano seconds it was adding 60,807 ms to my current time. Makes sense once you know the issue, but this data was coming from a stored proc and I could just truncate off the nanoseconds, so I ended up taking the left 23 characters of the current Date and then applying a pattern of “yyyy-MM-dd HH:mm:ss.SSS” and then using DateUtils.round to get the correct result. The resulting code, absent my try/catches is below.

public static String parseDate(final String currentDateStr, String currentFormat, String expectedFormat) {
    String currentDate = StringUtils.left(currentDateStr, 23);
    Date date = DateUtils.round(date, Calendar.SECOND);
    return new SimpleDateFormat(expectedFormat).format(date);
  }
parseDate(processDate, "yyyy-MM-dd HH:mm:ss.SSS", "MM/dd/yyyy hh:mm:ss a");

read more

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#))

read more

Given you have set up a default_schema in your hibernate configuration

<hibernate-configuration>
   <session-factory>
      <property name="hibernate.default_schema">mySchema</property>
   </session-factory>
</hibernate-configuration>

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.

read more

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);
}

read more

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);
      dupe=true;
    }
    allScripts.push(value.src);
  });
  if(!dupe){
    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');}})();

read more

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();

 session.close();
 return orgTypeIds;
};

read more

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"() {
  given:
  setSecurityContext()
  when:
  def response = service.getUserId()
  then:
  response == "hello"
}

def setSecurityContext() {
  def authentication = Mock(Authentication)
  authentication.getName() >> "hello"
  def securityContext = Mock(SecurityContext)
  securityContext.getAuthentication() >> authentication
  SecurityContextHolder.setContext(securityContext)
}

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

read more