Adding rows to an excel spreadsheet using apache poi

In a recent project for a client I was tasked with modifying an existing excel spreadsheet to add data from a query. Being familiar with Java and ColdFusion I assumed this would be a pretty trivial exercise. Read the existing file, get the sheet and then write the data, but I ran into an issue where adding rows using shiftRows didn’t make them writable and/or visible to apache poi. I realized I needed to literally add the rows and the columns to the excel spreadsheet to be able to change the values. Not a big deal code-wise and also really fast to complete, but frustrating to figure out.

currentCharterTemplate = 'existingWorkbook.xlsx';
currentFilePath = getDirectoryFromPath(getCurrentTemplatePath());
javaFile = createObject('java', 'java.io.File').init(currentFilePath & currentCharterTemplate);
excelFile = createObject('java', 'java.io.FileInputStream').init(javaFile);
xssfWorkbook = createObject('java', 'org.apache.poi.xssf.usermodel.XSSFWorkbook').init(excelFile);

summarySheet = xssfWorkbook.getSheetAt(0);
totalColumns = 12;
rowsToAdd = query.recordCount;
//add enough rows/columns to the spreadsheet to handle the record count of the query and the sort fields
for (rows = 1; rows <= rowsToAdd; rows++) {
  summarySheet.createRow(rows);
  theCurrentRow = summarySheet.getRow(rows);
  for (columns = 0; columns <= totalColumns; columns++) {
    theCurrentRow.createCell(columns);
    theCurrentRow.getCell(columns);
  }
}

Finding the version of a jar file

I needed to find the version of a jar file I was using to help out our middleware team at work and had never needed to do so before, so I went to googling. I figured it would be pretty straightforward and it is, once you find the correct commands. I initially started out unzipping the jar file and found the version that way through the manifest file by looking at the Implementation-Version. It wasn’t too much work, but I knew there had to be a better way and I stumbled upon this stackoverflow answer

If you open a terminal window and cd to the directory with your jar file you can do unzip -p file.jar META-INF/MANIFEST.MF to view the manifest file without manually unzipping the file.

Spring Escape HTML and prevent XSS attacks

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.

Using hibernate default schema as a variable

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.

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

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

 session.close();
 return orgTypeIds;
};

Websphere unable to check if application exists

I’m still fairly new to Websphere, but this seemed like a pretty common problem someone would have and I couldn’t find anything that properly explained the issue to me. I was running into an issue where WebSphere kept saying it was unable to check if application exists when it was being deployed. I thought initially this was a permissions problem, so I fooled around with permissions but had the same issue. If you scroll down a ways on the stack trace it says

insufficient or empty credentials

I double checked my configuration in Hudson/Jenkins and testing the connection worked fine, so I continued searching for other solutions. There are literally 3 search results on google for “unable to check if application exists” and 0 results on bing. Most of the other articles I found offered no help. Eventually I circled back around and decided to look at the access level my user had in WebSphere. Turns out it wasn’t listed as a user! I’m still not sure how testing the connection in Hudson worked but I wasn’t listed as a user in WebSphere. I checked this half a dozen times just to make sure I wasn’t mistaken.

To add a user in WebSphere

  1. Users and Groups
  2. Administrative user roles
  3. Select the Administrator role
  4. Enter the user to search for
  5. Use the arrow to move the user to the Mapped to role box
  6. Click OK
  7. Click Save

Step 7 is super important because it requires an additional save than most programs do and I find myself forgetting to do the second confirmation too often.¬†After you’ve added the appropriate permissions redeploy your ear and you should be all set.

Full stack trace of the error I was getting

