DAO Medge Code Examples

Back to the Main Page

Selecting from Multiple Tables

package au.id.medge.daotester.testsuite;

import au.id.medge.dao.BaseDatabaseObject;
import au.id.medge.dao.annotations.daoproperty;
import au.id.medge.dao.annotations.daotable;
import au.id.medge.dao.query.Filter;
import au.id.medge.dao.query.PropertyFilter;
import au.id.medge.dao.query.SelectColumn;
import au.id.medge.dao.utilities.DAOException;
import au.id.medge.tutorial.Item;
import au.id.medge.tutorial.ItemLent;
import au.id.medge.tutorial.Person;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

public class MultipleTableSelectTests extends AbstractTestSuite {

    private final SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy HH:mm");

    public MultipleTableSelectTests() {
        super("Multi-Table");
    }

    private void testMultiTableBasicPK() {
        try {
            // Tables involved
            daotable tablePerson = getDAO().getTable(Person.class);
            daotable tableItem = getDAO().getTable(Item.class);

            // Properties to select
            daoproperty propertyPersonId = getDAO().getProperty(Person.class, Person.PERSONID);
            daoproperty propertyItemId = getDAO().getProperty(Item.class, Item.ITEMID);

            // Convert to SelectColumns
            SelectColumn selectPersonId = new SelectColumn(tablePerson, propertyPersonId);
            SelectColumn selectItemId = new SelectColumn(tableItem, propertyItemId);

            List, BaseDatabaseObject>> res
                    = getDAO().selectMultipleTables(new SelectColumn[]{selectPersonId, selectItemId});
            for(Map, BaseDatabaseObject> map : res) {
                StringBuilder buf = new StringBuilder();
                for (Class klass : map.keySet()) {
                    BaseDatabaseObject obj = map.get(klass);
                    buf.append(klass.getCanonicalName());
                    buf.append(" = ");
                    buf.append(obj.getPrimaryKeyAsString());
                    buf.deleteCharAt(buf.length() - 1);
                    buf.append(", ");
                }
                buf.deleteCharAt(buf.length() - 1);
                buf.deleteCharAt(buf.length() - 1);
                reportSuccess(buf.toString());
            }
            disconnect();
        } catch(DAOException | SQLException ex) {
            reportFailure("Multi table failure", ex);
        }
    }

    private void testMultiTableNoPK() {
        try {
            // Tables involved
            daotable tablePerson = getDAO().getTable(Person.class);
            daotable tableItemLent = getDAO().getTable(ItemLent.class);
            daotable tableItem = getDAO().getTable(Item.class);

            // Properties to select
            daoproperty propertyPrefName = getDAO().getProperty(Person.class, Person.PREFERREDNAME);
            daoproperty propertyReturnedDate = getDAO().getProperty(ItemLent.class, ItemLent.RETURNEDDATE);
            daoproperty propertyLentDate = getDAO().getProperty(ItemLent.class, ItemLent.LENTDATE);
            daoproperty propertyItemName = getDAO().getProperty(Item.class, Item.ITEMNAME);
            daoproperty propertyItemDescription = getDAO().getProperty(Item.class, Item.ITEMDESCRIPTION);

            // Convert to SelectColumns
            SelectColumn selectPrefName = new SelectColumn(tablePerson, propertyPrefName);
            SelectColumn selectReturnedDate = new SelectColumn(tableItemLent, propertyReturnedDate);
            SelectColumn selectLentDate = new SelectColumn(tableItemLent, propertyLentDate);
            SelectColumn selectItemName = new SelectColumn(tableItem, propertyItemName);
            SelectColumn selectItemDescription = new SelectColumn(tableItem, propertyItemDescription);

            List, BaseDatabaseObject>> res
                    = getDAO().selectMultipleTables(new SelectColumn[]{selectPrefName,
                        selectReturnedDate, selectItemName, selectLentDate,
                        selectItemDescription});

            for(Map, BaseDatabaseObject> map : res) {
                Person person = (Person)map.get(Person.class);
                ItemLent itemLent = (ItemLent)map.get(ItemLent.class);
                Item item = (Item)map.get(Item.class);

                StringBuilder buf = new StringBuilder();
                buf.append(person.getPreferredName());
                buf.append(" borrowed ");
                buf.append(item.getItemName());
                buf.append(" (");
                buf.append(item.getItemDescription());
                buf.append(") on ");
                buf.append(sdf.format(itemLent.getLentDate()));
                if (itemLent.getReturnedDate() == null) {
                    buf.append(" and still has it.");
                } else {
                    buf.append(" and returned it on ");
                    buf.append(sdf.format(itemLent.getReturnedDate()));
                    buf.append(".");
                }
                reportSuccess(buf.toString());
            }
        } catch(DAOException | SQLException ex) {
            reportFailure("Multi table failure", ex);
        }
    }

