Hello,
The offending piece of logic is here
This adds all fields from a table to a select clause
sq.addSelect(returningFields);
This adds new fields to the fromFrom table's internal list of fields:
List<Field<?>> allFields = fromTable.asTable().getFields();
for (int x = 0; x < joinTables.size(); x++) {
allFields.addAll(joinTables.get(x).asTable().getFields());
}
Every time you're running the above loop, the generated table instance
gets new fields.
As a workaround, you should not modify the result from getFields(),
but create your own copy like this:
List<Field<?>> allFields = new
ArrayList<Field<?>>(fromTable.asTable().getFields())
Clearly, this is a flaw in jOOQ and will be corrected in the next
release. Generated Tables are expected to be immutable objects:
https://sourceforge.net/apps/trac/jooq/ticket/1199
Thanks for reporting this!
Cheers
Lukas
2012/3/1 <[email protected]>:
> Ok,
> Thank you!
>
> The project is developed with netbeans. (+simple h2 db)
>
> I attach you the project
>
> Feel free to contact me for everything.
>
> Thank you again,
>
> Agharta
>
>
>
>
> Il giorno giovedì 1 marzo 2012 15:39:20 UTC+1, Lukas Eder ha scritto:
>>
>> Hello Agharta,
>>
>> You can attach files to mails to the user group
>>
>> Cheers
>> Lukas
>>
>> 2012/3/1 Agharta agharta
>>
>> > Hi Mr Lukas Eder,
>> > Thank you for the quick reply.
>> >
>> > To find the problem, i've created a test case, with scripts and i have
>> > simulated the problem.
>> >
>> > How to send the test case zip file?
>> >
>> > Thank you,
>> >
>> > Agharta
>> >
>> >
>> >
>> > On 1 Mar, 13:42, Lukas Eder <[email protected]> wrote:
>> >> Hello Agharta,
>> >>
>> >> Please use the jooq-user group for support requests of this kind.
>> >> I cannot reproduce the issue you're mentioning by repetitively calling
>> >> something of this kind:
>> >>
>> >> Municipality.MUNICIPALITY.asTable().getFields()
>> >> Municipality.MUNICIPALITY.asTable().getFields()
>> >> Municipality.MUNICIPALITY.asTable().getFields()
>> >>
>> >> I always get the same list of fields. Beware that if you join two
>> >> tables, the resulting join table will contain all combined fields. I
>> >> suspect you join tables multiple times, which might lead to the issue
>> >> you're encountering. You will find some documentation about table
>> >> sources here:http://www.jooq.org/manual/DSL/TABLESOURCE/
>> >>
>> >> Also, note that jOOQ artefacts are NOT thread-safe. Furthermore, some
>> >> artefacts are NOT immutable. Maybe you are re-using a SelectQuery
>> >> object from a previous query?
>> >>
>> >> If you wish any further analysis on my side, please narrow down the
>> >> problematic code to the bit that is causing the problem. It is hard to
>> >> reproduce an issue from so much code.
>> >>
>> >> Cheers
>> >> Lukas
>> >>
>> >> 2012/3/1 agharta
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> > Hi Mr,
>> >> > I\'m using jooq in a complex system: Netbeans Platform +
>> >> > Openswing + Spring + H2.
>> >>
>> >> > I\'ve created a wrapper class thats auto create joins and
>> >> > conditions based on Openswing input.
>> >>
>> >> > But i\'ve a problem:
>> >> > For unknown reasons the call of
>> >> > UpdatableTable.Table.asTable.getFields() sometime returns me
>> >> > many fields, more than Table contains. (fields of other tables).
>> >>
>> >> > I send you the code of wrapper class and a little
>> >> > implementation, you can try it.
>> >>
>> >> > Sorry if i not post this into user group or bugtracking, but
>> >> > i want to known your opinion before creating a ticket.
>> >>
>> >> > THE WRAPPER CLASS (Sorry for the poor code):
>> >> > public class QueryComposerUtil {
>> >>
>> >> > public static <R extends Record> SelectQuery
>> >> > compileQueryJoinOne(UpdatableTable<R> fromTable,
>> >> > List<Field<?>> returningFields, UpdatableTable<?> joinTable,
>> >> > JoinType joinType, Condition joinCondition, Map<String,
>> >> > Field<?>> replacementJoinFieldsNames,
>> >> > Collection<FilterWhereClause[]> filterWhereClauseList,
>> >> > List<String> currentSortedColumns, List<String>
>> >> > currentSortedColumnsVersus) {
>> >> > List<UpdatableTable<?>> joinTables = new
>> >> > ArrayList<UpdatableTable<?>>();
>> >> > List<JoinType> joinTypes = new ArrayList<JoinType>();
>> >> > List<Condition> joinConditions = new
>> >> > ArrayList<Condition>();
>> >> > joinTables.add(joinTable);
>> >> > joinTypes.add(joinType);
>> >>
>> >> > joinConditions.add(joinCondition);
>> >> > return compileQueryJoin(fromTable, returningFields,
>> >> > joinTables, joinTypes, joinConditions,
>> >> > replacementJoinFieldsNames, filterWhereClauseList,
>> >> > currentSortedColumns, currentSortedColumnsVersus);
>> >> > }
>> >>
>> >> > public static <R extends Record> SelectQuery
>> >> > compileQueryJoin(UpdatableTable<R> fromTable, List<Field<?>>
>> >> > returningFields, List<UpdatableTable<?>> joinTables,
>> >> > List<JoinType> joinTypes, List<Condition> joinConditions,
>> >> > Map<String, Field<?>> replacementJoinFieldsNames,
>> >> > Collection<FilterWhereClause[]> filterWhereClauseList,
>> >> > List<String> currentSortedColumns, List<String>
>> >> > currentSortedColumnsVersus) {
>> >> > PublicFactory vs = new
>> >> >
>> >> > PublicFactory((Connection)SpringLoader.getBean(\"datasourceConnection\"));
>> >>
>> >> > SelectQuery sq = vs.selectQuery();
>> >> > sq.addSelect(returningFields);
>> >> > sq.addFrom(fromTable);
>> >> > for (int x = 0; x < joinTables.size(); x++) {
>> >> > sq.addJoin(joinTables.get(x), joinTypes.get(x),
>> >> > joinConditions.get(x));
>> >> > }
>> >>
>> >> > List<Field<?>> allFields =
>> >> > fromTable.asTable().getFields();
>> >> > for (int x = 0; x < joinTables.size(); x++) {
>> >>
>> >> > allFields.addAll(joinTables.get(x).asTable().getFields());
>> >> > }
>> >>
>> >> > if (!filterWhereClauseList.isEmpty()) {
>> >> > for (FilterWhereClause[] fwc :
>> >> > filterWhereClauseList) {
>> >> > for (int i = 0; i < fwc.length; i++) {
>> >> > final FilterWhereClause fwc1 = fwc[i];
>> >> > if (fwc1 != null) {
>> >> > Field<?> f = null;
>> >> > //first, apply primary substitution
>> >> > if
>> >> > (replacementJoinFieldsNames.containsKey(fwc1.getAttributeName()))
>> >> > {
>> >> > f =
>> >> > replacementJoinFieldsNames.get(fwc1.getAttributeName());
>> >> > }
>> >>
>> >> > if (f == null) {
>> >> > //not found, use base
>> >> > f = getFieldByName(allFields,
>> >> > fwc1.getAttributeName());
>> >> > }
>> >> > if (f != null) {
>> >>
>> >> > prepareQuestionConditionsJoin(sq, Operator.AND,
>> >> > (TableField<?, Object>) f, fwc1.getOperator(), fwc1.getValue());
>> >> > }
>> >> > }
>> >> > }
>> >> > }
>> >> > }
>> >>
>> >> > if (!currentSortedColumns.isEmpty()) {
>> >> > int x = 0;
>> >> > for (String col : (List<String>)
>> >> > currentSortedColumns) {
>> >> > String dir = (String)
>> >> > currentSortedColumnsVersus.get(x);
>> >> > Field<?> f = null;
>> >> > //first, apply primary substitution
>> >> > if
>> >> > (replacementJoinFieldsNames.containsKey(col)) {
>> >> > f = replacementJoinFieldsNames.get(col);
>> >> > }
>> >>
>> >> > if (f == null) {
>> >> > //not found, use base
>> >> > f = getFieldByName(allFields, col);
>> >> > }
>> >>
>> >> > if (f != null) {
>> >> > prepareQuestionOrdeByJoin(sq,
>> >> > (TableField<?, Object>) f, dir);
>> >> > }
>> >> > x++;
>> >> > }
>> >> > }
>> >>
>> >> > return sq;
>> >> > }
>> >>
>> >> > public static <R extends Record> SimpleSelectQuery<R>
>> >> > compileQuery(UpdatableTable<R> table,
>> >> > Collection<FilterWhereClause[]> filterWhereClauseList,
>> >> > List<String> currentSortedColumns, List<String>
>> >> > currentSortedColumnsVersus) {
>> >> > PublicFactory vs = new
>> >> >
>> >> > PublicFactory((Connection)SpringLoader.getBean(\"datasourceConnection\"));
>> >> > SimpleSelectQuery<R> qb = vs.selectQuery(table);
>> >> > //ok, get fields from table
>> >>
>> >> > List<Field<?>> allFields = table.asTable().getFields();
>> >>
>> >> > if (!filterWhereClauseList.isEmpty()) {
>> >> > for (FilterWhereClause[] fwc :
>> >> > filterWhereClauseList) {
>> >> > for (int i = 0; i < fwc.length; i++) {
>> >> > FilterWhereClause fwc1 = fwc[i];
>> >> > if (fwc1 != null) {
>> >> > Field<?> f =
>> >> > getFieldByName(allFields, fwc1.getAttributeName());
>> >> > if (f != null) {
>> >> > prepareQuestionConditions(qb,
>> >> > Operator.AND, (TableField<?, Object>) f, fwc1.getOperator(),
>> >> > fwc1.getValue());
>> >> > }
>> >> > }
>> >> > }
>> >> > }
>> >> > }
>> >>
>> >> > if (!currentSortedColumns.isEmpty()) {
>> >> > int x = 0;
>> >> > for (String col : (List<String>)
>> >> > currentSortedColumns) {
>> >> > String dir = (String)
>> >> > currentSortedColumnsVersus.get(x);
>> >> > Field<?> f = getFieldByName(allFields, col);
>> >> > if (f != null) {
>> >> > prepareQuestionOrdeBy(qb, (TableField<?,
>> >> > Object>) f, dir);
>> >> > }
>> >> > x++;
>> >> > }
>> >> > }
>> >>
>> >> > return qb;
>> >> > }
>> >>
>> >> > private static Field<?> getFieldByName(List<Field<?>>
>> >> > allFields, final String name) {
>> >> > Field<?> foundedFiled = (Field<?>)
>> >> > CollectionUtils.find(allFields, new Predicate() {
>> >> > @Override
>> >> > public boolean evaluate(Object o) {
>> >> > return ((Field<?>)
>> >> > o).getName().trim().equalsIgnoreCase(name.trim());
>> >> > }
>> >> > });
>> >>
>> >> > if (foundedFiled != null) {
>> >> > return foundedFiled;
>> >> > } else {
>> >> > return null;
>> >> > }
>> >> > }
>> >>
>> >> > private static void
>> >> > prepareQuestionConditions(SimpleSelectQuery<?> ssq, Operator
>> >> > o, TableField<?, Object> field, String condition, Object
>> >> > valueToCompare) {
>> >>
>> >> > if (condition != null && condition.trim().length() >
>> >> > 0) {
>> >> > if (condition.equals(\">\")) {
>> >> > ssq.addConditions(o,
>> >> > field.greaterThan(valueToCompare));
>> >> > } else if (condition.equals(\">=\")) {
>> >> > ssq.addConditions(o,
>> >> > field.greaterOrEqual(valueToCompare));
>> >> > } else if (condition.equals(\"<\")) {
>> >> > ssq.addConditions(o,
>> >> > field.lessThan(valueToCompare));
>> >> > } else if (condition.equals(\"<=\")) {
>> >> > ssq.addConditions(o,
>> >> > field.lessOrEqual(valueToCompare));
>> >> > } else if (condition.equals(\"=\")) {
>> >> > ssq.addConditions(o,
>> >> > field.equal(valueToCompare));
>> >> > } else if (condition.equals(\"is null\")) {
>> >> > ssq.addConditions(o, field.isNull());
>> >> > } else if (condition.equals(\"is not null\")) {
>> >> > ssq.addConditions(o, field.isNotNull());
>> >> > } else if (condition.equals(\"in\")) {
>> >> > ssq.addConditions(o, field.in(valueToCompare));
>> >> > } else if (condition.equals(\"not in\")) {
>> >> > ssq.addConditions(o, field.in(valueToCompare));
>> >> > } else if (condition.equals(\"<>\")) {
>> >> > ssq.addConditions(o,
>> >> > field.notEqual(valueToCompare));
>> >> > } else if (condition.equals(\"like\")) {
>> >> > ssq.addConditions(o, field.like((String)
>> >> > valueToCompare));
>> >> > }
>> >> > }
>> >>
>> >> > }
>> >>
>> >> > private static void
>> >> > prepareQuestionConditionsJoin(SelectQuery ssq, Operator o,
>> >> > TableField<?, Object> field, String condition, Object
>> >> > valueToCompare) {
>> >>
>> >> > if (condition != null && condition.trim().length() >
>> >> > 0) {
>> >> > if (condition.equals(\">\")) {
>> >> > ssq.addConditions(o,
>> >> > field.greaterThan(valueToCompare));
>> >> > } else if (condition.equals(\">=\")) {
>> >> > ssq.addConditions(o,
>> >> > field.greaterOrEqual(valueToCompare));
>> >> > } else if (condition.equals(\"<\")) {
>> >> > ssq.addConditions(o,
>> >> > field.lessThan(valueToCompare));
>> >> > } else if (condition.equals(\"<=\")) {
>> >> > ssq.addConditions(o,
>> >> > field.lessOrEqual(valueToCompare));
>> >> > } else if (condition.equals(\"=\")) {
>> >> > ssq.addConditions(o,
>> >> > field.equal(valueToCompare));
>> >> > } else if (condition.equals(\"is null\")) {
>> >> > ssq.addConditions(o, field.isNull());
>> >> > } else if (condition.equals(\"is not null\")) {
>> >> > ssq.addConditions(o, field.isNotNull());
>> >> > } else if (condition.equals(\"in\")) {
>> >> > ssq.addConditions(o, field.in(valueToCompare));
>> >> > } else if (condition.equals(\"not in\")) {
>> >> > ssq.addConditions(o, field.in(valueToCompare));
>> >> > } else if (condition.equals(\"<>\")) {
>> >> > ssq.addConditions(o,
>> >> > field.notEqual(valueToCompare));
>> >> > } else if (condition.equals(\"like\")) {
>> >> > ssq.addConditions(o, field.like((String)
>> >> > valueToCompare));
>> >> > }
>> >> > }
>> >>
>> >> > }
>> >>
>> >> > private static void
>> >> > prepareQuestionOrdeBy(SimpleSelectQuery<?> ssq,
>> >> > TableField<?, Object> field, String direction) {
>> >> > if (direction != null && direction.trim().length() >
>> >> > 0) {
>> >> > ssq.addOrderBy(direction.equalsIgnoreCase(\"ASC\")
>> >> > ? field.asc() : field.desc());
>> >> > }
>> >> > }
>> >>
>> >> > private static void
>> >> > prepareQuestionOrdeByJoin(SelectQuery ssq, TableField<?,
>> >> > Object> field, String direction) {
>> >> > if (direction != null && direction.trim().length() >
>> >> > 0) {
>> >> > ssq.addOrderBy(direction.equalsIgnoreCase(\"ASC\")
>> >> > ? field.asc() : field.desc());
>> >> > }
>> >> > }
>> >> > }
>> >>
>> >> > NOW, the calling code:
>> >>
>> >> > Map<String, Field<?>> repMap = new HashMap<String, Field<?>>();
>> >> > repMap.put(\"provinceId\", Province.PROVINCE.CODE);
>> >>
>> >> > List<Field<?>> allfields =
>> >> > Municipality.MUNICIPALITY.asTable().getFields();
>> >> > SelectQuery qb = new
>> >> > QueryComposerUtil().compileQueryJoinOne(Municipality.MUNICIPALITY,
>> >> > allfields, Province.PROVINCE,
>> >> > JoinType.LEFT_OUTER_JOIN,
>> >> > Municipality.MUNICIPALITY.PROVINCE_ID.equal(Province.PROVINCE.ID),
>> >> > repMap,
>> >> > filteredColumns.values(),
>> >> > currentSortedColumns, currentSortedVersusColumns);
>> >>
>> >> > The problem appears in
>> >> > Municipality.MUNICIPALITY.asTable().getFields();
>> >> > Remember: Province.PROVINCE has COUNTRY foreign key.
>> >> > If i call the joined table (Province.PROVINCE) in a separate
>> >> > call and next i call the previous code, I recive a error:
>> >>
>> >> > AbstractQuery.execute; SQL [select
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"ID\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"ISTAT_CODE\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"DESCRIPTION\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"CAP\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"CADASTRE_CODE\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"PROVINCE_ID\",
>> >> > \"PUBLIC\".\"PROVINCE\".\"ID\", \"PUBLIC\".\"PROVINCE\".\"CODE\",
>> >> > \"PUBLIC\".\"PROVINCE\".\"COUNTRY_ID\",
>> >> > \"PUBLIC\".\"COUNTRY\".\"ID\",
>> >> > \"PUBLIC\".\"COUNTRY\".\"CODE\",
>> >> > \"PUBLIC\".\"COUNTRY\".\"DESCRIPTION\"
>> >> > from \"PUBLIC\".\"MUNICIPALITY\" left outer join
>> >> > \"PUBLIC\".\"PROVINCE\" on
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"PROVINCE_ID\"
>> >> > = \"PUBLIC\".\"PROVINCE\".\"ID\" order by
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"DESCRIPTION\" desc]; Column
>> >> > \"PUBLIC.COUNTRY.ID\" not found; SQL statement:
>> >> > select \"PUBLIC\".\"MUNICIPALITY\".\"ID\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"ISTAT_CODE\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"DESCRIPTION\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"CAP\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"CADASTRE_CODE\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"PROVINCE_ID\",
>> >> > \"PUBLIC\".\"PROVINCE\".\"ID\", \"PUBLIC\".\"PROVINCE\".\"CODE\",
>> >> > \"PUBLIC\".\"PROVINCE\".\"COUNTRY_ID\",
>> >> > \"PUBLIC\".\"COUNTRY\".\"ID\",
>> >> > \"PUBLIC\".\"COUNTRY\".\"CODE\",
>> >> > \"PUBLIC\".\"COUNTRY\".\"DESCRIPTION\"
>> >> > from \"PUBLIC\".\"MUNICIPALITY\" left outer join
>> >> > \"PUBLIC\".\"PROVINCE\" on
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"PROVINCE_ID\"
>> >> > = \"PUBLIC\".\"PROVINCE\".\"ID\" order by
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"DESCRIPTION\" desc [42122-164]\' is
>> >> > not defined.
>> >>
>> >> > But, why \"PUBLIC\".\"COUNTRY\" is included into select query if
>> >> > i use List<Field<?>> allfields =
>> >> > Municipality.MUNICIPALITY.asTable().getFields() ???
>> >>
>> >> > I\'ve debugged
>> >> > Municipality.MUNICIPALITY.asTable().getFields() and i\'ve
>> >> > found that the getFields returns me all previous getted
>> >> > fields, from other tables. If i recall getFields, the
>> >> > returned list is exponentially moltiplicated!!!
>> >> > (100,200,400,800 times)
>> >>
>> >> > This mail could be strange. If you want i send you the
>> >> > entire netbeans project (it careate the db at runtime in
>> >> > home dir, but you can examite the source code yourself).
>> >>
>> >> > Feel free to ask me anything,
>> >>
>> >> > My directy mail is [email protected]
>> >>
>> >> > Best regards,
>> >>
>> >> > Agharta
>> >>
>> >> > --
>> >> > This message was sent to your SourceForge.net email alias via the web
>> >> > mail form. You may reply to this message directly, or
>> >> > viahttps://sourceforge.net/sendmessage.php?touser=838732
>> >> > To update your email alias preferences, please
>> >> > visithttps://sourceforge.net/account
>
>
> Il giorno giovedì 1 marzo 2012 15:39:20 UTC+1, Lukas Eder ha scritto:
>>
>> Hello Agharta,
>>
>> You can attach files to mails to the user group
>>
>> Cheers
>> Lukas
>>
>> 2012/3/1 Agharta agharta <[email protected]>:
>> > Hi Mr Lukas Eder,
>> > Thank you for the quick reply.
>> >
>> > To find the problem, i've created a test case, with scripts and i have
>> > simulated the problem.
>> >
>> > How to send the test case zip file?
>> >
>> > Thank you,
>> >
>> > Agharta
>> >
>> >
>> >
>> > On 1 Mar, 13:42, Lukas Eder <[email protected]> wrote:
>> >> Hello Agharta,
>> >>
>> >> Please use the jooq-user group for support requests of this kind.
>> >> I cannot reproduce the issue you're mentioning by repetitively calling
>> >> something of this kind:
>> >>
>> >> Municipality.MUNICIPALITY.asTable().getFields()
>> >> Municipality.MUNICIPALITY.asTable().getFields()
>> >> Municipality.MUNICIPALITY.asTable().getFields()
>> >>
>> >> I always get the same list of fields. Beware that if you join two
>> >> tables, the resulting join table will contain all combined fields. I
>> >> suspect you join tables multiple times, which might lead to the issue
>> >> you're encountering. You will find some documentation about table
>> >> sources here:http://www.jooq.org/manual/DSL/TABLESOURCE/
>> >>
>> >> Also, note that jOOQ artefacts are NOT thread-safe. Furthermore, some
>> >> artefacts are NOT immutable. Maybe you are re-using a SelectQuery
>> >> object from a previous query?
>> >>
>> >> If you wish any further analysis on my side, please narrow down the
>> >> problematic code to the bit that is causing the problem. It is hard to
>> >> reproduce an issue from so much code.
>> >>
>> >> Cheers
>> >> Lukas
>> >>
>> >> 2012/3/1 agharta <[email protected]>:
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >>
>> >> > Hi Mr,
>> >> > I\'m using jooq in a complex system: Netbeans Platform +
>> >> > Openswing + Spring + H2.
>> >>
>> >> > I\'ve created a wrapper class thats auto create joins and
>> >> > conditions based on Openswing input.
>> >>
>> >> > But i\'ve a problem:
>> >> > For unknown reasons the call of
>> >> > UpdatableTable.Table.asTable.getFields() sometime returns me
>> >> > many fields, more than Table contains. (fields of other tables).
>> >>
>> >> > I send you the code of wrapper class and a little
>> >> > implementation, you can try it.
>> >>
>> >> > Sorry if i not post this into user group or bugtracking, but
>> >> > i want to known your opinion before creating a ticket.
>> >>
>> >> > THE WRAPPER CLASS (Sorry for the poor code):
>> >> > public class QueryComposerUtil {
>> >>
>> >> > public static <R extends Record> SelectQuery
>> >> > compileQueryJoinOne(UpdatableTable<R> fromTable,
>> >> > List<Field<?>> returningFields, UpdatableTable<?> joinTable,
>> >> > JoinType joinType, Condition joinCondition, Map<String,
>> >> > Field<?>> replacementJoinFieldsNames,
>> >> > Collection<FilterWhereClause[]> filterWhereClauseList,
>> >> > List<String> currentSortedColumns, List<String>
>> >> > currentSortedColumnsVersus) {
>> >> > List<UpdatableTable<?>> joinTables = new
>> >> > ArrayList<UpdatableTable<?>>();
>> >> > List<JoinType> joinTypes = new ArrayList<JoinType>();
>> >> > List<Condition> joinConditions = new
>> >> > ArrayList<Condition>();
>> >> > joinTables.add(joinTable);
>> >> > joinTypes.add(joinType);
>> >>
>> >> > joinConditions.add(joinCondition);
>> >> > return compileQueryJoin(fromTable, returningFields,
>> >> > joinTables, joinTypes, joinConditions,
>> >> > replacementJoinFieldsNames, filterWhereClauseList,
>> >> > currentSortedColumns, currentSortedColumnsVersus);
>> >> > }
>> >>
>> >> > public static <R extends Record> SelectQuery
>> >> > compileQueryJoin(UpdatableTable<R> fromTable, List<Field<?>>
>> >> > returningFields, List<UpdatableTable<?>> joinTables,
>> >> > List<JoinType> joinTypes, List<Condition> joinConditions,
>> >> > Map<String, Field<?>> replacementJoinFieldsNames,
>> >> > Collection<FilterWhereClause[]> filterWhereClauseList,
>> >> > List<String> currentSortedColumns, List<String>
>> >> > currentSortedColumnsVersus) {
>> >> > PublicFactory vs = new
>> >> >
>> >> > PublicFactory((Connection)SpringLoader.getBean(\"datasourceConnection\"));
>> >>
>> >> > SelectQuery sq = vs.selectQuery();
>> >> > sq.addSelect(returningFields);
>> >> > sq.addFrom(fromTable);
>> >> > for (int x = 0; x < joinTables.size(); x++) {
>> >> > sq.addJoin(joinTables.get(x), joinTypes.get(x),
>> >> > joinConditions.get(x));
>> >> > }
>> >>
>> >> > List<Field<?>> allFields =
>> >> > fromTable.asTable().getFields();
>> >> > for (int x = 0; x < joinTables.size(); x++) {
>> >>
>> >> > allFields.addAll(joinTables.get(x).asTable().getFields());
>> >> > }
>> >>
>> >> > if (!filterWhereClauseList.isEmpty()) {
>> >> > for (FilterWhereClause[] fwc :
>> >> > filterWhereClauseList) {
>> >> > for (int i = 0; i < fwc.length; i++) {
>> >> > final FilterWhereClause fwc1 = fwc[i];
>> >> > if (fwc1 != null) {
>> >> > Field<?> f = null;
>> >> > //first, apply primary substitution
>> >> > if
>> >> > (replacementJoinFieldsNames.containsKey(fwc1.getAttributeName()))
>> >> > {
>> >> > f =
>> >> > replacementJoinFieldsNames.get(fwc1.getAttributeName());
>> >> > }
>> >>
>> >> > if (f == null) {
>> >> > //not found, use base
>> >> > f = getFieldByName(allFields,
>> >> > fwc1.getAttributeName());
>> >> > }
>> >> > if (f != null) {
>> >>
>> >> > prepareQuestionConditionsJoin(sq, Operator.AND,
>> >> > (TableField<?, Object>) f, fwc1.getOperator(), fwc1.getValue());
>> >> > }
>> >> > }
>> >> > }
>> >> > }
>> >> > }
>> >>
>> >> > if (!currentSortedColumns.isEmpty()) {
>> >> > int x = 0;
>> >> > for (String col : (List<String>)
>> >> > currentSortedColumns) {
>> >> > String dir = (String)
>> >> > currentSortedColumnsVersus.get(x);
>> >> > Field<?> f = null;
>> >> > //first, apply primary substitution
>> >> > if
>> >> > (replacementJoinFieldsNames.containsKey(col)) {
>> >> > f = replacementJoinFieldsNames.get(col);
>> >> > }
>> >>
>> >> > if (f == null) {
>> >> > //not found, use base
>> >> > f = getFieldByName(allFields, col);
>> >> > }
>> >>
>> >> > if (f != null) {
>> >> > prepareQuestionOrdeByJoin(sq,
>> >> > (TableField<?, Object>) f, dir);
>> >> > }
>> >> > x++;
>> >> > }
>> >> > }
>> >>
>> >> > return sq;
>> >> > }
>> >>
>> >> > public static <R extends Record> SimpleSelectQuery<R>
>> >> > compileQuery(UpdatableTable<R> table,
>> >> > Collection<FilterWhereClause[]> filterWhereClauseList,
>> >> > List<String> currentSortedColumns, List<String>
>> >> > currentSortedColumnsVersus) {
>> >> > PublicFactory vs = new
>> >> >
>> >> > PublicFactory((Connection)SpringLoader.getBean(\"datasourceConnection\"));
>> >> > SimpleSelectQuery<R> qb = vs.selectQuery(table);
>> >> > //ok, get fields from table
>> >>
>> >> > List<Field<?>> allFields = table.asTable().getFields();
>> >>
>> >> > if (!filterWhereClauseList.isEmpty()) {
>> >> > for (FilterWhereClause[] fwc :
>> >> > filterWhereClauseList) {
>> >> > for (int i = 0; i < fwc.length; i++) {
>> >> > FilterWhereClause fwc1 = fwc[i];
>> >> > if (fwc1 != null) {
>> >> > Field<?> f =
>> >> > getFieldByName(allFields, fwc1.getAttributeName());
>> >> > if (f != null) {
>> >> > prepareQuestionConditions(qb,
>> >> > Operator.AND, (TableField<?, Object>) f, fwc1.getOperator(),
>> >> > fwc1.getValue());
>> >> > }
>> >> > }
>> >> > }
>> >> > }
>> >> > }
>> >>
>> >> > if (!currentSortedColumns.isEmpty()) {
>> >> > int x = 0;
>> >> > for (String col : (List<String>)
>> >> > currentSortedColumns) {
>> >> > String dir = (String)
>> >> > currentSortedColumnsVersus.get(x);
>> >> > Field<?> f = getFieldByName(allFields, col);
>> >> > if (f != null) {
>> >> > prepareQuestionOrdeBy(qb, (TableField<?,
>> >> > Object>) f, dir);
>> >> > }
>> >> > x++;
>> >> > }
>> >> > }
>> >>
>> >> > return qb;
>> >> > }
>> >>
>> >> > private static Field<?> getFieldByName(List<Field<?>>
>> >> > allFields, final String name) {
>> >> > Field<?> foundedFiled = (Field<?>)
>> >> > CollectionUtils.find(allFields, new Predicate() {
>> >> > @Override
>> >> > public boolean evaluate(Object o) {
>> >> > return ((Field<?>)
>> >> > o).getName().trim().equalsIgnoreCase(name.trim());
>> >> > }
>> >> > });
>> >>
>> >> > if (foundedFiled != null) {
>> >> > return foundedFiled;
>> >> > } else {
>> >> > return null;
>> >> > }
>> >> > }
>> >>
>> >> > private static void
>> >> > prepareQuestionConditions(SimpleSelectQuery<?> ssq, Operator
>> >> > o, TableField<?, Object> field, String condition, Object
>> >> > valueToCompare) {
>> >>
>> >> > if (condition != null && condition.trim().length() >
>> >> > 0) {
>> >> > if (condition.equals(\">\")) {
>> >> > ssq.addConditions(o,
>> >> > field.greaterThan(valueToCompare));
>> >> > } else if (condition.equals(\">=\")) {
>> >> > ssq.addConditions(o,
>> >> > field.greaterOrEqual(valueToCompare));
>> >> > } else if (condition.equals(\"<\")) {
>> >> > ssq.addConditions(o,
>> >> > field.lessThan(valueToCompare));
>> >> > } else if (condition.equals(\"<=\")) {
>> >> > ssq.addConditions(o,
>> >> > field.lessOrEqual(valueToCompare));
>> >> > } else if (condition.equals(\"=\")) {
>> >> > ssq.addConditions(o,
>> >> > field.equal(valueToCompare));
>> >> > } else if (condition.equals(\"is null\")) {
>> >> > ssq.addConditions(o, field.isNull());
>> >> > } else if (condition.equals(\"is not null\")) {
>> >> > ssq.addConditions(o, field.isNotNull());
>> >> > } else if (condition.equals(\"in\")) {
>> >> > ssq.addConditions(o, field.in(valueToCompare));
>> >> > } else if (condition.equals(\"not in\")) {
>> >> > ssq.addConditions(o, field.in(valueToCompare));
>> >> > } else if (condition.equals(\"<>\")) {
>> >> > ssq.addConditions(o,
>> >> > field.notEqual(valueToCompare));
>> >> > } else if (condition.equals(\"like\")) {
>> >> > ssq.addConditions(o, field.like((String)
>> >> > valueToCompare));
>> >> > }
>> >> > }
>> >>
>> >> > }
>> >>
>> >> > private static void
>> >> > prepareQuestionConditionsJoin(SelectQuery ssq, Operator o,
>> >> > TableField<?, Object> field, String condition, Object
>> >> > valueToCompare) {
>> >>
>> >> > if (condition != null && condition.trim().length() >
>> >> > 0) {
>> >> > if (condition.equals(\">\")) {
>> >> > ssq.addConditions(o,
>> >> > field.greaterThan(valueToCompare));
>> >> > } else if (condition.equals(\">=\")) {
>> >> > ssq.addConditions(o,
>> >> > field.greaterOrEqual(valueToCompare));
>> >> > } else if (condition.equals(\"<\")) {
>> >> > ssq.addConditions(o,
>> >> > field.lessThan(valueToCompare));
>> >> > } else if (condition.equals(\"<=\")) {
>> >> > ssq.addConditions(o,
>> >> > field.lessOrEqual(valueToCompare));
>> >> > } else if (condition.equals(\"=\")) {
>> >> > ssq.addConditions(o,
>> >> > field.equal(valueToCompare));
>> >> > } else if (condition.equals(\"is null\")) {
>> >> > ssq.addConditions(o, field.isNull());
>> >> > } else if (condition.equals(\"is not null\")) {
>> >> > ssq.addConditions(o, field.isNotNull());
>> >> > } else if (condition.equals(\"in\")) {
>> >> > ssq.addConditions(o, field.in(valueToCompare));
>> >> > } else if (condition.equals(\"not in\")) {
>> >> > ssq.addConditions(o, field.in(valueToCompare));
>> >> > } else if (condition.equals(\"<>\")) {
>> >> > ssq.addConditions(o,
>> >> > field.notEqual(valueToCompare));
>> >> > } else if (condition.equals(\"like\")) {
>> >> > ssq.addConditions(o, field.like((String)
>> >> > valueToCompare));
>> >> > }
>> >> > }
>> >>
>> >> > }
>> >>
>> >> > private static void
>> >> > prepareQuestionOrdeBy(SimpleSelectQuery<?> ssq,
>> >> > TableField<?, Object> field, String direction) {
>> >> > if (direction != null && direction.trim().length() >
>> >> > 0) {
>> >> > ssq.addOrderBy(direction.equalsIgnoreCase(\"ASC\")
>> >> > ? field.asc() : field.desc());
>> >> > }
>> >> > }
>> >>
>> >> > private static void
>> >> > prepareQuestionOrdeByJoin(SelectQuery ssq, TableField<?,
>> >> > Object> field, String direction) {
>> >> > if (direction != null && direction.trim().length() >
>> >> > 0) {
>> >> > ssq.addOrderBy(direction.equalsIgnoreCase(\"ASC\")
>> >> > ? field.asc() : field.desc());
>> >> > }
>> >> > }
>> >> > }
>> >>
>> >> > NOW, the calling code:
>> >>
>> >> > Map<String, Field<?>> repMap = new HashMap<String, Field<?>>();
>> >> > repMap.put(\"provinceId\", Province.PROVINCE.CODE);
>> >>
>> >> > List<Field<?>> allfields =
>> >> > Municipality.MUNICIPALITY.asTable().getFields();
>> >> > SelectQuery qb = new
>> >> > QueryComposerUtil().compileQueryJoinOne(Municipality.MUNICIPALITY,
>> >> > allfields, Province.PROVINCE,
>> >> > JoinType.LEFT_OUTER_JOIN,
>> >> > Municipality.MUNICIPALITY.PROVINCE_ID.equal(Province.PROVINCE.ID),
>> >> > repMap,
>> >> > filteredColumns.values(),
>> >> > currentSortedColumns, currentSortedVersusColumns);
>> >>
>> >> > The problem appears in
>> >> > Municipality.MUNICIPALITY.asTable().getFields();
>> >> > Remember: Province.PROVINCE has COUNTRY foreign key.
>> >> > If i call the joined table (Province.PROVINCE) in a separate
>> >> > call and next i call the previous code, I recive a error:
>> >>
>> >> > AbstractQuery.execute; SQL [select
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"ID\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"ISTAT_CODE\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"DESCRIPTION\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"CAP\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"CADASTRE_CODE\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"PROVINCE_ID\",
>> >> > \"PUBLIC\".\"PROVINCE\".\"ID\", \"PUBLIC\".\"PROVINCE\".\"CODE\",
>> >> > \"PUBLIC\".\"PROVINCE\".\"COUNTRY_ID\",
>> >> > \"PUBLIC\".\"COUNTRY\".\"ID\",
>> >> > \"PUBLIC\".\"COUNTRY\".\"CODE\",
>> >> > \"PUBLIC\".\"COUNTRY\".\"DESCRIPTION\"
>> >> > from \"PUBLIC\".\"MUNICIPALITY\" left outer join
>> >> > \"PUBLIC\".\"PROVINCE\" on
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"PROVINCE_ID\"
>> >> > = \"PUBLIC\".\"PROVINCE\".\"ID\" order by
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"DESCRIPTION\" desc]; Column
>> >> > \"PUBLIC.COUNTRY.ID\" not found; SQL statement:
>> >> > select \"PUBLIC\".\"MUNICIPALITY\".\"ID\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"ISTAT_CODE\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"DESCRIPTION\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"CAP\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"CADASTRE_CODE\",
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"PROVINCE_ID\",
>> >> > \"PUBLIC\".\"PROVINCE\".\"ID\", \"PUBLIC\".\"PROVINCE\".\"CODE\",
>> >> > \"PUBLIC\".\"PROVINCE\".\"COUNTRY_ID\",
>> >> > \"PUBLIC\".\"COUNTRY\".\"ID\",
>> >> > \"PUBLIC\".\"COUNTRY\".\"CODE\",
>> >> > \"PUBLIC\".\"COUNTRY\".\"DESCRIPTION\"
>> >> > from \"PUBLIC\".\"MUNICIPALITY\" left outer join
>> >> > \"PUBLIC\".\"PROVINCE\" on
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"PROVINCE_ID\"
>> >> > = \"PUBLIC\".\"PROVINCE\".\"ID\" order by
>> >> > \"PUBLIC\".\"MUNICIPALITY\".\"DESCRIPTION\" desc [42122-164]\' is
>> >> > not defined.
>> >>
>> >> > But, why \"PUBLIC\".\"COUNTRY\" is included into select query if
>> >> > i use List<Field<?>> allfields =
>> >> > Municipality.MUNICIPALITY.asTable().getFields() ???
>> >>
>> >> > I\'ve debugged
>> >> > Municipality.MUNICIPALITY.asTable().getFields() and i\'ve
>> >> > found that the getFields returns me all previous getted
>> >> > fields, from other tables. If i recall getFields, the
>> >> > returned list is exponentially moltiplicated!!!
>> >> > (100,200,400,800 times)
>> >>
>> >> > This mail could be strange. If you want i send you the
>> >> > entire netbeans project (it careate the db at runtime in
>> >> > home dir, but you can examite the source code yourself).
>> >>
>> >> > Feel free to ask me anything,
>> >>
>> >> > My directy mail is [email protected]
>> >>
>> >> > Best regards,
>> >>
>> >> > Agharta
>> >>
>> >> > --
>> >> > This message was sent to your SourceForge.net email alias via the web
>> >> > mail form. You may reply to this message directly, or
>> >> > viahttps://sourceforge.net/sendmessage.php?touser=838732
>> >> > To update your email alias preferences, please
>> >> > visithttps://sourceforge.net/account
