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

The following examples show how to use java.sql.PreparedStatement#executeUpdate() . These examples are extracted from open source projects. 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 want to check out the right sidebar which shows the related API usage.
Example 1
/**
 * 更新字符字段
 * @param id
 * @param field
 * @param value
 * @return
 */
@Override
public int updateStringField(String id, String field, String value) {
    Connection conn = ConnectionProvider.getConnection();
    if (conn == null) {
        throw new DAOException(CONNECTION_NOT_FOUND_EXCEPTION);
    }
    StringBuilder sql = new StringBuilder(256);
    sql.append("UPDATE ").append(CHANNEL_TABLE).append(" SET ");
    sql.append(field).append("=? WHERE id=?");
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement(sql.toString());
        stmt.setString(1, value);
        stmt.setString(2, id);
        return stmt.executeUpdate();
    } catch (SQLException e) {
        throw new DAOException(e);
    } finally {
        DbUtils.closeStatement(stmt);
    }
}
 
Example 2
/**
 * Test Sybase ASE 15+ bigint data type.
 * @throws Exception
 */
public void testBigint() throws Exception {
    if (!isVersion15orHigher()) {
        return;
    }
    Statement stmt = con.createStatement();
    stmt.execute("CREATE TABLE #TEST (val bigint primary key, val2 bigint null)");
    PreparedStatement pstmt = con.prepareStatement("INSERT INTO #TEST VALUES(?,?)");
    pstmt.setLong(1, Long.MAX_VALUE);
    pstmt.setLong(2, Long.MIN_VALUE);
    pstmt.executeUpdate();
    ResultSet rs = stmt.executeQuery("SELECT * FROM #TEST");
    rs.next();
    assertEquals(Long.MAX_VALUE, rs.getLong(1));
    assertEquals(Long.MIN_VALUE, rs.getLong(2));
    ResultSetMetaData rsmd = rs.getMetaData();
    assertEquals("bigint", rsmd.getColumnTypeName(1));
    assertEquals("bigint", rsmd.getColumnTypeName(2));
    assertEquals(Types.BIGINT, rsmd.getColumnType(1));
    assertEquals(Types.BIGINT, rsmd.getColumnType(2));
}
 
Example 3
Source Project: wind-im   File: SiteFriendApplyDao.java    License: Apache License 2.0 6 votes vote down vote up
public boolean deleteApply(String siteUserId, String siteFriendId) throws SQLException {
	long startTime = System.currentTimeMillis();
	int result = 0;
	String sql = "DELETE FROM " + FRIEND_APPLY_TABLE + " WHERE site_user_id=? AND site_friend_id=?;";

	Connection conn = null;
	PreparedStatement ps = null;
	try {
		conn = DatabaseConnection.getConnection();
		ps = conn.prepareStatement(sql);
		ps.setString(1, siteUserId);
		ps.setString(2, siteFriendId);

		result = ps.executeUpdate();
	} catch (Exception e) {
		throw e;
	} finally {
		DatabaseConnection.returnConnection(conn, ps);
	}

	LogUtils.dbDebugLog(logger, startTime, result, sql, siteUserId);
	return result > 0;
}
 
Example 4
@Test
public void testMetadataOnlySelectAfterExecution() throws Exception {
    connection.createStatement().execute(
        "DROP TABLE IF EXISTS test.mymetadata");
    connection.createStatement().execute(
        "CREATE TABLE IF NOT EXISTS test.mymetadata "
      + "(idx Int32, s String) "
      + "ENGINE = TinyLog"
    );
    PreparedStatement insertStmt = connection.prepareStatement(
        "INSERT INTO test.mymetadata (idx, s) VALUES (?, ?)");
    insertStmt.setInt(1, 42);
    insertStmt.setString(2, "foo");
    insertStmt.executeUpdate();
    PreparedStatement metaStmt = connection.prepareStatement(
        "SELECT idx, s FROM test.mymetadata WHERE idx = ?");
    metaStmt.setInt(1, 42);
    metaStmt.executeQuery();
    ResultSetMetaData metadata = metaStmt.getMetaData();
    Assert.assertEquals(metadata.getColumnCount(), 2);
    Assert.assertEquals(metadata.getColumnName(1), "idx");
    Assert.assertEquals(metadata.getColumnName(2), "s");
}
 
