Recursively Search Files Containing Text in Unix

find . -exec grep -H -n -I 'hello' {} \;

The above command will search all files in the current directory and any sub directories for the text 'hello'. Replace 'hello' with whatever text you are trying to find.

Query Month to Date, Year to Date, Rolling 12 Months, & Prior Year Sales

Synopsis:

If using Oracle use the trunc and numtoyminterval functions; if using SQL Server use the datediff function.

---

Oracle Month to Date:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE TRANSACTION_DATE BETWEEN trunc(sysdate, 'MONTH') and sysdate

Oracle Year to Date:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE TRANSACTION_DATE BETWEEN trunc(sysdate, 'YEAR') and sysdate

Oracle Rolling 12 Months:

select sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE TRANSACTION_DATE between trunc(sysdate - numtoyminterval(12, 'MONTH'), 'MONTH') and sysdate

Oracle Prior Year:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE trunc(TRANSACTION_DATE, 'YEAR') = trunc(sysdate - numtoyminterval(1, 'YEAR'), 'YEAR')

---

SQL Server Month to Date:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE datediff(month, TRANSACTION_DATE, getdate()) = 0
AND TRANSACTION_DATE <= getdate()

SQL Server Year to Date:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE datediff(year, TRANSACTION_DATE, getdate()) = 0
AND TRANSACTION_DATE <= getdate()

SQL Server Rolling 12 Months:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE datediff(month, TRANSACTION_DATE, getdate()) <= 12
AND TRANSACTION_DATE <= getdate()

SQL Server Prior Year:

SELECT sum(TRANSACTION_AMOUNT)
FROM TRANSACTIONS
WHERE datediff(year, TRANSACTION_DATE, getdate()) = 1

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