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

The following examples show how to use java.sql.PreparedStatement#setLong() . 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: ScoreDB.java    From opsu with GNU General Public License v3.0 6 votes vote down vote up
/**
 * Sets all statement fields using a given ScoreData object.
 * @param stmt the statement to set fields for
 * @param data the score data
 * @throws SQLException
 */
private static void setStatementFields(PreparedStatement stmt, ScoreData data)
		throws SQLException {
	stmt.setLong(1, data.timestamp);
	stmt.setInt(2, data.MID);
	stmt.setInt(3, data.MSID);
	stmt.setString(4, data.title);
	stmt.setString(5, data.artist);
	stmt.setString(6, data.creator);
	stmt.setString(7, data.version);
	stmt.setInt(8, data.hit300);
	stmt.setInt(9, data.hit100);
	stmt.setInt(10, data.hit50);
	stmt.setInt(11, data.geki);
	stmt.setInt(12, data.katu);
	stmt.setInt(13, data.miss);
	stmt.setLong(14, data.score);
	stmt.setInt(15, data.combo);
	stmt.setBoolean(16, data.perfect);
	stmt.setInt(17, data.mods);
}
 
Example 2
Source File: LargeStoreQueries.java    From Plan with GNU Lesser General Public License v3.0 6 votes vote down vote up
public static Executable storeAllSessionsWithoutKillOrWorldData(Collection<Session> sessions) {
    if (Verify.isEmpty(sessions)) {
        return Executable.empty();
    }

    return new ExecBatchStatement(SessionsTable.INSERT_STATEMENT) {
        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            for (Session session : sessions) {
                statement.setString(1, session.getUnsafe(SessionKeys.UUID).toString());
                statement.setLong(2, session.getUnsafe(SessionKeys.START));
                statement.setLong(3, session.getUnsafe(SessionKeys.END));
                statement.setInt(4, session.getValue(SessionKeys.DEATH_COUNT).orElse(0));
                statement.setInt(5, session.getValue(SessionKeys.MOB_KILL_COUNT).orElse(0));
                statement.setLong(6, session.getValue(SessionKeys.AFK_TIME).orElse(0L));
                statement.setString(7, session.getUnsafe(SessionKeys.SERVER_UUID).toString());
                statement.addBatch();
            }
        }
    };
}
 
Example 3
Source File: MetaDataClient.java    From phoenix with BSD 3-Clause "New" or "Revised" License 6 votes vote down vote up
private long incrementTableSeqNum(PTable table, PTableType expectedType, boolean isImmutableRows, boolean disableWAL, boolean isMultiTenant, int columnCountDelta) throws SQLException {
    String schemaName = table.getSchemaName().getString();
    String tableName = table.getTableName().getString();
    // Ordinal position is 1-based and we don't count SALT column in ordinal position
    int totalColumnCount = table.getColumns().size() + (table.getBucketNum() == null ? 0 : -1);
    final long seqNum = table.getSequenceNumber() + 1;
    PreparedStatement tableUpsert = connection.prepareStatement(MUTATE_TABLE);
    try {
        tableUpsert.setString(1, connection.getTenantId() == null ? null : connection.getTenantId().getString());
        tableUpsert.setString(2, schemaName);
        tableUpsert.setString(3, tableName);
        tableUpsert.setString(4, expectedType.getSerializedValue());
        tableUpsert.setLong(5, seqNum);
        tableUpsert.setInt(6, totalColumnCount + columnCountDelta);
        tableUpsert.setBoolean(7, isImmutableRows);
        tableUpsert.setBoolean(8, disableWAL);
        tableUpsert.setBoolean(9, isMultiTenant);
        tableUpsert.execute();
    } finally {
        tableUpsert.close();
    }
    return seqNum;
}
 
