1 /* -*- mode: java; c-basic-offset: 4; indent-tabs-mode: nil; -*- 2 * vim:expandtab:shiftwidth=4:tabstop=4:smarttab: 3 * 4 * Copyright (c) 2010, Oracle and/or its affiliates. All rights reserved. 5 * 6 * This program is free software; you can redistribute it and/or modify 7 * it under the terms of the GNU General Public License, version 2.0, 8 * as published by the Free Software Foundation. 9 * 10 * This program is also distributed with certain software (including 11 * but not limited to OpenSSL) that is licensed under separate terms, 12 * as designated in a particular file or component or in included license 13 * documentation. The authors of MySQL hereby grant you an additional 14 * permission to link the program and your derivative works with the 15 * separately licensed software that they have included with MySQL. 16 * 17 * This program is distributed in the hope that it will be useful, 18 * but WITHOUT ANY WARRANTY; without even the implied warranty of 19 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 20 * GNU General Public License, version 2.0, for more details. 21 * 22 * You should have received a copy of the GNU General Public License 23 * along with this program; if not, write to the Free Software 24 * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA 25 */ 26 27 package com.mysql.cluster.benchmark.tws; 28 29 import java.sql.Connection; 30 import java.sql.DriverManager; 31 import java.sql.SQLException; 32 import java.sql.PreparedStatement; 33 import java.sql.ResultSet; 34 35 class JdbcLoad extends TwsLoad { 36 37 // JDBC settings 38 protected String jdbcDriver; 39 protected String url; 40 protected String username; 41 protected String password; 42 43 // JDBC resources 44 protected Class jdbcDriverClass; 45 protected Connection connection; 46 protected PreparedStatement ins0; 47 protected PreparedStatement sel0; 48 protected PreparedStatement upd0; 49 protected PreparedStatement del0; 50 protected PreparedStatement delAll; 51 JdbcLoad(TwsDriver driver, MetaData md)52 public JdbcLoad(TwsDriver driver, MetaData md) { 53 super(driver, md); 54 } 55 56 // ---------------------------------------------------------------------- 57 // JDBC intializers/finalizers 58 // ---------------------------------------------------------------------- 59 initProperties()60 protected void initProperties() { 61 out.println(); 62 out.print("setting jdbc properties ..."); 63 64 final StringBuilder msg = new StringBuilder(); 65 final String eol = System.getProperty("line.separator"); 66 67 // load the JDBC driver class 68 jdbcDriver = driver.props.getProperty("jdbc.driver"); 69 if (jdbcDriver == null) { 70 throw new RuntimeException("Missing property: jdbc.driver"); 71 } 72 try { 73 Class.forName(jdbcDriver); 74 } catch (ClassNotFoundException e) { 75 out.println("Cannot load JDBC driver '" + jdbcDriver 76 + "' from classpath '" 77 + System.getProperty("java.class.path") + "'"); 78 throw new RuntimeException(e); 79 } 80 81 url = driver.props.getProperty("jdbc.url"); 82 if (url == null) { 83 throw new RuntimeException("Missing property: jdbc.url"); 84 } 85 86 username = driver.props.getProperty("jdbc.user"); 87 password = driver.props.getProperty("jdbc.password"); 88 89 if (msg.length() == 0) { 90 out.println(" [ok]"); 91 } else { 92 out.println(); 93 out.print(msg.toString()); 94 } 95 96 // have url initialized first 97 descr = "jdbc(" + url + ")"; 98 } 99 printProperties()100 protected void printProperties() { 101 out.println("jdbc.driver: " + jdbcDriver); 102 out.println("jdbc.url: " + url); 103 out.println("jdbc.user: \"" + username + "\""); 104 out.println("jdbc.password: \"" + password + "\""); 105 } 106 init()107 public void init() throws Exception { 108 super.init(); 109 assert (jdbcDriverClass == null); 110 111 // load the JDBC driver class 112 out.print("loading jdbc driver ..."); 113 out.flush(); 114 try { 115 jdbcDriverClass = Class.forName(jdbcDriver); 116 } catch (ClassNotFoundException e) { 117 out.println("Cannot load JDBC driver '" + jdbcDriver 118 + "' from classpath '" 119 + System.getProperty("java.class.path") + "'"); 120 throw new RuntimeException(e); 121 } 122 out.println(" [ok: " + jdbcDriverClass.getName() + "]"); 123 } 124 close()125 public void close() throws Exception { 126 assert (jdbcDriverClass != null); 127 128 //out.println(); 129 jdbcDriverClass = null; 130 131 super.close(); 132 } 133 134 // ---------------------------------------------------------------------- 135 // JDBC datastore operations 136 // ---------------------------------------------------------------------- 137 initConnection()138 public void initConnection() throws SQLException { 139 assert (jdbcDriverClass != null); 140 assert (connection == null); 141 142 out.println(); 143 out.println("initializing jdbc resources ..."); 144 145 // create a connection to the database 146 out.print("starting jdbc connection ..."); 147 out.flush(); 148 try { 149 connection = DriverManager.getConnection(url, username, password); 150 } catch (SQLException e) { 151 out.println("Cannot connect to database '" + url + "'"); 152 throw new RuntimeException(e); 153 } 154 out.println(" [ok: " + url + "]"); 155 156 out.print("setting isolation level ..."); 157 out.flush(); 158 // ndb storage engine only supports READ_COMMITTED 159 final int il = Connection.TRANSACTION_READ_COMMITTED; 160 connection.setTransactionIsolation(il); 161 out.print(" [ok: "); 162 switch (connection.getTransactionIsolation()) { 163 case Connection.TRANSACTION_READ_UNCOMMITTED: 164 out.print("READ_UNCOMMITTED"); 165 break; 166 case Connection.TRANSACTION_READ_COMMITTED: 167 out.print("READ_COMMITTED"); 168 break; 169 case Connection.TRANSACTION_REPEATABLE_READ: 170 out.print("REPEATABLE_READ"); 171 break; 172 case Connection.TRANSACTION_SERIALIZABLE: 173 out.print("SERIALIZABLE"); 174 break; 175 default: 176 assert false; 177 } 178 out.println("]"); 179 180 initPreparedStatements(); 181 } 182 closeConnection()183 public void closeConnection() throws SQLException { 184 assert (connection != null); 185 186 out.println(); 187 out.println("releasing jdbc resources ..."); 188 189 closePreparedStatements(); 190 191 out.print("closing jdbc connection ..."); 192 out.flush(); 193 connection.close(); 194 connection = null; 195 out.println(" [ok]"); 196 } 197 initPreparedStatements()198 public void initPreparedStatements() throws SQLException { 199 assert (connection != null); 200 assert (ins0 == null); 201 assert (sel0 == null); 202 assert (upd0 == null); 203 assert (del0 == null); 204 205 out.print("using lock mode for reads ..."); 206 out.flush(); 207 final String lm; 208 switch (driver.lockMode) { 209 case READ_COMMITTED: 210 lm = ""; 211 break; 212 case SHARED: 213 lm = " LOCK IN share mode"; 214 break; 215 case EXCLUSIVE: 216 lm = " FOR UPDATE"; 217 break; 218 default: 219 lm = ""; 220 assert false; 221 } 222 out.println(" [ok: " + "SELECT" + lm + ";]"); 223 224 out.print("compiling jdbc statements ..."); 225 out.flush(); 226 227 final String sqlIns0 = "INSERT INTO mytable (c0, c1, c2, c3, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14) " 228 + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; 229 ins0 = connection.prepareStatement(sqlIns0); 230 231 final String sqlSel0 = ("SELECT * FROM mytable where c0=?" + lm); 232 sel0 = connection.prepareStatement(sqlSel0); 233 234 final String sqlUpd0 = "UPDATE mytable " 235 + "SET c1 = ?, c2 = ?, c3 = ?, c5 = ?, c6 = ?, c7 = ?, c8 = ?, c9 = ?, c10 = ?, c11 = ?, c12 = ?, c13 = ?, c14 = ? " 236 + "WHERE c0=?"; 237 upd0 = connection.prepareStatement(sqlUpd0); 238 239 final String sqlDel0 = "DELETE FROM mytable WHERE c0=?"; 240 del0 = connection.prepareStatement(sqlDel0); 241 242 delAll = connection.prepareStatement("DELETE FROM mytable"); 243 244 out.println(" [ok]"); 245 } 246 closePreparedStatements()247 protected void closePreparedStatements() throws SQLException { 248 assert (ins0 != null); 249 assert (sel0 != null); 250 assert (upd0 != null); 251 assert (del0 != null); 252 assert (delAll != null); 253 254 out.print("closing jdbc statements ..."); 255 out.flush(); 256 257 ins0.close(); 258 ins0 = null; 259 260 sel0.close(); 261 sel0 = null; 262 263 upd0.close(); 264 upd0 = null; 265 266 del0.close(); 267 del0 = null; 268 269 delAll.close(); 270 delAll = null; 271 272 out.println(" [ok]"); 273 } 274 275 // ---------------------------------------------------------------------- 276 runOperations()277 public void runOperations() throws SQLException { 278 out.println(); 279 out.println("running JDBC operations ..." 280 + " [nRows=" + driver.nRows + "]"); 281 282 if (driver.doSingle) { 283 if (driver.doInsert) runJdbcInsert(TwsDriver.XMode.SINGLE); 284 if (driver.doLookup) runJdbcLookup(TwsDriver.XMode.SINGLE); 285 if (driver.doUpdate) runJdbcUpdate(TwsDriver.XMode.SINGLE); 286 if (driver.doDelete) runJdbcDelete(TwsDriver.XMode.SINGLE); 287 } 288 if (driver.doBulk) { 289 if (driver.doInsert) runJdbcInsert(TwsDriver.XMode.BULK); 290 if (driver.doLookup) runJdbcLookup(TwsDriver.XMode.BULK); 291 if (driver.doUpdate) runJdbcUpdate(TwsDriver.XMode.BULK); 292 if (driver.doDelete) runJdbcDelete(TwsDriver.XMode.BULK); 293 } 294 if (driver.doBatch) { 295 if (driver.doInsert) runJdbcInsert(TwsDriver.XMode.BATCH); 296 //if (driver.doLookup) runJdbcLookup(TwsDriver.XMode.BATCH); 297 if (driver.doUpdate) runJdbcUpdate(TwsDriver.XMode.BATCH); 298 if (driver.doDelete) runJdbcDelete(TwsDriver.XMode.BATCH); 299 } 300 } 301 302 // ---------------------------------------------------------------------- 303 runJdbcInsert(TwsDriver.XMode mode)304 protected void runJdbcInsert(TwsDriver.XMode mode) throws SQLException { 305 final String name = "insert_" + mode.toString().toLowerCase(); 306 driver.begin(name); 307 308 connection.setAutoCommit(mode == TwsDriver.XMode.SINGLE); 309 for(int i = 0; i < driver.nRows; i++) { 310 jdbcInsert(i, mode); 311 } 312 if (mode == TwsDriver.XMode.BATCH) 313 ins0.executeBatch(); 314 if (mode != TwsDriver.XMode.SINGLE) 315 connection.commit(); 316 317 driver.finish(name); 318 } 319 jdbcInsert(int c0, TwsDriver.XMode mode)320 protected void jdbcInsert(int c0, TwsDriver.XMode mode) { 321 // include exception handling as part of jdbc pattern 322 try { 323 final int i = c0; 324 final String str = Integer.toString(i); 325 ins0.setString(1, str); // key 326 int width = metaData.getColumnWidth(1); 327 ins0.setString(2, fixedStr.substring(0, width)); 328 ins0.setInt(3, i); 329 ins0.setInt(4, i); 330 331 for(int j = 5; j < metaData.getColumnCount(); j++) { 332 width = metaData.getColumnWidth(j); 333 ins0.setString(j, fixedStr.substring(0, width)); 334 } 335 336 if (mode == TwsDriver.XMode.BATCH) { 337 ins0.addBatch(); 338 } else { 339 int cnt = ins0.executeUpdate(); 340 assert (cnt == 1); 341 } 342 } catch (SQLException e) { 343 throw new RuntimeException(e); 344 } 345 } 346 347 // ---------------------------------------------------------------------- 348 runJdbcLookup(TwsDriver.XMode mode)349 protected void runJdbcLookup(TwsDriver.XMode mode) throws SQLException { 350 assert(mode != TwsDriver.XMode.BATCH); 351 352 final String name = "lookup_" + mode.toString().toLowerCase(); 353 driver.begin(name); 354 355 connection.setAutoCommit(mode == TwsDriver.XMode.SINGLE); 356 for(int i = 0; i < driver.nRows; i++) { 357 jdbcLookup(i); 358 } 359 if (mode != TwsDriver.XMode.SINGLE) 360 connection.commit(); 361 362 driver.finish(name); 363 } 364 jdbcLookup(int c0)365 protected void jdbcLookup(int c0) { 366 // include exception handling as part of jdbc pattern 367 try { 368 sel0.setString(1, Integer.toString(c0)); // key 369 ResultSet resultSet = sel0.executeQuery(); 370 371 if (resultSet.next()) { 372 // not verifying at this time 373 String ac0 = resultSet.getString(1); 374 String c1 = resultSet.getString(2); 375 int c2 = resultSet.getInt(3); 376 int c3 = resultSet.getInt(4); 377 int c4 = resultSet.getInt(5); 378 String c5 = resultSet.getString(6); 379 String c6 = resultSet.getString(7); 380 String c7 = resultSet.getString(8); 381 String c8 = resultSet.getString(9); 382 String c9 = resultSet.getString(10); 383 String c10 = resultSet.getString(11); 384 String c11 = resultSet.getString(12); 385 String c12 = resultSet.getString(13); 386 String c13 = resultSet.getString(14); 387 String c14 = resultSet.getString(15); 388 } 389 assert (!resultSet.next()); 390 391 resultSet.close(); 392 } catch (SQLException e) { 393 throw new RuntimeException(e); 394 } 395 } 396 397 // ---------------------------------------------------------------------- 398 runJdbcUpdate(TwsDriver.XMode mode)399 protected void runJdbcUpdate(TwsDriver.XMode mode) throws SQLException { 400 final String name = "update_" + mode.toString().toLowerCase(); 401 driver.begin(name); 402 403 connection.setAutoCommit(mode == TwsDriver.XMode.SINGLE); 404 for(int i = 0; i < driver.nRows; i++) { 405 jdbcUpdate(i, mode); 406 } 407 if (mode == TwsDriver.XMode.BATCH) 408 upd0.executeBatch(); 409 if (mode != TwsDriver.XMode.SINGLE) 410 connection.commit(); 411 412 driver.finish(name); 413 } 414 jdbcUpdate(int c0, TwsDriver.XMode mode)415 protected void jdbcUpdate(int c0, TwsDriver.XMode mode) { 416 final String str0 = Integer.toString(c0); 417 final int r = -c0; 418 final String str1 = Integer.toString(r); 419 420 // include exception handling as part of jdbc pattern 421 try { 422 upd0.setString(1, str1); 423 upd0.setInt(2, r); 424 upd0.setInt(3, r); 425 426 for(int j = 5; j < metaData.getColumnCount(); j++) { 427 int width = metaData.getColumnWidth(j); 428 upd0.setString(j - 1, fixedStr.substring(0, width)); 429 } 430 431 upd0.setString(14, str0); // key 432 433 if (mode == TwsDriver.XMode.BATCH) { 434 upd0.addBatch(); 435 } else { 436 int cnt = upd0.executeUpdate(); 437 assert (cnt == 1); 438 } 439 } catch (SQLException e) { 440 throw new RuntimeException(e); 441 } 442 } 443 444 // ---------------------------------------------------------------------- 445 runJdbcDelete(TwsDriver.XMode mode)446 protected void runJdbcDelete(TwsDriver.XMode mode) throws SQLException { 447 final String name = "delete_" + mode.toString().toLowerCase(); 448 driver.begin(name); 449 450 connection.setAutoCommit(mode == TwsDriver.XMode.SINGLE); 451 for(int i = 0; i < driver.nRows; i++) { 452 jdbcDelete(i, mode); 453 } 454 if (mode == TwsDriver.XMode.BATCH) 455 del0.executeBatch(); 456 if (mode != TwsDriver.XMode.SINGLE) 457 connection.commit(); 458 459 driver.finish(name); 460 } 461 jdbcDelete(int c0, TwsDriver.XMode mode)462 protected void jdbcDelete(int c0, TwsDriver.XMode mode) { 463 // include exception handling as part of jdbc pattern 464 try { 465 final String str = Integer.toString(c0); 466 del0.setString(1, str); 467 if (mode == TwsDriver.XMode.BATCH) { 468 del0.addBatch(); 469 } else { 470 int cnt = del0.executeUpdate(); 471 assert (cnt == 1); 472 } 473 } catch (SQLException e) { 474 throw new RuntimeException(e); 475 } 476 } 477 } 478