    private void restrictedSelectOneTable() {
        try {
            daotable tablePerson = getDAO().getTable(Person.class);
            daoproperty propertyPersonId = getDAO().getProperty(Person.class, Person.PERSONID);
            SelectColumn selectPersonId = new SelectColumn(tablePerson, propertyPersonId);
            List persons = getDAO().select(Person.class, selectPersonId);
            for (Person person : persons) {
                if (person.getPersonId() == null) {
                    reportFailure("Null person id");
                }
                if (person.getSurname() != null) {
                    reportFailure("Returned more than it should have.");
                }
                if (person.getPersonId() != null && person.getSurname() == null) {
                    reportSuccess("All good on " + person.getPersonId());
                }
            }
            for (Person person : persons) {
                person.select();
                if (person.getSurname() == null) {
                    reportFailure("Internal select failed.");
                }
            }
        } catch(DAOException | SQLException ex) {
            reportFailure("Restricted column select failure", ex);
        }
    }

    private static final String TEST_SURNAME = "Smith";
    private void restrictedSelectOneTableWithFilter() {
        try {
            daotable tablePerson = getDAO().getTable(Person.class);
            daoproperty propertyPersonId = getDAO().getProperty(Person.class, Person.PERSONID);
            daoproperty propertySurname = getDAO().getProperty(Person.class, Person.SURNAME);
            SelectColumn selectPersonId = new SelectColumn(tablePerson, propertyPersonId);
            PropertyFilter filter = new PropertyFilter(tablePerson, propertySurname, TEST_SURNAME);
            List persons = getDAO().select(Person.class, new SelectColumn[]{selectPersonId}, filter);
            for (Person person : persons) {
                if (person.getPersonId() == null) {
                    reportFailure("Null person id");
                }
                if (person.getSurname() != null) {
                    reportFailure("Returned more than it should have.");
                }
                if (person.getPersonId() != null && person.getSurname() == null) {
                    reportSuccess("All good on " + person.getPersonId());
                }
            }
            for (Person person : persons) {
                person.select();
                if (person.getSurname() == null
                        || !person.getSurname().equalsIgnoreCase(TEST_SURNAME)) {
                    reportFailure("Internal select failed.");
                }
            }
        } catch(DAOException | SQLException ex) {
            reportFailure("Restricted column select failure", ex);
        }
    }

    private void testMultiTableRealLifeScenario() {
        try {
            daotable tablePerson = getDAO().getTable(Person.class);
            daotable tableItemLent = getDAO().getTable(ItemLent.class);

            daoproperty propertyPersonId = getDAO().getProperty(Person.class, Person.PERSONID);
            daoproperty propertyItemId = getDAO().getProperty(ItemLent.class, ItemLent.ITEMID);
            daoproperty propertyReturnedDate = getDAO().getProperty(ItemLent.class, ItemLent.RETURNEDDATE);

            // Convert to SelectColumns
            SelectColumn selectPersonId = new SelectColumn(tablePerson, propertyPersonId);
            SelectColumn selectItemId = new SelectColumn(tableItemLent, propertyItemId);

            PropertyFilter filterReturnDate = new PropertyFilter(tableItemLent, propertyReturnedDate, Filter.OPERAND.OPERAND_EQUALS, null);

            List, BaseDatabaseObject>> res
                    = getDAO().selectMultipleTables(new SelectColumn[]{selectPersonId, selectItemId}, filterReturnDate);

            for (Map, BaseDatabaseObject> row : res) {
                Person person = (Person)row.get(Person.class);
                ItemLent itemLent = (ItemLent)row.get(ItemLent.class);
                person.select();
                Item item = itemLent.getItem();
                StringBuilder buf = new StringBuilder();
                buf.append(person.getPreferredName());
                buf.append(" has ");
                buf.append(item.getItemName());
                buf.append(" (");
                buf.append(item.getItemDescription());
                buf.append(")");
                reportSuccess(buf.toString());
            }
            disconnect();
        } catch(DAOException | SQLException ex) {
            reportFailure("Restricted column select failure", ex);
        }
    }

    @Override
    public void runTestSuite() {
        testMultiTableBasicPK();
        testMultiTableNoPK();
        restrictedSelectOneTable();
        testMultiTableRealLifeScenario();
        restrictedSelectOneTableWithFilter();
    }

}

Back to the Main Page