Pivot Tables

Suppose you have the following table named TRANSACTIONS, which has two columns, ITEM_ID and PRICE. Below is an example of the table:

ITEM_ID	PRICE
1	$5.00
1	$6.00
2	$5.00
3	$7.00
3	$2.00
4	$3.00

A user would like to see this data pivoted, where each distinct ITEM_ID becomes a column header, and the data becomes the sum of PRICE grouped by ITEM_ID. Below is the desired result:

1	2	3	4
$11.00	$5.00	$9.00	$3.00

You could probably achieve this result by writing four separate queries then joining the individual results together but there is perhaps a better way to do it. The trick is to use the CASE keyword in combination with the SUM aggregate function. Here’s how to do it:

SELECT
sum(case when ITEM_ID = 1 then PRICE else 0 end) AS [1],
sum(case when ITEM_ID = 2 then PRICE else 0 end) AS [2],
sum(case when ITEM_ID = 3 then PRICE else 0 end) AS [3],
sum(case when ITEM_ID = 4 then PRICE else 0 end) AS [4]
FROM TRANSACTIONS

Prevent SQL Injection Attacks in Java

To prevent hackers from exploiting your Java website:

NEVER do this (manually concatenate SQL):

String sql = "select 1 from user where userId='" + userId + "' and password='" + password + "'";

Instead do this (use prepared statements):

String sql = "select 1 from user where userId = ? and password = ?";
PreparedStatement prepStmt = con.prepareStatement(sql);
prepStmt.setString(1, userId);
prepStmt.setString(2, password);

When you use prepared statements it passes the parameters directly to the database independent of the SQL statement so that the hacker never has a chance to alter them.

Clean your JSP Code with JSTL & JSP EL

Note: After writing this blog I realized 3rd party templating engines like Velocity or FreeMarker are viable alternatives to JSTL & JSP EL. However JSTL & JSP EL are the standard and have official support while the others do not. I may write a couple follow up blogs in the future detailing how to use Velocity or FreeMarker.

Below is an example of mixing HTML with JSP scriptlets and expressions:

<% if (user.getRole().equals("member")) { %>
    <p>Welcome, <%=user.getName() %>!</p>
<% } else { %>
    <p>Welcome, guest!</p>
<% } %>

The mixing and HTML's syntax and Java's syntax makes the code ugly and hard to read and understand. It can also encourage bad coding practices; it is sometimes tempting to add additional business logic in your JSP files. JSTL and JSP EL were created to remedy this situation.

JSTL (JSP Standard Tag Library) is a standardized tag library that can be used to replace JSP scriptlet tags (<% %>) inside of JSP files. It has tags for all the most common operations necessary to create your JSP file (conditional tags, looping tags, formatting tags, etc).

JSP EL (JSP Expression Language) is an expression language that can be used to replace JSP expression tags (<%= %>) inside your JSP files. JSP EL has many convenient shortcuts and allows for a much simpler syntax to manipulate application data.

JSTL has integrated support for JSP EL; JSP EL can be used inside of the JSTL tag attributes. If your application server supports JSP 2.0 specification or greater then JSP EL can also be used outside of the JSTL tag attributes; otherwise you will need to wrap your expression using the JSTL out tag (see example below). Using JSTL combined with JSP EL the above code becomes:

<c:choose>
<c:when test="${user.role == 'member'}">
    <p>Welcome, <c:out value="${user.name}"/>!</p>
</c:when>
<c:otherwise>
    <p>Welcome, guest!</p>
</c:otherwise>
</c:choose>

As you can see the code is much improved compared to its original state. Finally, you can rid yourself of all the ugly JSP tags.

JSTL may or may not be included with your application server. If it is not included you can download the JAR files and add them to your project's WEB-INF/lib folder or to your application server's shared library folder. The JSTL version you need to download will vary based on which JSP specification version your application server supports. Below is a table that maps the JSTL version to the JSP specification version:

Servlet Version	JSP Version	JSTL Version	Java EE Version
2.5	2.1	1.2	5
2.4	2.0	1.1	1.4
2.3	1.2	1.0	1.2