Example 5
protected static void insertEvents(int numEvents, boolean cleanExistingRows, int startRowId)
{
  try (Connection con = DriverManager.getConnection(URL); Statement stmt = con.createStatement()) {
    if (cleanExistingRows) {
      String cleanTable = "delete from " + TABLE_POJO_NAME;
      stmt.executeUpdate(cleanTable);
    }

    String insert = "insert into " + TABLE_POJO_NAME + " values (?,?,?,?,?,?)";
    PreparedStatement pStmt = con.prepareStatement(insert);
    con.prepareStatement(insert);

    for (int i = 0; i < numEvents; i++) {
      pStmt.setInt(1, startRowId + i);
      pStmt.setString(2, "name" + i);
      pStmt.setDate(3, new Date(2016, 1, 1));
      pStmt.setTime(4, new Time(2016, 1, 1));
      pStmt.setTimestamp(5, new Timestamp(2016, 1, 1, 0, 0, 0, 0));
      pStmt.setDouble(6, new Double(55.4));
      pStmt.executeUpdate();
    }

  } catch (SQLException e) {
    throw new RuntimeException(e);
  }
}
 
Example 6
Source Project: Rel   File: TableJDBC.java    License: Apache License 2.0 6 votes vote down vote up
@Override
public void delete(Generator generator, ValueTuple tuple) {
	PreparedStatement preparedStatement;
	String[] values = CSVLineParse.parseTrimmed(tuple.toCSV());
	StringBuffer line = new StringBuffer("delete from " + meta.getTable() + " where ");
	try {
		ResultSet resultSet = statement.executeQuery("select * from " + meta.getTable());
		for (int i = 1; i <= resultSet.getMetaData().getColumnCount(); i++) {
			int type = resultSet.getMetaData().getColumnType(i);
			line.append(resultSet.getMetaData().getColumnName(i) + "=");
			if (type == Types.CHAR || type == Types.VARCHAR || type == Types.LONGVARCHAR || type == Types.NCHAR || type == Types.NVARCHAR)
				line.append("\'" + values[i - 1] + "\' AND ");
			else
				line.append(values[i - 1] + " AND ");
		}
		preparedStatement = connect.prepareStatement(line.substring(0, line.length() - 5) + ";");
		preparedStatement.executeUpdate();

	} catch (SQLException e) {
		System.out.println("TableJDBC[3]: error " + e);
	}
}
 
Example 7
public int fillAndExecutePreparedInsertStatements(List pstmts, List stmts, int bid)
    throws SQLException {
  int results = 0;
  int numTicketsPerBroker = BrokerPrms.getNumTicketsPerBroker();
  int numTicketPrices = BrokerPrms.getNumTicketPrices();
  PreparedStatement stmt = (PreparedStatement) pstmts.get(0);
  for (int i = 0; i < numTicketsPerBroker; i++) {
    int id = BrokerTicket.getId(i, bid, numTicketsPerBroker);
    stmt.setInt(1, id);
    stmt.setInt(2, bid);
    stmt.setDouble(3, BrokerTicket.getPrice(id, numTicketPrices));
    stmt.setInt(4, BrokerTicket.getQuantity(id));
    stmt.setString(5, BrokerTicket.getTicker(id));
    stmt.setString(6, BrokerTicket.getFiller(id)); // str01
    stmt.setString(7, BrokerTicket.getFiller(id)); // str02
    stmt.setString(8, BrokerTicket.getFiller(id)); // str03
    stmt.setString(9, BrokerTicket.getFiller(id)); // str04
    stmt.setString(10, BrokerTicket.getFiller(id)); // str05
    stmt.setString(11, BrokerTicket.getFiller(id)); // str06
    stmt.setString(12, BrokerTicket.getFiller(id)); // str07
    stmt.setString(13, BrokerTicket.getFiller(id)); // str08
    stmt.setString(14, BrokerTicket.getFiller(id)); // str09
    stmt.setString(15, BrokerTicket.getFiller(id)); // str10
    stmt.setString(16, BrokerTicket.getFiller(id)); // str11
    stmt.setString(17, BrokerTicket.getFiller(id)); // str12
    stmt.setString(18, BrokerTicket.getFiller(id)); // str13
    stmt.setString(19, BrokerTicket.getFiller(id)); // str14
    stmt.setString(20, BrokerTicket.getFiller(id)); // str15
    if (logUpdates) {
      Log.getLogWriter().info("Executing update: " + stmt);
    }
    results += stmt.executeUpdate();
    if (logUpdates) {
      Log.getLogWriter().info("Executed update: " + stmt);
    }
  }
  return results;
}
 
