Exploring the SQLMap PHP DataMapper API through the TSqlMapper

The TSqlMapper instance acts as a facade to provide access the rest of the DataMapper framework. The DataMapper API methods are shown below.

/* Query API */ public function queryForObject($statementName, $parameter=null, $result=null); public function queryForList($statementName, $parameter=null, $result=null, $skip=-1, $max=-1); public function queryForPagedList($statementName, $parameter=null, $pageSize=10, $page=0); public function queryForMap($statementName, $parameter=null, $keyProperty=null, $valueProperty=null); public function insert($statementName, $parameter=null) public function update($statementName, $parameter=null) public function delete($statementName, $parameter=null) /* Connection API */ public function openConnection() public function closeConnection() /* Transaction API */ public function beginTransaction() public function commitTransaction() public function rollBackTransaction()

Note that each of the API methods accept the name of the Mapped Statement as the first parameter. The statementName parameter corresponds to the id of the Mapped Statement in the Data Map definition. In each case, a parameterObject also may be passed. The following sections describe how the API methods work.

Insert, Update, Delete

public function insert($statementName, $parameter=null) public function update($statementName, $parameter=null) public function delete($statementName, $parameter=null)

If a Mapped Statement uses one of the <insert>, <update>, or <delete> statement-types, then it should use the corresponding API method. The <insert> element supports a nested <selectKey> element for generating primary keys. If the <selectKey> stanza is used, then insert returns the generated key; otherwise a null object is returned. Both the update and delete methods return the number of rows affected by the statement.

QueryForObject

public function queryForObject($statementName, $parameter=null, $result=null);

If a Mapped Statement is expected to select a single row, then call it using queryForObject. Since the Mapped Statement definition specifies the result class expected, the framework can both create and populate the result class for you. Alternatively, if you need to manage the result object yourself, say because it is being populated by more than one statement, you can use the alternate form and pass your $resultObject as the third parameter.

QueryForList

public function queryForList($statementName, $parameter=null, $result=null, $skip=-1, $max=-1);

If a Mapped Statement is expected to select multiple rows, then call it using queryForList. Each entry in the list will be an result object populated from the corresponding row of the query result. If you need to manage the $resultObject yourself, then it can be passed as the third parameter. If you need to obtain a partial result, the fourth parameter $skip and fifth parameter $max allow you to skip a number of records (the starting point) and the maximum number to return.

QueryForPagedList

public function queryForPagedList($statementName, $parameter=null, $pageSize=10, $page);

We live in an age of information overflow. A database query often returns more hits than users want to see at once, and our requirements may say that we need to offer a long list of results a "page" at a time. If the query returns 1000 hits, we might need to present the hits to the user in sets of fifty, and let them move back and forth between the sets. Since this is such a common requirement, the framework provides a convenience method.

The TSqlMapPagedList interface includes methods for navigating through pages (nextPage(), previousPage(), gotoPage($pageIndex)) and also checking the status of the page (getIsFirstPage(), getIsMiddlePage(), getIsLastPage(), getIsNextPageAvailable(), getIsPreviousPageAvailable(), getCurrentPageIndex(), getPageSize()). The total number of records available is not accessible from the TSqlMapPagedList interface, unless a virtual count is defined using setVirtualCount($value), this should be easily accomplished by simply executing a second statement that counts the expected results.

Tip: The queryForPagedList method is convenient, but note that a larger set (up to 3 times the page size) will first be returned by the database provider and the smaller set extracted by the framework. The higher the page size, the larger set that will be returned and thrown away. For very large sets, you may want to use a stored procedure or your own query that uses $skip and $max as parameters in queryForList.
Tip: The $page parameter was introduced in 3.1.3. Before there was an additional query to always fetch the data for page 0 on object creation. Since this might be a problem in performance critical situations with 3.1.2, you might be better of also using queryForList with $skip and $max instead.

QueryForMap

public function queryForMap($statementName, $parameter=null, $keyProperty=null, $valueProperty=null);

The queryForList methods return the result objects within a TList or array instance. Alternatively, the queryForMap returns a TMap or associative array instance. The value of each entry is one of the result objects. The key to each entry is indicated by the $keyProperty parameter. This is the name of the one of the properties of the result object, the value of which is used as the key for each entry. For example, If you needed a set of Employee objects, you might want them returned as a TMap keyed by each object's EmployeeNumber property.

If you don't need the entire result object in your result, you can add the $valueProperty parameter to indicate which result object property should be the value of an entry. For example, you might just want the EmployeeName keyed by EmployeeNumber.

Transaction

The DataMapper API includes methods to demarcate transactional boundaries. A transaction can be started, committed and/or rolled back. You can call the transaction methods from the TSqlMapper instance.

// Begin a transactional session using Adodb transaction API public function beginTransaction() // Commit a transaction, uses Adodb transaction API public function commitTransaction() // RollBack a transaction, uses Adodb transaction API public void RollBackTransaction()

Using transactions example.

try { $sqlMap->beginTransaction(); $item = $sqlMap->queryForObject("getItem", $itemId); $item->setDescription($newDescription); $sqlMap->update("updateItem", $item); $sqlMap->commitTransaction(); } catch { $sqlMap->rollBackTransaction(); }