Mapping SQL Database Tables To Java Objects
by Michael J. Yuan

Listing One
# schema.sql
# NOTE: The SQL type "DATETIME" corresponds to Java type "java.sql.Timestamp";
# "BIGINT(20)" corresponds to "Long"; 
# "TEXT" and "MEDIUMTEXT" correspond to "String".

CREATE TABLE Article (
  Article.ArticleID BIGINT(20) PRIMARY KEY,
  Article.Title TEXT,
  Article.Authors TEXT,
  Article.Abstract TEXT,
  Article.Text MEDIUMTEXT,
  Article.PostDate DATETIME
);
CREATE TABLE Category (
  Category.CategoryID BIGINT(20) PRIMARY KEY,
  Category.Name TEXT,
  Category.ParentID BIGINT(20)
);
CREATE TABLE ArticleCategory (
  ArticleCategory.ArticleCategoryID BIGINT(20) PRIMARY KEY,
  ArticleCategory.ArticleID BIGINT(20) NOT NULL,
  ArticleCategory.CategoryID BIGINT(20) NOT NULL
);


Listing Two  
#TableRow.java
package TableObjects;

import java.util.*;
import java.sql.*;

public interface TableRow {

  // "getValue/setValue" retrieve/update values
  // in the current row object.
  Object getValue (String tableFieldieldName) throws Exception;
  void setValue (String tableFieldName, Object tableFieldValue) 
                            throws Exception;
  // commit changes into backend database.
  void commit () throws Exception;

  String getTableName ();
  String [] getFieldNames (); 
  String getPrimaryKeyName ();
  Object getPrimaryKeyValue ();

}

Listing Three
// initialize a Factory instance 
Table ArticleFactory = new Article();

// Get a row using a factory method.
// Find the row that has a primary key value of 0.
TableRow articleRow = ArticleFactory.findByPrimaryKey( (new Long(0) );

// Retrieve a field value from the row.
String subject = (String) articleRow.getValue("Subject");

// Change a field value in the Row.
articleRow.setValue("Subject", "First Post!");

// Commit the change to the backend database
articleRow.commit();





1