Note: I am having great difficulty trying to find downloads for the older versions of JSTL. The download links on the Jakarta site are broken. I have not found a solution to this problem yet.

For more information see the following links:

• http://today.java.net/pub/a/today/2003/10/07/jstl1.html
• http://www.mularien.com/blog/2008/04/24/how-to-reference-and-use-jstl-in-your-web-application/

Portal Breadcrumbs

In a portal application all the navigation is theoretically pre-defined, since the structure of the portal application is defined in a hierarchal way. For example, to the right is the structure of the portal file created using Weblogic Portal 8.1.

To create the breadcrumbs for this portal application we simply have to iterate through the structure of the portal file, starting with the current page and working upwards towards the root.

The code to accomplish this is below (slight tweaks may be required for your own use). This code is specific to Weblogic Portal 8.1 but the basic concept should be applicable to all portal platforms. In this particular example all the code is saved in a single JSP file. (Sorry, I know the code is ugly):

<%@ page import="com.bea.netuix.servlets.controls.page.PagePresentationContext,
     com.bea.netuix.servlets.controls.page.BookPresentationContext,
     com.bea.netuix.servlets.controls.application.DesktopPresentationContext
     com.bea.netuix.servlets.controls.PresentationContext,
     com.bea.portlet.PageURL,
     java.util.List" %>
<%

ArrayList breadcrumbs = new ArrayList();

PagePresentationContext pageCtx = PagePresentationContext.getPagePresentationContext(request);

do
{
 PagePresentationContext parentPageCtx = pageCtx.getParentPagePresentationContext();

 String title;
 String url; 

 if (pageCtx instanceof BookPresentationContext)
 {
  title = pageCtx.getTitle();
  url = PageURL.createPageURL(request, response, ((BookPresentationContext)pageCtx).getDefaultPage()).toString();
 }
 else
 {
  title = pageCtx.getTitle();
  url = PageURL.createPageURL(request, response, pageCtx.getDefinitionLabel()).toString();
 }

 if (parentPageCtx == null)
 {
  title = "Home";
 }

 if (!(
   parentPageCtx != null &&
   parentPageCtx instanceof BookPresentationContext &&
   ((BookPresentationContext)parentPageCtx).getDefaultPage().equals(pageCtx.getDefinitionLabel())
  ))
 {
  breadcrumbs.add(new String[] {title, url});
 }

 pageCtx = parentPageCtx;
}
while (pageCtx != null);

%>

<p class="x-small bold">

<%

for (int i = breadcrumbs.size() - 1; i >= 0; i--)
{
 String title = ((String[])breadcrumbs.get(i))[0];
 String url = ((String[])breadcrumbs.get(i))[1];

 if (i > 0)
 {
 %>
  <a class="breadcrumb-hyperlink" href="<%=url%>"><%=title%></a> >
 <%
 }
 else
 {
 %>
  <%=title%>
 <%
 }
}

%>

</p>

Javascript Obfuscator

Have some sensitive javascript code?

In general this is a bad idea since javascript code is client-side and therefore can easily be manipulated or hacked by the user. If you are worried about your javascript code being tampered with you should probably move it to the server-side.

In some rare cases though it may be helpful to scramble your javascript source code so that it is much more difficult to hack. Again, we can never make javascript code 100% secure (the process of scrambling your javascript code is reversible), but you can at least make it difficult for the user to access it. This is enough to deter the average computer-nerd-wannabe. The process of scrambling the code is called obfuscation. There is a free javascript obfuscator available at the following link:

http://www.javascriptobfuscator.com/default.aspx

Integrate Java with Microsoft SQL Server Reporting Services 2005/2008

The best way to accomplish this is through the use of the ReportExecution2005 web service that is provided out of the box by Microsoft SQL Server Reporting Services.

