Introduction

What's Mirage-SQL?

Mirage-SQL is an easy and powerful SQL centric database access library.

There are many Java based ORMs already, but they can't handle complex queries very well. In real-world business applications there's still the requirement to handle native SQLs. Some ORMs do support native SQLs, but most of them can't handle dynamic queries, (MyBatis being one of the few exceptions). Developers still need to assemble dynamic queries using error prone String operations, and due to the PreparedStatement shortcomings, the "indexed parameter hell" still remains an unsolved problem.

Mirage-SQL supports dynamic queries with named parameters (not indexed), all this from external SQL files. Parameters and the dynamic parts are embedded into SQL comments. So basically, an externalized Mirage SQL file is a dynamic SQL template, but it's also a plain SQL, executable by any SQL client tool. This feature: the "dynamic SQL template" of Mirage-SQL is called 2WaySQL, and it solves all the issues mentioned above.

Usually, update operations are simpler to develop than data selection, but applications that make use of allot of such updates are quite tedious to develop. Mirage-SQL also provides an easy way to insert / update / delete records using entity classes (without writing SQLs).

Quick Start

The Configuration page details how to setup Mirage-SQL.

First example of Mirage-SQL

A basic example of searching using a SQL file. SqlManager is the main interface of Mirage.-SQL You can get it from the Session. The Session is also responsible for transaction handling.

Session session = SessionFactory.getSession();
SqlManager sqlManager = session.getSqlManager();
session.begin();

try {
  BookParam param = new BookParam();

  List<Book> result = sqlManager.getResultList(
      Book.class, "/META-INF/selectBooks.sql", param);

  // ...

  session.commit();

} catch(Exception ex) {
  session.rollback();

} finally {
  session.release();
}

Here the Book is a POJO to map each row of the ResultSet:

public class Book {
  public Long bookId;
  public String bookName;
  public String author;
  public Integer price;
}

The BookParam is a parameter class:

public class BookParam {
  public String author;
  public Integer minPrice;
  public Integer maxPrice;
}

but you can also use a HashMap with the required parameters as keys.

The external /META-INF/selectProducts.sql file:

SELECT * FROM BOOK
/*BEGIN*/
  WHERE
  /*IF author != null */
        AUTHOR = /*author*/'Naoki Takezoe'
  /*END*/
  /*IF minPrice != null */
    AND PRICE >= /*minPrice*/20
  /*END*/
  /*IF maxPrice != null */
    AND PRICE <= /*maxPrice*/100
  /*END*/
/*END*/
ORDER BY BOOK_ID ASC

2WaySQL

Mirage supports 2WaySQL. This is inspired by the Japanese O/R mapping framework S2JDBC. 2WaySQL is the plain old SQL template. You can specify parameters and conditions using SQL comments. So these SQLs are executable using any SQL client tool.

For example, the following SQL statement has the SQL comment(/*author*/), but it's also executable in any SQL tool since it has a default value: ('Naoki Takezoe').

SELECT * FROM BOOK
WHERE AUTHOR = /*author*/'Naoki Takezoe'
ORDER BY BOOK_ID ASC

At runtime, Mirage-SQL replaces /*author*/ with the placeholder ? and removes the default value of ('Naoki Takezoe'), so Mirage-SQL would execute the following SQL:

SELECT * FROM BOOK
WHERE AUTHOR = ?
ORDER BY BOOK_ID ASC

and would bind the required parameter.

See 2WaySQL to know details about 2WaySQL.

SQL less Update

Mirage-SQL can insert / update / delete using entity objects (without using SQL).

The Entity Class must have a primary key field with annotated with @PrimaryKey:

public class Book {
  @PrimaryKey(generationType=GenerationType.IDENTITY)
  public Long bookId;
  public String bookName;
  public String author;
  public Integer price;
}

You should specify the generation type of primary keys. Mirage-SQL supports the following generation types:

• GenerationType.APPLICATION

  Primary keys have to be set by the application before invocation of SqlManager#insertEntity().

@PrimaryKey(generationType=GenerationType.APPLICATION)
public Long id;

• GenerationType.IDENTITY

  Primary keys are set by database automatically. Database have to support PreparedStatement.RETURN_GENERATED_KEYS to acquire generated keys. Oracle does not support this feature.

@PrimaryKey(generationType=GenerationType.IDENTITY)
public Long id;

• GenerationType.SEQUENCE

  Primary keys are set by Mirage-SQL using sequence. In this strategy, you have to specify the sequence name at generator attribute of @PrimaryKey annotation.

@PrimaryKey(generationType=GenerationType.SEQUENCE, generator="USER_ID_SEQ")
public Long id;

Example to insert an entity using SqlManager#insertEntity():

Book book = new Book();
book.bookName = "Mirage in Action";
book.author = "Naoki Takezoe";
book.price = 20;

sqlManager.insertEntity(entity);

You can also select an entity using SqlManager#findEntity().

Book book = sqlManager.findEntity(Book.class, bookId);

Please note that Mirage-SQL does not support relationships between entities because it would make the framework too complex.

Iteration Search

SqlManager#getResultList() will instantiate all entities into a list, but for large large result sets it might causes OutOfMemoryError.

In these case, you should use SqlManager#iterate() instead of SqlManager#getResultList().

Integer result = sqlManager.iterate(
    Book.class,
    new IterationCallback<Book, Integer>() {

      private int result;

      @Override public Integer iterate(Book entity) {
        result = result + entity.price;
        return result;
      }

    },
    SQL_PREFIX + "SqlManagerImplTest_selectBooks.sql");

SqlManager#iterate() accepts IterationCallback and invoke it per record. The return value of SqlManager#iterate() is a last returned value from IterationCallback.

If you want to break iteration search on the way, throw BreakIterationException from IterationCallback. By this exception, iteration search is stopped and SqlManager#iterate() returns the previous returned value from IterationCallback. If BreakIterationException is thrown at first IterationCallback#iterate() invocation, SqlManager#iterate() returns null.

Stored Procedure / Function

Mirage-SQL can call stored procedures / functions in a database independent manner.

• SqlManager#call()

  Calls the procedure or the function which returns the single result.

• SqlManager#callForList()

  Calls the function which returns a result list.

// Parameter class
public class GetCountParam {
  @In
  public long deptId;
}

// Creates a parameter object to be passed to the stored procedure / function
GetCountParam param = new GetCountParam();
param.deptId = 1;

// Calls a stored procedure / function and gets a result value.
Long count = sqlManager.call(Long.class, "get_emp_count", param);

Fields of the parameter class have annotations such as @In, @Out, @InOut and @ResultSet. These annotations indicate the parameter type.

• The field has no annotation or it has @In annotation, it means the IN parameter.
• The field has @Out annotation, it means the OUT parameter.
• The field has @InOut annotation, it means the INOUT parameter.
• The field has @ResultSet annotation, it's mapped to the result set which is not returned as a parameter. In the database which can return a result set as a parameter only such as Oracle or PostgreSQL, @ResultSet field means the OUT parameter.