List of usage examples for java.sql PreparedStatement setShort
void setShort(int parameterIndex, short x) throws SQLException;
short value. From source file:org.lockss.db.SqlDbManager.java
/** * Adds to the database the database version. * //from w w w.j av a 2s . com * @param conn * A Connection with the database connection to be used. * @param version * An int with version to be updated. * @return an int with the number of database rows added. * @throws SQLException * if any problem occurred accessing the database. */ private int addDbVersion(Connection conn, int version) throws SQLException { final String DEBUG_HEADER = "addDbVersion(): "; int addedCount = 0; PreparedStatement insertVersion = prepareStatementBeforeReady(conn, INSERT_DB_VERSION_QUERY); try { insertVersion.setString(1, DATABASE_VERSION_TABLE_SYSTEM); insertVersion.setShort(2, (short) version); addedCount = executeUpdateBeforeReady(insertVersion); } finally { SqlDbManager.safeCloseStatement(insertVersion); } log.debug3(DEBUG_HEADER + "addedCount = " + addedCount); return addedCount; }
From source file:org.apache.sqoop.repository.derby.DerbyRepositoryHandler.java
/** * We are creating the LINK FORM for HDFS and later it the schema will * be renamed to LINK CONFIG/* ww w.j a v a2 s . co m*/ * NOTE: Should be used only in the upgrade path! */ @Deprecated private Long createHdfsLinkForm(Connection conn, Long connectorId) { if (LOG.isTraceEnabled()) { LOG.trace("Creating HDFS link."); } PreparedStatement stmt = null; int result; try { short index = 0; stmt = conn.prepareStatement(STMT_INSERT_INTO_FORM, Statement.RETURN_GENERATED_KEYS); stmt.setLong(1, connectorId); stmt.setString(2, "linkConfig"); // it could also be set to the deprecated "CONNECTION" stmt.setString(3, MConfigType.LINK.name()); stmt.setShort(4, index); result = stmt.executeUpdate(); if (result != 1) { throw new SqoopException(DerbyRepoError.DERBYREPO_0012, Integer.toString(result)); } ResultSet rsetFormId = stmt.getGeneratedKeys(); if (!rsetFormId.next()) { throw new SqoopException(DerbyRepoError.DERBYREPO_0013); } if (LOG.isTraceEnabled()) { LOG.trace("Created HDFS connector link FORM."); } return rsetFormId.getLong(1); } catch (SQLException ex) { throw new SqoopException(DerbyRepoError.DERBYREPO_0019, ex); } finally { closeStatements(stmt); } }
From source file:org.apache.ddlutils.platform.PlatformImplBase.java
/** * This is the core method to set the parameter of a prepared statement to a given value. * The primary purpose of this method is to call the appropriate method on the statement, * and to give database-specific implementations the ability to change this behavior. * /* w w w .jav a2s . co m*/ * @param statement The statement * @param sqlIndex The parameter index * @param typeCode The JDBC type code * @param value The value * @throws SQLException If an error occurred while setting the parameter value */ protected void setStatementParameterValue(PreparedStatement statement, int sqlIndex, int typeCode, Object value) throws SQLException { if (value == null) { statement.setNull(sqlIndex, typeCode); } else if (value instanceof String) { statement.setString(sqlIndex, (String) value); } else if (value instanceof byte[]) { statement.setBytes(sqlIndex, (byte[]) value); } else if (value instanceof Boolean) { statement.setBoolean(sqlIndex, ((Boolean) value).booleanValue()); } else if (value instanceof Byte) { statement.setByte(sqlIndex, ((Byte) value).byteValue()); } else if (value instanceof Short) { statement.setShort(sqlIndex, ((Short) value).shortValue()); } else if (value instanceof Integer) { statement.setInt(sqlIndex, ((Integer) value).intValue()); } else if (value instanceof Long) { statement.setLong(sqlIndex, ((Long) value).longValue()); } else if (value instanceof BigDecimal) { // setObject assumes a scale of 0, so we rather use the typed setter statement.setBigDecimal(sqlIndex, (BigDecimal) value); } else if (value instanceof Float) { statement.setFloat(sqlIndex, ((Float) value).floatValue()); } else if (value instanceof Double) { statement.setDouble(sqlIndex, ((Double) value).doubleValue()); } else { statement.setObject(sqlIndex, value, typeCode); } }
From source file:org.apache.sqoop.repository.derby.DerbyRepositoryHandler.java
/** * Register configs in derby database. This method will insert the ids * generated by the repository into the configs passed in itself. * * Use given prepared statements to create entire config structure in database. * * @param configurableId//from w ww.j a va 2s . c om * @param configs * @param type * @param baseConfigStmt * @param baseInputStmt * @param conn * @return short number of configs registered. * @throws SQLException */ private short registerConfigs(Long configurableId, Direction direction, List<MConfig> configs, String type, PreparedStatement baseConfigStmt, PreparedStatement baseInputStmt, Connection conn) throws SQLException { short configIndex = 0; for (MConfig config : configs) { if (configurableId == null) { baseConfigStmt.setNull(1, Types.BIGINT); } else { baseConfigStmt.setLong(1, configurableId); } baseConfigStmt.setString(2, config.getName()); baseConfigStmt.setString(3, type); baseConfigStmt.setShort(4, configIndex++); int baseConfigCount = baseConfigStmt.executeUpdate(); if (baseConfigCount != 1) { throw new SqoopException(DerbyRepoError.DERBYREPO_0015, Integer.toString(baseConfigCount)); } ResultSet rsetConfigId = baseConfigStmt.getGeneratedKeys(); if (!rsetConfigId.next()) { throw new SqoopException(DerbyRepoError.DERBYREPO_0016); } long configId = rsetConfigId.getLong(1); config.setPersistenceId(configId); if (direction != null) { registerConfigDirection(configId, direction, conn); } // Insert all the inputs List<MInput<?>> inputs = config.getInputs(); registerConfigInputs(configId, inputs, baseInputStmt); } return configIndex; }
From source file:com.wso2telco.dep.reportingservice.dao.BillingDAO.java
/** * Gets the northbound api counts for subscription. * * @param consumerKey the consumer key/* w w w . ja va2s. co m*/ * @param year the year * @param month the month * @param apiName the api name * @param apiVersion the api version * @param operationId the operation id * @return the northbound api counts for subscription * @throws Exception the exception */ public Map<CategoryCharge, BilledCharge> getNorthboundAPICountsForSubscription(String consumerKey, short year, short month, String apiName, String apiVersion, int operationId) throws Exception { Connection connection = null; PreparedStatement ps = null; ResultSet results = null; StringBuilder sql = new StringBuilder(); sql.append( "SELECT category,subcategory,sum(response_count) as total, sum(chargeAmount) as totalPayAmount from ") .append(HostObjectConstants.NB_RESPONSE_SUMMARY_TABLE) .append(" where year=? and month=? and api=? and version=? and consumerKey=? ") .append("and responseCode like '2%' and operationType = ? group by category,subcategory"); Map<CategoryCharge, BilledCharge> apiCount = new HashMap<CategoryCharge, BilledCharge>(); try { connection = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB); ps = connection.prepareStatement(sql.toString()); ps.setShort(1, year); ps.setShort(2, month); ps.setString(3, apiName); ps.setString(4, apiVersion); ps.setString(5, consumerKey); ps.setInt(6, operationId); results = ps.executeQuery(); log.debug("REPORTS DEBUG LOGS 01 : ps = " + ps); CategoryCharge categoryCharge = null; while (results.next()) { categoryCharge = new CategoryCharge(operationId, results.getString("category"), results.getString("subcategory")); apiCount.put(categoryCharge, new BilledCharge(results.getInt("total"))); } } catch (Exception e) { handleException("getNorthboundAPICountsForSubscription", e); } finally { DbUtils.closeAllConnections(ps, connection, results); } return apiCount; }
From source file:com.wso2telco.dep.reportingservice.dao.BillingDAO.java
/** * Gets the API counts for subscription. * * @param consumerKey the consumer key/*from w w w . j a v a 2s .c o m*/ * @param year the year * @param month the month * @param apiName the api name * @param apiVersion the api version * @param operatorId the operator id * @param operationId the operation id * @return the API counts for subscription * @throws Exception the exception */ public Map<CategoryCharge, BilledCharge> getAPICountsForSubscription(String consumerKey, short year, short month, String apiName, String apiVersion, String operatorId, int operationId) throws Exception { Connection connection = null; PreparedStatement ps = null; ResultSet results = null; StringBuilder sql = new StringBuilder(); sql.append("SELECT category,subcategory,sum(response_count) as total, sum(chargeAmount) as totalPayAmount ") .append("from ").append(HostObjectConstants.SB_RESPONSE_SUMMARY_TABLE) .append(" where year=? and month=? and api=? and version=? and consumerKey=? and operatorId=? ") .append("and responseCode like '2%' and operationType = ? group by category,subcategory"); Map<CategoryCharge, BilledCharge> apiCount = new HashMap<CategoryCharge, BilledCharge>(); try { connection = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB); ps = connection.prepareStatement(sql.toString()); ps.setShort(1, year); ps.setShort(2, month); ps.setString(3, apiName); ps.setString(4, apiVersion); ps.setString(5, consumerKey); ps.setString(6, operatorId); ps.setInt(7, operationId); results = ps.executeQuery(); CategoryCharge categoryCharge = null; while (results.next()) { categoryCharge = new CategoryCharge(operationId, results.getString("category"), results.getString("subcategory")); apiCount.put(categoryCharge, new BilledCharge(results.getInt("total"))); } } catch (Exception e) { handleException("getAPICountsForSubscription", e); } finally { DbUtils.closeAllConnections(ps, connection, results); } log.debug("apiCount :" + apiName + " :" + apiVersion + ": " + consumerKey); return apiCount; }
From source file:com.wso2telco.dep.reportingservice.dao.BillingDAO.java
/** * Gets the nb payment amounts.// w w w .j a v a2 s . c o m * * @param year the year * @param month the month * @param consumerKey the consumer key * @param api_version the api_version * @param operation the operation * @param category the category * @param subcategory the subcategory * @return the nb payment amounts * @throws Exception the exception */ //Retriving amount charged from end-user through payment API public Set<PaymentRequestDTO> getNbPaymentAmounts(short year, short month, String consumerKey, String api_version, int operation, String category, String subcategory) throws Exception { Connection connection = null; PreparedStatement ps = null; ResultSet results = null; StringBuilder sql = new StringBuilder(); sql.append("SELECT api,userId,consumerKey,chargeAmount,category,subcategory,merchantId,time FROM ") .append(HostObjectConstants.NB_RESPONSE_SUMMARY_TABLE) .append(" WHERE year=? and month=? and api_version =? and consumerKey=? and operationType=? and category=? and subcategory=? and responseCode like '2%' AND operatorRef NOT IN ") .append(" (SELECT distinct operatorRef from ").append(HostObjectConstants.NB_RESPONSE_SUMMARY_TABLE) .append(" WHERE year=? and month=? and consumerKey=? and operationType=101 and responseCode like '2%')"); Set<PaymentRequestDTO> requestSet = new HashSet<PaymentRequestDTO>(); try { connection = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB); ps = connection.prepareStatement(sql.toString()); ps.setShort(1, year); ps.setShort(2, month); ps.setString(3, api_version); ps.setString(4, consumerKey); ps.setInt(5, operation); ps.setString(6, category); ps.setString(7, subcategory); ps.setShort(8, year); ps.setShort(9, month); ps.setString(10, consumerKey); results = ps.executeQuery(); while (results.next()) { PaymentRequestDTO paymentRequest = new PaymentRequestDTO(); paymentRequest.setUserId(results.getString("userId")); paymentRequest.setConsumerKey(results.getString("consumerKey")); String value = results.getString("chargeAmount"); paymentRequest.setAmount(new BigDecimal(value.replaceAll(",", ""))); paymentRequest.setCategory(results.getString("category")); paymentRequest.setSubcategory(results.getString("subcategory")); paymentRequest.setMerchant(results.getString("merchantId")); String rqdate = results.getString("time"); paymentRequest.setDate(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(rqdate)); requestSet.add(paymentRequest); } } catch (Exception e) { handleException("getNbPaymentAmounts", e); } finally { DbUtils.closeAllConnections(ps, connection, results); } return requestSet; }
From source file:com.wso2telco.dep.reportingservice.dao.BillingDAO.java
/** * Gets the payment amounts.// w w w . j av a2s . co m * * @param year the year * @param month the month * @param consumerKey the consumer key * @param api_version the api_version * @param operatorId the operator id * @param operation the operation * @param category the category * @param subcategory the subcategory * @return the payment amounts * @throws Exception the exception */ //Retriving amount charged from end-user through payment API public Set<PaymentRequestDTO> getPaymentAmounts(short year, short month, String consumerKey, String api_version, String operatorId, int operation, String category, String subcategory) throws Exception { Connection connection = null; PreparedStatement ps = null; ResultSet results = null; StringBuilder sql = new StringBuilder(); sql.append("SELECT api,userId,consumerKey,chargeAmount,category,subcategory,merchantId,time FROM ") .append(HostObjectConstants.SB_RESPONSE_SUMMARY_TABLE) .append(" WHERE api_version =? and consumerKey=? and operatorId =? and responseCode like '2%' and month=? and year=? and operationType=? and category=? and subcategory=? AND operatorRef NOT IN ") .append(" (SELECT distinct operatorRef FROM ").append(HostObjectConstants.SB_RESPONSE_SUMMARY_TABLE) .append(" WHERE api='refund') "); Set<PaymentRequestDTO> requestSet = new HashSet<PaymentRequestDTO>(); try { connection = DbUtils.getDbConnection(DataSourceNames.WSO2AM_STATS_DB); ps = connection.prepareStatement(sql.toString()); ps.setString(1, api_version); ps.setString(2, consumerKey); ps.setString(3, operatorId); ps.setShort(4, month); ps.setShort(5, year); ps.setInt(6, operation); ps.setString(7, category); ps.setString(8, subcategory); ps.setString(9, consumerKey); ps.setString(10, operatorId); ps.setShort(11, year); ps.setShort(12, month); log.debug("SQL (PS) st ---> " + ps.toString()); results = ps.executeQuery(); log.debug("SQL (PS) ed ---> "); while (results.next()) { PaymentRequestDTO paymentRequest = new PaymentRequestDTO(); paymentRequest.setUserId(results.getString("userId")); paymentRequest.setConsumerKey(results.getString("consumerKey")); String value = results.getString("chargeAmount"); paymentRequest.setAmount(new BigDecimal(value.replaceAll(",", ""))); paymentRequest.setCategory(results.getString("category")); paymentRequest.setSubcategory(results.getString("subcategory")); paymentRequest.setMerchant(results.getString("merchantId")); String rqdate = results.getString("time"); paymentRequest.setDate(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(rqdate)); requestSet.add(paymentRequest); } } catch (Exception e) { handleException("getPaymentAmounts", e); } finally { DbUtils.closeAllConnections(ps, connection, results); } log.debug("done getPaymentAmounts :" + consumerKey + " :" + api_version + " :" + operatorId); return requestSet; }
From source file:com.funambol.foundation.items.dao.PIMContactDAO.java
/** * Updates the property 'photoType' of the contact with the given id to the * given value using the given connection. * <br/>//from www . j a v a 2 s.co m * The contact is moreover marked as UPDATED and its last_update is updated * @param con the connection to use * @param contactId the contact to update * @param photoType the type of the photo * @return the number of the updated rows * @throws DAOException if an error occurs */ private int setPhotoType(Connection con, Long contactId, Short photoType) throws DAOException { if (!ContactWrapper.EMPTY_PHOTO.equals(photoType) && !ContactWrapper.PHOTO_IMAGE.equals(photoType) && !ContactWrapper.PHOTO_URL.equals(photoType) && photoType != null) { throw new IllegalArgumentException(photoType + " is not a valid photoType"); } PreparedStatement stmt = null; int numUpdatedRows = 0; try { stmt = con.prepareStatement(SQL_UPDATE_FNBL_PIM_CONTACT_PHOTO_TYPE); stmt.setString(1, String.valueOf(Def.PIM_STATE_UPDATED)); stmt.setLong(2, System.currentTimeMillis()); if (photoType != null) { stmt.setShort(3, photoType); } else { stmt.setNull(3, Types.SMALLINT); } stmt.setLong(4, contactId); stmt.setString(5, userId); numUpdatedRows = stmt.executeUpdate(); } catch (SQLException ex) { throw new DAOException("Error updating '" + contactId + "'", ex); } finally { DBTools.close(null, stmt, null); } return numUpdatedRows; }
From source file:org.LexGrid.util.sql.lgTables.SQLTableUtilities.java
/** * Runs SQL Statement "INSERT" on the given table and and table prefix for * the supplied attributeValues/*w ww . j a v a 2s. co m*/ * * @param table * @param attributeValues * @return * @throws SQLException */ public boolean insertRow(String table, Map attributeValues) throws SQLException { PreparedStatement prepStmt = null; Object attribute = null; boolean success = false; try { prepStmt = sqlConnection_.prepareStatement(getSQLTableConstants().getInsertStatementSQL(table)); for (int i = 0; i < attributeValues.size(); i++) { attribute = attributeValues.get("" + (i + 1)); // If null, we are unable to determine the SQL param type, // so String is assumed by default. if (attribute == null) { prepStmt.setString(i + 1, null); } else if (attribute instanceof String) { prepStmt.setString(i + 1, (String) attribute); } else if (attribute instanceof Blob) { prepStmt.setBlob(i + 1, (Blob) attribute); } else if (attribute instanceof Boolean) { prepStmt.setBoolean(i + 1, ((Boolean) attribute).booleanValue()); } else if (attribute instanceof Byte) { prepStmt.setByte(i + 1, ((Byte) attribute).byteValue()); } else if (attribute instanceof byte[]) { prepStmt.setBytes(i + 1, (byte[]) attribute); } else if (attribute instanceof Date) { prepStmt.setDate(i + 1, (Date) attribute); } else if (attribute instanceof Double) { prepStmt.setDouble(i + 1, ((Double) attribute).doubleValue()); } else if (attribute instanceof Float) { prepStmt.setFloat(i + 1, ((Float) attribute).floatValue()); } else if (attribute instanceof Integer) { prepStmt.setInt(i + 1, ((Integer) attribute).intValue()); } else if (attribute instanceof Long) { prepStmt.setLong(i + 1, ((Long) attribute).longValue()); } else if (attribute instanceof Short) { prepStmt.setShort(i + 1, ((Short) attribute).shortValue()); } else if (attribute instanceof Timestamp) { prepStmt.setTimestamp(i + 1, (Timestamp) attribute); } } success = prepStmt.execute(); } finally { prepStmt.close(); } return success; }