> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ditto.live/llms.txt
> Use this file to discover all available pages before exploring further.

# Java Legacy→DQL Migration Guide

> Guide for migrating Java apps from legacy query builder APIs to DQL (Ditto Query Language) syntax.

## Overview

This guide covers migrating from Ditto's legacy query builder API to DQL (Ditto Query Language) in Java. DQL provides a SQL-like syntax that's more powerful and intuitive for querying and manipulating data.

**Note:** This migration is required alongside the [V4→V5 API Migration](./java-v4) for a complete upgrade.

***

## AI Agent Prompt

Use this prompt when working with an AI coding assistant to migrate your Ditto Java app to DQL.

<Accordion title="Copy AI Migration Prompt (Click to Expand)">
  ````text theme={null}
  I need help migrating a Ditto Java application from legacy query builder to DQL (Ditto Query Language). Focus on these critical changes:

  QUERIES:
  Replace collection().find() with DQL SELECT statements
  - Use parameterized queries with Map<String, Object> arguments
  - Pass null as second argument for queries without parameters
  - Quote string literals in queries or use parameters

  MUTATIONS:
  Replace upsert/update/remove with DQL INSERT/UPDATE/EVICT
  - Use parameterized arguments for all values
  - Results are CompletableFuture-based with .thenAccept()

  OBSERVERS:
  Convert to registerObserver(query, args, callback)
  - Results are List<Map<String, Object>> instead of List<DittoDocument>
  - Same cleanup pattern with .stop()

  SUBSCRIPTIONS:
  Convert to registerSubscription(query, args)
  - Cleanup uses .cancel() instead of .close()

  COUNTERS:
  Critical change - use PN_INCREMENT BY in APPLY clause
  - Never initialize counter fields
  - Use APPLY clause: "APPLY viewCount = PN_INCREMENT BY :amount"

  BEFORE (LEGACY):
  ```java
  ditto.getStore()
      .collection("cars")
      .find("color == 'red'")
      .exec()
      .thenAccept(docs -> {
          // Use docs
      });

  ditto.getStore()
      .collection("cars")
      .findById(new DittoDocumentID("abc123"))
      .update(mutableDoc -> {
          ((DittoCounter) mutableDoc.get("viewCount")).increment(1);
      });
  ```

  AFTER (DQL):
  ```java
  Map<String, Object> args = Map.of("color", "red");
  ditto.getStore().execute(
      "SELECT * FROM cars WHERE color = :color",
      args
  ).thenAccept(result -> {
      List<Map<String, Object>> cars = result.getItems();
      // Use cars
  });

  Map<String, Object> args = Map.of(
      "id", "abc123",
      "amount", 1
  );
  ditto.getStore().execute(
      "UPDATE cars APPLY viewCount = PN_INCREMENT BY :amount WHERE _id = :id",
      args
  );
  ```

  COMMON PITFALLS:
  - Always quote string literals or use parameters
  - Never initialize counter fields to 0
  - Use APPLY (not SET) for counter operations
  - Pass null for queries without parameters
  - Cast result values to appropriate types

  Work through the codebase systematically. Show me each file's changes.
  ````
</Accordion>

***

## Document Query Syntax

### Query All Documents

<CodeGroup>
  ```java JAVA (DQL) theme={null}
  ditto.getStore().execute(
      "SELECT * FROM cars",
      null
  ).thenAccept(result -> {
      List<Map<String, Object>> cars = result.getItems();
      // Use cars
  }).exceptionally(error -> {
      System.out.println("Query failed: " + error);
      return null;
  });
  ```

  ```java JAVA (LEGACY) theme={null}
  ditto.getStore()
      .collection("cars")
      .findAll()
      .exec()
      .thenAccept(docs -> {
          // Use docs
      });
  ```
</CodeGroup>

### Query Document by ID

<CodeGroup>
  ```java JAVA (DQL) theme={null}
  Map<String, Object> args = Map.of("id", "abc123");

  ditto.getStore().execute(
      "SELECT * FROM cars WHERE _id = :id",
      args
  ).thenAccept(result -> {
      if (!result.getItems().isEmpty()) {
          Map<String, Object> car = result.getItems().get(0);
          // Use car
      }
  });
  ```

  ```java JAVA (LEGACY) theme={null}
  ditto.getStore()
      .collection("cars")
      .findById(new DittoDocumentID("abc123"))
      .exec()
      .thenAccept(doc -> {
          // Use doc
      });
  ```
</CodeGroup>

### Query with Predicate

