下面列出了java.sql.PreparedStatement#setShort ( ) 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
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();
}
/**
* 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);
}
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;
}
}
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;
}
}
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();
}
}
/**
* 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;
}
}
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());
}
@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);
}
}
/**
* 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);
}
}
/**
* 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()));
}
}
/**
* 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)
;
}
/**
* 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);
}
/**
* 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)
;
}
@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;
}
}
/**
* 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;
}
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;
}
/**
* 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();
}
/**
* 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);
}
}
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;
}
/**
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);
}