How To : Creating a Class to Contain Row Data from an SQL Table
Contents |
[edit] Description
This document describes how to create a class that you can use to contain data from an SQL table and how to automatically populate a collection with the table data.
[edit] Prerequisites
[edit] Procedure
To create a class into which you can map the data from a table, you need to create fields in your class that correspond to each column in the table. The fields must be named the same as the columns in the table, including capitalization. Alternatively, you can use the select [column name] as [field name] syntax to map the column name to the field name. Task Class and SQL Query below demonstrate an example of a class and SQL table designed to work together.
[edit] Task Class
The following screen shot shows the fields in the Task class. This class is designed to contain the data retrieved from the task table, created using the sql query shown below in SQL Query.
In addition, the field string priority also has an enumeration of Low, Normal, High which corresponds to ENUM('Low','Normal','High') on the task table's priority column.
And the field string status also has an enumeration of Not Started, In Progress, Completed,On Hold,Deferred which corresponds to ENUM('Not Started', 'In Progress', 'Completed','On Hold','Deferred') on the task table's status column.
The field boolean done, corresponds to ENUM('true','false') on the task table's status column. Currently, you need to use lower case for true and false in the ENUM to insure that the value is mapped to the field correctly.
Note You do not need to have a one-to-one mapping of fields to columns. Any extra fields that do not correspond to a column will be ignored and have not effect on the mapping. And any extra columns in your table that do not have a corresponding field in the class, will be skipped in the mapping process. For example, the Task class has an boolean isDirty field which is not a column in the task table so, it is ignored when the row data is mapped to an instance of Task.
The SQLConnection Runtime Type (superclass to the MySQL and Postgres classes) has a function, queryObjectResults, used to map each row in the task table to an instance of the Task class is described in Logging in to an SQL Database.
[edit] SQL Query
CREATE TABLE `task` ( `id` SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT, `subject` VARCHAR(255), `notes` VARCHAR(1024), `priority` ENUM('Low','Normal','High') NOT NULL, `status` ENUM('Not Started', 'In Progress', 'Completed','On Hold','Deferred') NOT NULL, `due_date` DATE, `category` VARCHAR(255), `done` ENUM('true','false') NOT NULL, PRIMARY KEY (`id`) )
[edit] Error Handling
[edit] Examples
[edit]
[edit] Next Steps
Logging in to an SQL Database
Adding CRUD Operations to a Class
Using SQL Query Escaping
Debugging SQL