<CodeGroup>
  ```java JAVA (DQL) theme={null}
  ditto.getStore().execute(
      "SELECT * FROM cars WHERE color = 'red'",
      null
  ).thenAccept(result -> {
      List<Map<String, Object>> redCars = result.getItems();
      // Use redCars
  });
  ```

  ```java JAVA (LEGACY) theme={null}
  ditto.getStore()
      .collection("cars")
      .find("color == 'red'")
      .exec()
      .thenAccept(docs -> {
          // Use docs
      });
  ```
</CodeGroup>

### Query with Parameterized Arguments

<CodeGroup>
  ```java JAVA (DQL) theme={null}
  Map<String, Object> args = Map.of("color", "red");

  ditto.getStore().execute(
      "SELECT * FROM cars WHERE color = :color",
      args
  ).thenAccept(result -> {
      List<Map<String, Object>> redCars = result.getItems();
      // Use redCars
  });
  ```

  ```java JAVA (LEGACY) theme={null}
  String color = "red";
  ditto.getStore()
      .collection("cars")
      .find(String.format("color == '%s'", color))
      .exec()
      .thenAccept(docs -> {
          // Use docs
      });
  ```
</CodeGroup>

***

## Insert, Update, Delete, Eviction

### Insert Document

<CodeGroup>
  ```java JAVA (DQL) theme={null}
  Map<String, Object> args = Map.of(
      "id", "abc123",
      "color", "red",
      "miles", 20000
  );

  ditto.getStore().execute(
      "INSERT INTO cars DOCUMENTS (:doc)",
      Map.of("doc", args)
  ).thenAccept(result -> {
      System.out.println("Insert successful");
  });
  ```

  ```java JAVA (LEGACY) theme={null}
  Map<String, Object> content = Map.of(
      "color", "red",
      "miles", 20000
  );

  ditto.getStore()
      .collection("cars")
      .upsert(content, new DittoDocumentID("abc123"))
      .thenAccept(id -> {
          System.out.println("Insert successful");
      });
  ```
</CodeGroup>

### Update Document

<CodeGroup>
  ```java JAVA (DQL) theme={null}
  Map<String, Object> args = Map.of(
      "id", "abc123",
      "miles", 25000
  );

  ditto.getStore().execute(
      "UPDATE cars SET miles = :miles WHERE _id = :id",
      args
  ).thenAccept(result -> {
      System.out.println("Update successful");
  });
  ```

  ```java JAVA (LEGACY) theme={null}
  ditto.getStore()
      .collection("cars")
      .findById(new DittoDocumentID("abc123"))
      .update(mutableDoc -> {
          mutableDoc.put("miles", 25000);
      })
      .thenAccept(updated -> {
          System.out.println("Update successful");
      });
  ```
</CodeGroup>

### Delete Document

<CodeGroup>
  ```java JAVA (DQL) theme={null}
  Map<String, Object> args = Map.of("id", "abc123");

  ditto.getStore().execute(
      "EVICT FROM cars WHERE _id = :id",
      args
  ).thenAccept(result -> {
      System.out.println("Delete successful");
  });
  ```

  ```java JAVA (LEGACY) theme={null}
  ditto.getStore()
      .collection("cars")
      .findById(new DittoDocumentID("abc123"))
      .remove()
      .thenAccept(success -> {
          System.out.println("Delete successful");
      });
  ```
</CodeGroup>

### Evict All Documents Matching Condition

<CodeGroup>
  ```java JAVA (DQL) theme={null}
  Map<String, Object> args = Map.of("miles", 100000);

  ditto.getStore().execute(
      "EVICT FROM cars WHERE miles > :miles",
      args
  ).thenAccept(result -> {
      System.out.println("Eviction successful");
  });
  ```

  ```java JAVA (LEGACY) theme={null}
  ditto.getStore()
      .collection("cars")
      .find("miles > 100000")
      .remove()
      .thenAccept(success -> {
          System.out.println("Eviction successful");
      });
  ```
</CodeGroup>

***

## Query Response Handling

### Working with Query Results

<CodeGroup>
  ```java JAVA (DQL) theme={null}
  ditto.getStore().execute(
      "SELECT * FROM cars",
      null
  ).thenAccept(result -> {
      List<Map<String, Object>> items = result.getItems();

      for (Map<String, Object> car : items) {
          String id = (String) car.get("_id");
          String color = (String) car.get("color");
          Integer miles = (Integer) car.get("miles");

          System.out.println("Car " + id + " is " + color + " with " + miles + " miles");
      }
  });
  ```

  ```java JAVA (LEGACY) theme={null}
  ditto.getStore()
      .collection("cars")
      .findAll()
      .exec()
      .thenAccept(docs -> {
          for (DittoDocument doc : docs) {
              String id = doc.getId().toString();
              String color = (String) doc.get("color");
              Integer miles = (Integer) doc.get("miles");

              System.out.println("Car " + id + " is " + color + " with " + miles + " miles");
          }
      });
  ```
