MDBitz Development Resources

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

There are a few methods available to the Sybase Transact-SQL developer who needs to obtain the next primary key for the next row to be inserted. If the primary column is an identity column then the next_identity function can be used. The proper syntax is:

SELECT next_identity( "TABLE_NAME" )

If however you have not used the identity column then you can obtain the next ID to be used for INT type columns by getting the highest ID currenlty being used than adding 1 to it. This is done by using the isnull and MAX functions.

SELECT isnull( MAX(ID), 0 ) + 1 FROM TABLE_NAME

SYBASE Adaptive Server Enterprise (ASE) is a relational model database server, that uses Transact-SQL (T-SQL) as the procedural language for creating, modifying and querying the Databases.

Detailed below is a short overview of how to create tables in an ASE Database Server including auto generating ID SEQUENCES, foreign keys, and unique indexes.

Basic Format

Like other SQL implementations we utilize the create table command to create a new table in the Database:

CREATE TABLE EX_USER (
  ID INT not null,
  NAME VARCHAR(255) not null,
  ACTIVE TINYINT(1) not null,
  ROLE_ID INT not null,
  USER_NAME VARCHAR(25) not null,
  PASSWORD VARCHAR(25) not null
)

How this statement works is that you specify the table name after the create table statement and inside the () you define the various columns in the table with the format of name type  identity, null, or not null and optional other modifiers

Identity Columns

When created database tables we often want to create identity columns that are used to designate the row of data. In most cases we also want that column’s value to automatically populate on an insert with its value. In Transact-SQL this is done by the identity keyword. In our example EX_USER table lets modify the ID column to be an identity column by replacing not null with the identity keyword.

ID int identity

Unique Indices

In addition to identifiers to identify the data row in a table we also may want to specify a column to be unique in that every value is only allowed one at a time. This is accomplished by creating a unique constraint. This can be done as a separate statement or appended to the parameters in the create table statement.

CREATE TABLE EX_USER (
  ID INT IDENTITY,
  NAME VARCHAR(255) not null,
  ACTIVE TINYINT(1) not null,
  ROLE_ID INT not null,
  USER_NAME VARCHAR(25) not null,
  PASSWORD VARCHAR(25) not null,
  UNIQUE NONCLUSTERED (USER_NAME)
)

The nonclustered part of the statement means do not organize the data in order of the column. If by chance you wanted the data to order itself based on the column you can use the clustered keyword.

Foreign Keys

Foreign Keys are used to define columns whose value is from another table. This guarantees that the value found in the column is a valid value in the other column, and is used for lookup tables as well as for linking data across tables. In our example lets assume that we have a lookup table called EX_RULE that contains all the available user roles. We would have our RULE_ID column link to the table by the use of the references keyword.

RULE_ID INT not null REFERENCES EX_RULE(ID)

References

• Sybase’s Transact-SQL User’s Guide

What is iBATIS?

iBATIS is a framework that utilize DAO and SQL Maps to create a light weight persistence layer interfaces that allows you as the developer to easily interact with your Database using objects and sql that you write instead of learning complex ORMs such as Hibernate. Simply put iBATIS acts as the mapper layer between your Database and Objects.  Also for those that find themselves working in both Java and .NET iBATIS has implementations in both languages. Basic information on both SQL Maps and the DAO Framework is below, to get started either visit the iBATIS website or their official Developer Documentation.

SQL Maps

SQL Maps are XML descriptor files that outline how to connect with your database, and how to map your objects to SQL Queries and SQL Queries Results to your objects. iBATIS provides basic a tutorial on using SQLMaps on their project site.

DAO

Data Access Objects (DAOs) are objects that represent an interface for interacting with data without exposing the underlying database.  This provides a separation of application or business logic from the persistance layer or database allowing code to be reused across multiple environments.