Java Code Examples for java.sql.PreparedStatement#setShort()

The following examples show how to use java.sql.PreparedStatement#setShort() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: GoodsInsertJob.java    From dble with GNU General Public License v2.0 6 votes vote down vote up
private int insert(Connection con, List<Map<String, String>> list) throws SQLException {
    PreparedStatement ps;
    String sql = "insert into goods (id,name ,good_type,good_img_url,good_created ,good_desc, price ) values(?,? ,?,?,? ,?, ?)";
    ps = con.prepareStatement(sql);
    for (Map<String, String> map : list) {
        ps.setLong(1, Long.parseLong(map.get("id")));
        ps.setString(2, (String) map.get("name"));
        ps.setShort(3, Short.parseShort(map.get("good_type")));
        ps.setString(4, (String) map.get("good_img_url"));
        ps.setString(5, (String) map.get("good_created"));
        ps.setString(6, (String) map.get("good_desc"));
        ps.setDouble(7, Double.parseDouble(map.get("price")));
        ps.addBatch();
    }
    ps.executeBatch();
    return list.size();
}
 
Example 2
Source File: TableQueryCondition.java    From MyBox with Apache License 2.0 6 votes vote down vote up
public static boolean insert(PreparedStatement statement, QueryCondition condition) {
    if (statement == null || condition == null || !condition.isValid()) {
        return false;
    }
    try {
        statement.setString(1, condition.getDataName());
        statement.setShort(2, (short) condition.getOperation());
        statement.setString(3, condition.getTitle());
        statement.setString(4, condition.getPrefix());
        statement.setString(5, condition.getWhere());
        statement.setString(6, condition.getOrder());
        statement.setString(7, condition.getFetch());
        statement.setInt(8, condition.getTop());
        statement.setString(9, DateTools.datetimeToString(new Date()));
        return statement.executeUpdate() > 0;
    } catch (Exception e) {
        failed(e);
        logger.debug(e.toString());
        return false;
    }
}
 
Example 3
Source File: TableEpidemicReport.java    From MyBox with Apache License 2.0 6 votes vote down vote up
private static boolean setInsert(PreparedStatement statement, EpidemicReport report) {
    if (statement == null || !validReport(report)) {
        return false;
    }
    try {
        statement.setString(1, report.getDataSet());
        statement.setLong(2, report.getLocationid());
        statement.setString(3, DateTools.datetimeToString(report.getTime()));
        statement.setShort(4, (short) report.getSource());
        statement.setLong(5, report.getConfirmed());
        statement.setLong(6, report.getHealed());
        statement.setLong(7, report.getDead());
        statement.setLong(8, report.getIncreasedConfirmed());
        statement.setLong(9, report.getIncreasedHealed());
        statement.setLong(10, report.getIncreasedDead());
        return true;
    } catch (Exception e) {
        failed(e);
        logger.debug(e.toString());
        return false;
    }
}
 
Example 4
Source File: Standard.java    From spliceengine with GNU Affero General Public License v3.0 6 votes vote down vote up
/**
 * Schedule a delivery using the database as the queuing
 * mechanism and the results file.
 * See delivery.sql.
 */
public void scheduleDelivery(Display display, Object displayData, short w,
        short carrier) throws Exception {
    
    PreparedStatement sdSchedule = prepareStatement(
            "INSERT INTO DELIVERY_REQUEST(DR_W_ID, DR_CARRIER_ID, DR_STATE) " +
            "VALUES(?, ?, 'Q')");
    
    int isolation = conn.getTransactionIsolation(); 
    try {

        conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        
        sdSchedule.setShort(1, w);
        sdSchedule.setShort(2, carrier);
        sdSchedule.executeUpdate();
        reset(sdSchedule);
        conn.commit();
    } finally {
        conn.setTransactionIsolation(isolation);
    }
    
    if (display != null)
        display.displayScheduleDelivery(displayData, w, carrier);
}
 
