MDBitz Development Resources

For some reason I have the utmost trouble remembering how to do a single line or inline if else statement. This article is mainly for myself so hopefully I will remember the next time I want to do an inline if else statement.

The usual format for performing an if else statement is:

if( $val1 > $val2 ) {
    // code executed if true
} else {
    // code executed if false
}

If you are trying to set a variable to a value depending on a condition then it is often the case that an inline if else statement will be sufficient and easier to read. This is done by the use of ?. The full format is (condition) ? (true output) : (false output) or as an example:

$val = ($var1 > $var2) ? $var1 : $var2;

How many times have you found yourself defining multiple find or search methods for obtaining data in your Web Applications. For example for a Users table you may have a findByName, findById, FindByEmail, and etc. Utilizing the iBATIS framework and SQL Maps we can have all these sql statements condensed into a single dynamic select statement.

To illustrate how this would be completed lets define our User table as the following:

Traditionally to create the above find methods you would create a sql statement for each query. Using iBATIS’s Dynamic SQl we are able to add them all into one statement by use of the dynamic element. To help illustrate lets start with a basic select SQL Map.

<select id="User.find" parameterClass="com.mdbitz.model.User" resultMap="userMap">
    SELECT * 
    FROM USERS
    WHERE 
        FIRST_NAME LIKE #firstName:VARCHAR#
        AND LAST_NAME LIKE #lastName:VARCHAR#
</select>

Using this simple map as a base we could extends it by adding a dynamic clause that only adds the where clauses if a property is set in the parameter object. To do this we would use the isNotNull element that outputs its content only if a property is not null. An updated dynamic map would look like:

<select id="User.find" parameterClass="com.mdbitz.model.User" resultMap="userMap">
    SELECT * 
    FROM USERS
    <dynamic prepend="WHERE">
        <isNotNull property="firstName" prepend="AND">
        FIRST_NAME LIKE #firstName:VARCHAR#
        </isNotNull>
        <isNotNull property="lastName" prepend="AND">
        LAST_NAME LIKE #lastName:VARCHAR#
        </isNotNull>
    </dynamic>
</select>

Using these dynamic statements you could extend this to do any type of find by that you need. You could query by name, phone number, email, id, or any combination of them. In addition to the property you could also use isNull, isEmpty, isNotEmpty, isGreaterThan, IsGreaterEqual, and etc. Instead of dozens of SQL Map statements for each desired search or query. Dynamic SQL allows you to condense your sql query into simpler more robust statements.

Resources

• iBATIS – official site

Most Java programmers are aware of how to initialize and use an iterator utilizing the basic while loop. In most cases developers will iterate over a collection for every element in it using the follow generic code:

Iterator lIter = myList.iterator();
while(lIter.hasNext()) {
    ... // some logic
}

However it is surprising how many developers don’t know how to use the iterator in a for loop. You might wonder why would you want to use the for loop? Simply put you limit the scope of your iterator to the loop itself and it condenses the code to a single line. Rewriting the above iterator would look like:

for(Iterator lIter = myList.iterator(); lIter.hasNext(); ) {
    ... // some logic
}

There you have it, how to iterate over all the elements of a collection using an iterator and the for loop.

SQL Maps are the core to the iBATIS Persistence Framework. These xml documents define the mappings between sql statements and your application objects. The base elements that make up the sqlmap are:

• resultMap
  resultMap elements define how returned data from select statements should be mapped to your objects. Providing relationships between object properties and columns in the result set.
• select
  The select element defines a select statement.
• insert
  The insert element defines an insert statement
• update
  The update element defines an update statement
• delete
  The delete element defines a delete statement

resultMap

The resultMap element requires 3 attributes, id, class, and groupBy. These properties define the id for identifying the result map when used in addtional statements, the Java Class this map correlates to, and the column that the results should be grouped together by in case your object has a collections of additional data. Below shows the resultMap for a basic Status Object.

<resultMap id="statusMap" class="com.mdbitz.Status" groupBy="id">
    <result property="id" column="ID" />
    <result property="name" column="NAME" />
    <result property="description" column="DESCRIPTION" />
</resultMap>

The above xml snippet defines our result mapping. We have identified the mapping as statusMap that maps the results to our Status object grouping data by the id property. Inside the element we define the individual column mappings where for example the ID column of the result set maps to the id property of the Status object. All mappings assume that their are get/set methods defined for the properties.

select

The select statement also requires 2 or 3 attributes for its definition an id, parameterClass, and a resultMap. Where the parameterClass determines what object, primitive type will be used to pass in any variables and the resultMap is the mappings for the result set. If no parameters are being passed in then the parameterClass attribute can be left out. Below is a simple select all statement with no parameters:

