How To : Creating a Class to Contain Row Data from an SQL Table

Page Status: Beta
Jump to: navigation, search

Back to Concepts and HOW TOs

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

Connecting to an SQL Database

[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

[edit] Tags

    Copyright © 2005 - 2007 Bungee Labs. All rights reserved.