Example 4
Source File: ShardLimitOffsetTest.java    From Oceanus with Apache License 2.0 6 votes vote down vote up
@Test
public void testOrderByLimit3() throws SQLException {
	Connection conn = this.getConnection();
	ConnectionWrapper wrapper = new ConnectionWrapper(conn);

	String sql = "select * from t_userdynamic where uid>? or uid<? order by ver limit 4,?";
	PreparedStatement statement = wrapper.prepareStatement(sql);
	statement.setLong(1, 2000);
	statement.setLong(2, 1000);
	statement.setInt(3, 8);
	ResultSet result = statement.executeQuery();
	while (result.next()) {
		System.out.print("result:[");
		System.out.print(result.getString(1) + ",");
		System.out.print(result.getString(2) + ",");
		System.out.print(result.getString(3) + ",");
		System.out.println("]");
	}
}
 
Example 5
Source File: LobLimitsTest.java    From spliceengine with GNU Affero General Public License v3.0 6 votes vote down vote up
/**
 * insert blob
 * 
 * @param bloblen length of blob to insert
 * @param start start id value for insert
 * @param rows insert rows number of rows
 * @param streamLength stream length passed to setBinaryStream(,,length)
 */
private void insertBlob_SetBinaryStream(String testId, PreparedStatement ps, int bloblen, int start,
        int rows, int streamLength) throws SQLException {
    println("========================================");
    println("START " + testId + "insertBlob of size = "
            + bloblen);
    long ST = System.currentTimeMillis();

    int count = 0;
    java.util.Random random = new java.util.Random();
    for (int i = start; i < start + rows; i++) {
        ps.setInt(1, i);
        ps.setInt(2, 0);
        ps.setLong(3, bloblen);
        ps.setBinaryStream(4, new RandomByteStreamT(random, bloblen),
                streamLength);
        count += ps.executeUpdate();
    }
    commit();
    println("Insert Blob (" + bloblen + ")" + " rows= "
               + count + " = " + (long) (System.currentTimeMillis() - ST));
    verifyTest(count, rows, " Rows inserted with blob of size (" + bloblen
            + ") =");
    println("========================================");

}
 
Example 6
Source File: DBMessageRetry.java    From joyqueue with Apache License 2.0 5 votes vote down vote up
/**
 * 用于重试失败,获取下一次重试时间
 *
 * @param id
 * @param topic
 * @return
 * @throws JoyQueueException
 */
protected Long getNextRetryTime(long id, String topic, String app) throws JoyQueueException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    Long nextRetryTime = null; // 下一次重试时间
    try {
        connection = writeDataSource.getConnection();
        statement = connection.prepareStatement(QUERY_ENTITY_SQL);

        if (id <= 0) {
            return null;
        }

        statement.setLong(1, id);
        statement.setString(2, topic);
        resultSet = statement.executeQuery();

        if (resultSet.next()) {
            resultSet.getLong(1);  // 使用分区消息序号暂存主键ID
            Timestamp createTime = resultSet.getTimestamp(2);
            long startTime = createTime.getTime(); // 临时存放创建时间
            int retryCount = resultSet.getInt(3);

            nextRetryTime = retryPolicyProvider.getPolicy(TopicName.parse(topic), app).getTime(SystemClock.now(), retryCount, startTime);
        }
    } catch (Exception e) {
        throw new JoyQueueException(JoyQueueCode.CN_DB_ERROR, e);
    } finally {
        Close.close(connection, statement, resultSet);
    }

    return nextRetryTime;
}
 
Example 7
Source File: LobLimitsTest.java    From spliceengine with GNU Affero General Public License v3.0 5 votes vote down vote up
/**
 * insert clob into CLOBTBL2
 * 
 * @param cloblen length of clob to insert
 * @param start id value for insert
 * @param rows insert rows number of rows
 * @param streamLength stream length passed to
 *            setCharacterStream(pos,reader,streamLength)
 * @param file name of the file that has data to be inserted
 */