<select id="list" resultMap="statusMap" >
    SELECT 
        *
    FROM PBL_RUN_STATUS
</select>

As can be seen from this basic statement, inside the select element you define the SQL statement that you want to have executed. By passing in parameters you can make the defined statement dynamic as shown below:

<select id="findByName" parameterClass="String" resultMap="statusMap" >
    SELECT 
        *
    FROM P_STATUS s
    WHERE s.NAME = #value#
</select>

In the above statement we made the query dynamic by passing in a string parameter that gets placed at the #value# text of the statement. So when called we could pass in “ABORTED” to make the sql query SELECT * FROM P_STATUS s WHERE s.NAME = “ABORTED”

insert

The insert statement functions similar to the select statement with the exception that their is no resultMap attribute as no result is returned beyond the inserted row id if the selectKey sub element is defined. The selectKey element defines how to define the id of the inserted row. Below is a sample insert statmenet for an TSQL database that uses an identity key.

<insert id="insert" parameterClass="com.mdbitz.Status">
    <selectKey resultClass="int" keyProperty="id">
        SELECT next_identity( 'PBL_RUN_STATUS' )
    </selectKey>
     INSERT INTO P_STATUS
    ( NAME, DESCRIPTION )
    VALUES
    ( #name:VARCHAR#, #description:VARCHAR# )
</insert>

In this example you will note 2 things first is the selectKey sub element where we identify how to get the identifier of the inserted row, in this note how we identify the keyProperty of the object as well as its resultClass for its type. Second you will note then when using an object parameter you utilize it’s property names to access how to use their values in the statement, in this case the name and description properties are being utilized.

update / delete

The update delete clauses work the same as the insert statement excep that you do not define a selectKey sub element. Instead you simply define your id and parameter class and write your marked up sql as the contents of the element. Below are examples for the update or deletion of the Status object used throughout the examples.

<update id="update" parameterClass="com.mdbitz.Status">
    UPDATE P_STATUS
    SET
        NAME = #name:VARCHAR#,
        DESCRIPTION = #description:VARCHAR#
    WHERE
        ID = #id:int#
</update>

<delete id="delete" parameterClass="com.mdbitz.Status">
    DELETE 
    FROM PBL_RUN_STATUS
    WHERE 
    ID=#id:INT#
</delete>

Using iBATIS as your persistence framework for you applications can greatly reduce your development time. Full details on the framework and SQL Maps can be obtained from the projects official site ibatis.apache.org.

Resources

• iBatis – official site

Java Calendar

The Calendar class is a java utility for obtaining and modifying the current date and time. It contains all the functionality necessary to get the current time in any time zone as well as methods for manipulating the Date.

import java.util.Calendar;

Java SimpleDateFormat

The SimpleDateFormat is another utility that is used to format a Date object into a String based on the supplied format. The SimpleDateFormat object can be used to return the full date and time, date only, the month, or any such combination.

import java.text.SimpleDateFormat

How to use the Calendar utility

The Calendar object is instantiated by using the getInstance method.

Calendar calendar = Calendar.getInstance();

Once you have an instance of the calendar you can obtain the Date by use of the getTime method

calendar.getTime()

It is a common practice of having to modify the Date object to a different time in our applications. This is done by the set and add methods. The add method is used to manipulate a parameter of the Date while the set method is used to set a parameter to a certain value.

For example if we wanted to get yesterdays date we would use the add method and modify the Date by -1.

calendar.add( Calendar.DATE, -1 );

We could also modify the month the same way.

calendar.add( Calendar.MONTH, 1 );

However if we wanted to change the Date to the first of the month we would use the set method

calendar.set( Calendar.DAY_OF_MONTH, 1 );

In addition to the constants above that represent the date, month, and day of month there are others for year, seconds, hours, and etc that can be used to modify the date in any many necessary for your application.

Using the SimpleDateFormat Object to return the Date in readable format.

To return a readable string representation of the Date obtained by the Calendar object the SimpleDateFormat format method is utilized. This method returns a string that represents the inputted date based on the format of the SimpleDateFormat object.

To instantiate the SimpleDateFormat object you call its constructor passing the textual format of the desired output of the date.  The format object understands the standard date and time representations of dd, mm, MM, YYYY, yy, and etc.  The example below shows how to display the time format as Date/Month/Year.

SimpleDateFormat dateFormat = new SimpleDateFormat( "dd/MM/yy" );
String date = dateFormat.format( calendar.getTime() );

Conclusion

Utilizing the Calendar and SimpleDateFormat objects allows you as a developer to easily obtain and modify the Date without doing custom arithmetic. They can be especially helpful in generating SQL queries for obtaining data for the past month, year, or a range of times.

Resources

• Calendar JavaDoc
• SimpleDateFormat JavaDoc
• Date JavaDoc