X DevAPI 用户指南  / 第三章CRUD操作  /  3.4 参数绑定

3.4 参数绑定

最好不要在表达式字符串中直接使用值，而是将值与表达式字符串分开。这是使用表达式字符串中的参数和 bind()将值绑定到参数的函数来完成的。

可以通过以下方式指定参数：匿名和命名。

参数类型

句法

例子

在 CRUD 操作中允许

在 SQL 字符串中允许

匿名的

？

'年龄 > ?'

不

是的

命名的

:<名称>

'年龄>：年龄'

是的

不

以下示例显示如何在 bind()函数之前使用 execute()函数。对于每个命名参数，提供一个bind()包含参数名称及其值的参数。传递参数值对的顺序bind()并不重要。该示例假定test 模式已分配给变量db并且集合my_collection存在。

MySQL Shell 和 Node.js JavaScript 代码 

// Collection.find() function with fixed values
var myColl = db.getCollection('my_collection');

var myRes1 = myColl.find('age = 18').execute();

// Using the .bind() function to bind parameters
var myRes2 = myColl.find('name = :param1 AND age = :param2').bind('param1','Rohit').bind('param2', 18).execute();

// Using named parameters
myColl.modify('name = :param').set('age', 55).
        bind('param', 'Nadya').execute();

// Binding works for all CRUD statements except add()
var myRes3 = myColl.find('name like :param').
        bind('param', 'R%').execute();

当使用 Connector/Node.js 运行它时，请注意 execute()返回一个 Promise。您可能想要检查结果以避免错误丢失。

MySQL 外壳 Python 代码 

# Collection.find() function with hardcoded values
myColl = db.get_collection('my_collection')

myRes1 = myColl.find('age = 18').execute()

# Using the .bind() function to bind parameters
myRes2 = myColl.find('name = :param1 AND age = :param2').bind('param1','Rohit').bind('param2', 18).execute()

# Using named parameters
myColl.modify('name = :param').set('age', 55).bind('param', 'Nadya').execute()

# Binding works for all CRUD statements except add()
myRes3 = myColl.find('name like :param').bind('param', 'R%').execute()

C#代码 

// Collection.Find() function with fixed values
var myColl = db.GetCollection("my_collection");

var myRes1 = myColl.Find("age = 18").Execute();

// Using the .Bind() function to bind parameters
var myRes2 = myColl.Find("name = :param1 AND age = :param2").Bind("param1", "Rohit").Bind("param2", 18).Execute();

// Using named parameters
myColl.Modify("name = :param").Set("age", 55)
  .Bind("param", "Nadya").Execute();

// Binding works for all CRUD statements except Add()
var myRes3 = myColl.Find("name like :param")
  .Bind("param", "R%").Execute();

Python代码 

# Collection.find() function with hardcoded values
my_coll = my_schema.get_collection('my_collection')

my_res_1 = my_coll.find('age = 18').execute()

# Using the .bind() function to bind parameters
my_res_2 = my_coll.find('name = :param1 AND age = :param2').bind('param1', 'Rohit').bind('param2', 18).execute()

# Using named parameters
my_coll.modify('name = :param').set('age', 55).bind('param', 'Nadya').execute()

# Binding works for all CRUD statements except add()
my_res_3 = my_coll.find('name like :param').bind('param', 'R%').execute()

Java代码 

// Collection.find() function with fixed values
Collection myColl = db.getCollection("my_collection");

DocResult myRes1 = myColl.find("age = 18").execute();

// Using the .bind() function to bind parameters
DocResult myRes2 = myColl.find("name = :param1 AND age = :param2").bind("param1", "Rohit").bind("param2", 18).execute();

// Using named parameters
myColl.modify("name = :param").set("age", 55)
  .bind("param", "Nadya").execute();

// Using named parameters with a Map
Map<String, Object> params = new HashMap<>();
params.put("name", "Nadya");
myColl.modify("name = :name").set(".age", 55).bind(params).execute();

// Binding works for all CRUD statements except add()
DocResult myRes3 = myColl.find("name like :param")
  .bind("param", "R%").execute();    }

C++代码 

/// Collection.find() function with fixed values
Collection myColl = db.getCollection("my_collection");

auto myRes1 = myColl.find("age = 18").execute();