Example 8
Source Project: sample.daytrader7   File: TradeDirect.java    License: Apache License 2.0 5 votes vote down vote up
private void updateOrderHolding(Connection conn, int orderID, int holdingID) throws Exception {
    PreparedStatement stmt = getStatement(conn, updateOrderHoldingSQL);

    stmt.setInt(1, holdingID);
    stmt.setInt(2, orderID);
    stmt.executeUpdate();
    stmt.close();
}
 
Example 9
Source Project: AsuraFramework   File: StdJDBCDelegate.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * <p>
 * Delete the cron trigger data for a trigger.
 * </p>
 * 
 * @param conn
 *          the DB Connection
 * @param triggerName
 *          the name of the trigger
 * @param groupName
 *          the group containing the trigger
 * @return the number of rows deleted
 */
public int deleteBlobTrigger(Connection conn, String triggerName,
        String groupName) throws SQLException {
    PreparedStatement ps = null;

    try {
        ps = conn.prepareStatement(rtp(DELETE_BLOB_TRIGGER));
        ps.setString(1, triggerName);
        ps.setString(2, groupName);

        return ps.executeUpdate();
    } finally {
        closeStatement(ps);
    }
}
 
Example 10
public void testNotNullCheck_Bug40018_3() throws Exception
{
  startVMs(1, 3);
  clientSQLExecute(
      1,
      "create table Child ( id2 int primary key, sector_id2 int not null, subsector_id2 int not null)"+getOverflowSuffix());

  try {
    String updateQuery = "update Child set subsector_id2 = ?, sector_id2 = sector_id2 + ? where subsector_id2 = ?";     
    clientSQLExecute(1, "insert into Child values (1,1,1)");
    clientSQLExecute(1, "insert into Child values (2,1,1)");
    clientSQLExecute(1, "insert into Child values (3,1,1)");
    TestUtil.setupConnection();
    PreparedStatement ps = TestUtil.jdbcConn.prepareStatement(updateQuery);
    ps.setNull(1, Types.INTEGER);
    ps.setInt(2, 2);
    ps.setInt(3, 1);
    try {
      ps.executeUpdate();
      fail("Update should not have occured as not null column should not get assigned null value");
    }
    catch (SQLException sqle) {
      this.getLogWriter().info("Expected exception=" + sqle.getMessage());
      assertTrue(sqle.getMessage().indexOf("cannot accept a NULL value") != -1);
    }
  }
  finally {
    GemFireXDQueryObserverHolder
        .setInstance(new GemFireXDQueryObserverAdapter());
    clientSQLExecute(1, "Drop table Child ");     
    //invokeInEveryVM(this.getClass(), "reset");
  }
}
 
Example 11
Source Project: ShoppingCartinJava   File: KidsDB.java    License: MIT License 5 votes vote down vote up
public static void delete(String model){
     try {
         Connection con = DriverManager.getConnection("jdbc:sqlite:DBs/kidsDB.db");
         PreparedStatement ps = con.prepareStatement("DELETE FROM kids WHERE mmodel=?");
         ps.setString(1, model);
         if(ps.executeUpdate()==0)
             JOptionPane.showMessageDialog(null, "Entry does not exist!");
         else
             JOptionPane.showMessageDialog(null, "Entry deleted successfully!");
         
     } catch (SQLException ex) {
         Logger.getLogger(ElectronicsDB.class.getName()).log(Level.SEVERE, null, ex);
     }
         
}
 
