# Basic Usage ## Overview There are several categories of operations that can be accomplished against documents. - **Count** returns the number of documents matching some criteria - **Exists** returns true if any documents match the given criteria - **Insert** (`Document.insert`) adds a new document, failing if the ID field is not unique - **Save** (`Document.save`) adds a new document, updating an existing one if the ID is already present ("upsert") - **Update** updates an existing document, doing nothing if no documents satisfy the criteria - **Patch** updates a portion of an existing document, doing nothing if no documents satisfy the criteria - **Find** returns the documents matching some criteria as domain objects - **Json** returns documents matching some criteria as a JSON string, or writes that text directly to a `PrintWriter` - **RemoveFields** removes fields from documents matching some criteria - **Delete** removes documents matching some criteria `Insert` and `Save` were the only two that don't mention criteria. For the others, "some criteria" can be defined a few different ways: - **all** references all documents in the table; applies to Count and Find - **byId** looks for a single document on which to operate; applies to all but Count - **byFields** uses JSON field comparisons to select documents for further processing (PostgreSQL will use a numeric comparison if the field value is numeric, or a string comparison otherwise; SQLite will do its usual [best-guess on types][]); applies to all but Update - **byContains** (PostgreSQL only) uses a JSON containment query (the `@>` operator) to find documents where the given sub-document occurs (think of this as an `=` comparison based on one or more properties in the document; looking for hotels with `{ "country": "USA", "rating": 4 }` would find all hotels with a rating of 4 in the United States); applies to all but Update - **byJsonPath** (PostgreSQL only) uses a JSON patch match query (the `@?` operator) to make specific queries against a document's structure (it also supports more operators than a containment query; to find all hotels rated 4 _or higher_ in the United States, we could query for `"$ ? (@.country == \"USA\" && @.rating > 4)"`); applies to all but Update Finally, `Find` also has `firstBy*` implementations for all supported criteria types, and `Find.*Ordered` implementations to sort the results in the database. ## Saving Documents > [!NOTE] > All code samples are in Java unless otherwise noted. Also, they use the connection-creating functions for clarity; see below for notes on how these names translate to `Connection` extensions. The library provides three different ways to save data. The first equates to a SQL `INSERT` statement, and adds a single document to the repository. ```java Room room = new Room(/* ... */); // Parameters are table name and document Document.insert("room", room); ``` The second is `save`; and inserts the data it if does not exist and replaces the document if it does exist (what some call an "upsert"). It utilizes the `ON CONFLICT` syntax to ensure an atomic statement. Its parameters are the same as those for `insert`. The third equates to a SQL `UPDATE` statement. `Update` applies to a full document and is usually used by ID, while `Patch` is used for partial updates and may be done by field comparison, JSON containment, or JSON Path match. For a few examples, let's begin with a query that may back the "edit hotel" page. This page lets the user update nearly all the details for the hotel, so updating the entire document would be appropriate. ```java Hotel hotel = Find.byId("hotel", hotelId, Hotel.class); if (hotel != null) { // update hotel properties from the posted form Update.byId("hotel", hotel.getId(), hotel); } ``` For the next example, suppose we are upgrading our hotel, and need to take rooms 221-240 out of service. We can utilize a patch via JSON Path to accomplish this. ```java Patch.byJsonPath("room", "$ ? (@.hotelId == \"abc\" && (@.roomNumber >= 221 && @.roomNumber <= 240)", Map.of("inService", false)); ``` > [!NOTE] > We are ignoring the current reservations, end date, etc. This is very naïve example! JSON Path queries are only available for PostgreSQL. Both PostgreSQL and SQLite could accomplish this using the `Between` comparison and a `byFields` query: ```java // Parameters are table name, fields to match, and patch to apply; // there are others, but they are optional Patch.byFields("room", List.of(Field.between("roomNumber", 221, 240)), Map.of("inService", false)); ``` Scala programs can use its `::` operator to provide an immutable list: ```scala // Scala Patch.byFields("room", Field.between("roomNumber", 221, 240) :: Nil, Map.Map1("inService", false)) ``` > [!NOTE] > These examples use `Map`s as the patch documents, as that is a convenient way to update one or more fields in a document. As patches are serialized to JSON, this can be used to update a sub-document (a domain object which is a property in a larger document/object). This could also be done using multiple fields, such as `Field.greaterOrEqual` and `Field.lessOrEqual`, providing `FieldMatch.All` after the patch object; there are many different ways to do things! There are more complex ways to update data, including custom queries; that will be detailed in the [Advanced Usage][] section. ## Retrieving Documents ### As Domain Items Functions to find documents start with `Find.`. There are variants to find all documents in a table, find by ID, find by JSON field comparisons, find by JSON containment, or find by JSON Path. The hotel update example above utilizes an ID lookup; the descriptions of JSON containment and JSON Path show examples of the criteria used to retrieve using those techniques. `Find.*Ordered` methods append an `ORDER BY` clause to the query that will sort the results in the database. These take, as their last parameter, a collection of `Field` items; a `.named` static method allows for field creation for these names. Within these names, prefixing the name with `n:` will tell PostgreSQL to sort this field numerically rather than alphabetically; it has no effect in SQLite (it does its own [type coercion][best-guess on types]). Prefixing the name with `i:` will do a case-insensitive sort. Adding " DESC" at the end will sort high-to-low instead of low-to-high. #### Choose Your Adventure Document retrieval is the reason there are three distinct implementations of this library (`groovy` shares `core`'s implementation). Java's type-erased generics require a `Class` for documents to be deserialized. Scala's implicit `ClassTag` and Kotlin's reified generics do not require a `Class` instance as a parameter, but will typically require a generic type when they are called. Scala's collection types will convert back and forth to Java's and Kotlin's, but littering `.asJava` at the end of collections is a terrible developer experience. Rather than try to take one pass through the `Find` methods, calling out all the different ways they are implemented, we'll do a pass for each implementation. #### Core _(Java, Groovy, reflection-based Kotlin)_ Methods that attempt to return a single document will return an instance of Java's `Optional` class. Methods that return a list of documents return a Kotlin `List` (immutable, but otherwise behaves as one would expect a Java list to behave). Parameters which can have multiple values expect a Kotlin `Sequence`, which most Java collection types satisfy (`ArrayList`, `HashSet`, etc.). Each method will have, as one of its later parameters, a `Class` instance. This is used by the deserializer to select the class which gets created. For example, `Find.byId`'s parameters are table name, document ID, and `Class` to return. #### Scala Methods that attempt to return a single document will return an instance of Scala's `Option[A]` type. Methods that return a list of documents return Scala's immutable `List[Doc]`. Parameters which can have multiple values expect a Scala `Seq[A]`. Each `Find.` method requires a generic type parameter to indicate what type of object should be returned. To select a hotel by its ID, for example: ```scala // Scala val hotel = Find.byId[Hotel](tableName, hotelId) ``` #### KotlinX Methods that attempt to return a single document will return a nullable type. Others are the same as the `core` module. Each `Find.` method requires a generic type parameter, and requires `inline` on the method definition to support the reified type parameter (and compiler-implemented serialization). This choice can cascade throughout the calling application, but the compiler will be happy to tell you if the enclosing context needs to be marked as `inline`. ### As JSON All `Find` methods and functions have two corresponding `Json` methods. While the input types for these methods also vary based on the implementation, these all return either `String`s or `void`. * The first set return the expected document(s) as a `String`, and will always return valid JSON. Single-document queries with nothing found will return `{}`, while zero-to-many queries will return `[]` if no documents match the given criteria. * The second set are prefixed with `write`, and take a `PrintWriter` immediately after the table name parameter. These functions write results to the given writer as they are retrieved from the database, instead of accumulating them all and returning a `String`. This can be useful for JSON API scenarios, as it can be written directly to a servlet output stream. > [!NOTE] > This library does no flushing of the underlying stream. Applications should handle that after calling `Json.write*` or configure the `PrintWriter` to auto-flush. ## Deleting Documents Functions to delete documents start with `Delete.`. Document deletion is supported by ID, JSON field comparison, JSON containment, or JSON Path match. The pattern is the same as for finding or partially updating. _(There is no library method provided to delete all documents, though deleting by JSON field comparison where a non-existent field is null would accomplish this.)_ ## Counting Documents Functions to count documents start with `Count.`. Documents may be counted by a table in its entirety, by JSON field comparison, by JSON containment, or by JSON Path match. _(Counting by ID is an existence check!)_ ## Document Existence Functions to check for existence start with `Exists.`. Documents may be checked for existence by ID, JSON field comparison, JSON containment, or JSON Path match. ## What / How Cross-Reference The table below shows which commands are available for each access method. (X = supported for both, P = PostgreSQL only) | Operation | `all` | `byId` | `byFields` | `byContains` | `byJsonPath` | `firstByFields` | `firstByContains` | `firstByJsonPath` | |-----------------|:-----:|:------:|:----------:|:------------:|:------------:|:---------------:|:-----------------:|:-----------------:| | `Count` | X | | X | P | P | | | | | `Exists` | | X | X | P | P | | | | | `Find` / `Json` | X | X | X | P | P | X | P | P | | `Patch` | | X | X | P | P | | | | | `RemoveFields` | | X | X | P | P | | | | | `Delete` | | X | X | P | P | | | | `Document.insert`, `Document.save`, and `Update.*` operate on single documents. ### Extension Methods Extension methods are defined in the `.extensions` package for each library. They can be selectively imported, or all can be imported at once. (Groovy extension methods should be visible just from the module being installed.) Here is how the names are translated to those extensions methods: - Most have the name of the class plus the name of the method - e.g., `Count.all` becomes `countAll`, `Find.byFieldsOrdered` becomes `findByFieldsOrdered`, etc. - `Document` and `Definition` methods are defined using their existing name - e.g., `Document.insert` is simply `insert`, `Definition.ensureTable` is `ensureTable`, etc. - `Json.write*` functions begin with `writeJson` - e.g., `Json.writeByFields` becomes `writeJsonByFields`, etc. As extensions, these will be visible on the instance of the `Connection` object in the application. [best-guess on types]: https://sqlite.org/datatype3.html "Datatypes in SQLite • SQLite" [JSON Path]: https://www.postgresql.org/docs/15/functions-json.html#FUNCTIONS-SQLJSON-PATH "JSON Functions and Operators • PostgreSQL Documentation" [Advanced Usage]: ./advanced/index.md "Advanced Usage • solutions.bitBadger.documents"