use of java.sql.CallableStatement in project zaproxy by zaproxy.
the class HsqldbDatabaseServer method shutdown.
@Override
void shutdown(boolean compact) throws SQLException {
super.shutdown(compact);
Connection conn = getNewConnection();
CallableStatement psCompact = null;
if (compact) {
// db is not new and useful for future. Compact it.
psCompact = conn.prepareCall("SHUTDOWN COMPACT");
} else {
// new need to compact database. just shutdown.
psCompact = conn.prepareCall("SHUTDOWN");
}
psCompact.execute();
psCompact.close();
conn.close();
}
use of java.sql.CallableStatement in project voltdb by VoltDB.
the class JDBCBenchmark method main.
// Application entry point
public static void main(String[] args) {
try {
KVConfig config = new KVConfig();
config.parse(JDBCBenchmark.class.getName(), args);
System.out.println(config.getConfigDumpString());
// ---------------------------------------------------------------------------------------------------------------------------------------------------
// We need only do this once, to "hot cache" the JDBC driver reference so the JVM may realize it's there.
Class.forName(DRIVER_NAME);
// Prepare the JDBC URL for the VoltDB driver
String url = "jdbc:voltdb://" + config.servers;
// Prepare the Datasource if choose to use a connection pool
if (config.externalConnectionPool.equalsIgnoreCase(C3P0_CONNECTIONPOOL)) {
useConnectionPool = true;
ComboPooledDataSource cpds = new ComboPooledDataSource();
//loads the jdbc driver
cpds.setDriverClass(DRIVER_NAME);
cpds.setJdbcUrl(url);
Ds = cpds;
} else if (config.externalConnectionPool.equalsIgnoreCase(TOMCAT_CONNECTIONPOOL)) {
useConnectionPool = true;
// read the config file for connection pool
String configName = "tomcat.properties";
boolean useDefaultConnectionPoolConfig = true;
Properties cpProperties = new Properties();
try {
FileInputStream fileInput = new FileInputStream(new File(configName));
cpProperties.load(fileInput);
fileInput.close();
useDefaultConnectionPoolConfig = false;
} catch (FileNotFoundException e) {
System.out.println("connection pool property file '" + configName + "' not found, use default settings");
}
PoolProperties p = new PoolProperties();
p.setUrl(url);
p.setDriverClassName(DRIVER_NAME);
if (useDefaultConnectionPoolConfig) {
p.setInitialSize(config.threads + 1);
} else {
p.setInitialSize(Integer.parseInt(cpProperties.getProperty("tomcat.initialSize", "40")));
}
org.apache.tomcat.jdbc.pool.DataSource tomcatDs = new org.apache.tomcat.jdbc.pool.DataSource();
tomcatDs.setPoolProperties(p);
Ds = tomcatDs;
} else if (config.externalConnectionPool.equalsIgnoreCase(BONECP_CONNECTIONPOOL)) {
useConnectionPool = true;
String configName = "bonecp.properties";
boolean useDefaultConnectionPoolConfig = true;
Properties cpProperties = new Properties();
try {
FileInputStream fileInput = new FileInputStream(new File(configName));
cpProperties.load(fileInput);
fileInput.close();
useDefaultConnectionPoolConfig = false;
} catch (FileNotFoundException e) {
System.out.println("connection pool property file '" + configName + "' not found, use default settings");
}
BoneCPConfig p;
if (useDefaultConnectionPoolConfig) {
p = new BoneCPConfig();
p.setDefaultReadOnly(false);
p.setPartitionCount(config.threads / 2);
p.setMaxConnectionsPerPartition(4);
} else {
p = new BoneCPConfig(cpProperties);
}
// set the JDBC url
p.setJdbcUrl(url + "?enableSetReadOnly=true");
BoneCPDataSource boneDs = new BoneCPDataSource(p);
Ds = boneDs;
} else if (config.externalConnectionPool.equalsIgnoreCase(HIKARI_CONNECTIONPOOL)) {
useConnectionPool = true;
HikariConfig p = new HikariConfig("hikari.properties");
p.setDriverClassName(DRIVER_NAME);
p.setJdbcUrl(url);
HikariDataSource hiDs = new HikariDataSource(p);
Ds = hiDs;
} else {
useConnectionPool = false;
Ds = null;
}
// Get a client connection - we retry for a while in case the server hasn't started yet
System.out.printf("Connecting to: %s\n", url);
int sleep = 1000;
while (true) {
try {
if (useConnectionPool) {
Ds.getConnection();
System.out.printf("Using Connection Pool: %s\n", config.externalConnectionPool);
}
Con = DriverManager.getConnection(url, "", "");
break;
} catch (Exception e) {
System.err.printf("Connection failed - retrying in %d second(s).\n " + e, sleep / 1000);
try {
Thread.sleep(sleep);
} catch (Exception tie) {
}
if (sleep < 8000)
sleep += sleep;
}
}
// Statistics manager objects from the connection, used to generate latency histogram
ClientStatsContext fullStatsContext = ((IVoltDBConnection) Con).createStatsContext();
periodicStatsContext = ((IVoltDBConnection) Con).createStatsContext();
System.out.println("Connected. Starting benchmark.");
// Get a payload generator to create random Key-Value pairs to store in the database and process (uncompress) pairs retrieved from the database.
final PayloadProcessor processor = new PayloadProcessor(config.keysize, config.minvaluesize, config.maxvaluesize, config.entropy, config.poolsize, config.usecompression);
// Initialize the store
if (config.preload) {
System.out.print("Initializing data store... ");
final PreparedStatement removeCS = Con.prepareStatement("DELETE FROM store;");
final CallableStatement putCS = Con.prepareCall("{call STORE.upsert(?,?)}");
for (int i = 0; i < config.poolsize; i++) {
if (i == 0) {
removeCS.execute();
}
putCS.setString(1, String.format(processor.KeyFormat, i));
putCS.setBytes(2, processor.generateForStore().getStoreValue());
putCS.execute();
}
System.out.println(" Done.");
}
// start the stats
fullStatsContext.fetchAndResetBaseline();
periodicStatsContext.fetchAndResetBaseline();
benchmarkStartTS = System.currentTimeMillis();
// ---------------------------------------------------------------------------------------------------------------------------------------------------
// Create a Timer task to display performance data on the operating procedures
Timer timer = new Timer();
TimerTask statsPrinting = new TimerTask() {
@Override
public void run() {
printStatistics();
}
};
timer.scheduleAtFixedRate(statsPrinting, config.displayinterval * 1000l, config.displayinterval * 1000l);
// ---------------------------------------------------------------------------------------------------------------------------------------------------
// Create multiple processing threads
ArrayList<Thread> threads = new ArrayList<Thread>();
for (int i = 0; i < config.threads; i++) threads.add(new Thread(new ClientThread(url, processor, config.duration, config.getputratio)));
// Start threads
for (Thread thread : threads) thread.start();
// Wait for threads to complete
for (Thread thread : threads) thread.join();
// ---------------------------------------------------------------------------------------------------------------------------------------------------
// We're done - stop the performance statistics display task
timer.cancel();
// ---------------------------------------------------------------------------------------------------------------------------------------------------
// Now print application results:
// stop and fetch the stats
ClientStats stats = fullStatsContext.fetch().getStats();
// 1. Store statistics as tracked by the application (ops counts, payload traffic)
System.out.printf("\n-------------------------------------------------------------------------------------\n" + " Store Results\n" + "-------------------------------------------------------------------------------------\n\n" + "A total of %,d operations was posted...\n" + " - GETs: %,9d Operations (%,9d Misses/Failures)\n" + " %,9d MB in compressed store data\n" + " %,9d MB in uncompressed application data\n" + " Network Throughput: %6.3f Gbps*\n\n" + " - PUTs: %,9d Operations (%,9d Failures)\n" + " %,9d MB in compressed store data\n" + " %,9d MB in uncompressed application data\n" + " Network Throughput: %6.3f Gbps*\n\n" + " - Total Network Throughput: %6.3f Gbps*\n\n" + "* Figure includes key & value traffic but not database protocol overhead.\n" + "\n" + "-------------------------------------------------------------------------------------\n", GetStoreResults.get(0) + GetStoreResults.get(1) + PutStoreResults.get(0) + PutStoreResults.get(1), GetStoreResults.get(0), GetStoreResults.get(1), GetCompressionResults.get(0) / 1048576l, GetCompressionResults.get(1) / 1048576l, ((double) GetCompressionResults.get(0) + (GetStoreResults.get(0) + GetStoreResults.get(1)) * config.keysize) / (134217728d * config.duration), PutStoreResults.get(0), PutStoreResults.get(1), PutCompressionResults.get(0) / 1048576l, PutCompressionResults.get(1) / 1048576l, ((double) PutCompressionResults.get(0) + (PutStoreResults.get(0) + PutStoreResults.get(1)) * config.keysize) / (134217728d * config.duration), ((double) GetCompressionResults.get(0) + (GetStoreResults.get(0) + GetStoreResults.get(1)) * config.keysize) / (134217728d * config.duration) + ((double) PutCompressionResults.get(0) + (PutStoreResults.get(0) + PutStoreResults.get(1)) * config.keysize) / (134217728d * config.duration));
System.out.println("\n\n-------------------------------------------------------------------------------------\n" + " Client Latency Statistics\n" + "-------------------------------------------------------------------------------------\n\n");
System.out.printf("Average latency: %,9.2f ms\n", stats.getAverageLatency());
System.out.printf("10th percentile latency: %,9.2f ms\n", stats.kPercentileLatencyAsDouble(.1));
System.out.printf("25th percentile latency: %,9.2f ms\n", stats.kPercentileLatencyAsDouble(.25));
System.out.printf("50th percentile latency: %,9.2f ms\n", stats.kPercentileLatencyAsDouble(.5));
System.out.printf("75th percentile latency: %,9.2f ms\n", stats.kPercentileLatencyAsDouble(.75));
System.out.printf("90th percentile latency: %,9.2f ms\n", stats.kPercentileLatencyAsDouble(.9));
System.out.printf("95th percentile latency: %,9.2f ms\n", stats.kPercentileLatencyAsDouble(.95));
System.out.printf("99th percentile latency: %,9.2f ms\n", stats.kPercentileLatencyAsDouble(.99));
System.out.printf("99.5th percentile latency: %,9.2f ms\n", stats.kPercentileLatencyAsDouble(.995));
System.out.printf("99.9th percentile latency: %,9.2f ms\n", stats.kPercentileLatencyAsDouble(.999));
System.out.println("\n\n" + stats.latencyHistoReport());
// Dump statistics to a CSV file
Con.unwrap(IVoltDBConnection.class).saveStatistics(stats, config.statsfile);
Con.close();
// ---------------------------------------------------------------------------------------------------------------------------------------------------
} catch (Exception x) {
System.out.println("Exception: " + x);
x.printStackTrace();
}
}
use of java.sql.CallableStatement in project voltdb by VoltDB.
the class JDBCBenchmark method runBenchmark.
/**
* Core benchmark code. Connect. Initialize. Run the loop. Cleanup. Print
* Results.
*
* @throws Exception
* if anything unexpected happens.
*/
public void runBenchmark() throws Exception {
System.out.print(HORIZONTAL_RULE);
System.out.println(" Setup & Initialization");
System.out.println(HORIZONTAL_RULE);
// connect to one or more servers, loop until success
connect(config.servers);
// initialize using synchronous call
// Initialize the application
System.out.println("\nPopulating Static Tables\n");
final CallableStatement initializeCS = client.prepareCall("{call Initialize(?,?)}");
initializeCS.setInt(1, config.contestants);
initializeCS.setString(2, CONTESTANT_NAMES_CSV);
initializeCS.executeUpdate();
System.out.print(HORIZONTAL_RULE);
System.out.println("Starting Benchmark");
System.out.println(HORIZONTAL_RULE);
// create/start the requested number of threads
Thread[] voterThreads = new Thread[config.threads];
for (int i = 0; i < config.threads; ++i) {
voterThreads[i] = new Thread(new VoterThread());
voterThreads[i].start();
}
// Run the benchmark loop for the requested warmup time
System.out.println("Warming up...");
Thread.sleep(1000l * config.warmup);
// signal to threads to end the warmup phase
warmupComplete.set(true);
// reset the stats after warmup
fullStatsContext.fetchAndResetBaseline();
periodicStatsContext.fetchAndResetBaseline();
// print periodic statistics to the console
benchmarkStartTS = System.currentTimeMillis();
schedulePeriodicStats();
// Run the benchmark loop for the requested warmup time
System.out.println("\nRunning benchmark...");
Thread.sleep(1000l * config.duration);
// stop the threads
benchmarkComplete.set(true);
// cancel periodic stats printing
timer.cancel();
// join on the threads
for (Thread t : voterThreads) {
t.join();
}
// print the summary results
printResults();
// close down the client connections
client.close();
}
use of java.sql.CallableStatement in project voltdb by VoltDB.
the class JDBCBenchmark method printResults.
/**
* Prints the results of the voting simulation and statistics about
* performance.
*
* @throws Exception
* if anything unexpected happens.
*/
public synchronized void printResults() throws Exception {
ClientStats stats = fullStatsContext.fetch().getStats();
// 1. Voting Board statistics, Voting results and performance statistics
String display = "\n" + HORIZONTAL_RULE + " Voting Results\n" + HORIZONTAL_RULE + "\nA total of %d votes were received...\n" + " - %,9d Accepted\n" + " - %,9d Rejected (Invalid Contestant)\n" + " - %,9d Rejected (Maximum Vote Count Reached)\n" + " - %,9d Failed (Transaction Error)\n\n";
System.out.printf(display, stats.getInvocationsCompleted(), acceptedVotes.get(), badContestantVotes.get(), badVoteCountVotes.get(), failedVotes.get());
// 2. Voting results
final CallableStatement resultsCS = client.prepareCall("{call Results}");
ResultSet result = resultsCS.executeQuery();
String winner = "";
long winnerVoteCount = 0;
System.out.println("Contestant Name\t\tVotes Received");
while (result.next()) {
if (result.getLong(3) > winnerVoteCount) {
winnerVoteCount = result.getLong(3);
winner = result.getString(1);
}
System.out.printf("%s\t\t%,14d\n", result.getString(1), result.getLong(3));
}
System.out.printf("\nThe Winner is: %s\n\n", winner);
// 3. Performance statistics
System.out.print(HORIZONTAL_RULE);
System.out.println(" Client Workload Statistics");
System.out.println(HORIZONTAL_RULE);
System.out.printf("Average throughput: %,9d txns/sec\n", stats.getTxnThroughput());
System.out.printf("Average latency: %,9.2f ms\n", stats.getAverageLatency());
System.out.printf("95th percentile latency: %,9.2f ms\n", stats.kPercentileLatencyAsDouble(.95));
System.out.printf("99th percentile latency: %,9.2f ms\n", stats.kPercentileLatencyAsDouble(.99));
System.out.print("\n" + HORIZONTAL_RULE);
System.out.println(" System Server Statistics");
System.out.println(HORIZONTAL_RULE);
System.out.printf("Reported Internal Avg Latency: %,9.2f ms\n", stats.getAverageInternalLatency());
// 4. Write stats to file if requested
((IVoltDBConnection) client).writeSummaryCSV(stats, config.statsfile);
}
use of java.sql.CallableStatement in project voltdb by VoltDB.
the class TestJDBCDriver method testInsertNulls.
// Check that the null type is handled the same way as specifying the correct type
// this is for spring framework compatibility
@Test
public void testInsertNulls() throws SQLException {
// First inserted row contains all null values (this will cause VoltType.NULL_STRING_OR_VARBINARY to be inserted in every field)
CallableStatement cs = conn.prepareCall("{call InsertAllTypes(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
cs.setInt(1, 0);
cs.setNull(2, Types.NULL);
cs.setNull(3, Types.NULL);
cs.setNull(4, Types.NULL);
cs.setNull(5, Types.NULL);
cs.setNull(6, Types.NULL);
cs.setNull(7, Types.NULL);
cs.setNull(8, Types.NULL);
cs.setNull(9, Types.NULL);
cs.setNull(10, Types.NULL);
cs.setNull(11, Types.NULL);
cs.setNull(12, Types.NULL);
cs.execute();
// Second inserted row contains the specific type of the field causing the typed nulls to be inserted
cs = conn.prepareCall("{call InsertAllTypes(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}");
cs.setInt(1, 1);
cs.setNull(2, Types.TINYINT);
cs.setNull(3, Types.SMALLINT);
cs.setNull(4, Types.INTEGER);
cs.setNull(5, Types.BIGINT);
cs.setNull(6, Types.DOUBLE);
cs.setNull(7, Types.VARCHAR);
cs.setNull(8, Types.VARBINARY);
cs.setNull(9, Types.TIMESTAMP);
cs.setNull(10, Types.DECIMAL);
cs.setNull(11, Types.OTHER);
cs.setNull(12, Types.OTHER);
cs.execute();
// Call SelectC (select * from ALL_TYPES)
cs = conn.prepareCall("{call SelectC}");
ResultSet results = cs.executeQuery();
// Retrieve the values for the first row
results.next();
byte a2 = results.getByte(2);
short a3 = results.getShort(3);
int a4 = results.getInt(4);
long a5 = results.getLong(5);
double a6 = results.getDouble(6);
String a7 = results.getString(7);
byte[] a8 = results.getBytes(8);
Timestamp a9 = results.getTimestamp(9);
BigDecimal a10 = results.getBigDecimal(10);
// Compare the second row values with the first row
results.next();
assertEquals(results.getByte(2), a2);
assertEquals(results.getShort(3), a3);
assertEquals(results.getInt(4), a4);
assertEquals(results.getLong(5), a5);
assertEquals(results.getDouble(6), a6, 0);
assertEquals(results.getString(7), a7);
assertEquals(results.getBytes(8), a8);
assertEquals(results.getTimestamp(9), a9);
assertEquals(results.getBigDecimal(10), a10);
}
Aggregations