Ad-Hoc Database Queries

Top  Previous  Next

When writing ad-hoc database queries the schema element must define the following 3 attributes:

 

schema (Root Element)

 

- packageScope* <url>

- catalog* <database name>

- dataSourceName* <JNDI name>

 

SQL

 

- namePart select<EmployeeCount>

 

Statement

 

- params comma separated list

 

Result

 

- type (integer)

- ref <Container Name>

 

For Example:

 

<schema packageScope="com.wickedfastsolutions.demo.domain" catalog="CentralDatabase" dataSourceName="jdbc/central">
    <SQL namePart="EmployeeCount">
        <Statement>
            <![CDATA[
                select count(*) from employees
            ]]>
        </Statement>
        <Result type="integer"></Result>
    </SQL>
    <SQL namePart="Employee">
        <!-- returns employee's, up to the supplied limit -->
        <Statement params="limit">
            <![CDATA[
                 select 
                 emp_no as Id,
                 birth_date as BirthDate,
                 first_name as FirstName,
                 last_name as LastName,
                 gender as Gender,
                 hire_date as HireDate
                 from employees
                 limit $limit
            ]]>
        </Statement>
        <Result>
            <Employee maxOccurs="unbounded">
                <Id type="positiveInteger">10001</Id>
                <BirthDate regex="(19|20)[0-9]{2}[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])">1953-09-02</BirthDate>
                <FirstName minLength="1" maxLength="32">Georgi</FirstName>
                <LastName minLength="1" maxLength="32">Facello</LastName>
                <Gender type="set" values="MALE(M),FEMALE(F)">M</Gender>
                <HireDate regex="(19|20)[0-9]{2}[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])">1986-06-26</HireDate>
            </Employee>
        </Result>
    </SQL>
    <SQL namePart="Employee">
        <!-- returns employee's, up to the supplied limit -->
        <Statement params="limit">
            <![CDATA[
                 select 
                 emp_no as Id,
                 birth_date as BirthDate,
                 first_name as FirstName,
                 last_name as LastName,
                 gender as Gender,
                 hire_date as HireDate
                 from employees
                 limit $limit
            ]]>
        </Statement>
        <Result ref="Employee"/>
    </SQL>
    <Employee maxOccurs="unbounded">
        <Id type="positiveInteger">10001</Id>
        <BirthDate regex="(19|20)[0-9]{2}[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])">1953-09-02</BirthDate>
        <FirstName minLength="1" maxLength="32">Georgi</FirstName>
        <LastName minLength="1" maxLength="32">Facello</LastName>
        <Gender type="set" values="MALE(M),FEMALE(F)">M</Gender>
        <HireDate regex="(19|20)[0-9]{2}[- /.](0[1-9]|1[012])[- /.](0[1-9]|[12][0-9]|3[01])">1986-06-26</HireDate>
    </Employee>
</schema>

 

The SQL Element:

 

 

 

Another example

 

<schema catalog="ROS" dataSourceName="jdbc/ros" packageScope="com.instymeds.ros.database">
   <SQL namePart="CountFromAudit">
        <Statement params="whereClause">
            <![CDATA[
                SELECT count(*) as count from AUDIT $whereClause
            ]]>
        </Statement>
        <Result type="integer"></Result>
    </SQL>
   <SQL namePart="CountFromMachine">
        <Statement>
            <![CDATA[
                SELECT count(*) as count from MACHINE
            ]]>
        </Statement>
        <Result type="integer"></Result>
    </SQL>