DBOSelect

From GreenVulcano Wiki
Jump to: navigation, search

Description

The DBOSelect element represents the DBO optimized for select operations.

GreenVulcano® ESB configuration

DBOSelect with VulCon

DBOSelect allows you to perform a select on database.

The result of the select(s) is an XML document in internal format that can then be transformed into a useful document for the client.

Can be executed more select statements, each one associated to an id, with the possibility of aggregating, using keys, the extracted data.

Can be defined, for every statement and field, the FieldFormatter that allows you to force the formatting of the values ​​extracted from the database.

It is used by DBOBuilder.

The following table shows the DBOSelect attributes:

Attribute Type Description
type fixed This attribute must assume the value dbo
class fixed This attribute must assume the value it.greenvulcano.gvesb.datahandling.dbo.DBOSelect
name required DOSelect name
transformation optional Transformation to be used for converting internal rowset representation in user desired XML document.
force-mode required (caller or db2xml): Force mode of the DBO, can be used in DBOBuilder whit more heterogeneous DBO.
Mode:
caller - inherit the mode of the caller
db2xml - data extraction mode
jdbc-connection-name required DataSource JNDI name.
Override connection defined in DBOBuilder.
ignore-input optional (true or false): Tells the engine do not use the input data.
output-data optional The default is the value of @name-Output.
Attribute Type Description
rowset-builder optional Defines how-to build the output RowSet XML document:
  • standard (default)
  • extended

For example: 

<?xml version="1.0" encoding="UTF-8"?>
<DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"   
            jdbc-connection-name="ds.gv_test" 
            name="SearchPerson"   type="dbobuilder">
      <DHVariables>
             <DHVariable name="decimal-separator" value="."/>
             <DHVariable name="grouping-separator" value=","/>
             <DHVariable name="format" value="#0.00"/>
             <DHVariable name="NAME" value="NULL"/>
             <DHVariable name="CITY" value="NULL"/>
      </DHVariables>
      <DBOSelect class="it.greenvulcano.gvesb.datahandling.dbo.DBOSelect"  
                 name="SearchPerson" 
                 transformation="SearchPerson"   type="dbo">
             <statement id="1" keys="1,2,3" type="select">
                  select p.NAME, p.BIRTHDATE, c.NAME, cc.CNUMBER, cc.CREDIT, cc.ENABLED
                  from PERSON p, CITY c, CREDIT_CARD cc
                  where p.ID_CITY = c.ID
                  and p.ID = cc.ID_OWNER
                  decode{{@{{NAME}}::NULL::::and p.NAME like ‘%@{{NAME}}%’}}
                  decode{{@{{CITY}}::NULL::::and c.NAME like ‘%@{{CITY}}%’}}
                  order by c.NAME, p.NAME
             </statement>
       </DBOSelect>
</DBOBuilder>

According to the data present into the DB we obtain as output the following XML (also known as 'standard' RowSet mode): 

<?xml version="1.0" encoding="UTF-8"?>
<RowSet>
    <data id="1" key_1="ANTONIO ROSSI" key_2="19800205 00:00:00" key_3="MILANO">
        <row id="1">
            <col type="string">1111222233338888</col>
            <col type="string">48.32000</col>
            <col type="string">Y</col>
        </row>
        <row id="1">
            <col type="string">1234123414444789</col>
            <col type="string">600.00000</col>
            <col type="string">Y</col>
        </row>
    </data>
    <data id="1" key_1="MARIO BIANCHI" key_2="19700815 00:00:00" key_3="MILANO">
        <row id="1">
            <col type="string">1234123412341234</col>
            <col type="string">100.00000</col>
            <col type="string">Y</col>
        </row>
        <row id="1">
            <col type="string">1234123415555666</col>
            <col type="string">300.00000</col>
            <col type="string">Y</col>
        </row>
    </data>
</RowSet>

To enable 'extended' RowSet mode: 

