Apache Empire-db

<h2 id="major-benefits">Major benefits</h2>
<p>Empire-db's key strength is its API for dynamic SQL generation for arbitrary select, update, insert or delete statements, purely by using Java methods which reference the model objects. This provides type-safety and almost entirely eliminates the use of string literals for names or expressions in code. Additionally DBMS independence is achieved through a pluggable driver model.
</p><p>Using references to table and column objects significantly improves compile-time safety and thus reduces the amount of testing. As a positive side effect the IDE's code completion can be used to browse the data model, increases productivity and eliminates the need for other external tools or IDE-plugins.
</p><p>Further the object model also provides safe and easy access to meta-information of the data model such as field data type, maximum field length, whether a field is mandatory and a finite choice of options for a field's values. Metadata is user-extensible and not limited to DBMS related metadata. Availability of meta-information encourages more generic code and eliminates redundancies throughout application layers.
</p>
<h2 id="features-at-a-glance">Features at a glance</h2>
<ul><li>Data model definition through a Java object model omits the need to learn XML schemas or annotations and easily allows user interceptions and extensions.</li>
<li>Portable RDBMS independent record handling and command definition with support for a variety of relational databases such as <a href="/facts/Oracle_Database/FHv5g732">Oracle</a>, <a href="/facts/Microsoft_SQL_Server/eCdt2JBL">Microsoft SQL Server</a>, <a href="/facts/MySQL/QEEDNBDP">MySQL</a>, <a href="/facts/Apache_Derby/vSE2vVtG">Derby</a>, <a href="/facts/H2_(DBMS)/Nst23HsP">H2</a> and <a href="/facts/HSQLDB/IpE7WkgR">HSQLDB</a> (as of version 2.0.5)</li>
<li>DDL generation for target DBMS from object definition, either for the entire database or for individual objects such as tables, views, columns and relations.</li>
<li>Type-safe API for dynamic SQL command generation allows dynamic building of SQL statements using API methods and object references only instead of string literals. This provides a high degree of type-safety which simplifies testing and maintenance.</li>
<li>Reduced amount of Java code and powerful interception of field and metadata access through dynamic beans as an alternative to POJOs. This even allows data model changes (DDL) at runtime.</li>
<li>Automatic tracking of record state and field modification (aka "dirty checking") to only insert/ update modified fields.</li>
<li>Support for optimistic locking through timestamp column.</li>
<li>No need to always work with full database entities. Build queries to provide the data exactly as needed, and obtain the result for example as a list of any type of POJO with matching property setters or constructor.</li>
<li>Lightweight and passive library with zero configuration footprint that allows simple integration with any architecture or framework.</li></ul>
<h2 id="example">Example</h2>
<p>As an example consider a database with two tables called <i>Employees</i> and <i>Departments</i> for which a list of employees in a particular format, with certain constraints and a given order should be retrieved.
</p><p>The corresponding Oracle syntax SQL statement is assumed to be as follows:
</p>
    SELECT t1.EMPLOYEE_ID, 
           t1.LASTNAME || ', ' || t1.FIRSTNAME AS NAME, 
           t2.DEPARTMENT
    FROM (EMPLOYEES t1 
          INNER JOIN DEPARTMENTS t2 ON t1.DEPARTMENT_ID = t2.DEPARTMENT_ID)
    WHERE upper(t1.LASTNAME) LIKE upper('Foo%') 
      AND t1.RETIRED=0
    ORDER BY t1.LASTNAME, t1.FIRSTNAME

<p>This SQL statement can be created using Empire-db's command API using object model references like this:
</p>
    SampleDB db = getDatabase();
    // Declare shortcuts (not necessary but convenient)
    SampleDB.Employees EMP = db.EMPLOYEES;
    SampleDB.Departments DEP = db.DEPARTMENTS;
    // Create a command object
    DBCommand cmd = db.createCommand();
    // Select columns
    cmd.select(EMP.EMPLOYEE_ID);
    cmd.select(EMP.LASTNAME.append(", ").append(EMP.FIRSTNAME).as("NAME"));
    cmd.select(DEP.DEPARTMENT);
    // Join tables
    cmd.join  (EMP.DEPARTMENT_ID, DEP.DEPARTMENT_ID);
    // Set constraints
    cmd.where(EMP.LASTNAME.likeUpper("Foo%"));
    cmd.where(EMP.RETIRED.is(false));
    // Set order
    cmd.orderBy(EMP.LASTNAME);
    cmd.orderBy(EMP.FIRSTNAME);

<p>In order to execute the query and retrieve a list of POJO's holding the query result the following code may be used:
</p>
    // Class definition for target objects
    public class EmployeeInfo {
        private int employeeId;
        private String name;
        private String department;
        // Getter's and Setters for all properties
        // or a public Constructor using fields
        public get...
        public set...
    }

// Retrieve employee list using the cmd object created above
    DBReader reader = new DBReader();
    try {
        reader.open(cmd, getConnection());
        List<EmployeeInfo> empList = reader.getBeanList(EmployeeInfo.class);
    } finally {
        reader.close()
    }

<p>Empire-db also supports field access through object references or obtaining query results as XML.
</p>
<h2 id="history">History</h2>
<p>Empire-db was originally developed at ESTEAM Software a German software development company which used Empire-db to develop various applications for a variety of different branches.
</p><p>In January 2008 Empire-db was made officially open source and first published though SourceForge.net.
</p><p>In June 2008 a proposal was submitted to the Apache Software Foundation for Empire-db to become an Apache Incubator project. In July 2008 Empire-db got accepted for incubation and all rights over the Software were transferred to the Apache Foundation.
</p><p>In October 2008 Empire-db 2.0.4 was the first official Apache incubator release with all package names changed to begin with org.apache.empire.
</p>
<h2 id="see-also">See also</h2>
<ul>
<li>Computer programming portal</li></ul>
<ul><li><a href="/facts/Java_Database_Connectivity/uUVYg2sB">Java Database Connectivity</a> (JDBC)</li>
<li><a href="/facts/Object-relational_mapping/sjIPEIfq">Object-relational mapping</a></li>
<li><a href="/facts/Hibernate_(Java)/7hHcgTkB">Hibernate</a></li>
<li><a href="/facts/IBATIS/H4mB2Yee">iBATIS</a></li>
<li><a href="/facts/TopLink/ESDCyJsj">TopLink</a></li>
<li><a href="/facts/Apache_Struts/n6IaPbD7">Apache Struts</a></li></ul>

<h2 id="external-links">External links</h2>
<ul><li><a href="https://empire-db.apache.org/">Official website</a></li></ul>

<h2 id="references">References</h2>

<ol>
<li id="fn:1"><p>"Apache Empire-db". empire-db.apache.org. Retrieved 2022-03-21. <a href="https://empire-db.apache.org/pages/home.html" target="_blank">https://empire-db.apache.org/pages/home.html</a> <a href="#fnref:1" class="footnote-back-ref">↩</a></p></li>
</ol>

Apache Empire-db open-in-new

Apache Empire-db