Example 5
Source File: GemFireXDDataExtractorJUnit.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
private void updateData(String tableName, int startIndex, int endIndex) throws SQLException {
  PreparedStatement ps = connection.prepareStatement("UPDATE " + tableName + " set blobField=?, charField=?," +
      "charForBitData=?, clobField=?, dateField=?, decimalField=?, doubleField=?, floatField=?, longVarcharForBitDataField=?, numericField=?," +
      "realField=?, smallIntField=?, timeField=?, timestampField=?, varcharField=?, varcharForBitData=?, xmlField=xmlparse(document cast (? as clob) PRESERVE WHITESPACE) where bigIntegerField=?");
 
  for (int i = startIndex; i < endIndex; i++) {
    int lessThan10 = i % 10;

    ps.setBlob(1,new ByteArrayInputStream(new byte[]{(byte)i,(byte)i,(byte)i,(byte)i}));
    ps.setString(2, ""+lessThan10);
    ps.setBytes(3, ("" + lessThan10).getBytes());
    ps.setClob(4, new StringReader("UPDATE CLOB " + i));
    ps.setDate(5, new Date(System.currentTimeMillis()));
    ps.setBigDecimal(6, new BigDecimal(lessThan10 + .8));
    ps.setDouble(7, i + .88);
    ps.setFloat(8, i + .9f);
    ps.setBytes(9, ("B" + lessThan10).getBytes());
    ps.setBigDecimal(10, new BigDecimal(i));
    ps.setFloat(11, lessThan10 * 1111);
    ps.setShort(12, (short)i);
    ps.setTime(13, new Time(System.currentTimeMillis()));
    ps.setTimestamp(14, new Timestamp(System.currentTimeMillis()));
    ps.setString(15, "BY" + lessThan10);
    ps.setBytes(16, ("" + lessThan10).getBytes());
    ps.setClob(17, new StringReader("<xml><sometag>UPDATE XML CLOB " + i + "</sometag></xml>"));
    ps.setLong(18, i);
    ps.execute();
  }
}
 
Example 6
Source File: ShortPreparedStatementIndexSetter.java    From SimpleFlatMapper with MIT License 5 votes vote down vote up
@Override
public void set(PreparedStatement target, Short value, int columnIndex, Context context) throws SQLException {
    if (value != null) {
        target.setShort(columnIndex, value);
    } else {
        target.setNull(columnIndex, Types.SMALLINT);
    }
}
 
Example 7
Source File: DbAppender.java    From semagrow with Apache License 2.0 5 votes vote down vote up
void bindLoggingEventWithInsertStatement(PreparedStatement stmt,
                                         ILoggingEvent event) throws SQLException
{
    stmt.setLong(TIMESTMP_INDEX, event.getTimeStamp());
    stmt.setString(FORMATTED_MESSAGE_INDEX, event.getFormattedMessage());
    stmt.setString(LOGGER_NAME_INDEX, event.getLoggerName());
    stmt.setString(LEVEL_STRING_INDEX, event.getLevel().toString());
    stmt.setString(THREAD_NAME_INDEX, event.getThreadName());
    stmt.setShort(REFERENCE_FLAG_INDEX, DBHelper.computeReferenceMask(event));

    bindLoggingEventArgumentsWithPreparedStatement(stmt, event.getArgumentArray());
}
 
Example 8
Source File: Standard.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
/**
 * Order status by customer identifier.
 * Based up the example SQL queries in appendix A.3
 */
public void orderStatus(Display display, Object displayData, short w,
        short d, int c) throws Exception {
    
    PreparedStatement osCustomerById = prepareStatement(
            "SELECT C_BALANCE, C_FIRST, C_MIDDLE, C_LAST " +
            "FROM CUSTOMER WHERE C_W_ID = ? AND C_D_ID = ? AND C_ID = ?");
    
    customer.clear();
    customer.setWarehouse(w);
    customer.setDistrict(d);
    customer.setId(c);
    
    try {
        // Get the customer information
        osCustomerById.setShort(1, w);
        osCustomerById.setShort(2, d);
        osCustomerById.setInt(3, c);
        ResultSet rs = osCustomerById.executeQuery();
        rs.next();
        customer.setBalance(rs.getString("C_BALANCE"));
        customer.setFirst(rs.getString("C_FIRST"));
        customer.setMiddle(rs.getString("C_MIDDLE"));
        customer.setLast(rs.getString("C_LAST"));    
        reset(osCustomerById);

        getOrderStatusForCustomer(display, displayData, false, customer);
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    }
}
 
Example 9
Source File: Operation.java    From dacapobench with Apache License 2.0 4 votes vote down vote up
/**
 * Payment by customer last name. Section 2.5.2 The CUSTOMER row will be
 * fetched and then updated. This is due to the need to select the specific
 * customer first based upon last name (which will actually fetch and hence
 * lock a number of customers).
 */
