Documentation Home
X DevAPI User Guide
Download this Manual

X DevAPI User Guide  /  Working with Collections  /  Basic CRUD Operations on Collections

Pre-General Availability Draft: 2018-02-20

4.1 Basic CRUD Operations on Collections

Working with collections of documents is straight forward when using the X DevAPI. The following examples show the basic usage of CRUD operations when working with documents.

After establishing a connection to a MySQL Server, a new collection that can hold JSON documents is created and several documents are inserted. Then, a find operation is executed to search for a specific document from the collection. Finally, the collection is dropped again from the database. The example assumes that the test schema exists and that the collection my_collection does not exist.

MySQL Shell JavaScript Code

// Connecting to MySQL Server and working with a Collection

var mysqlx = require('mysqlx');

// Connect to server
var mySession = mysqlx.getNodeSession( {
host: 'localhost', port: 33060,
dbUser: 'mike', dbPassword: 's3cr3t!'} );

var myDb = mySession.getSchema('test');

// Create a new collection 'my_collection'
var myColl = myDb.createCollection('my_collection');

// Insert documents
myColl.add({name: 'Sakila', age: 15}).execute();
myColl.add({name: 'Susanne', age: 24}).execute();
myColl.add({name: 'Mike', age: 39}).execute();

// Find a document
var docs = myColl.find('name like :param1 AND age < :param2').limit(1).
        bind('param1','S%').bind('param2',20).execute();

// Print document
print(docs.fetchOne());

// Drop the collection
session.dropCollection('test','my_collection');

MySQL Shell Python Code

# Connecting to MySQL Server and working with a Collection

import mysqlx

# Connect to server
mySession = mysqlx.get_session( {
'host': 'localhost', 'port': 33060,
'dbUser': 'mike', 'dbPassword': 's3cr3t!'} )

myDb = mySession.get_schema('test')

# Create a new collection 'my_collection'
myColl = myDb.create_collection('my_collection')

# Insert documents
myColl.add({'name': 'Sakila', 'age': 15}).execute()
myColl.add({'name': 'Susanne', 'age': 24}).execute()
myColl.add({'name': 'Mike', 'age': 39}).execute()

# Find a document
docs = myColl.find('name like :param1 AND age < :param2') \
          .limit(1) \
          .bind('param1','S%') \
          .bind('param2',20) \
          .execute()

# Print document
doc = docs.fetch_one()
print doc

# Drop the collection
session.drop_collection('test','my_collection')

Node.js JavaScript Code

// --------------------------------------------------
// Connecting to MySQL Server and working with a Collection
var mysqlx = require('@mysql/xdevapi');
var db;

// Connect to server
mysqlx
  .getSession({
    dbUser: 'mike',
    dbPassword: 's3cr3t!',
    host: 'localhost',
    port: '33060',
  })
  .then(function (session) {
    db = session.getSchema('test');
    // Create a new collection 'my_collection'
    return db.createCollection('my_collection');
  })
  .then(function (myColl) {
    // Insert documents
    return Promise
      .all([
        myColl.add({ name: 'Sakila', age: 15 }).execute(),
        myColl.add({ name: 'Susanne', age: 24 }).execute(),
        myColl.add({ name: 'Mike', age: 39 }).execute()
      ])
      .then(function () {
        // Find a document
        return myColl
            .find('name like :name && age < :age')
            .bind({ name: 'S%', age: 20 })
            .limit(1)
            .execute(function (doc) {
              // Print document
              console.log(doc);
            });
      });
  })
  .then(function(docs) {
    // Drop the collection
    return db.dropCollection('my_collection');
  })
  .catch(function(err) {
    // Handle error
  });

C# Code

{
// Connecting to MySQL Server and working with a Collection

// Connect to server
var mySession = MySQLX.GetSession("server=localhost;port=33060;user=mike;password=s3cr3t!;");

var myDb = mySession.GetSchema("test");

// Create a new collection "my_collection"
var myColl = myDb.CreateCollection("my_collection");

// Insert documents
myColl.Add(new { name = "Sakila", age = 15}).Execute();
myColl.Add(new { name = "Susanne", age = 24}).Execute();
myColl.Add(new { name = "Mike", age = 39}).Execute();

// Find a document
var docs = myColl.Find("name like :param1 AND age < :param2").Limit(1)
.Bind("param1", "S%").Bind("param2", 20).Execute();

// Print document
Console.WriteLine(docs.FetchOne());

// Drop the collection
myDb.DropCollection("my_collection");
}

Java Code

// Connect to server
Session mySession = new SessionFactory().getSession("mysqlx://localhost:33060/test?user=mike&amp;password=s3cr3t!");

Schema myDb = mySession.getSchema("test");

// Create a new collection 'my_collection'
Collection myColl = myDb.createCollection("my_collection");

// Insert documents
myColl.add("{\"name\":\"Sakila\", \"age\":15}").execute();
myColl.add("{\"name\":\"Susanne\", \"age\":24}").execute();
myColl.add("{\"name\":\"Mike\", \"age\":39}").execute();

// Find a document
DocResult docs = myColl.find("name like :name AND age < :age")
        .bind("name", 20).bind("age", 20).execute();

// Print document
DbDoc doc = docs.fetchOne();
System.out.println(doc);

// Drop the collection
myDB.dropCollection("test", "my_collection");

C++ Code

// Connecting to MySQL Server and working with a Collection

#include <mysql_devapi.h>

// Connect to server
Session session(33060, "mike", "s3cr3t!");
Schema db = session.getSchema("test");

// Create a new collection 'my_collection'
Collection myColl = db.createCollection("my_collection");

// Insert documents
myColl.add(R"({"name": "Sakila", "age": 15})").execute();
myColl.add(R"({"name": "Susanne", "age": 24})").execute();
myColl.add(R"({"name": "Mike", "age": 39})").execute();

// Find a document
DocResult docs = myColl.find("name like :param1 AND age < :param2").limit(1)
                        .bind("param1","S%").bind("param2",20).execute();

// Print document
cout << docs.fetchOne();

// Drop the collection
session.dropCollection("test", "my_collection");

User Comments
Sign Up Login You must be logged in to post a comment.