2/12/2008

S2Dao: 2-Way SQL

S2Dao can assemble dynamic query using SQL comment. For example:

@Sql("SELECT * FROM emp WHERE empno = /*empno*/7788")
@Arguments("empno")
public Emp selectEmpByEmpNo(int empNo);

/*empno*/ replaces the given argument and 7788 is removed by S2Dao at runtime. We can execute this SQL without no modification with the database client software such as psql. This solutuion is called "2-Way SQL" in S2Dao.

We can also use IF..ELSE statement as SQL comment.

/*IF hoge != null*/hoge = /*hoge*/'abc'/*END*/

And see the following example. If all conditions are false, S2Dao ignores /*BEGIN*/ ... /*END*/ block. So WHERE phrase is removed when it's not needed.

/*BEGIN*/WHERE
  /*IF job != null*/job = /*job*/'CLERK'/*END*/
  /*IF deptno != null*/AND deptno = /*deptno*/20/*END*/
/*END*/

Again, these SQL can be executed with database client software with no modifications. 2-Way SQL makes possible to test SQL easily.

0 件のコメント: