Java Legacy→DQL Migration Guide

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 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.

Copy AI Migration Prompt (Click to Expand)

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.

Document Query Syntax

Query All Documents

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;
});

ditto.getStore()
    .collection("cars")
    .findAll()
    .exec()
    .thenAccept(docs -> {
        // Use docs
    });

Query Document by ID

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
    }
});

ditto.getStore()
    .collection("cars")
    .findById(new DittoDocumentID("abc123"))
    .exec()
    .thenAccept(doc -> {
        // Use doc
    });

Query with Predicate

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

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

Query with Parameterized Arguments

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
});

String color = "red";
ditto.getStore()
    .collection("cars")
    .find(String.format("color == '%s'", color))
    .exec()
    .thenAccept(docs -> {
        // Use docs
    });

Insert, Update, Delete, Eviction

Insert Document

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");
});

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");
    });

Update Document

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");
});

ditto.getStore()
    .collection("cars")
    .findById(new DittoDocumentID("abc123"))
    .update(mutableDoc -> {
        mutableDoc.put("miles", 25000);
    })
    .thenAccept(updated -> {
        System.out.println("Update successful");
    });

Delete Document

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");
});

ditto.getStore()
    .collection("cars")
    .findById(new DittoDocumentID("abc123"))
    .remove()
    .thenAccept(success -> {
        System.out.println("Delete successful");
    });

Evict All Documents Matching Condition

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");
});

ditto.getStore()
    .collection("cars")
    .find("miles > 100000")
    .remove()
    .thenAccept(success -> {
        System.out.println("Eviction successful");
    });

Query Response Handling

Working with Query Results

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");
    }
});

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");
        }
    });

Observer Migration

Observing Query Results

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();

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

// Later: clean up
liveQuery.stop();

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()

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.

Best Practice: Create Indexes for Observer Queries

// 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.

Sync Subscriptions Migration

Creating Sync Subscriptions

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();

DittoSyncSubscription subscription = ditto.getStore()
    .collection("cars")
    .find("color == 'red'")
    .subscribe();

// Later: clean up
subscription.close();

Key Differences:

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

Counter Type Migration

Updating Counter Values

Critical Change: Counters are now modified using the PN_INCREMENT BY operation in the APPLY clause, not by setting values directly.

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.

// 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
);

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);
    });

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

// 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);
    }
});

// ❌ 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")
);

Attachment Operations

Fetching Attachments

// 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
                    }
                });
        }
    }
});

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
        }
    });

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:

// 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:

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

For comprehensive information on indexing strategies, syntax, and best practices, see the DQL Indexing documentation.

Common Pitfalls to Avoid

1. DQL Syntax Errors

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

// ❌ 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>.

// ❌ 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.

// ❌ 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.

// ❌ 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().

// ❌ 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)
);

Overview & Setup

SDKs

Accessing Data

Syncing Data

Authentication and Authorization

Managing the Mesh

Deploying your App

Java Legacy→DQL Migration Guide

Overview

AI Agent Prompt

Document Query Syntax

Query All Documents

Query Document by ID

Query with Predicate

Query with Parameterized Arguments

Insert, Update, Delete, Eviction

Insert Document

Update Document

Delete Document

Evict All Documents Matching Condition

Query Response Handling

Working with Query Results

Observer Migration

Observing Query Results

Sync Subscriptions Migration

Creating Sync Subscriptions

Counter Type Migration

Updating Counter Values

Attachment Operations

Fetching Attachments

Performance Enhancements

Indexes for Improved Query Performance

Common Pitfalls to Avoid

1. DQL Syntax Errors

2. Missing Parameter Binding

3. Counter Type Errors

4. Memory Management with Observers

5. Attachment Handling

Migration Checklist

​Overview

​AI Agent Prompt

​Document Query Syntax

​Query All Documents

​Query Document by ID

​Query with Predicate

​Query with Parameterized Arguments

​Insert, Update, Delete, Eviction

​Insert Document

​Update Document

​Delete Document

​Evict All Documents Matching Condition

​Query Response Handling

​Working with Query Results

​Observer Migration

​Observing Query Results

​Sync Subscriptions Migration

​Creating Sync Subscriptions

​Counter Type Migration

​Updating Counter Values

​Attachment Operations

​Fetching Attachments

​Performance Enhancements

​Indexes for Improved Query Performance

​Common Pitfalls to Avoid

​1. DQL Syntax Errors

​2. Missing Parameter Binding

​3. Counter Type Errors

​4. Memory Management with Observers

​5. Attachment Handling

​Migration Checklist

Overview

AI Agent Prompt

Document Query Syntax

Query All Documents

Query Document by ID

Query with Predicate

Query with Parameterized Arguments

Insert, Update, Delete, Eviction

Insert Document

Update Document

Delete Document

Evict All Documents Matching Condition

Query Response Handling

Working with Query Results

Observer Migration

Observing Query Results

Sync Subscriptions Migration

Creating Sync Subscriptions

Counter Type Migration

Updating Counter Values

Attachment Operations

Fetching Attachments

Performance Enhancements

Indexes for Improved Query Performance

Common Pitfalls to Avoid

1. DQL Syntax Errors

2. Missing Parameter Binding

3. Counter Type Errors

4. Memory Management with Observers

5. Attachment Handling

Migration Checklist