public void payment(Display display, Object displayData, short w, short d, short cw, short cd, String customerLast, String amount) throws Exception {

  PreparedStatement pyCustomerByName = prepareStatement("SELECT C_ID " + "FROM CUSTOMER WHERE C_W_ID = ? AND C_D_ID = ? AND C_LAST = ? " + "ORDER BY C_FIRST");

  // Since so much data is needed for the payment transaction
  // from the customer we don't fill it in as we select the
  // correct customer. Instead we just fetch the identifier
  // and then execute a payment by identifier.
  try {
    pyCustomerByName.setShort(1, cw);
    pyCustomerByName.setShort(2, cd);
    pyCustomerByName.setString(3, customerLast);
    ResultSet rs = pyCustomerByName.executeQuery();

    nameList.clear();
    while (rs.next()) {
      nameList.add(rs.getObject("C_ID"));
    }
    reset(pyCustomerByName);
    if (nameList.isEmpty())
      throw new SQLException("Payment by name - no matching customer " + customerLast);

    // Customer to use is midpoint (with round up) (see 2.5.2.2)
    int mid = nameList.size() / 2;
    if (mid != 0) {
      if (nameList.size() % 2 == 1)
        mid++;
    }

    int c = ((Integer) nameList.get(mid)).intValue();

    paymentById(display, displayData, w, d, cw, cd, c, amount);
  } catch (SQLException e) {
    conn.rollback();
    throw e;
  }

  if (display != null)
    ;
}
 
Example 10
Source File: Standard.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
/**
 * Fetch the order details having obtained the customer information
 * and display it.
 */
private void getOrderStatusForCustomer(Display display, Object displayData,
        boolean byName, Customer customer) throws Exception
{
    PreparedStatement osLastOrderNumber = prepareStatement(
            "SELECT MAX(O_ID) AS LAST_ORDER FROM ORDERS " +
            "WHERE O_W_ID = ? AND O_D_ID = ? AND O_C_ID = ?");
    PreparedStatement osOrderDetails = prepareStatement(
            "SELECT O_ENTRY_D, O_CARRIER_ID, O_OL_CNT " +
            "FROM ORDERS WHERE O_W_ID = ? AND O_D_ID = ? AND O_ID = ?");
    PreparedStatement osOrderLineItems = prepareStatement(
            "SELECT OL_I_ID, OL_SUPPLY_W_ID, OL_QUANTITY, OL_AMOUNT, " +
            "OL_DELIVERY_D FROM ORDERLINE " +
            "WHERE OL_W_ID = ? AND OL_D_ID = ? AND OL_O_ID = ?");
    
    order.clear();
    order.setWarehouse(customer.getWarehouse());
    order.setDistrict(customer.getDistrict());
    
    // Find the most recent order number for this customer
    osLastOrderNumber.setShort(1, customer.getWarehouse());
    osLastOrderNumber.setShort(2, customer.getDistrict());
    osLastOrderNumber.setInt(3, customer.getId());
    ResultSet rs = osLastOrderNumber.executeQuery();
    rs.next();
    order.setId(rs.getInt("LAST_ORDER"));
    reset(osLastOrderNumber);
    
    // Details for the order.
    osOrderDetails.setShort(1, customer.getWarehouse());
    osOrderDetails.setShort(2, customer.getDistrict());
    osOrderDetails.setInt(3, order.getId());
    rs = osOrderDetails.executeQuery();
    rs.next();
    order.setEntry_d(rs.getTimestamp("O_ENTRY_D"));
    order.setCarrier_id((Integer) rs.getObject("O_CARRIER_ID"));
    order.setOl_cnt(rs.getInt("O_OL_CNT"));
    rs.close();

    OrderLine[] lineItems = new OrderLine[order.getOl_cnt()];
    
    osOrderLineItems.setShort(1, order.getWarehouse());
    osOrderLineItems.setShort(2, order.getDistrict());
    osOrderLineItems.setInt(3, order.getId());
    rs = osOrderLineItems.executeQuery();
    int oli = 0;
    while (rs.next())
    {
        OrderLine ol = new OrderLine();
        ol.setI_id(rs.getInt("OL_I_ID"));
        ol.setSupply_w_id(rs.getShort("OL_SUPPLY_W_ID"));
        ol.setQuantity(rs.getShort("OL_QUANTITY"));
        ol.setAmount(rs.getString("OL_AMOUNT"));
        ol.setDelivery_d( rs.getTimestamp("OL_DELIVERY_D"));
        
        lineItems[oli++] = ol;
    }
    rs.close();
    conn.commit();
    
    if (display != null)
        display.displayOrderStatus(displayData,
                byName, customer, order, lineItems);
}
 
