List of usage examples for org.hibernate SQLQuery setString
@Deprecated @SuppressWarnings("unchecked") default Query<R> setString(int position, String val)
From source file:org.egov.egf.web.actions.brs.AutoReconcileHelper.java
License:Open Source License
private void findandUpdateDuplicates() { // for payment cheques instrumentNo,debit,accountId combination should be unique else mark it duplicate try {//from www . j av a2 s .co m String duplicates = "select instrumentNo,debit,accountId from " + TABLENAME + " where accountId=:accountId" + " and debit>0 and action='" + BRS_ACTION_TO_BE_PROCESSED + "' group by instrumentNo,debit,accountId having count(*)>1"; final SQLQuery paymentDuplicateChequesQuery = persistenceService.getSession() .createSQLQuery(duplicates); paymentDuplicateChequesQuery.addScalar("instrumentNo").addScalar("debit") .addScalar("accountId", LongType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(AutoReconcileBean.class)); // paymentDuplicateChequesQuery.setParameter("accountId", Long.class); paymentDuplicateChequesQuery.setLong("accountId", accountId); final List<AutoReconcileBean> duplicatePaymentCheques = paymentDuplicateChequesQuery.list(); final String backUpdateDuplicatePaymentquery = "update " + TABLENAME + " set action='" + BRS_ACTION_TO_BE_PROCESSED_MANUALLY + "'," + " errorMessage='" + BRS_MESSAGE_DUPPLICATE_IN_BANKSTATEMENT + "' where debit=:debit and accountid=:accountId and instrumentNo=:instrumentNo " + " and action='" + BRS_ACTION_TO_BE_PROCESSED + "'"; final SQLQuery paymentDuplicateUpdate = persistenceService.getSession() .createSQLQuery(backUpdateDuplicatePaymentquery); for (final AutoReconcileBean bean : duplicatePaymentCheques) { paymentDuplicateUpdate.setLong("accountId", bean.getAccountId()); paymentDuplicateUpdate.setBigDecimal("debit", bean.getDebit()); paymentDuplicateUpdate.setString("instrumentNo", bean.getInstrumentNo()); paymentDuplicateUpdate.executeUpdate(); } // this portion is for receipts instrumentNo,credit,accountId combination should be unique else mark it duplicate duplicates = "select instrumentNo,credit,accountId from " + TABLENAME + " where accountid=:accountId" + " and credit>0 and action='" + BRS_ACTION_TO_BE_PROCESSED + "' group by instrumentNo,credit,accountId having count(*)>1"; final SQLQuery receiptsDuplicateChequesQuery = persistenceService.getSession() .createSQLQuery(duplicates); receiptsDuplicateChequesQuery.addScalar("instrumentNo").addScalar("credit") .addScalar("accountId", LongType.INSTANCE) .setResultTransformer(Transformers.aliasToBean(AutoReconcileBean.class)); receiptsDuplicateChequesQuery.setLong("accountId", accountId); final List<AutoReconcileBean> duplicateReceiptsCheques = receiptsDuplicateChequesQuery.list(); final String backUpdateDuplicateReceiptsQuery = "update " + TABLENAME + " set action='" + BRS_ACTION_TO_BE_PROCESSED_MANUALLY + "'" + " ,errorMessage='" + BRS_MESSAGE_DUPPLICATE_IN_BANKSTATEMENT + "' where credit=:credit and accountid=:accountId and instrumentNo=:instrumentNo " + " and action='" + BRS_ACTION_TO_BE_PROCESSED + "'"; final SQLQuery receiptDuplicateUpdate = persistenceService.getSession() .createSQLQuery(backUpdateDuplicateReceiptsQuery); for (final AutoReconcileBean bean : duplicateReceiptsCheques) { receiptDuplicateUpdate.setLong("accountId", bean.getAccountId()); receiptDuplicateUpdate.setBigDecimal("credit", bean.getCredit()); receiptDuplicateUpdate.setString("instrumentNo", bean.getInstrumentNo()); receiptDuplicateUpdate.executeUpdate(); } } catch (final HibernateException e) { throw new ApplicationRuntimeException("Failed while processing autoreconciliation "); } }
From source file:org.egov.services.payment.PaymentService.java
License:Open Source License
/** * * @param billList/* ww w. java2 s . com*/ * @param restrictedDate * @return * @throws NumberFormatException * @throws ApplicationException this api check if the voucher contains subledger on credit side and */ public boolean CheckForContractorSubledgerCodes(final List<PaymentBean> billList, final Date restrictedDate) throws NumberFormatException, ApplicationException { boolean retVal = false; int billDateFlag = 0; final String query = "Select gld.detailkeyid from generalledger gl,voucherheader vh, generalledgerdetail gld " + "where gl.id= gld.generalledgerid " + "and vh.id= gl.voucherheaderid " + "and gl.creditamount>0 and gld.detailtypeid in " + "(select id from accountdetailtype t where t.name in ('Creditor','contractor') ) " + "and vh.vouchernumber=?"; if (null != billList && !billList.isEmpty()) for (final PaymentBean bean : billList) if (bean != null) { final SQLQuery createSQLQuery = getSession().createSQLQuery(query); createSQLQuery.setString(0, bean.getBillVoucherNumber()); if (createSQLQuery.list().size() > 0 && bean.getBillDate().compareTo(restrictedDate) > 0) { billDateFlag++; break; } } if (billDateFlag > 0) retVal = true; return retVal; }
From source file:org.generationcp.middleware.dao.gdms.AlleleValuesDAO.java
License:Open Source License
@SuppressWarnings("unchecked") public List<Integer> getGidsByMarkersAndAlleleValues(List<Integer> markerIdList, List<String> alleleValueList) throws MiddlewareQueryException { List<Integer> values = new ArrayList<Integer>(); if (markerIdList.isEmpty() || alleleValueList.isEmpty()) { throw new MiddlewareQueryException("markerIdList and alleleValueList must not be empty"); }//from w w w. jav a 2s . c om if (markerIdList.size() != alleleValueList.size()) { throw new MiddlewareQueryException("markerIdList and alleleValueList must have the same size"); } List<String> placeholderList = new ArrayList<String>(); for (int i = 0; i < markerIdList.size(); i++) { placeholderList.add("(?,?)"); } String placeholders = StringUtil.joinIgnoreNull(",", placeholderList); String sql = new StringBuffer().append("SELECT gid ").append("FROM gdms_allele_values ") .append("WHERE (marker_id, allele_bin_value) IN (" + placeholders + ") ").toString(); try { SQLQuery query = this.getSession().createSQLQuery(sql); for (int i = 0; i < markerIdList.size(); i++) { int baseIndex = i * 2; query.setInteger(baseIndex, markerIdList.get(i)); query.setString(baseIndex + 1, alleleValueList.get(i)); } values = query.list(); } catch (HibernateException e) { this.logAndThrowException("Error with getGidsByMarkersAndAlleleValues(markerIdList=" + markerIdList + ", alleleValueList=" + alleleValueList + "): " + e.getMessage(), e); } return values; }
From source file:org.generationcp.middleware.dao.gdms.MapDAO.java
License:Open Source License
@SuppressWarnings("rawtypes") public List<MapDetailElement> getMapDetailsByName(final String nameLike, final int start, final int numOfRows) { final SQLQuery query = this.getSession().createSQLQuery(MapDAO.GET_MAP_DETAILS_BY_NAME); query.setString("nameLike", nameLike.toLowerCase()); query.setFirstResult(start);/* ww w . j a v a2s. co m*/ query.setMaxResults(numOfRows); final List<MapDetailElement> maps = new ArrayList<MapDetailElement>(); try { final List results = query.list(); for (final Object o : results) { final Object[] result = (Object[]) o; if (result != null) { final int markerCount = ((BigInteger) result[0]).intValue(); final Double maxStartPosition = (Double) result[1]; final String linkageGroup = (String) result[2]; final String mapName = (String) result[3]; final String mapType = (String) result[4]; final String mapDesc = (String) result[5]; final String mapUnit = (String) result[6]; final MapDetailElement map = new MapDetailElement(markerCount, maxStartPosition, linkageGroup, mapName, mapType, mapDesc, mapUnit); maps.add(map); } } return maps; } catch (final HibernateException e) { MapDAO.LOG.error(e.getMessage(), e); throw new MiddlewareQueryException("Error with getMapDetailsByName() query from Map: " + e.getMessage(), e); } }
From source file:org.generationcp.middleware.dao.gdms.MapDAO.java
License:Open Source License
public List<MapInfo> getMapInfoByMapAndChromosome(final Integer mapId, final String chromosome) { final SQLQuery query = this.getSession().createSQLQuery(MapDAO.GET_MAP_INFO_BY_MAP_AND_CHROMOSOME); query.setInteger("mapId", mapId); query.setString("chromosome", chromosome); query.addScalar("marker_id", new IntegerType()); query.addScalar("marker_name", new StringType()); query.addScalar("map_name", new StringType()); query.addScalar("map_type", new StringType()); query.addScalar("start_position", new FloatType()); query.addScalar("linkage_group", new StringType()); query.addScalar("map_unit", new StringType()); final List<MapInfo> mapInfoList = new ArrayList<MapInfo>(); try {// w w w .j a v a 2 s. c om @SuppressWarnings("rawtypes") final List results = query.list(); for (final Object o : results) { final Object[] result = (Object[]) o; if (result != null) { final Integer markerId = (Integer) result[0]; final String markerName = (String) result[1]; final String mapName = (String) result[2]; final String mapType = (String) result[3]; final Float startPosition = (Float) result[4]; final String linkageGroup = (String) result[5]; final String mapUnit = (String) result[6]; final MapInfo mapInfo = new MapInfo(markerId, markerName, mapId, mapName, linkageGroup, startPosition, mapType, mapUnit); mapInfoList.add(mapInfo); } } } catch (final HibernateException e) { MapDAO.LOG.error(e.getMessage(), e); throw new MiddlewareQueryException("Error with getMapInfoByMapAndChromosome() query: " + e.getMessage(), e); } return mapInfoList; }
From source file:org.generationcp.middleware.dao.gdms.MapDAO.java
License:Open Source License
public List<MapInfo> getMapInfoByMapChromosomeAndPosition(final Integer mapId, final String chromosome, final Float startPosition) { final SQLQuery query = this.getSession().createSQLQuery(MapDAO.GET_MAP_INFO_BY_MAP_CHROMOSOME_AND_POSITION); query.setInteger("mapId", mapId); query.setString("chromosome", chromosome); query.setFloat("startPosition", startPosition); query.addScalar("marker_id", new IntegerType()); query.addScalar("marker_name", new StringType()); query.addScalar("map_name", new StringType()); query.addScalar("map_type", new StringType()); query.addScalar("linkage_group", new StringType()); query.addScalar("map_unit", new StringType()); final List<MapInfo> mapInfoList = new ArrayList<MapInfo>(); try {//from w w w . j a v a2 s. c om @SuppressWarnings("rawtypes") final List results = query.list(); for (final Object o : results) { final Object[] result = (Object[]) o; if (result != null) { final Integer markerId = (Integer) result[0]; final String markerName = (String) result[1]; final String mapName = (String) result[2]; final String mapType = (String) result[3]; final String linkageGroup = (String) result[4]; final String mapUnit = (String) result[5]; final MapInfo mapInfo = new MapInfo(markerId, markerName, mapId, mapName, linkageGroup, startPosition, mapType, mapUnit); mapInfoList.add(mapInfo); } } } catch (final HibernateException e) { MapDAO.LOG.error(e.getMessage(), e); throw new MiddlewareQueryException( "Error with getMapInfoByMapChromosomeAndPosition() query: " + e.getMessage(), e); } return mapInfoList; }
From source file:org.generationcp.middleware.dao.gdms.MapDAO.java
License:Open Source License
public Long countMapDetailsByName(final String nameLike) { final SQLQuery query = this.getSession().createSQLQuery(MapDAO.COUNT_MAP_DETAILS_BY_NAME); query.setString("nameLike", nameLike.toLowerCase()); try {/*from w ww . ja v a2 s . co m*/ final BigInteger result = (BigInteger) query.uniqueResult(); return result.longValue(); } catch (final HibernateException e) { MapDAO.LOG.error(e.getMessage(), e); throw new MiddlewareQueryException("Error with countMapDetailsByName() query: " + e.getMessage(), e); } }
From source file:org.jasig.ssp.service.impl.PersonServiceBulkCoachLookupIntegrationTest.java
License:Apache License
public void createExternalPerson(final String schoolId, final String username, final String firstName, final String lastName, final String middleName, final String primaryEmailAddress) { final Session session = sessionFactory.getCurrentSession(); final SQLQuery sqlQuery = session.createSQLQuery("insert into external_person (school_id," + "username, first_name, last_name, middle_name," + "primary_email_address) values (?,?,?,?,?,?)"); sqlQuery.setString(0, schoolId).setString(1, username).setString(2, firstName).setString(3, lastName) .setString(4, middleName).setString(5, primaryEmailAddress); sqlQuery.executeUpdate();//from w ww . ja v a2s . c om }
From source file:org.kimios.kernel.reporting.impl.EntityInformationsReport.java
License:Open Source License
public String getData() throws ConfigException, DataSourceException { Vector<Cell> cells = new Vector<Cell>(); Report report = new Report("EntityInformations"); report.addColumn("Position"); report.addColumn("AllVersionsVolume"); report.addColumn("CurrentVersionsVolume"); report.addColumn("EntitiesNumber"); /* get informations about entity */ String rqInformationsAboutEntity = "SELECT e.dm_entity_path as Position, e.dm_entity_type as EntityType "; rqInformationsAboutEntity += "FROM dm_entity e "; rqInformationsAboutEntity += "WHERE e.dm_entity_id=:dmEntityUid "; SQLQuery sqlInformationsAboutEntity = FactoryInstantiator.getInstance().getDtrFactory().getSession() .createSQLQuery(rqInformationsAboutEntity); sqlInformationsAboutEntity.addScalar("Position", StringType.INSTANCE); sqlInformationsAboutEntity.addScalar("EntityType", IntegerType.INSTANCE); sqlInformationsAboutEntity.setLong("dmEntityUid", dmEntity.getUid()); List<Object[]> reports = sqlInformationsAboutEntity.list(); for (Object[] r : reports) { cells.add(new Cell("Position", (String) r[0])); }//from ww w .j av a 2s . co m /* get all versions volume */ String rqAllVersionsVolume = "SELECT SUM(v.version_length) as AllVersionsVolume " + "FROM dm_entity e, document_version v " + "WHERE e.dm_entity_id=v.document_id " + "AND e.dm_entity_path LIKE :dmEntityPath"; SQLQuery sqlAllVersionsVolume = FactoryInstantiator.getInstance().getDtrFactory().getSession() .createSQLQuery(rqAllVersionsVolume); sqlAllVersionsVolume.addScalar("AllVersionsVolume", LongType.INSTANCE); sqlAllVersionsVolume.setString("dmEntityPath", dmEntity.getPath() + "/%"); Object allVersionsVolume = sqlAllVersionsVolume.list().get(0); if (allVersionsVolume == null) { allVersionsVolume = new Long(0); } cells.add(new Cell("AllVersionsVolume", allVersionsVolume)); /* get current versions volume */ String rqCurrentVersionsVolume = "SELECT SUM(v.version_length) as AllVersionsVolume " + "FROM document_version v, dm_entity e " + "WHERE v.document_id=e.dm_entity_id " + "AND e.dm_entity_path LIKE :dmEntityPath " + "AND v.creation_date IN ( " + "SELECT MAX(creation_date) as creationDate " + "FROM document_version v " + "GROUP BY document_id " + ")"; SQLQuery sqlCurrentVersionsVolume = FactoryInstantiator.getInstance().getDtrFactory().getSession() .createSQLQuery(rqCurrentVersionsVolume); sqlCurrentVersionsVolume.addScalar("AllVersionsVolume", LongType.INSTANCE); sqlCurrentVersionsVolume.setString("dmEntityPath", dmEntity.getPath() + "/%"); Object currentVersionsVolume = sqlCurrentVersionsVolume.list().get(0); if (currentVersionsVolume == null) { currentVersionsVolume = new Long(0); } cells.add(new Cell("CurrentVersionsVolume", currentVersionsVolume)); /* get entities number */ String rqEntitiesNumber = "SELECT COUNT(dm_entity_id) as EntitiesNumber " + "FROM dm_entity e " + "WHERE e.dm_entity_path LIKE :dmEntityPath "; SQLQuery sqlEntitiesNumber = getSession().createSQLQuery(rqEntitiesNumber); sqlEntitiesNumber.addScalar("EntitiesNumber", LongType.INSTANCE); sqlEntitiesNumber.setString("dmEntityPath", dmEntity.getPath() + "/%"); cells.add(new Cell("EntitiesNumber", sqlEntitiesNumber.list().get(0))); report.addRow(new Row(cells)); return report.toXML(); }
From source file:org.kimios.kernel.reporting.impl.UserActionsReport.java
License:Open Source License
public String getData() throws ConfigException, DataSourceException { if (order != null && order.length() == 0) { order = null;/* ww w. j av a 2 s. co m*/ } if (actionType != null && order != null && order.length() == 0) { actionType = null; } String tmpTable = null; try { Report temporaryReport = new Report("UserActions"); temporaryReport.addColumn("Position"); temporaryReport.addColumn("ActionType"); temporaryReport.addColumn("ActionTypeParameters"); temporaryReport.addColumn("Date"); Calendar dtFrom = Calendar.getInstance(); dtFrom.setTime(dateFrom); Calendar dtTo = Calendar.getInstance(); dtTo.setTime(dateTo); dtFrom.set(Calendar.SECOND, 0); dtFrom.set(Calendar.MINUTE, 0); dtFrom.set(Calendar.HOUR, 0); dtTo.set(Calendar.SECOND, 59); dtTo.set(Calendar.MINUTE, 59); dtTo.set(Calendar.HOUR, 23); /* Workspace log */ String rqWorkspaceLog = "SELECT w.action as ActionType, "; rqWorkspaceLog += "w.action_parameter as ActionTypeParameters, "; rqWorkspaceLog += "w.dm_entity_id as EntityUid, "; rqWorkspaceLog += "w.log_time as Date, "; rqWorkspaceLog += "e.dm_entity_name as EntityName "; rqWorkspaceLog += "FROM authentication_source a, entity_log w "; rqWorkspaceLog += "LEFT JOIN dm_entity e "; rqWorkspaceLog += "ON w.dm_entity_id = e.dm_entity_id "; rqWorkspaceLog += "WHERE a.source_name=w.user_source "; rqWorkspaceLog += "AND w.username=:userName "; rqWorkspaceLog += "AND w.user_source=:userSource "; rqWorkspaceLog += "AND w.log_time >= :dateFrom "; rqWorkspaceLog += "AND w.log_time <= :dateTo"; SQLQuery sqlWorkspaceLog = FactoryInstantiator.getInstance().getDtrFactory().getSession() .createSQLQuery(rqWorkspaceLog); sqlWorkspaceLog.addScalar("ActionType", IntegerType.INSTANCE); sqlWorkspaceLog.addScalar("ActionTypeParameters", StringType.INSTANCE); sqlWorkspaceLog.addScalar("EntityUid", LongType.INSTANCE); sqlWorkspaceLog.addScalar("Date", StringType.INSTANCE); sqlWorkspaceLog.addScalar("EntityName", StringType.INSTANCE); sqlWorkspaceLog.setString("userName", user.getUid()); sqlWorkspaceLog.setString("userSource", user.getAuthenticationSourceName()); sqlWorkspaceLog.setDate("dateFrom", dtFrom.getTime()); sqlWorkspaceLog.setDate("dateTo", dtTo.getTime()); List<Object[]> reportWorkspaceLog = sqlWorkspaceLog.list(); for (Object[] r : reportWorkspaceLog) { Vector<Cell> cells = new Vector<Cell>(); cells.add(new Cell("ActionType", (Integer) r[0])); cells.add(new Cell("ActionTypeParameters", r[1] == null ? "" : (String) r[1])); cells.add(new Cell("Date", (String) r[3])); cells.add(new Cell("EntityName", r[4] == null ? "" : (String) r[4])); cells.add(new Cell("Position", new String("/"))); temporaryReport.addRow(new Row(cells)); } /* Folder log */ String rqFolderLog = "SELECT f.action as ActionType, "; rqFolderLog += "f.action_parameter as ActionTypeParameters, "; rqFolderLog += "f.dm_entity_id as EntityUid, "; rqFolderLog += "f.log_time as Date, "; rqFolderLog += "e.dm_entity_name as EntityName, "; rqFolderLog += "entt.dm_entity_path as Position "; rqFolderLog += "FROM authentication_source a, entity_log f "; rqFolderLog += "LEFT JOIN dm_entity e "; rqFolderLog += "ON f.dm_entity_id = e.dm_entity_id "; rqFolderLog += "LEFT JOIN dm_entity entt "; rqFolderLog += "ON f.dm_entity_id = entt.dm_entity_id "; rqFolderLog += "WHERE a.source_name=f.user_source "; rqFolderLog += "AND f.username=:userName "; rqFolderLog += "AND f.user_source=:userSource "; rqFolderLog += "AND f.log_time >= :dateFrom "; rqFolderLog += "AND f.log_time <= :dateTo "; SQLQuery sqlFolderLog = FactoryInstantiator.getInstance().getDtrFactory().getSession() .createSQLQuery(rqFolderLog); sqlFolderLog.addScalar("ActionType", IntegerType.INSTANCE); sqlFolderLog.addScalar("ActionTypeParameters", StringType.INSTANCE); sqlFolderLog.addScalar("EntityUid", LongType.INSTANCE); sqlFolderLog.addScalar("Date", StringType.INSTANCE); sqlFolderLog.addScalar("EntityName", StringType.INSTANCE); sqlFolderLog.addScalar("Position", StringType.INSTANCE); sqlFolderLog.setString("userName", user.getUid()); sqlFolderLog.setString("userSource", user.getAuthenticationSourceName()); sqlFolderLog.setDate("dateFrom", dtFrom.getTime()); sqlFolderLog.setDate("dateTo", dtTo.getTime()); List<Object[]> reportFolderLog = sqlFolderLog.list(); for (Object[] r : reportFolderLog) { Vector<Cell> cells = new Vector<Cell>(); cells.add(new Cell("ActionType", (Integer) r[0])); cells.add(new Cell("ActionTypeParameters", r[1] == null ? "" : (String) r[1])); cells.add(new Cell("Date", (String) r[3])); cells.add(new Cell("EntityName", r[4] == null ? "" : (String) r[4])); cells.add(new Cell("Position", r[5] == null ? "" : (String) r[5])); temporaryReport.addRow(new Row(cells)); } /* Document log */ String rqDocumentLog = "SELECT d.action as ActionType, "; rqDocumentLog += "d.action_parameter as ActionTypeParameters, "; rqDocumentLog += "d.dm_entity_id as EntityUid, "; rqDocumentLog += "d.log_time as Date, "; rqDocumentLog += "entt.dm_entity_name as EntityName, "; rqDocumentLog += "entt.dm_entity_path as Position "; rqDocumentLog += "FROM authentication_source a, entity_log d "; rqDocumentLog += "LEFT JOIN dm_entity entt "; rqDocumentLog += "ON d.dm_entity_id = entt.dm_entity_id "; rqDocumentLog += "WHERE a.source_name=d.user_source "; rqDocumentLog += "AND d.username=:userName "; rqDocumentLog += "AND d.user_source=:userSource "; rqDocumentLog += "AND d.log_time >= :dateFrom "; rqDocumentLog += "AND d.log_time <= :dateTo"; SQLQuery sqlDocumentLog = FactoryInstantiator.getInstance().getDtrFactory().getSession() .createSQLQuery(rqDocumentLog); sqlDocumentLog.addScalar("ActionType", IntegerType.INSTANCE); sqlDocumentLog.addScalar("ActionTypeParameters", StringType.INSTANCE); sqlDocumentLog.addScalar("EntityUid", LongType.INSTANCE); sqlDocumentLog.addScalar("Date", StringType.INSTANCE); sqlDocumentLog.addScalar("EntityName", StringType.INSTANCE); sqlDocumentLog.addScalar("Position", StringType.INSTANCE); sqlDocumentLog.setString("userName", user.getUid()); sqlDocumentLog.setString("userSource", user.getAuthenticationSourceName()); sqlDocumentLog.setDate("dateFrom", dtFrom.getTime()); sqlDocumentLog.setDate("dateTo", dtTo.getTime()); List<Object[]> reportDocumentLog = sqlDocumentLog.list(); for (Object[] r : reportDocumentLog) { Vector<Cell> cells = new Vector<Cell>(); cells.add(new Cell("ActionType", (Integer) r[0])); cells.add(new Cell("ActionTypeParameters", r[1] == null ? "" : (String) r[1])); cells.add(new Cell("Date", (String) r[3])); cells.add(new Cell("EntityName", r[4] == null ? "" : (String) r[4])); cells.add(new Cell("Position", r[5] == null ? "" : (String) r[5])); temporaryReport.addRow(new Row(cells)); } /* Create temporary table */ tmpTable = "tmp_" + sessionUid.substring(0, 8) + "_" + new Date().getTime(); String rqCreateTable = "CREATE TABLE " + tmpTable + " ( "; rqCreateTable += "ReportActionType character varying(2), "; rqCreateTable += "ReportActionTypeParameters character varying(255), "; rqCreateTable += "ReportEntityName character varying(255), "; rqCreateTable += "ReportDate character varying(255), "; rqCreateTable += "ReportPosition character varying(255) )"; SQLQuery sqlCreateTable = FactoryInstantiator.getInstance().getDtrFactory().getSession() .createSQLQuery(rqCreateTable); sqlCreateTable.executeUpdate(); for (Row row : temporaryReport.getBody().getRows()) { String action = String.valueOf(row.getValue("ActionType")); String parameters = String.valueOf(row.getValue("ActionTypeParameters")); String entityName = String.valueOf(row.getValue("EntityName")); String date = String.valueOf(row.getValue("Date")); String position = String.valueOf(row.getValue("Position")); String rqInsertTable = "INSERT INTO " + tmpTable + " ( "; rqInsertTable += "ReportActionType, ReportActionTypeParameters, ReportEntityName, ReportDate, ReportPosition "; rqInsertTable += " ) VALUES (:actionType,:parameters,:entityName,:date,:position)"; SQLQuery sqlInsertTable = getSession().createSQLQuery(rqInsertTable); sqlInsertTable.setString("actionType", action); sqlInsertTable.setString("parameters", parameters); sqlInsertTable.setString("entityName", entityName); sqlInsertTable.setString("date", date); sqlInsertTable.setString("position", position); sqlInsertTable.executeUpdate(); } /* Report */ String rq = "SELECT ReportActionType, ReportActionTypeParameters, ReportEntityName, ReportDate, ReportPosition "; rq += "FROM " + tmpTable + " "; rq += (actionType != null ? " WHERE ReportActionType=:actionType " : " "); rq += " ORDER BY " + (order == null ? "ReportDate" : order) + " " + (asc ? "ASC" : "DESC"); SQLQuery sql = FactoryInstantiator.getInstance().getDtrFactory().getSession().createSQLQuery(rq); sql.addScalar("ReportActionType", StringType.INSTANCE); sql.addScalar("ReportActionTypeParameters", StringType.INSTANCE); sql.addScalar("ReportEntityName", StringType.INSTANCE); sql.addScalar("ReportDate", StringType.INSTANCE); sql.addScalar("ReportPosition", StringType.INSTANCE); if (actionType != null) { sql.setString("actionType", actionType); } Report report = new Report("UserActions"); report.addColumn("Position"); report.addColumn("EntityName"); report.addColumn("ActionType"); report.addColumn("ActionTypeParameters"); report.addColumn("Date"); List<Object[]> reports = sql.list(); for (Object[] r : reports) { Vector<Cell> cells = new Vector<Cell>(); cells.add(new Cell("ActionType", (String) r[0])); cells.add(new Cell("ActionTypeParameters", (String) r[1])); cells.add(new Cell("EntityName", (String) r[2])); cells.add(new Cell("Date", (String) r[3])); String pos = ((String) r[4]); int index = pos.lastIndexOf('/'); if (index != -1 && !"/".equals(pos)) { pos = pos.substring(0, index); } cells.add(new Cell("Position", pos)); report.addRow(new Row(cells)); } return report.toXML(); } catch (HibernateException he) { he.printStackTrace(); throw he; } finally { /* Drop temporary table */ FactoryInstantiator.getInstance().getDtrFactory().getSession().createSQLQuery("DROP TABLE " + tmpTable) .executeUpdate(); } }