private void insertClob2(String testId,
        PreparedStatement ps, int cloblen, int start, int rows,
        int streamLength, String file) throws Exception {
    println("========================================");
    println("START " + testId + "insert Clob of size = "
            + cloblen);
    int count = 0;
    long ST = System.currentTimeMillis();
    for (int i = start; i < start + rows; i++) {
        FileReader reader = PrivilegedFileOpsForTests
                    .getFileReader(new File(file));
        try {
            println("Got reader for file " + file + " " + reader);
            ps.setInt(1, i);
            ps.setInt(2, 0);
            ps.setLong(4, cloblen);
            ps.setCharacterStream(3, reader, streamLength);
            count += ps.executeUpdate();
        } finally {
            reader.close();
            println("Closed reader for file " + file + " " + reader);
        }
    }
    commit();

    println("Insert Clob (" + cloblen + ")" + " rows= "
               + count + " = " + (long) (System.currentTimeMillis() - ST));
    verifyTest(count, rows, " Rows inserted with clob of size (" + cloblen
            + ") =");
    println("========================================");

}
 
Example 8
Source File: TPCETradeOrder.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
protected void getCustomer(long c_id) throws SQLException {
 PreparedStatement ps = conn.prepareStatement(selectCustomer);
  ps.setLong(1, c_id);
  ResultSet rs = ps.executeQuery();
  if (rs.next()) {
    customer = new Customer();
    customer.setCFName(rs.getString("C_F_NAME"));
    customer.setCLName(rs.getString("C_L_NAME"));
    customer.setCTier(rs.getShort("C_TIER"));
    customer.setCTaxId(rs.getString("C_TAX_ID"));
    customer.setCId(c_id);
    
    if (logDML) {
      Log.getLogWriter().info(selectCustomer + " gets C_F_NAME: " + customer.getCFName()
          + " C_L_NAME:" + customer.getCLName() + " C_TIER: " + customer.getCTier() 
          + " C_TAX_ID: " + customer.getCTaxId()
          + " for C_ID = " + c_id);
    }
    if (rs.next()) {
      if (logDML) {
        Log.getLogWriter().info(selectCustomer + " gets C_F_NAME: " + customer.getCFName()
            + " C_L_NAME:" + customer.getCLName() + " C_TIER: " + customer.getCTier() 
            + " C_TAX_ID: " + customer.getCTaxId()
            + " for C_ID = " + c_id);
      }
      throw new TestException ( selectCustomer+ " has more than 1 row " +
          "in result set for C_ID = " + c_id);
    }
  } else {
    throw new TestException ( selectCustomer + " does not get single row " +
        "in result set for C_ID = " + c_id);
  }
  rs.close();
}
 
Example 9
Source File: StorageService.java    From seata-samples with Apache License 2.0 5 votes vote down vote up
/**
 * 0.8.0 release
 *
 * @throws SQLException
 */
@GlobalTransactional
public void batchUpdate() throws SQLException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        connection = dataSource.getConnection();
        connection.setAutoCommit(false);
        String sql = "update storage_tbl set count = ?" +
            "    where id = ? and commodity_code = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, 100);
        preparedStatement.setLong(2, 1);
        preparedStatement.setString(3, "2001");
        preparedStatement.addBatch();
        preparedStatement.setInt(1, 200);
        preparedStatement.setLong(2, 2);
        preparedStatement.setString(3, "2002");
        preparedStatement.addBatch();
        preparedStatement.setInt(1, 300);
        preparedStatement.setLong(2, 3);
        preparedStatement.setString(3, "2003");
        preparedStatement.addBatch();
        preparedStatement.executeBatch();
        connection.commit();
        System.out.println(1 / 0);
    } catch (Exception e) {
        throw e;
    } finally {
        connection.close();
        preparedStatement.close();
    }
}
 
Example 10
Source File: DataStoreQueries.java    From Plan with GNU Lesser General Public License v3.0 5 votes vote down vote up
/**
 * Store a BaseUser for the player in the database.
 *
 * @param playerUUID UUID of the player.
 * @param registered Time the player registered on the server for the first time.
 * @param playerName Name of the player.
 * @return Executable, use inside a {@link com.djrapitops.plan.storage.database.transactions.Transaction}
 */