Example 11
Source File: DefaultStorageDataTypeContext.java    From registry with Apache License 2.0 4 votes vote down vote up
@Override
public void setPreparedStatementParams(PreparedStatement preparedStatement,
                                       Schema.Type type, int index, Object val) throws SQLException {
    if (val == null) {
        preparedStatement.setNull(index, getSqlType(type));
        return;
    }

    switch (type) {
        case BOOLEAN:
            preparedStatement.setBoolean(index, (Boolean) val);
            break;
        case BYTE:
            preparedStatement.setByte(index, (Byte) val);
            break;
        case SHORT:
            preparedStatement.setShort(index, (Short) val);
            break;
        case INTEGER:
            preparedStatement.setInt(index, (Integer) val);
            break;
        case LONG:
            preparedStatement.setLong(index, (Long) val);
            break;
        case FLOAT:
            preparedStatement.setFloat(index, (Float) val);
            break;
        case DOUBLE:
            preparedStatement.setDouble(index, (Double) val);
            break;
        case STRING:
            preparedStatement.setString(index, (String) val);
            break;
        case BINARY:
            preparedStatement.setBytes(index, (byte[]) val);
            break;
        case BLOB:
            preparedStatement.setBinaryStream(index, (InputStream) val);
            break;
        case NESTED:
        case ARRAY:
            preparedStatement.setObject(index, val);    //TODO check this
            break;
    }
}
 
Example 12
Source File: Standard.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
/**
 * Payment by customer last name.
 * Section 2.5.2
 * The CUSTOMER row will be fetched and then updated.
 * This is due to the need to select the specific customer
 * first based upon last name (which will actually fetch and
 * hence lock a number of customers).
 */
public void payment(Display display, Object displayData, short w, short d,
        short cw, short cd, String customerLast, String amount)
        throws Exception {
        
    PreparedStatement pyCustomerByName = prepareStatement(
                "SELECT C_ID " +
                "FROM CUSTOMER WHERE C_W_ID = ? AND C_D_ID = ? AND C_LAST = ? " +
                "ORDER BY C_FIRST");
    
    // Since so much data is needed for the payment transaction
    // from the customer we don't fill it in as we select the
    // correct customer. Instead we just fetch the identifier
    // and then execute a payment by identifier.
    try {
        pyCustomerByName.setShort(1, cw);
        pyCustomerByName.setShort(2, cd);
        pyCustomerByName.setString(3, customerLast);
        ResultSet rs = pyCustomerByName.executeQuery();

        nameList.clear();
        while (rs.next())
        {           
            nameList.add(rs.getObject("C_ID"));            
        }
        reset(pyCustomerByName);
        if (nameList.isEmpty())
            throw new SQLException("Payment by name - no matching customer "
                    + customerLast);
        
        // Customer to use is midpoint (with round up) (see 2.5.2.2)
        int mid = nameList.size()/2;
        if (mid != 0) {
            if (nameList.size()%2 == 1)
                mid++;
        }
        
        int c = ((Integer) nameList.get(mid)).intValue();

        paymentById(display, displayData, w, d, cw, cd, c, amount);
    } catch (SQLException e) {
        conn.rollback();
        throw e;
    }
    
    if (display != null)
        ;
}
 
