Skip to main content

Creating Entities and DAOs

im_mirage is the database access foundation that intra-mart Accel Platform provides for the JavaEE development model. It is in substance the 2WaySQL object-relational mapping (ORM) framework Mirage-SQL, brought into intra-mart's own namespace (jp.co.intra_mart.mirage.*), with im_mirage as the module name. "IM-Mirage" is not a product name, so this documentation writes im_mirage, matching the module name.

To work with a single table, you write two things: an entity class that mirrors the shape of the table, and a DAO class that operates on that entity. If you need a more involved search, a 2WaySQL SQL file joins them.

The Class Hierarchy​

You do not write a DAO from scratch; you place it at the end of a hierarchy the platform provides.

DAO<T>                                … The base interface
   ↑
BaseDAO<T>                            … Holds protected IntramartSqlManager sqlManager
   ↑
AbstractDAO<T>                        … Common implementations of insert / update / delete / find
   ↑
OrderDAO extends AbstractDAO<OrderEntity>   … A concrete DAO that adds its own queries

Because BaseDAO holds sqlManager, there is no need to redeclare it in a concrete DAO.

A concrete DAO extends AbstractDAO<entity type> directly. If you insert an intermediate class that gathers common processing, pass the type variable straight through, as in CommonDAO<T> extends AbstractDAO<T>. This is because AbstractDAO#find resolves the entity type from the type argument of the class the DAO class directly extends. Inserting an intermediate class that fixes the type argument, or extending a concrete DAO further, leaves the type unresolvable, and the call to find results in a NullPointerException. insert, update, delete, and custom queries that use SQL files do not use this resolution, so the problem does not surface until find is called.

The Entity Class​

An entity is a class that mirrors a single row of a table as-is.

package jp.co.example.foo.infrastructure.entity;

import java.math.BigDecimal;
import java.sql.Timestamp;

import jp.co.intra_mart.mirage.annotation.Column;
import jp.co.intra_mart.mirage.annotation.PrimaryKey;
import jp.co.intra_mart.mirage.annotation.PrimaryKey.GenerationType;
import jp.co.intra_mart.mirage.annotation.Table;

/**
 * Order information entity.
 */
@Table(name = "foo_order")
public class OrderEntity {

    @PrimaryKey(generationType = GenerationType.APPLICATION)
    @Column(name = "order_id")
    public String orderId;

    @Column(name = "customer_name")
    public String customerName;

    @Column(name = "amount")
    public BigDecimal amount;

    @Column(name = "status")
    public String status;

    @Column(name = "create_user_cd")
    public String createUserCd;

    @Column(name = "create_date")
    public Timestamp createDate;

    @Column(name = "record_user_cd")
    public String recordUserCd;

    @Column(name = "record_date")
    public Timestamp recordDate;
}

There are four conventions to satisfy.

  • Use public fields: im_mirage maps values directly to public fields. If you use private fields with getters, no values are mapped.
  • Provide a no-argument constructor: The ORM creates instances through reflection. Declaring only fields is enough, since the implicit constructor covers it, but add one explicitly if you define any other constructor.
  • Apply @Table, @Column, and @PrimaryKey: Specify table and column names explicitly. They are converted automatically from the class name when unspecified, but these conventions require them to be explicit.
  • Restrict the primary key generation type to GenerationType.APPLICATION: Delegating numbering to IDENTITY or SEQUENCE on the database side creates a dependency on the database product and makes it impossible to work with an assigned ID before registration.

@PrimaryKey also defines IDENTITY and SEQUENCE, but they are not used under these conventions. For a composite primary key, apply @PrimaryKey to each of the fields that make up the key.

Matching Class Names to Table Names​

The basic form of a class name is the table name (snake case) converted to Pascal case. order_status_history becomes OrderStatusHistoryEntity. This is not a mandatory rule, though; it is a goal to keep in mind as you implement.

Following the correspondence mechanically can pull module prefixes and abbreviations straight into the class name, which can hurt readability instead. In that case, favor readability and translate the name freely.

  • foo_order → OrderEntity: foo_ is the placeholder company prefix shared across the samples, not a word that expresses the domain, so it is dropped. Module prefixes in a real project, such as b_m_ or b_t_, can be treated the same way.
  • b_m_account_b → AccountBasicInfoEntity: A straightforward conversion is unreadable, so it is replaced with an English name that carries the meaning.

Even when you drop a prefix or translate an abbreviation, write the real table name in @Table(name = "...") and record it in the class JavaDoc as well. That way the path from the table to the class remains traceable even when the names diverge.

Supported Column Types​

Choose the Java type to match the type you defined in your DDL.

DB typeJava typeNotes
VARCHAR / NVARCHARString
TIMESTAMP / DATETIMEjava.sql.Timestamp
INTEGER / INTint or longChoose according to the range of values. Use long for IDs and large values
DECIMAL / NUMERICjava.math.BigDecimalDo not use double or float
A CHAR(1) flagStringHold the database value as-is, such as "0" or "1"

