List of usage examples for org.apache.commons.beanutils RowSetDynaClass RowSetDynaClass
public RowSetDynaClass(ResultSet resultSet, boolean lowerCase) throws SQLException
Construct a new RowSetDynaClass for the specified ResultSet.
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 av a2s.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 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(") }"); 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/*w ww . j a v a 2 s .c o 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); } }