1. Start Eclipse and create a new workspace named ssrsClient
   
   
2. Create a new Java project called ssrsClient
   
   
3. Configure the advanced Axis settings so that "Generate code for all elements, even unreferenced ones" is checked, and so that "Timeout in seconds" is set to -1. (See my previous post, Configure Advanced Axis Settings from Eclipse, for more details)
   
   
4. Create a web service client proxy for the ReportExecution2005 web service; the URL for the WSDL definition will be http://<HOST>/reportserver/ReportExecution2005.asmx?wsdl. (See my previous post, Consume Any Web Service Using Eclipse + Axis, for more details)
   
   
5. Enable NTLM Authentication in Axis by adding the Jakarta Commons HTTPClient and Codec JAR files to the classpath. (See my previous post, Enable NTLM Authentication in Axis, for more details)
   
   
6. Create a new Java Class named Test.java
   
   
7. Copy and paste the following code into Test.java (overwrite the existing code)
   

   import com.microsoft.schemas.sqlserver._2005._06._30.reporting.reportingservices.*;
   import com.microsoft.schemas.sqlserver._2005._06._30.reporting.reportingservices.holders.*;
   import java.net.URL;
   import javax.xml.rpc.holders.ByteArrayHolder;
   import javax.xml.rpc.holders.StringHolder;
   import javax.xml.soap.SOAPException;
   import org.apache.axis.message.SOAPHeaderElement;
   
   public class Test
   {
    public static void main(String[] arg)
    {
     ReportExecutionServiceSoapStub service = getService();
     ParameterValue[] parameters = new ParameterValue[1];
     parameters[0] = new ParameterValue();
     parameters[0].setName("<PARAMETER_1_NAME>");
     parameters[0].setValue("<PARAMETER_1_VALUE>");
     
     try
     {
      ExecutionInfo info = service.loadReport("/<FOLDER_NAME>/<REPORT_NAME>", null); //Load report
      setExecutionId(service, info.getExecutionID()); //You must set the session id before continuing
      service.setExecutionParameters(parameters, "en-us"); //Set report parameters
      
      String format = "HTML4.0"; //Valid options are HTML4.0, MHTML, EXCEL, CSV, PDF, etc
      String deviceInfo = "<DeviceInfo><Toolbar>False</Toolbar><HTMLFragment>True</HTMLFragment></DeviceInfo>"; //Only generate an HTML fragment
      ByteArrayHolder result = new ByteArrayHolder();
      StringHolder extension = new StringHolder();
      StringHolder mimeType = new StringHolder();
      StringHolder encoding = new StringHolder();
      ArrayOfWarningHolder warnings = new ArrayOfWarningHolder();
      ArrayOfStringHolder streamIDs = new ArrayOfStringHolder();   
      service.render(format, deviceInfo, result, extension, mimeType, encoding, warnings, streamIDs); //Render report to HTML
      
      System.out.println(new String(result.value)); //Prints the report HTML; this could be embedded in a JSP
     }
     catch (Exception e)
     {
      e.printStackTrace();
     }
     
    }
    
    public static void setExecutionId(ReportExecutionServiceSoapStub service, String id) throws SOAPException
    {
     SOAPHeaderElement sessionHeader = new SOAPHeaderElement("http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices", "ExecutionHeader");
     sessionHeader.addChildElement("ExecutionID").addTextNode(id);
     service.setHeader(sessionHeader);
    }
    
    public static ReportExecutionServiceSoapStub getService()
    {
     try
     {
      String endpoint = "http://<HOST>/reportserver/ReportExecution2005.asmx";   
      ReportExecutionServiceSoapStub service = (ReportExecutionServiceSoapStub)new ReportExecutionServiceLocator(getEngineConfiguration()).getReportExecutionServiceSoap(new URL(endpoint));
      service.setUsername("<DOMAIN>\\<USER_NAME>");
      service.setPassword("<PASSWORD>");
      return service;
     }
     catch (Exception e)
     {
      e.printStackTrace();
     }
     
     return null;
    }
    
    public static org.apache.axis.EngineConfiguration getEngineConfiguration()
    {
     java.lang.StringBuffer sb = new java.lang.StringBuffer();
     
     sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n");
     sb.append("<deployment name=\"defaultClientConfig\"\r\n");
     sb.append("xmlns=\"http://xml.apache.org/axis/wsdd/\"\r\n");
     sb.append("xmlns:java=\"http://xml.apache.org/axis/wsdd/providers/java\">\r\n");
     sb.append("<globalConfiguration>\r\n");
     sb.append("<parameter name=\"disablePrettyXML\" value=\"true\"/>\r\n");
     sb.append("<parameter name=\"enableNamespacePrefixOptimization\" value=\"true\"/>\r\n");
     sb.append("</globalConfiguration>\r\n");
     sb.append("<transport name=\"http\" pivot=\"java:org.apache.axis.transport.http.CommonsHTTPSender\"/>\r\n");
     sb.append("<transport name=\"local\" pivot=\"java:org.apache.axis.transport.local.LocalSender\"/>\r\n");
     sb.append("<transport name=\"java\" pivot=\"java:org.apache.axis.transport.java.JavaSender\"/>\r\n");
     sb.append("</deployment>\r\n");
     
     return new org.apache.axis.configuration.XMLStringProvider(sb.toString());
    }
   }
   

   
   