Example 12
public void testDropStore() throws Exception {
  Connection conn = TestUtil.getConnection();
  Statement st = conn.createStatement();
  ResultSet rs = null;

  checkDirExistence("./myhdfs");
  st.execute("create hdfsstore myhdfs namenode 'hdfs://127.0.0.1:" + port + "' homedir './myhdfs'");
  st.execute("create table t1 (col1 int primary key, col2 int) hdfsstore (myhdfs)");
  try {
    st.execute("drop hdfsstore myhdfs");
    fail("Should have received an exception");
  } catch(SQLException exected) {
    //do nothing
  }
  
  //insert some data
  PreparedStatement ps = conn.prepareStatement("insert into t1 values (?, ?)");
  int NUM_ROWS = 100;
  for (int i = 0; i < NUM_ROWS; i++) {
    ps.setInt(1, i);
    ps.setInt(2, i + 1);
    ps.executeUpdate();
  }
  
  st.execute("drop table t1");
  //Now this should work
  st.execute("drop hdfsstore myhdfs");
}
 
Example 13
Source Project: phoenix   File: UpsertSelectIT.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void testUpsertSelectLongToInt() throws Exception {
    byte[][] splits = new byte[][] { PInteger.INSTANCE.toBytes(1), PInteger.INSTANCE.toBytes(2),
            PInteger.INSTANCE.toBytes(3), PInteger.INSTANCE.toBytes(4)};
    long ts = nextTimestamp();
    ensureTableCreated(getUrl(),"IntKeyTest",splits, ts-2);
    Properties props = new Properties();
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 1));
    Connection conn = DriverManager.getConnection(getUrl(), props);
    String upsert = "UPSERT INTO IntKeyTest VALUES(1)";
    PreparedStatement upsertStmt = conn.prepareStatement(upsert);
    int rowsInserted = upsertStmt.executeUpdate();
    assertEquals(1, rowsInserted);
    conn.commit();
    conn.close();
    
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 5));
    conn = DriverManager.getConnection(getUrl(), props);
    upsert = "UPSERT INTO IntKeyTest select i+1 from IntKeyTest";
    upsertStmt = conn.prepareStatement(upsert);
    rowsInserted = upsertStmt.executeUpdate();
    assertEquals(1, rowsInserted);
    conn.commit();
    conn.close();
    
    props.setProperty(PhoenixRuntime.CURRENT_SCN_ATTRIB, Long.toString(ts + 10));
    conn = DriverManager.getConnection(getUrl(), props);
    String select = "SELECT i FROM IntKeyTest";
    ResultSet rs = conn.createStatement().executeQuery(select);
    assertTrue(rs.next());
    assertEquals(1,rs.getInt(1));
    assertTrue(rs.next());
    assertEquals(2,rs.getInt(1));
    assertFalse(rs.next());
    conn.close();
}
 
Example 14
Source Project: freeacs   File: ACSUnit.java    License: MIT License 5 votes vote down vote up
private int executeSql(
    String sql, Connection c, UnittypeParameter unittypeParameter, String value, String unitId)
    throws SQLException {
  PreparedStatement pp = c.prepareStatement(sql);
  pp.setString(1, value);
  pp.setString(2, unitId);
  pp.setInt(3, unittypeParameter.getId());
  pp.setQueryTimeout(60);
  int rowsupdated = pp.executeUpdate();
  pp.close();
  return rowsupdated;
}
 
Example 15
@Test
public void dropTableInvalidates() throws Exception {
    PreparedStatement pScan = newScan();
    PreparedStatement pDrop = newDropTable();
    assertEquals("Row count from scan1", ROW_COUNT, countRows(pScan.executeQuery()));
    int count = pDrop.executeUpdate();
    assertEquals("Count from drop table", 0, count);
    expectStale(pScan);
    pScan.close();
    pDrop.close();
}
 
