X DevAPI User Guide
The following section explains the individual functions of the Collection object.
The most common operations to be performed on a Collection are the Create Read Update Delete (CRUD) operations. In order to speed up find operations it is recommended to make proper use of indexes.
The Collection.add()
function is used to
store documents in the database. It takes a single document or a
list of documents and is executed by the
run()
function.
The collection needs to be created with the
Schema.createCollection()
function before
documents can be inserted. To insert documents into an existing
collection use the Schema.getCollection()
function.
The following example shows how to use the
Collection.add()
function. The example
assumes that the test schema exists and that the collection
my_collection
does not exist.
MySQL Shell JavaScript Code
// Create a new collection var myColl = db.createCollection('my_collection'); // Insert a document myColl.add( {_id: '1', name: 'Laurie', age: 19 } ).execute(); // Insert several documents at once myColl.add( [ {_id: '5', name: 'Nadya', age: 54 }, {_id: '6', name: 'Lukas', age: 32 } ] ).execute();
MySQL Shell Python Code
# Create a new collection myColl = db.create_collection('my_collection') # Insert a document myColl.add( {'_id': '1', 'name': 'Laurie', 'age': 19 } ).execute() # Insert several documents at once myColl.add( [ {'_id': '5', 'name': 'Nadya', 'age': 54 }, {'_id': '6', 'name': 'Lukas', 'age': 32 } ] ).execute()
Node.js JavaScript Code
// Create a new collection db.createCollection('myCollection').then(function (myColl) { return Promise.all([ // Insert a document myColl .add({ name: 'Laurie', age: 19 }) .execute(), // Insert several documents at once myColl .add([ { name: 'Nadya', age: 54 }, { name: 'Lukas', age: 32 } ]) .execute() ]) });
C# Code
// Assumptions: test schema assigned to db, my_collection collection not exists // Create a new collection var myColl = db.CreateCollection("my_collection"); // Insert a document myColl.Add(new { name = "Laurie", age = 19 }).Execute(); // Insert several documents at once myColl.Add(new[] { new { name = "Nadya", age = 54 }, new { name = "Lukas", age = 32 } }).Execute();
Python Code
# Create a new collection my_coll = my_schema.create_collection('my_collection') # Insert a document my_coll.add({'name': 'Laurie', 'age': 19}).execute() # Insert several documents at once my_coll.add([ {'name': 'Nadya', 'age': 54}, {'name': 'Lukas', 'age': 32} ]).execute()
Java Code
// Create a new collection Collection coll = db.createCollection("payments"); // Insert a document coll.add("{\"name\":\"Laurie\", \"age\":19}"); // Insert several documents at once coll.add("{\"name\":\"Nadya\", \"age\":54}", "{\"name\":\"Lukas\", \"age\":32}");
C++ Code
// Create a new collection Collection coll = db.createCollection("payments"); // Insert a document coll.add(R"({"name":"Laurie", "age":19})").execute(); // Insert several documents at once std::list<DbDoc> docs = { DbDoc(R"({"name":"Nadya", "age":54})"), DbDoc(R"({"name":"Lukas", "age":32})") }; coll.add(docs).execute();
For more information, see CollectionAddFunction.
Every document has a unique identifier called the document ID.
The document ID is stored in the _id
field of
a document. The document ID is a
VARBINARY()
with a maximum length
of 32 characters. This section describes how document IDs can be
used, see Section 5.1, “Working with Document IDs” for more
information.
The following example assumes that the test
schema exists and is assigned to the variable
db
, that the collection
my_collection
exists and that
custom_id
is unique.
MySQL Shell JavaScript Code
// If the _id is provided, it will be honored var result = myColl.add( { _id: 'custom_id', a : 1 } ).execute(); var document = myColl.find("a = 1").execute().fetchOne(); print("User Provided Id:", document._id); // If the _id is not provided, one will be automatically assigned result = myColl.add( { b: 2 } ).execute(); print("Autogenerated Id:", result.getGeneratedIds()[0]);
MySQL Shell Python Code
# If the _id is provided, it will be honored result = myColl.add( { '_id': 'custom_id', 'a' : 1 } ).execute() document = myColl.find('a = 1').execute().fetch_one() print("User Provided Id: %s" % document._id) # If the _id is not provided, one will be automatically assigned result = myColl.add( { 'b': 2 } ).execute() print("Autogenerated Id: %s" % result.get_generated_ids()[0])
Node.js JavaScript Code
// If the _id is provided, it will be honored myColl.add({ _id: 'custom_id', a : 1 }).execute().then(function () { myColl.getOne('custom_id').then(function (doc) { console.log('User Provided Id:', doc._id); }); }); // If the _id is not provided, one will be automatically assigned myColl.add({ b: 2 }).execute().then(function (result) { console.log('Autogenerated Id:', result.getGeneratedIds()[0]); });
C# Code
// If the _id is provided, it will be honored var result = myColl.Add(new { _id = "custom_id", a = 1 }).Execute(); Console.WriteLine("User Provided Id:", result.AutoIncrementValue); // If the _id is not provided, one will be automatically assigned result = myColl.Add(new { b = 2 }).Execute(); Console.WriteLine("Autogenerated Id:", result.AutoIncrementValue);
Python Code
# If the _id is provided, it will be honored result = my_coll.add({'_id': 'custom_id', 'a': 1}).execute() print("User Provided Id: {0}".format(result.get_last_document_id())) # If the _id is not provided, one will be automatically assigned result = my_coll.add({'b': 2}).execute() print("Autogenerated Id: {0}".format(result.get_last_document_id()))
Java Code
// If the _id is provided, it will be honored AddResult result = coll.add("{\"_id\":\"custom_id\",\"a\":1}").execute(); System.out.println("User Provided Id:" + ((JsonString) coll.getOne("custom_id").get("_id")).getString()); // If the _id is not provided, one will be automatically assigned result = coll.add("{\"b\":2}").execute(); System.out.println("Autogenerated Id:" + result.getGeneratedIds().get(0));
C++ Code
// If the _id is provided, it will be honored Result result = myColl.add(R"({ "_id": "custom_id", "a" : 1 })").execute(); std::vector<string> ids = result.getGeneratedIds(); if (ids.empty()) cout << "No Autogenerated Ids" << endl; // If the _id is not provided, one will be automatically assigned result = myColl.add(R"({ "b": 2 })").execute(); ids = result.getGeneratedIds(); cout << "Autogenerated Id:" << ids[0] << endl;
Some documents have a natural unique key. For example, a
collection that holds a list of books is likely to include the
International Standard Book Number (ISBN) for each document that
represents a book. The ISBN is a string with a length of 13
characters which is well within the length limit of the
_id
field.
// using a book's unique ISBN as the object ID myColl.add( { _id: "978-1449374020", title: "MySQL Cookbook: Solutions for Database Developers and Administrators" }).execute();
Use find()
to fetch the newly inserted book
from the collection by its document ID.
var book = myColl.find('_id = "978-1449374020"').execute();
Currently, X DevAPI does not support using any document field
other than the implicit _id
as the document
ID. There is no way of defining a different document ID (primary
key).
The find()
function is used to get documents
from the database. It takes a
SearchConditionStr as a parameter to
specify the documents that should be returned from the database.
Several methods such as fields()
,
sort()
, skip()
and
limit()
can be chained to the
find()
function to further refine the result.
The fetch()
function actually triggers the
execution of the operation. The example assumes that the test
schema exists and that the collection
my_collection
exists.
MySQL Shell JavaScript Code
// Use the collection 'my_collection' var myColl = db.getCollection('my_collection'); // Find a single document that has a field 'name' that starts with 'L' var docs = myColl.find('name like :param'). limit(1).bind('param', 'L%').execute(); print(docs.fetchOne()); // Get all documents with a field 'name' that starts with 'L' docs = myColl.find('name like :param'). bind('param','L%').execute(); var myDoc; while (myDoc = docs.fetchOne()) { print(myDoc); }
MySQL Shell Python Code
# Use the collection 'my_collection' myColl = db.get_collection('my_collection') # Find a single document that has a field 'name' that starts with 'L' docs = myColl.find('name like :param').limit(1).bind('param', 'L%').execute() print(docs.fetch_one()) # Get all documents with a field 'name' that starts with 'L' docs = myColl.find('name like :param').bind('param','L%').execute() myDoc = docs.fetch_one() while myDoc: print(myDoc) myDoc = docs.fetch_one()
Node.js JavaScript Code
// Use the collection 'my_collection' var myColl = db.getCollection('my_collection'); // Find a single document that has a field 'name' that starts with 'L' myColl .find('name like :name') .bind('name', 'L%') .limit(1) .execute(function (doc) { console.log(doc); }) .then(function () { // handle details }); // Get all documents with a field 'name' that starts with 'L' myColl .find('name like :name') .bind('name', 'L%') .execute(function (doc) { console.log(doc); }) .then(function () { // handle details });
C# Code
// Use the collection "my_collection" var myColl = db.GetCollection("my_collection"); // Find a single document that has a field "name" that starts with "L" var docs = myColl.Find("name like :param") .Limit(1).Bind("param", "L%").Execute(); Console.WriteLine(docs.FetchOne()); // Get all documents with a field "name" that starts with "L" docs = myColl.Find("name like :param") .Bind("param", "L%").Execute(); while (docs.Next()) { Console.WriteLine(docs.Current); }
Python Code
# Use the collection 'my_collection' my_coll = my_schema.get_collection('my_collection') # Find a single document that has a field 'name' that starts with 'L' docs = my_coll.find('name like :param').limit(1).bind('param', 'L%').execute() print(docs.fetch_one()) # Get all documents with a field 'name' that starts with 'L' docs = my_coll.find('name like :param').bind('param', 'L%').execute() doc = docs.fetch_one() print(doc)
Java Code
// Use the collection 'my_collection' Collection myColl = db.getCollection("my_collection"); // Find a single document that has a field 'name' that starts with 'L' DocResult docs = myColl.find("name like :name").bind("name", "L%").execute(); System.out.println(docs.fetchOne()); // Get all documents with a field 'name' that starts with 'L' docs = myColl.find("name like :name").bind("name", "L%").execute(); while (docs.hasNext()) { DbDoc myDoc = docs.next(); System.out.println(myDoc); }
C++ Code
// Use the collection 'my_collection' Collection myColl = db.getCollection("my_collection"); // Find a single document that has a field 'name' that starts with 'L' DocResult docs = myColl.find("name like :param") .limit(1).bind("param", "L%").execute(); cout << docs.fetchOne() << endl; // Get all documents with a field 'name' that starts with 'L' docs = myColl.find("name like :param") .bind("param","L%").execute(); DbDoc myDoc; while ((myDoc = docs.fetchOne())) { cout << myDoc << endl; }
For more information, see CollectionFindFunction.