8. Replace all the placeholders with actual values:
   

   Lines 13-17

   Replace the report parameter names/values with your own report parameter names/values. Increase the size of the array if you have more than one parameter.
   
   

   Line 21

   Replace the report path with the path to your own report. Do NOT include the .RDL extension in this path.
   
   

   Line 55

   Replace the report host with your own report host. This is the endpoint for the ReportExecution2005 web service.
   
   

   Lines 57-58

   Replace the domain, username, and password with your own. These are the Windows credentials that will be used to access the report.
   
   

9. Execute the code. You should see the report HTML printed as output. If you face an authentication error (401) then verify NTLM is enabled and also try adding

   
   3
   
   
   
   

   in your rsreportserver.config under reportserver for basic authentication with SSRS (Thanks Sridhar Iyer).
   
   If you still receive an authentication error (401) then it may be that SSRS and/or Windows is using NTMLv2 (verified for Windows 7). See JB's comment near the bottom of this page and also visit the following link for more information: http://devsac.blogspot.com/2010/10/supoprt-for-ntlmv2-with-apache.html. (Thanks JB!)
   
   
10. Package all the classes into a JAR file and copy it into the WEB-INF/lib folder of your web app. Use the same technique as above to embed the rendered report HTML in a JSP file.
    
    
11. Build a nice user interface that accepts the appropriate report parameters and passes them through to the web service. Add an "Export" drop down list on the report page that allows users to export the report to different formats like Excel, PDF, CSV, etc. You do not have to completely reload the report every time you export it to a different format; once you initially obtain the Report Execution ID you can save it and re-use for rendering to other formats.

Enable NTLM Authentication in Axis

By default Axis is not configured to support NTLM Authentication. To enable NTLM Authentication Axis must be configured to use the Jakarta Commons HTTPClient library. NTLM Authentication is often required to use Windows based web services.

There are a few different methods for accomplishing this, but this is probably the quickest and simplest technique:

1. Download and copy both the Jakarta Commons HTTPClient and Codec JAR files into your project and add them to the classpath.
   
   
2. Copy and paste the following method into your own class; append it to the end of your pre-existing class or create a separate utility class:
   

    public static org.apache.axis.EngineConfiguration getEngineConfiguration()
    {
     java.lang.StringBuffer sb = new java.lang.StringBuffer();
     
     sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\r\n");
     sb.append("<deployment name=\"defaultClientConfig\"\r\n");
     sb.append("xmlns=\"http://xml.apache.org/axis/wsdd/\"\r\n");
     sb.append("xmlns:java=\"http://xml.apache.org/axis/wsdd/providers/java\">\r\n");
     sb.append("<globalConfiguration>\r\n");
     sb.append("<parameter name=\"disablePrettyXML\" value=\"true\"/>\r\n");
     sb.append("<parameter name=\"enableNamespacePrefixOptimization\" value=\"true\"/>\r\n");
     sb.append("</globalConfiguration>\r\n");
     sb.append("<transport name=\"http\" pivot=\"java:org.apache.axis.transport.http.CommonsHTTPSender\"/>\r\n");
     sb.append("<transport name=\"local\" pivot=\"java:org.apache.axis.transport.local.LocalSender\"/>\r\n");
     sb.append("<transport name=\"java\" pivot=\"java:org.apache.axis.transport.java.JavaSender\"/>\r\n");
     sb.append("</deployment>\r\n");
     
     return new org.apache.axis.configuration.XMLStringProvider(sb.toString());
    }

   Note: It's probably a good idea to separate the XML markup from the Java code (move XML markup to separate file). I just left the two joined together for the sake of simplicity / laziness since it eliminated one extra step from this blog posting.