</CodeGroup>

***

## Observer Migration

### Observing Query Results

<CodeGroup>
  ```java JAVA (DQL) theme={null}
  String query = "SELECT * FROM cars WHERE color = :color";
  Map<String, Object> args = Map.of("color", "red");

  DittoStoreObserver observer = ditto.getStore().registerObserver(
      query,
      args,
      (result) -> {
          List<Map<String, Object>> redCars = result.getItems();
          // Update UI with redCars
      }
  );

  // Later: clean up
  observer.stop();
  ```

  ```java JAVA (LEGACY) theme={null}
  DittoLiveQuery liveQuery = ditto.getStore()
      .collection("cars")
      .find("color == 'red'")
      .observe((docs, event) -> {
          // Update UI with docs
      });

  // Later: clean up
  liveQuery.stop();
  ```
</CodeGroup>

**Key Differences:**

* DQL observers use `registerObserver(query, args, callback)` instead of `.observe()`
* Results are `List<Map<String, Object>>` instead of `List<DittoDocument>`
* Same cleanup pattern with `.stop()`

<Warning>
  **Performance Consideration**: DQL observers provide more advanced return results including aggregates and projections. This requires more database full scans to ensure consistent results compared to the legacy query builder.

  **Use indexes on query fields** to maintain and improve observer performance. Indexes ensure your observers remain functional with optimal query performance.
</Warning>

**Best Practice: Create Indexes for Observer Queries**

```java theme={null}
// Create index on frequently queried fields
ditto.getStore().execute("""
    CREATE INDEX idx_cars_color
    ON cars (color)
    """, null);

// Then register observer - queries will use the index
DittoStoreObserver observer = ditto.getStore().registerObserver(
    "SELECT * FROM cars WHERE color = :color",
    Map.of("color", "red"),
    (result) -> {
        // Process results
    }
);
```

For more information on creating and managing indexes, see the [DQL Indexing documentation](/dql/indexing).

***

## Sync Subscriptions Migration

### Creating Sync Subscriptions

<CodeGroup>
  ```java JAVA (DQL) theme={null}
  String query = "SELECT * FROM cars WHERE color = :color";
  Map<String, Object> args = Map.of("color", "red");

  DittoSyncSubscription subscription = ditto.getSync().registerSubscription(
      query,
      args
  );

  // Later: clean up
  subscription.cancel();
  ```

  ```java JAVA (LEGACY) theme={null}
  DittoSyncSubscription subscription = ditto.getStore()
      .collection("cars")
      .find("color == 'red'")
      .subscribe();

  // Later: clean up
  subscription.close();
  ```
</CodeGroup>

**Key Differences:**

* DQL subscriptions use `registerSubscription(query, args)` instead of `.subscribe()`
* Cleanup now uses `.cancel()` instead of `.close()`

***

## Counter Type Migration

### Updating Counter Values

<Warning>
  **Critical Change**: Counters are now modified using the `PN_INCREMENT BY` operation in the `APPLY` clause, not by setting values directly.
</Warning>

<Note>
  **PN\_COUNTER is the DQL equivalent of the legacy `DittoCounter` type.** When migrating counter operations from the legacy query builder's counter methods, use `PN_INCREMENT BY` in the `APPLY` clause. This maintains full compatibility with existing counter data created by `DittoCounter`.
</Note>

<CodeGroup>
  ```java JAVA (DQL) theme={null}
  // Increment a counter
  Map<String, Object> args = Map.of(
      "id", "abc123",
      "amount", 1
  );

  ditto.getStore().execute(
      "UPDATE cars " +
      "APPLY viewCount INCREMENT BY :amount " +
      "WHERE _id = :id",
      args
  );
  ```

  ```java JAVA (LEGACY) theme={null}
  ditto.getStore()
      .collection("cars")
      .findById(new DittoDocumentID("abc123"))
      .update(mutableDoc -> {
          if (mutableDoc.get("viewCount") == null) {
              mutableDoc.put("viewCount", new DittoCounter());
          }
          ((DittoCounter) mutableDoc.get("viewCount")).increment(1);
      });
  ```
</CodeGroup>

**Important Notes:**

* **Never initialize counter fields** in INSERT statements - they start at 0 automatically when incremented
* Use `PN_INCREMENT BY` in the `APPLY` clause for counter operations
* Positive values increment, negative values decrement
* Counter values are read as integers in SELECT queries