// Using the .bind() function to bind parameters
auto myRes2 = myColl.find("name = :param1 AND age = :param2")
                    .bind("param1","Rohit").bind("param2", 18)
                    .execute();

// Using named parameters
myColl.modify("name = :param").set("age", 55)
      .bind("param", "Nadya").execute();

// Binding works for all CRUD statements except add()
auto myRes3 = myColl.find("name like :param")
                    .bind("param", "R%").execute();

X DevAPI 不支持匿名占位符。此限制通过使用占位符的多种方法提高了 CRUD 命令链中的代码清晰度。无论 使用何种bind()语法变体，参数和基于参数名称的占位符之间始终存在明确的关联。

CRUD 命令链的所有方法为占位符形成一个命名空间。在以下示例中， modify() and set() （或 find() and Fields()） 被链接在一起。这两种方法都采用带占位符的表达式。占位符指的是一个组合命名空间。两者都使用一个名为:param. bind()使用一个名称值参数 for 的单个调用 :param用于将占位符值分配给:param链式方法中的两次出现.

MySQL 外壳 JavaScript 代码 

// one bind() per parameter
var myColl = db.getCollection('relatives');
var juniors = myColl.find('alias = "jr"').execute().fetchAll();

for (var index in juniors){
  myColl.modify('name = :param').
    set('parent_name',mysqlx.expr(':param')).
    bind('param', juniors[index].name).execute();
}

MySQL 外壳 Python 代码 

# one bind() per parameter
myColl = db.get_collection('relatives')
juniors = myColl.find('alias = "jr"').execute().fetch_all()

for junior in juniors:
  myColl.modify('name = :param'). \
    set('parent_name',mysqlx.expr(':param')). \
    bind('param', junior.name).execute()

Node.js JavaScript 代码 

// one bind() per parameter
db
  .getCollection('relatives');
  .find('alias = "jr"')
  .execute(function (junior) {
    return myColl
      .modify('name = :param')
      .set('parent_name', mysqlx.expr(':param'))
      .bind('param', junior.name)
      .execute();
  });

C#代码 

// one bind() per parameter
myColl.Find("a = :param").Fields(":param as b")
  .Bind(new { param = "c"}).Execute();

Python代码 

# one bind() per parameter
my_coll = my_schema.get_collection('relatives')
juniors = my_coll.find('alias = "jr"').execute().fetch_all()

for junior in juniors:
    my_coll.modify('name = :param') \
        .set('parent_name', mysqlx.expr(':param')) \
        .bind('param', junior.name).execute()

Java代码 

# one bind() per parameter
Collection myColl = db.getCollection("relatives");
DocResult juniors = myColl.find("alias = 'Jr.'").execute();
while (juniors.hasNext()){
    myColl.modify("name = :param")
            .set("parent_name", Expression.expr(":param"))
            .bind("param", juniors.next().get("name"))
            .execute();
}

C++代码 

// one bind() per parameter
Collection myColl = db.getCollection("relatives");
DocResult  juniors = myColl.find("alias = 'jr'").execute();

DbDoc junior;
while ((junior = juniors.fetchOne()))
{
  myColl.modify("name = :param")
        .set("parent_name", expr(":param"))
        .bind("param", junior["name"]).execute();
}

命名参数不允许使用以数字开头的名称。例如，:1one和 :1是不允许的。

准备 CRUD 语句

bind()除了用and execute()or 直接绑定和执行 CRUD 操作之外， execute()还可以将 CRUD 操作对象存储在变量中以供以后执行。

这样做的好处是可以将几组变量绑定到表达式字符串中定义的参数，从而在执行大量类似操作时获得更好的性能。该示例假定 test模式已分配给变量 db并且集合 my_collection存在。

MySQL 外壳 JavaScript 代码 

var myColl = db.getCollection('my_collection');

// Only prepare a Collection.remove() operation, but do not run it yet
var myRemove = myColl.remove('name = :param1 AND age = :param2');

// Binding parameters to the prepared function and .execute()
myRemove.bind('param1', 'Leon').bind('param2', 39).execute();
myRemove.bind('param1', 'Johannes').bind('param2', 28).execute();

// Binding works for all CRUD statements but add()
var myFind = myColl.find('name like :param1 AND age > :param2');

var myDocs = myFind.bind('param1', 'L%').bind('param2', 20).execute();
var MyOtherDocs = myFind.bind('param1', 'J%').bind('param2', 25).execute();