3. When you create an instance of your web service locator class pass the getEngineConfiguration() method as an argument in the constructor like this:
   

   MyWebServiceStub stub = (MyWebServiceStub)new MyWebServiceLocator(getEngineConfiguration());

   
   
4. Set your user name and password like this:

   stub.setUsername("<DOMAIN>\\<USER_NAME>"); //Must use this format
   stub.setPassword("<PASSWORD>");

   
   

For more information see the following links:

• http://dev-answers.blogspot.com/2011/03/ntlm-from-axis-soap-service-client-in-3.html
• http://tjordahl.blogspot.com/2007/03/apache-axis-and-commons-httpclient.html
• http://www.nsftools.com/stubby/ApacheAxisClientTips.htm

Configure Advanced Axis Settings from Eclipse

Occasionally you may need to configure advanced Axis settings from Eclipse. Some common reasons for this are:

• You need to increase or disable the Axis timeout if you are trying to consume a very large or complex web service.
  
  
• You need to generate code for all elements, even unreferenced ones.
  
  

To access these settings, follow these instructions:

1. Click Window -> Preferences from the Eclipse menu bar.
   
   
2. Expand the Web Services node and then select Axis Emitter in the tree view located in the left pane.
   
   

You should see the Axis Emitter screen which contains a variety of options for both Wsdl2Java and Java2Wsdl. These settings are workspace specific so you will need to redefine them again if you switch workspaces. From this screen you can check the "Generate code for all elements, even unreferenced ones" checkbox, and you can set the "Timeout in seconds" to -1 to disable it (see screenshot below).

At the time of writing this tutorial I am using Eclipse Helios with the J2EE extensions installed; options may vary slightly if using a different version.

Consume Any Web Service Using Eclipse + Axis

1. Start Eclipse and create a new workspace named wsTest (click File -> Switch Workspace -> Other…)
   
   
   
   
2. Create a new Java Project named wsTest (click File -> New -> Java Project)
   
   
   
   
3. Create a new Web Service Client Proxy (click File -> New -> Other -> Web Services -> Web Service Client). In this case we are consuming a free, public web service that returns weather information for the United States. The URL for the WSDL definition is http://wsf.cdyne.com/WeatherWS/Weather.asmx?wsdl.
   
   
   
   
4. Create a new Java Class named Test.java (click File -> New -> Class)
   
   
   
   
5. Copy and paste the following code into Test.java (overwrite the existing code):
   

   import com.cdyne.ws.WeatherWS.*;
   import java.net.URL;
   
   public class Test
   {
    public static void main(String[] arg)
    {
     try
     {
      WeatherSoapStub service = (WeatherSoapStub)new WeatherLocator().getWeatherSoap(new URL("http://ws.cdyne.com/WeatherWS/Weather.asmx"));
      WeatherReturn weather = service.getCityWeatherByZIP("47710");
      System.out.println("Location: " + weather.getCity() + ", " + weather.getState());
      System.out.println("Description: " + weather.getDescription());
      System.out.println("Temperature: " + weather.getTemperature() + " degrees");
     }
     catch (Exception e)
     {
      e.printStackTrace();
     }
    }
   }
   

   
   Note that we set the endpoint URL on line 10. The endpoint URL is typically the same as the WSDL definition URL except the endpoint URL does not include the ?WSDL query string parameter.
   
   
6. Execute the code (click Run -> Run)
   
   You should see something resembling the following output:
   
   Location: Evansville, IN
   Description: Sunny
   Temperature: 49 degrees

At the time of writing this post I am using Eclipse Helios with the J2EE extensions installed; options may vary slightly if using a different version.

---

How It Works

Eclipse is executing the Axis Wsdl2Java process which parses the WSDL file and translates it into a set of Java client files that you can then use in your own application. It is possible to accomplish the same thing outside of Eclipse by directly executing the Axis Wsdl2Java process yourself.