Example 16
Source Project: gemfirexd-oss   File: TransactionDUnit.java    License: Apache License 2.0 4 votes vote down vote up
public void testNonKeyBasedTransactionalUpdatesRollbackAndCommit()
    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 column (tid) "+ getSuffix());

  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();
  }
  // psUpdate.executeUpdate();
  // InternalDistributedSystem.getAnyInstance().getLogWriter().info("XXXX update is done");
  ResultSet rs = st.executeQuery("select * from trade.securities");
  int numRowsReturned = 0;
  while (rs.next()) {
    assertTrue("Got" + rs.getString("SYMBOL").trim(),
        (rs.getString("SYMBOL").trim()).startsWith("YYY"));
    numRowsReturned++;
  }
  assertEquals("Expected " + numRows + " row but found " + numRowsReturned,
      numRows, numRowsReturned);
  conn.rollback();
  // now commit, should be an empty tran.
  conn.commit();
  rs = st.executeQuery("select * from trade.securities");

  int numUpdates = 0;
  while (rs.next()) {
    assertTrue("Got " + rs.getString("SYMBOL").trim(), rs.getString("SYMBOL")
        .trim().startsWith("XXXX"));
    numUpdates++;
  }
  assertEquals(numRows, numUpdates);
  rs.close();

  for (int i = 0; i < numRows; i++) {
    psUpdate.setString(1, "YYY" + i);
    psUpdate.setInt(2, i);
    psUpdate.setInt(3, i);
    psUpdate.executeUpdate();
  }
  conn.commit();

  // verify.
  rs = st.executeQuery("select * from trade.securities");
  numUpdates = 0;
  while (rs.next()) {
    assertTrue("Got " + rs.getString("SYMBOL").trim(), rs.getString("SYMBOL")
        .trim().startsWith("YYY"));
    numUpdates++;
  }
  assertEquals(numRows, numUpdates);

  conn.commit();
  rs.close();
  st.close();
  psUpdate.close();
  ps.close();
  conn.close();
}
 
Example 17
Source Project: gemfirexd-oss   File: AutoGenJDBC30Test.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * After a one-row insert into a table with an auto-generated key, next
 * inserts into a table that does not have an auto-generated key, then
 * requests generated keys.
 * Old harness Test 8.
 * Expected result: ResultSet has one row with a non-NULL key. All four
 * queries in this test return the same result because they fetch the
 * key generated for the previous insert, not the current one.
 * @throws SQLException 
 */
public void testInsertNoGenColAfterOneRowKey() throws SQLException
{
    // Do a one-row insert into a table with an auto-generated key.
    Statement s = createStatement();
    s.execute("insert into t11_AutoGen(c11) values (99)");

    /* The insert into t21_noAutoGen below doesn't insert into a table 
     * with an auto-generated column, so it won't increment the key from 
     * 1 to 2.  The key it fetches will be for the previous insert into 
     * t11_AutoGen.
     */
    int expected=1;

    s.execute("insert into t21_noAutoGen values(27, 'true')", 
        Statement.RETURN_GENERATED_KEYS);
    int keyval = getKeyValue (s.getGeneratedKeys());
    assertEquals("Key value after s.execute()", expected, keyval);

    s.executeUpdate("insert into t21_noAutoGen values(28, 'true')",
        Statement.RETURN_GENERATED_KEYS);
    keyval = getKeyValue (s.getGeneratedKeys());
    assertEquals("Key value after s.executeUpdate()", expected, keyval);

    s.close();

    PreparedStatement ps = prepareStatement(
        "insert into t21_noAutoGen values(29, 'true')", 
        Statement.RETURN_GENERATED_KEYS);
    ps.execute();
    keyval = getKeyValue (ps.getGeneratedKeys());
    assertEquals("Key value after ps.execute()", expected, keyval);

    ps = prepareStatement("insert into t21_noAutoGen values(30, 'true')", 
        Statement.RETURN_GENERATED_KEYS);
    ps.executeUpdate();
    keyval = getKeyValue (ps.getGeneratedKeys());
    assertEquals("Key value after ps.executeUpdate()", expected, keyval);

    ps.close();
}
 
