文件首頁
X DevAPI 使用者指南
下載本手冊
PDF (美式信紙) - 1.4Mb
PDF (A4) - 1.4Mb
X DevAPI 使用者指南  /  CRUD 操作  /  參數綁定

3.4 參數綁定

最佳做法是將值與運算式字串分開，而不是直接在運算式字串中使用值。這可以使用運算式字串中的參數，並使用 bind() 函式將值繫結至參數來完成。

參數可以使用下列方式指定：匿名和具名。

參數類型

語法

範例

允許在 CRUD 操作中使用

允許在 SQL 字串中使用

匿名

?

'age > ?'

否

是

具名

:

'age > :age'

是

否

以下範例說明如何在 execute() 函式之前使用 bind() 函式。對於每個具名參數，請向 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 Shell 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() 和 set()（或 find() 和 Fields()）會鏈結在一起。這兩種方法都會採用具有預留位置的運算式。預留位置會參考一個組合的命名空間。兩者都使用一個名為 :param 的預留位置。使用 :param 的一個名稱值參數對 bind() 進行單次呼叫，以將預留位置值指派給鏈結方法中 :param 的兩個實例。

MySQL Shell 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 Shell 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() 和 execute() 或 execute() 直接繫結並執行 CRUD 操作，也可以將 CRUD 操作物件儲存在變數中以供稍後執行。

這樣做的好處是能夠將多組變數繫結至運算式字串中定義的參數，因此在執行大量類似操作時獲得更好的效能。此範例假設 test 綱要已指派給變數 db，且集合 my_collection 存在。

MySQL Shell 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 Shell 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();

上一個   首頁   向上   下一個
下載本手冊
PDF (美式信紙) - 1.4Mb
PDF (A4) - 1.4Mb