001/* 002 * #%L 003 * Netarchivesuite - common 004 * %% 005 * Copyright (C) 2005 - 2014 The Royal Danish Library, the Danish State and University Library, 006 * the National Library of France and the Austrian National Library. 007 * %% 008 * This program is free software: you can redistribute it and/or modify 009 * it under the terms of the GNU Lesser General Public License as 010 * published by the Free Software Foundation, either version 2.1 of the 011 * License, or (at your option) any later version. 012 * 013 * This program is distributed in the hope that it will be useful, 014 * but WITHOUT ANY WARRANTY; without even the implied warranty of 015 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 016 * GNU General Lesser Public License for more details. 017 * 018 * You should have received a copy of the GNU General Lesser Public 019 * License along with this program. If not, see 020 * <http://www.gnu.org/licenses/lgpl-2.1.html>. 021 * #L% 022 */ 023 024package dk.netarkivet.common.utils; 025 026import java.io.StringReader; 027import java.sql.Connection; 028import java.sql.PreparedStatement; 029import java.sql.ResultSet; 030import java.sql.SQLException; 031import java.sql.Statement; 032import java.sql.Timestamp; 033import java.sql.Types; 034import java.util.ArrayList; 035import java.util.Arrays; 036import java.util.Date; 037import java.util.HashMap; 038import java.util.Iterator; 039import java.util.List; 040import java.util.Map; 041import java.util.Set; 042import java.util.TreeSet; 043 044import org.slf4j.Logger; 045import org.slf4j.LoggerFactory; 046 047import dk.netarkivet.common.exceptions.ArgumentNotValid; 048import dk.netarkivet.common.exceptions.IOFailure; 049import dk.netarkivet.common.exceptions.PermissionDenied; 050 051/** 052 * Various database related utilities. 053 */ 054public final class DBUtils { 055 056 /** The logger. */ 057 private static final Logger log = LoggerFactory.getLogger(DBUtils.class); 058 059 /** default constructor. Is private to avoid initialization. */ 060 private DBUtils() { 061 } 062 063 /** 064 * Execute an SQL statement and return the single integer in the result set. 065 * 066 * @param s A prepared statement 067 * @return The integer result, or null if the result value was null. 068 * @throws IOFailure if the statement didn't result in exactly one integer. 069 */ 070 public static Integer selectIntValue(PreparedStatement s) { 071 ArgumentNotValid.checkNotNull(s, "PreparedStatement s"); 072 try { 073 ResultSet res = s.executeQuery(); 074 if (!res.next()) { 075 throw new IOFailure("No results from " + s); 076 } 077 Integer resultInt = res.getInt(1); 078 if (res.wasNull()) { 079 resultInt = null; 080 } 081 if (res.next()) { 082 throw new IOFailure("Too many results from " + s); 083 } 084 return resultInt; 085 } catch (SQLException e) { 086 throw new IOFailure("SQL error executing statement " + s + "\n" + ExceptionUtils.getSQLExceptionCause(e), e); 087 } 088 } 089 090 /** 091 * Execute an SQL statement and return the single int in the result set. This variant takes a query string and a 092 * single string arg and combines them to form a normal query. 093 * <p> 094 * NB: the method does not close the provided connection. 095 * 096 * @param connection connection to database. 097 * @param query a query with ? for parameters (must not be null or empty string) 098 * @param args parameters of type string, int, long or boolean 099 * @return The integer result 100 * @throws IOFailure if the statement didn't result in exactly one integer 101 */ 102 public static Integer selectIntValue(Connection connection, String query, Object... args) { 103 ArgumentNotValid.checkNotNull(connection, "Connection connection"); 104 ArgumentNotValid.checkNotNullOrEmpty(query, "String query"); 105 ArgumentNotValid.checkNotNull(args, "Object... args"); 106 try ( PreparedStatement s = prepareStatement(connection, query, args); ) { 107 return selectIntValue(s); 108 } catch (SQLException e) { 109 throw new IOFailure("SQL error preparing statement " + query + " args " + Arrays.toString(args) + "\n" 110 + ExceptionUtils.getSQLExceptionCause(e), e); 111 } 112 } 113 114 /** 115 * Execute an SQL statement and return the single long in the result set. 116 * 117 * @param s A prepared statement 118 * @return The long result, or null if the result was a null value Note that a null value is not the same as no 119 * result rows. 120 * @throws IOFailure if the statement didn't result in exactly one row with a long or null value 121 */ 122 public static Long selectLongValue(PreparedStatement s) { 123 ArgumentNotValid.checkNotNull(s, "PreparedStatement s"); 124 try { 125 ResultSet res = s.executeQuery(); 126 if (!res.next()) { 127 throw new IOFailure("No results from " + s); 128 } 129 Long resultLong = res.getLong(1); 130 if (res.wasNull()) { 131 resultLong = null; 132 } 133 if (res.next()) { 134 throw new IOFailure("Too many results from " + s); 135 } 136 return resultLong; 137 } catch (SQLException e) { 138 throw new IOFailure("SQL error executing statement " + s + "\n" + ExceptionUtils.getSQLExceptionCause(e), e); 139 } 140 } 141 142 /** 143 * Execute an SQL statement and return the single long in the result set. This variant takes a query string and a 144 * single string arg and combines them to form a normal query. 145 * <p> 146 * NB: the provided connection is not closed. 147 * 148 * @param connection connection to database. 149 * @param query a query with ? for parameters (must not be null or empty string) 150 * @param args parameters of type string, int, long or boolean 151 * @return The long result 152 * @throws IOFailure if the statement didn't result in exactly one long value 153 */ 154 public static Long selectLongValue(Connection connection, String query, Object... args) { 155 ArgumentNotValid.checkNotNull(connection, "Connection connection"); 156 ArgumentNotValid.checkNotNullOrEmpty(query, "String query"); 157 ArgumentNotValid.checkNotNull(args, "Object... args"); 158 try (PreparedStatement s = DBUtils.prepareStatement(connection, query, args);) { 159 return selectLongValue(s); 160 } catch (SQLException e) { 161 throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n" 162 + ExceptionUtils.getSQLExceptionCause(e), e); 163 } 164 } 165 166 /** 167 * Execute an SQL statement and return the first long in the result set, or null if resultset is empty. 168 * <p> 169 * NB: the provided connection is not closed. 170 * 171 * @param connection connection to database. 172 * @param query a query with ? for parameters (must not be null or empty string) 173 * @param args parameters of type string, int, long or boolean 174 * @return The long result, or will return null in one of the two following cases: There is no results, or the first 175 * result is a null-value. 176 * @throws IOFailure on SQL errors. 177 */ 178 179 public static Long selectFirstLongValueIfAny(Connection connection, String query, Object... args) { 180 ArgumentNotValid.checkNotNull(connection, "Connection connection"); 181 ArgumentNotValid.checkNotNullOrEmpty(query, "String query"); 182 ArgumentNotValid.checkNotNull(args, "Object... args"); 183 try (PreparedStatement s = DBUtils.prepareStatement(connection, query, args);) { 184 ResultSet rs = s.executeQuery(); 185 if (rs.next()) { 186 return DBUtils.getLongMaybeNull(rs, 1); 187 } else { 188 return null; 189 } 190 } catch (SQLException e) { 191 String message = "SQL error executing '" + query + "'" + "\n" + ExceptionUtils.getSQLExceptionCause(e); 192 log.warn(message, e); 193 throw new IOFailure(message, e); 194 } 195 } 196 197 /** 198 * Prepare a statement given a query string and some args. 199 * <p> 200 * NB: the provided connection is not closed. 201 * 202 * @param c a Database connection 203 * @param query a query string (must not be null or empty) 204 * @param args some args to insert into this query string (must not be null) 205 * @return a prepared statement 206 * @throws SQLException If unable to prepare a statement 207 * @throws ArgumentNotValid If unable to handle type of one the args, or the arguments are either null or an empty 208 * String. 209 */ 210 public static PreparedStatement prepareStatement(Connection c, String query, Object... args) throws SQLException { 211 ArgumentNotValid.checkNotNull(c, "Connection c"); 212 ArgumentNotValid.checkNotNullOrEmpty(query, "String query"); 213 ArgumentNotValid.checkNotNull(args, "Object... args"); 214 PreparedStatement s = c.prepareStatement(query); 215 int i = 1; 216 for (Object arg : args) { 217 if (arg instanceof String) { 218 s.setString(i, (String) arg); 219 } else if (arg instanceof Integer) { 220 s.setInt(i, (Integer) arg); 221 } else if (arg instanceof Long) { 222 s.setLong(i, (Long) arg); 223 } else if (arg instanceof Boolean) { 224 s.setBoolean(i, (Boolean) arg); 225 } else if (arg instanceof Date) { 226 s.setTimestamp(i, new Timestamp(((Date) arg).getTime())); 227 } else { 228 throw new ArgumentNotValid("Cannot handle type '" + arg.getClass().getName() 229 + "'. We can only handle string, " + "int, long, date or boolean args for query: " + query); 230 } 231 ++i; 232 } 233 return s; 234 } 235 236 /** 237 * Prepare a statement for iteration given a query string, fetch size and some args. 238 * <p> 239 * NB: the provided connection is not closed. 240 * 241 * @param c a Database connection 242 * @param fetchSize hint to JDBC driver on number of results to cache 243 * @param query a query string (must not be null or empty) 244 * @param args some args to insert into this query string (must not be null) 245 * @return a prepared statement 246 * @throws SQLException If unable to prepare a statement 247 * @throws ArgumentNotValid If unable to handle type of one the args, or the arguments are either null or an empty 248 * String. 249 */ 250 public static PreparedStatement prepareStatement(Connection c, int fetchSize, String query, Object... args) 251 throws SQLException { 252 ArgumentNotValid.checkNotNull(c, "Connection c"); 253 ArgumentNotValid.checkPositive(fetchSize, "int fetchSize"); 254 ArgumentNotValid.checkNotNullOrEmpty(query, "String query"); 255 ArgumentNotValid.checkNotNull(args, "Object... args"); 256 c.setAutoCommit(false); 257 PreparedStatement s = c.prepareStatement(query, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 258 s.setFetchSize(fetchSize); 259 int i = 1; 260 for (Object arg : args) { 261 if (arg instanceof String) { 262 s.setString(i, (String) arg); 263 } else if (arg instanceof Integer) { 264 s.setInt(i, (Integer) arg); 265 } else if (arg instanceof Long) { 266 s.setLong(i, (Long) arg); 267 } else if (arg instanceof Boolean) { 268 s.setBoolean(i, (Boolean) arg); 269 } else if (arg instanceof Date) { 270 s.setTimestamp(i, new Timestamp(((Date) arg).getTime())); 271 } else { 272 throw new ArgumentNotValid("Cannot handle type '" + arg.getClass().getName() 273 + "'. We can only handle string, " + "int, long, date or boolean args for query: " + query); 274 } 275 ++i; 276 } 277 return s; 278 } 279 280 /** 281 * Execute an SQL statement and return the list of strings in its result set. This uses specifically the harvester 282 * database. 283 * <p> 284 * NB: the provided connection is not closed. 285 * 286 * @param connection connection to the database. 287 * @param query the given sql-query (must not be null or empty) 288 * @param args The arguments to insert into this query (must not be null) 289 * @return the list of strings in its result set 290 * @throws IOFailure If this query fails 291 */ 292 public static List<String> selectStringList(Connection connection, String query, Object... args) { 293 ArgumentNotValid.checkNotNull(connection, "Connection connection"); 294 ArgumentNotValid.checkNotNullOrEmpty(query, "String query"); 295 ArgumentNotValid.checkNotNull(args, "Object... args"); 296 try (PreparedStatement s = prepareStatement(connection, query, args);) { 297 ResultSet result = s.executeQuery(); 298 List<String> results = new ArrayList<String>(); 299 while (result.next()) { 300 if (result.getString(1) == null) { 301 String warning = "NULL pointer found in resultSet from query: " + query; 302 log.warn(warning); 303 throw new IOFailure(warning); 304 } 305 results.add(result.getString(1)); 306 } 307 return results; 308 } catch (SQLException e) { 309 throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n" 310 + ExceptionUtils.getSQLExceptionCause(e), e); 311 } 312 } 313 314 /** 315 * Execute an SQL statement and return the list of strings -> id mappings in its result set. 316 * <p> 317 * NB: the provided connection is not closed. 318 * 319 * @param connection connection to the database. 320 * @param query the given sql-query (must not be null or empty string) 321 * @param args The arguments to insert into this query 322 * @return the list of strings -> id mappings 323 * @throws SQLException If this query fails 324 */ 325 public static Map<String, Long> selectStringLongMap(Connection connection, String query, Object... args) 326 throws SQLException { 327 ArgumentNotValid.checkNotNull(connection, "Connection connection"); 328 ArgumentNotValid.checkNotNullOrEmpty(query, "String query"); 329 ArgumentNotValid.checkNotNull(args, "Object... args"); 330 try (PreparedStatement s = prepareStatement(connection, query, args);) { 331 ResultSet result = s.executeQuery(); 332 Map<String, Long> results = new HashMap<String, Long>(); 333 while (result.next()) { 334 String resultString = result.getString(1); 335 long resultLong = result.getLong(2); 336 if ((resultString == null) || (resultLong == 0L && result.wasNull())) { 337 String warning = "NULL pointers found in entry (" + resultString + "," + resultLong 338 + ") in resultset from query: " + query; 339 log.warn(warning); 340 } 341 results.put(resultString, resultLong); 342 } 343 return results; 344 } 345 } 346 347 /** 348 * Execute an SQL statement and return the list of Long-objects in its result set. 349 * <p> 350 * 351 * @param connection connection to the database. 352 * @param query the given sql-query (must not be null or empty string) 353 * @param args The arguments to insert into this query 354 * @return the list of Long-objects in its result set 355 */ 356 public static List<Long> selectLongList(Connection connection, String query, Object... args) { 357 ArgumentNotValid.checkNotNull(connection, "Connection connection"); 358 ArgumentNotValid.checkNotNullOrEmpty(query, "String query"); 359 ArgumentNotValid.checkNotNull(args, "Object... args"); 360 try (PreparedStatement s = prepareStatement(connection, query, args);) { 361 ResultSet result = s.executeQuery(); 362 List<Long> results = new ArrayList<Long>(); 363 while (result.next()) { 364 if (result.getLong(1) == 0L && result.wasNull()) { 365 String warning = "NULL value encountered in query: " + query; 366 log.warn(warning); 367 } 368 results.add(result.getLong(1)); 369 } 370 return results; 371 } catch (SQLException e) { 372 throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n" 373 + ExceptionUtils.getSQLExceptionCause(e), e); 374 } 375 } 376 377 /** 378 * Return an iterator to a list of Longs. 379 * 380 * @param connection an open connection to the database 381 * @param query The given sql-query (must not be null or empty string) 382 * @param args The arguments to insert into this query 383 * @return an iterator to a list of Longs. 384 */ 385 public static Iterator<Long> selectLongIterator(Connection connection, String query, Object... args) { 386 ArgumentNotValid.checkNotNull(connection, "Connection connection"); 387 ArgumentNotValid.checkNotNullOrEmpty(query, "String query"); 388 ArgumentNotValid.checkNotNull(args, "Object... args"); 389 try { 390 PreparedStatement s = prepareStatement(connection, 8192, query, args); 391 ResultSet result = s.executeQuery(); 392 Iterator<Long> results = new ResultSetIterator<Long>(s, result) { 393 @Override 394 public Long filter(ResultSet result) { 395 try { 396 return result.getLong(1); 397 } catch (SQLException e) { 398 log.warn("Error retrieving long from resultset\n{}", ExceptionUtils.getSQLExceptionCause(e), e); 399 return 0L; 400 } 401 } 402 }; 403 return results; 404 } catch (SQLException e) { 405 throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n" 406 + ExceptionUtils.getSQLExceptionCause(e), e); 407 } 408 } 409 410 /** 411 * Execute an SQL statement and return the set of Long-objects in its result set. 412 * <p> 413 * NB: the provided connection is not closed. 414 * 415 * @param connection connection to the database. 416 * @param query the given sql-query (must not be null or empty string) 417 * @param args The arguments to insert into this query 418 * @return the set of Long-objects in its result set 419 */ 420 public static Set<Long> selectLongSet(Connection connection, String query, Object... args) { 421 ArgumentNotValid.checkNotNull(connection, "Connection connection"); 422 ArgumentNotValid.checkNotNullOrEmpty(query, "String query"); 423 ArgumentNotValid.checkNotNull(args, "Object... args"); 424 try (PreparedStatement s = prepareStatement(connection, query, args);) { 425 ResultSet result = s.executeQuery(); 426 Set<Long> results = new TreeSet<Long>(); 427 while (result.next()) { 428 if (result.getLong(1) == 0L && result.wasNull()) { 429 String warning = "NULL value encountered in query: " + query; 430 log.warn(warning); 431 } 432 results.add(result.getLong(1)); 433 } 434 return results; 435 } catch (SQLException e) { 436 throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n" 437 + ExceptionUtils.getSQLExceptionCause(e), e); 438 } 439 } 440 441 /** 442 * Get the automatically generated key that was created with the just-executed statement. 443 * 444 * @param s A statement created with Statement.RETURN_GENERATED_KEYS 445 * @return The single generated key 446 * @throws SQLException If a database access error occurs or the PreparedStatement is closed, or the JDBC driver 447 * does not support the setGeneratedKeys() method 448 */ 449 public static long getGeneratedID(PreparedStatement s) throws SQLException { 450 ArgumentNotValid.checkNotNull(s, "PreparedStatement s"); 451 ResultSet res = s.getGeneratedKeys(); 452 if (!res.next()) { 453 throw new IOFailure("No keys generated by " + s); 454 } 455 return res.getLong(1); 456 } 457 458 /** 459 * Returns the version of a table according to schemaversions, or 0 for the initial, unnumbered version. 460 * <p> 461 * NB: the provided connection is not closed 462 * 463 * @param connection connection to the database. 464 * @param tablename The name of a table in the database. 465 * @return Version of the given table. 466 * @throws IOFailure if DB table schemaversions does not exist 467 */ 468 public static int getTableVersion(Connection connection, String tablename) throws IOFailure { 469 ArgumentNotValid.checkNotNull(connection, "Connection connection"); 470 ArgumentNotValid.checkNotNullOrEmpty(tablename, "String tablenname"); 471 try (PreparedStatement s = connection.prepareStatement( 472 "SELECT version FROM schemaversions WHERE tablename = ?");) { 473 int version = 0; 474 s.setString(1, tablename); 475 ResultSet res = s.executeQuery(); 476 if (!res.next()) { 477 log.warn("As yet unknown tablename '{}' in table schemaversions. The table should be automatically " 478 + "created in the database when it is first needed.", tablename); 479 } else { 480 version = res.getInt(1); 481 if (res.wasNull()) { 482 log.warn("Null table version for '{}'", tablename); 483 } 484 } 485 return version; 486 } catch (SQLException e) { 487 String msg = "SQL Error checking version of table " + tablename + "\n" 488 + ExceptionUtils.getSQLExceptionCause(e); 489 log.warn(msg, e); 490 throw new IOFailure(msg, e); 491 } 492 } 493 494 /** 495 * Set String Max Length. If contents.length() > maxSize, contents is truncated to contain the first maxSize 496 * characters of the contents, and a warning is logged. 497 * 498 * @param s a Prepared Statement 499 * @param fieldNum a index into the above statement 500 * @param contents the contents 501 * @param maxSize the maximum size of field: fieldName 502 * @param o the Object, which is assumed to have a field named fieldName 503 * @param fieldname the name of a given field 504 * @throws SQLException if set operation fails 505 */ 506 public static void setStringMaxLength(PreparedStatement s, int fieldNum, String contents, int maxSize, Object o, 507 String fieldname) throws SQLException { 508 ArgumentNotValid.checkNotNull(s, "PreparedStatement s"); 509 ArgumentNotValid.checkNotNegative(fieldNum, "int fieldNum"); 510 511 if (contents != null) { 512 if (contents.length() > maxSize) { 513 log.warn("{} of {} is longer than the allowed {} characters. The contents is truncated to length {}." 514 + " The untruncated contents was: {}", fieldname, o, maxSize, maxSize, contents); 515 // truncate to length maxSize 516 contents = contents.substring(0, maxSize); 517 } 518 s.setString(fieldNum, contents); 519 } else { 520 s.setNull(fieldNum, Types.VARCHAR); 521 } 522 } 523 524 /** 525 * Set the comments of a Named object into the given field of statement. 526 * 527 * @param s a prepared statement 528 * @param fieldNum the index of the given field to be set 529 * @param o the Named object 530 * @param maxFieldSize max size of the comments field 531 * @throws SQLException If any trouble accessing the database during the operation 532 * @throws PermissionDenied If length of o.getComments() is larger than Constants.MAX_COMMENT_SIZE 533 */ 534 public static void setComments(PreparedStatement s, int fieldNum, Named o, int maxFieldSize) throws SQLException { 535 ArgumentNotValid.checkNotNull(s, "PreparedStatement s"); 536 ArgumentNotValid.checkNotNegative(fieldNum, "int fieldNum"); 537 ArgumentNotValid.checkNotNull(o, "Named o"); 538 ArgumentNotValid.checkNotNegative(maxFieldSize, "int maxFieldSize"); 539 540 if (o.getComments().length() > maxFieldSize) { 541 throw new PermissionDenied("Length of comments (" + o.getComments().length() 542 + ") is larger than allowed. Max length is " + maxFieldSize); 543 } 544 setStringMaxLength(s, fieldNum, o.getComments(), maxFieldSize, o, "comments"); 545 } 546 547 /** 548 * Set the name of a Named object into the given field. 549 * 550 * @param s a prepared statement 551 * @param fieldNum the index of the given field to be set 552 * @param o the Named object 553 * @param maxFieldSize max size of the name field 554 * @throws SQLException If any trouble accessing the database during the operation 555 * @throws PermissionDenied If length of o.getName() is larger than Constants.MAX_NAME_SIZE 556 */ 557 public static void setName(PreparedStatement s, int fieldNum, Named o, int maxFieldSize) throws SQLException { 558 ArgumentNotValid.checkNotNull(s, "PreparedStatement s"); 559 ArgumentNotValid.checkNotNegative(fieldNum, "int fieldNum"); 560 ArgumentNotValid.checkNotNull(o, "Named o"); 561 ArgumentNotValid.checkNotNegative(maxFieldSize, "int maxFieldSize"); 562 563 if (o.getName().length() > maxFieldSize) { 564 throw new PermissionDenied("Length of name (" + o.getName().length() 565 + ") is larger than allowed. Max length is " + maxFieldSize); 566 } 567 setStringMaxLength(s, fieldNum, o.getName(), maxFieldSize, o, "name"); 568 } 569 570 /** 571 * Set the Date into the given field of a statement. 572 * 573 * @param s a prepared statement 574 * @param fieldNum the index of the given field to be set 575 * @param date the date (may be null) 576 * @throws SQLException If any trouble accessing the database during the operation 577 */ 578 public static void setDateMaybeNull(PreparedStatement s, int fieldNum, Date date) throws SQLException { 579 ArgumentNotValid.checkNotNull(s, "PreparedStatement s"); 580 ArgumentNotValid.checkNotNegative(fieldNum, "int fieldNum"); 581 582 if (date != null) { 583 s.setTimestamp(fieldNum, new Timestamp(date.getTime())); 584 } else { 585 s.setNull(fieldNum, Types.DATE); 586 } 587 } 588 589 /** 590 * Get a Date from a column in the resultset. Returns null, if the value in the column is NULL. 591 * 592 * @param rs the resultSet 593 * @param columnIndex The given column, where the Date resides 594 * @return a Date from a column in the resultset 595 * @throws SQLException If columnIndex does not correspond to a parameter marker in the ResultSet, or a database 596 * access error occurs or this method is called on a closed ResultSet 597 */ 598 public static Date getDateMaybeNull(ResultSet rs, final int columnIndex) throws SQLException { 599 ArgumentNotValid.checkNotNull(rs, "ResultSet rs"); 600 ArgumentNotValid.checkNotNegative(columnIndex, "int columnIndex"); 601 602 final Timestamp startTimestamp = rs.getTimestamp(columnIndex); 603 if (rs.wasNull()) { 604 return null; 605 } 606 Date startdate; 607 if (startTimestamp != null) { 608 startdate = new Date(startTimestamp.getTime()); 609 } else { 610 startdate = null; 611 } 612 return startdate; 613 } 614 615 /** 616 * Method to perform a rollback of complex DB updates. If no commit has been performed, this will undo the entire 617 * transaction, otherwise nothing will happen. If autoCommit is true then no action is taken. This method should be 618 * called in a finally block with no DB updates after the last commit. Thus exceptions while closing are ignored, 619 * but logged as warnings. 620 * <p> 621 * NB: the provided connection is not closed. 622 * 623 * @param c the db-connection 624 * @param action The action going on, before calling this method 625 * @param o The Object being acted upon by this action 626 */ 627 public static void rollbackIfNeeded(Connection c, String action, Object o) { 628 ArgumentNotValid.checkNotNull(c, "Connection c"); 629 try { 630 if (!c.getAutoCommit()) { 631 c.rollback(); 632 c.setAutoCommit(true); 633 } 634 } catch (SQLException e) { 635 log.warn("SQL error doing rollback after {} {}\n{}", action, o, ExceptionUtils.getSQLExceptionCause(e), e); 636 // Can't throw here, we want the real exception 637 } 638 } 639 640 /** 641 * Set the CLOB maxlength. If contents.length() > maxSize, contents is truncated to contain the first maxSize 642 * characters of the contents, and a warning is logged. 643 * 644 * @param s a prepared statement 645 * @param fieldNum the field-index, where the contents are inserted 646 * @param contents the contents 647 * @param maxSize the maxsize for this contents 648 * @param o the Object, which is assumed to have a field named fieldName 649 * @param fieldName a given field (Assumed to present in Object o) 650 * @throws SQLException If fieldNum does not correspond to a parameter marker in the PreparedStatement, or a 651 * database access error occurs or this method is called on a closed PreparedStatement 652 */ 653 public static void setClobMaxLength(PreparedStatement s, int fieldNum, String contents, long maxSize, Object o, 654 String fieldName) throws SQLException { 655 ArgumentNotValid.checkNotNull(s, "PreparedStatement s"); 656 if (contents != null) { 657 if (contents.length() > maxSize) { 658 log.warn("The field '{}' is {} characters long, which is {} longer than the allowed {} characters. " 659 + "The contents is now truncated to length {}", fieldName, contents.length(), 660 (contents.length() - maxSize), maxSize, maxSize); 661 // This caused OOM if both the 'contents' and o.toString() was large 662 // (See NAS-2015). 663 // It is therefore omitted from this log-entry. 664 665 // truncate to length maxSize (if maxSize <= Integer.MAX_VALUE) 666 // else truncate to length Integer.MAX_VALUE 667 if (maxSize > Integer.MAX_VALUE) { 668 log.warn("The maxSize is larger than maxint ({}), which is not allowed. MaxSize changed to maxint", 669 Integer.MAX_VALUE); 670 maxSize = Integer.MAX_VALUE; 671 } 672 contents = contents.substring(0, (int) maxSize); 673 } 674 s.setCharacterStream(fieldNum, new StringReader(contents), contents.length()); 675 s.setString(fieldNum, contents); 676 } else { 677 s.setNull(fieldNum, Types.CLOB); 678 } 679 } 680 681 /** 682 * Insert a long value (which could be null) into the given field of a statement. 683 * 684 * @param s a prepared Statement 685 * @param i the number of a given field in the prepared statement 686 * @param value the long value to insert (maybe null) 687 * @throws SQLException If i does not correspond to a parameter marker in the PreparedStatement, or a database 688 * access error occurs or this method is called on a closed PreparedStatement 689 */ 690 public static void setLongMaybeNull(PreparedStatement s, int i, Long value) throws SQLException { 691 ArgumentNotValid.checkNotNull(s, "PreparedStatement s"); 692 if (value != null) { 693 s.setLong(i, value); 694 } else { 695 s.setNull(i, Types.BIGINT); 696 } 697 } 698 699 /** 700 * Insert an Integer in prepared statement. 701 * 702 * @param s a prepared statement 703 * @param i the index of the statement, where the Integer should be inserted 704 * @param value The Integer to insert (maybe null) 705 * @throws SQLException If i does not correspond to a parameter marker in the PreparedStatement, or a database 706 * access error occurs or this method is called on a closed PreparedStatement 707 */ 708 public static void setIntegerMaybeNull(PreparedStatement s, int i, Integer value) throws SQLException { 709 ArgumentNotValid.checkNotNull(s, "PreparedStatement s"); 710 711 if (value != null) { 712 s.setInt(i, value); 713 } else { 714 s.setNull(i, Types.INTEGER); 715 } 716 } 717 718 /** 719 * Get an Integer from the resultSet in column i. 720 * 721 * @param rs the resultset 722 * @param i the column where the wanted Integer resides 723 * @return an Integer object located in column i in the resultset 724 * @throws SQLException If the columnIndex is not valid, or a database access error occurs or this method is called 725 * on a closed result set 726 */ 727 public static Integer getIntegerMaybeNull(ResultSet rs, int i) throws SQLException { 728 ArgumentNotValid.checkNotNull(rs, "ResultSet rs"); 729 Integer res = rs.getInt(i); 730 if (rs.wasNull()) { 731 return null; 732 } 733 return res; 734 } 735 736 /** 737 * Get a Long from the resultSet in column i. 738 * 739 * @param rs the resultset 740 * @param i the column where the wanted Long resides 741 * @return a Long object located in column i in the resultset 742 * @throws SQLException If the columnIndex is not valid, or a database access error occurs or this method is called 743 * on a closed result set 744 */ 745 public static Long getLongMaybeNull(ResultSet rs, int i) throws SQLException { 746 ArgumentNotValid.checkNotNull(rs, "ResultSet rs"); 747 Long res = rs.getLong(i); 748 if (rs.wasNull()) { 749 return null; 750 } 751 return res; 752 } 753 754 /** 755 * Return a description of where an object is used elsewhere in the database, or null. 756 * <p> 757 * NB: the provided connection is not closed. 758 * 759 * @param connection connection to the database. 760 * @param select A select statement finding the names of other uses. The statement should result in exactly one 761 * column of string values. 762 * @param victim The object being used. 763 * @param args Any objects that may be used to prepare the select statement. 764 * @return A string describing the usages, or null if no usages were found. 765 */ 766 public static String getUsages(Connection connection, String select, Object victim, Object... args) { 767 ArgumentNotValid.checkNotNull(connection, "Connection connection"); 768 PreparedStatement s = null; 769 try { 770 s = prepareStatement(connection, select, args); 771 ResultSet res = s.executeQuery(); 772 if (res.next()) { 773 List<String> usedIn = new ArrayList<String>(); 774 do { 775 usedIn.add(res.getString(1)); 776 } while (res.next()); 777 return usedIn.toString(); 778 } 779 return null; 780 } catch (SQLException e) { 781 final String message = "SQL error checking for usages of " + victim + "\n" 782 + ExceptionUtils.getSQLExceptionCause(e); 783 log.warn(message, e); 784 throw new IOFailure(message, e); 785 } finally { 786 closeStatementIfOpen(s); 787 } 788 } 789 790 /** 791 * Execute an SQL statement and return the single string in the result set. This variant takes a query string and a 792 * single string arg and combines them to form a normal query. 793 * <p> 794 * This assumes the connection is to the harvester database. 795 * 796 * @param connection connection to the database. 797 * @param query a query with ? for parameters (must not be null or an empty string) 798 * @param args parameters of type string, int, long or boolean 799 * @return The string result 800 * @throws IOFailure if the statement didn't result in exactly one string value 801 */ 802 public static String selectStringValue(Connection connection, String query, Object... args) { 803 ArgumentNotValid.checkNotNullOrEmpty(query, "String query"); 804 ArgumentNotValid.checkNotNull(args, "Object... args"); 805 ArgumentNotValid.checkNotNull(connection, "Connection connection"); 806 807 try ( PreparedStatement s = prepareStatement(connection, query, args); ) { 808 return DBUtils.selectStringValue(s); 809 } catch (SQLException e) { 810 throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n" 811 + ExceptionUtils.getSQLExceptionCause(e), e); 812 } 813 } 814 815 /** 816 * Execute an SQL statement and return the single string in the result set. 817 * 818 * @param s A prepared statement 819 * @return The string result, or null if the result was a null value Note that a null value is not the same as no 820 * result rows. 821 * @throws IOFailure if the statement didn't result in exactly one row with a string or null value 822 */ 823 public static String selectStringValue(PreparedStatement s) { 824 ArgumentNotValid.checkNotNull(s, "PreparedStatement s"); 825 try { 826 ResultSet res = s.executeQuery(); 827 if (!res.next()) { 828 throw new IOFailure("No results from " + s); 829 } 830 String resultString = res.getString(1); 831 if (res.wasNull()) { 832 resultString = null; 833 } 834 if (res.next()) { 835 throw new IOFailure("Too many results from " + s); 836 } 837 return resultString; 838 } catch (SQLException e) { 839 throw new IOFailure("SQL error executing statement " + s + "\n" + ExceptionUtils.getSQLExceptionCause(e), e); 840 } 841 } 842 843 /** 844 * Execute an SQL query and return whether the result contains any rows. 845 * <p> 846 * NB: the provided connection is not closed. 847 * 848 * @param connection connection to the database. 849 * @param query a query with ? for parameters (must not be null or an empty String) 850 * @param args parameters of type string, int, long or boolean 851 * @return True if executing the query resulted in at least one row. 852 * @throws IOFailure if there were problems with the SQL query 853 */ 854 public static boolean selectAny(Connection connection, String query, Object... args) { 855 ArgumentNotValid.checkNotNull(connection, "Connection connection"); 856 ArgumentNotValid.checkNotNullOrEmpty(query, "String query"); 857 ArgumentNotValid.checkNotNull(args, "Object... args"); 858 859 try ( PreparedStatement s = prepareStatement(connection, query, args); ) { 860 return s.executeQuery().next(); 861 } catch (SQLException e) { 862 throw new IOFailure("Error preparing SQL statement " + query + " args " + Arrays.toString(args) + "\n" 863 + ExceptionUtils.getSQLExceptionCause(e), e); 864 } 865 } 866 867 /** 868 * Translate a "normal" glob (with * and .) into SQL syntax. 869 * 870 * @param glob A shell-like glob string (must not be null) 871 * @return A string that implements glob in SQL "LIKE" constructs. 872 */ 873 public static String makeSQLGlob(String glob) { 874 ArgumentNotValid.checkNotNull(glob, "String glob"); 875 return glob.replace("*", "%").replace("?", "_"); 876 } 877 878 /** 879 * Update a database by executing all the statements in the updates String array. NOTE: this must NOT be used for 880 * tables under version control It must only be used in connection with temporary tables e.g. used for backup. 881 * <p> 882 * NB: the method does not close the provided connection. 883 * 884 * @param connection connection to the database. 885 * @param updates The SQL statements that makes the necessary updates. 886 * @throws IOFailure in case of problems in interacting with the database 887 */ 888 public static void executeSQL(Connection connection, final String... updates) { 889 ArgumentNotValid.checkNotNull(updates, "String... updates"); 890 String s = ""; 891 892 try { 893 connection.setAutoCommit(false); 894 for (String update : updates) { 895 s = update; 896 log.debug("Executing SQL-statement: {}", update); 897 try (PreparedStatement st = prepareStatement(connection, update);) { 898 st.executeUpdate(); 899 } 900 } 901 connection.setAutoCommit(true); 902 if (log.isDebugEnabled()) { 903 log.debug("Updated database using updates '{}'.", StringUtils.conjoin(";", updates)); 904 } 905 } catch (SQLException e) { 906 String msg = "SQL error updating database with sql: " + s + "\n" + ExceptionUtils.getSQLExceptionCause(e); 907 log.warn(msg, e); 908 throw new IOFailure(msg, e); 909 } finally { 910 rollbackIfNeeded(connection, "updating table with SQL: ", StringUtils.conjoin(";", updates) + "'."); 911 } 912 } 913 914 /** 915 * Close a statement, if not closed already Note: This does not throw any a SQLException, because it is always 916 * called inside a finally-clause. Exceptions are logged as warnings, though. 917 * 918 * @param s a statement 919 */ 920 public static void closeStatementIfOpen(Statement s) { 921 if (s != null) { 922 try { 923 s.close(); 924 } catch (SQLException e) { 925 log.warn("Error closing SQL statement {}\n{}", s, ExceptionUtils.getSQLExceptionCause(e), e); 926 } 927 } 928 } 929}