public static Executable registerBaseUser(UUID playerUUID, long registered, String playerName) {
    return new ExecStatement(UsersTable.INSERT_STATEMENT) {
        @Override
        public void prepare(PreparedStatement statement) throws SQLException {
            statement.setString(1, playerUUID.toString());
            statement.setString(2, playerName);
            statement.setLong(3, registered);
            statement.setInt(4, 0); // times kicked
        }
    };
}
 
Example 11
Source File: OLATUpgrade_4_1_0.java    From olat with Apache License 2.0 5 votes vote down vote up
private void cleanupUnusedMessageProperties(final UpgradeManager upgradeManager, final UpgradeHistoryData uhd) {
    // BEGIN MSG CLEAN UP
    // each message generates a property entry for each user if he read a
    // message in a forum. The deletion of the message did not delete the
    // property entry.
    if (!uhd.getBooleanDataValue(TASK_CLEAN_UP_MSGREAD_PROPERTIES_DONE)) {
        String query = "select o_property.id " + "from o_property LEFT JOIN o_message " + "ON o_property.longvalue=o_message.message_id "
                + "where o_message.message_id is NULL " + "AND o_property.category='rvst' " + "AND o_property.resourcetypename='Forum'; ";

        try {
            Connection con = upgradeManager.getDataSource().getConnection();
            final Statement stmt = con.createStatement();
            final ResultSet results = stmt.executeQuery(query);

            // delete each property and do logging
            query = "delete from o_property where id = ?";
            final PreparedStatement deleteStmt = con.prepareStatement(query);
            while (results.next()) {
                final long id = results.getLong("id");
                log.info("Audit:Deleting unused property (see: bugs.olat.org/jira/browse/OLAT-1273) from table (o_property) with id = " + id);
                deleteStmt.setLong(1, id);
                deleteStmt.execute();
            }

            con.close();
            con = null;
        } catch (final SQLException e) {
            log.warn("Could not execute system upgrade sql query. Query:" + query, e);
            throw new StartupException("Could not execute system upgrade sql query. Query:" + query, e);
        }
        uhd.setBooleanDataValue(TASK_CLEAN_UP_MSGREAD_PROPERTIES_DONE, true);
        upgradeManager.setUpgradesHistory(uhd, VERSION);
    }
}
 
Example 12
Source File: SiteUserDeviceDao.java    From openzaly with Apache License 2.0 5 votes vote down vote up
private int deleteSessionAsLimit(String siteUserId, int limitNum, long limitTime) throws SQLException {
	long startTime = System.currentTimeMillis();
	// 删除site_user_session中设备
	String sql = "DELETE FROM " + USER_SESSION_TABLE
			+ " WHERE site_user_id=? AND device_id NOT IN (SELECT s.device_id FROM (SELECT device_id FROM "
			+ USER_DEVICE_TABLE
			+ " WHERE site_user_id=? AND active_time>? ORDER BY active_time DESC LIMIT ?) as s);";

	int num = 0;
	Connection conn = null;
	PreparedStatement ps = null;
	try {
		conn = DatabaseConnection.getConnection();
		ps = conn.prepareStatement(sql);
		ps.setString(1, siteUserId);
		ps.setString(2, siteUserId);
		ps.setLong(3, limitTime);
		ps.setInt(4, limitNum);

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

	LogUtils.dbDebugLog(logger, startTime, num, sql, siteUserId);
	return num;
}
 
Example 13
Source File: IEngineImpl.java    From ramus with GNU General Public License v3.0 4 votes vote down vote up
@Override
public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
    if (cached) {
        Qualifier q = qualifeirsCache.get(rs.getLong("QUALIFIER_ID"));
        if (q != null)
            return q.createSaveCopy();
    }

    Qualifier qualifier = new Qualifier();
    fillFields(rs, qualifier);
    qualifier.setSystem(rs.getBoolean("QUALIFIER_SYSTEM"));

    long branch1 = 0l;

    if (rs.getObject("ATTRIBUTE_FOR_NAME") != null) {
        qualifier.setAttributeForName(rs.getLong("ATTRIBUTE_FOR_NAME"));
    }

    PreparedStatement ps = template
            .getPreparedStatement(
                    "SELECT * FROM "
                            + prefix
                            + "qualifiers_history qh WHERE qualifier_id=? AND created_branch_id IN (SELECT MAX(created_branch_id) FROM "
                            + prefix
                            + "qualifiers_history WHERE qualifier_id=qh.qualifier_id AND created_branch_id <=?)",
                    true);

    ps.setLong(1, qualifier.getId());
    ps.setLong(2, branch);

    ResultSet rs1 = ps.executeQuery();
    if (rs1.next()) {
        branch1 = rs1.getLong("created_branch_id");
        fillFields(rs1, qualifier);
    }

    rs1.close();

    loadAttributes(qualifier.getAttributes(), false, qualifier.getId(),
            branch1);
    loadAttributes(qualifier.getSystemAttributes(), true,
            qualifier.getId(), branch1);
    if (cached) {
        qualifeirsCache.put(qualifier.getId(), qualifier);
        return qualifier.createSaveCopy();
    } else
        return qualifier;
}
 
