Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Open棟梁 動的パラメタライズド・クエリのサンプル
<?xml version="1.0"?>
<ROOT>
<WHERE>
<IF></IF>
</WHERE>
<PARAM>
</PARAM>
</ROOT>
<?xml version="1.0"?>
<ROOT>
SELECT * FROM SHIPPERS
<WHERE>
WHERE
<IF>SHIPPERID = @p1</IF>
</WHERE>
<PARAM>
p1, Int32, 1
</PARAM>
</ROOT>
<?xml version="1.0"?>
<ROOT>
SELECT * FROM SHIPPERS
<WHERE>
WHERE
<IF>SHIPPERID = @p1</IF>
<IF>AND COMPANYNAME = @p2</IF>
</WHERE>
<PARAM>
p1, Int32, 1<DIV/>
p2, String, Speedy Express
</PARAM>
</ROOT>
<?xml version="1.0"?>
<ROOT>
SELECT * FROM SHIPPERS
<WHERE>
WHERE
<LIST>SHIPPERID IN (@p1)</LIST>
<IF>AND COMPANYNAME = @p2</IF>
</WHERE>
<PARAM>
p1, Int32, 1, 2
</PARAM>
</ROOT>
<?xml version="1.0"?>
<ROOT>
<!--コメントA-->
SELECT * FROM SHIPPERS
<WHERE>
WHERE
<!--コメントB-->
<IF>SHIPPERID = @p1</IF>
<!--LIST>AND SHIPPERID IN (@p2)</LIST-->
<IF>AND SHIPPERID BETWEEN @p3 AND @p4</IF>
<IF>AND COMPANYNAME <![CDATA[ <> ]]><![CDATA[ @p5 ]]><![CDATA[ /* &&& */ ]]></IF>
<VAL name="pval"/>
</WHERE>
<PARAM>
p1, <!--コメント1-->Int32<!--コメント2-->, <!--コメント3-->1<DIV/>
<!--p2, Int32, 1, 2<DIV/-->
p3, Int32, 1<DIV/>
p4, Int32, 2<DIV/>
<![CDATA[ p5, String, &<> ]]><DIV/>
pval, <![CDATA[ /* &<> */ ]]>
</PARAM>
</ROOT>
<?xml version="1.0" encoding="shift_jis" ?>
<ROOT>
SELECT DISTINCT ctm.companyname, ctm.contactname, ctm.contacttitle FROM orders AS o
INNER JOIN customers AS ctm ON o.customerid = ctm.customerid
<JOIN name="j1">
INNER JOIN shippers AS s ON o.shipvia = s.shipperid
</JOIN>
<JOIN name="j2">
INNER JOIN [order details] AS od ON o.orderid = od.orderid
INNER JOIN products AS p ON od.productid = p.productid
INNER JOIN categories AS cgy ON p.categoryid = cgy.categoryid
</JOIN>
<WHERE>WHERE<IF>s.companyname=@p1</IF><IF>AND cgy.categoryname=@p2</IF></WHERE>
ORDER BY [<VAL name="COLUMN"/>] <VAL name="SEQUENCE"/>
</ROOT>
<?xml version="1.0" encoding="shift_jis" ?>
<ROOT>
SELECT DISTINCT ctm.companyname, ctm.contactname, ctm.contacttitle FROM orders AS o
INNER JOIN customers AS ctm ON o.customerid = ctm.customerid
<JOIN name="j1">
INNER JOIN shippers AS s ON o.shipvia = s.shipperid
</JOIN>
<JOIN name="j2">
INNER JOIN [order details] AS od ON o.orderid = od.orderid
INNER JOIN products AS p ON od.productid = p.productid
INNER JOIN categories AS cgy ON p.categoryid = cgy.categoryid
</JOIN>
<WHERE>WHERE<IF>s.companyname=@p1</IF><IF>AND cgy.categoryname=@p2</IF></WHERE>
ORDER BY [<VAL name="COLUMN"/>] <VAL name="SEQUENCE"/>
</ROOT>
this.SetSqlByFile2("ファイル名");
// パラメタ ライズド クエリのパラメタに対して、動的に値を設定する。
this.SetParameter("j1", true);
this.SetParameter("j2", true);
this.SetParameter("p1", "United Package");
this.SetParameter("P2", "Beverages");
// ユーザ定義パラメタを置換する。
this. SetUserParameter("COLUMN", "ctm.companyname");
this. SetUserParameter("SEQUENCE", "DESC");
SELECT DISTINCT ctm.companyname, ctm.contactname, ctm.contacttitle FROM orders AS o
INNER JOIN customers AS ctm ON o.customerid = ctm.customerid
INNER JOIN shippers AS s ON o.shipvia = s.shipperid
INNER JOIN [order details] AS od ON o.orderid = od.orderid
INNER JOIN products AS p ON od.productid = p.productid
INNER JOIN categories AS cgy ON p.categoryid = cgy.categoryid
WHERE s.companyname=@p1 AND cgy.categoryname=@p2
ORDER BY ctm.[companyname] DESC
<?xml version="1.0" encoding="shift_jis" ?>
<ROOT>
SELECT * FROM PRODUCTS
<WHERE>WHERE
<SUB name="SUB1">CATEGORYID IN(SELECT CATEGORYID FROM CATEGORIES
<WHERE>WHERE
<SUB name="SUB2">CATEGORYID IN(SELECT CATEGORYID FROM CATEGORIES
<WHERE>WHERE
<IF name="ISNOTNULL1">CATEGORYID IS NOT NULL
<ELSE>CATEGORYID IS NULL</ELSE>
</IF>
</WHERE>)
</SUB>
<IF name="ISNOTNULL2">AND CATEGORYID IS NOT NULL
<ELSE>AND CATEGORYID IS NULL</ELSE>
</IF>
</WHERE>)
</SUB>
<IF name="ISNOTNULL3">AND CATEGORYID IS NOT NULL
<ELSE>AND CATEGORYID IS NULL</ELSE>
</IF>
<LIST>AND CATEGORYID IN(@PLIST)</LIST>
AND DISCONTINUED = @BIT
</WHERE>
ORDER BY [<VAL name="COLUMN"/>] <VAL name="SEQUENCE"/>
</ROOT>
this.SetSqlByFile2("ファイル名");
// パラメタ ライズド クエリのパラメタに対して、動的に値を設定する。
this.SetParameter("SUB1", true);
this.SetParameter("SUB2", true);
this.SetParameter("ISNOTNULL1", true);
this.SetParameter("ISNOTNULL2", true);
this.SetParameter("ISNOTNULL3", true);
this.SetParameter("PLIST", new ArrayList(new short[] { 1, 2, 3, 4, 5, 6, 7, 8 }));
this.SetParameter("BIT", true);
// ユーザ定義パラメタを置換する。
this. SetUserParameter("COLUMN", "SUPPLIERID");
this. SetUserParameter("SEQUENCE", "DESC");
SELECT * FROM PRODUCTS WHERE
CATEGORYID IN(SELECT CATEGORYID FROM CATEGORIES
WHERE CATEGORYID IN(SELECT CATEGORYID FROM CATEGORIES
WHERE CATEGORYID IS NOT NULL) AND CATEGORYID IS NOT NULL)
AND CATEGORYID IS NOT NULL
AND CATEGORYID IN(@PLIST_1, @PLIST_2, @PLIST_3, @PLIST_4, @PLIST_5, @PLIST_6, @PLIST_7, @PLIST_8)
AND DISCONTINUED = @BIT
ORDER BY [SUPPLIERID] DESC
<?xml version="1.0" encoding="shift_jis" ?>
<ROOT>
SELECT DISTINCT ctm.companyname, ctm.contactname, ctm.contacttitle FROM orders AS o
INNER JOIN customers AS ctm ON o.customerid = ctm.customerid
<JOIN name="j1">INNER JOIN shippers AS s ON o.shipvia = s.shipperid</JOIN>
<JOIN name="j2">
INNER JOIN [order details] AS od ON o.orderid = od.orderid
INNER JOIN products AS p ON od.productid = p.productid
INNER JOIN categories AS cgy ON p.categoryid = cgy.categoryid
</JOIN>
<WHERE>WHERE s.companyname=@p1</IF><IF>AND cgy.categoryname=@p2</IF></WHERE>
ORDER BY [<VAL name="COLUMN"/>] <VAL name="SEQUENCE"/>
<PARAM>
j1, Boolean, true<DIV/>
j2, Boolean, true<DIV/>
p1, String, United Package<DIV/>
p2, String, Beverages<DIV/>
COLUMN, ctm.companyname<DIV/>
SEQUENCE, DESC<DIV/>
</PARAM>
</ROOT>
<?xml version="1.0" encoding="shift_jis" ?>
<ROOT>
SELECT * FROM PRODUCTS
<WHERE>WHERE
<SUB name="SUB1">CATEGORYID IN(SELECT CATEGORYID FROM CATEGORIES
<WHERE>WHERE
<SUB name="SUB2">CATEGORYID IN(SELECT CATEGORYID FROM CATEGORIES
<WHERE>WHERE
<IF name="ISNOTNULL1">CATEGORYID IS NOT NULL
<ELSE>CATEGORYID IS NULL</ELSE>
</IF>
</WHERE>)
</SUB>
<IF name="ISNOTNULL2">AND CATEGORYID IS NOT NULL
<ELSE>AND CATEGORYID IS NULL</ELSE>
</IF>
</WHERE>)
</SUB>
<IF name="ISNOTNULL3">AND CATEGORYID IS NOT NULL
<ELSE>AND CATEGORYID IS NULL</ELSE>
</IF>
<LIST>AND CATEGORYID IN(@PLIST)</LIST>
AND DISCONTINUED = @BIT
</WHERE>
ORDER BY [<VAL name="COLUMN"/>] <VAL name="SEQUENCE"/>
<PARAM>
SUB1, Boolean, true<DIV/>
SUB2, Boolean, true<DIV/>
ISNOTNULL1, Boolean, true<DIV/>
ISNOTNULL2, Boolean, true<DIV/>
ISNOTNULL3, Boolean, true<DIV/>
PLIST, Int16, 1, 2, 3, 4, 5, 6, 7, 8<DIV/>
BIT, Boolean, true<DIV/>
COLUMN, SUPPLIERID<DIV/>
SEQUENCE, DESC<DIV/>
</PARAM>
</ROOT>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.