Open棟梁 動的パラメタライズド・クエリのサンプル

0. template.xml

<?xml version="1.0"?>
<ROOT>
  <WHERE>
    <IF></IF>
  </WHERE>
  <PARAM>
  </PARAM>
</ROOT>

1. demo.xml

<?xml version="1.0"?>
<ROOT>
  SELECT * FROM SHIPPERS
  <WHERE>
    WHERE
      <IF>SHIPPERID = @p1</IF>
  </WHERE>
  <PARAM>
    p1, Int32, 1
  </PARAM>
</ROOT>

2. demo.xml

<?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>

3. demo.xml

<?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>

4. demo.xml

<?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>

5. example.xml

<?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>

6. example.xml

<?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>

7. example.cs

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");

8. example.sql

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

A. example.xml

<?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>

B. example.cs

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");

C. example.sql

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

D. example.xml

<?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>

E. example.xml

<?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>