Example 18
Source Project: incubator-tajo   File: AbstractDBStore.java    License: Apache License 2.0 4 votes vote down vote up
@Override
public void deleteTable(final String name) throws CatalogException {
  Connection conn = null;
  PreparedStatement pstmt = null;

  try {
    conn = getConnection();
    conn.setAutoCommit(false);

    StringBuilder sql = new StringBuilder();
    sql.append("DELETE FROM ").append(TB_COLUMNS);
    sql.append(" WHERE ").append(C_TABLE_ID).append(" = ? ");

    if (LOG.isDebugEnabled()) {
      LOG.debug(sql.toString());
    }

    pstmt = conn.prepareStatement(sql.toString());
    pstmt.setString(1, name);
    pstmt.executeUpdate();
    pstmt.close();

    sql.delete(0, sql.length());
    sql.append("DELETE FROM ").append(TB_OPTIONS);
    sql.append(" WHERE ").append(C_TABLE_ID).append(" = ? ");

    if (LOG.isDebugEnabled()) {
      LOG.debug(sql.toString());
    }

    pstmt = conn.prepareStatement(sql.toString());
    pstmt.setString(1, name);
    pstmt.executeUpdate();
    pstmt.close();

    sql.delete(0, sql.length());
    sql.append("DELETE FROM ").append(TB_STATISTICS);
    sql.append(" WHERE ").append(C_TABLE_ID).append(" = ? ");

    if (LOG.isDebugEnabled()) {
      LOG.debug(sql.toString());
    }

    pstmt = conn.prepareStatement(sql.toString());
    pstmt.setString(1, name);
    pstmt.executeUpdate();
    pstmt.close();

    sql.delete(0, sql.length());
    sql.append("DELETE FROM ").append(TB_PARTTIONS);
    sql.append(" WHERE ").append(C_TABLE_ID).append(" = ? ");

    if (LOG.isDebugEnabled()) {
      LOG.debug(sql.toString());
    }

    pstmt = conn.prepareStatement(sql.toString());
    pstmt.setString(1, name);
    pstmt.executeUpdate();
    pstmt.close();

    sql.delete(0, sql.length());
    sql.append("DELETE FROM ").append(TB_TABLES);
    sql.append(" WHERE ").append(C_TABLE_ID).append(" = ? ");

    if (LOG.isDebugEnabled()) {
      LOG.debug(sql.toString());
    }

    pstmt = conn.prepareStatement(sql.toString());
    pstmt.setString(1, name);
    pstmt.executeUpdate();

    // If there is no error, commit the changes.
    conn.commit();
  } catch (SQLException se) {
    try {
      // If there is any error, rollback the changes.
      conn.rollback();
    } catch (SQLException se2) {
    }
    throw new CatalogException(se);
  } finally {
    CatalogUtil.closeQuietly(conn, pstmt);
  }
}
 
Example 19
/**
 * Test for 40234 (Performing updates on all indexes irrespective of 
 * index keys being updated or not)
 */
public void _test40234() throws Exception{
  startVMs(1, 1);
  clientSQLExecute(1, "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), " +
      "constraint sec_uq unique (symbol, exchange), " +
      "constraint exc_ch check (exchange in ('nasdaq', 'nye', 'amex', 'lse', 'fse', 'hkse', 'tse')))  ");

  clientSQLExecute(1,"create table trade.customers (cid int not null, " +
      "cust_name varchar(100), since date, addr varchar(100), " +
      "tid int, primary key (cid))");

  clientSQLExecute( 1, "create table trade.buyorders(oid int not null constraint buyorders_pk primary key, " +
      "cid int, sid int, qty int, bid decimal (30, 20), " +
      "ordertime timestamp, status varchar(10), tid int, " +
      "constraint bo_cust_fk foreign key (cid) references trade.customers (cid), " +
      "constraint bo_sec_fk foreign key (sid) references trade.securities (sec_id), " +
      "constraint bo_qty_ck check (qty>=0))");

  clientSQLExecute(1,"insert into trade.securities values (1,'IBM', " +
      "25.25, 'nasdaq', 5)");
  java.sql.Date since = new java.sql.Date(System.currentTimeMillis());
  Connection conn = TestUtil.jdbcConn;
  PreparedStatement psInsertCust = conn.prepareStatement("insert into trade.customers values (?,?,?,?,?)");
  psInsertCust.setInt(1, 1);
  psInsertCust.setString(2, "XXXX1");
  psInsertCust.setDate(3, since);
  psInsertCust.setString(4, "XXXX1");
  psInsertCust.setInt(5,1);
  psInsertCust.executeUpdate();

  PreparedStatement psInsertBuy = conn.prepareStatement("insert into trade.buyorders values (?,?,?,?,?,?,?,?)");
  BigDecimal value = new BigDecimal (Double.toString((new Random().nextInt(10000)+1) * .01));
  java.sql.Timestamp tstmp = new java.sql.Timestamp(System.currentTimeMillis());
  psInsertBuy.setInt(1,1);
  psInsertBuy.setInt(2,1);
  psInsertBuy.setInt(3,1);
  psInsertBuy.setInt(4, 10);
  psInsertBuy.setBigDecimal(5, value);
  psInsertBuy.setTimestamp(6, tstmp);
  psInsertBuy.setString(7, "open");
  psInsertBuy.setInt(8, 1);
  psInsertBuy.executeUpdate();
  // clientSQLExecute(1, "insert into trade.buyorders(1, 1, 1, 10, "+
  //     new BigDecimal (Double.toString((new Random().nextInt(10000)+1) * .01))+", ' " +
  //                   ""+new java.sql.Timestamp(System.currentTimeMillis())+" ', 'open', 1)");
  PreparedStatement psUpdateBuy = conn.prepareStatement("update trade.buyorders set status = 'cancelled' " +
      "where (ordertime >? or sid=?) and status = 'open' and tid =?");
  psUpdateBuy.setTimestamp(1, tstmp);
  psUpdateBuy.setInt(2, 1);
  psUpdateBuy.setInt(3,1);
  int numUpdate = psUpdateBuy.executeUpdate();
  assertEquals("Should update one row",1, numUpdate);
}
 
