001/* 002 * ============================================================================ 003 * Copyright © 2002-2026 by Thomas Thrien. 004 * All Rights Reserved. 005 * ============================================================================ 006 * Licensed to the public under the agreements of the GNU Lesser General Public 007 * License, version 3.0 (the "License"). You may obtain a copy of the License at 008 * 009 * http://www.gnu.org/licenses/lgpl.html 010 * 011 * Unless required by applicable law or agreed to in writing, software 012 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT 013 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the 014 * License for the specific language governing permissions and limitations 015 * under the License. 016 */ 017 018package org.tquadrat.foundation.sql; 019 020import static java.lang.Character.isWhitespace; 021import static org.apiguardian.api.API.Status.STABLE; 022import static org.tquadrat.foundation.lang.CommonConstants.EMPTY_STRING; 023import static org.tquadrat.foundation.lang.Objects.nonNull; 024import static org.tquadrat.foundation.lang.Objects.requireNonNullArgument; 025import static org.tquadrat.foundation.lang.Objects.requireNotEmptyArgument; 026import static org.tquadrat.foundation.util.StringUtils.isEmptyOrBlank; 027import static org.tquadrat.foundation.util.StringUtils.splitString; 028 029import java.io.Serial; 030import java.io.Serializable; 031import java.sql.Connection; 032import java.sql.DatabaseMetaData; 033import java.sql.ResultSet; 034import java.sql.SQLException; 035import java.util.ArrayList; 036import java.util.Collection; 037import java.util.HashMap; 038import java.util.List; 039import java.util.Map; 040import java.util.Optional; 041import java.util.StringJoiner; 042import java.util.stream.Stream; 043import java.util.stream.StreamSupport; 044 045import org.apiguardian.api.API; 046import org.tquadrat.foundation.annotation.ClassVersion; 047import org.tquadrat.foundation.annotation.UtilityClass; 048import org.tquadrat.foundation.exception.EmptyArgumentException; 049import org.tquadrat.foundation.exception.NullArgumentException; 050import org.tquadrat.foundation.exception.PrivateConstructorForStaticClassCalledError; 051import org.tquadrat.foundation.exception.ValidationException; 052import org.tquadrat.foundation.lang.Objects; 053import org.tquadrat.foundation.sql.internal.ResultSetSpliterator; 054 055/** 056 * <p>{@summary Several utilities for the work with databases that will be 057 * accessed through plain JDBC.} 058 * 059 * @version $Id: DatabaseUtils.java 1161 2026-03-16 21:00:45Z tquadrat $ 060 * @extauthor Thomas Thrien - thomas.thrien@tquadrat.org 061 * @UMLGraph.link 062 * @since 0.1.0 063 */ 064@ClassVersion( sourceVersion = "$Id: DatabaseUtils.java 1161 2026-03-16 21:00:45Z tquadrat $" ) 065@UtilityClass 066@API( status = STABLE, since = "0.1.0" ) 067public final class DatabaseUtils 068{ 069 /*---------------*\ 070 ====** Inner Classes **==================================================== 071 \*---------------*/ 072 /** 073 * <p>{@summary Instances of this class are used to return the status of 074 * a call to 075 * {@link DatabaseUtils#execute(Connection,String...)}.}</p> 076 * 077 * @param command The failed command. 078 * @param error The exception that was thrown to indicate the failure. 079 * 080 * @extauthor Thomas Thrien - thomas.thrien@tquadrat.org 081 * @version $Id: DatabaseUtils.java 1161 2026-03-16 21:00:45Z tquadrat $ 082 * @since 0.0.1 083 * 084 * @UMLGraph.link 085 */ 086 @ClassVersion( sourceVersion = "$Id: DatabaseUtils.java 1161 2026-03-16 21:00:45Z tquadrat $" ) 087 @API( status = STABLE, since = "0.0.1" ) 088 @SuppressWarnings( "doclint:missing" ) 089 /* 090 * Due to a known bug in Javadoc will the fields of the record cause a 091 * warning about a missing @serial tag in the Javadoc comment. 092 */ 093 public record ExecStatus( String command, SQLException error ) implements Serializable 094 { 095 /*------------------------*\ 096 ====** Static Initialisations **======================================= 097 \*------------------------*/ 098 /** 099 * The serial version UID for objects of this class: {@value}. 100 * 101 * @hidden 102 */ 103 @Serial 104 private static final long serialVersionUID = 1L; 105 106 /*--------------*\ 107 ====** Constructors **================================================= 108 \*--------------*/ 109 /** 110 * Creates a new {@code ExecStatus} instance. 111 * 112 * @param command The failed command. 113 * @param error The exception for the failure. 114 */ 115 public ExecStatus 116 { 117 requireNotEmptyArgument( command, "command" ); 118 requireNonNullArgument( error, "error" ); 119 } // ExecStatus() 120 } 121 // record ExecStatus 122 123 /*--------------*\ 124 ====** Constructors **===================================================== 125 \*--------------*/ 126 /** 127 * No instance allowed for this class. 128 */ 129 private DatabaseUtils() { throw new PrivateConstructorForStaticClassCalledError( DatabaseUtils.class ); } 130 131 /*---------*\ 132 ====** Methods **========================================================== 133 \*---------*/ 134 /** 135 * <p>{@summary Checks whether the table, that is specified by its 136 * {@code catalog}, schema (through {@code schemaPattern}) and 137 * {@code tableNamePattern}, exists.}</p> 138 * <p>Where a pattern is allowed, the wildcards {@literal "%"} and 139 * {@literal "_"} can be used.</p> 140 * <p>This method works for any RDBMS.</p> 141 * 142 * @param connection The connection to the database. 143 * @param catalog A catalogue name; it must match the catalogue name as 144 * it is stored in the database. The empty String retrieves those 145 * tables without a catalogue, and {@code null} means that the catalogue 146 * name should not be used to narrow the search. 147 * @param schemaPattern A schema name pattern; it must match the schema 148 * name as it is stored in the database. The empty String retrieves 149 * those tables without a schema, and {@code null} means that the 150 * schema name should not be used to narrow the search. 151 * @param tableNamePattern A table name pattern; it must match the 152 * table name as it is stored in the database. 153 * @param tableTypes A list of table types, which must be from the list 154 * of table types returned from 155 * {@link DatabaseMetaData#getTableTypes()}, 156 * to include. It can be omitted to return all types. 157 * @return {@code true} if the specified table exists, {@code false} 158 * otherwise. 159 * @throws SQLException A database access error occurred. 160 */ 161 public static final boolean checkIfTableExists( final Connection connection, final String catalog, final String schemaPattern, final String tableNamePattern, final String... tableTypes ) throws SQLException 162 { 163 final var metaData = connection.getMetaData(); 164 var retValue = false; 165 final var effectiveTypes = nonNull( tableTypes ) && tableTypes.length == 0 ? null : tableTypes; 166 try( final var resultSet = metaData.getTables( catalog, schemaPattern, tableNamePattern, effectiveTypes ) ) 167 { 168 retValue = resultSet.next(); 169 } 170 171 //---* Done *---------------------------------------------------------- 172 return retValue; 173 } // checkIfTableExists() 174 175 /** 176 * Dumps the given result set to a 177 * {@link List} 178 * of Strings. 179 * 180 * @param resultSet The result set to dump. 181 * @return The contents of the result set. 182 * @throws SQLException Something went wrong when reading the result 183 * set. 184 * 185 * @since 0.4.1 186 */ 187 @API( status = STABLE, since = "0.4.1" ) 188 public static final List<String> dumpResultSet( final ResultSet resultSet ) throws SQLException 189 { 190 final var metaData = requireNonNullArgument( resultSet, "resultSet" ).getMetaData(); 191 final var columnCount = metaData.getColumnCount(); 192 final List<String> retValue = new ArrayList<>(); 193 194 while( resultSet.next() ) 195 { 196 final var line = new StringJoiner( "|", "{", "}" ); 197 line.setEmptyValue( "{}" ); 198 for( var i = 0; i < columnCount; ++i ) 199 { 200 final var label = metaData.getColumnLabel( i + 1 ); 201 final var value = Objects.toString( resultSet.getObject( i + 1 ) ); 202 line.add( resultSet.wasNull() ? "%s=NULL".formatted( label ) : "%s='%s'".formatted( label, value ) ); 203 } 204 retValue.add( line.toString() ); 205 } 206 207 //---* Done *---------------------------------------------------------- 208 return retValue; 209 } // dumpResultSet() 210 211 /** 212 * <p>{@summary Executes the given list of commands on the given database 213 * connection.}</p> 214 * <p>The commands should be DDL or DML commands, not queries. If all 215 * commands were executed successfully, the method calls 216 * {@link Connection#commit() commit()} 217 * on the provided connection, otherwise a call to 218 * {@link Connection#rollback() rollback()} 219 * is issued. In case the connection is configured for 220 * {@linkplain Connection#getAutoCommit() AutoCommit}, 221 * neither call will be made.</p> 222 * <p>In case of an error, the return value is not 223 * {@linkplain Optional#empty() empty}.</p> 224 * <p>Empty commands and commands that will start with a hash 225 * ("{@code #}") will be ignored; this allows to process 226 * script files without extensive reformatting.</p> 227 * 228 * @param connection The connection to the database. 229 * @param commands A list of SQL (DDL or DML) commands that will be 230 * executed against the provided connection. 231 * @return An instance of 232 * {@link Optional} 233 * that holds the execution status; will be. 234 * {@linkplain Optional#empty() empty} 235 * if all commands were successfully executed. 236 * 237 * @see Connection#getAutoCommit() 238 * @see Connection#setAutoCommit(boolean) 239 * @see #parseSQLScript(CharSequence) 240 */ 241 @API( status = STABLE, since = "0.0.1" ) 242 public static final Optional<ExecStatus> execute( final Connection connection, final String... commands ) 243 { 244 final var retValue = execute( connection, List.of( requireNonNullArgument( commands, "commands" ) ) ); 245 246 //---* Done *---------------------------------------------------------- 247 return retValue; 248 } // execute() 249 250 /** 251 * <p>{@summary Executes the given list of commands on the given database 252 * connection.}</p> 253 * <p>The commands should be DDL or DML commands, not queries. If all 254 * commands were executed successfully, the method calls 255 * {@link Connection#commit() commit()} 256 * on the provided connection, otherwise a call to 257 * {@link Connection#rollback() rollback()} 258 * is issued. In case the connection is configured for 259 * {@linkplain Connection#getAutoCommit() AutoCommit}, 260 * neither call will be made.</p> 261 * <p>In case of an error, the return value is not 262 * {@linkplain Optional#empty() empty}.</p> 263 * <p>Empty commands will be silently ignored.</p> 264 * 265 * @param connection The connection to the database. 266 * @param commands A list of SQL (DDL or DML) commands that will be 267 * executed against the provided connection. 268 * @return An instance of 269 * {@link Optional} 270 * that holds the execution status; will be. 271 * {@linkplain Optional#empty() empty} 272 * if all commands were successfully executed. 273 * 274 * @see Connection#getAutoCommit() 275 * @see Connection#setAutoCommit(boolean) 276 * @see #parseSQLScript(CharSequence) 277 */ 278 @API( status = STABLE, since = "0.1.0" ) 279 public static final Optional<ExecStatus> execute( final Connection connection, final List<String> commands ) 280 { 281 Optional<ExecStatus> retValue = Optional.empty(); 282 var currentCommand = EMPTY_STRING; 283 try( final var statement = requireNonNullArgument( connection, "connection" ).createStatement() ) 284 { 285 ExecLoop: for( final var sql : requireNonNullArgument( commands, "commands" ) ) 286 { 287 if( isEmptyOrBlank( sql ) ) continue ExecLoop; 288 currentCommand = sql; 289 statement.execute( sql ); 290 } // ExecLoop: 291 292 if( !connection.getAutoCommit() ) 293 { 294 currentCommand = "commit;"; 295 connection.commit(); 296 } 297 } 298 catch( final SQLException e ) 299 { 300 try 301 { 302 if( !connection.getAutoCommit() ) { connection.rollback(); } 303 } 304 catch( final SQLException eOnRollback ) 305 { 306 e.addSuppressed( eOnRollback ); 307 } 308 retValue = Optional.of( new ExecStatus( currentCommand, e ) ); 309 } 310 311 //---* Done *---------------------------------------------------------- 312 return retValue; 313 } // execute() 314 315 /** 316 * <p>{@summary Parses the given SQL script.}</p> 317 * <p>Basically, the method splits the provided String into the single 318 * commands (they are separated by semicolon ";"), and returns 319 * them as a 320 * {@link List} 321 * that can be used with 322 * {@link #execute(Connection, List)}.</p> 323 * <p>Text between "--" (two hyphens, &#x002D) and the end 324 * of the line will be seen as a comment and is ignored; same for comments 325 * between "/*" and "*/". 326 * 327 * @param script The script. 328 * @return The separated commands. 329 */ 330 @SuppressWarnings( {"OverlyLongMethod", "OverlyComplexMethod"} ) 331 @API( status = STABLE, since = "0.0.1" ) 332 public static final List<String> parseSQLScript( final CharSequence script ) 333 { 334 //---* Get the single commands *--------------------------------------- 335 final var chars = requireNonNullArgument( script, "script" ).toString().toCharArray(); 336 final var sourceLen = chars.length; 337 final var buffer = new char [sourceLen]; 338 var targetLen = 0; 339 var isSingleQuoteString = false; 340 var isDoubleQuoteString = false; 341 var isBlockComment = false; 342 var isLineComment = false; 343 var lastChar = ' '; 344 345 ScanLoop: for( final var currentChar : chars ) 346 { 347 if( (targetLen == 0) && isWhitespace( currentChar ) ) continue ScanLoop; 348 349 AnalyzeSwitch: 350 //noinspection EnhancedSwitchMigration 351 switch( currentChar ) 352 { 353 case '-': 354 { 355 if( isBlockComment || isLineComment ) break AnalyzeSwitch; 356 if( isSingleQuoteString || isDoubleQuoteString ) 357 { 358 buffer [targetLen++] = lastChar; 359 break AnalyzeSwitch; 360 } 361 if( lastChar == currentChar ) 362 { 363 isLineComment = true; 364 lastChar = ' '; 365 continue ScanLoop; 366 } 367 buffer [targetLen++] = lastChar; 368 break AnalyzeSwitch; 369 } 370 371 case '\n': 372 { 373 if( isBlockComment ) continue ScanLoop; 374 if( isLineComment ) 375 { 376 isLineComment = false; 377 lastChar = ' '; 378 continue ScanLoop; 379 } 380 if( isSingleQuoteString || isDoubleQuoteString ) 381 { 382 buffer [targetLen++] = lastChar; 383 lastChar = ' '; 384 continue ScanLoop; 385 } 386 if( isWhitespace( lastChar ) ) 387 { 388 continue ScanLoop; 389 } 390 if( lastChar == ';' ) 391 { 392 buffer [targetLen++] = ';'; 393 break AnalyzeSwitch; 394 } 395 buffer [targetLen++] = lastChar; 396 lastChar = ' '; 397 continue ScanLoop; 398 } 399 400 case '\'': 401 { 402 if( isBlockComment || isLineComment ) break AnalyzeSwitch; 403 if( !isDoubleQuoteString ) 404 { 405 if( !isSingleQuoteString ) 406 { 407 isSingleQuoteString = true; 408 } 409 else if( lastChar != '\\' ) 410 { 411 isSingleQuoteString = false; 412 } 413 } 414 buffer [targetLen++] = lastChar; 415 break AnalyzeSwitch; 416 } 417 418 case '"': 419 { 420 if( isBlockComment || isLineComment ) break AnalyzeSwitch; 421 if( !isSingleQuoteString ) 422 { 423 if( !isDoubleQuoteString ) 424 { 425 isDoubleQuoteString = true; 426 } 427 else if( lastChar != '\\' ) 428 { 429 isDoubleQuoteString = false; 430 } 431 } 432 buffer [targetLen++] = lastChar; 433 break AnalyzeSwitch; 434 } 435 436 case '/': 437 { 438 if( isBlockComment ) 439 { 440 if( lastChar == '*' ) 441 { 442 isBlockComment = false; 443 lastChar = ' '; 444 continue ScanLoop; 445 } 446 break AnalyzeSwitch; 447 } 448 if( isLineComment ) break AnalyzeSwitch; 449 buffer [targetLen++] = lastChar; 450 break AnalyzeSwitch; 451 } 452 453 case '*': 454 { 455 if( isBlockComment || isLineComment ) break AnalyzeSwitch; 456 if( isSingleQuoteString || isDoubleQuoteString ) 457 { 458 buffer [targetLen++] = lastChar; 459 break AnalyzeSwitch; 460 } 461 if( lastChar == '/' ) 462 { 463 isBlockComment = true; 464 lastChar = ' '; 465 continue ScanLoop; 466 } 467 buffer [targetLen++] = lastChar; 468 break AnalyzeSwitch; 469 } 470 471 default: 472 { 473 if( isBlockComment || isLineComment ) break AnalyzeSwitch; 474 if( isWhitespace( currentChar ) ) 475 { 476 if( !isWhitespace( lastChar ) ) 477 { 478 buffer [targetLen++] = lastChar; 479 } 480 break AnalyzeSwitch; 481 } 482 if( isSingleQuoteString || isDoubleQuoteString ) 483 { 484 buffer [targetLen++] = lastChar; 485 break AnalyzeSwitch; 486 } 487 if( currentChar == ';' ) 488 { 489 buffer [targetLen++] = lastChar; 490 buffer [targetLen++] = ';'; 491 lastChar = '\n'; 492 continue ScanLoop; 493 } 494 buffer [targetLen++] = lastChar; 495 break AnalyzeSwitch; 496 } 497 } // AnalyzeSwitch: 498 499 lastChar = currentChar; 500 } // ScanLoop: 501 502 //---* Compose the return value *-------------------------------------- 503 final List<String> retValue = targetLen > 1 ? List.of( splitString( new String( buffer, 1, targetLen ).trim(), '\n' ) ) : List.of(); 504 505 //---* Done *---------------------------------------------------------- 506 return retValue; 507 } // parseSQLScript() 508 509 /** 510 * <p>{@summary This method checks whether the given connection is not 511 * {@code null} and that it is open.} Otherwise it will throw a 512 * {@link ValidationException}.</p> 513 * <p>This method will test the method by calling 514 * {@link Connection#isValid(int)}.</p> 515 * 516 * @param connection The connection to check; can be {@code null}. 517 * @param name The name of the argument; this is used for the error 518 * message. 519 * @param validationTimeout The validation timeout in seconds, with -1 520 * for no validation (in this case, only 521 * {@link Connection#isClosed()} 522 * is called); a value of 0 means no timeout. 523 * @return The value if the validation succeeds. 524 * @throws ValidationException The connection is closed or otherwise not 525 * valid. 526 * @throws NullArgumentException {@code name} or the connection is 527 * {@code null}. 528 * @throws EmptyArgumentException {@code name} is the empty String. 529 * 530 * @since 0.4.1 531 */ 532 @API( status = STABLE, since = "0.4.1" ) 533 public static final Connection requireValidConnectionArgument( final Connection connection, final String name, final int validationTimeout ) throws ValidationException 534 { 535 final var message = "The connection in argument '%s' is not valid".formatted( requireNotEmptyArgument( name, "name" ) ); 536 requireNonNullArgument( connection, "connection" ); 537 538 try 539 { 540 final var validationResult = (validationTimeout < 0) ? !connection.isClosed() : connection.isValid( validationTimeout ); 541 if( !validationResult ) throw new ValidationException( message ); 542 } 543 catch( final SQLException e ) 544 { 545 throw new ValidationException( message, e ); 546 } 547 548 //---* Done *---------------------------------------------------------- 549 return connection; 550 } // requireValidConnectionArgument() 551 552 /** 553 * Reads the records from the given 554 * {@link ResultSet} 555 * to a 556 * {@link Map} 557 * that uses the 558 * {@linkplain java.sql.ResultSetMetaData#getColumnLabel(int) column labels} 559 * as key and stores these to a 560 * {@link List}. 561 * 562 * @param resultSet The result set to dump. 563 * @return The contents of the result set; maybe empty, but will never be 564 * {@code null}. 565 * @throws SQLException Something went wrong when reading the result 566 * set. 567 * 568 * @since 0.4.1 569 */ 570 @API( status = STABLE, since = "0.4.1" ) 571 public static final List<Map<String,Object>> resultSetToMap( final ResultSet resultSet ) throws SQLException 572 { 573 final var metaData = requireNonNullArgument( resultSet, "resultSet" ).getMetaData(); 574 final var columnCount = metaData.getColumnCount(); 575 final var labels = new String [columnCount]; 576 for( var i = 0; i < columnCount; ++i ) labels [i] = metaData.getColumnLabel( i + 1 ); 577 final Collection<Map<String,Object>> buffer = new ArrayList<>(); 578 579 while( resultSet.next() ) 580 { 581 final Map<String,Object> record = new HashMap<>(); 582 for( var i = 0; i < columnCount; ++i ) 583 { 584 final var value = resultSet.getObject( i + 1 ); 585 record.put( labels [i], resultSet.wasNull() ? null : value ); 586 } 587 buffer.add( Map.copyOf( record ) ); 588 } 589 590 final var retValue = List.copyOf( buffer ); 591 592 //---* Done *---------------------------------------------------------- 593 return retValue; 594 } // resultSetToMap() 595 596 /** 597 * <p>{@summary Returns a 598 * {@link Stream} 599 * implementation for the given 600 * {@link ResultSet}.}</p> 601 * <p>Several operations on a {@code ResultSet} instance will not work 602 * when called on the streamed instance.</p> 603 * <p>When one of the operations that would move the cursor is called on 604 * the {@code ResultSet} instance that is pushed into an operation on the 605 * stream, an 606 * {@link UnsupportedOperationException} 607 * is thrown; this affects the methods</p> 608 * <ul> 609 * <li>{@link ResultSet#absolute(int)}</li> 610 * <li>{@link ResultSet#afterLast()}</li> 611 * <li>{@link ResultSet#beforeFirst()}</li> 612 * <li>{@link ResultSet#first()}</li> 613 * <li>{@link ResultSet#last()}</li> 614 * <li>{@link ResultSet#moveToCurrentRow()}</li> 615 * <li>{@link ResultSet#moveToInsertRow()}</li> 616 * <li>{@link ResultSet#next()}</li> 617 * <li>{@link ResultSet#previous()}</li> 618 * <li>{@link ResultSet#relative(int)}</li> 619 * </ul> 620 * <p>as well as the following methods:</p> 621 * <ul> 622 * <li>{@link ResultSet#close()}</li> 623 * <li>{@link ResultSet#deleteRow()}</li> 624 * <li>{@link ResultSet#insertRow()}</li> 625 * <li>{@link ResultSet#setFetchDirection(int)}</li> 626 * <li>{@link ResultSet#setFetchSize(int)}</li> 627 * </ul> 628 * <p>A call to 629 * {@link Stream#close()} 630 * does not close the 631 * {@link ResultSet}!</p> 632 * 633 * @param resultSet The result set to stream on. 634 * @return The stream. 635 */ 636 @API( status = STABLE, since = "0.0.1" ) 637 public static final Stream<ResultSet> stream( final ResultSet resultSet ) 638 { 639 final var spliterator = new ResultSetSpliterator( resultSet ); 640 final var retValue = StreamSupport.stream( spliterator, false ); 641 642 //---* Done *---------------------------------------------------------- 643 return retValue; 644 } // stream() 645} 646// class DatabaseUtils 647 648/* 649 * End of File 650 */