Chapter 6 Working with Relational Tables

Table of Contents

6.1 Syntax of the SQL CRUD Functions

The X DevAPI SQL CRUD functions allow you to work with relational tables in manners similar to using traditional SQL statements. The following code sample shows how to use the add() and select() methods of the X DevAPI SQL CRUD functions, which are similar to running INSERT and SELECT statements on a table with an SQL client. Compare this with the examples found in Section 4.3, “Collection CRUD Function Overview” to see the differences and similarities between the CRUD functions for tables and collections in the X DevAPI.

MySQL Shell JavaScript Code

// Working with Relational Tables
var mysqlx = require('mysqlx');

// Connect to server using a connection URL
var mySession = mysqlx.getSession( {
  host: 'localhost', port: 33060,
  user: 'user', password: 'password'} )

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

// Accessing an existing table
var myTable = myDb.getTable('my_table');

// Insert SQL Table data
myTable.insert(['name', 'birthday', 'age']).
  values('Laurie', mysqlx.dateValue(2000, 5, 27), 19).execute();

// Find a row in the SQL Table
var myResult = myTable.select(['_id', 'name', 'birthday']).
  where('name like :name AND age < :age').
  bind('name', 'L%').bind('age', 30).execute();

// Print result
print(myResult.fetchOne());

MySQL Shell Python Code

# Working with Relational Tables
from mysqlsh import mysqlx

# Connect to server using a connection URL
mySession = mysqlx.get_session( {
  'host': 'localhost', 'port': 33060,
  'user': 'user', 'password': 'password'} )

myDb = mySession.get_schema('test')

# Accessing an existing table
myTable = myDb.get_table('my_table')

# Insert SQL Table data
myTable.insert(['name','birthday','age']) \
  .values('Laurie', mysqlx.date_value(2000, 5, 27), 19).execute()

# Find a row in the SQL Table
myResult = myTable.select(['_id', 'name', 'birthday']) \
  .where('name like :name AND age < :age') \
  .bind('name', 'L%') \
  .bind('age', 30).execute()

# Print result
print(myResult.fetch_all())

Node.js JavaScript Code

// Working with Relational Tables
var mysqlx = require('@mysql/xdevapi');
var myTable;

// Connect to server using a connection URL
mysqlx
  .getSession({
    user: 'user',
    password: 'password',
    host: 'localhost',
    port: 33060
  })
  .then(function (session) {
    // Accessing an existing table
    myTable = session.getSchema('test').getTable('my_table');

    // Insert SQL Table data
    return myTable
      .insert(['name', 'birthday', 'age'])
      .values(['Laurie', '2000-5-27', 19])
      .execute()
  })
  .then(function () {
    // Find a row in the SQL Table
    return myTable
        .select(['_id', 'name', 'birthday'])
        .where('name like :name && age < :age)')
        .bind('name', 'L%')
        .bind('age', 30)
        .execute();
  })
  .then(function (myResult) {
    console.log(myResult.fetchAll());
  });

C# Code

// Working with Relational Tables

// Connect to server using a connection
var db = MySQLX.GetSession(
  "server=localhost;port=33060;user=user;password=password")
.GetSchema("test");

// Accessing an existing table
var myTable = db.GetTable("my_table");

// Insert SQL Table data
myTable.Insert("name", "age")
.Values("Laurie", "19").Execute();

// Find a row in the SQL Table
var myResult = myTable.Select("_id, name, age")
.Where("name like :name AND age < :age")
.Bind(new { name = "L%", age = 30 }).Execute();

// Print result
PrintResult(myResult.FetchAll());

Python Code

# Working with Relational Tables
import mysqlx

# Connect to server using a connection URL
my_session = mysqlx.get_session({
    'host': 'localhost', 'port': 33060,
    'user': 'user', 'password': 'password'
})

my_schema = my_session.get_schema('test')

# Accessing an existing table
my_table = my_schema.get_table('my_table')

# Insert SQL Table data
my_table.insert(['name', 'birthday', 'age']) \
    .values('Laurie', mysqlx.date_value(2000, 5, 27), 19).execute()

# Find a row in the SQL Table
result = my_table.select(['_id', 'name', 'birthday']) \
    .where('name like :name AND age < :age') \
    .bind('name', 'L%') \
    .bind('age', 30).execute()

# Print result
print(result.fetch_all())

Java Code

// Working with Relational Tables
import com.mysql.cj.xdevapi.*;

// Connect to server using a connection URL
Session mySession = new SessionFactory().getSession("mysqlx://localhost:33060/test?user=user&password=password");
Schema db = mySession.getSchema("test");

// Accessing an existing table
Table myTable = db.getTable("my_table");

// Insert SQL Table data
myTable.insert("name", "birthday", "age").values("Laurie", "2000-05-27", 19).execute();

// Find a row in the SQL Table
RowResult myResult = myTable.select("_id, name, birthday")
     .where("name like :name AND age < :age")
     .bind("name", "L%").bind("age", 30).execute();

// Print result
myResult.forEach(r -> 
      System.out.println(r.getString(1) + ": " + r.getDate(2)));

C++ Code

// Working with Relational Tables
#include <mysqlx/xdevapi.h>

// Connect to server using a connection URL
Session mySession(33060, "user", "password");

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

// Accessing an existing table
Table myTable = myDb.getTable("my_table");

// Insert SQL Table data
myTable.insert("name", "birthday", "age")
       .values("Laurie", "2000-5-27", 19).execute();

// Find a row in the SQL Table
RowResult myResult = myTable.select("_id", "name", "birthday")
  .where("name like :name AND age < :age")
  .bind("name", "L%").bind("age", 30).execute();

// Print result
Row row = myResult.fetchOne();
cout << "     _id: " << row[0] << endl;
cout << "    name: " << row[1] << endl;
cout << "birthday: " << row[2] << endl;