Example 13
Source File: CommonRepositoryHandler.java    From sqoop-on-spark with Apache License 2.0 4 votes vote down vote up
/**
 * 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
 * @param configs
 * @param type
 * @param baseConfigStmt
 * @param baseInputStmt
 * @param conn
 * @return short number of configs registered.
 * @throws java.sql.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) {
    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(CommonRepositoryError.COMMON_0012,
          Integer.toString(baseConfigCount));
    }
    ResultSet rsetConfigId = baseConfigStmt.getGeneratedKeys();
    if (!rsetConfigId.next()) {
      throw new SqoopException(CommonRepositoryError.COMMON_0013);
    }

    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(config, inputs, baseInputStmt);
    // validate all the input relations
    Map<Long, List<String>> inputRelationships = new HashMap<Long, List<String>>();
    for (MInput<?> input : inputs) {
      List<String> inputOverrides = validateAndGetOverridesAttribute(input, config);
      if (inputOverrides != null && inputOverrides.size() > 0) {
        inputRelationships.put(input.getPersistenceId(), inputOverrides);
      }
    }

    // Insert all input relations
    if (inputRelationships != null && inputRelationships.size() > 0) {
      for (Map.Entry<Long, List<String>> entry : inputRelationships.entrySet()) {
        List<String> children = entry.getValue();
        for (String child : children) {
          Long childId = config.getInput(child).getPersistenceId();
          insertConfigInputRelationship(entry.getKey(), childId, conn);
        }
      }
    }
  }
  return configIndex;
}
 
Example 14
Source File: TradeCompaniesDMLStmt.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
protected int deleteFromTable(PreparedStatement stmt, String symbol,
    String exchange, short type, UUID uid, UUID uid2, String companyName,
    String note, UDTPrice price, long asset, byte[] logo, int tid, 
    int whichDelete) throws SQLException {
  
  String database = SQLHelper.isDerbyConn(stmt.getConnection())?"Derby - " :"gemfirexd - ";  
  
  String query = " QUERY: " + delete[whichDelete];
  
  int rowCount = 0;
  switch (whichDelete) {
  case 0:   
    //"delete from trade.companies where symbol = ? and exchange = ?",
    Log.getLogWriter().info(database + "deleting  trade.companies with SYMBOL:" + symbol + 
        ",EXCHANGE:" + exchange + ",TID:" + tid + query);  
    stmt.setString(1, symbol);
    stmt.setString(2, exchange);
    rowCount = stmt.executeUpdate();
    Log.getLogWriter().info(database + "deleted " +  rowCount + " rows in trade.companies with SYMBOL:" + symbol + 
        ",EXCHANGE:" + exchange + ",TID:" + tid + query);
    break;
  case 1:
    //"delete from trade.companies where companyName = ? and tid=?",
    Log.getLogWriter().info(database + "deleting  trade.companies with  COMPANYNAME:" + companyName 
              + ",TID:" + tid + query);  
    stmt.setString(1, companyName);
    stmt.setInt(2, tid);
    rowCount = stmt.executeUpdate();
    Log.getLogWriter().info(database + "deleted " +  rowCount + " rows in trade.companies with  COMPANYNAME:" + companyName 
              + ",TID:" + tid + query);  
    break;
  case 2:   
    //"delete from trade.companies where companyType IN (?, ?) and trade.getHighPrice(histPrice) <? and tid=?",     
    short type2 = (short) ((type + 1) % 10);
    Log.getLogWriter().info(database + "deleting  trade.companies with 1_TYPE:" + type + ",2_TYPE:" + type2 +
        ",HIGHPRICE:" + UDTPrice.getHighPrice(price) + ",TID:" + tid + query);  
    stmt.setShort(1, type);
    stmt.setShort(2, type2);
    stmt.setBigDecimal(3, UDTPrice.getHighPrice(price));
    stmt.setInt(4, tid);
    rowCount = stmt.executeUpdate();
    Log.getLogWriter().info(database + "deleted " +  rowCount + " rows in trade.companies with 1_TYPE:" + type + ",2_TYPE:" + type2 +
        ",HIGHPRICE:" + UDTPrice.getHighPrice(price) + ",TID:" + tid + query); 
    break;
  case 3:   
    //"delete from trade.companies where tid=? and symbol < ? and  trade.getHighPrice(histPrice) >=? and asset <= ? and companyType = ?",
    Log.getLogWriter().info(database + "deleting  trade.companies with  TID:" + tid +
        ",SYMBOL:" + symbol + ",HIGHPRICE:" + UDTPrice.getHighPrice(price) + ",ASSET:" + asset + 
        ",TYPE:" + type + query);  
    stmt.setInt(1, tid);
    stmt.setString(2, symbol);
    stmt.setBigDecimal(3, UDTPrice.getHighPrice(price));
    stmt.setLong(4, asset);
    stmt.setShort(5, type);
    rowCount = stmt.executeUpdate();
    Log.getLogWriter().info(database + "deleted " +  rowCount + " rows in trade.companies with  TID:" + tid +
        ",SYMBOL:" + symbol + ",HIGHPRICE:" + UDTPrice.getHighPrice(price) + ",ASSET:" + asset + 
        ",TYPE:" + type + query);  
    break;
  case 4: 
    String pattern = (note != null && note.length() > 4 ) ? "%" + note.substring(1, 4) : "%abc";
    pattern += '%';
    //"delete from trade.companies where tid=? and trade.getLowPrice(histPrice) <=? and note like ? and companyType = ?",
    Log.getLogWriter().info(database + "deleting  trade.companies with TID:" + tid +
        ",LOWPRICE:" + UDTPrice.getLowPrice(price) + ",TYPE:" + type + ",PATTERN:" + pattern + query);  
    stmt.setInt(1, tid);
    stmt.setString(3, pattern);
    stmt.setBigDecimal(2, UDTPrice.getLowPrice(price));
    stmt.setShort(4, type);
    rowCount = stmt.executeUpdate();
    Log.getLogWriter().info(database + "deleted " +  rowCount + " rows in trade.companies TID:" + tid +
        ",LOWPRICE:" + UDTPrice.getLowPrice(price) + ",TYPE:" + type + ",PATTERN:" + pattern + query); 
    break;
  default:
    throw new TestException("incorrect delete statement, should not happen");
  }  
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rowCount;
}
 
Example 15
Source File: JdbcUtils.java    From pinlater with Apache License 2.0 4 votes vote down vote up
/**
 * Bind parameter onto PreparedStatement.
 *
 * @param stmt  the prepared statement to bind the parameter onto.
 * @param index the index of the parameter in the statement.
 * @param value the value of the parameter be bound onto the statement.
 * @throws SQLException
 */