MySQL 外壳 Python 代码 

myColl = db.get_collection('my_collection')

# Only prepare a Collection.remove() operation, but do not run it yet
myRemove = myColl.remove('name = :param1 AND age = :param2')

# Binding parameters to the prepared function and .execute()
myRemove.bind('param1', 'Leon').bind('param2', 39).execute()
myRemove.bind('param1', 'Johannes').bind('param2', 28).execute()

# Binding works for all CRUD statements but add()
myFind = myColl.find('name like :param1 AND age > :param2')

myDocs = myFind.bind('param1', 'L%').bind('param2', 20).execute()
MyOtherDocs = myFind.bind('param1', 'J%').bind('param2', 25).execute()

Node.js JavaScript 代码 

var myColl = db.getCollection('my_collection');

// Only prepare a Collection.remove() operation, but do not run it yet
var myRemove = myColl.remove('name = :param1 AND age = :param2');

// Binding parameters to the prepared function and .execute()
myRemove.bind('param1', 'Leon').bind('param2', 39).execute();
myRemove.bind('param1', 'Johannes').bind('param2', 28).execute();

// Binding works for all CRUD statements but add()
var myFind = myColl.find('name like :param1 AND age > :param2');

var myDocs = myFind.bind('param1', 'L%').bind('param2', 20).execute();
var MyOtherDocs = myFind.bind('param1', 'J%').bind('param2', 25).execute();

C#代码 

var myColl = db.GetCollection("my_collection");

// Only prepare a Collection.Remove() operation, but do not run it yet
var myRemove = myColl.Remove("name = :param1 AND age = :param2");

// Binding parameters to the prepared function and .Execute()
myRemove.Bind("param1", "Leon").Bind("param2", 39).Execute();
myRemove.Bind("param1", "Johannes").Bind("param2", 28).Execute();

// Binding works for all CRUD statements but Add()
var myFind = myColl.Find("name like :param1 AND age > :param2");

var myDocs = myFind.Bind("param1", "L%").Bind("param2", 20).Execute();
var MyOtherDocs = myFind.Bind("param1", "J%").Bind("param2", 25).Execute();

Python代码 

my_coll = my_schema.get_collection('my_collection')

# Only prepare a Collection.remove() operation, but do not run it yet
my_remove = my_coll.remove('name = :param1 AND age = :param2')

# Binding parameters to the prepared function and .execute()
my_remove.bind('param1', 'Leon').bind('param2', 39).execute()
my_remove.bind('param1', 'Johannes').bind('param2', 28).execute()

# Binding works for all CRUD statements but add()
my_find = my_coll.find('name like :param1 AND age > :param2')

my_docs = my_find.bind('param1', 'L%').bind('param2', 20).execute()
my_other_docs = my_find.bind('param1', 'J%').bind('param2', 25).execute()

Java代码 

Collection myColl = db.getCollection("my_collection");

// Create Collection.remove() operation, but do not run it yet
RemoveStatement myRemove = myColl.remove("name = :param1 AND age = :param2");

// Binding parameters to the prepared function and .execute()
myRemove.bind("param1", "Leon").bind("param2", 39).execute();
myRemove.bind("param1", "Johannes").bind("param2", 28).execute();

// Binding works for all CRUD statements but add()
FindStatement myFind = myColl.find("name LIKE :name AND age > :age");

Map<String, Object> params = new HashMap<>();
params.put("name", "L%");
params.put("age", 20);
DocResult myDocs = myFind.bind(params).execute();
params.put("name", "J%");
params.put("age", 25);
DocResult myOtherDocs = myFind.bind(params).execute();

C++代码 

Collection myColl = db.getCollection("my_collection");

// Create Collection.remove() operation, but do not run it yet
auto myRemove = myColl.remove("name = :param1 AND age = :param2");

// Binding parameters to the prepared function and .execute()
myRemove.bind("param1", "Leon").bind("param2", 39).execute();
myRemove.bind("param1", "Johannes").bind("param2", 28).execute();

// Binding works for all CRUD statements but Add()
auto myFind = myColl.find("name like :param1 AND age > :param2");

auto myDocs = myFind.bind("param1", "L%").bind("param2", 20).execute();
auto MyOtherDocs = myFind.bind("param1", "J%").bind("param2", 25).execute();