Dashboard > Struts University > Home > MailReader > MailReader Database Worksheet
MailReader Database Worksheet
Added by Ted Husted, last edited by Ted Husted on Jul 31, 2006  (view change) show comment

This worksheet outlines the development of the Mailreader database access layer, including the SQL schema and data transfer object.

  • The original "DAO" layer is designed for a heirarchical database stored in local memory as an object graph.
  • This alternative layer is designed for a relational database stored in a DBMS layer (with local memory caching).
    • This layer utilizes a single data-transfer object, rather than a domain object graph, as one might use with Hibernate.
    • The DTO does not represent the database per se. The DTO is a composite of the properties utilized by database queries that are visible to the registration interface (including "hidden" properties).
    • The MailReader DTO can be used to populate or receive any data statement exposed by the application.
    • The DTO also contains UI-specific properties that are not persisted, like a confirmation password.

Dynamic Statements Specified by Screens

  • http://planetstruts.org/strut2-mailreader/


  • List of supported locales
    • locale_name, locale_code


  • Credentials for username
    • username, password


  • Account's Full Name
    • fullname


  • Account details
    • username, password, fullname, email_from, email_replyto
  • Subscription list
    • subscription_host, host_user, protocol, host_auto


  • Server Type list
    • protocol_name
  • Subscription details
    • username, subscription_host, host_user, host_pass, host_protocol, host_auto



  • locale_code
  • locale_name


  • username, password, fullname, from, replyto


  • subscription_host, host_user, host_pass, host_protocol, host_auto


  • protocol_code
  • protocol_name

Entity Relationships

  • Accounts HAVE Subscriptions
  • Subscriptions HAVE protocols

Database Statements (iBATIS)

pk_ Primary Key
fk_ Foreign Key (corresonds to the pk_ column in the linked table).

SELECT COUNT(*) FROM registration WHERE username=#username#;

INSERT INTO registration (pk_registration, fk_locale, username, password, fullname, from, replyto) VALUES (#registration_key#, #locale_key#, #username#, #password#, #fullname#, #email_from#, #email_replyto#);

SELECT username, password FROM registration WHERE username=#username#;

SELECT username, fullname FROM registration WHERE username=#username#;

SELECT pk_registration AS registration_key, fk_locale AS locale_key, username, password, fullname, from, replyto FROM registration WHERE username=#username#;

UPDATE registration SET fk_locale=#locale_key#, username=#username#, password=#password#, fullname=#fullname#, email_from=#email_from#, email_replyto=#email_replyto# WHERE pk_registration=#registration_key#;

SELECT COUNT(*) FROM subscription WHERE fk_registration=#registration_key# AND subscription_host=#subscription_host#;

INSERT INTO subscription (pk_subscription, fk_registration, fk_protocol, subscription_host, host_user, host_pass, host_auto) VALUES (#subscription_key#, #registration_key#, #protocol_key#, #username#, #subscription_host#, #host_user#, #host_pass#, #host_auto#);

SELECT pk_subscription AS subscription_key, subscription_host, host_user, host_pass, host_auto, protocol_name FROM subscription JOIN protocol ON fk_protocol=pk_protocol WHERE fk_registration=#registration_key#;

SELECT pk_subscription AS subscription_key, fk_registration AS registration_key, fk_protocol AS protocol_key, subscription_host, host_user, host_pass, host_auto FROM subscription, protocol WHERE pk_subscription=#subscription_key#;

UPDATE subscription SET fk_registration=#registration_key#, fk_protocol=#protocol_key#, subscription_host=#subscription_host#, host_user=#host_user#, host_pass=#host_pass#, host_auto=#host_auto# FROM subscription WHERE pk_subscription=#subscription_key#;

Table Create and Insert Statements

  • See attachments.

Data Transfer Object

In a JavaBean, each property would be represented by a pair of stylized "getter" and "setter" methods, such as "getLocale_key" and "setLocale_key".

public class AppData {
    private boolean nominal;
    private String locale_key;
    private String locale_code;
    private String locale_name;
    private String account_key;
    private String username;
    private String password;
    private String password2;
    private String fullname;
    private String email_from;
    private String email_replyto;
    private String protocol_key;
    private String protocol_code;
    private String protocol_name;
    private String host_key;
    private String host_name;
    private String host_user;
    private String host_pass;
    private Integer host_auto;
    * <p>Adapt internal Integer value to external boolean value. </p>
    * @return False if host_auto==0, True otherwise
    public boolean isHost_auto_checkbox() {
    Integer _host_auto = getHost_auto();
    if (_host_auto == null) _host_auto = 0;
    return (_host_auto == 0);
    * <p>Adapt internal Integer value to external boolean value. </p>
    public void setHost_auto_checkbox(boolean host_auto_checkbox) {
    if (host_auto_checkbox)
    else setHost_auto(0);
    * <p>Return true if a logic or state test passed,
    * such as whether a record already exists.</p>
    * @return True if a business logic test passed.
    public boolean isNominal() {
    return nominal;
    * <p>Record outcome of a logic or state test,
    * such as whether a record already exists.</p>
    public void setNominal(boolean nominal) {
    this.nominal = nominal;
    <!-- Standard properties.for other private fields ... -->

Common Questions

Why are the primary key columns (pk}}table) aliased as table{{key properties?_

We use the pk/fk convention to clarify the schema, however, in the normal course,
external services don't need to know if the value is used by the statement as a
primary or foreign key.

Why is only the locale name only given in one language?

The database field "locale_name" is memoranda. In practice, a internationalized application
will localize the name based on the "locale_code".

The application is internationalized and will present the localized names based on the code.

Why do some of the property names use underscores, instead of the usual camelCase?

The JavaBean specification does not exclude the use of underscores in property names.
Only that the leading letter of the property be uppercase.
Since this JavaBean is a Data Transfer Object, it's simplier to use consistent naming
conventions with the underlying database than to make arbitrary changes.

Why are the iBATIS statement IDs all UPPER CASE?

Since we refer to the statements at least twice, once in the unit tests and again in the domain code, we like to encapsulate the statement IDs as String constants. Since the convention is for constants to be upper case, we make the statement IDs upper case too (so we could copy and paste rather than edit).


  • https://www6.software.ibm.com/developerworks/education/os-ad-ibatis1/

Site running on a free Atlassian Confluence Open Source Project License granted to OSS. Evaluate Confluence today.
Powered by Atlassian Confluence, the Enterprise Wiki. (Version: 2.5.5 Build:#811 Jul 25, 2007) - Bug/feature request - Contact Administrators