public static void bindParameter(PreparedStatement stmt, int index, Object value)
    throws SQLException {
  if (value == Nulls.NULL_STRING || value instanceof String) {
    // string or clob
    if (value == Nulls.NULL_STRING) {
      stmt.setNull(index, Types.VARCHAR);
    } else {
      stmt.setString(index, (String) value);
    }
  } else if (value == Nulls.NULL_LONG || value instanceof Long) {
    stmt.setLong(index, (Long) value);
  } else if (value == Nulls.NULL_TIMESTAMP || value instanceof java.sql.Timestamp) {
    // we don't support java.sql.Date, and we should not be using that type either.
    stmt.setTimestamp(index, (java.sql.Timestamp) value);
  } else if (value == Nulls.NULL_INTEGER || value instanceof Integer) {
    if (value == Nulls.NULL_INTEGER) {
      stmt.setNull(index, Types.INTEGER);
    } else {
      stmt.setInt(index, (Integer) value);
    }
  } else if (value == Nulls.NULL_BOOLEAN || value instanceof Boolean) {
    stmt.setBoolean(index, (Boolean) value);
  } else if (value == Nulls.NULL_BYTE || value instanceof Byte) {
    stmt.setByte(index, (Byte) value);
  } else if (value == Nulls.NULL_SHORT || value instanceof Short) {
    stmt.setShort(index, (Short) value);
  } else if (value == Nulls.NULL_FLOAT || value instanceof Float) {
    stmt.setFloat(index, (Float) value);
  } else if (value == Nulls.NULL_DOUBLE || value instanceof Double) {
    stmt.setDouble(index, (Double) value);
  } else if (value == Nulls.NULL_BYTE_ARRAY || value instanceof byte[]) {
    // blob
    stmt.setBytes(index, (byte[]) value);
  } else if (value == Nulls.NULL_BIGDECIMAL || value instanceof BigDecimal) {
    stmt.setBigDecimal(index, (BigDecimal) value);
  } else if (value == null) {
    stmt.setNull(index, Types.NULL);
  } else {
    throw new InvalidParameterException(
        String.format("value type not supported: %s", value.getClass().getName()));
  }
}
 
Example 16
Source File: PrepareStatementTest.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
/**
   * This test case ensures that the bug introduced by the first patch for
   * Jira-815 has not been re-introduced.  The bug resulted in a hang if a
   * prepared statement was first executed with a lob value, and then
   * re-executed with a null-value in place of the lob.
   */
// GemStone changes BEGIN
// disabled due to #41047
  public void testAlternatingLobValuesAndNull()  throws Exception