<?xml version="1.0" encoding="UTF-8"?>
<DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder"   
            jdbc-connection-name="ds.gv_test" 
            name="SearchPerson" type="dbobuilder">
      <DHVariables>
             <DHVariable name="decimal-separator" value="."/>
             <DHVariable name="grouping-separator" value=","/>
             <DHVariable name="format" value="#0.00"/>
             <DHVariable name="NAME" value="NULL"/>
             <DHVariable name="CITY" value="NULL"/>
      </DHVariables>
      <DBOSelect class="it.greenvulcano.gvesb.datahandling.dbo.DBOSelect"  
                 name="SearchPerson" rowset-builder="extended"
                 transformation="SearchPerson" type="dbo">
             <statement id="1" keys="1,2,3" type="select">
                  select p.NAME, p.BIRTHDATE, c.NAME, cc.CNUMBER, cc.CREDIT, cc.ENABLED
                  from PERSON p, CITY c, CREDIT_CARD cc
                  where p.ID_CITY = c.ID
                  and p.ID = cc.ID_OWNER
                  decode{{@{{NAME}}::NULL::::and p.NAME like ‘%@{{NAME}}%’}}
                  decode{{@{{CITY}}::NULL::::and c.NAME like ‘%@{{CITY}}%’}}
                  order by c.NAME, p.NAME
             </statement>
       </DBOSelect>
</DBOBuilder>

According to the data present into the DB we obtain as output the following XML (also known as 'extended' RowSet mode): 

<RowSet xmlns="http://www.greenvulcano.com/database">
    <data id="1">
        <key>
            <NAME id="1" isNull="false" type="string">ANTONIO ROSSI</NAME>
            <BIRTHDATE format="yyyyMMdd HH:mm:ss" id="2" isNull="false" type="timestamp">19800205 00:00:00</BIRTHDATE>
            <CITY id="3" isNull="false" type="string">MILANO</CITY>
        </key>
        <row id="1">
            <CNUMBER isNull="false" type="string">1111222233338888</CNUMBER>
            <CREDIT isNull="false" type="string">500.57000</CREDIT>
            <ENABLED isNull="false" type="string">Y</ENABLED>
        </row>
        <row id="1">
            <CNUMBER isNull="false" type="string">1234123414444789</CNUMBER>
            <CREDIT isNull="false" type="string">600.00000</CREDIT>
            <ENABLED isNull="false" type="string">Y</ENABLED>
        </row>
    </data>
    <data id="1">
        <key>
            <NAME id="1" isNull="false" type="string">MARIO BIANCHI</NAME>
            <BIRTHDATE format="yyyyMMdd HH:mm:ss" id="2" isNull="false" type="timestamp">19700815 00:00:00</BIRTHDATE>
            <CITY id="3" isNull="false" type="string">MILANO</CITY>
        </key>
        <row id="1">
            <CNUMBER isNull="false" type="string">1234123412341234</CNUMBER>
            <CREDIT isNull="false" type="string">100.00000</CREDIT>
            <ENABLED isNull="false" type="string">Y</ENABLED>
        </row>
        <row id="1">
            <CNUMBER isNull="false" type="string">1234123415555666</CNUMBER>
            <CREDIT isNull="false" type="string">300.00000</CREDIT>
            <ENABLED isNull="false" type="string">Y</ENABLED>
        </row>
    </data>
</RowSet>

It's possible to make a query also on a local or remote neo4j graph database using the related cypher (Cypher Query Language) language [1]. To enable this type of query it needs to set the jdbc-connection-name using the neo4j driver jdbc and write a query using the cypher syntax: 

<?xml version="1.0" encoding="UTF-8"?>
<DBOBuilder class="it.greenvulcano.gvesb.datahandling.dbobuilder.DBOBuilder" 
           jdbc-connection-name="openejb:Resource/testDHDataSourceN4J" 
		   name="GVESB::TestSelectWithPropsN4J" type="dbobuilder">
     <DBOSelect class="it.greenvulcano.gvesb.datahandling.dbo.DBOSelect" 
	            name="TestSelectWithPropsN4J-select"
                transformation="identityXML" type="dbo">
            <statement id="0" type="select">MATCH (n {title: '@{{PARAM_TITLE}}'}) RETURN n</statement>
     </DBOSelect>
</DBOBuilder>

As you can see in the above section you can make any queries achievable with cypher language. According to the data present into neo4j DB we obtain as output the same XML structure as returned with the use of the standard jdbc driver SQL.

DBOSelect might contain the following sub-elements:

Retrieved from "https://gvwiki.greenvulcano.com/gvwiki/index.php?title=DBOSelect&oldid=4489"