<CodeGroup>
  ```java JAVA (DQL - CORRECT) theme={null}
  // First increment - no initialization needed
  ditto.getStore().execute(
      "UPDATE cars " +
      "APPLY viewCount = PN_INCREMENT BY 1 " +
      "WHERE _id = :id",
      Map.of("id", "abc123")
  );

  // Reading counter value
  ditto.getStore().execute(
      "SELECT viewCount FROM cars WHERE _id = :id",
      Map.of("id", "abc123")
  ).thenAccept(result -> {
      if (!result.getItems().isEmpty()) {
          Integer count = (Integer) result.getItems().get(0).get("viewCount");
          System.out.println("View count: " + count);
      }
  });
  ```

  ```java JAVA (DQL - WRONG) theme={null}
  // ❌ WRONG: Don't initialize counters to 0
  ditto.getStore().execute(
      "INSERT INTO cars DOCUMENTS (:doc)",
      Map.of("doc", Map.of(
          "_id", "abc123",
          "viewCount", 0  // ❌ WRONG
      ))
  );

  // ❌ WRONG: Don't SET counters
  ditto.getStore().execute(
      "UPDATE cars SET viewCount = viewCount + 1 WHERE _id = :id",
      Map.of("id", "abc123")
  );
  ```
</CodeGroup>

***

## Attachment Operations

### Fetching Attachments

<CodeGroup>
  ```java JAVA (DQL) theme={null}
  // Get attachment token from query
  ditto.getStore().execute(
      "SELECT avatar FROM cars WHERE _id = :id",
      Map.of("id", "abc123")
  ).thenAccept(result -> {
      if (!result.getItems().isEmpty()) {
          Map<String, Object> car = result.getItems().get(0);
          DittoAttachmentToken token = (DittoAttachmentToken) car.get("avatar");

          if (token != null) {
              DittoAttachmentFetcher fetcher = ditto.getStore()
                  .fetchAttachment(token, (attachment, error) -> {
                      if (error == null) {
                          byte[] data = attachment.getData();
                          // Use attachment data
                      }
                  });
          }
      }
  });
  ```

  ```java JAVA (LEGACY) theme={null}
  ditto.getStore()
      .collection("cars")
      .findById(new DittoDocumentID("abc123"))
      .exec()
      .thenAccept(doc -> {
          DittoAttachment attachment = doc.getAttachment("avatar");
          if (attachment != null) {
              byte[] data = attachment.getData();
              // Use attachment data
          }
      });
  ```
</CodeGroup>

***

## Performance Enhancements

### Indexes for Improved Query Performance

DQL observers and queries benefit significantly from proper indexing. When migrating from the legacy query builder to DQL, creating indexes on frequently queried fields is essential for maintaining optimal performance.

**Why Indexes Matter for DQL:**

* DQL observers support advanced features like aggregates and projections
* These advanced features require full database scans to ensure consistent results
* Indexes dramatically reduce query execution time by avoiding full scans
* Combining indexes with observers provides better performance than legacy query builder

**Creating Indexes:**

```java theme={null}
// Create index on single field
ditto.getStore().execute("""
    CREATE INDEX idx_cars_color
    ON cars (color)
    """, null);

// Create compound index on multiple fields
ditto.getStore().execute("""
    CREATE INDEX idx_cars_color_year
    ON cars (color, year)
    """, null);

// Create index on nested field
ditto.getStore().execute("""
    CREATE INDEX idx_cars_location
    ON cars (_id.locationId)
    """, null);
```

**Best Practices:**

1. Create indexes on fields used in `WHERE` clauses
2. Create indexes before registering observers for those queries
3. Use compound indexes for queries with multiple filter conditions
4. Monitor query performance and add indexes as needed

For comprehensive information on indexing strategies, syntax, and best practices, see the [DQL Indexing documentation](/dql/indexing).

***

## Common Pitfalls to Avoid

### 1. DQL Syntax Errors

Use `:paramName` for parameters, not string literals or concatenation.

```java theme={null}
// ❌ Wrong: Hardcoded string literal without quotes
ditto.getStore().execute("SELECT * FROM cars WHERE color = red", null);

// ❌ Wrong: String concatenation
String color = "red";
ditto.getStore().execute("SELECT * FROM cars WHERE color = '" + color + "'", null);

// ✅ Correct: Using :paramName with Map<String, Object>
Map<String, Object> args = Map.of("color", "red");
ditto.getStore().execute("SELECT * FROM cars WHERE color = :color", args);
```

### 2. Missing Parameter Binding

**NEVER** use string concatenation in queries. Always use parameterized queries with `Map<String, Object>`.