Example 14
Source File: FilterAccessSessionMySQL.java    From Cynthia with GNU General Public License v2.0 4 votes vote down vote up
public Filter queryFilter(UUID id)
{
	if(id == null)
	{
		return null;
	}
	Filter filter = null;

	PreparedStatement pstm = null;
	Connection conn = null;
	ResultSet rs = null;
	try
	{
		conn = DbPoolConnection.getInstance().getReadConnection();
		pstm = conn.prepareStatement("SELECT * FROM filter"
				+ " WHERE id = ?");
		pstm.setLong(1, Long.parseLong(id.getValue()));

		rs = pstm.executeQuery();
		if(rs.next())
		{
			String createUser = rs.getString("create_user");
			Timestamp createTime = rs.getTimestamp("create_time");
			UUID fatherId = null;
			if(rs.getObject("father_id") != null)
				fatherId = DataAccessFactory.getInstance().createUUID(rs.getObject("father_id").toString());

			filter = new FilterImpl(id, createUser, createTime, fatherId);
			filter.setName(rs.getString("name"));
			filter.setXml(rs.getString("xml"));
			filter.setAnd(rs.getBoolean("is_and"));
			filter.setPublic(rs.getBoolean("is_public"));
			filter.setVisible(rs.getBoolean("is_visible"));
		}
	}
	catch(Exception e)
	{
		e.printStackTrace();
	}
	finally
	{
		DbPoolConnection.getInstance().closeResultSet(rs);
		DbPoolConnection.getInstance().closeStatment(pstm);
		DbPoolConnection.getInstance().closeConn(conn);
	}

	return filter;
}
 
Example 15
Source File: EtDataSourceManager.java    From EasyTransaction with Apache License 2.0 4 votes vote down vote up
private void deleteUndoLog(String xid, long branchId, Connection conn) throws SQLException {
    PreparedStatement deletePST = conn.prepareStatement(DELETE_UNDO_LOG_SQL);
    deletePST.setLong(1, branchId);
    deletePST.setString(2, xid);
    deletePST.executeUpdate();
}
 
