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
