Documentation Home
MySQL Connector/J Developer Guide
Related Documentation Download this Manual
PDF (US Ltr) - 1.2Mb
PDF (A4) - 1.2Mb


10.2 Schema Validation

When working with MySQL Server 8.0.19 and later: Schema validation can be configured for a Collection, so that documents in the Collection are validated against a schema before they can be inserted or updated. This is done by specifying a JSON Schema during Collection creation or modification; schema validation is then performed by the server at a document creation or update, and an error is returned if the document does not validate against the assigned schema. For more information on JSON schema validation in MySQL, see JSON Schema Validation Functions. This section describes how to configure schema validation for a Collection with Connector/J.

To configure schema validation during the creation of a Collection, pass to the createCollection() method a CreateCollectionOptions object, which has these fields:

  • reuse: a boolean set by the setReuseExisting method. If it is true, when the Collection to be created already exists within the Schema that is to contain it, Connector/J returns success (without any attempt to apply JSON schema to the existing Collection); in the same case, Connector/J returns an error if the parameter is set to false. If reuse is not set, it is taken to be false.

  • validation: a Validation object set by the setValidation() method. A Validation object in turns contains these fields:

    • level: a enumeration of the class ValidationLevel, set by the setLevel() method; it can be one of the following two values:

      • STRICT: Strict validation. Attempting to insert or modify a document that violates the validation schema results in a server error being raised.

      • OFF: No validation. Schema validation is turned off.

      If level is not set, it is taken as OFF for MySQL Server 8.0.19, and STRICT for 8.0.20 and later.

    • schema: A string representing a JSON Schema to be used to validate a Document in the Collection; set by the setSchema() method.

      If schema is not provided but level is set to STRICT, the Collection is validated against the default schema {"type" : "object"}.

This is an example of how to configure schema validation at the creation of a Collection:

Collection coll = this.schema.createCollection(collName,
    new CreateCollectionOptions()
        .setReuseExisting(false)
        .setValidation(new Validation()
           .setLevel(ValidationLevel.STRICT)
           .setSchema(
               "{\"id\": \"http://json-schema.org/geo\","
               + "\"$schema\": \"http://json-schema.org/draft-06/schema#\","
               + "       \"description\": \"A geographical coordinate\","
               + "       \"type\": \"object\","
               + "       \"properties\": {"
               + "          \"latitude\": {"
               + "             \"type\": \"number\""
               + "          },"
               + "          \"longitude\": {"
               + "             \"type\": \"number\""
               + "          }"
               + "       },"
               + "       \"required\": [\"latitude\", \"longitude\"]"
               + "  }"
)));

The set fields are accessible by the corresponding getter methods.

To modify the schema validation configuration for a Collection, use the modifyCollection() method and pass to it a ModifyCollectionOptions object, which has the same fields as the CreateCollectionOptions object except for the reuse field, which does not exist for a ModifyCollectionOptions object. For the Validation object of a ModifyCollectionOptions object, users can set either its level or schema, or both. Here is an example of using the modifyCollection() to change the schema validation configuration:

schema.modifyCollection(collName,
    new ModifyCollectionOptions()
        .setValidation(new Validation()
           .setLevel(ValidationLevel.OFF)
           .setSchema(
               "{\"id\": \"http://json-schema.org/geo\","
               + "\"$schema\": \"http://json-schema.org/draft-06/schema#\","
               + "       \"description\": \"NEW geographical coordinate\","
               + "       \"type\": \"object\","
               + "       \"properties\": {"
               + "          \"latitude\": {"
               + "             \"type\": \"number\""
               + "          },"
               + "          \"longitude\": {"
               + "             \"type\": \"number\""
               + "          }"
               + "       },"
               + "       \"required\": [\"latitude\", \"longitude\"]"
               + "  }"
)));

If the Collection contains documents that do not validate against the new JSON schema supplied through ModifyCollectionOptions, the server will reject the schema modification with the error ERROR 5180 (HY000) Document is not valid according to the schema assigned to collection.

Note

createCollection() and modifyCollection() are overloaded: they can be called without passing to them the CreateCollectionOptions or the ModifyCollectionOptions, respectively, in which case schema validation will not be applied to the Collection.