Example 16
Source File: ResourceManagerGuildImpl.java    From NovaGuilds with GNU General Public License v3.0 4 votes vote down vote up
@Override
public boolean save(NovaGuild guild) {
	if(!guild.isChanged() && !isInSaveQueue(guild) || guild.isUnloaded() || isInRemovalQueue(guild)) {
		return false;
	}

	if(!guild.isAdded()) {
		add(guild);
		return true;
	}

	getStorage().connect();

	try {
		String homeCoordinates = StringUtils.parseDBLocation(guild.getHome());
		String vaultLocationString = StringUtils.parseDBLocation(guild.getVaultLocation());
		IConverter<NovaGuild, UUID> cvt = new ResourceToUUIDConverterImpl<>();

		PreparedStatement preparedStatement = getStorage().getPreparedStatement(PreparedStatements.GUILDS_UPDATE);

		preparedStatement.setString( 1,  guild.getTag());                                                      //tag
		preparedStatement.setString( 2,  guild.getName());                                                     //name
		preparedStatement.setString( 3,  guild.getLeader().getUUID().toString());                              //leader uuid
		preparedStatement.setString( 4,  homeCoordinates);                                                     //home location
		preparedStatement.setString( 5,  StringUtils.joinSemicolon(cvt.convert(guild.getAllies())));           //allies
		preparedStatement.setString( 6,  StringUtils.joinSemicolon(cvt.convert(guild.getAllyInvitations())));  //ally invitations
		preparedStatement.setString( 7,  StringUtils.joinSemicolon(cvt.convert(guild.getWars())));             //wars
		preparedStatement.setString( 8,  StringUtils.joinSemicolon(cvt.convert(guild.getNoWarInvitations()))); //no war invitations
		preparedStatement.setDouble( 9,  guild.getMoney());                                                    //money
		preparedStatement.setInt(    10, guild.getPoints());                                                   //points
		preparedStatement.setInt(    11, guild.getLives());                                                    //lives amount
		preparedStatement.setLong(   12, guild.getTimeRest());                                                 //rest time
		preparedStatement.setLong(   13, guild.getLostLiveTime());                                             //lost live time
		preparedStatement.setLong(   14, guild.getInactiveTime());                                             //inactive time
		preparedStatement.setString( 15, vaultLocationString);                                                 //vault location
		preparedStatement.setInt(    16, guild.getSlots());                                                    //slots
		preparedStatement.setBoolean(17, guild.isOpenInvitation());                                            //open invitation
		preparedStatement.setString( 18, BannerUtils.serialize(guild.getBannerMeta()));                        //banner

		preparedStatement.setString( 19, guild.getUUID().toString());                                          //guild UUID

		preparedStatement.executeUpdate();
		guild.setUnchanged();
	}
	catch(SQLException e) {
		LoggerUtils.info("SQLException while saving a guild.");
		LoggerUtils.exception(e);
	}

	return true;
}
 
Example 17
Source File: PhoenixTracingEndToEndIT.java    From phoenix with Apache License 2.0 4 votes vote down vote up
@Test
public void testScanTracingOnServer() throws Exception {

    LOGGER.info("testScanTracingOnServer TableName: " + tracingTableName);

    // separate connections to minimize amount of traces that are generated
    Connection traceable = getTracingConnection();
    Connection conn = getConnectionWithoutTracing();

    // one call for client side, one call for server side
    latch = new CountDownLatch(5);
    testTraceWriter.start();

    // create a dummy table
    createTestTable(conn, false);

    // update the table, but don't trace these, to simplify the traces we read
    LOGGER.debug("Doing dummy the writes to the tracked table");
    String insert = "UPSERT INTO " + enabledForLoggingTable + " VALUES (?, ?)";
    PreparedStatement stmt = conn.prepareStatement(insert);
    stmt.setString(1, "key1");
    stmt.setLong(2, 1);
    stmt.execute();
    conn.commit();

    // setup for next set of updates
    stmt.setString(1, "key2");
    stmt.setLong(2, 2);
    stmt.execute();
    conn.commit();

    // do a scan of the table
    String read = "SELECT COUNT(*) FROM " + enabledForLoggingTable;
    ResultSet results = traceable.createStatement().executeQuery(read);
    assertTrue("Didn't get count result", results.next());
    // make sure we got the expected count
    assertEquals("Didn't get the expected number of row", 2, results.getInt(1));
    results.close();

    assertTrue("Didn't get expected updates to trace table", latch.await(60, TimeUnit.SECONDS));

    // don't trace reads either
    boolean found = checkStoredTraces(conn, new TraceChecker() {
        @Override
        public boolean foundTrace(TraceHolder trace) {
            String traceInfo = trace.toString();
            return traceInfo.contains(BaseScannerRegionObserver.SCANNER_OPENED_TRACE_INFO);
        }
    });
    assertTrue("Didn't find the parallel scanner in the tracing", found);
}
 