Example 20
Source Project: TrakEM2   File: DBLoader.java    License: GNU General Public License v3.0 4 votes vote down vote up
/** The ImagePlus, if updated, is saved in the 'tiff_working' column always. */
private void updateInDatabase(Patch patch, String key) throws Exception {

	if (key.equals("tiff_snapshot")) {
		/* // DEPRECATED, now using mipmaps
		InputStream i_stream = null;
		try {
			ImagePlus imp = new ImagePlus("s", snaps.get(patch.getId())); // not calling fetchSnapshot because old code could end in a loop.
			if (null == imp) {
				Utils.log2("DBLoader: snapshot ImagePlus is null!");
				stmt_update_snap.setNull(1, java.sql.Types.BINARY);
			} else {
				i_stream = createZippedStream(imp);
				stmt_update_snap.setBinaryStream(1, i_stream, i_stream.available());
				flush(imp);
			}
			stmt_update_snap.setLong(2, patch.getId());
			stmt_update_snap.executeUpdate();
		} catch (Exception e) {
			IJError.print(e);
		} finally {
			if (null != i_stream) try { i_stream.close(); } catch (Exception e1) { IJError.print(e1); }
		}
		*/
		return;
	}

	StringBuffer sb = new StringBuffer("UPDATE ab_patches SET ");
	boolean update_imp = false;

	if (key.equals("tiff_working")) {
		sb.append("imp_type=").append(patch.getType())
		  .append(", tiff_working=?");
		update_imp = true;
	} else if (key.equals("remove_tiff_working")) {
		sb.append("tiff_working=NULL");
	} else if (key.equals("min_and_max")) {
		sb.append("min=").append(patch.getMin())
		  .append(", max=").append(patch.getMax());
	} else {
		// try the Displayable level
		updateInDatabase((Displayable)patch, key);
		return;
	}

	PreparedStatement st = connection.prepareStatement(sb.append(" WHERE id=").append(patch.getId()).toString());
	int i = 1;
	InputStream i_stream2 = null;
	try {
		if (update_imp) {
			ImagePlus imp = mawts.get(patch.getId()); // WARNING if the cache is very small relative to the size of the images, this strategy may fail
			i_stream2 = createZippedStream(imp);
			st.setBinaryStream(i, i_stream2, i_stream2.available());
			i++; // defensive programming: if later I add any other ..
		}

		st.executeUpdate();

		if (null != i_stream2) i_stream2.close();

	} catch (Exception e) {
		IJError.print(e);
		if (null != i_stream2) try { i_stream2.close(); } catch (Exception e2) { IJError.print(e2); }
	}
}