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 via > https://sourceforge.net/sendmessage.php?touser=838732 > To update your email alias preferences, please visit > https://sourceforge.net/account