Four choices tend to cause confusion.

  • Use only java.sql.Timestamp for dates and times. im_mirage cannot map java.util.Date or java.time.LocalDateTime.
  • Use java.math.BigDecimal for amounts and decimals. double and float introduce rounding errors, so they cannot be used for money or precise calculations.
  • Do not map flags to boolean. Hold them as String to match the value of the database column, and interpret them as true or false on the domain model side.
  • Receive enumerated values as String as well. Converting to a Java enum is the job of the domain model layer.

Even for a column with a NOT NULL constraint, the public field of the entity is nullable, and the validity of values is guaranteed by the DAO layer and by database constraints.

Audit Fields Set Automatically​

The four columns that record who created and who updated a row, along with the respective timestamps, must be included in every entity.

@Column(name = "create_user_cd")
public String createUserCd;

@Column(name = "create_date")
public Timestamp createDate;

@Column(name = "record_user_cd")
public String recordUserCd;

@Column(name = "record_date")
public Timestamp recordDate;

AbstractDAO#insert calls EntityHelper.setCreateFields internally and AbstractDAO#update calls EntityHelper.setRecordFields, setting values on the audit fields automatically.

The two behave differently.

  • setCreateFields covers all four audit fields and sets a value only on those that are null. If a value is already present, it is not overwritten.
  • setRecordFields covers only recordUserCd and recordDate, and always sets them. It does not fill in createUserCd or createDate.

The audit fields are not just a requirement of the conventions; the implementation cannot do without them either. Passing an entity that fails to declare even one of the four makes insert and update throw a NullPointerException. insert needs all four, and update needs the two record-side fields.

Setting these by hand on the calling side can leave unintended values in the registered row, or duplicate what AbstractDAO sets.

// Avoid this
order.createUserCd = "system";
order.createDate = new Timestamp(System.currentTimeMillis());
dao.insert(order);

Keep the entity side to declarations only, and leave setting the values to the DAO.

The DAO Class​

If you do not need your own queries, extending AbstractDAO is all it takes.

package jp.co.example.foo.infrastructure.dao;

import jp.co.intra_mart.mirage.ext.dao.AbstractDAO;
import jp.co.example.foo.infrastructure.entity.OrderEntity;

/**
 * DAO that operates on foo_order.
 */
public class OrderDAO extends AbstractDAO<OrderEntity> {
}

That alone makes the following methods available.

MethodBehavior
insert(T entity)Registers one row. Sets automatically those audit fields whose value is null
insertBatch(T... entities)Registers multiple rows
update(T entity)Updates one row. Updates every column except the primary key, and sets the record-side audit fields automatically
updateBatch(T... entities)Updates multiple rows
delete(T entity)Deletes one row
deleteBatch(T... entities)Deletes multiple rows
find(Object... ids)Retrieves one row by primary key. Returns null if there is no match

Pass the primary key values as the arguments to find. For a composite primary key, list the values in the order they are declared in the entity.

Base Updates on the Existing Entity​

update is not a partial update. It lists every column except the primary key in the SET clause, so any field left unset is overwritten with null. In addition, update fills in only the two record-side fields, not the two create-side fields. Passing an entity newly built from the domain model erases the creator and creation timestamp recorded at registration.

final OrderEntity existing = dao.find(orderId);
if (existing != null) {
    existing.status = newStatus;
    dao.update(existing);
}

Apply only the changes to the existing entity obtained with find, then pass it to update. If you want to update only some of the columns, prepare an SQL file with an UPDATE statement and run it with sqlManager.executeUpdate. executeUpdate does not set the audit fields automatically, so include the updater and update timestamp in the SET clause as well.

Obtaining a DAO Instance​

Do not use new for a DAO; obtain it from DAOFactory.

final OrderDAO dao = DAOFactory.getTenantDatabaseDAO(OrderDAO.class);
dao.insert(order);

To target the shared database, obtain it with a connection ID.

final OrderDAO dao = DAOFactory.getSharedDatabaseDAO(OrderDAO.class, connectId);

Creating one directly with new OrderDAO() leaves the sqlManager field of BaseDAO unset. That field is injected by DAOFactory through reflection, and without the injection the first call results in a NullPointerException.

The instance you obtain is cached in a thread local and released automatically when the session is released. There is no need to manage caching or releasing on the calling side.

Custom Queries and Search Criteria​

Searches that basic CRUD cannot express are implemented with an SQL file and a sqlManager call.

package jp.co.example.foo.infrastructure.dao;

import java.util.List;

import jp.co.intra_mart.mirage.ext.dao.AbstractDAO;
import jp.co.example.foo.infrastructure.entity.OrderEntity;