```java theme={null}
// ❌ Wrong: String concatenation
String locationId = "loc_123";
ditto.getStore().execute(
    "SELECT * FROM cars WHERE _id.locationId = '" + locationId + "'",
    null
);

// ✅ Correct: Parameterized query
Map<String, Object> args = Map.of("locationId", locationId);
ditto.getStore().execute(
    "SELECT * FROM cars WHERE _id.locationId = :locationId",
    args
);
```

### 3. Counter Type Errors

Use `COUNTER` annotation in collection definitions. Do **NOT** use `SET` with `COUNTER` fields. Use `APPLY` with `PN_INCREMENT BY`. Pass negative values for decrements.

```java theme={null}
// ❌ Wrong: Initializing counter with a number (creates REGISTER, not COUNTER)
Map<String, Object> doc = Map.of("_id", id, "counter", 0);
ditto.getStore().execute(
    "INSERT INTO items DOCUMENTS (:doc)",
    Map.of("doc", doc)
);

// ❌ Wrong: Using SET on counter field
Map<String, Object> args = Map.of("id", id);
ditto.getStore().execute(
    "UPDATE items SET counter = 5 WHERE _id = :id",
    args
);

// ✅ Correct: Use PN_INCREMENT BY with APPLY clause (creates counter on first use)
Map<String, Object> args = Map.of("value", 1, "id", id);
ditto.getStore().execute(
    "UPDATE COLLECTION items (counter COUNTER) APPLY counter PN_INCREMENT BY :value WHERE _id = :id",
    args
);

// ✅ Correct: Decrement by passing negative value
Map<String, Object> args = Map.of("value", -1, "id", id);
ditto.getStore().execute(
    "UPDATE items APPLY counter PN_INCREMENT BY :value WHERE _id = :id",
    args
);
```

### 4. Memory Management with Observers

Call `item.close()` after extracting data from QueryResultItems. Always close observers when done. Use indexes for improved memory and performance.

```java theme={null}
// ❌ Wrong: Storing QueryResultItems without closing
private List<DittoQueryResultItem> items = new ArrayList<>();

DittoStoreObserver observer = ditto.getStore().registerObserver(
    "SELECT * FROM cars",
    null,
    result -> {
        items = result.getItems();  // Holds native memory
    }
);

// ✅ Correct: Extract data and close items immediately
public class CarRepository {
    private DittoStoreObserver observer;
    private List<String> carIds = new ArrayList<>();

    public void startObserving() {
        observer = ditto.getStore().registerObserver(
            "SELECT * FROM cars",
            null,
            result -> {
                List<String> ids = new ArrayList<>();
                for (DittoQueryResultItem item : result.getItems()) {
                    String id = (String) item.getValue().get("_id");
                    item.close();  // Free native memory
                    ids.add(id);
                }
                this.carIds = ids;
                // Update UI with extracted data
            }
        );
    }

    public void stopObserving() {
        if (observer != null) {
            observer.close();  // Always close observer
        }
    }
}
```

### 5. Attachment Handling

Use `ATTACHMENT` annotation in collection definitions. Create attachments with `ditto.getStore().newAttachment()`.

```java theme={null}
// ❌ Wrong: Missing ATTACHMENT annotation
ditto.getStore().execute(
    "INSERT INTO cars DOCUMENTS (:doc)",
    Map.of("doc", docWithAttachment)
);

// ✅ Correct: Use ATTACHMENT annotation in COLLECTION definition
DittoAttachment attachment = ditto.getStore().newAttachment(
    inputStream,
    metadata
);

Map<String, Object> doc = Map.of(
    "_id", id,
    "image", attachment
);

ditto.getStore().execute(
    "INSERT INTO COLLECTION cars (image ATTACHMENT) DOCUMENTS (:doc)",
    Map.of("doc", doc)
);
```

***

## Migration Checklist

* [ ] Replace all `.collection().find()` with DQL SELECT queries
* [ ] Replace all `.collection().findById()` with DQL SELECT with `_id` filter
* [ ] Replace all `.collection().upsert()` with DQL INSERT statements
* [ ] Replace all `.update()` callbacks with DQL UPDATE statements
* [ ] Replace all `.remove()` with DQL EVICT statements
* [ ] Update all observers to use `registerObserver(query, args, callback)`
* [ ] Update all subscriptions to use `registerSubscription(query, args)`
* [ ] Convert counter operations to use `PN_INCREMENT BY` in APPLY clause
* [ ] Remove counter field initialization from INSERT statements
* [ ] Add `null` as second argument for queries without parameters
* [ ] Update result handling from `DittoDocument` to `Map<String, Object>`
* [ ] Verify attachment fetching uses tokens from query results

***