Re: Problems generating sql with the SqlGenerator
Armin Waibel <[email protected]>
| Newsgroups | gmane.comp.jakarta.ojb.user |
|---|---|
| Message-ID | <[email protected]> |
Josef Wagner wrote:
> Hi Armin,
>
> yes, I have the problem only when adding my extension. Generally, all
> works fine.
> We have tried a other solution like this:
> -------------------------------------------------
> public String getPreparedSelectStatement(Query query, ClassDescriptor
> cld) {
> try {
> if ((cld.getClassOfObject().newInstance() instanceof
> LogableAbstractPoIfc)) {
> logQuery.setCriteria(new Criteria());
> logQuery.getCriteria().addEqualTo( "log_table",
> OnErgySqlGenerator.getLogTable(cld.getClassOfObject()));
> logQuery.getCriteria().addEqualTo("log_funktion", "D");
> logQuery.getCriteria().addEqualToColumn(log_table_obj_id,
> "A0.obj_id");
> query.getCriteria().addNotExists(logQuery);
> -------------------------------------------------
>
> Except of one Query, all works fine. In this one query, ojb works with
> two outer joins instead of in join and i can not set in join as
> preffered like query.setPathOutterJoin() :o/
>
> In my opinion, the old solution in my generator must also work, any idea??
>
Sorry I never worked on the "query/criteria stuff" (Jakob did the most
work on this section of OJB - currently he can't spend time for OJB).
You could try the OJB_1_0_RELEASE branch from SVN (upcoming 1.0.5) -
maybe your problem is fixed in this version.
regards,
Armin
> Thanks Armin for time you spend
> Josef
>
>
> Armin Waibel schrieb:
>> Hi Josef,
>>
>> could you clarify your problem: The generated sql is only invalid when
>> you add your extension or is the generated sql invalid in general?
>>
>> regards,
>> Armin
>>
>> Josef Wagner wrote:
>>> 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
>>>
>>>
>>> ------------------------------------------------------------------------
>>>
>>> <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>
>>>
>>>
>>> ------------------------------------------------------------------------
>>>
>>> <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>
>>>
>>>
>>> ------------------------------------------------------------------------
>>>
>>> <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>
>>>
>>>
>>> ------------------------------------------------------------------------
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe, e-mail: [email protected]
>>> For additional commands, e-mail: [email protected]
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: [email protected]
>> For additional commands, e-mail: [email protected]
>>
>
>