Author: jpepin Date: 2010-08-21 13:51:36 +0200 (Sat, 21 Aug 2010) New Revision: 3011 Url: http://chorem.org/repositories/revision/lima/3011 Log: Optimize topiaquery and lazy loading. Modified: trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/FinancialTransactionServiceImpl.java trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/ReportServiceImpl.java trunk/lima-business/src/main/java/org/chorem/lima/business/ejbinterface/FinancialTransactionService.java trunk/lima-business/src/main/java/org/chorem/lima/business/ejbinterface/FinancialTransactionServiceLocal.java trunk/lima-callao/src/main/java/org/chorem/lima/entity/AccountDAOImpl.java trunk/lima-callao/src/main/xmi/accounting.properties trunk/lima-callao/src/main/xmi/accounting.zargo trunk/lima-swing/src/main/java/org/chorem/lima/ui/financialtransactionunbalanced/FinancialTransactionUnbalancedTableModel.java trunk/lima-swing/src/main/java/org/chorem/lima/ui/home/FinancialTransactionsPane.java Modified: trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/FinancialTransactionServiceImpl.java =================================================================== --- trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/FinancialTransactionServiceImpl.java 2010-08-20 23:46:18 UTC (rev 3010) +++ trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/FinancialTransactionServiceImpl.java 2010-08-21 11:51:36 UTC (rev 3011) @@ -21,9 +21,7 @@ import java.math.BigDecimal; import java.util.Date; -import java.util.HashSet; import java.util.List; -import java.util.Set; import javax.ejb.Stateless; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; @@ -147,8 +145,12 @@ FinancialTransactionDAO transactionDAO = LimaCallaoDAOHelper.getFinancialTransactionDAO(topiaTransaction); + String financialPeriodProperty = TopiaQuery.getProperty(FinancialTransaction.FINANCIAL_PERIOD); + String entryBookLabelProperty = TopiaQuery.getProperty(FinancialTransaction.ENTRY_BOOK); + String accountProperty = TopiaQuery.getProperty(FinancialTransaction.ENTRY, Entry.ACCOUNT); + // filter with topia query - TopiaQuery query = transactionDAO.createQuery(); + TopiaQuery query = transactionDAO.createQuery("T"); if (entryBook != null) { query.addEquals("entryBook", entryBook); @@ -157,19 +159,13 @@ if (financialPeriod != null) { query.addEquals("financialPeriod", financialPeriod); } - query.addOrder(FinancialTransaction.TOPIA_CREATE_DATE); + query.addDistinct() + .addOrder("T."+FinancialTransaction.TOPIA_CREATE_DATE) + //load for lazy + .addLoad(financialPeriodProperty) + .addLoad(entryBookLabelProperty) + .addFetch("T."+accountProperty); financialTransactions = transactionDAO.findAllByQuery(query); - - //IMPORTANT : LOADING ENTRIES AND IS COLUMN FOR NO LAZY EXCEPTION - for (FinancialTransaction financialTransaction : financialTransactions) { - financialTransaction.getEntryBook(); - - for (Entry entry : financialTransaction.getEntry()) { - entry.getAccount(); - entry.getFinancialTransaction(); - entry.getFinancialTransaction().getFinancialPeriod(); - } - } } catch (TopiaException ex) { doCatch(topiaTransaction, ex, log); @@ -186,44 +182,35 @@ * Get unbalanced financialtransaction from selected fiscalperiod */ @Override - public Set<FinancialTransaction> getAllInexactFinancialTransactions(FiscalPeriod fiscalPeriod) throws LimaException { + public List<FinancialTransaction> getAllInexactFinancialTransactions(FiscalPeriod fiscalPeriod) throws LimaException { TopiaContext topiaContext = null; - Set<FinancialTransaction> result = new HashSet<FinancialTransaction>(); + List<FinancialTransaction> result = null; try { topiaContext = beginTransaction(); FinancialTransactionDAO financialTransactionDAO = LimaCallaoDAOHelper.getFinancialTransactionDAO(topiaContext); - TopiaQuery query = financialTransactionDAO.createQuery("E"); - query.addFrom(FiscalPeriod.class, "F") - .addWhere("E.amountCredit != E.amountDebit OR E.entryBook = null") - .addInElements("E.financialPeriod", "F.financialPeriod") - .addEquals("F", fiscalPeriod); - result.addAll(financialTransactionDAO.findAllByQuery(query)); - TopiaQuery query2 = financialTransactionDAO.createQuery("T"); - query2.addFrom(Entry.class, "E") - .addWhere("E.account = null") - .addInElements("E", "T.entry"); - result.addAll(financialTransactionDAO.findAllByQuery(query2)); + String financialPeriodProperty = TopiaQuery.getProperty(FinancialTransaction.FINANCIAL_PERIOD); + String entryBookLabelProperty = TopiaQuery.getProperty(FinancialTransaction.ENTRY_BOOK); + String accountProperty = TopiaQuery.getProperty(Entry.ACCOUNT); - /* Just for fun : IN HQL - result = topiaContext.find( - "FROM org.chorem.lima.entity.FinancialTransaction "+ - "WHERE amountCredit != amountDebit and (financialPeriod in (" + - "FROM org.chorem.lima.entity.FinancialPeriod " + - "WHERE (fiscalPeriod = :fiscalPeriod)))","fiscalPeriod",fiscalPeriod);*/ + + + TopiaQuery query = financialTransactionDAO.createQuery("T"); + query.addFrom(FiscalPeriod.class, "F") + .addDistinct() + .addLeftJoin("T."+FinancialTransaction.ENTRY, "E", true) + .addWhere("T.amountCredit != T.amountDebit OR E.account = null OR T.entryBook = null") + .addInElements("T.financialPeriod", "F.financialPeriod") + .addEquals("F", fiscalPeriod) + // load lazy + .addLeftJoin("E."+accountProperty,"A", true) + .addLoad(financialPeriodProperty) + .addLoad(entryBookLabelProperty); - //IMPORTANT : LOADING ENTRIES AND IS COLUMN FOR NO LAZY EXCEPTION - for (FinancialTransaction financialTransaction : result) { - - for (Entry entry : financialTransaction.getEntry()) { - entry.getAccount(); - entry.getFinancialTransaction(); - entry.getFinancialTransaction().getEntryBook(); - entry.getFinancialTransaction().getFinancialPeriod(); - } - } + result = financialTransactionDAO.findAllByQuery(query); + } catch (TopiaException ex) { @@ -241,7 +228,7 @@ List<FinancialTransaction> result = null; try { topiaContext = beginTransaction(); - result = getAllFinancialTransactionsBalanced(topiaContext); + result = getAllFinancialTransactionsBalanced(fiscalPeriod, topiaContext); } catch (TopiaException ex) { doCatch(topiaContext, ex, log); @@ -256,28 +243,24 @@ /** * Get balanced financialtransaction from selected fiscalperiod */ - @Override - public List<FinancialTransaction> getAllFinancialTransactionsBalanced(TopiaContext topiaContext) throws LimaException { + public List<FinancialTransaction> getAllFinancialTransactionsBalanced(FiscalPeriod fiscalPeriod, TopiaContext topiaContext) throws LimaException { List<FinancialTransaction> result = null; try { FinancialTransactionDAO financialTransactionDAO = LimaCallaoDAOHelper.getFinancialTransactionDAO(topiaContext); + + // WARN No load lazy, modify query with addLoad or addFetch if loading is necessary + TopiaQuery query = financialTransactionDAO.createQuery("T"); + query.addFrom(FiscalPeriod.class, "F") + .addLeftJoin("T."+FinancialTransaction.ENTRY, "E", false) + .addWhere("T.amountCredit = T.amountDebit") + .addNotNull("E.account") + .addNotNull("T.entryBook") + .addInElements("T.financialPeriod", "F.financialPeriod") + .addEquals("F", fiscalPeriod); - TopiaQuery query = financialTransactionDAO.createQuery("E"); - query.addWhere("E.amountCredit = E.amountDebit") - .addNotNull("E.entryBook"); - result = financialTransactionDAO.findAllByQuery(query); - - //IMPORTANT : LOADING ENTRIES AND IS COLUMN FOR NO LAZY EXCEPTION - for (FinancialTransaction financialTransaction : result) { - - for (Entry entry : financialTransaction.getEntry()) { - entry.getAccount(); - entry.getFinancialTransaction(); - entry.getFinancialTransaction().getEntryBook(); - entry.getFinancialTransaction().getFinancialPeriod(); - } - } + result = financialTransactionDAO.findAllByQuery(query); + } catch (TopiaException ex) { doCatch(topiaContext, ex, log); @@ -648,7 +631,8 @@ } else if (accountsList != null){ AccountDAO accountDAO = LimaCallaoDAOHelper.getAccountDAO(topiaContext); - List<Account> accounts = accountDAO.stringToListAccounts(accountsList, false); + List<Account> accounts = + accountDAO.stringToListAccounts(accountsList, false); StringBuffer accountNumbers = new StringBuffer("0,"); int max = accounts.size(); for (int i = 1; i <= max; i++) { Modified: trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/ReportServiceImpl.java =================================================================== --- trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/ReportServiceImpl.java 2010-08-20 23:46:18 UTC (rev 3010) +++ trunk/lima-business/src/main/java/org/chorem/lima/business/ejb/ReportServiceImpl.java 2010-08-21 11:51:36 UTC (rev 3011) @@ -155,23 +155,24 @@ /** * Query for find entries for accountsreports and balancereports - * Just balanced transaction are calculated + * Just exact and balanced transaction are calculated */ public TopiaQuery createEntryQuery(Account account, Date beginDate, Date endDate, EntryDAO entryDAO, String queryAlias) throws LimaException{ TopiaQuery query = entryDAO.createQuery(queryAlias); - String transactionDateProperty = - TopiaQuery.getProperty(Entry.FINANCIAL_TRANSACTION, - FinancialTransaction.TRANSACTION_DATE); - String amountCreditProperty = - TopiaQuery.getProperty(Entry.FINANCIAL_TRANSACTION, - FinancialTransaction.AMOUNT_CREDIT); - String amountDebitProperty = - TopiaQuery.getProperty(Entry.FINANCIAL_TRANSACTION, - FinancialTransaction.AMOUNT_DEBIT); + String transactionDateProperty = TopiaQuery.getProperty( + Entry.FINANCIAL_TRANSACTION, FinancialTransaction.TRANSACTION_DATE); + String amountCreditProperty = TopiaQuery.getProperty( + Entry.FINANCIAL_TRANSACTION, FinancialTransaction.AMOUNT_CREDIT); + String amountDebitProperty = TopiaQuery.getProperty( + Entry.FINANCIAL_TRANSACTION, FinancialTransaction.AMOUNT_DEBIT); + String entryBookProperty = TopiaQuery.getProperty( + Entry.FINANCIAL_TRANSACTION, FinancialTransaction.ENTRY_BOOK); + query .addWhere(amountCreditProperty+" = "+amountDebitProperty) .addWhere(transactionDateProperty+" BETWEEN :beginDate AND :endDate") + .addNotNull(entryBookProperty) .addParam("beginDate", beginDate) .addParam("endDate", endDate) .addEquals(Entry.ACCOUNT, account); @@ -355,12 +356,6 @@ entriesQuery.addOrder(orderDateProperty) .addOrder(orderIdProperty); reportsDatas.setListEntry(entryDAO.findAllByQuery(entriesQuery)); - - - //TODO 20100603 PEPIN imbriqué les deux conditions de comparator - // tri sur date, tri sur topiaid - //Collections.sort(entries, new EntryFinancialTransactionDateComparator()); - //Collections.sort(entries, new EntryFinancialTransactionComparator()); TopiaQuery amountsQuery = createEntryBookReportsQuery(entryBook, beginDate, endDate, entryDAO, "E"); @@ -427,7 +422,6 @@ FinancialTransaction.ENTRY_BOOK); query .addWhere(amountCreditProperty+" = "+amountDebitProperty) - //TODO check this .addEquals(entryBookProperty, entryBook) .addWhere(transactionDateProperty+" BETWEEN :beginDate AND :endDate") .addParam("beginDate", beginDate) Modified: trunk/lima-business/src/main/java/org/chorem/lima/business/ejbinterface/FinancialTransactionService.java =================================================================== --- trunk/lima-business/src/main/java/org/chorem/lima/business/ejbinterface/FinancialTransactionService.java 2010-08-20 23:46:18 UTC (rev 3010) +++ trunk/lima-business/src/main/java/org/chorem/lima/business/ejbinterface/FinancialTransactionService.java 2010-08-21 11:51:36 UTC (rev 3011) @@ -60,7 +60,7 @@ List<FinancialTransaction> getAllFinancialTransactionsForEntryBookAndFinancialPeriod( EntryBook entryBook, FinancialPeriod period) throws LimaException; - Set<FinancialTransaction> getAllInexactFinancialTransactions(FiscalPeriod fiscalPeriod) throws LimaException; + List<FinancialTransaction> getAllInexactFinancialTransactions(FiscalPeriod fiscalPeriod) throws LimaException; List<FinancialTransaction> getAllFinancialTransactionsBalanced(FiscalPeriod fiscalPeriod) throws LimaException; Modified: trunk/lima-business/src/main/java/org/chorem/lima/business/ejbinterface/FinancialTransactionServiceLocal.java =================================================================== --- trunk/lima-business/src/main/java/org/chorem/lima/business/ejbinterface/FinancialTransactionServiceLocal.java 2010-08-20 23:46:18 UTC (rev 3010) +++ trunk/lima-business/src/main/java/org/chorem/lima/business/ejbinterface/FinancialTransactionServiceLocal.java 2010-08-21 11:51:36 UTC (rev 3011) @@ -19,7 +19,6 @@ package org.chorem.lima.business.ejbinterface; -import java.util.List; import javax.ejb.Local; import org.chorem.lima.business.LimaException; import org.chorem.lima.entity.Entry; @@ -38,8 +37,6 @@ @Local public interface FinancialTransactionServiceLocal extends FinancialTransactionService { - - List<FinancialTransaction> getAllFinancialTransactionsBalanced(TopiaContext topiaContext) throws LimaException; void createFinancialTransactionWithTransaction(FinancialTransaction financialtransaction, TopiaContext topiaContext) throws LimaException; Modified: trunk/lima-callao/src/main/java/org/chorem/lima/entity/AccountDAOImpl.java =================================================================== --- trunk/lima-callao/src/main/java/org/chorem/lima/entity/AccountDAOImpl.java 2010-08-20 23:46:18 UTC (rev 3010) +++ trunk/lima-callao/src/main/java/org/chorem/lima/entity/AccountDAOImpl.java 2010-08-21 11:51:36 UTC (rev 3011) @@ -23,12 +23,16 @@ import java.util.List; import java.util.StringTokenizer; import org.apache.commons.lang.StringUtils; +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; import org.nuiton.topia.TopiaException; import org.nuiton.topia.framework.TopiaQuery; import org.nuiton.topia.framework.TopiaQuery.Op; public class AccountDAOImpl <E extends Account> extends AccountDAOAbstract<E> { + private static final Log log = LogFactory.getLog(AccountDAOImpl.class); + /** * List all subaccounts (no folderaccounts, exclude accounts contains thirdpart accounts) * Sort by accout number in lexicographical order @@ -42,118 +46,98 @@ return (List<Account>) findAllByQuery(query); } - - /** - * Convert string of extends number to list of accounts - * Example '22, 45..48, 67' -> [22, 45, 46, 47, 48, 67] - * SubAccountsMode return list of existing subaccounts ex 6 return : 61, 62, .., 69, 610, .., 619 etc - */ @Override + public Account findSubAccountByNumber(String number) throws TopiaException { + TopiaQuery query = createQuery(); + String subAccountsProperty = TopiaQuery.getProperty(Account.SUB_ACCOUNTS); + String subLedgersProperty = TopiaQuery.getProperty(Account.SUB_LEDGERS); + query.addWhere("not exists elements ("+subAccountsProperty+")") + .addWhere("not exists elements ("+subLedgersProperty+")") + .addEquals(Account.ACCOUNT_NUMBER, number); + return findByQuery(query); + } + + public List<Account> findIntervalAccountByNumber(String accountNumberLow, String accountNumberHigh) throws TopiaException { + TopiaQuery query = createQuery(); + query.addBetween(Account.ACCOUNT_NUMBER, accountNumberLow, accountNumberHigh); + return (List<Account>) findAllByQuery(query); + } + + @Override public List<Account> stringToListAccounts(String selectedAccounts, Boolean subAccountsMode) throws TopiaException { - List<Account> accounts = new ArrayList<Account>(); + HashSet<Account> accounts = new HashSet<Account>(); if (selectedAccounts != null){ //Remove Spaces - selectedAccounts = StringUtils.deleteWhitespace(selectedAccounts); - //use hashset for delete duplicate numbers - HashSet<String> accountNumbers = new HashSet<String>(); - HashSet<String> accountNumbersToRemove = new HashSet<String>(); + String result = StringUtils.deleteWhitespace(selectedAccounts); + Boolean first = true; - StringTokenizer stStar = new StringTokenizer(selectedAccounts, "-"); + StringTokenizer stStar = new StringTokenizer(result, "-"); while (stStar.hasMoreTokens()) { String subString = stStar.nextToken(); + log.debug(subString); //Split comma StringTokenizer stComma = new StringTokenizer(subString, ","); while (stComma.hasMoreTokens()) { String s = stComma.nextToken(); + //if intervall account if (s.contains("..") && !s.endsWith("..")){ //Split .. String stringDoubleDot[] = s.split("\\.\\."); - int lowAccount = Integer.parseInt(stringDoubleDot[0]); - int highAccount = Integer.parseInt(stringDoubleDot[1]); - //prevent to much result, can't let intervall superior to 1000 - if (highAccount - lowAccount <= 10000){ - for (int i=lowAccount; i <= highAccount; i++) { - //if first add accounts, else remove - if (first){ - accountNumbers.add(String.valueOf(i)); - } - else { - accountNumbersToRemove.add(String.valueOf(i)); - } - } - } + List<Account> resultIntervall = + findIntervalAccountByNumber(stringDoubleDot[0], stringDoubleDot[1]); + + //if first add accounts, else remove + if (first){ + accounts.addAll(resultIntervall); + } + else { + accounts.removeAll(resultIntervall); + } } + //else one account else{ - //if first - if (first){ - accountNumbers.add(s); - } - else { - accountNumbersToRemove.add(s); - } + Account account = null; + if (subAccountsMode){ + account = findSubAccountByNumber(s); + } + else { + account = findByAccountNumber(s); + } + //if exist + if (account != null){ + //if first + if (first){ + accounts.add(account); + } + else { + accounts.remove(account); + } + } + //search all account start with accountnumber + else { + TopiaQuery query = createQuery(); + String subAccountsProperty = TopiaQuery.getProperty(Account.SUB_ACCOUNTS); + query.addWhere("not exists elements ("+subAccountsProperty+")") + .addWhere(Account.ACCOUNT_NUMBER, Op.LIKE, s+"%"); + List<Account> accountsResult = (List<Account>) findAllByQuery(query); + if (accountsResult != null){ + //if first + if (first){ + accounts.addAll(accountsResult); + } + else { + accounts.removeAll(accountsResult); + } + } + } } } first=false; } - //add all accounts - for (String accountNumber : accountNumbers) { - Account account = null; - if (subAccountsMode){ - account = findSubAccountByNumber(accountNumber); - } - else { - account = findByAccountNumber(accountNumber); - - } - //add account if exist - if (account != null){ - accounts.add(account); - } - //search all account start with accountnumber - else { - TopiaQuery query = createQuery(); - String subAccountsProperty = TopiaQuery.getProperty(Account.SUB_ACCOUNTS); - //String subLedgersProperty = TopiaQuery.getProperty(Account.SUB_LEDGERS); - query.addWhere("not exists elements ("+subAccountsProperty+")") - //.addWhere("not exists elements ("+subLedgersProperty+")") - .addWhere(Account.ACCOUNT_NUMBER, Op.LIKE, accountNumber+"%"); - List<Account> accountsResult = (List<Account>) findAllByQuery(query); - if (accountsResult != null){ - accounts.addAll(accountsResult); - } - } - } - //remove all accounts - for (String accountNumber : accountNumbersToRemove) { - Account account = null; - if (subAccountsMode){ - account = findSubAccountByNumber(accountNumber); - } - else { - account = findByAccountNumber(accountNumber); - } - if (account != null) { - accounts.remove(account); - } - } } - return accounts; + return new ArrayList(accounts); } - - - - @Override - public Account findSubAccountByNumber(String number) throws TopiaException { - TopiaQuery query = createQuery(); - String subAccountsProperty = TopiaQuery.getProperty(Account.SUB_ACCOUNTS); - String subLedgersProperty = TopiaQuery.getProperty(Account.SUB_LEDGERS); - query.addWhere("not exists elements ("+subAccountsProperty+")") - .addWhere("not exists elements ("+subLedgersProperty+")") - .addEquals(Account.ACCOUNT_NUMBER, number); - return findByQuery(query); - } - } Modified: trunk/lima-callao/src/main/xmi/accounting.properties =================================================================== --- trunk/lima-callao/src/main/xmi/accounting.properties 2010-08-20 23:46:18 UTC (rev 3010) +++ trunk/lima-callao/src/main/xmi/accounting.properties 2010-08-21 11:51:36 UTC (rev 3011) @@ -21,7 +21,6 @@ # lazy org.chorem.lima.entity.Account.attribute.subAccounts.tagvalue.lazy=false org.chorem.lima.entity.Account.attribute.subLedgers.tagvalue.lazy=false -org.chorem.lima.entity.FinancialTransaction.attribute.financialPeriod.tagvalue.lazy=false org.chorem.lima.entity.FiscalPeriod.attribute.financialPeriod.tagvalue.lazy=false org.chorem.lima.entity.FinancialStatement.attribute.subFinancialStatements.tagvalue.lazy=false org.chorem.lima.entity.FinancialStatement.attribute.masterFinancialStatement.tagvalue.lazy=false Modified: trunk/lima-callao/src/main/xmi/accounting.zargo =================================================================== (Binary files differ) Modified: trunk/lima-swing/src/main/java/org/chorem/lima/ui/financialtransactionunbalanced/FinancialTransactionUnbalancedTableModel.java =================================================================== --- trunk/lima-swing/src/main/java/org/chorem/lima/ui/financialtransactionunbalanced/FinancialTransactionUnbalancedTableModel.java 2010-08-20 23:46:18 UTC (rev 3010) +++ trunk/lima-swing/src/main/java/org/chorem/lima/ui/financialtransactionunbalanced/FinancialTransactionUnbalancedTableModel.java 2010-08-21 11:51:36 UTC (rev 3011) @@ -100,7 +100,7 @@ List<Object> results = new ArrayList<Object>(); if(selectedFiscalPeriod != null){ try { - Set<FinancialTransaction> financialtransactions = + List<FinancialTransaction> financialtransactions = financialTransactionService.getAllInexactFinancialTransactions(selectedFiscalPeriod); for (FinancialTransaction financialtransaction : financialtransactions) { results.add(financialtransaction); Modified: trunk/lima-swing/src/main/java/org/chorem/lima/ui/home/FinancialTransactionsPane.java =================================================================== --- trunk/lima-swing/src/main/java/org/chorem/lima/ui/home/FinancialTransactionsPane.java 2010-08-20 23:46:18 UTC (rev 3010) +++ trunk/lima-swing/src/main/java/org/chorem/lima/ui/home/FinancialTransactionsPane.java 2010-08-21 11:51:36 UTC (rev 3011) @@ -21,8 +21,6 @@ import static org.nuiton.i18n.I18n._; import java.awt.Color; import java.util.List; -import java.util.Set; - import javax.swing.JEditorPane; import javax.swing.event.HyperlinkEvent; import javax.swing.event.HyperlinkListener; @@ -101,7 +99,7 @@ List<FiscalPeriod> unblockedFiscalPeriods = fiscalPeriodService.getAllUnblockedFiscalPeriods(); if (unblockedFiscalPeriods.size() != 0){ - Set<FinancialTransaction> financialTransactionsUnbal = + List<FinancialTransaction> financialTransactionsUnbal = financialTransactionService. getAllInexactFinancialTransactions(unblockedFiscalPeriods.get(0)); if (financialTransactionsUnbal.size()>0){