[INFO] No custom module-to-server mappings found -- using default configuration.
com.insertcompanynamehere.websphere.deployment.client.DeploymentException: Unable to check if application exists 'NewBusiness'
at com.insertcompanynamehere.websphere.deployment.client.was61.DeploymentManager.applicationExists(DeploymentManager.java:265)
at com.insertcompanynamehere.websphere.deployment.WebsphereDeploymentTask.execute(WebsphereDeploymentTask.java:59)
at com.insertcompanynamehere.hudson.plugin.WebSphereDeploymentBuilder.perform(WebSphereDeploymentBuilder.java:194)
at hudson.tasks.BuildStepMonitor$3.perform(BuildStepMonitor.java:45)
at hudson.model.AbstractBuild$AbstractBuildExecution.perform(AbstractBuild.java:761)
at hudson.model.AbstractBuild$AbstractBuildExecution.performAllBuildSteps(AbstractBuild.java:721)
at hudson.model.Build$BuildExecution.post2(Build.java:183)
at hudson.model.AbstractBuild$AbstractBuildExecution.post(AbstractBuild.java:670)
at hudson.model.Run.execute(Run.java:1743)
at hudson.model.FreeStyleBuild.run(FreeStyleBuild.java:43)
at hudson.model.ResourceController.execute(ResourceController.java:89)
at hudson.model.Executor.run(Executor.java:240)
Caused by: com.ibm.websphere.management.exception.AdminException:
at com.ibm.websphere.management.application.AppManagementProxy.proxyInvoke(AppManagementProxy.java:189)
at com.ibm.websphere.management.application.AppManagementProxy.checkIfAppExists(AppManagementProxy.java:266)
at com.insertcompanynamehere.websphere.deployment.client.was61.DeploymentManager.applicationExists(DeploymentManager.java:263)
... 11 more
Caused by: javax.management.JMRuntimeException: ADMN0022E: Access is denied for the checkIfAppExists operation on AppManagement MBean because of insufficient or empty credentials.
at com.ibm.ws.management.connector.soap.SOAPConnectorClient.handleAdminFault(SOAPConnectorClient.java:948)
at com.ibm.ws.management.connector.soap.SOAPConnectorClient.invokeTemplateOnce(SOAPConnectorClient.java:916)
at com.ibm.ws.management.connector.soap.SOAPConnectorClient.invokeTemplate(SOAPConnectorClient.java:682)
at com.ibm.ws.management.connector.soap.SOAPConnectorClient.invokeTemplate(SOAPConnectorClient.java:672)
at com.ibm.ws.management.connector.soap.SOAPConnectorClient.invoke(SOAPConnectorClient.java:658)
at com.ibm.ws.management.connector.soap.SOAPConnectorClient.invoke(SOAPConnectorClient.java:480)
at $Proxy43.invoke(Unknown Source)
at com.ibm.ws.management.AdminClientImpl.invoke(AdminClientImpl.java:224)
at com.ibm.websphere.management.application.AppManagementProxy.proxyInvoke(AppManagementProxy.java:183)
... 13 more
Build step 'IBM WebSphere v8.0 Deployment' changed build result to FAILURE
Finished: FAILURE

Adding the stack trace to a Java Server Page error page

I’ve recently been switched to a Java project and one of the most frustrating parts of the application (other than barely knowing Java) is the error page would only show a “An error as occurred” message and not the actual stack trace. In production this is a perfectly valid scenario, but when developing having to go back to RAD and scroll through the console to find the error message was wasting a lot of time, so after a decent amount of googling I found a way to dump the stack trace to the page.

The code ended up looking like this. In the message board post they used exception instead of error, but hopefully you get the point.

<jsp:useBean id="error" scope="request" class="java.lang.Throwable" />
  <%
    Object billingError = request.getSession().getAttribute(RequestParamConstants.UNKNOWN_BILLING_ERROR);
    error = (Throwable)billingError;
  %>
  <%@page isErrorPage="true" import="java.io.*"%>
  <pre>
    <%
      error.printStackTrace(new PrintWriter(out));
    %>
  </pre>

A while after implementing this I ran into an error where the first line was about 400 characters long, so I had to scroll way over to the right. This is because by default the pre tag does not wrap, so I added this simple css fix which allows the pre tag to wrap

pre { white-space: pre-wrap;}