// GemStone changes END
  {
      getConnection().setAutoCommit(false);
      Statement st = createStatement();
      st.execute("create table tt1 (CLICOL01 smallint not null)");
      st.execute("alter table tt1 add clicol02 smallint");
      st.execute("alter table tt1 add clicol03 int not null default 1");
      st.execute("alter table tt1 add clicol04 int");
      st.execute("alter table tt1 add clicol05 decimal(10,0) not null default 1");
      st.execute("alter table tt1 add clicol51 blob(1G)");
      st.execute("alter table tt1 add clicol52 blob(50)");
      st.execute("alter table tt1 add clicol53 clob(2G) not null default ''");
      st.execute("alter table tt1 add clicol54 clob(60)");
      commit();

      PreparedStatement pSt =
          prepareStatement("insert into tt1 values (?,?,?,?,?,?,?,?,?)");
      pSt.setShort(1, (short)500);
      pSt.setShort(2, (short)501);
      pSt.setInt(3, 496);
      pSt.setInt(4, 497);
      pSt.setDouble(5, 484);
      pSt.setBytes(6, "404 bit".getBytes());
      pSt.setBytes(7, "405 bit".getBytes());
      pSt.setString(8, "408 bit");
      pSt.setString(9, "409 bit");

      // Inserting first row
      assertUpdateCount(pSt, 1);

      pSt.setNull(2, java.sql.Types.SMALLINT);
      pSt.setNull(4, java.sql.Types.DOUBLE);
      pSt.setNull(7, java.sql.Types.BLOB);
      pSt.setNull(9, java.sql.Types.CLOB);

      // Inserting second row
      assertUpdateCount(pSt, 1);

      // Now inserting 3rd row, using lobs from 1st row
      ResultSet rs = st.executeQuery("select * from tt1");
      rs.next();
      pSt.setShort(1, rs.getShort(1));
      pSt.setShort(2, rs.getShort(2));
      pSt.setInt(3, rs.getInt(3));
      pSt.setInt(4, rs.getInt(4));
      pSt.setDouble(5, rs.getDouble(5));
      pSt.setBlob(6, rs.getBlob(6));
      pSt.setBlob(7, rs.getBlob(7));
      pSt.setClob(8, rs.getClob(8));
      pSt.setClob(9, rs.getClob(9));
      pSt.execute();

      // Now inserting 4th row, using lobs from 2nd row
      rs.next();
      pSt.setNull(2, java.sql.Types.SMALLINT);
      pSt.setNull(4, java.sql.Types.DOUBLE);
      pSt.setBlob(6, rs.getBlob(6));
      pSt.setNull(7, java.sql.Types.BLOB);
      pSt.setClob(8, rs.getClob(8));
      pSt.setNull(9, java.sql.Types.CLOB);
      pSt.execute();

      rs.close();
      pSt.close();

      commit();
  }
 