Example 18
Source File: DBTest.java    From canal-1.1.3 with Apache License 2.0 4 votes vote down vote up
@Test
public void test01() throws SQLException {
    DruidDataSource dataSource = new DruidDataSource();
    // dataSource.setDriverClassName("oracle.jdbc.OracleDriver");
    // dataSource.setUrl("jdbc:oracle:thin:@127.0.0.1:49161:XE");
    // dataSource.setUsername("mytest");
    // dataSource.setPassword("m121212");

    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/mytest?useUnicode=true");
    dataSource.setUsername("root");
    dataSource.setPassword("121212");

    dataSource.setInitialSize(1);
    dataSource.setMinIdle(1);
    dataSource.setMaxActive(2);
    dataSource.setMaxWait(60000);
    dataSource.setTimeBetweenEvictionRunsMillis(60000);
    dataSource.setMinEvictableIdleTimeMillis(300000);

    dataSource.init();

    Connection conn = dataSource.getConnection();

    conn.setAutoCommit(false);
    PreparedStatement pstmt = conn
        .prepareStatement("insert into user (id,name,role_id,c_time,test1,test2) values (?,?,?,?,?,?)");

    java.util.Date now = new java.util.Date();
    for (int i = 1; i <= 10000; i++) {
        pstmt.clearParameters();
        pstmt.setLong(1, (long) i);
        pstmt.setString(2, "test_" + i);
        pstmt.setLong(3, (long) i % 4 + 1);
        pstmt.setDate(4, new java.sql.Date(now.getTime()));
        pstmt.setString(5, null);
        pstmt.setBytes(6, null);

        pstmt.execute();
        if (i % 5000 == 0) {
            conn.commit();
        }
    }
    conn.commit();

    pstmt.close();

    // Statement stmt = conn.createStatement();
    // ResultSet rs = stmt.executeQuery("select * from user t where 1=2");
    //
    // ResultSetMetaData rsm = rs.getMetaData();
    // int cnt = rsm.getColumnCount();
    // for (int i = 1; i <= cnt; i++) {
    // System.out.println(rsm.getColumnName(i) + " " + rsm.getColumnType(i));
    // }

    // rs.close();
    // stmt.close();

    // PreparedStatement pstmt = conn
    // .prepareStatement("insert into tb_user (id,name,role_id,c_time,test1,test2)
    // values (?,?,?,?,?,?)");
    // pstmt.setBigDecimal(1, new BigDecimal("5"));
    // pstmt.setString(2, "test");
    // pstmt.setBigDecimal(3, new BigDecimal("1"));
    // pstmt.setDate(4, new Date(new java.util.Date().getTime()));
    // byte[] a = { (byte) 1, (byte) 2 };
    // pstmt.setBytes(5, a);
    // pstmt.setBytes(6, a);
    // pstmt.execute();
    //
    // pstmt.close();

    conn.close();
    dataSource.close();
}
 
Example 19
Source File: LongType.java    From cacheonix-core with GNU Lesser General Public License v2.1 4 votes vote down vote up
public void set(PreparedStatement st, Object value, int index)
throws SQLException {

	st.setLong( index, ( (Long) value ).longValue() );
}
 
