給予適當的建構資料,就可以得到我們所需要的物件.
詳細的說明可以參考後面所附的參考資料,那些資料就已經寫的很好了,我想我沒必要再畫蛇添足.
最初製作 SQL Builder class 的時候是想說,我只要給予它一些欄位和條件,他就會自動幫我產生 SQL 敘述,而不必再去考量 SQL 敘述是否正確啊…等等的問題.剛好想到 Builder pattern 其實很適合,所以就實做看看.
目前這個 class 已經蠻齊全了,不過有些地方可以再加強.
1. Join 部分
2. 資料型態轉換,作的不好~應該可以再更精簡,更可靠.
3. 傳入 select – sql 敘述,就能產生 delete-sql, update-sql.
4. …
參考資料:
http://www.dotspace.idv.tw/Patterns/Jdon_Builder.htm
http://140.109.17.201/Jyemii/patternscolumn/articles/DesignPatternPart(2).htm
Design Pattern 中譯本 – 葉秉哲譯
範例程式:
* 主程式
LOCAL lc_sql
LOCAL lo_builder
lo_builder=CREATEOBJECT(“csqlbuilder”)
lo_builder.ADDCOLUMN(“u_id”)
lo_builder.ADDCOLUMN(“u_name”)
lo_builder.setTable(“users”)
lo_builder.addWhere(“”, “u_id”, “=”, “drury”)
lo_builder.addOrderby( “u_id” )
lo_builder.addGroupby( “u_id” )
? “=====”
? “select sql::”
?? lo_builder.getSelectSQL()
lo_builder.reconf()
lo_builder.addPair(“u_id”, “ellery”)
lo_builder.addPair(“u_name”, “ellery”)
lo_builder.setTable(“users”)
lo_builder.addWhere(“”, “u_id”, “=”, “drury”)
? “=====”
? “insert sql::” + lo_builder.getInsertSQL()
? “update sql::” + lo_builder.getUpdateSQL()
? “delete sql::” + lo_builder.getDeleteSQL()
*
* Builder pattern 類別實作
* 此類別適用環境: VFP 8.0
* 類別定義開始
*
DEFINE CLASS CSQLBuilder AS CUSTOM
ADD OBJECT PROTECTED m_pairs AS COLLECTION
ADD OBJECT PROTECTED m_where AS COLLECTION
ADD OBJECT PROTECTED m_groupby AS COLLECTION
ADD OBJECT PROTECTED m_orderby AS COLLECTION
ADD OBJECT PROTECTED m_keys as collection
m_table = “” && the table
m_targettype=”” && cursor, table
m_target=”” && name
* Init
PROCEDURE INIT
ENDPROC
* Destroy
PROCEDURE DESTROY
ENDPROC
* Reconfigure
PROCEDURE reconf
* MSDN said: pass -1 will clear all items
THIS.m_pairs.REMOVE( -1 )
THIS.m_where.REMOVE( -1 )
THIS.m_groupby.REMOVE( -1 )
THIS.m_orderby.REMOVE( -1 )
THIS.m_table = “”
THIS.m_targettype=””
THIS.m_target=””
ENDPROC
* addColumns
PROCEDURE ADDCOLUMN( c_field AS STRING )
THIS.m_pairs.ADD( .NULL., c_field )
ENDPROC
PROCEDURE addWhere( c_logical AS STRING, c_field AS STRING, c_operator AS STRING, o_value AS OBJECT )
THIS.m_where.ADD( o_value, c_logical + c_field + c_operator )
ENDPROC
PROCEDURE addPair( c_field AS STRING, o_value AS OBJECT )
THIS.m_pairs.ADD( o_value, c_field )
ENDPROC
PROCEDURE addGroupby( c_field AS STRING )
THIS.m_groupby.ADD( .NULL., c_field )
ENDPROC
PROCEDURE addOrderby( c_field AS STRING )
THIS.m_orderby.ADD( .NULL., c_field )
ENDPROC
PROCEDURE addKey( c_field as String )
this.m_keys.add( .null., c_field )
ENDPROC
PROCEDURE addJoin
* todo: this is the most hard part.
ENDPROC
PROCEDURE setTable( c_table AS STRING )
THIS.m_table=c_table
ENDPROC
PROCEDURE setTarget( c_type AS STRING, c_target AS STRING )
THIS.m_targettype=c_type
THIS.m_target=c_string
ENDPROC
PROCEDURE getInsertSQL
LOCAL i
LOCAL lc_sql
LOCAL lc_fields, lc_values, lc_type
lc_fields=””
lc_values=””
lc_sql=”insert into ” + THIS.m_table + ” ”
FOR i=1 TO THIS.m_pairs.COUNT
lc_fields=lc_fields+THIS.m_pairs.GETKEY(i)
IF( i+1 <= THIS.m_pairs.COUNT )
lc_fields=lc_fields+","
ENDIF
lc_type=VARTYPE( THIS.m_pairs.ITEM(i) )
DO CASE
CASE lc_type="C"
lc_values=lc_values+ "'" + THIS.m_pairs.ITEM(i)+ "'"
CASE INLIST( lc_type, "N", "Y" )
lc_values=lc_values + ALLTRIM( STR(THIS.m_pairs.ITEM(i) ) )
CASE lc_type="D"
lc_values=lc_values + "{^" + DTOC( THIS.m_pairs.ITEM(i) ) + "}"
CASE lc_type="L"
lc_values=lc_values + IIF( THIS.m_pairs.ITEM(i), ".T.", ".F." )
CASE lc_type="X"
lc_values=lc_values + ".null."
ENDCASE
IF( i+1 =1 )
lc_sql=” where ”
ELSE
lc_sql=””
ENDIF
FOR i=1 TO THIS.m_where.COUNT
lc_type=VARTYPE( THIS.m_where.ITEM(i) )
DO CASE
CASE lc_type=”C”
lc_value= “‘” + THIS.m_where.ITEM(i)+ “‘”
CASE INLIST( lc_type, “N”, “Y” )
lc_value= ALLTRIM( STR(THIS.m_where.ITEM(i) ) )
CASE lc_type=”D”
lc_value=”{” + DTOC( THIS.m_where.ITEM(i) ) + “}”
CASE lc_type=”L”
lc_value=IIF( THIS.m_where.ITEM(i), “.T.”, “.F.” )
CASE lc_type=”X”
lc_value=”.null.”
ENDCASE
lc_sql=lc_sql + THIS.m_where.GETKEY(i) + lc_value
IF( i+1 <= THIS.m_where.COUNT )
lc_sql=lc_sql+","
ENDIF
NEXT
RETURN lc_sql
ENDPROC
PROCEDURE getOnlyKey( o_collection AS COLLECTION )
LOCAL i
LOCAL lc_sql
lc_sql=""
FOR i=1 TO o_collection.COUNT
lc_sql=lc_sql+o_collection.GETKEY(i)
IF( i+1 <= o_collection.COUNT )
lc_sql=lc_sql+","
ENDIF
NEXT
RETURN lc_sql
ENDPROC
PROCEDURE getOrderBySQL
LOCAL lc_sql
lc_sql=THIS.getOnlyKey( THIS.m_orderby )
IF( EMPTY(lc_sql) )
RETURN lc_sql
ELSE
RETURN " order by " + lc_sql
ENDIF
ENDPROC
PROCEDURE getGroupbySQL
LOCAL lc_sql
lc_sql=THIS.getOnlyKey( THIS.m_groupby )
IF( EMPTY(lc_sql) )
RETURN lc_sql
ELSE
RETURN " group by " + lc_sql
ENDIF
ENDPROC
PROCEDURE getSelectSQL
LOCAL i
LOCAL lc_sql, lc_where, lc_orderby, lc_groupby
lc_sql="select "
FOR i=1 TO THIS.m_pairs.COUNT
lc_sql=lc_sql+THIS.m_pairs.GETKEY(i)
IF( i+1 <= THIS.m_pairs.COUNT )
lc_sql=lc_sql+","
ENDIF
NEXT
lc_sql=lc_sql + " from " + THIS.m_table
lc_where=THIS.getWhereSQL()
lc_sql=lc_sql + lc_where
lc_orderby=THIS.getOrderBySQL()
lc_sql=lc_sql+lc_orderby
lc_groupby=THIS.getGroupbySQL()
lc_sql=lc_sql+lc_groupby
RETURN lc_sql
ENDPROC
PROCEDURE getDeleteSQL
LOCAL lc_sql
LOCAL i
LOCAL lc_value, lc_type, lc_where
lc_sql="delete from " + THIS.m_table
lc_where=THIS.getWhereSQL()
lc_sql=lc_sql+lc_where
RETURN lc_sql
ENDPROC
PROCEDURE getUpdateSQL
LOCAL i
LOCAL lc_sql
LOCAL lc_fields, lc_values, lc_type, lc_where
lc_fields=""
lc_values=""
lc_sql="update " + THIS.m_table + " set "
FOR i=1 TO THIS.m_pairs.COUNT
lc_sql=lc_sql+THIS.m_pairs.GETKEY(i)+"="
lc_type=VARTYPE( THIS.m_pairs.ITEM(i) )
DO CASE
CASE lc_type="C"
lc_sql=lc_sql + "'" + THIS.m_pairs.ITEM(i) + "'"
CASE INLIST( lc_type, "N", "Y" )
lc_sql=lc_sql + ALLTRIM( STR(THIS.m_pairs.ITEM(i) ) )
CASE lc_type="D"
lc_sql=lc_sql + "{^" + DTOC( THIS.m_pairs.ITEM(i) ) + "}"
CASE lc_type="L"
lc_values=lc_sql + IIF( THIS.m_pairs.ITEM(i), ".T.", ".F." )
CASE lc_type="X"
lc_values=lc_sql + ".null."
ENDCASE
IF( i+1 =1 )
lc_fields=””
ELSE
RETURN “”
ENDIF
FOR i=1 TO THIS.m_keys.COUNT
lc_fields=lc_fields+this.m_keys.getKey(i)
IF( i+1 <= THIS.m_keys.COUNT )
lc_fields=lc_fields+","
ENDIF
NEXT
RETURN lc_fields
ENDPROC
PROCEDURE getTable()
RETURN THIS.m_table
ENDPROC
PROCEDURE getUpdatableFieldList()
LOCAL i, lc_fields
lc_fields=""
FOR i=1 TO THIS.m_pairs.COUNT
lc_fields=lc_fields+THIS.m_pairs.GETKEY(i)
IF( i+1 <= THIS.m_pairs.COUNT )
lc_fields=lc_fields+","
ENDIF
NEXT
RETURN lc_fields
ENDPROC
PROCEDURE getUpdateNameList()
LOCAL i, lc_fields
lc_fields=""
FOR i=1 TO THIS.m_pairs.COUNT
lc_fields=lc_fields+THIS.m_pairs.GETKEY(i)+" "
lc_fields=lc_fields+this.m_table+"."+this.m_pairs.getkey(i)
IF( i+1 <= THIS.m_pairs.COUNT )
lc_fields=lc_fields+","
ENDIF
NEXT
RETURN lc_fields
ENDPROC
ENDDEFINE
* 類別定義結束