Jeeva Blog

iBatis XML Sample

SELECT GAMEPLAN_ID,GAMEPLAN_STATUS,CREATE_USR_BTLR_ID,
START_DT,END_DT,DESCRIPTION_ID FROM PCIMS_GAMEPLAN
WHERE GAMEPLAN_STATUS !='H'
order by start_dt desc




SELECT GAMEPLAN_ID,GAMEPLAN_STATUS,CREATE_USR_BTLR_ID,
START_DT,END_DT,DESCRIPTION_ID FROM PCIMS_GAMEPLAN
WHERE GAMEPLAN_STATUS = 'A'
order by start_dt desc





SELECT GP.GAMEPLAN_ID
FROM PCIMS_GAMEPLAN GP
WHERE
GP.START_DT BETWEEN
(
SELECT P.START_DT FROM PCIMS_PERIOD P
WHERE
P.YEAR = #trimesterYear:NUMERIC#
AND P.PERIOD_NUMBER = #startPeriod:NUMERIC#
)
AND
(
SELECT P.END_DT FROM PCIMS_PERIOD P
WHERE
P.YEAR = #trimesterYear:NUMERIC#
AND P.PERIOD_NUMBER = #endPeriod:NUMERIC#
)






select pga.file_id,pf.file_name ,pf.file_type,pga.gameplan_id from pcims_file pf
,pcims_gameplan_attachment pga where pf.file_id=pga.file_id
and pga.gameplan_id=#gamePlanID:int# and pf.file_type = 'G'





SELECT GP.GAMEPLAN_ID,GP.GAMEPLAN_STATUS, GP.START_DT,GP.END_DT,
GP.CREATE_USR_BTLR_ID,GP.DESCRIPTION_ID
FROM PCIMS_GAMEPLAN GP
WHERE
GP.GAMEPLAN_ID=#gamePlanID:int#





SELECT SEQ_IMS_GAMEPLAN.NEXTVAL AS ID FROM DUAL

INSERT INTO PCIMS_GAMEPLAN(
GAMEPLAN_ID,
GAMEPLAN_STATUS,
CREATE_USR_BTLR_ID,
CREATE_DT,
UPDATE_USR_BTLR_ID,
UPDATE_DT,
START_DT,
END_DT,
DESCRIPTION_ID
)VALUES(
#gamePlanID:NUMERIC#,
#gamePlanStatus:VARCHAR#,
LOWER(#createUsrBtlrId:VARCHAR#),
#createDate:DATE#,
LOWER(#updateUsrBtlrId:VARCHAR#),
sysdate,
#startDate:DATE#,
#endDate:DATE#,
#descriptionId:NUMERIC#
)



UPDATE PCIMS_GAMEPLAN
SET
GAMEPLAN_STATUS = #gamePlanStatus:VARCHAR#,
END_DT = #endDate:DATE#,
DESCRIPTION_ID = #descriptionId:NUMERIC#,
START_DT = #startDate:DATE#,
UPDATE_DT = sysdate,
UPDATE_USR_BTLR_ID = LOWER(#updateUsrBtlrId:VARCHAR#)
WHERE gamePlan_id = #gamePlanID:NUMERIC#



UPDATE PCIMS_GAMEPLAN
SET
DESCRIPTION_ID = #descriptionId:NUMERIC#,
UPDATE_DT = sysdate,
UPDATE_USR_BTLR_ID = LOWER(#updateUsrBtlrId:VARCHAR#)
WHERE gamePlan_id = #gamePlanID:NUMERIC#



UPDATE PCIMS_GAMEPLAN
SET GAMEPLAN_STATUS = 'A'
WHERE (GAMEPLAN_ID = #gamePlanID:NUMERIC#)


SELECT DECODE (COUNT(GAMEPLAN_ID),0,'N','Y')
FROM (
SELECT GP.GAMEPLAN_ID
FROM PCIMS_GAMEPLAN GP
WHERE
GP.START_DT BETWEEN
(
SELECT P.START_DT FROM PCIMS_PERIOD P
WHERE
P.YEAR = #trimesterYear:NUMERIC#
AND P.PERIOD_NUMBER = #startPeriod:NUMERIC#
)
AND
(
SELECT P.END_DT FROM PCIMS_PERIOD P
WHERE
P.YEAR = #trimesterYear:NUMERIC#
AND P.PERIOD_NUMBER = #endPeriod:NUMERIC#
)
AND GAMEPLAN_ID <> #gamePlanID:NUMERIC#
)


INSERT INTO PCIMS_GAMEPLAN_ATTACHMENT(
GAMEPLAN_ID,
FILE_ID
)VALUES(
#gamePlanID:NUMERIC#,
#fileId:NUMERIC#
)



DELETE FROM PCIMS_GAMEPLAN_ATTACHMENT
WHERE FILE_ID = #fileId:VARCHAR#



UPDATE PCIMS_GAMEPLAN
SET GAMEPLAN_STATUS = 'H'
WHERE TRUNC(ADD_MONTHS(START_DT,12)) < SYSDATE
AND GAMEPLAN_STATUS <> 'H'

iBatis SQL MAP CONFIG

cacheModelsEnabled="true"
enhancementEnabled="true"
maxSessions="64"
maxTransactions="8"
maxRequests="128"/>

iBatis

What is iBatis ?

* A JDBC Framework
o Developers write SQL, iBATIS executes it using JDBC.
o No more try/catch/finally/try/catch.
* An SQL Mapper
o Automatically maps object properties to prepared statement parameters.
o Automatically maps result sets to objects.
o Support for getting rid of N+1 queries.
* A Transaction Manager
o iBATIS will provide transaction management for database operations if no other transaction manager is available.
o iBATIS will use external transaction management (Spring, EJB CMT, etc.) if available.
* Great integration with Spring, but can also be used without Spring (the Spring folks were early supporters of iBATIS).


What isn't iBATIS ?

* An ORM
o Does not generate SQL
o Does not have a proprietary query language
o Does not know about object identity
o Does not transparently persist objects
o Does not build an object cache

Essentially, iBatis is a very lightweight persistence solution that gives you most of the semantics of an O/R Mapping toolkit, without all the drama. In other words ,iBATIS strives to ease the development of data-driven applications by abstracting the low-level details involved in database communication (loading a database driver, obtaining and managing connections, managing transaction semantics, etc.), as well as providing higher-level ORM capabilities (automated and configurable mapping of objects to SQL calls, data type conversion management, support for static queries as well as dynamic queries based upon an object's state, mapping of complex joins to complex object graphs, etc.). iBATIS simply maps JavaBeans to SQL statements using a very simple XML descriptor. Simplicity is the key advantage of iBATIS over other frameworks and object relational mapping tools.

Back to top
Spring - iBATIS Configuration


HAVE YOUR SAY

At last a quick Ibatis tutorial that works.
Many thanks

Declanmagee, UK

Send us your comments
Close

* Social Web
* Comments

* del.icio.us
* Digg
* Furl
* Netscape
* Yahoo! My Web
* StumbleUpon
* Google Bookmarks
* Technorati
* BlinkList
* Newsvine
* ma.gnolia
* reddit
* Windows Live
* Tailrank

Name: *
E-Mail: *
Message: *
Confirmation Code:
Enter code: *
(case sensitive)


Installing iBATIS is a quick and simple process. Because it is a library, and not an application, there is no installation process, but you do need to go through a few steps to use iBATIS in your application. You need to get the iBATIS library files. The entire iBATIS framework can be downloaded from iBATIS homepage. iBATIS is delivered in a single JAR file: ibatis-version.build.jar (as of now, ibatis-2.3.0.677.jar) and should be copied and made available at runtime via the application's class path.

Configuring the Environment:

Let�s see how to configure the iBatis-Spring environment. When working with Spring and iBATIS, you will have a minimum of three XML configuration files, and you will often have even more. It is important that you become familiar with these config files. So how do you make iBATIS available to your application? There are a couple of ways:

1.
Using Spring - iBATIS with a stand-alone application:

You should place the ibatis-2.3.0.677.jar in the application's classpath. At minimum, you must define and place the following three configuration files in the classpath
* Spring config - This file defines the database connection parameters, the location of the SQL Map config file, and one or more Spring beans for use within the application. ( applicationContext.xml).
* SQL Map config - This file defines any iBATIS-specific configuration settings that you may need and declares the location for any SQL Map files that should be accessible through this config file. (SqlMapConfig.xml)
* SQL Map(s) � One or more SQL Map files are declared in the SQL Map config and typically mapped to a single business entity within the application ,often represented by a single Java class (domainObject.xml).

2.
Using Spring - iBATIS with a web application

Same as the above ,but just need one more xml (web.xml).

Now that we know the basic configuration steps,let's jump into example.