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