Example 17
Source File: PlatformImplBase.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
/**
 * 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.
 * 
 * @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);
       }
}
 
Example 18
Source File: TradeCompaniesDMLStmt.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
protected int insertToTable(PreparedStatement stmt, String symbol, String exchange, 
    short companyType, UUID uid, String companyName, Clob companyInfo, String note, 
    UDTPrice price, long asset, byte[] logo, int tid, boolean isPut) throws SQLException {
  
  String database = SQLHelper.isDerbyConn(stmt.getConnection())?"Derby - " :"gemfirexd - ";  
  
  
  Log.getLogWriter().info(database + (isPut ? "putting" : "inserting") + " into trade.companies with SYMBOL:"+ symbol + 
      ",EXCHANGE:" + exchange + ",COMPANYTYPE:" + companyType +
      ",UID:" + ResultSetHelper.convertByteArrayToString(getUidBytes(uid)) + 
      ",UUID:" + uid + ",COMPANYNAME:" + companyName +
      ",COMPANYINFO:" + (ResultSetHelper.useMD5Checksum && companyInfo != null ? 
      ResultSetHelper.convertClobToChecksum(companyInfo, companyInfo.length()) : 
        getStringFromClob(companyInfo)) +
      ",NOTE:" + (ResultSetHelper.useMD5Checksum && note !=null && note.length() > ResultSetHelper.longVarCharSize ? 
        ResultSetHelper.convertStringToChecksum(note, note.length()) : note) +
      ",HIGHPRICE:" + price + ",ASSET:" + ((getMyTid() % 11 == 0)? null : asset) +
      ",LOGO:" + ResultSetHelper.convertByteArrayToString(logo) + ",TID:" + tid);
  
  
  stmt.setString(1, symbol);
  stmt.setString(2, exchange); 
  stmt.setShort(3, companyType);
  stmt.setBytes(4, getUidBytes(uid));
  stmt.setObject(5, uid);

  stmt.setString(6, companyName);
  if (companyInfo == null)
    stmt.setNull(7, Types.CLOB);
  else
    stmt.setClob(7, companyInfo);
  stmt.setString(8, note);
  stmt.setObject(9, price);
  if (getMyTid() % 11 == 0) 
    stmt.setNull(10, Types.BIGINT);
  else
    stmt.setLong(10, asset);
  stmt.setBytes(11, logo);
  stmt.setInt(12, tid);

  int rowCount = stmt.executeUpdate();
  
  Log.getLogWriter().info(database + (isPut ? "put " : "inserted ") + rowCount + " rows into trade.companies SYMBOL:"+ symbol + 
      ",EXCHANGE:" + exchange + ",COMPANYTYPE:" + companyType +
      ",UID:" + ResultSetHelper.convertByteArrayToString(getUidBytes(uid)) + 
      ",UUID:" + uid + ",COMPANYNAME:" + companyName +
      ",COMPANYINFO:" + (ResultSetHelper.useMD5Checksum && companyInfo != null ? 
      ResultSetHelper.convertClobToChecksum(companyInfo, companyInfo.length()) : 
        getStringFromClob(companyInfo)) +
      ",NOTE:" + (ResultSetHelper.useMD5Checksum && note !=null && note.length() > ResultSetHelper.longVarCharSize ? 
        ResultSetHelper.convertStringToChecksum(note, note.length()) : note) +
      ",HIGHPRICE:" + price + ",ASSET:" + ((getMyTid() % 11 == 0)? null : asset) +
      ",LOGO:" + ResultSetHelper.convertByteArrayToString(logo) + ",TID:" + tid);
  
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  return rowCount;
}
 
Example 19
Source File: MetricsDbBase.java    From mysql_perf_analyzer with Apache License 2.0 4 votes vote down vote up
/**
  * Store db info into db. If exists, update the info. If not, insert new one.
  * @param dbinfo
  */
 public boolean upsertDBInfo(Connection conn, DBInstanceInfo dbinfo, boolean insert)
 {
   if(dbinfo==null)return false;
String sql2 = "update " +DBINFO_TABLENAME+" set dbgroupname=?, hostname=?, dbtype=?,instance=?, port=?, database_name=?,USE_SSHTUNNEL=?, LOCAL_HOSTNAME=?, LOCAL_PORT=?, CONNECTION_VERIFIED=?, VIRTUAL_HOST=?, OWNER=? where DBID=?";
String sql3 = "insert into "+DBINFO_TABLENAME+" (dbgroupname,hostname,dbtype,instance, port, database_name,USE_SSHTUNNEL, LOCAL_HOSTNAME, LOCAL_PORT,CONNECTION_VERIFIED,VIRTUAL_HOST, OWNER, DBID) values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
	
logger.info("Store or update db "+dbinfo.toString());
PreparedStatement pstmt = null;
boolean findOne = false;
try
{
  //first, check if we have record
  findOne = !insert;
  pstmt = conn.prepareStatement(findOne?sql2:sql3);
  int idx = 1;
  pstmt.setString(idx++, dbinfo.getDbGroupName().toLowerCase());
  pstmt.setString(idx++, dbinfo.getHostName().toLowerCase());
  pstmt.setString(idx++, dbinfo.getDbType());
  pstmt.setString(idx++, String.valueOf(dbinfo.getInstance()));
  if(dbinfo.getPortShort() != 0)
	  pstmt.setShort(idx++, dbinfo.getPortShort());
  else 
	  pstmt.setNull(idx++, java.sql.Types.SMALLINT);
  pstmt.setString(idx++, dbinfo.getDatabaseName());
  pstmt.setString(idx++, dbinfo.isUseTunneling()?"1":"0");
  pstmt.setString(idx++, dbinfo.getLocalHostName());
  if(dbinfo.getLocalPortShort() != 0)
	  pstmt.setShort(idx++, dbinfo.getLocalPortShort());
  else 
	  pstmt.setNull(idx++, java.sql.Types.SMALLINT);
  pstmt.setString(idx++, dbinfo.isConnectionVerified()?"1":"0");
  pstmt.setString(idx++, dbinfo.isVirtualHost()?"1":"0");
  pstmt.setString(idx++, dbinfo.getOwner());
  pstmt.setInt(idx++, dbinfo.getDbid());
  pstmt.execute();
  return true;
}catch(Exception ex)
{
  logger.info("Failed to save "+dbinfo.toString()+", "+ex.getMessage());
  if(conn!=null)try{conn.rollback();}catch(Exception iex){}
  return false;
}finally
{
  DBUtils.close(pstmt);
}			
 }
 
Example 20
Source File: SQLSmallint.java    From gemfirexd-oss with Apache License 2.0 3 votes vote down vote up
/**
	Set the value into a PreparedStatement.

	@exception SQLException Error setting value in PreparedStatement
*/
public final void setInto(PreparedStatement ps, int position) throws SQLException {

	if (isNull()) {
		ps.setNull(position, java.sql.Types.SMALLINT);
		return;
	}

	ps.setShort(position, value);
}