X DevAPI User Guide
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;