Example 20
Source File: TradeNetworthV1DMLDistTxStmt.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
protected int insertToTable(PreparedStatement stmt, long cid, BigDecimal cash,
    BigDecimal securities, int loanLimit, BigDecimal availLoan, int tid, boolean isPut)
    throws SQLException {
  String txid =  SQLDistTxTest.curTxId.get() == null ? "" : "TXID:" + (Integer)SQLDistTxTest.curTxId.get() + " "; 
  
  int round = (Integer) SQLDistTxTest.iteration.get();
  
  String database = SQLHelper.isDerbyConn(stmt.getConnection())?"Derby - " :"gemfirexd - " + txid + " " ;
  Log.getLogWriter().info( database + (isPut ? "putting" : "inserting") + " into trade.networthv1 with CID:"
      + cid + ",CASH:" + cash + ":SECURITIES," + securities
      + ",LOANLIMIT:" + loanLimit + ",AVAILLOAN:" + availLoan
      + ",TID:" + tid + ",ROUND:" + round+ ",C_CID:" + cid);
  stmt.setLong(1, cid);
  stmt.setBigDecimal(2, cash);
  stmt.setBigDecimal(3, securities);  //insert is 0, will be updated by security through trigger
  stmt.setInt(4, loanLimit); 
  stmt.setBigDecimal(5, availLoan);   //availLoan is the same as loanLimit during insert
  stmt.setInt(6, tid);
  stmt.setInt(7, round);
  stmt.setLong(8, cid);
  int rowCount = stmt.executeUpdate();
  Log.getLogWriter().info(database + (isPut ? "put" : "inserted ") + rowCount + " rows in trade.networthv1 with CID:"
      + cid + ",CASH:" + cash + ":SECURITIES," + securities
      + ",LOANLIMIT:" + loanLimit + ",AVAILLOAN:" + availLoan
      + ",TID:" + tid + ",ROUND:" + round+ ",C_CID:" + cid);
  
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning   
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 
  /* tables needs to be changed in hdfs 
  if ( database.contains("gemfirexd") && isPut) {
    if (! SQLTest.ticket49794fixed) {
      //manually update fulldataset table for above entry.
        String deleteStatement = "DELETE FROM TRADE.NETWORTH_FULLDATASET  WHERE  cid = "  + cid ;
        String insertStatement = " INSERT INTO TRADE.NETWORTH_FULLDATASET  VALUES ( " + cid + " ,  " +   cash  + " ,  " + securities + "," +  loanLimit  + " ,  " +  availLoan + "," +   tid +  ")";
        Log.getLogWriter().info(" Trigger behaviour is not defined for putDML hence deleting  the  row  from TRADE.NETWORTH_FULLDATASET with data CID:" +  cid );
        stmt.getConnection().createStatement().execute(deleteStatement);
        Log.getLogWriter().info(" Trigger behaviour is not defined for putDML hence inserting  the  row  into  TRADE.NETWORTH_FULLDATASET with data CID:" +  cid +  ",CASH:" + cash  + ",SECURITIES:" +  securities + " ,LOANLIMIT:" + loanLimit + " ,AVAILLOAN:" + availLoan + ",TID:" + tid );
        stmt.getConnection().createStatement().execute(insertStatement);
      }
       Log.getLogWriter().info( database + (isPut ? "putting" : "inserting") + " into trade.networth with CID:"
        + cid + ",CASH:" + cash + ":SECURITIES," + securities
        + ",LOANLIMIT:" + loanLimit + ",AVAILLOAN:" + availLoan
        + ",TID:" + tid);
      rowCount = stmt.executeUpdate();
      Log.getLogWriter().info(database + (isPut ? "put" : "inserted ") + rowCount + " rows in trade.networth CID:"
          + cid + ",CASH:" + cash + ":SECURITIES," + securities
          + ",LOANLIMIT:" + loanLimit + ",AVAILLOAN:" + availLoan
          + ",TID:" + tid);
      warning = stmt.getWarnings(); //test to see there is a warning   
      if (warning != null) {
        SQLHelper.printSQLWarning(warning);
      } 
  }  
  */  
  return rowCount;
}