/**
 * DAO that operates on foo_order.
 */
public class OrderDAO extends AbstractDAO<OrderEntity> {

    /** SQL file path (relative to the classpath, with no leading slash) */
    private static final String SQL_PATH = "META-INF/sql/jp/co/example/foo/infrastructure/dao/OrderDAO/";

    /**
     * Retrieves a list of orders for the specified status.
     * @param status the status to search for (all rows when null)
     * @return the list of orders
     */
    public List<OrderEntity> findByStatus(final String status) {
        final OrderEntity criteria = new OrderEntity();
        criteria.status = status;
        return super.sqlManager.getResultList(OrderEntity.class, SQL_PATH.concat("find-by-status.sql"), criteria);
    }
}

Here the entity plays two roles. The OrderEntity.class passed as the first argument of getResultList is the type that receives the result, while the criteria passed as the third argument is the container that carries the search conditions. As long as the placeholder name in the SQL file (/*status*/) matches a property name on the object you passed, the value is bound.

Entities are not the only thing you can use for search criteria.

  • An entity: when the conditions correspond to the columns of the table. The example above is one of these.
  • Any JavaBean: when you want conditions that do not exist as columns, such as the start and end of a period, or the offset for paging.
  • Map<String, Object>: when the conditions are dynamic and a dedicated class would be overkill. Key names become the placeholder names directly.

Reusing an entity for conditions means the audit fields come along in the condition object as well. If the number of conditions grows, or conditions that do not correspond to columns are mixed in, preparing a dedicated JavaBean is one option.

Choosing Between the SqlManager Methods​

SqlManager has two families of methods with very similar names. The meaning of the argument that specifies the SQL differs, so mixing them up is hard to notice until run time.

FamilyMethodsArgument that specifies the SQLHow parameters are passed
SQL filegetResultList / getSingleResult / getCount / executeUpdate / iterateThe path of a 2WaySQL fileAn entity, a JavaBean, or a Map
SQL stringgetResultListBySql / getSingleResultBySql / executeUpdateBySql / iterateBySqlThe SQL statement itselfVarargs (? placeholders)
// In the SQL file family, the SQL is specified as a file path
sqlManager.getResultList(OrderEntity.class, "META-INF/sql/jp/co/example/foo/infrastructure/dao/OrderDAO/find-by-status.sql", criteria);

// To pass an SQL statement directly, use the BySql family
sqlManager.getResultListBySql(OrderEntity.class, "SELECT * FROM foo_order WHERE status = ?", status);

The xxxBySql family cannot use 2WaySQL comment syntax. If you want to reshape the SQL depending on conditions, choose the SQL file family.

Beyond these, SqlManager also provides per-entity CRUD (insertEntity, updateEntity, and so on) and stored procedure calls (call and callForList). The entity CRUD methods are what the AbstractDAO methods call internally, so ordinarily using AbstractDAO is enough.

Return Values of Methods That Return One Row​

getSingleResult and find do not guarantee that the result is unique.

SituationBehavior
0 rowsReturns null without throwing an exception
2 or more rowsReturns the first row without throwing an exception (the rest are discarded)

Always check the return value for null. For a search that must be unique, guarantee it with a primary key or unique constraint, or retrieve with getResultList and check the number of rows. When a search without ORDER BY matches multiple rows, which row is returned depends on the order in which the database returns them.

Getting a Row Count​

getCount wraps the SQL it is given, whole, in a SELECT COUNT(*) FROM (...) subquery and runs it. So the SQL file you pass should contain a SELECT of the same shape you would use to retrieve the list.

  • Do not write SELECT COUNT(*): it ends up counting the single row that holds the count, and always returns 1 without raising an exception.
  • Do not write ORDER BY: it ends up inside the subquery, which is a syntax error on SQLServer.

Where SQL Files Belong​

Place SQL files under src/main/resources/META-INF/sql: create the same package path as the DAO class, and put them in a directory named after the DAO class under it.

src/main/java/jp/co/example/foo/infrastructure/dao/OrderDAO.java
src/main/resources/META-INF/sql/jp/co/example/foo/infrastructure/dao/OrderDAO/find-by-status.sql

Name files in kebab case, matching the DAO method that uses the file (find-by-status.sql if used from findByStatus). If the syntax differs by database product, add files with a dialect suffix at the end of the file name (2WaySQL).

The DAO's SQL_PATH is relative to the classpath: it starts from META-INF/sql/ with no leading slash, and ends with the directory named after the DAO class. If the path does not match where the files are placed, it fails with resource: xxx.sql is not found.

Placing them under src/main/java leaves them out of the runtime classpath after the build, and they fail with resource: xxx.sql is not found. In the source tree of the platform's standard features, .java and .sql appear side by side in the same directory, but that is the repository layout before the build, and it is separate from where they belong under the standard Maven layout.