Re: Problems generating sql with the SqlGenerator
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
the query-customizer sets an and-criteria width log_table equals for
example "GROUPS"
--
gr
Re: Problems generating sql with the SqlGenerator
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??
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
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
-
table="groups">
jdbc-type="INTEGER"primarykey="true"
autoincrement="true">
collection-class="org.apache.ojb.broker.util.collections.ManageableArrayLi
Re: Problems generating sql with the SqlGenerator
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
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
-
table="groups">
jdbc-type="INTEGER"primarykey="true"
autoincrement="true">
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"
>
-
that complete repository.xml data are in attachment, but in german...
Thanks a lot for your help
Josef Wagner/ Germany
