001package io.ebean; 002 003import io.ebean.search.Match; 004import io.ebean.search.MultiMatch; 005import io.ebean.search.TextCommonTerms; 006import io.ebean.search.TextQueryString; 007import io.ebean.search.TextSimple; 008 009import javax.annotation.Nonnull; 010import javax.annotation.Nullable; 011import javax.persistence.NonUniqueResultException; 012import java.sql.Timestamp; 013import java.util.Collection; 014import java.util.List; 015import java.util.Map; 016import java.util.Optional; 017import java.util.Set; 018import java.util.function.Consumer; 019import java.util.function.Predicate; 020 021/** 022 * List of Expressions that make up a where or having clause. 023 * <p> 024 * An ExpressionList is returned from {@link Query#where()}. 025 * </p> 026 * <p> 027 * The ExpressionList has a list of convenience methods that create the standard 028 * expressions and add them to this list. 029 * </p> 030 * <p> 031 * The ExpressionList also duplicates methods that are found on the Query such 032 * as findList() and orderBy(). The purpose of these methods is provide a fluid 033 * API. The upside of this approach is that you can build and execute a query 034 * via chained methods. The down side is that this ExpressionList object has 035 * more methods than you would initially expect (the ones duplicated from 036 * Query). 037 * </p> 038 * 039 * @see Query#where() 040 */ 041public interface ExpressionList<T> { 042 043 /** 044 * Return the query that owns this expression list. 045 * <p> 046 * This is a convenience method solely to support a fluid API where the 047 * methods are chained together. Adding expressions returns this expression 048 * list and this method can be used after that to return back the original 049 * query so that further things can be added to it. 050 * </p> 051 */ 052 Query<T> query(); 053 054 /** 055 * Set the order by clause replacing the existing order by clause if there is 056 * one. 057 * <p> 058 * This follows SQL syntax using commas between each property with the 059 * optional asc and desc keywords representing ascending and descending order 060 * respectively. 061 * </p> 062 * <p> 063 * This is EXACTLY the same as {@link #orderBy(String)}. 064 * </p> 065 */ 066 Query<T> order(String orderByClause); 067 068 /** 069 * Return the OrderBy so that you can append an ascending or descending 070 * property to the order by clause. 071 * <p> 072 * This will never return a null. If no order by clause exists then an 'empty' 073 * OrderBy object is returned. 074 * </p> 075 */ 076 OrderBy<T> order(); 077 078 /** 079 * Return the OrderBy so that you can append an ascending or descending 080 * property to the order by clause. 081 * <p> 082 * This will never return a null. If no order by clause exists then an 'empty' 083 * OrderBy object is returned. 084 * </p> 085 */ 086 OrderBy<T> orderBy(); 087 088 /** 089 * Add an orderBy clause to the query. 090 * 091 * @see Query#orderBy(String) 092 */ 093 Query<T> orderBy(String orderBy); 094 095 /** 096 * Add an orderBy clause to the query. 097 * 098 * @see Query#orderBy(String) 099 */ 100 Query<T> setOrderBy(String orderBy); 101 102 /** 103 * Apply the path properties to the query replacing the select and fetch clauses. 104 */ 105 Query<T> apply(FetchPath fetchPath); 106 107 /** 108 * Perform an 'As of' query using history tables to return the object graph 109 * as of a time in the past. 110 * <p> 111 * To perform this query the DB must have underlying history tables. 112 * </p> 113 * 114 * @param asOf the date time in the past at which you want to view the data 115 */ 116 Query<T> asOf(Timestamp asOf); 117 118 /** 119 * Execute the query against the draft set of tables. 120 */ 121 Query<T> asDraft(); 122 123 /** 124 * Convert the query to a DTO bean query. 125 * <p> 126 * We effectively use the underlying ORM query to build the SQL and then execute 127 * and map it into DTO beans. 128 */ 129 <D> DtoQuery<D> asDto(Class<D> dtoClass); 130 131 /** 132 * Return the underlying query as an UpdateQuery. 133 * <p> 134 * Typically this is used with query beans to covert a query bean 135 * query into an UpdateQuery like the examples below. 136 * </p> 137 * 138 * <pre>{@code 139 * 140 * int rowsUpdated = new QCustomer() 141 * .name.startsWith("Rob") 142 * .asUpdate() 143 * .set("active", false) 144 * .update();; 145 * 146 * }</pre> 147 * 148 * <pre>{@code 149 * 150 * int rowsUpdated = new QContact() 151 * .notes.note.startsWith("Make Inactive") 152 * .email.endsWith("@foo.com") 153 * .customer.id.equalTo(42) 154 * .asUpdate() 155 * .set("inactive", true) 156 * .setRaw("email = lower(email)") 157 * .update(); 158 * 159 * }</pre> 160 */ 161 UpdateQuery<T> asUpdate(); 162 163 /** 164 * Execute using "for update" clause which results in the DB locking the record. 165 */ 166 Query<T> forUpdate(); 167 168 /** 169 * Execute using "for update" clause with No Wait option. 170 * <p> 171 * This is typically a Postgres and Oracle only option at this stage. 172 * </p> 173 */ 174 Query<T> forUpdateNoWait(); 175 176 /** 177 * Execute using "for update" clause with Skip Locked option. 178 * <p> 179 * This is typically a Postgres and Oracle only option at this stage. 180 * </p> 181 */ 182 Query<T> forUpdateSkipLocked(); 183 184 /** 185 * Execute the query including soft deleted rows. 186 */ 187 Query<T> setIncludeSoftDeletes(); 188 189 /** 190 * Execute as a delete query deleting the 'root level' beans that match the predicates 191 * in the query. 192 * <p> 193 * Note that if the query includes joins then the generated delete statement may not be 194 * optimal depending on the database platform. 195 * </p> 196 * 197 * @return the number of rows that were deleted. 198 */ 199 int delete(); 200 201 /** 202 * Execute as a delete query deleting the 'root level' beans that match the predicates 203 * in the query. 204 * <p> 205 * Note that if the query includes joins then the generated delete statement may not be 206 * optimal depending on the database platform. 207 * </p> 208 * 209 * @return the number of rows that were deleted. 210 */ 211 int delete(Transaction transaction); 212 213 /** 214 * Execute as a update query. 215 * 216 * @return the number of rows that were updated. 217 * @see UpdateQuery 218 */ 219 int update(); 220 221 /** 222 * Execute as a update query with the given transaction. 223 * 224 * @return the number of rows that were updated. 225 * @see UpdateQuery 226 */ 227 int update(Transaction transaction); 228 229 /** 230 * Execute the query returning true if a row is found. 231 * <p> 232 * The query is executed using max rows of 1 and will only select the id property. 233 * This method is really just a convenient way to optimise a query to perform a 234 * 'does a row exist in the db' check. 235 * </p> 236 * 237 * <h2>Example:</h2> 238 * <pre>{@code 239 * 240 * boolean userExists = query().where().eq("email", "rob@foo.com").exists(); 241 * 242 * }</pre> 243 * 244 * <h2>Example using a query bean:</h2> 245 * <pre>{@code 246 * 247 * boolean userExists = new QContact().email.equalTo("rob@foo.com").exists(); 248 * 249 * }</pre> 250 * 251 * @return True if the query finds a matching row in the database 252 */ 253 boolean exists(); 254 255 /** 256 * Execute the query iterating over the results. 257 * 258 * @see Query#findIterate() 259 */ 260 QueryIterator<T> findIterate(); 261 262 /** 263 * Execute the query process the beans one at a time. 264 * 265 * @see Query#findEach(Consumer) 266 */ 267 void findEach(Consumer<T> consumer); 268 269 /** 270 * Execute the query processing the beans one at a time with the ability to 271 * stop processing before reading all the beans. 272 * 273 * @see Query#findEachWhile(Predicate) 274 */ 275 void findEachWhile(Predicate<T> consumer); 276 277 /** 278 * Execute the query returning a list. 279 * 280 * @see Query#findList() 281 */ 282 @Nonnull 283 List<T> findList(); 284 285 /** 286 * Execute the query returning the list of Id's. 287 * 288 * @see Query#findIds() 289 */ 290 @Nonnull 291 <A> List<A> findIds(); 292 293 /** 294 * Return the count of entities this query should return. 295 * <p> 296 * This is the number of 'top level' or 'root level' entities. 297 * </p> 298 */ 299 int findCount(); 300 301 /** 302 * Execute the query returning a set. 303 * 304 * @see Query#findSet() 305 */ 306 @Nonnull 307 Set<T> findSet(); 308 309 /** 310 * Execute the query returning a map. 311 * 312 * @see Query#findMap() 313 */ 314 @Nonnull 315 <K> Map<K, T> findMap(); 316 317 /** 318 * Execute the query returning a list of values for a single property. 319 * 320 * <h3>Example 1:</h3> 321 * <pre>{@code 322 * 323 * List<String> names = 324 * DB.find(Customer.class) 325 * .select("name") 326 * .orderBy().asc("name") 327 * .findSingleAttributeList(); 328 * 329 * }</pre> 330 * 331 * <h3>Example 2:</h3> 332 * <pre>{@code 333 * 334 * List<String> names = 335 * DB.find(Customer.class) 336 * .setDistinct(true) 337 * .select("name") 338 * .where().eq("status", Customer.Status.NEW) 339 * .orderBy().asc("name") 340 * .setMaxRows(100) 341 * .findSingleAttributeList(); 342 * 343 * }</pre> 344 * 345 * @return the list of values for the selected property 346 */ 347 @Nonnull 348 <A> List<A> findSingleAttributeList(); 349 350 /** 351 * Execute a query returning a single value of a single property/column. 352 * <pre>{@code 353 * 354 * String name = 355 * DB.find(Customer.class) 356 * .select("name") 357 * .where().eq("id", 42) 358 * .findSingleAttribute(); 359 * 360 * }</pre> 361 */ 362 default <A> A findSingleAttribute() { 363 List<A> list = findSingleAttributeList(); 364 return !list.isEmpty() ? list.get(0) : null; 365 } 366 367 /** 368 * Execute the query returning a single bean or null (if no matching 369 * bean is found). 370 * <p> 371 * If more than 1 row is found for this query then a NonUniqueResultException is 372 * thrown. 373 * </p> 374 * 375 * @throws NonUniqueResultException if more than one result was found 376 * @see Query#findOne() 377 */ 378 @Nullable 379 T findOne(); 380 381 /** 382 * Execute the query returning an optional bean. 383 */ 384 @Nonnull 385 Optional<T> findOneOrEmpty(); 386 387 /** 388 * Execute find row count query in a background thread. 389 * <p> 390 * This returns a Future object which can be used to cancel, check the 391 * execution status (isDone etc) and get the value (with or without a 392 * timeout). 393 * </p> 394 * 395 * @return a Future object for the row count query 396 */ 397 @Nonnull 398 FutureRowCount<T> findFutureCount(); 399 400 /** 401 * Execute find Id's query in a background thread. 402 * <p> 403 * This returns a Future object which can be used to cancel, check the 404 * execution status (isDone etc) and get the value (with or without a 405 * timeout). 406 * </p> 407 * 408 * @return a Future object for the list of Id's 409 */ 410 @Nonnull 411 FutureIds<T> findFutureIds(); 412 413 /** 414 * Execute find list query in a background thread. 415 * <p> 416 * This returns a Future object which can be used to cancel, check the 417 * execution status (isDone etc) and get the value (with or without a 418 * timeout). 419 * </p> 420 * 421 * @return a Future object for the list result of the query 422 */ 423 @Nonnull 424 FutureList<T> findFutureList(); 425 426 /** 427 * Return a PagedList for this query using firstRow and maxRows. 428 * <p> 429 * The benefit of using this over findList() is that it provides functionality to get the 430 * total row count etc. 431 * </p> 432 * <p> 433 * If maxRows is not set on the query prior to calling findPagedList() then a 434 * PersistenceException is thrown. 435 * </p> 436 * <pre>{@code 437 * 438 * PagedList<Order> pagedList = DB.find(Order.class) 439 * .setFirstRow(50) 440 * .setMaxRows(20) 441 * .findPagedList(); 442 * 443 * // fetch the total row count in the background 444 * pagedList.loadRowCount(); 445 * 446 * List<Order> orders = pagedList.getList(); 447 * int totalRowCount = pagedList.getTotalRowCount(); 448 * 449 * }</pre> 450 * 451 * @return The PagedList 452 * @see Query#findPagedList() 453 */ 454 @Nonnull 455 PagedList<T> findPagedList(); 456 457 /** 458 * Return versions of a @History entity bean. 459 * <p> 460 * Generally this query is expected to be a find by id or unique predicates query. 461 * It will execute the query against the history returning the versions of the bean. 462 * </p> 463 */ 464 @Nonnull 465 List<Version<T>> findVersions(); 466 467 /** 468 * Return versions of a @History entity bean between the 2 timestamps. 469 * <p> 470 * Generally this query is expected to be a find by id or unique predicates query. 471 * It will execute the query against the history returning the versions of the bean. 472 * </p> 473 */ 474 @Nonnull 475 List<Version<T>> findVersionsBetween(Timestamp start, Timestamp end); 476 477 /** 478 * Add some filter predicate expressions to the many property. 479 */ 480 @Nonnull 481 ExpressionList<T> filterMany(String prop); 482 483 /** 484 * Specify specific properties to fetch on the main/root bean (aka partial 485 * object). 486 * 487 * @see Query#select(String) 488 */ 489 Query<T> select(String properties); 490 491 /** 492 * Apply the fetchGroup which defines what part of the object graph to load. 493 */ 494 Query<T> select(FetchGroup<T> fetchGroup); 495 496 /** 497 * Set whether this query uses DISTINCT. 498 * <p> 499 * The select() clause MUST be specified when setDistinct(true) is set. The reason for this is that 500 * generally ORM queries include the "id" property and this doesn't make sense for distinct queries. 501 * </p> 502 * <pre>{@code 503 * 504 * List<Customer> customers = 505 * DB.find(Customer.class) 506 * .setDistinct(true) 507 * .select("name") // only select the customer name 508 * .findList(); 509 * 510 * }</pre> 511 */ 512 Query<T> setDistinct(boolean distinct); 513 514 /** 515 * Set the index(es) to search for a document store which uses partitions. 516 * <p> 517 * For example, when executing a query against ElasticSearch with daily indexes we can 518 * explicitly specify the indexes to search against. 519 * </p> 520 * 521 * @param indexName The index or indexes to search against 522 * @return This query 523 * @see Query#setDocIndexName(String) 524 */ 525 Query<T> setDocIndexName(String indexName); 526 527 /** 528 * Set the first row to fetch. 529 * 530 * @see Query#setFirstRow(int) 531 */ 532 Query<T> setFirstRow(int firstRow); 533 534 /** 535 * Set the maximum number of rows to fetch. 536 * 537 * @see Query#setMaxRows(int) 538 */ 539 Query<T> setMaxRows(int maxRows); 540 541 /** 542 * Set the name of the property which values become the key of a map. 543 * 544 * @see Query#setMapKey(String) 545 */ 546 Query<T> setMapKey(String mapKey); 547 548 /** 549 * Set to true when this query should use the bean cache. 550 * <p> 551 * This is now the same as setUseBeanCache(CacheMode.ON) and will be deprecated. 552 * </p> 553 * 554 * @see Query#setUseCache(boolean) 555 */ 556 Query<T> setUseCache(boolean useCache); 557 558 /** 559 * Set the mode to use the bean cache when executing this query. 560 * 561 * @see Query#setBeanCacheMode(CacheMode) 562 */ 563 Query<T> setBeanCacheMode(CacheMode beanCacheMode); 564 565 /** 566 * Set the {@link CacheMode} to use the query cache for executing this query. 567 * 568 * @see Query#setUseQueryCache(boolean) 569 */ 570 Query<T> setUseQueryCache(CacheMode useCache); 571 572 /** 573 * Extended version for setDistinct in conjunction with "findSingleAttributeList"; 574 * <pre>{@code 575 * 576 * List<CountedValue<Order.Status>> orderStatusCount = 577 * 578 * DB.find(Order.class) 579 * .select("status") 580 * .where() 581 * .gt("orderDate", LocalDate.now().minusMonths(3)) 582 * 583 * // fetch as single attribute with a COUNT 584 * .setCountDistinct(CountDistinctOrder.COUNT_DESC_ATTR_ASC) 585 * .findSingleAttributeList(); 586 * 587 * for (CountedValue<Order.Status> entry : orderStatusCount) { 588 * System.out.println(" count:" + entry.getCount()+" orderStatus:" + entry.getValue() ); 589 * } 590 * 591 * // produces 592 * 593 * count:3 orderStatus:NEW 594 * count:1 orderStatus:SHIPPED 595 * count:1 orderStatus:COMPLETE 596 * 597 * }</pre> 598 */ 599 Query<T> setCountDistinct(CountDistinctOrder orderBy); 600 601 /** 602 * Calls {@link #setUseQueryCache(CacheMode)} with <code>ON</code> or <code>OFF</code>. 603 * 604 * @see Query#setUseQueryCache(CacheMode) 605 */ 606 default Query<T> setUseQueryCache(boolean enabled) { 607 return setUseQueryCache(enabled ? CacheMode.ON : CacheMode.OFF); 608 } 609 610 /** 611 * Set to true if this query should execute against the doc store. 612 * <p> 613 * When setting this you may also consider disabling lazy loading. 614 * </p> 615 */ 616 Query<T> setUseDocStore(boolean useDocsStore); 617 618 /** 619 * Set true if you want to disable lazy loading. 620 * <p> 621 * That is, once the object graph is returned further lazy loading is disabled. 622 * </p> 623 */ 624 Query<T> setDisableLazyLoading(boolean disableLazyLoading); 625 626 /** 627 * Disable read auditing for this query. 628 * <p> 629 * This is intended to be used when the query is not a user initiated query and instead 630 * part of the internal processing in an application to load a cache or document store etc. 631 * In these cases we don't want the query to be part of read auditing. 632 * </p> 633 */ 634 Query<T> setDisableReadAuditing(); 635 636 /** 637 * Set a label on the query (to help identify query execution statistics). 638 */ 639 Query<T> setLabel(String label); 640 641 /** 642 * Add expressions to the having clause. 643 * <p> 644 * The having clause is only used for queries based on raw sql (via SqlSelect 645 * annotation etc). 646 * </p> 647 */ 648 ExpressionList<T> having(); 649 650 /** 651 * Add another expression to the where clause. 652 */ 653 ExpressionList<T> where(); 654 655 /** 656 * Path exists - for the given path in a JSON document. 657 * <pre>{@code 658 * 659 * where().jsonExists("content", "path.other") 660 * 661 * }</pre> 662 * 663 * @param propertyName the property that holds a JSON document 664 * @param path the nested path in the JSON document in dot notation 665 */ 666 ExpressionList<T> jsonExists(String propertyName, String path); 667 668 /** 669 * Path does not exist - for the given path in a JSON document. 670 * <pre>{@code 671 * 672 * where().jsonNotExists("content", "path.other") 673 * 674 * }</pre> 675 * 676 * @param propertyName the property that holds a JSON document 677 * @param path the nested path in the JSON document in dot notation 678 */ 679 ExpressionList<T> jsonNotExists(String propertyName, String path); 680 681 /** 682 * Equal to expression for the value at the given path in the JSON document. 683 * <pre>{@code 684 * 685 * where().jsonEqualTo("content", "path.other", 34) 686 * 687 * }</pre> 688 * 689 * @param propertyName the property that holds a JSON document 690 * @param path the nested path in the JSON document in dot notation 691 * @param value the value used to test against the document path's value 692 */ 693 ExpressionList<T> jsonEqualTo(String propertyName, String path, Object value); 694 695 /** 696 * Not Equal to - for the given path in a JSON document. 697 * <pre>{@code 698 * 699 * where().jsonNotEqualTo("content", "path.other", 34) 700 * 701 * }</pre> 702 * 703 * @param propertyName the property that holds a JSON document 704 * @param path the nested path in the JSON document in dot notation 705 * @param value the value used to test against the document path's value 706 */ 707 ExpressionList<T> jsonNotEqualTo(String propertyName, String path, Object value); 708 709 /** 710 * Greater than - for the given path in a JSON document. 711 * <pre>{@code 712 * 713 * where().jsonGreaterThan("content", "path.other", 34) 714 * 715 * }</pre> 716 */ 717 ExpressionList<T> jsonGreaterThan(String propertyName, String path, Object value); 718 719 /** 720 * Greater than or equal to - for the given path in a JSON document. 721 * <pre>{@code 722 * 723 * where().jsonGreaterOrEqual("content", "path.other", 34) 724 * 725 * }</pre> 726 */ 727 ExpressionList<T> jsonGreaterOrEqual(String propertyName, String path, Object value); 728 729 /** 730 * Less than - for the given path in a JSON document. 731 * <pre>{@code 732 * 733 * where().jsonLessThan("content", "path.other", 34) 734 * 735 * }</pre> 736 */ 737 ExpressionList<T> jsonLessThan(String propertyName, String path, Object value); 738 739 /** 740 * Less than or equal to - for the given path in a JSON document. 741 * <pre>{@code 742 * 743 * where().jsonLessOrEqualTo("content", "path.other", 34) 744 * 745 * }</pre> 746 */ 747 ExpressionList<T> jsonLessOrEqualTo(String propertyName, String path, Object value); 748 749 /** 750 * Between - for the given path in a JSON document. 751 * <pre>{@code 752 * 753 * where().jsonBetween("content", "orderDate", lowerDateTime, upperDateTime) 754 * 755 * }</pre> 756 */ 757 ExpressionList<T> jsonBetween(String propertyName, String path, Object lowerValue, Object upperValue); 758 759 /** 760 * Add an Expression to the list. 761 */ 762 ExpressionList<T> add(Expression expr); 763 764 /** 765 * Add a list of Expressions to this ExpressionList.s 766 */ 767 ExpressionList<T> addAll(ExpressionList<T> exprList); 768 769 /** 770 * Equal To - property is equal to a given value. 771 */ 772 ExpressionList<T> eq(String propertyName, Object value); 773 774 /** 775 * Equal To or Null - property is equal to a given value or null. 776 */ 777 ExpressionList<T> eqOrNull(String propertyName, Object value); 778 779 /** 780 * Not Equal To - property not equal to the given value. 781 */ 782 ExpressionList<T> ne(String propertyName, Object value); 783 784 /** 785 * Case Insensitive Equal To - property equal to the given value (typically 786 * using a lower() function to make it case insensitive). 787 */ 788 ExpressionList<T> ieq(String propertyName, String value); 789 790 /** 791 * Case Insensitive Not Equal To - property not equal to the given value (typically 792 * using a lower() function to make it case insensitive). 793 */ 794 ExpressionList<T> ine(String propertyName, String value); 795 796 /** 797 * Value in Range between 2 properties. 798 * 799 * <pre>{@code 800 * 801 * .startDate.inRangeWith(endDate, now) 802 * 803 * // which equates to 804 * startDate <= now and (endDate > now or endDate is null) 805 * 806 * }</pre> 807 * 808 * <p> 809 * This is a convenience expression combining a number of simple expressions. 810 * The most common use of this could be called "effective dating" where 2 date or 811 * timestamp columns represent the date range in which 812 */ 813 ExpressionList<T> inRangeWith(String lowProperty, String highProperty, Object value); 814 815 /** 816 * In Range - property >= value1 and property < value2. 817 * <p> 818 * Unlike Between inRange is "half open" and usually more useful for use with dates or timestamps. 819 * </p> 820 */ 821 ExpressionList<T> inRange(String propertyName, Object value1, Object value2); 822 823 /** 824 * Between - property between the two given values. 825 */ 826 ExpressionList<T> between(String propertyName, Object value1, Object value2); 827 828 /** 829 * Between - value between the two properties. 830 */ 831 ExpressionList<T> betweenProperties(String lowProperty, String highProperty, Object value); 832 833 /** 834 * Greater Than - property greater than the given value. 835 */ 836 ExpressionList<T> gt(String propertyName, Object value); 837 838 /** 839 * Greater Than or Null - property greater than the given value or null. 840 */ 841 ExpressionList<T> gtOrNull(String propertyName, Object value); 842 843 /** 844 * Greater Than or Equal to - property greater than or equal to the given 845 * value. 846 */ 847 ExpressionList<T> ge(String propertyName, Object value); 848 849 /** 850 * Less Than - property less than the given value. 851 */ 852 ExpressionList<T> lt(String propertyName, Object value); 853 854 /** 855 * Less Than or Null - property less than the given value or null. 856 */ 857 ExpressionList<T> ltOrNull(String propertyName, Object value); 858 859 /** 860 * Less Than or Equal to - property less than or equal to the given value. 861 */ 862 ExpressionList<T> le(String propertyName, Object value); 863 864 /** 865 * Is Null - property is null. 866 */ 867 ExpressionList<T> isNull(String propertyName); 868 869 /** 870 * Is Not Null - property is not null. 871 */ 872 ExpressionList<T> isNotNull(String propertyName); 873 874 /** 875 * A "Query By Example" type of expression. 876 * <p> 877 * Pass in an example entity and for each non-null scalar properties an 878 * expression is added. 879 * </p> 880 * <p> 881 * By Default this case sensitive, will ignore numeric zero values and will 882 * use a Like for string values (you must put in your own wildcards). 883 * </p> 884 * <p> 885 * To get control over the options you can create an ExampleExpression and set 886 * those options such as case insensitive etc. 887 * </p> 888 * <pre>{@code 889 * 890 * // create an example bean and set the properties 891 * // with the query parameters you want 892 * Customer example = new Customer(); 893 * example.setName("Rob%"); 894 * example.setNotes("%something%"); 895 * 896 * List<Customer> list = 897 * DB.find(Customer.class) 898 * .where().exampleLike(example) 899 * .findList(); 900 * 901 * }</pre> 902 * <p> 903 * Similarly you can create an ExampleExpression 904 * </p> 905 * <pre>{@code 906 * 907 * Customer example = new Customer(); 908 * example.setName("Rob%"); 909 * example.setNotes("%something%"); 910 * 911 * // create a ExampleExpression with more control 912 * ExampleExpression qbe = new ExampleExpression(example, true, LikeType.EQUAL_TO).includeZeros(); 913 * 914 * List<Customer> list = DB.find(Customer.class).where().add(qbe).findList(); 915 * 916 * }</pre> 917 */ 918 ExpressionList<T> exampleLike(Object example); 919 920 /** 921 * Case insensitive version of {@link #exampleLike(Object)} 922 */ 923 ExpressionList<T> iexampleLike(Object example); 924 925 /** 926 * Like - property like value where the value contains the SQL wild card 927 * characters % (percentage) and _ (underscore). 928 */ 929 ExpressionList<T> like(String propertyName, String value); 930 931 /** 932 * Case insensitive Like - property like value where the value contains the 933 * SQL wild card characters % (percentage) and _ (underscore). Typically uses 934 * a lower() function to make the expression case insensitive. 935 */ 936 ExpressionList<T> ilike(String propertyName, String value); 937 938 /** 939 * Starts With - property like value%. 940 */ 941 ExpressionList<T> startsWith(String propertyName, String value); 942 943 /** 944 * Case insensitive Starts With - property like value%. Typically uses a 945 * lower() function to make the expression case insensitive. 946 */ 947 ExpressionList<T> istartsWith(String propertyName, String value); 948 949 /** 950 * Ends With - property like %value. 951 */ 952 ExpressionList<T> endsWith(String propertyName, String value); 953 954 /** 955 * Case insensitive Ends With - property like %value. Typically uses a lower() 956 * function to make the expression case insensitive. 957 */ 958 ExpressionList<T> iendsWith(String propertyName, String value); 959 960 /** 961 * Contains - property like %value%. 962 */ 963 ExpressionList<T> contains(String propertyName, String value); 964 965 /** 966 * Case insensitive Contains - property like %value%. Typically uses a lower() 967 * function to make the expression case insensitive. 968 */ 969 ExpressionList<T> icontains(String propertyName, String value); 970 971 /** 972 * In expression using pairs of value objects. 973 */ 974 ExpressionList<T> inPairs(Pairs pairs); 975 976 /** 977 * In - using a subQuery. 978 */ 979 ExpressionList<T> in(String propertyName, Query<?> subQuery); 980 981 /** 982 * In - property has a value in the array of values. 983 */ 984 ExpressionList<T> in(String propertyName, Object... values); 985 986 /** 987 * In - property has a value in the collection of values. 988 */ 989 ExpressionList<T> in(String propertyName, Collection<?> values); 990 991 /** 992 * In where null or empty values means that no predicate is added to the query. 993 * <p> 994 * That is, only add the IN predicate if the values are not null or empty. 995 * <p> 996 * Without this we typically need to code an <code>if</code> block to only add 997 * the IN predicate if the collection is not empty like: 998 * </p> 999 * 1000 * <h3>Without inOrEmpty()</h3> 1001 * <pre>{@code 1002 * 1003 * query.where() // add some predicates 1004 * .eq("status", Status.NEW); 1005 * 1006 * if (ids != null && !ids.isEmpty()) { 1007 * query.where().in("customer.id", ids); 1008 * } 1009 * 1010 * query.findList(); 1011 * 1012 * }</pre> 1013 * 1014 * <h3>Using inOrEmpty()</h3> 1015 * <pre>{@code 1016 * 1017 * query.where() 1018 * .eq("status", Status.NEW) 1019 * .inOrEmpty("customer.id", ids) 1020 * .findList(); 1021 * 1022 * }</pre> 1023 */ 1024 ExpressionList<T> inOrEmpty(String propertyName, Collection<?> values); 1025 1026 /** 1027 * In - using a subQuery. 1028 * <p> 1029 * This is exactly the same as in() and provided due to "in" being a Kotlin keyword 1030 * (and hence to avoid the slightly ugly escaping when using in() in Kotlin) 1031 */ 1032 default ExpressionList<T> isIn(String propertyName, Query<?> subQuery) { 1033 return in(propertyName, subQuery); 1034 } 1035 1036 /** 1037 * In - property has a value in the array of values. 1038 * <p> 1039 * This is exactly the same as in() and provided due to "in" being a Kotlin keyword 1040 * (and hence to avoid the slightly ugly escaping when using in() in Kotlin) 1041 */ 1042 default ExpressionList<T> isIn(String propertyName, Object... values) { 1043 return in(propertyName, values); 1044 } 1045 1046 /** 1047 * In - property has a value in the collection of values. 1048 * <p> 1049 * This is exactly the same as in() and provided due to "in" being a Kotlin keyword 1050 * (and hence to avoid the slightly ugly escaping when using in() in Kotlin) 1051 */ 1052 default ExpressionList<T> isIn(String propertyName, Collection<?> values) { 1053 return in(propertyName, values); 1054 } 1055 1056 /** 1057 * Not In - property has a value in the array of values. 1058 */ 1059 ExpressionList<T> notIn(String propertyName, Object... values); 1060 1061 /** 1062 * Not In - property has a value in the collection of values. 1063 */ 1064 ExpressionList<T> notIn(String propertyName, Collection<?> values); 1065 1066 /** 1067 * Not In - using a subQuery. 1068 */ 1069 ExpressionList<T> notIn(String propertyName, Query<?> subQuery); 1070 1071 /** 1072 * Is empty expression for collection properties. 1073 */ 1074 ExpressionList<T> isEmpty(String propertyName); 1075 1076 /** 1077 * Is not empty expression for collection properties. 1078 */ 1079 ExpressionList<T> isNotEmpty(String propertyName); 1080 1081 /** 1082 * Exists expression 1083 */ 1084 ExpressionList<T> exists(Query<?> subQuery); 1085 1086 /** 1087 * Not exists expression 1088 */ 1089 ExpressionList<T> notExists(Query<?> subQuery); 1090 1091 /** 1092 * Id IN a list of id values. 1093 */ 1094 ExpressionList<T> idIn(Object... idValues); 1095 1096 /** 1097 * Id IN a collection of id values. 1098 */ 1099 ExpressionList<T> idIn(Collection<?> idValues); 1100 1101 /** 1102 * Id Equal to - ID property is equal to the value. 1103 */ 1104 ExpressionList<T> idEq(Object value); 1105 1106 /** 1107 * All Equal - Map containing property names and their values. 1108 * <p> 1109 * Expression where all the property names in the map are equal to the 1110 * corresponding value. 1111 * </p> 1112 * 1113 * @param propertyMap a map keyed by property names. 1114 */ 1115 ExpressionList<T> allEq(Map<String, Object> propertyMap); 1116 1117 /** 1118 * Array property contains entries with the given values. 1119 */ 1120 ExpressionList<T> arrayContains(String propertyName, Object... values); 1121 1122 /** 1123 * Array does not contain the given values. 1124 * <p> 1125 * Array support is effectively limited to Postgres at this time. 1126 * </p> 1127 */ 1128 ExpressionList<T> arrayNotContains(String propertyName, Object... values); 1129 1130 /** 1131 * Array is empty - for the given array property. 1132 * <p> 1133 * Array support is effectively limited to Postgres at this time. 1134 * </p> 1135 */ 1136 ExpressionList<T> arrayIsEmpty(String propertyName); 1137 1138 /** 1139 * Array is not empty - for the given array property. 1140 * <p> 1141 * Array support is effectively limited to Postgres at this time. 1142 * </p> 1143 */ 1144 ExpressionList<T> arrayIsNotEmpty(String propertyName); 1145 1146 /** 1147 * Add expression for ANY of the given bit flags to be set. 1148 * <pre>{@code 1149 * 1150 * where().bitwiseAny("flags", BwFlags.HAS_BULK + BwFlags.HAS_COLOUR) 1151 * 1152 * }</pre> 1153 * 1154 * @param propertyName The property that holds the flags value 1155 * @param flags The flags we are looking for 1156 */ 1157 ExpressionList<T> bitwiseAny(String propertyName, long flags); 1158 1159 /** 1160 * Add expression for ALL of the given bit flags to be set. 1161 * <pre>{@code 1162 * 1163 * where().bitwiseAll("flags", BwFlags.HAS_BULK + BwFlags.HAS_COLOUR) 1164 * 1165 * }</pre> 1166 * 1167 * @param propertyName The property that holds the flags value 1168 * @param flags The flags we are looking for 1169 */ 1170 ExpressionList<T> bitwiseAll(String propertyName, long flags); 1171 1172 /** 1173 * Add expression for the given bit flags to be NOT set. 1174 * <pre>{@code 1175 * 1176 * where().bitwiseNot("flags", BwFlags.HAS_COLOUR) 1177 * 1178 * }</pre> 1179 * 1180 * @param propertyName The property that holds the flags value 1181 * @param flags The flags we are looking for 1182 */ 1183 ExpressionList<T> bitwiseNot(String propertyName, long flags); 1184 1185 /** 1186 * Add bitwise AND expression of the given bit flags to compare with the match/mask. 1187 * <p> 1188 * <pre>{@code 1189 * 1190 * // Flags Bulk + Size = Size 1191 * // ... meaning Bulk is not set and Size is set 1192 * 1193 * long selectedFlags = BwFlags.HAS_BULK + BwFlags.HAS_SIZE; 1194 * long mask = BwFlags.HAS_SIZE; // Only Size flag set 1195 * 1196 * where().bitwiseAnd("flags", selectedFlags, mask) 1197 * 1198 * }</pre> 1199 * 1200 * @param propertyName The property that holds the flags value 1201 * @param flags The flags we are looking for 1202 */ 1203 ExpressionList<T> bitwiseAnd(String propertyName, long flags, long match); 1204 1205 /** 1206 * Add raw expression with a single parameter. 1207 * <p> 1208 * The raw expression should contain a single ? at the location of the 1209 * parameter. 1210 * </p> 1211 * <p> 1212 * When properties in the clause are fully qualified as table-column names 1213 * then they are not translated. logical property name names (not fully 1214 * qualified) will still be translated to their physical name. 1215 * </p> 1216 * <p> 1217 * <h4>Example:</h4> 1218 * <pre>{@code 1219 * 1220 * // use a database function 1221 * raw("add_days(orderDate, 10) < ?", someDate) 1222 * 1223 * }</pre> 1224 * 1225 * <h4>Subquery example:</h4> 1226 * <pre>{@code 1227 * 1228 * .raw("t0.customer_id in (select customer_id from customer_group where group_id = any(?::uuid[]))", groupIds) 1229 * 1230 * }</pre> 1231 */ 1232 ExpressionList<T> raw(String raw, Object value); 1233 1234 /** 1235 * Add raw expression with an array of parameters. 1236 * <p> 1237 * The raw expression should contain the same number of ? as there are 1238 * parameters. 1239 * </p> 1240 * <p> 1241 * When properties in the clause are fully qualified as table-column names 1242 * then they are not translated. logical property name names (not fully 1243 * qualified) will still be translated to their physical name. 1244 * </p> 1245 */ 1246 ExpressionList<T> raw(String raw, Object... values); 1247 1248 /** 1249 * Add raw expression with no parameters. 1250 * <p> 1251 * When properties in the clause are fully qualified as table-column names 1252 * then they are not translated. logical property name names (not fully 1253 * qualified) will still be translated to their physical name. 1254 * </p> 1255 * <pre>{@code 1256 * 1257 * raw("orderQty < shipQty") 1258 * 1259 * }</pre> 1260 * 1261 * <h4>Subquery example:</h4> 1262 * <pre>{@code 1263 * 1264 * .raw("t0.customer_id in (select customer_id from customer_group where group_id = any(?::uuid[]))", groupIds) 1265 * 1266 * }</pre> 1267 */ 1268 ExpressionList<T> raw(String raw); 1269 1270 /** 1271 * Only add the raw expression if the values is not null or empty. 1272 * <p> 1273 * This is a pure convenience expression to make it nicer to deal with the pattern where we use 1274 * raw() expression with a subquery and only want to add the subquery predicate when the collection 1275 * of values is not empty. 1276 * </p> 1277 * <h3>Without inOrEmpty()</h3> 1278 * <pre>{@code 1279 * 1280 * query.where() // add some predicates 1281 * .eq("status", Status.NEW); 1282 * 1283 * // common pattern - we can use rawOrEmpty() instead 1284 * if (orderIds != null && !orderIds.isEmpty()) { 1285 * query.where().raw("t0.customer_id in (select o.customer_id from orders o where o.id in (?1))", orderIds); 1286 * } 1287 * 1288 * query.findList(); 1289 * 1290 * }</pre> 1291 * 1292 * <h3>Using rawOrEmpty()</h3> 1293 * Note that in the example below we use the <code>?1</code> bind parameter to get "parameter expansion" 1294 * for each element in the collection. 1295 * 1296 * <pre>{@code 1297 * 1298 * query.where() 1299 * .eq("status", Status.NEW) 1300 * // only add the expression if orderIds is not empty 1301 * .rawOrEmpty("t0.customer_id in (select o.customer_id from orders o where o.id in (?1))", orderIds); 1302 * .findList(); 1303 * 1304 * }</pre> 1305 * 1306 * <h3>Postgres ANY</h3> 1307 * With Postgres we would often use the SQL <code>ANY</code> expression and array parameter binding 1308 * rather than <code>IN</code>. 1309 * 1310 * <pre>{@code 1311 * 1312 * query.where() 1313 * .eq("status", Status.NEW) 1314 * .rawOrEmpty("t0.customer_id in (select o.customer_id from orders o where o.id = any(?))", orderIds); 1315 * .findList(); 1316 * 1317 * }</pre> 1318 * <p> 1319 * Note that we need to cast the Postgres array for UUID types like: 1320 * </p> 1321 * <pre>{@code 1322 * 1323 * " ... = any(?::uuid[])" 1324 * 1325 * }</pre> 1326 * 1327 * @param raw The raw expression that is typically a subquery 1328 * @param values The values which is typically a list or set of id values. 1329 */ 1330 ExpressionList<T> rawOrEmpty(String raw, Collection<?> values); 1331 1332 /** 1333 * Add a match expression. 1334 * 1335 * @param propertyName The property name for the match 1336 * @param search The search value 1337 */ 1338 ExpressionList<T> match(String propertyName, String search); 1339 1340 /** 1341 * Add a match expression with options. 1342 * 1343 * @param propertyName The property name for the match 1344 * @param search The search value 1345 */ 1346 ExpressionList<T> match(String propertyName, String search, Match options); 1347 1348 /** 1349 * Add a multi-match expression. 1350 */ 1351 ExpressionList<T> multiMatch(String search, String... properties); 1352 1353 /** 1354 * Add a multi-match expression using options. 1355 */ 1356 ExpressionList<T> multiMatch(String search, MultiMatch options); 1357 1358 /** 1359 * Add a simple query string expression. 1360 */ 1361 ExpressionList<T> textSimple(String search, TextSimple options); 1362 1363 /** 1364 * Add a query string expression. 1365 */ 1366 ExpressionList<T> textQueryString(String search, TextQueryString options); 1367 1368 /** 1369 * Add common terms expression. 1370 */ 1371 ExpressionList<T> textCommonTerms(String search, TextCommonTerms options); 1372 1373 /** 1374 * And - join two expressions with a logical and. 1375 */ 1376 ExpressionList<T> and(Expression expOne, Expression expTwo); 1377 1378 /** 1379 * Or - join two expressions with a logical or. 1380 */ 1381 ExpressionList<T> or(Expression expOne, Expression expTwo); 1382 1383 /** 1384 * Negate the expression (prefix it with NOT). 1385 */ 1386 ExpressionList<T> not(Expression exp); 1387 1388 /** 1389 * Start a list of expressions that will be joined by AND's 1390 * returning the expression list the expressions are added to. 1391 * <p> 1392 * This is exactly the same as conjunction(); 1393 * </p> 1394 * <p> 1395 * Use endAnd() or endJunction() to end the AND junction. 1396 * </p> 1397 * <p> 1398 * Note that a where() clause defaults to an AND junction so 1399 * typically you only explicitly need to use the and() junction 1400 * when it is nested inside an or() or not() junction. 1401 * </p> 1402 * <pre>{@code 1403 * 1404 * // Example: Nested and() 1405 * 1406 * .where() 1407 * .or() 1408 * .and() // nested and 1409 * .startsWith("name", "r") 1410 * .eq("anniversary", onAfter) 1411 * .endAnd() 1412 * .and() 1413 * .eq("status", Customer.Status.ACTIVE) 1414 * .gt("id", 0) 1415 * .endAnd() 1416 * .orderBy().asc("name") 1417 * .findList(); 1418 * }</pre> 1419 */ 1420 Junction<T> and(); 1421 1422 /** 1423 * Return a list of expressions that will be joined by OR's. 1424 * This is exactly the same as disjunction(); 1425 * <p> 1426 * Use endOr() or endJunction() to end the OR junction. 1427 * </p> 1428 * 1429 * <pre>{@code 1430 * 1431 * // Example: (status active OR anniversary is null) 1432 * 1433 * .where() 1434 * .or() 1435 * .eq("status", Customer.Status.ACTIVE) 1436 * .isNull("anniversary") 1437 * .orderBy().asc("name") 1438 * .findList(); 1439 * 1440 * }</pre> 1441 * 1442 * <pre>{@code 1443 * 1444 * // Example: Use or() to join 1445 * // two nested and() expressions 1446 * 1447 * .where() 1448 * .or() 1449 * .and() 1450 * .startsWith("name", "r") 1451 * .eq("anniversary", onAfter) 1452 * .endAnd() 1453 * .and() 1454 * .eq("status", Customer.Status.ACTIVE) 1455 * .gt("id", 0) 1456 * .endAnd() 1457 * .orderBy().asc("name") 1458 * .findList(); 1459 * 1460 * }</pre> 1461 */ 1462 Junction<T> or(); 1463 1464 /** 1465 * Return a list of expressions that will be wrapped by NOT. 1466 * <p> 1467 * Use endNot() or endJunction() to end expressions being added to the 1468 * NOT expression list. 1469 * </p> 1470 * 1471 * <pre>{@code 1472 * 1473 * .where() 1474 * .not() 1475 * .gt("id", 1) 1476 * .eq("anniversary", onAfter) 1477 * .endNot() 1478 * 1479 * }</pre> 1480 * 1481 * <pre>{@code 1482 * 1483 * // Example: nested not() 1484 * 1485 * .where() 1486 * .eq("status", Customer.Status.ACTIVE) 1487 * .not() 1488 * .gt("id", 1) 1489 * .eq("anniversary", onAfter) 1490 * .endNot() 1491 * .orderBy() 1492 * .asc("name") 1493 * .findList(); 1494 * 1495 * }</pre> 1496 */ 1497 Junction<T> not(); 1498 1499 /** 1500 * Start (and return) a list of expressions that will be joined by AND's. 1501 * <p> 1502 * This is the same as and(). 1503 * </p> 1504 */ 1505 Junction<T> conjunction(); 1506 1507 /** 1508 * Start (and return) a list of expressions that will be joined by OR's. 1509 * <p> 1510 * This is the same as or(). 1511 * </p> 1512 */ 1513 Junction<T> disjunction(); 1514 1515 /** 1516 * Start a list of expressions that will be joined by MUST. 1517 * <p> 1518 * This automatically makes the query a useDocStore(true) query that 1519 * will execute against the document store (ElasticSearch etc). 1520 * </p> 1521 * <p> 1522 * This is logically similar to and(). 1523 * </p> 1524 */ 1525 Junction<T> must(); 1526 1527 /** 1528 * Start a list of expressions that will be joined by SHOULD. 1529 * <p> 1530 * This automatically makes the query a useDocStore(true) query that 1531 * will execute against the document store (ElasticSearch etc). 1532 * </p> 1533 * <p> 1534 * This is logically similar to or(). 1535 * </p> 1536 */ 1537 Junction<T> should(); 1538 1539 /** 1540 * Start a list of expressions that will be joined by MUST NOT. 1541 * <p> 1542 * This automatically makes the query a useDocStore(true) query that 1543 * will execute against the document store (ElasticSearch etc). 1544 * </p> 1545 * <p> 1546 * This is logically similar to not(). 1547 * </p> 1548 */ 1549 Junction<T> mustNot(); 1550 1551 /** 1552 * End a junction returning the parent expression list. 1553 * <p> 1554 * Ends a and(), or(), not(), must(), mustNot() or should() junction 1555 * such that you get the parent expression. 1556 * </p> 1557 * <p> 1558 * Alternatively you can always use where() to return the top level expression list. 1559 * </p> 1560 */ 1561 ExpressionList<T> endJunction(); 1562 1563 /** 1564 * End a AND junction - synonym for endJunction(). 1565 */ 1566 ExpressionList<T> endAnd(); 1567 1568 /** 1569 * End a AND junction - synonym for endJunction(). 1570 */ 1571 ExpressionList<T> endOr(); 1572 1573 /** 1574 * End a NOT junction - synonym for endJunction(). 1575 */ 1576 ExpressionList<T> endNot(); 1577 1578}