下面列出了java.sql.PreparedStatement#setDouble ( ) 实例代码,或者点击链接到github查看源代码,也可以在右侧发表评论。
private static void bindField(ValueBuffer valueBuffer, PreparedStatement preparedStatement, int parameterIndex, boolean isUuid)
throws SQLException
{
Type type = valueBuffer.getType();
if (valueBuffer.isNull()) {
preparedStatement.setNull(parameterIndex, typeToSqlType(type));
}
else if (type.getJavaType() == long.class) {
preparedStatement.setLong(parameterIndex, valueBuffer.getLong());
}
else if (type.getJavaType() == double.class) {
preparedStatement.setDouble(parameterIndex, valueBuffer.getDouble());
}
else if (type.getJavaType() == boolean.class) {
preparedStatement.setBoolean(parameterIndex, valueBuffer.getBoolean());
}
else if (type.getJavaType() == Slice.class && isUuid) {
preparedStatement.setBytes(parameterIndex, valueBuffer.getSlice().getBytes());
}
else if (type.getJavaType() == Slice.class) {
preparedStatement.setString(parameterIndex, new String(valueBuffer.getSlice().getBytes(), UTF_8));
}
else {
throw new IllegalArgumentException("Unknown Java type: " + type.getJavaType());
}
}
@Override
public void apply(Connection connection) throws Exception {
wideColumnPrepare(connection, "Float64");
String params = Strings.repeat("?, ", columnNum);
PreparedStatement pstmt = connection.prepareStatement("INSERT INTO " + getTableName() +" values(" + params.substring(0, params.length()-2) + ")");
for (int i = 0; i < batchSize; i++) {
for (int j = 0; j < columnNum; j++ ) {
pstmt.setDouble(j + 1, j + 1.2);
}
pstmt.addBatch();
}
int []res = pstmt.executeBatch();
Assert.assertEquals(res.length, batchSize);
wideColumnAfter(connection);
}
private HoldingDataBean createHolding(Connection conn, int accountID, String symbol, double quantity, BigDecimal purchasePrice) throws Exception {
Timestamp purchaseDate = new Timestamp(System.currentTimeMillis());
PreparedStatement stmt = getStatement(conn, createHoldingSQL);
Integer holdingID = KeySequenceDirect.getNextID(conn, "holding", inSession, getInGlobalTxn());
stmt.setInt(1, holdingID.intValue());
stmt.setTimestamp(2, purchaseDate);
stmt.setBigDecimal(3, purchasePrice);
stmt.setDouble(4, quantity);
stmt.setString(5, symbol);
stmt.setInt(6, accountID);
stmt.executeUpdate();
stmt.close();
return getHoldingData(conn, holdingID.intValue());
}
/**
* 通过参数构建SQL
* @param psmt
* @param paramIndex
* @param param
* ;
*/
private void setParameter(PreparedStatement psmt, int paramIndex, Object param) {
try {
if (param instanceof String) {
psmt.setString(paramIndex, (String) param);
} else if (param instanceof Integer) {
psmt.setInt(paramIndex, (Integer) param);
} else if (param instanceof Long) {
psmt.setLong(paramIndex, (Long) param);
} else if (param instanceof Float) {
psmt.setFloat(paramIndex, (Float) param);
} else if (param instanceof Double) {
psmt.setDouble(paramIndex, (Double) param);
} else if (param instanceof Date) {
psmt.setTimestamp(paramIndex, new Timestamp(((Date) param).getTime()));
}
} catch (SQLException e) {
LOGGER.error("", e);
}
}
/**
* Execute a prepared statement with a single double argument
* and return the double value from the single row returned.
*/
private double getValue(PreparedStatement ps, double value)
throws SQLException {
ps.setDouble(1, value);
ResultSet rs = ps.executeQuery();
rs.next(); // we know a single value will be returned.
double rValue = rs.getDouble(1);
rs.close();
return rValue;
}
private void setDoubleValue(int queryType, String value, String paramType,
PreparedStatement sqlQuery, int i) throws SQLException {
Double val = null;
if (value != null) {
val = Double.parseDouble(value);
}
if (QueryTypes.IN.equals(paramType)) {
if (queryType == SQLQuery.DS_QUERY_TYPE_NORMAL) {
if (value == null) {
sqlQuery.setNull(i + 1, Types.DOUBLE);
} else {
sqlQuery.setDouble(i + 1, val);
}
} else {
if (value == null) {
((CallableStatement) sqlQuery).setNull(i + 1, Types.DOUBLE);
} else {
((CallableStatement) sqlQuery).setDouble(i + 1, val);
}
}
} else if (QueryTypes.INOUT.equals(paramType)) {
if (value == null) {
((CallableStatement) sqlQuery).setNull(i + 1, Types.DOUBLE);
} else {
((CallableStatement) sqlQuery).setDouble(i + 1, val);
}
((CallableStatement) sqlQuery).registerOutParameter(i + 1, Types.DOUBLE);
} else {
((CallableStatement) sqlQuery).registerOutParameter(i + 1, Types.DOUBLE);
}
}
@Override
public void excluir(Compra compra) throws Exception {
Conexao c = new Conexao();
String sql = "UPDATE TBCOMPRA SET CODIGOFORNECEDOR=?, DATACOMPRA=?, VALORTOTAL=?, SITUACAO=? WHERE CODIGO=?";
PreparedStatement ps = c.getConexao().prepareStatement(sql);
ps.setInt(1, compra.getFornecedor().getCodigo());
ps.setDate(2, new Date(compra.getDataCompra().getTime()));
ps.setDouble(3, compra.getValorTotal());
ps.setInt(4, Situacao.CANCELADA.getId());
ps.setInt(5, compra.getCodigo());
ps.execute();
c.confirmar();
}
private void updateUnsignedTable(Connection conn, double data) throws Exception {
PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + unsignedTableName + " VALUES (?, ?, ?, ?, ?, ?, ?)");
stmt.setString(1, KEY);
Double d = Double.valueOf(data);
stmt.setDouble(2, d.doubleValue());
stmt.setFloat(3, d.floatValue());
stmt.setInt(4, d.intValue());
stmt.setLong(5, d.longValue());
stmt.setShort(6, d.shortValue());
stmt.setByte(7, d.byteValue());
stmt.executeUpdate();
conn.commit();
}
static void addParameters(final PreparedStatement preparedStatement, final Object... parameters)
throws SQLException {
if (parameters == null) {
return;
}
int index = 0;
for (final Object param : parameters) {
index++;
if (param instanceof Integer) {
preparedStatement.setInt(index, (Integer) param);
} else if (param instanceof Long) {
preparedStatement.setLong(index, (Long) param);
} else if (param instanceof Boolean) {
preparedStatement.setBoolean(index, (Boolean) param);
} else if (param instanceof String) {
preparedStatement.setString(index, (String) param);
} else if (param instanceof Double) {
preparedStatement.setDouble(index, (Double) param);
} else if (param instanceof Float) {
preparedStatement.setFloat(index, (Float) param);
} else if (param instanceof byte[]) {
preparedStatement.setBytes(index, (byte[]) param);
} else {
throw new IllegalStateException("invalid parameter type: " + param);
}
}
}
private void insertData(String tableName, int startIndex, int endIndex) throws SQLException{
Connection connection = TestUtil.getConnection();
PreparedStatement ps = connection.prepareStatement("INSERT INTO " + tableName + "(bigIntegerField, blobField, charField," +
"charForBitData, clobField, dateField, decimalField, doubleField, floatField, longVarcharForBitDataField, numericField," +
"realField, smallIntField, timeField, timestampField, varcharField, varcharForBitData, xmlField) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, xmlparse(document cast (? as clob) PRESERVE WHITESPACE))");
for (int i = startIndex; i < endIndex; i++) {
int lessThan10 = i % 10;
ps.setLong(1, i); //BIG INT
ps.setBlob(2,new ByteArrayInputStream(new byte[]{(byte)i,(byte)i,(byte)i,(byte)i}));
ps.setString(3, ""+lessThan10);
ps.setBytes(4, ("" + lessThan10).getBytes());
ps.setClob(5, new StringReader("SOME CLOB " + i));
ps.setDate(6, new Date(System.currentTimeMillis()));
ps.setBigDecimal(7, new BigDecimal(lessThan10 + .8));
ps.setDouble(8, i + .88);
ps.setFloat(9, i + .9f);
ps.setBytes(10, ("A" + lessThan10).getBytes());
ps.setBigDecimal(11, new BigDecimal(i));
ps.setFloat(12, lessThan10 * 1111);
ps.setShort(13, (short)i);
ps.setTime(14, new Time(System.currentTimeMillis()));
ps.setTimestamp(15, new Timestamp(System.currentTimeMillis()));
ps.setString(16, "HI" + lessThan10);
ps.setBytes(17, ("" + lessThan10).getBytes());
ps.setClob(18, new StringReader("<xml><sometag>SOME XML CLOB " + i + "</sometag></xml>"));
ps.execute();
}
}
public static void setSelectActivityIndexSQLParameters(PreparedStatement statement, int index, long playtimeThreshold, long date) throws SQLException {
statement.setDouble(index, Math.PI);
statement.setLong(index + 1, playtimeThreshold);
statement.setLong(index + 2, date - TimeUnit.DAYS.toMillis(7L));
statement.setLong(index + 3, date);
statement.setLong(index + 4, date - TimeUnit.DAYS.toMillis(14L));
statement.setLong(index + 5, date - TimeUnit.DAYS.toMillis(7L));
statement.setLong(index + 6, date - TimeUnit.DAYS.toMillis(21L));
statement.setLong(index + 7, date - TimeUnit.DAYS.toMillis(14L));
}
@Override
public void setNonNullParameter(PreparedStatement ps, int index, Double parameter, JdbcType jdbcType)
throws SQLException {
ps.setDouble(index, parameter);
}
public void testNonKeyBasedTransactionalUpdatesAndConflict() throws Exception {
startVMs(1, 1);
Connection conn = TestUtil.jdbcConn;
Statement st = conn.createStatement();
st.execute("create schema trade");
st.execute("create table trade.securities (sec_id int not null, "
+ "symbol varchar(10) not null, price decimal (30, 20), "
+ "exchange varchar(10) not null, tid int, "
+ "constraint sec_pk primary key (sec_id) ) "
+ " partition by primary key"+ getSuffix());// column (tid) ");
conn.setTransactionIsolation(getIsolationLevel());
conn.setAutoCommit(false);
final int numRows = 5;
PreparedStatement ps = conn
.prepareStatement("insert into trade.securities values "
+ "(?, ?, ?, ?, ?)");
for (int i = 0; i < numRows; i++) {
ps.setInt(1, i);
ps.setString(2, "XXXX" + i);
ps.setDouble(3, i);
ps.setString(4, "nasdaq");
ps.setInt(5, i);
ps.executeUpdate();
}
conn.commit();
PreparedStatement psUpdate = conn
.prepareStatement("update trade.securities "
+ "set symbol = ? where sec_id = ? and tid = ?");
for (int i = 0; i < numRows; i++) {
psUpdate.setString(1, "YYY" + i);
psUpdate.setInt(2, i);
psUpdate.setInt(3, i);
psUpdate.executeUpdate();
}
this.gotConflict = false;
Thread otherTx = new Thread(new Runnable() {
@Override
public void run() {
final Region<Object, Object> r = Misc
.getRegionForTable("TRADE.SECURITIES", true);
final CacheTransactionManager txManager = Misc.getGemFireCache()
.getCacheTransactionManager();
txManager.begin();
try {
Object key = TestUtil.getGemFireKey(0, r);
Object row = r.get(key);
r.put(key, row);
} catch (ConflictException ce) {
gotConflict = true;
txManager.rollback();
} catch (StandardException se) {
gotConflict = false;
fail("failing put with exception", se);
}
}
});
otherTx.start();
otherTx.join();
assertTrue("expected conflict", this.gotConflict);
this.gotConflict = false;
conn.commit();
// rs.close();
st.close();
psUpdate.close();
ps.close();
conn.close();
}
/**
* 设置PreparedStatement的参数
*
* @param stmt
* @param parameters
* @throws SQLException
* @throws NumberFormatException
*/
default void setPreparedStatmentParameters(PreparedStatement stmt, List<Parameter> parameters)
throws NumberFormatException, SQLException {
if (parameters != null) {
for (int i = 0; i < parameters.size(); i++) {
switch (parameters.get(i).getDataType()) {
case SHORT:
stmt.setShort(i + 1, Short.parseShort(parameters.get(i).getValue().toString()));
break;
case INT:
stmt.setInt(i + 1, Integer.parseInt(parameters.get(i).getValue().toString()));
break;
case LONG:
stmt.setLong(i + 1, Long.parseLong(parameters.get(i).getValue().toString()));
break;
case FLOAT:
stmt.setFloat(i + 1, Float.parseFloat(parameters.get(i).getValue().toString()));
break;
case DOUBLE:
stmt.setDouble(i + 1, Double.parseDouble(parameters.get(i).getValue().toString()));
break;
case STRING:
stmt.setString(i + 1, parameters.get(i).getValue().toString());
break;
case DATE:
SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy-MM-dd");
try {
Date formattedDate = dateFormatter.parse(parameters.get(i).getValue().toString());
stmt.setDate(i + 1, new java.sql.Date(formattedDate.getTime()));
} catch (ParseException e) {
throw new DAOException(e);
}
break;
case DATETIME:
Date date = (Date) parameters.get(i).getValue();
stmt.setDate(i + 1, new java.sql.Date(date.getTime()));
break;
case TIMESTAMP:
Date dt = (Date) parameters.get(i).getValue();
SimpleDateFormat timestampFormatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
stmt.setTimestamp(i + 1, java.sql.Timestamp.valueOf(timestampFormatter.format(dt)));
break;
case BOOLEAN:
stmt.setBoolean(i + 1, Boolean.parseBoolean(parameters.get(i).getValue().toString()));
break;
case OBJECT:
stmt.setObject(i + 1, parameters.get(i));
break;
default:
stmt.setObject(i + 1, parameters.get(i));
}
}
}
}
/**
* Flush the instance of this clause into database.
* Here "instance" means all the constant ID in instance with
* its weight as a row in DB.
* @param db the database object used to store clause instances.
*/
protected void sealClauseInstances(RDB db) {
if(!isTemplate){
Clause.mappingFromID2Desc.put(this.id+ ".0", this.toString(-1));
return;
}
// cosntruct SQL of adding instances
ArrayList<String> atts = new ArrayList<String>();
ArrayList<String> pholders = new ArrayList<String>();
atts.add("weight FLOAT8");
pholders.add("?");
for(String v : metaVars) {
if (Config.constants_as_raw_string) {
atts.add(v + " TEXT");
} else {
atts.add(v + " INT");
}
pholders.add("?");
}
atts.add("myisfixed TEXT");
pholders.add("?");
db.dropTable(relIntanceClauses);
String sql = "CREATE TABLE " + relIntanceClauses +
StringMan.commaListParen(atts);
db.update(sql);
//TODO:
db.dropSequence(relIntanceClauses+"_seq");
sql = "CREATE SEQUENCE " + relIntanceClauses + "_seq;";
db.update(sql);
sql = "ALTER TABLE " + relIntanceClauses + " ADD myid INT;";
db.update(sql);
sql = "ALTER TABLE " + relIntanceClauses + " ALTER COLUMN myid SET " +
"DEFAULT NEXTVAL('" + relIntanceClauses + "_seq');";
db.update(sql);
int instanceCount = 0;
sql = "INSERT INTO " + relIntanceClauses + " VALUES" +
StringMan.commaListParen(pholders);
PreparedStatement psAddMeta = db.getPrepareStatement(sql);
int ni = 0;
try {
for(ClauseInstance ins : instances){
double weight = ins.weight;
ArrayList<Term> meta = ins.conList;
psAddMeta.setDouble(1, weight);
for(int k=0; k<meta.size(); k++) {
if (Config.constants_as_raw_string) {
psAddMeta.setString(k+2, meta.get(k).constantString());
} else {
psAddMeta.setInt(k+2, meta.get(k).constant());
}
}
psAddMeta.setString(meta.size()+2, ins.isFixedWeight == true? "fixed" : "");
instanceCount ++;
Clause.mappingFromID2Desc.put(this.id+ "." + instanceCount, this.toString(ni++));
psAddMeta.addBatch();
}
psAddMeta.executeBatch();
psAddMeta.close();
} catch (SQLException e) {
ExceptionMan.handle(e);
}
psAddMeta = null;
}
/**
* Test index scan on multivm queries on tables with redundancy.
* @throws Exception
*/
public void testIndexScans () throws Exception {
startVMs(2, 2);
Connection conn = TestUtil.jdbcConn;
Statement s = conn.createStatement();
PreparedStatement psInsertBrokerTickets = null;
s.execute("Create table broker_tickets (id int not null, ticketPrice int not null ," +
" firmId int not null , price double, quantity int, ticker varchar(20)) " +
" redundancy 1");
psInsertBrokerTickets = conn.prepareStatement("insert into broker_tickets " +
"(id, ticketPrice, firmId, price, quantity, ticker ) " +
"values (?, ?, ?, ?, ?, ?)");
int numInserts = 1000;
int key = 0;
double price = 1.0;
int quantity = 10;
for (int i = 0; i < numInserts; i++) {
psInsertBrokerTickets.setInt(1, key + 10);
if ((i % 2) == 0) {
psInsertBrokerTickets.setInt(2, 10);
} else {
psInsertBrokerTickets.setInt(2, 20);
}
psInsertBrokerTickets.setInt(3, key + 10);
psInsertBrokerTickets.setDouble(4, price);
psInsertBrokerTickets.setInt(5, quantity);
psInsertBrokerTickets.setString(6, "YYYY" + key);
int rt = psInsertBrokerTickets.executeUpdate();
assertEquals("Insert should return 1.", 1, rt) ;
key += 10;
price += 0.01;
}
s.execute("create index index_ticketPrice on broker_tickets (ticketPrice)");
ResultSet rs = s.executeQuery("select avg(distinct ticketPrice) from " +
"broker_tickets");
while (rs.next()) {
assertEquals("Result should match ", 15, rs.getInt(1));
}
}
static void assignParameter(@NotNull final PreparedStatement stmt,
final int index,
@Nullable final Object object)
throws SQLException
{
if (object == null) {
assignNull(stmt, index);
return;
}
String setter = null;
try {
if (object instanceof Boolean) {
setter = "setBoolean";
stmt.setBoolean(index, (Boolean) object);
}
else if (object instanceof Byte) {
setter = "setByte";
stmt.setByte(index, (Byte) object);
}
else if (object instanceof Short) {
setter = "setShort";
stmt.setShort(index, (Short) object);
}
else if (object instanceof Integer) {
setter = "setInt";
stmt.setInt(index, (Integer) object);
}
else if (object instanceof Float) {
setter = "setFloat";
stmt.setFloat(index, (Float) object);
}
else if (object instanceof Double) {
setter = "setDouble";
stmt.setDouble(index, (Double) object);
}
else if (object instanceof BigInteger) {
setter = "setBigDecimal";
BigDecimal bigDecimal = new BigDecimal(object.toString());
stmt.setBigDecimal(index, bigDecimal);
}
else if (object instanceof BigDecimal) {
setter = "setBigDecimal";
stmt.setBigDecimal(index, (BigDecimal) object);
}
else if (object instanceof Long) {
setter = "setLong";
stmt.setLong(index, (Long) object);
}
else if (object instanceof Character) {
setter = "setString";
stmt.setString(index, object.toString());
}
else if (object instanceof String) {
setter = "setString";
stmt.setString(index, (String) object);
}
else if (object instanceof java.sql.Date) {
setter = "setDate";
stmt.setDate(index, (java.sql.Date) object);
}
else if (object instanceof Timestamp) {
setter = "setTimestamp";
stmt.setTimestamp(index, (Timestamp) object);
}
else if (object instanceof java.sql.Time) {
setter = "setTime";
stmt.setTime(index, (java.sql.Time) object);
}
else if (object instanceof java.util.Date) {
setter = "setTimestamp";
stmt.setTimestamp(index, new Timestamp(((java.util.Date) object).getTime()));
}
else if (object instanceof byte[]) {
setter = "setBytes";
stmt.setBytes(index, (byte[]) object);
}
else {
setter = "setObject";
stmt.setObject(index, object);
}
}
catch (Exception e) {
//noinspection ConstantConditions
String message = setter != null
? format("A problem with setting parameter %d using %s(). The original value class is %s. Exception %s: %s",
index, setter, object.getClass().getCanonicalName(), e.getClass().getSimpleName(), e.getMessage())
: format("An unexpected problem with setting parameter %d. Exception %s: %s",
index, e.getClass().getSimpleName(), e.getMessage());
throw new DBParameterSettingException(message, e, null);
}
}
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Double parameter, JdbcType jdbcType) throws SQLException {
ps.setDouble(i, parameter);
}
@Override
public void setParameter(PreparedStatement statement, int parameterIndex, Double arg) throws SQLException {
statement.setDouble(parameterIndex, arg);
}
@Override
public void saveUsageRecords(List<UsageVO> usageRecords) {
TransactionLegacy txn = TransactionLegacy.currentTxn();
try {
txn.start();
String sql = INSERT_USAGE_RECORDS;
PreparedStatement pstmt = null;
pstmt = txn.prepareAutoCloseStatement(sql); // in reality I just want CLOUD_USAGE dataSource connection
for (UsageVO usageRecord : usageRecords) {
pstmt.setLong(1, usageRecord.getZoneId());
pstmt.setLong(2, usageRecord.getAccountId());
pstmt.setLong(3, usageRecord.getDomainId());
pstmt.setString(4, usageRecord.getDescription());
pstmt.setString(5, usageRecord.getUsageDisplay());
pstmt.setInt(6, usageRecord.getUsageType());
pstmt.setDouble(7, usageRecord.getRawUsage());
if (usageRecord.getVmInstanceId() != null) {
pstmt.setLong(8, usageRecord.getVmInstanceId());
} else {
pstmt.setNull(8, Types.BIGINT);
}
pstmt.setString(9, usageRecord.getVmName());
if (usageRecord.getOfferingId() != null) {
pstmt.setLong(10, usageRecord.getOfferingId());
} else {
pstmt.setNull(10, Types.BIGINT);
}
if (usageRecord.getTemplateId() != null) {
pstmt.setLong(11, usageRecord.getTemplateId());
} else {
pstmt.setNull(11, Types.BIGINT);
}
if (usageRecord.getUsageId() != null) {
pstmt.setLong(12, usageRecord.getUsageId());
} else {
pstmt.setNull(12, Types.BIGINT);
}
pstmt.setString(13, usageRecord.getType());
if (usageRecord.getSize() != null) {
pstmt.setLong(14, usageRecord.getSize());
} else {
pstmt.setNull(14, Types.BIGINT);
}
if (usageRecord.getNetworkId() != null) {
pstmt.setLong(15, usageRecord.getNetworkId());
} else {
pstmt.setNull(15, Types.BIGINT);
}
pstmt.setString(16, DateUtil.getDateDisplayString(s_gmtTimeZone, usageRecord.getStartDate()));
pstmt.setString(17, DateUtil.getDateDisplayString(s_gmtTimeZone, usageRecord.getEndDate()));
if (usageRecord.getVirtualSize() != null) {
pstmt.setLong(18, usageRecord.getVirtualSize());
} else {
pstmt.setNull(18, Types.BIGINT);
}
pstmt.addBatch();
}
pstmt.executeBatch();
txn.commit();
} catch (Exception ex) {
txn.rollback();
s_logger.error("error saving usage records to cloud_usage db", ex);
throw new CloudRuntimeException(ex.getMessage());
}
}