Problems generating sql with the SqlGenerator

Josef Wagner <[email protected]>
Newsgroups gmane.comp.jakarta.ojb.user
Organization On-Ergy Systemberatung GmbH
Message-ID <[email protected]> 
Hi List,

i have written my one SqlGenerator, because i have to extend each query 
width the criteria, that the status of the entry, which is described in 
a seperate log-table is not 'DELETED'.
------------------
Problem
------------------
Now, my problem is, that the generated statemts are incorecct.
I extend from the SqlGeneratorDefaultImpl and override the Method 
getPreparedSelectStatement as followed:

   public String getPreparedSelectStatement(Query query, ClassDescriptor 
cld) {
       try {
       if ((cld.getClassOfObject().newInstance() instanceof 
LogableAbstractPoIfc)) {
           query.getCriteria().addEqualTo("log.log_table", 
OnErgySqlGenerator.getLogTable(cld.getClassOfObject()));
           query.getCriteria().addNotEqualTo("log.log_status", "D");
       }
       }              catch(Exception iaex) {
           logger.warn("Statement wurde evtl. nicht korrekt 
aufbereitet");                      logger.warn(iaex);                  }
             SqlCacheKey key = new SqlCacheKey(query, cld, 
SqlCacheKey.TYPE_SELECT);
       String result = (String) m_sqlCacheMap.get(key);
       if (result == null) {
           SqlStatement sql = new SqlSelectStatement(getPlatform(), cld, 
query, logger);
           result = sql.getStatement();
           if (logger.isDebugEnabled()) {
               logger.debug("SQL:" + result);
           }
           m_sqlCacheMap.put(key, result);
       }
       return result;
   }

The following incorrect statment will be generated, wenn calling
new User(1).getGroups().size();

-------------
Incorrect generated sql-statement
-------------
SELECT count(*) FROM groups A0,users_groups INNER JOIN log 
A1,users_groups ON A0.obj_id=A1.log_table_obj_id WHERE 
(((users_groups.users_obj_id = 1) AND users_groups.groups_obj_id = 
A0.obj_id) AND log_table = 'GrOUPS') AND log_funktion <> 'D'

------------
statement i would expect
------------
SELECT count(*) FROM groups A0 INNER JOIN log A1 ON 
A0.obj_id=A1.log_table_obj_id INNER JOIN users_groups ON A0.obj_id= 
users_groups.groups_obj_id WHERE (((users_groups.users_obj_id = 1) AND 
users_groups.groups_obj_id = A0.obj_id) AND log_table = 'GROUPS') AND 
log_funktion <> 'D'

---------------------------
Description to the tables
---------------------------


##############            #####################           ##############
# groups     #            # groups_users      #           # users      #
##############            #####################           ##############
# int obj_id #            # int groups_obj_id #           # int obj_id #
##############            # int users_obj_id  #           ##############


#########################
# log                   #
#########################
# int obj_id            #
# int log_table_obj_id  #
# string log_table      #
# string log_status     #
#########################

- table groups and table users are joined width the m-n-table groups_users
- for each data-entry, there is a entry in the log-table, which 
describes the status of the data-entry (deleted, inserted...) the 
repository.xml of groups and users have an collection-descrpitor to the 
log-table with a query-optimizer vor verifing the source-table with 
string log_table
       <!-- 1-n to Log -->
   <collection-descriptor
      auto-retrieve="true"
      auto-delete="object"
      auto-update="object"
      proxy="true"
      name="log"
      element-class-ref="de.on_ergy.lakon.data.model.Log"
   >
      <inverse-foreignkey field-ref="logTableObjId"/>
      <query-customizer
           class="de.on_ergy.lakon.data.QueryCustomizerLakonImpl">
           <attribute
               attribute-name="logTable"
               attribute-value="GROUPS"
           />
       </query-customizer>
        </collection-descriptor>

the query-customizer sets an and-criteria width log_table equals for 
example "GROUPS"


--------------------------------------
groups_repostory.xml, users_repository is equal, only width other 
parameters
-----------------------------------------
<class-descriptor class="de.on_ergy.lakon.data.model.Groups" 
table="groups">
   <field-descriptor name="objId"         column="obj_id"          
jdbc-type="INTEGER"                primarykey="true" 
autoincrement="true"></field-descriptor>

   <!-- m - n  users_groups to users -->
   <collection-descriptor
    name="users"
    
collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList" 

    element-class-ref="de.on_ergy.lakon.data.model.Users"
    auto-retrieve="true"
    auto-update="false"
    auto-delete="link"
      proxy="true"
    indirection-table="users_groups"
 >
    <fk-pointing-to-this-class column="groups_obj_id"/>
    <fk-pointing-to-element-class column="users_obj_id"/>
 </collection-descriptor>  
<!-- 1-n to log -->
   <collection-descriptor
      auto-retrieve="true"
      auto-delete="object"
      auto-update="object"
      proxy="true"
      name="log"
      element-class-ref="de.on_ergy.lakon.data.model.Log"
   >
      <inverse-foreignkey field-ref="logTableObjId"/>
      <query-customizer
           class="de.on_ergy.lakon.data.QueryCustomizerLakonImpl">
           <attribute
               attribute-name="logTable"
               attribute-value="GROUPS"
           />
       </query-customizer>
        </collection-descriptor>
  </class-descriptor>


---------------------
that complete repository.xml data are in attachment, but in german...

Thanks a lot for your help

Josef Wagner/ Germany

---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
repository_benutzer.xml (text/xml, 3.1 KB) 
<class-descriptor class="de.on_ergy.lakon.data.model.Benutzer" table="benutzer">
	<field-descriptor name="objId"       column="obj_id"         jdbc-type="INTEGER"                primarykey="true" autoincrement="true"></field-descriptor>
	<field-descriptor name="benutzerKz"   column="benutzer_kz"      jdbc-type="VARCHAR"  length="6"   ></field-descriptor>
	<field-descriptor name="vorName"  column="vorname"    jdbc-type="VARCHAR"  length="50"   ></field-descriptor>
	<field-descriptor name="nachName"   column="nachname"      jdbc-type="VARCHAR"  length="50"   ></field-descriptor>
	<field-descriptor name="passwort"   column="passwort"      jdbc-type="VARCHAR"  length="50"   ></field-descriptor>
	<field-descriptor name="memoObjId"   column="memo_obj_id"    jdbc-type="INTEGER"                ></field-descriptor>
	<field-descriptor name="spracheObjId"   column="sprache_obj_id"    jdbc-type="INTEGER"                ></field-descriptor>
	<!-- Referenz auf den Memotexts -->
	<reference-descriptor
	   auto-delete="object"
	   auto-update="object"
	   proxy="true"
       name="memotext"
       class-ref="de.on_ergy.lakon.data.model.Memotexte"
    >
    <foreignkey field-ref="memoObjId"/>       
    </reference-descriptor>        
	<!-- Referenz auf die Sprache -->
	<reference-descriptor
       proxy="true"
       name="sprache"
       class-ref="de.on_ergy.lakon.data.model.Sprache"
    >
    <foreignkey field-ref="spracheObjId"/>       
    </reference-descriptor>        
    <!-- m - n über benutzer_gruppen zu gruppen -->
    <collection-descriptor
     name="gruppen"
     collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList"
     element-class-ref="de.on_ergy.lakon.data.model.Gruppen"
     auto-retrieve="true"
     auto-update="false"
     auto-delete="link"
     proxy="true"
     indirection-table="benutzer_gruppen"
  >
     <fk-pointing-to-this-class column="benutzer_obj_id"/>
     <fk-pointing-to-element-class column="gruppen_obj_id"/>
  </collection-descriptor>    

  <!-- m - n über benutzer_teams zu den Teams -->
  <collection-descriptor
     name="teams"
     collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList"
     element-class-ref="de.on_ergy.lakon.data.model.Teams"
     auto-retrieve="true"
     auto-update="false"
     auto-delete="link"
     proxy="true"
     indirection-table="benutzer_teams"
  >
     <fk-pointing-to-this-class column="benutzer_obj_id"/>
     <fk-pointing-to-element-class column="team_obj_id"/>
  </collection-descriptor>    
    	
    		    <!-- 1-n-Beziehung zu dem Log -->
	<collection-descriptor
       auto-retrieve="true"
	   auto-delete="object"
	   auto-update="object"
	   proxy="true"
       name="log"
       element-class-ref="de.on_ergy.lakon.data.model.Log"
    >
       <inverse-foreignkey field-ref="logKlasseObjId"/>
       <query-customizer
		    class="de.on_ergy.lakon.data.QueryCustomizerLakonImpl">
       <!-- Achtung, diesen Werte bitte aus Klasse Tabellentyp entnehmen -->
        	<attribute
            	attribute-name="logTable"
            	attribute-value="BENUTZER"
        	/>
	    </query-customizer>
       
	</collection-descriptor>
    	
</class-descriptor>
repository_gruppen.xml (text/xml, 3.2 KB) 
<class-descriptor class="de.on_ergy.lakon.data.model.Gruppen" table="gruppen">
	<field-descriptor name="objId"         column="obj_id"          jdbc-type="INTEGER"                primarykey="true" autoincrement="true"></field-descriptor>
	<field-descriptor name="gruppeKz"  column="gruppe_kz"  jdbc-type="CHAR"     length="12"   ></field-descriptor>
	<field-descriptor name="standard"  column="standard"  jdbc-type="BIT"  ></field-descriptor>
	<field-descriptor name="memoObjId"         column="memo_obj_id"          jdbc-type="INTEGER"                ></field-descriptor>
	<!-- 1-n Referenz auf die Bezeichnungen -->
	<collection-descriptor
	   auto-delete="object"
	   auto-update="object"
       proxy="true"
       name="bezeichnungen"
       element-class-ref="de.on_ergy.lakon.data.model.Bezeichnungen"
       orderby="sprach_obj_id"
       sort="ASC"
    >
       <inverse-foreignkey field-ref="parentObjId"/>
       <query-customizer
		    class="de.on_ergy.lakon.data.QueryCustomizerLakonImpl">
       <!-- Achtung, diesen Werte bitte der Schnittstelle TabellentypAttributeIfc entnehmen -->
        	<attribute
            	attribute-name="parentTabKz"
            	attribute-value="GRUPPEN"
        	/>
	    </query-customizer>
       
	</collection-descriptor>
	<!-- Referenz auf den Memotexts -->
	<reference-descriptor
	   auto-delete="object"
	   auto-update="object"
       proxy="true"
       name="memotext"
       class-ref="de.on_ergy.lakon.data.model.Memotexte"
    >
       <foreignkey field-ref="memoObjId"/>       
    </reference-descriptor>
    
    <!-- m - n über benutzer_gruppen zu Benutzer -->
    <collection-descriptor
     name="benutzer"
     collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList"
     element-class-ref="de.on_ergy.lakon.data.model.Benutzer"
     auto-retrieve="true"
     auto-update="false"
     auto-delete="link"
       proxy="true"
     indirection-table="benutzer_gruppen"
  >
     <fk-pointing-to-this-class column="gruppen_obj_id"/>
     <fk-pointing-to-element-class column="benutzer_obj_id"/>
  </collection-descriptor>    


    <!-- m - n über gruppen_rollen zu rollen -->
    <collection-descriptor
     name="rollen"
     collection-class="org.apache.ojb.broker.util.collections.ManageableArrayList"
     element-class-ref="de.on_ergy.lakon.data.model.Rollen"
     auto-retrieve="true"
     auto-update="false"
     auto-delete="link"
     proxy="true"
     indirection-table="gruppen_rollen"
  >
     <fk-pointing-to-this-class column="gruppen_obj_id"/>
     <fk-pointing-to-element-class column="rollen_obj_id"/>
  </collection-descriptor>    
	
	    <!-- 1-n-Beziehung zu dem Log -->
	<collection-descriptor
       auto-retrieve="true"
	   auto-delete="object"
	   auto-update="object"
	   proxy="true"
       name="log"
       element-class-ref="de.on_ergy.lakon.data.model.Log"
    >
       <inverse-foreignkey field-ref="logKlasseObjId"/>
       <query-customizer
		    class="de.on_ergy.lakon.data.QueryCustomizerLakonImpl">
       <!-- Achtung, diesen Werte bitte aus Klasse Tabellentyp entnehmen -->
        	<attribute
            	attribute-name="logTable"
            	attribute-value="GRUPPEN"
        	/>
	    </query-customizer>
       
	</collection-descriptor>
	
</class-descriptor>
repository_log.xml (text/xml, 1.4 KB) 
<class-descriptor class="de.on_ergy.lakon.data.model.Log" table="log">
	<field-descriptor name="objId"         column="obj_id"         jdbc-type="INTEGER"                primarykey="true" autoincrement="true"></field-descriptor>
	<field-descriptor name="logBenutzerObjId"         column="log_benutzer_obj_id"         jdbc-type="INTEGER"></field-descriptor>
	<field-descriptor name="logKlasseObjId"     column="log_klasse_obj_id"     jdbc-type="INTEGER"></field-descriptor>
	<field-descriptor name="mnObjId"         column="m_n_obj_id"         jdbc-type="INTEGER"          ></field-descriptor>
	<field-descriptor name="logTable"     column="log_table"     jdbc-type="VARCHAR"     length="8"></field-descriptor>
	<field-descriptor name="logFunktion"   column="log_funktion"   jdbc-type="CHAR"     length="1"   ></field-descriptor>
	<field-descriptor name="logDataDefault"     column="log_data_default"     jdbc-type="VARCHAR"  length="65535"  ></field-descriptor>	
	<field-descriptor name="logDataExport"     column="log_data_export"     jdbc-type="VARCHAR"  length="65535"  ></field-descriptor>	
	<field-descriptor name="erstelltTimestamp"  column="erstellt_timestamp"  jdbc-type="TIMESTAMP"               ></field-descriptor>
	<field-descriptor name="letzteAenderungTimestamp"  column="letzte_aenderung_timestamp"  jdbc-type="TIMESTAMP"               ></field-descriptor>
</class-descriptor>
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.