List of usage examples for org.apache.commons.beanutils RowSetDynaClass getDynaProperties
public DynaProperty[] getDynaProperties();
Return an array of ProperyDescriptors for the properties currently defined in this DynaClass.
From source file:org.brucalipto.sqlutil.DB2SQLManager.java
/** * Method useful for using STORED PROCEDURE * @param spib The {@link SPInputBean} bean containing data to execute the stored procedure * @return The {@link SPOutputBean} containing returned values *//*from w w w. j a v a 2 s. c o m*/ public SPOutputBean executeSP(final SPInputBean spib) throws SQLException { Connection conn = null; CallableStatement call = null; ResultSet resultSet = null; final String procedureName = spib.spName; SPParameter[] inputParameters = spib.inputParams; int[] outputParameters = spib.outputParams; final int inputParametersSize = inputParameters.length; final int outputParametersSize = outputParameters.length; final StringBuffer spName = new StringBuffer("call ").append(procedureName).append('('); int totalParameters = inputParametersSize + outputParametersSize; for (int i = 0; i < totalParameters; i++) { if (i != totalParameters - 1) { spName.append("?,"); } else { spName.append('?'); } } spName.append(")"); try { if (this.dataSource != null) { conn = this.dataSource.getConnection(); } else { conn = this.connection; } call = conn.prepareCall(spName.toString()); for (int i = 0; i < inputParametersSize; i++) { final SPParameter inputParam = inputParameters[i]; final int sqlType = inputParam.sqlType; final Object inputParamValue = inputParam.value; log.debug((i + 1) + ") Setting input value: " + inputParam); if (inputParamValue == null) { call.setNull(i + 1, sqlType); continue; } switch (sqlType) { case Types.VARCHAR: call.setString(i + 1, (String) inputParamValue); break; case Types.INTEGER: if (inputParamValue instanceof Integer) { call.setInt(i + 1, ((Integer) inputParamValue).intValue()); } else if (inputParamValue instanceof Long) { call.setLong(i + 1, ((Long) inputParamValue).longValue()); } break; case Types.DATE: call.setDate(i + 1, (Date) inputParamValue); break; case Types.BOOLEAN: call.setBoolean(i + 1, ((Boolean) inputParamValue).booleanValue()); break; case Types.CHAR: call.setString(i + 1, ((Character) inputParamValue).toString()); break; case Types.DOUBLE: call.setDouble(i + 1, ((Double) inputParamValue).doubleValue()); break; case Types.FLOAT: call.setFloat(i + 1, ((Float) inputParamValue).floatValue()); break; case Types.TIMESTAMP: call.setTimestamp(i + 1, (Timestamp) inputParamValue); break; default: call.setObject(i + 1, inputParamValue); break; } } for (int i = 0; i < outputParametersSize; i++) { int sqlType = outputParameters[i]; log.debug((i + 1) + ") Registering output type 'Types." + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'"); call.registerOutParameter(inputParametersSize + i + 1, sqlType); } log.debug("Going to call: '" + procedureName + "'"); long elapsedTime = System.currentTimeMillis(); boolean hasResultSet = call.execute(); log.debug("SP '" + procedureName + "' executed in " + (System.currentTimeMillis() - elapsedTime) + "millis"); if (hasResultSet) { log.debug("This SP is going to return also a resultSet"); } final SPOutputBean output = new SPOutputBean(); for (int i = 0; i < outputParametersSize; i++) { int sqlType = outputParameters[i]; log.debug((i + 1) + ") Getting output type 'Types." + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'"); final Object spResult = call.getObject(inputParametersSize + i + 1); SPParameter outParam = new SPParameter(sqlType, spResult); output.addResult(outParam); } if (hasResultSet) { RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(call.getResultSet(), false); if (log.isDebugEnabled()) { log.debug("Going to return a RowSetDynaClass with following properties:"); DynaProperty[] properties = rowSetDynaClass.getDynaProperties(); for (int i = 0; i < properties.length; i++) { log.debug("Name: '" + properties[i].getName() + "'; Type: '" + properties[i].getType().getName() + "'"); } } SPParameter outParam = new SPParameter(Types.JAVA_OBJECT, rowSetDynaClass); output.addResult(outParam); } return output; } finally { closeResources(resultSet, call, conn); } }
From source file:org.brucalipto.sqlutil.OracleSQLManager.java
/** * Method useful for using STORED PROCEDURE * @param spib The {@link SPInputBean} bean containing data to execute the stored procedure * @return The {@link SPOutputBean} containing returned values *///from w ww . j av a2 s . c om public SPOutputBean executeSP(final SPInputBean spib) throws SQLException { Connection conn = null; CallableStatement call = null; ResultSet resultSet = null; final String procedureName = spib.spName; SPParameter[] inputParameters = spib.inputParams; int[] outputParameters = spib.outputParams; final int inputParametersSize = inputParameters.length; final int outputParametersSize = outputParameters.length; final StringBuffer spName = new StringBuffer("{ call ").append(procedureName).append('('); int totalParameters = inputParametersSize + outputParametersSize; for (int i = 0; i < totalParameters; i++) { if (i != totalParameters - 1) { spName.append("?,"); } else { spName.append('?'); } } spName.append(") }"); log.debug("Going to call: '" + spName + "'"); try { conn = this.dataSource.getConnection(); call = conn.prepareCall(spName.toString()); for (int i = 0; i < inputParametersSize; i++) { final SPParameter inputParam = inputParameters[i]; final int sqlType = inputParam.sqlType; final Object inputParamValue = inputParam.value; log.debug((i + 1) + ") Setting input value 'Types." + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'-'" + inputParamValue + "'"); if (inputParamValue == null) { call.setNull(i + 1, sqlType); continue; } switch (sqlType) { case Types.VARCHAR: call.setString(i + 1, (String) inputParamValue); break; case Types.INTEGER: if (inputParamValue instanceof Integer) { call.setInt(i + 1, ((Integer) inputParamValue).intValue()); } else if (inputParamValue instanceof Long) { call.setLong(i + 1, ((Long) inputParamValue).longValue()); } break; case Types.DATE: call.setDate(i + 1, (Date) inputParamValue); break; case Types.BOOLEAN: call.setBoolean(i + 1, ((Boolean) inputParamValue).booleanValue()); break; case Types.CHAR: call.setString(i + 1, ((Character) inputParamValue).toString()); break; case Types.DOUBLE: call.setDouble(i + 1, ((Double) inputParamValue).doubleValue()); break; case Types.FLOAT: call.setFloat(i + 1, ((Float) inputParamValue).floatValue()); break; case Types.TIMESTAMP: call.setTimestamp(i + 1, (Timestamp) inputParamValue); break; default: call.setObject(i + 1, inputParamValue); break; } } for (int i = 0; i < outputParametersSize; i++) { int sqlType = outputParameters[i]; log.debug((i + 1) + ") Registering output type 'Types." + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'"); call.registerOutParameter(inputParametersSize + i + 1, sqlType); } call.execute(); final SPOutputBean output = new SPOutputBean(); for (int i = 0; i < outputParametersSize; i++) { int sqlType = outputParameters[i]; log.debug((i + 1) + ") Getting output type 'Types." + SQLUtilTypes.SQL_TYPES.get(Integer.valueOf("" + sqlType)) + "'"); final Object spResult = call.getObject(inputParametersSize + i + 1); SPParameter outParam = null; if (sqlType == SQLUtilTypes.CURSOR) { resultSet = (ResultSet) spResult; RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(resultSet, false); if (log.isDebugEnabled()) { log.debug("Going to return a RowSetDynaClass with following properties:"); DynaProperty[] properties = rowSetDynaClass.getDynaProperties(); for (int j = 0; j < properties.length; j++) { log.debug("Name: '" + properties[j].getName() + "'; Type: '" + properties[j].getType().getName() + "'"); } } outParam = new SPParameter(sqlType, rowSetDynaClass); } else { outParam = new SPParameter(sqlType, spResult); } output.addResult(outParam); } return output; } catch (SQLException sqle) { log.error("Caught SQLException", sqle); } finally { closeResources(resultSet, call, conn); } return null; }
From source file:org.brucalipto.sqlutil.SQLManager.java
/** * Method useful for SQL SELECT/*from w ww . j a v a 2 s . co m*/ * @param preparedStatement The prepared statement to execute * @param parameters List of {@link SQLParameter} to use to complete the prepared statement * @return Returns a RowSetDynaClass containing returned rows * @throws SQLException */ public RowSetDynaClass dynaSelect(final String preparedStatement, final SQLParameter[] params) throws SQLException { final long elapsedTime = System.currentTimeMillis(); SQLParameter[] parameters; if (params == null) { parameters = new SQLParameter[0]; log.debug("Going to execute a query without parameters."); } else { parameters = (SQLParameter[]) params.clone(); } Connection dbConn = null; PreparedStatement pstmt = null; ResultSet rs = null; try { if (this.dataSource != null) { dbConn = this.dataSource.getConnection(); } else { dbConn = this.connection; } pstmt = dbConn.prepareStatement(preparedStatement); for (int i = 0; i < parameters.length; i++) { final SQLParameter param = parameters[i]; log.debug((i + 1) + ") Going to add parameter " + param); final int sqlType = param.getSqlType(); final Object paramValue = param.getValue(); if (paramValue == null) { pstmt.setNull(i + 1, sqlType); continue; } switch (sqlType) { case Types.VARCHAR: pstmt.setString(i + 1, (String) paramValue); break; case Types.INTEGER: if (paramValue instanceof Integer) { pstmt.setInt(i + 1, ((Integer) paramValue).intValue()); } else if (paramValue instanceof Long) { pstmt.setLong(i + 1, ((Long) paramValue).longValue()); } break; case Types.DATE: pstmt.setDate(i + 1, (Date) paramValue); break; case Types.BOOLEAN: pstmt.setBoolean(i + 1, ((Boolean) paramValue).booleanValue()); break; case Types.CHAR: pstmt.setString(i + 1, ((Character) paramValue).toString()); break; case Types.DOUBLE: pstmt.setDouble(i + 1, ((Double) paramValue).doubleValue()); break; case Types.FLOAT: pstmt.setFloat(i + 1, ((Float) paramValue).floatValue()); break; case Types.TIMESTAMP: pstmt.setTimestamp(i + 1, (Timestamp) paramValue); break; default: pstmt.setObject(i + 1, paramValue); break; } } rs = pstmt.executeQuery(); RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(rs, false); if (log.isDebugEnabled()) { log.debug("Prepared statement '" + preparedStatement + "' returned '" + rowSetDynaClass.getRows().size() + "' rows in '" + (System.currentTimeMillis() - elapsedTime) + "' millis with following properties:"); DynaProperty[] properties = rowSetDynaClass.getDynaProperties(); for (int i = 0; i < properties.length; i++) { log.debug("Name: '" + properties[i].getName() + "'; Type: '" + properties[i].getType().getName() + "'"); } } return rowSetDynaClass; } catch (SQLException e) { log.error("Error executing prepared statement '" + preparedStatement + "'", e); throw e; } finally { closeResources(rs, pstmt, dbConn); } }
From source file:org.efs.openreports.engine.JasperReportEngine.java
private JasperPrint fillQueryReport(Report report, Map map, int exportType) throws Exception { Connection conn = null;/*from w ww.j av a 2 s . c om*/ PreparedStatement pStmt = null; ResultSet rs = null; // create new HashMap to send to JasperReports in order to // fix serialization problems Map parameters = new HashMap(map); List results = null; DynaProperty[] properties = null; try { ReportDataSource dataSource = report.getDataSource(); conn = dataSourceProvider.getConnection(dataSource.getId()); if (parameters == null || parameters.isEmpty()) { pStmt = conn.prepareStatement(report.getQuery()); } else { // Use JasperReports Query logic to parse parameters in chart // queries JRDesignQuery query = new JRDesignQuery(); query.setText(report.getQuery()); // convert parameters to JRDesignParameters so they can be // parsed Map jrParameters = ORUtil.buildJRDesignParameters(parameters); pStmt = JRQueryExecuter.getStatement(query, jrParameters, parameters, conn); } rs = pStmt.executeQuery(); RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(rs); results = rowSetDynaClass.getRows(); properties = rowSetDynaClass.getDynaProperties(); rs.close(); } catch (Exception e) { throw new ProviderException("Error executing report query: " + e.getMessage()); } finally { try { if (pStmt != null) pStmt.close(); if (conn != null) conn.close(); } catch (Exception c) { log.error("Error closing"); } } JasperDesign jasperDesign = new JasperDesign(); jasperDesign.setName(report.getName().replaceAll(" ", "_")); int width = jasperDesign.getPageWidth(); int height = jasperDesign.getPageHeight(); jasperDesign.setOrientation(JasperDesign.ORIENTATION_LANDSCAPE); jasperDesign.setPageHeight(width); jasperDesign.setPageWidth(height); for (int i = 0; i < properties.length; i++) { JRDesignField field = new JRDesignField(); field.setName((String) properties[i].getName()); field.setValueClass((Class) properties[i].getType()); try { jasperDesign.addField(field); } catch (Exception e) { log.warn(e); } } if (exportType == ReportEngine.EXPORT_PDF) { // add title JRDesignStaticText sText = new JRDesignStaticText(); sText.setX(0); sText.setY(0); sText.setWidth(jasperDesign.getPageHeight()); sText.setHeight(50); sText.setText(jasperDesign.getName()); sText.setFontSize(16); sText.setBold(true); JRDesignBand band = new JRDesignBand(); band.setHeight(50); band.addElement(sText); jasperDesign.setTitle(band); // add page footer for page numbers band = new JRDesignBand(); band.setHeight(15); sText = new JRDesignStaticText(); sText.setX(0); sText.setY(0); sText.setHeight(15); sText.setWidth(40); sText.setText("Page:"); band.addElement(sText); JRDesignExpression exp = new JRDesignExpression(); exp.addVariableChunk("PAGE_NUMBER"); exp.setValueClass(Integer.class); JRDesignTextField txt = new JRDesignTextField(); txt.setExpression(exp); txt.setX(40); txt.setY(0); txt.setHeight(15); txt.setWidth(100); band.addElement(txt); jasperDesign.setPageFooter(band); } JRDesignBand emptyBand = new JRDesignBand(); emptyBand.setHeight(0); jasperDesign.setPageHeader(emptyBand); jasperDesign.setColumnFooter(emptyBand); jasperDesign.setSummary(emptyBand); JRField[] fields = jasperDesign.getFields(); // add column header and detail bands JRDesignBand bandDetail = new JRDesignBand(); bandDetail.setHeight(20); JRDesignBand bandHeader = new JRDesignBand(); bandHeader.setHeight(20); int fieldWidth = (jasperDesign.getPageWidth() - jasperDesign.getLeftMargin() - jasperDesign.getRightMargin() - (fields.length - 1) * jasperDesign.getColumnSpacing()) / fields.length; for (int i = 0; i < fields.length; i++) { try { JRField field = fields[i]; JRDesignExpression exp = new JRDesignExpression(); exp.addFieldChunk(field.getName()); if (field.getValueClassName().equals("java.sql.Date")) { // JasperReports does not support java.sql.Date in text field expression exp.setValueClass(java.util.Date.class); } else { exp.setValueClass(field.getValueClass()); } JRDesignTextField txt = new JRDesignTextField(); txt.setExpression(exp); txt.setX(i * fieldWidth); txt.setY(0); txt.setHeight(20); txt.setWidth(fieldWidth); if (field.getValueClass().equals(Double.class)) { txt.setPattern("0.00"); } bandDetail.addElement(txt); JRDesignStaticText sText = new JRDesignStaticText(); sText.setX(i * fieldWidth); sText.setY(0); sText.setHeight(20); sText.setWidth(fieldWidth); sText.setText(field.getName()); sText.setUnderline(true); bandHeader.addElement(sText); } catch (Exception e) { log.warn(e); } } if (exportType == ReportEngine.EXPORT_PDF) jasperDesign.setColumnHeader(bandHeader); jasperDesign.setDetail(bandDetail); JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign); JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, parameters, new JRBeanCollectionDataSource(results)); return jasperPrint; }
From source file:org.efs.openreports.engine.QueryReportEngine.java
public ReportEngineOutput generateReport(ReportEngineInput input) throws ProviderException { Connection conn = null;// www . ja v a 2s . c o m PreparedStatement pStmt = null; ResultSet rs = null; try { Report report = input.getReport(); Map parameters = input.getParameters(); ReportDataSource dataSource = report.getDataSource(); conn = dataSourceProvider.getConnection(dataSource.getId()); if (parameters == null || parameters.isEmpty()) { pStmt = conn.prepareStatement(report.getQuery()); } else { // Use JasperReports Query logic to parse parameters in chart // queries JRDesignQuery query = new JRDesignQuery(); query.setText(report.getQuery()); // convert parameters to JRDesignParameters so they can be // parsed Map jrParameters = ORUtil.buildJRDesignParameters(parameters); pStmt = JRQueryExecuter.getStatement(query, jrParameters, parameters, conn); } ORProperty maxRows = propertiesProvider.getProperty(ORProperty.QUERYREPORT_MAXROWS); if (maxRows != null && maxRows.getValue() != null) { pStmt.setMaxRows(Integer.parseInt(maxRows.getValue())); } rs = pStmt.executeQuery(); RowSetDynaClass rowSetDynaClass = new RowSetDynaClass(rs); List results = rowSetDynaClass.getRows(); DynaProperty[] dynaProperties = rowSetDynaClass.getDynaProperties(); DisplayProperty[] properties = new DisplayProperty[dynaProperties.length]; for (int i = 0; i < dynaProperties.length; i++) { properties[i] = new DisplayProperty(dynaProperties[i].getName(), dynaProperties[i].getType().getName()); } rs.close(); QueryEngineOutput output = new QueryEngineOutput(); output.setResults(results); output.setProperties(properties); return output; } catch (Exception e) { throw new ProviderException("Error executing report query: " + e.getMessage()); } finally { try { if (pStmt != null) pStmt.close(); if (conn != null) conn.close(); } catch (Exception c) { log.error("Error closing"); } } }