You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@empire-db.apache.org by do...@apache.org on 2022/03/14 20:41:27 UTC
svn commit: r1898936 [14/14] - in /empire-db/site: ./ build/ build/css/ build/js/ community/ css/ documentation/ downloads/ empiredb/ images/ javadocs/ js/ legacy/ legacy/community/ legacy/css/ legacy/documentation/ legacy/downloads/ legacy/empiredb/ l...
Added: empire-db/site/pages/project.html
URL: http://svn.apache.org/viewvc/empire-db/site/pages/project.html?rev=1898936&view=auto
==============================================================================
--- empire-db/site/pages/project.html (added)
+++ empire-db/site/pages/project.html Mon Mar 14 20:41:26 2022
@@ -0,0 +1,817 @@
+<!DOCTYPE html>
+<html xmlns="http://www.w3.org/1999/xhtml">
+
+<!-- #BeginTemplate "../site.dwt" -->
+
+<head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width, initial-scale=1">
+<meta http-equiv="X-UA-Compatible" content="IE=edge">
+<!-- additinal tags -->
+<meta name="description" content="Apache Empire-db - full SQL-freedom for Java">
+<meta name="keywords" content="java, jdbc, database, sql, ddl, dbms, rdbms, metadata, entity, persistence, query, jpa, orm, or-mapping, annotations, hibernate, jdo" >
+<meta name="robots" content="index, follow" >
+<meta name="revisit-after" content="7 days" >
+<meta http-equiv="imagetoolbar" content="no">
+<!-- icons -->
+<link rel="shortcut icon" href="../empire-db.ico">
+<!-- css -->
+<link rel="preconnect" href="https://fonts.googleapis.com">
+<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Montserrat:400,600">
+<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/github-fork-ribbon-css/0.2.3/gh-fork-ribbon.min.css" />
+<link rel="stylesheet" href="../css/layout.css" type="text/css" />
+<!-- JavaScript -->
+<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
+<!-- app-specific -->
+<script type="text/javascript" src="../js/site.js"></script>
+<!-- #BeginEditable "head" -->
+<style type="text/css">
+</style>
+<title>Apache Empire-db</title>
+<!-- #EndEditable -->
+</head>
+<body>
+<div id="bodywrapper">
+<a class="github-fork-ribbon" target="_blank" href="https://github.com/apache/empire-db" data-ribbon="Fork me on GitHub" title="Fork me on GitHub"></a>
+<nav id="navigation">
+<!-- Main Nav -->
+<div class="navbar navbar-dark navbar-fixed-top navbar-main-style" id="navbar-main">
+ <div class="container">
+ <!-- brand and toggle -->
+ <div class="navbar-header">
+ <button type="button" class="navbar-toggle" data-toggle="collapse" data-target="#nav-main" aria-expanded="false" aria-controls="navbar">
+ <span class="icon-bar"></span>
+ <span class="icon-bar"></span>
+ <span class="icon-bar"></span>
+ </button>
+ <a class="navbar-link logo-empire homeLink" href="home.html">
+ <img alt="Empire-db" src="../res/empire-db-lg.png" />
+ </a>
+ </div>
+ <!-- Main Nav -->
+ <div class="navbar-collapse collapse" id="nav-main">
+ <div class="navbar-nav-wrapper">
+ <!-- row 1 -->
+ <ul class="nav navbar-nav">
+ <li id="mi.home">
+ <a class="homeLink" data-target="mi.home" href="home.html"><span class="first-letter">H</span>OME</a>
+ <div class="nav-indicator"><span></span></div>
+ </li>
+ <li id="mi.project">
+ <a class="pageLink" data-target="mi.project" href="project.html"><span class="first-letter">W</span>HY?</a>
+ <div class="nav-indicator"><span></span></div>
+ </li>
+ <li id="mi.download" class="highlight">
+ <div class="nav-highlight"><span></span></div>
+ <a class="pageLink" data-target="mi.download" href="download.html"><span class="first-letter">G</span>ET IT <span class="first-letter">N</span>OW</a>
+ <div class="nav-indicator"><span></span></div>
+ </li>
+ <li id="mi.community">
+ <a class="pageLink" data-target="mi.community" href="community.html"><span class="first-letter">C</span>OMMUNITY</a>
+ <div class="nav-indicator"><span></span></div>
+ </li>
+ </ul>
+ </div>
+ <div class="navbar-nav-right">
+ <a class="navbar-link logo-apache" href="https://apache.org" target="_blank">
+ <img alt="Apache.org" src="../res/apache-small.png" />
+ </a>
+ </div>
+ </div>
+ </div>
+</div>
+<!-- Sub Nav -->
+<div class="navbar navbar-light navbar-fixed-top" id="navbar-sub">
+ <div class="container">
+ <div class="navbar-header">
+ <button type="button" class="navbar-toggle" data-toggle="collapse" data-target="#nav-sub" aria-expanded="false" aria-controls="navbar">
+ <span class="icon-bar"></span>
+ <span class="icon-bar"></span>
+ <span class="icon-bar"></span>
+ </button>
+ </div>
+ <div class="navbar-collapse collapse" id="nav-sub">
+ <ul class="nav navbar-nav">
+ <!-- #BeginEditable "subnav" -->
+ <li id="mi.sec1">
+ <a class="sectionLink" href="#sec1">Who's the shark?</a>
+ <div class="nav-indicator"><span></span></div>
+ </li>
+ <li id="mi.sec2">
+ <a class="sectionLink" href="#sec2">Can your ORM do that?</a>
+ <div class="nav-indicator"><span></span></div>
+ </li>
+ <li id="mi.sec3">
+ <a class="sectionLink" href="#sec3">Going beyond data</a>
+ <div class="nav-indicator"><span></span></div>
+ </li>
+ <li id="mi.sec4">
+ <a class="sectionLink" href="#sec4">Final word</a>
+ <div class="nav-indicator"><span></span></div>
+ </li>
+ <!-- #EndEditable -->
+ </ul>
+ </div>
+ </div>
+</div>
+<!-- end nav -->
+</nav>
+
+<article id="content">
+<!-- #BeginEditable "content" -->
+ <!--
+ <header class="band title products">
+ <div class="content">
+ <div><h1>Projekt</h1></div>
+ </div>
+ </header>
+ -->
+
+ <section id="sec1" class="band even">
+ <div class="content">
+ <h1>Many fish in the sea...</h1>
+
+ <p>There are many fish in the Relational-Database-Access sea, but who's the Mackerel and who's the Shark?</p>
+
+ <div class="layout2col">
+ <div class="col-left">
+
+ <p>The most common approach for DBMS access is called <a class="extern" target="_blank" href="https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping">Object-Relational-Mapping</a> (ORM) which essentially maps database tables to java classes and table columns to class properties or fields.
+ ORM implementations are mostly built on the standards <a class="extern" target="_blank" href="https://en.wikipedia.org/wiki/Java_Data_Objects">JDO</a> and <a class="extern" target="_blank" href="https://www.ibm.com/docs/en/was-liberty/base?topic=overview-java-persistence-api-jpa">JPA</a>
+ and there are many available products such as <a class="extern" target="_blank" href="https://hibernate.org/">Hibernate</a>, <a class="extern" target="_blank" href="https://openjpa.apache.org/">OpenJPA</a>, <a class="extern" target="_blank" href="https://cayenne.apache.org/">Cayenne</a> and <a class="extern" target="_blank" href="https://en.wikipedia.org/wiki/List_of_object%E2%80%93relational_mapping_software#Java">more</a>.</p>
+
+ <p class="optional">But OR-Mapping has many limitations by design. As it largely shields you from SQL, you have little control over statements other than over the Where part of the query. On the Java side you are largely limited to work with Entity objects rather than specific query results in an "All or Nothing" manner.
+ Maintaining Annotations or Mapping files is a pain and requires special tools. For Relations you need to decide between Lazy and Eager fetching which - no matter what you pick - will be good sometimes and bad another. Metadata access is cumbersome and minimalistic.
+ And the query APIs provided, are unintuitive and largely limited to simple entity queries (with no control over the "select" part) and struggling with ad hoc joins, subqueries and anything that has to do with aggregation. Not to mention Union and Intersection queries. In the Coding Horror blog Object-Relational Mapping has even been called the <a class="extern" target="_blank" href="https://blog.codinghorror.com/object-relational-mapping-is-the-vietnam-of-computer-science/" target="_blank">Vietnam of Computer Science</a>
+ </p>
+
+ <p class="optional">Yes - in fairness - modern ORMs have found ways to mitigate some of the conceptual shortcomings and provide "workarounds", but this comes at a price. Just take a look at examples for <a class="extern" target="_blank" href="https://stackoverflow.com/questions/15990141/how-to-make-a-criteriabuilder-join-with-a-custom-on-condition">getCriteriaBuilder()</a> or <a class="extern" target="_blank" href="https://mikekowdley.medium.com/hibernate-criteriaquery-fetching-a-partial-entity-and-child-with-joins-984987545dd2">createTupleQuery()</a> and it will make your head spin. Seriously JPA?</p>
+
+ </div>
+ <div class="col-right">
+
+ <p><span class="bold">Empire-db</span> however, is a different type of fish. It is not an OR-Mapper in the classical sense, as it does not even attempt to rely on traditional Java Beans (POJOs) for data interchange, yet providing equivalent features as ORMs do.
+ And it generally relies more on the developer to actively do something rather than doing things automatically in the background.</p>
+
+ <p class="optional">Even though is supports traditional Java Beans (POJOs), this is not the preferred approach. Instead it provides Records which essentially are "Dynamic Beans" where you have a generic getter / setter which takes the column as parameter.
+ The record can be loaded as an entire table row, or partially with selected columns or even combine fields from different tables as a "logical" record.
+ But the real advantage comes with the ability not just to control all aspects of your SQL statements but also to do that in a simple and intuitive way so that you are actually capable of using everything a modern DBMS has to offer, such as various column functions, joins of any type (inner, outer, cross) with any kind of join restriction, joins on subqueries, aggregation, and so on,
+ and all that without having to fall back to SQL in String literals or the need for getting a special University degree.</p>
+
+ <p class="optional">The advantage becomes especially apparent in scenarios with large data models, complex relations, sophisticated query demands. And this is also where metadata becomes as - or sometimes even more - important than the actual data itself. With Metadata you can achieve so much, from writing generic functions to building or improving user interfaces.
+ <!--
+ Another distinct feature if Empire-db is its metadata capabilities, which don't just include tables and columns but may also consider the context of a record, allowing e.g. to get the set of allowed values for a field (option list) in the context of a particular record.</p>
+ -->
+ </div>
+ </div>
+
+ <p class="optional">Mackerel or Shark? You decide.</p>
+
+ <a class="showMe">Tell me more...</a>
+ <a class="hideMe">That's is too much!</a>
+
+ </div>
+ </section>
+
+ <section id="sec2" class="band odd">
+ <div class="content">
+
+ <h1>Can your ORM do that?</h1>
+
+ <p>Compare what your current data persistence solution with what Empire-db has to offer.</p>
+
+ <h3>Building queries</h3>
+ <p>The query building engine is the heart of Empire-db. Empire-db can build DQL, DML and DDL statements in the "flavour" of the DBMS used.</p>
+ <table class="features">
+ <colgroup>
+ <col class="feature">
+ <col class="description">
+ </colgroup>
+ <!-- row -->
+ <tr><td class="feature">Query data (DQL)</td>
+ <td class="description content-block">
+ <div class="feature">Query data (DQL)</div>
+ <ul class="bullets">
+ <li>Selecting individual columns from tables and views and allow renaming e.g. to match bean property names</li>
+ <li>Using column functions in selection like e.g. concat, coalesce, trim, substring, length, case ... when, convert, etc.</li>
+ <li>Allowing aggregation queries with aggregation functions (sum, avg, min, max) as well as Group By and Having</li>
+ <li>Joining of entity types using any type of join constraints, not just Foreign-Key relations</li>
+ <li>Joining with subqueries in any depth</li>
+ <li>Where constraints with column functions, logical combinations (and / or), subqueries, etc.</li>
+ <li>Limit and offset (skipping) Rows in the query result, independent from whether directly supported by DBMS or not</li>
+ </ul>
+ <!-- example -->
+ <div><a class="showMe">Give me an example</a></div>
+ <!-- start content-box -->
+ <div class="content-box optional">
+ <p>The following example queries employees with their total payments in the previous year and the percentage of their payments in comparison to the total payments of their respective department.
+ As you can see the query takes two subqueries which are both joined with the employees query.</p>
+ <div class="codebox">
+ <div class="title">Java</div>
+<pre>
+<span class="comment">// Define shortcuts for tables used - not necessary but convenient</span>
+<span class="type">SampleDB</span>.<span class="type">Employees</span> <span class="obj">EMP</span> = <span class="var">db</span>.<span class="obj">EMPLOYEES</span>;
+<span class="type">SampleDB</span>.<span class="type">Departments</span> <span class="obj">DEP</span> = <span class="var">db</span>.<span class="obj">DEPARTMENTS</span>;
+<span class="type">SampleDB</span>.<span class="type">Payments</span> <span class="obj">PAY</span> = <span class="var">db</span>.<span class="obj">PAYMENTS</span>;
+<span class="comment">// last year as literal</span>
+<span class="type">int</span> <span class="var">lastYear</span> = <span class="type">LocalDate</span>.<span class="func">now</span>().<span class="func">getYear</span>()-1;
+<span class="comment">// Employee payments query (QEP)</span>
+<span class="type">DBCommand</span> <span class="var">qepCmd</span> = <span class="var">context</span>.<span class="func">createCommand</span>()
+ .<span class="func">select</span>(<span class="obj">PAY</span>.<span class="field">EMPLOYEE_ID</span>, <span class="obj">PAY</span>.<span class="field">AMOUNT</span>.<span class="func">sum</span>().<span class="func">qualified</span>())
+ .<span class="func">where</span> (<span class="obj">PAY</span>.<span class="field">YEAR</span>.<span class="func">is</span>(<span class="var">lastYear</span>))
+ .<span class="func">groupBy</span>(<span class="obj">PAY</span>.<span class="field">EMPLOYEE_ID</span>);
+<span class="type">DBQuery</span> <span class="var">QEP</span> = <span class="keyword">new</span> <span class="type">DBQuery</span>(<span class="var">qepCmd</span>, <span class="literal">"qep"</span>);
+<span class="comment">// Department payments query (QDP)</span>
+<span class="type">DBCommand</span> <span class="var">qdpCmd</span> = <span class="var">context</span>.<span class="func">createCommand</span>()
+ .<span class="func">select</span>(<span class="obj">EMP</span>.<span class="field">DEPARTMENT_ID</span>, <span class="obj">PAY</span>.<span class="field">AMOUNT</span>.<span class="func">sum</span>().<span class="func">qualified</span>())
+ .<span class="func">join</span> (<span class="obj">PAY</span>.<span class="field">EMPLOYEE_ID</span>, <span class="obj">EMP</span>.<span class="field">ID</span>)
+ .<span class="func">where</span> (<span class="obj">PAY</span>.<span class="field">YEAR</span>.<span class="func">is</span>(<span class="var">lastYear</span>))
+ .<span class="func">groupBy</span>(<span class="obj">EMP</span>.<span class="field">DEPARTMENT_ID</span>);
+<span class="type">DBQuery</span> <span class="var">QDP</span> = <span class="keyword">new</span> <span class="type">DBQuery</span>(<span class="var">qdpCmd</span>, <span class="literal">"qdp"</span>);
+<span class="comment">// Now calculate the percentage of the departments total payments</span>
+<span class="type">DBColumnExpr</span> <span class="var">PCT_OF_DEP_COST</span> = <span class="var">QEP</span>.<span class="func">column</span>(<span class="obj">PAY</span>.<span class="field">AMOUNT</span>.<span class="func">sum</span>())
+ .<span class="func">multiplyWith</span>(100)
+ .<span class="func">divideBy</span>(<span class="var">QDP</span>.<span class="func">column</span>(<span class="obj">PAY</span>.<span class="field">AMOUNT</span>.<span class="func">sum</span>()));
+<span class="comment">// Create the employee query</span>
+<span class="type">DBCommand</span> <span class="var">cmd</span> = <span class="var">context</span>.<span class="func">createCommand</span>()
+ .<span class="func">select</span>(<span class="obj">EMP</span>.<span class="field">ID</span>, <span class="obj">EMP</span>.<span class="field">FIRST_NAME</span>, <span class="obj">EMP</span>.<span class="field">LAST_NAME</span>, <span class="obj">DEP</span>.<span class="field">NAME</span>.<span class="func">as</span>(<span class="literal">"DEPARTMENT"</span>))
+ .<span class="func">select</span>(<span class="var">QEP</span>.<span class="func">column</span>(<span class="obj">PAY</span>.<span class="field">AMOUNT</span>.<span class="func">sum</span>()), <span class="var">PCT_OF_DEP_COST</span>.<span class="func">as</span>(<span class="literal">"PCT_OF_DEPARTMENT_COST"</span>))
+ <span class="comment">// join Employee with Department</span>
+ .<span class="func">join</span>(<span class="obj">EMP</span>.<span class="field">DEPARTMENT_ID</span>, <span class="obj">DEP</span>.<span class="field">ID</span>)
+ <span class="comment">// Join with Subqueries</span>
+ .<span class="func">joinLeft</span>(<span class="obj">EMP</span>.<span class="field">ID</span>, <span class="var">QEP</span>.<span class="func">column</span>(<span class="obj">PAY</span>.<span class="field">EMPLOYEE_ID</span>))
+ .<span class="func">joinLeft</span>(<span class="obj">DEP</span>.<span class="field">ID</span>, <span class="var">QDP</span>.<span class="func">column</span>(<span class="obj">EMP</span>.<span class="field">DEPARTMENT_ID</span>))
+ <span class="comment">// Order by</span>
+ .<span class="func">orderBy</span>(<span class="obj">DEP</span>.<span class="field">NAME</span>.<span class="func">desc</span>())
+ .<span class="func">orderBy</span>(<span class="obj">EMP</span>.<span class="field">LAST_NAME</span>);
+</pre>
+ </div>
+ <!-- sql -->
+ <div class="sqlbox">
+ <div class="title">SQL</div>
+<pre>
+<span class="word">SELECT</span> <span class="alias">t2</span>.ID, <span class="alias">t2</span>.FIRST_NAME, <span class="alias">t2</span>.LAST_NAME, <span class="alias">t1</span>.NAME <span class="word">AS</span> DEPARTMENT
+ , <span class="alias">qep</span>.AMOUNT_SUM, <span class="alias">qep</span>.AMOUNT_SUM*100/<span class="alias">qdp</span>.AMOUNT_SUM <span class="word">AS</span> PCT_OF_DEPARTMENT_COST
+<span class="word">FROM</span> EMPLOYEES <span class="alias">t2</span>
+ <span class="word">INNER JOIN</span> DEPARTMENTS <span class="alias">t1</span> <span class="word">ON</span> <span class="alias">t1</span>.ID = <span class="alias">t2</span>.DEPARTMENT_ID
+ <span class="word">LEFT JOIN</span> (<span class="word">SELECT</span> <span class="alias">t3</span>.EMPLOYEE_ID, sum(<span class="alias">t3</span>.AMOUNT) <span class="word">AS</span> AMOUNT_SUM
+ <span class="word">FROM</span> PAYMENTS <span class="alias">t3</span>
+ <span class="word">WHERE</span> <span class="alias">t3</span>.YEAR=<span class="param"><span class="literal">2021</span></span>
+ <span class="word">GROUP BY</span> <span class="alias">t3</span>.EMPLOYEE_ID
+ ) <span class="alias">qep</span> <span class="word">ON</span> <span class="alias">qep</span>.EMPLOYEE_ID = <span class="alias">t2</span>.ID
+ <span class="word">LEFT JOIN</span> (<span class="word">SELECT</span> <span class="alias">t2</span>.DEPARTMENT_ID, sum(<span class="alias">t3</span>.AMOUNT) <span class="word">AS</span> AMOUNT_SUM
+ <span class="word">FROM</span> PAYMENTS <span class="alias">t3</span>
+ <span class="word">INNER JOIN</span> EMPLOYEES <span class="alias">t2</span> <span class="word">ON</span> <span class="alias">t2</span>.ID = <span class="alias">t3</span>.EMPLOYEE_ID
+ <span class="word">WHERE</span> <span class="alias">t3</span>.YEAR=<span class="param"><span class="literal">2021</span></span>
+ <span class="word">GROUP BY</span> <span class="alias">t2</span>.DEPARTMENT_ID
+ ) <span class="alias">qdp</span> <span class="word">ON</span> <span class="alias">qdp</span>.DEPARTMENT_ID = <span class="alias">t1</span>.ID
+<span class="word">ORDER BY</span> <span class="alias">t1</span>.NAME DESC, <span class="alias">t2</span>.LAST_NAME
+</pre>
+ </div>
+ <div class="hint align-left"><strong>Hint:</strong> If PreparedStatements are enabled, literals will be replaced by statement parameters (?)</div>
+ </div>
+ <!-- end content-box -->
+ <div><a class="hideMe">OK, got it</a></div>
+ </td>
+ </tr>
+ <!-- row -->
+ <tr><td class="feature">Manipulating data (DML)</td>
+ <td class="description content-block">
+ <div class="feature">Manipulating data (DML)</div>
+ <ul class="bullets">
+ <li>Simple insert, update and delete statements involving one entity-type</li>
+ <li>Update statements involving multiple entity-types with joins (if supported by DBMS)</li>
+ <li>Insert Into statements from any kind of query</li>
+ </ul>
+ <!-- example -->
+ <div><a class="showMe">Give me an example</a></div>
+ <!-- start content-box -->
+ <div class="content-box optional">
+ <p>This is an example of an update statement that also contains a join. Please note the difference in DBMS syntax between SQL-Server, PostgreSQL and Oracle.</p>
+ <!-- content-box -->
+ <div class="codebox">
+ <div class="title">Java</div>
+<pre>
+<span class="comment">// create command</span>
+<span class="type">DBCommand</span> <span class="var">cmd</span> = <span class="var">context</span>.<span class="func">createCommand</span>()
+ <span class="comment">// increase model base prices by 5% and set sales info</span>
+ .<span class="func">set</span> (<span class="obj">MODEL</span>.<span class="field">BASE_PRICE</span>.<span class="func">to</span>(<span class="obj">MODEL</span>.<span class="field">BASE_PRICE</span>.<span class="func">multiplyWith</span>(<span class="literal">105</span>).<span class="func">divideBy</span>(<span class="literal">100</span>).<span class="func">round</span>(<span class="literal">2</span>)))
+ .<span class="func">set</span> (<span class="obj">MODEL</span>.<span class="field"><span class="obj">SALES</span>_INFO</span>.<span class="func">to</span>(<span class="literal">"Price update "</span>+<span class="type">LocalDate</span>.<span class="func">now</span>().<span class="func">toString</span>()))
+ <span class="comment">// join with BRANDS</span>
+ .<span class="func">join</span> (<span class="obj">MODEL</span>.<span class="field">WMI</span>, <span class="obj">BRAND</span>.<span class="field">WMI</span>)
+ <span class="comment">// on all Volkswagen with Diesel engine</span>
+ .<span class="func">where</span>(<span class="obj">BRAND</span>.<span class="field">NAME</span>.<span class="func">upper</span>().<span class="func">like</span>(<span class="literal">"VOLKSWAGEN"</span>))
+ .<span class="func">where</span>(<span class="obj">MODEL</span>.<span class="field">ENGINE_TYPE</span>.<span class="func">is</span>(<span class="type">EngineType</span>.D));
+
+<span class="comment">// execute Update statement</span>
+<span class="type">int</span> count = <span class="var">context</span>.<span class="func">executeUpdate</span>(<span class="var">cmd</span>);
+log.<span class="func">info</span>(<span class="literal">"{} models affected"</span>, count);
+</pre>
+ </div>
+ <!-- sql -->
+ <div class="tab-view sqlbox">
+ <div class="tab-head floatbox">
+ <a class="title float-left tab-label active">SQL-Server</a>
+ <a class="title float-left tab-label">PostgreSQL</a>
+ <a class="title float-left tab-label">Oracle</a>
+ </div>
+ <div class="tab-body">
+ <!-- tab-page -->
+<pre>
+<span class="comment">// Microsoft SQLServer syntax</span>
+<span class="word">UPDATE</span> <span class="alias">t2</span>
+<span class="word">SET</span> BASE_PRICE=<span class="func">round</span>(<span class="alias">t2</span>.BASE_PRICE*105/100,2), SALES_INFO=<span class="param"><span class="literal">'Price update 2022-03-03'</span></span>
+<span class="word">FROM</span> MODEL <span class="alias">t2</span>
+ <span class="word">INNER JOIN</span> BRAND <span class="alias">t1</span> <span class="word">ON</span> <span class="alias">t1</span>.WMI = <span class="alias">t2</span>.WMI
+<span class="word">WHERE</span> upper(<span class="alias">t1</span>.NAME) <span class="word">LIKE</span> <span class="param"><span class="literal">'VOLKSWAGEN'</span></span> <span class="word">AND</span> <span class="alias">t2</span>.ENGINE_TYPE=<span class="param"><span class="literal">'D'</span></span>
+</pre>
+ <!-- tab-page -->
+<pre>
+<span class="comment">// PostgreSQL syntax</span>
+<span class="word">UPDATE</span> MODEL <span class="alias">t0</span>
+<span class="word">SET</span> BASE_PRICE=<span class="func">round</span>(<span class="alias">t2</span>.BASE_PRICE*105/100,2), SALES_INFO=<span class="param"><span class="literal">'Price update 2022-03-03'</span></span>
+<span class="word">FROM</span> MODEL <span class="alias">t2</span>
+ <span class="word">INNER JOIN</span> BRAND <span class="alias">t1</span> <span class="word">ON</span> <span class="alias">t1</span>.WMI = <span class="alias">t2</span>.WMI
+<span class="word">WHERE</span> <span class="alias">t0</span>.ID=<span class="alias">t2</span>.ID
+ <span class="word">AND</span> upper(<span class="alias">t1</span>.NAME) <span class="word">LIKE</span> <span class="param"><span class="literal">'VOLKSWAGEN'</span></span> <span class="word">AND</span> <span class="alias">t2</span>.ENGINE_TYPE=<span class="param"><span class="literal">'D'</span></span>
+</pre>
+ <!-- tab-page -->
+<pre>
+<span class="comment">// Oracle syntax</span>
+<span class="word">MERGE INTO</span> MODEL <span class="alias">t2</span>
+<span class="word">USING</span> (<span class="word">SELECT</span> <span class="alias">t2</span>.ID, <span class="func">round</span>(<span class="alias">t2</span>.BASE_PRICE*105/100,2) <span class="word">AS</span> COL_0
+ <span class="word">FROM</span> MODEL <span class="alias">t2</span> <span class="word">INNER JOIN</span> BRAND <span class="alias">t1</span> <span class="word">ON</span> <span class="alias">t1</span>.WMI = <span class="alias">t2</span>.WMI
+ <span class="word">WHERE</span> upper(<span class="alias">t1</span>.NAME) <span class="word">LIKE</span> <span class="param"><span class="literal">'VOLKSWAGEN'</span></span> <span class="word">AND</span> <span class="alias">t2</span>.ENGINE_TYPE=<span class="param"><span class="literal">'D'</span></span>) <span class="alias">q0</span>
+ <span class="word">ON</span> ( <span class="alias">q0</span>.ID=<span class="alias">t2</span>.ID)
+<span class="word">WHEN MATCHED THEN</span> <span class="word">UPDATE</span>
+<span class="word">SET</span> BASE_PRICE=<span class="alias">q0</span>.COL_0, SALES_INFO=<span class="param"><span class="literal">'Price update 2022-03-03'</span></span>
+</pre>
+ <!-- tab-end -->
+ </div>
+ </div>
+ <div class="hint align-left"><strong>Hint:</strong> If PreparedStatements are enabled, literals will be replaced by statement parameters (?)</div>
+ </div>
+ <!-- end content-box -->
+ <div><a class="hideMe">OK, got it</a></div>
+ </td>
+ </tr>
+ <!-- row -->
+ <tr><td class="feature">DDL-operations</td>
+ <td class="description content-block">
+ <div class="feature">DDL-operations</div>
+ <ul class="bullets">
+ <li>Create statements for tables, views and relations</li>
+ <li>Alter table statements to add, modify and remove columns</li>
+ <li>Drop statements for removing tables, views and relations</li>
+ </ul>
+ <!-- example -->
+ <div><a class="showMe">Give me some examples</a></div>
+ <!-- start content-box -->
+ <div class="content-box optional">
+ <div class="codebox">
+<pre>
+<span class="type">DBMSHandler</span> <span class="var">dbms</span> = <span class="var">context</span>.<span class="func">getDbms</span>();
+<span class="type">DBSQLScript</span> <span class="var">script</span> = <span class="keyword">new</span> <span class="type">DBSQLScript</span>(<span class="var">context</span>);
+<span class="comment">// Get DDL script for creating the entire database</span>
+<span class="var">db</span>.<span class="func">getCreateDDLScript</span>(<span class="var">script</span>);
+<span class="comment">// CREATE TABLE NewTable (ID INT IDENTITY(1, 1) NOT NULL, PRIMARY KEY (ID));</span>
+<span class="type">DBTable</span> <span class="obj">NEW_TABLE</span> = <span class="keyword">new</span> <span class="type">DBTable</span>(<span class="literal">"NewTable"</span>, <span class="var">db</span>);
+<span class="obj">NEW_TABLE</span>.<span class="func">addIdentity</span>(<span class="literal">"ID"</span>, <span class="keyword">null</span>);
+<span class="var">dbms</span>.<span class="func">getDDLScript</span>(<span class="type">DDLActionType</span>.CREATE, <span class="obj">NEW_TABLE</span>, <span class="var">script</span>);
+<span class="comment">// ALTER TABLE EMPLOYEES ADD NewColumn NVARCHAR(20) NULL; </span>
+<span class="type">DBColumn</span> <span class="obj">NEW_COLUMN</span> = <span class="obj">EMPLOYEES</span>.<span class="func">addColumn</span>(<span class="literal">"NewColumn"</span>, <span class="type">DataType</span>.VARCHAR, <span class="literal">20</span>, <span class="literal">false</span>);
+<span class="var">dbms</span>.<span class="func">getDDLScript</span>(<span class="type">DDLActionType</span>.CREATE, <span class="obj">NEW_COLUMN</span>, <span class="var">script</span>);
+<span class="comment">// ALTER TABLE EMPLOYEES ALTER COLUMN EMAIL NVARCHAR(80);</span>
+<span class="obj">EMPLOYEES</span>.<span class="field">EMAIL</span>.<span class="func">setSize</span>(<span class="literal">80</span>);
+<span class="var">dbms</span>.<span class="func">getDDLScript</span>(<span class="type">DDLActionType</span>.ALTER, <span class="obj">EMPLOYEES</span>.<span class="field">EMAIL</span>, <span class="var">script</span>);
+<span class="comment">// ALTER TABLE EMPLOYEES DROP COLUMN GENDER;</span>
+<span class="var">dbms</span>.<span class="func">getDDLScript</span>(<span class="type">DDLActionType</span>.DROP, <span class="obj">EMPLOYEES</span>.<span class="field">GENDER</span>, <span class="var">script</span>);
+<span class="comment">// DROP TABLE NewTable;</span>
+<span class="var">dbms</span>.<span class="func">getDDLScript</span>(<span class="type">DDLActionType</span>.DROP, <span class="obj">NEW_TABLE</span>, <span class="var">script</span>);
+</pre>
+ </div>
+ </div>
+ <!-- end content-box -->
+ <div><a class="hideMe">OK, got it</a></div>
+ </td>
+ </tr>
+ </table>
+ <p><br/></p>
+ <h3>Reading and modifying data</h3>
+ <p>Building queries is one thing, but it's far from being everything. It is certainly not convenient to build an insert or update statement from scratch
+ every time you want to add or modify an entity, even more so as other aspects like identity management and concurrency control have to be considered.<br/>
+ And as different situations have different needs, it's always good to have a choice. So please choose:</p>
+ <table class="features">
+ <colgroup>
+ <col class="feature">
+ <col class="description">
+ </colgroup>
+ <!-- row -->
+ <tr><td class="feature">Records</td>
+ <td class="description content-block">
+ <div class="feature">Records</div>
+ <p>Records are the best Java type for performing CRUD operations in Empire-db. Records allow field modification and data conversion. Records also deal with identity management and concurrency control a.k.a Optimistic Locking.</p>
+ <div><a class="showMe">Give me some examples</a></div>
+ <div class="codebox optional">
+<pre>
+<span class="type">DBRecord</span> <span class="var">record</span> = <span class="keyword">new</span> <span class="type">DBRecord</span>(<span class="var">context</span>, <span class="obj">EMPLOYEES</span>);
+<span class="comment">// read record with identity column primary key</span>
+<span class="var">record</span>.<span class="func">read</span>(<span class="literal">55</span>);
+<span class="comment">// read record with multi-column primary key </span>
+<span class="var">record</span>.<span class="func">read</span>(<span class="type">DBRecord</span>.<span class="func">key</span>(<span class="literal">55</span>, <span class="literal">2021</span>, <span class="literal">12</span>));
+<span class="comment">// read with constraints </span>
+<span class="var">record</span>.<span class="func">read</span>(<span class="obj">EMPLOYEES</span>.<span class="field">FIRST_NAME</span>.<span class="func">is</span>(<span class="literal">"Anna"</span>).<span class="func">and</span>(<span class="obj">EMPLOYEES</span>.<span class="field">LAST_NAME</span>.<span class="func">is</span>(<span class="literal">"Smith"</span>)));
+<span class="comment">// read record identified by a subquery</span>
+<span class="var">record</span>.<span class="func">read</span>(<span class="obj">EMPLOYEES</span>.<span class="field">ID</span>.<span class="func">is</span>(<span class="var">cmd</span>));
+<span class="comment">// read record partially with only firstname, lastname and salary</span>
+<span class="var">record</span>.<span class="func">read</span>(<span class="type">DBRecord</span>.<span class="func">key</span>(<span class="literal">55</span>), <span class="type">PartialMode</span>.INCLUDE, <span class="obj">EMPLOYEES</span>.<span class="field">FIRST_NAME</span>, <span class="obj">EMPLOYEES</span>.<span class="field">LAST_NAME</span>, <span class="obj">EMPLOYEES</span>.<span class="field">SALARY</span>);
+<span class="comment">// create a new record</span>
+<span class="var">record</span>.<span class="func">create</span>();
+<span class="comment">// modify a record </span>
+<span class="var">record</span>.<span class="func">set</span>(<span class="obj">EMPLOYEES</span>.<span class="field">FIRST_NAME</span>, <span class="literal">"Fred"</span>)
+ .<span class="func">set</span>(<span class="obj">EMPLOYEES</span>.<span class="field">LAST_NAME</span>, <span class="literal">"Flintstone"</span>);
+<span class="comment">// insert or update a record</span>
+<span class="var">record</span>.<span class="func">update</span>();
+<span class="comment">// delete a record</span>
+<span class="var">record</span>.<span class="func">delete</span>();
+<span class="comment">// read a list of records</span>
+<span class="type">List</span><<span class="type">DBRecord</span>> <span class="var">list</span> = <span class="var">context</span>.<span class="func">getUtils</span>().<span class="func">queryRecordList</span>(<span class="var">cmd</span>, <span class="obj">EMPLOYEES</span>);
+<span class="comment">// read a list of Employee records</span>
+<span class="type">List</span><<span class="type">EmployeeRecord</span>> <span class="var">list</span> = <span class="var">context</span>.<span class="func">getUtils</span>().<span class="func">queryRecordList</span>(<span class="var">cmd</span>, <span class="obj">EMPLOYEES</span>, <span class="type">EmployeeRecord</span>.<span class="keyword">class</span>);
+</pre>
+ </div>
+ <div><a class="hideMe">OK, got it</a></div>
+ </td>
+ </tr>
+ <!-- row -->
+ <tr><td class="feature">DataListEntry</td>
+ <td class="description content-block">
+ <div class="feature">DataListEntry</div>
+ <p>A DataListEntry is a lightweight form of data container providing the same interface as a Record but being read-only. You can easily mix columns from various entity-types and query either a single item or a list of items.</p>
+ <p>We also recommend to subclass DataListEntry in order to enrich it with additional getters methods.</p>
+ <!-- example -->
+ <div><a class="showMe">Give me an example</a></div>
+ <!-- start content-box -->
+ <div class="content-box optional">
+ <div class="codebox">
+<pre>
+<span class="type">DBUtils</span> <span class="var">utils</span> = <span class="var">context</span>.<span class="func">getUtils</span>();
+<span class="comment">// query a single data entry</span>
+<span class="type">DataListEntry</span> <span class="var">item</span> = <span class="var">utils</span>.<span class="func">queryDataEntry</span>(<span class="var">cmd</span>);
+<span class="comment">// query a list of data entries</span>
+<span class="type">List</span><<span class="type">DataListEntry</span>> <span class="var">list</span> = <span class="var">utils</span>.<span class="func">queryDataList</span>(<span class="var">cmd</span>);
+
+<span class="comment">// Subclass DataListEntry example</span>
+<span class="keyword">public</span> <span class="keyword">static</span> <span class="keyword">class</span> <span class="type">EmployeeInfo</span> <span class="keyword">extends</span> <span class="type">DataListEntry</span> {
+ <span class="keyword">private</span> <span class="keyword">static</span> <span class="keyword">final</span> <span class="type">long</span> serialVersionUID = 1L;
+ <span class="keyword">private</span> <span class="keyword">final</span> <span class="type">SampleDB</span> db;
+ <span class="comment">// Constructor</span>
+ <span class="keyword">public</span> <span class="type">EmployeeInfo</span>(<span class="type">DataListHead</span> head, <span class="type">Object</span>[] values) {
+ <span class="keyword">super</span>(head, values);
+ <span class="keyword">this</span>.db = head.<span class="func">getDatabase</span>(<span class="type">SampleDB</span>.<span class="keyword">class</span>);
+ }
+ <span class="comment">// Add additional getters</span>
+ <span class="keyword">public</span> <span class="type">String</span> getDisplayName()
+ {
+ <span class="keyword">return</span> <span class="func">getString</span>(db.<span class="obj">EMPLOYEES</span>.<span class="field">FIRST_NAME</span>).<span class="func">substring</span>(<span class="literal">1</span>)+<span class="literal">". "</span>
+ +<span class="func">getString</span>(db.<span class="obj">EMPLOYEES</span>.<span class="field">LAST_NAME</span>);
+ }
+}
+<span class="comment">// query a single EmployeeInfo</span>
+<span class="type">EmployeeInfo</span> <span class="var">item</span> = <span class="var">utils</span>.<span class="func">queryDataEntry</span>(<span class="var">cmd</span>, <span class="type">EmployeeInfo</span>.<span class="keyword">class</span>);
+<span class="comment">// query a list of EmployeeInfos</span>
+<span class="type">List</span><<span class="type">EmployeeInfo</span>> <span class="var">list</span> = <span class="var">utils</span>.<span class="func">queryDataList</span>(<span class="var">cmd</span>, <span class="type">EmployeeInfo</span>.<span class="keyword">class</span>);
+<span class="comment">// query a list of EmployeeInfos limited to 10 items maximum</span>
+<span class="type">List</span><<span class="type">EmployeeInfo</span>> <span class="var">list</span> = <span class="var">utils</span>.<span class="func">queryDataList</span>(<span class="var">cmd</span>, <span class="type">EmployeeInfo</span>.<span class="keyword">class</span>, <span class="literal">0</span>, <span class="literal">10</span>);
+</pre>
+ </div>
+ </div>
+ <!-- end content-box -->
+ <div><a class="hideMe">OK, got it</a></div>
+ </td>
+ </tr>
+ <!-- row -->
+ <tr><td class="feature">Java Beans (POJOs)</td>
+ <td class="description content-block">
+ <div class="feature">Java Beans (POJOs)</div>
+ <p>Traditional Java-Beans can be used to hold query results and exchange data with records in both directions.</p>
+ <div><a class="showMe">Give me some examples</a></div>
+ <div class="codebox optional">
+<pre>
+<span class="type">DBUtils</span> <span class="var">utils</span> = <span class="var">context</span>.<span class="func">getUtils</span>();
+<span class="comment">// query entity bean using primary key</span>
+<span class="type">Employee</span> <span class="var">employee</span> = <span class="var">utils</span>.<span class="func">queryBean</span>(<span class="type">Employee</span>.<span class="keyword">class</span>, <span class="obj">EMPLOYEES</span>, <span class="type">DBRecord</span>.<span class="func">key</span>(<span class="literal">55</span>));
+<span class="comment">// query entity bean with constraints </span>
+<span class="type">Employee</span> <span class="var">employee</span> = <span class="var">utils</span>.<span class="func">queryBean</span>(<span class="type">Employee</span>.<span class="keyword">class</span>, <span class="obj">EMPLOYEES</span>.<span class="field">FIRST_NAME</span>.<span class="func">is</span>(<span class="literal">"Anna"</span>)
+ .<span class="func">and</span>(<span class="obj">EMPLOYEES</span>.<span class="field">LAST_NAME</span> .<span class="func">is</span>(<span class="literal">"Smith"</span>)));
+<span class="comment">// query entity bean list from query</span>
+<span class="type">List</span><<span class="type">Employee</span>> <span class="var">list</span> = <span class="var">utils</span>.<span class="func">queryBeanList</span>(<span class="var">cmd</span>, <span class="type">Employee</span>.<span class="keyword">class</span>, <span class="obj">EMPLOYEES</span>, <span class="literal">null</span>);
+<span class="comment">// query result bean from query</span>
+<span class="type">QueryResult</span> <span class="var">result</span> = <span class="var">utils</span>.<span class="func">queryBean</span>(<span class="var">cmd</span>, <span class="type">QueryResult</span>.<span class="keyword">class</span>);
+<span class="comment">// query result bean list from query</span>
+<span class="type">List</span><<span class="type">QueryResult</span>> <span class="var">list</span> = <span class="var">utils</span>.<span class="func">queryBeanList</span>(<span class="var">cmd</span>, <span class="type">QueryResult</span>.<span class="keyword">class</span>, <span class="literal">null</span>);
+</pre>
+ </div>
+ <div><a class="hideMe">OK, got it</a></div>
+ </td>
+ </tr>
+ <!-- row -->
+ <tr><td class="feature">Simple data</td>
+ <td class="description content-block">
+ <div class="feature">Simple data</div>
+ <p>Very often you just want to query something simple like a single value or a simple list. The DBUtils class provides many methods that will provide you with that data based on your query statement.</p>
+ <!-- example -->
+ <div><a class="showMe">Give me an example</a></div>
+ <!-- start content-box -->
+ <div class="codebox optional">
+<pre>
+<span class="type">DBUtils</span> <span class="var">utils</span> = <span class="var">context</span>.<span class="func">getUtils</span>();
+<span class="comment">// query a single value, don't fail if no result</span>
+<span class="type">Object</span> value = <span class="var">utils</span>.<span class="func">querySingleValue</span>(<span class="var">cmd</span>, <span class="literal">false</span>);
+<span class="comment">// query a single decimal</span>
+<span class="type">BigDecimal</span> value = <span class="var">utils</span>.<span class="func">querySingleValue</span>(<span class="var">cmd</span>, <span class="type">BigDecimal</span>.<span class="keyword">class</span>, <span class="literal">false</span>);
+<span class="comment">// query a single integer, fail if no result</span>
+<span class="type">int</span> number = <span class="var">utils</span>.<span class="func">querySingleInt</span>(<span class="var">cmd</span>);
+<span class="comment">// query a single integer default to -1 </span>
+<span class="type">int</span> number = <span class="var">utils</span>.<span class="func">querySingleInt</span>(<span class="var">cmd</span>, <span class="literal">-1</span>);
+<span class="comment">// query a single String </span>
+<span class="type">String</span> text = <span class="var">utils</span>.<span class="func">querySingleString</span>(<span class="var">cmd</span>);
+<span class="comment">// query a list of Strings</span>
+<span class="type">List</span><<span class="type">String</span>> <span class="var">list</span> = <span class="var">utils</span>.<span class="func">querySimpleList</span>(<span class="type">String</span>.<span class="keyword">class</span>, <span class="var">cmd</span>);
+<span class="comment">// query a single row of data</span>
+<span class="type">Object</span>[] row = <span class="var">utils</span>.<span class="func">querySingleRow</span>(<span class="var">cmd</span>);
+<span class="comment">// query a set of options consisting of a value + text pair</span>
+<span class="type">Options</span> options = <span class="var">utils</span>.<span class="func">queryOptionList</span>(<span class="var">cmd</span>);
+<span class="comment">// query the number of rows in the result of a query</span>
+<span class="type">int</span> rowCount = <span class="var">utils</span>.<span class="func">queryRowCount</span>(<span class="var">cmd</span>);
+</pre>
+ </div>
+ <!-- end content-box -->
+ <div><a class="hideMe">OK, got it</a></div>
+ </td>
+ </tr>
+ </table>
+
+ </div>
+ </section>
+
+ <section id="sec3" class="band even">
+ <div class="content">
+
+ <h1>Going beyond the data</h1>
+
+ <p>In Empire-db the data is just one side of the coin. The other equally important side is metadata</p>
+ <p>Metadata is useful for many things in data processing. It is for example very useful to create generic functions that can be used with multiple entity-types like e.g. for data synchronization.
+ But one of the most consequential applications of metadata is when using it to build a UI of listings, input forms and the like.
+ What you are using metadata for is your business, but when it comes to metadata Empire-db has a lot to offer.</p>
+
+ <h3>Metadata types</h3>
+
+ <p>Empire-db supports more that just (trivial) metadata which is already provided with the data model definition like the data-type or the maximum number of characters of a column.
+ We generally distinguish the following types of metadata:</p>
+
+ <div class="layout2col">
+ <div class="col-left">
+ <!-- col start -->
+ <h4>Static model metadata</h4>
+ <p>Static model metadata is provided together with the data-model and usually attached to a particular table or view column.</p>
+ <ul class="bullets white">
+ <li>The column title used for e.g. for labels and table headers. This can also be a message key used for internationalization</li>
+ <li>A control-render-type that indicates which type of UI-widget should be used for rendering this column (for value input as well as display)</li>
+ <li>Number formatting options like e.g. number of fraction digits and whether or not to use a thousands separator</li>
+ <li>The unit of a column value like e.g. for currencies $, ⬠or £</li>
+ <li>Additional HTML style-classes when rendering values in HTML</li>
+ </ul>
+ <p>Additionally more user-defined metadata can easily be added to columns using the setAttribute() method.</p>
+ <!-- col end -->
+ </div>
+ <div class="col-right">
+ <!-- col start -->
+ <h4>Contextual metadata</h4>
+ <p>Context specific metadata may depend on the user, the value of other fields or any kind of business logic. This is provided via the record (entity)</p>
+ <ul class="bullets white">
+ <li>The set of context-specific options (allowed values) for a particular field in the given context</li>
+ <li>Whether or not a field is visible in this context</li>
+ <li>Whether or not a field is read-only in this context</li>
+ <li>Whether or not a field is mandatory in this context</li>
+ </ul>
+ <!-- col end -->
+ </div>
+ </div>
+
+ <h3>Metadata Use-Case: building a UI form from metadata</h3>
+
+ <div class="layout2col">
+ <div class="col-left">
+ <!-- col start -->
+ <div>This is what an input form for an Employee looks like when rendered by JavaServerFaces (JSF) using the <strong>empire-db-jsf</strong> extentions module:</div>
+ <div class="formPanel">
+ <table class="inputForm">
+ <tbody><tr><td class="eCtlLabel"><label class="eLabel" for="j_id_o:inp">Salutation:</label></td><td class="eCtlInput"><input id="j_id_o:inp" name="j_id_o:inp" type="text" value="Mrs." maxlength="5" class="eInput eTypeText eInpShort" lang="en"></td></tr>
+ <tr><td class="eCtlLabel"><label class="eLabel eInpReq" for="j_id_r:inp">Firstname:</label></td><td class="eCtlInput"><input id="j_id_r:inp" name="j_id_r:inp" type="text" value="Anna" maxlength="40" class="eInput eTypeText eInpReq" lang="en"></td></tr>
+ <tr><td class="eCtlLabel"><label class="eLabel eInpReq" for="j_id_u:inp">Lastname:</label></td><td class="eCtlInput"><input id="j_id_u:inp" name="j_id_u:inp" type="text" value="Smith" maxlength="40" class="eInput eTypeText eInpReq" lang="en"></td></tr>
+ <tr><td class="eCtlLabel"><label class="eLabel" for="j_id_x:inp">Date of Birth:</label></td><td class="eCtlInput"><input id="j_id_x:inp" name="j_id_x:inp" type="text" value="" maxlength="10" class="eInput eTypeDate eValNull" lang="en"><span class="eInputHint">[yyyy-MM-dd]</span></td></tr>
+ <tr><td class="eCtlLabel"><label class="eLabel eInpReq" for="j_id_10:inp">Department:</label></td><td class="eCtlInput"><select id="j_id_10:inp" name="j_id_10:inp" size="1" class="eInput eTypeNumber eInpReq"> <option value="2">Development</option> <option value="1">Procurement</option> <option value="3" selected="selected">Sales</option></select></td></tr>
+ <tr><td class="eCtlLabel"><label class="eLabel eInpReq" for="j_id_13:inp">Gender:</label></td><td class="eCtlInput"><select id="j_id_13:inp" name="j_id_13:inp" size="1" class="eInput eTypeText eInpReq eInpShort"> <option value="M">Male</option> <option value="F" selected="selected">Female</option></select></td> </tr>
+ <tr><td class="eCtlLabel"><label class="eLabel" for="j_id_16:inp">Phone:</label></td><td class="eCtlInput"><input id="j_id_16:inp" name="j_id_16:inp" type="text" value="0815-" maxlength="40" class="eInput eTypeText" lang="en"></td></tr>
+ <tr><td class="eCtlLabel"><label class="eLabel" for="j_id_19:inp">E-Mail:</label></td><td class="eCtlInput"><input id="j_id_19:inp" name="j_id_19:inp" type="text" value="" maxlength="80" class="eInput eTypeText eValNull" lang="en"></td></tr>
+ <tr><td class="eCtlLabel"><label class="eLabel" for="j_id_1c:inp">Salary p.a.:</label></td><td class="eCtlInput"><input id="j_id_1c:inp" name="j_id_1c:inp" type="text" value="44,250.00" maxlength="14" class="eInput eTypeNumber eInpDecimal" lang="en"><span class="eUnit">USD</span></td></tr>
+ <tr><td class="eCtlLabel"><label class="eLabel eInpReq" for="j_id_1f:inp">Retired:</label></td><td class="eCtlInput"><input id="j_id_1f:inp" type="checkbox" name="j_id_1f:inp" value="true" class="eInput eTypeBool eInpReq"></td></tr>
+ <tr><td class="eCtlLabel"><label class="eLabel">Last change:</label></td><td class="eCtlInput"><span class="eInput eTypeDate eInpDis">Saturday, February 26, 2022 2:37:03 PM CET</span></td></tr>
+ </tbody></table>
+ </div>
+ <div class="hint"><strong>Hint:</strong> Fields in yellow and with asterisk are mandatory fields.</div>
+
+ <!-- col end -->
+ </div>
+ <div class="col-right">
+ <!-- col start -->
+
+ <div>And this is the corresponding JSF xhtml code for the input form:</div>
+ <div class="codebox" style="margin-top:10px">
+ <div class="title">xhtml</div>
+<pre>
+<span class="comment"><!-- input form with empire-db controls --</span>>
+<<span class="tag">e:record</span> <span class="attr">value</span>="<span class="el">#{page.employeeRecord}</span>">
+<<span class="tag">h:panelGroup</span> <span class="attr">class</span>="<span class="literal">formPanel</span>" <span class="attr">layout</span>="<span class="literal">block</span>">
+ <<span class="tag">table</span> <span class="attr">class</span>="<span class="literal">inputForm</span>">
+ <<span class="tag">tr</span>><<span class="tag">e:control</span> <span class="attr">column</span>="<span class="el">#{db.EMPLOYEES.SALUTATION}</span>"/><<span class="tag">/tr</span>>
+ <<span class="tag">tr</span>><<span class="tag">e:control</span> <span class="attr">column</span>="<span class="el">#{db.EMPLOYEES.FIRST_NAME}</span>"/><<span class="tag">/tr</span>>
+ <<span class="tag">tr</span>><<span class="tag">e:control</span> <span class="attr">column</span>="<span class="el">#{db.EMPLOYEES.LAST_NAME}</span>"/><<span class="tag">/tr</span>>
+ <<span class="tag">tr</span>><<span class="tag">e:control</span> <span class="attr">column</span>="<span class="el">#{db.EMPLOYEES.DATE_OF_BIRTH}</span>"/><<span class="tag">/tr</span>>
+ <<span class="tag">tr</span>><<span class="tag">e:control</span> <span class="attr">column</span>="<span class="el">#{db.EMPLOYEES.DEPARTMENT_ID}</span>"/><<span class="tag">/tr</span>>
+ <<span class="tag">tr</span>><<span class="tag">e:control</span> <span class="attr">column</span>="<span class="el">#{db.EMPLOYEES.GENDER}</span>"/><<span class="tag">/tr</span>>
+ <<span class="tag">tr</span>><<span class="tag">e:control</span> <span class="attr">column</span>="<span class="el">#{db.EMPLOYEES.PHONE_NUMBER}</span>"/><<span class="tag">/tr</span>>
+ <<span class="tag">tr</span>><<span class="tag">e:control</span> <span class="attr">column</span>="<span class="el">#{db.EMPLOYEES.EMAIL}</span>"/><<span class="tag">/tr</span>>
+ <<span class="tag">tr</span>><<span class="tag">e:control</span> <span class="attr">column</span>="<span class="el">#{db.EMPLOYEES.SALARY}</span>"/><<span class="tag">/tr</span>>
+ <<span class="tag">tr</span>><<span class="tag">e:control</span> <span class="attr">column</span>="<span class="el">#{db.EMPLOYEES.RETIRED}</span>"/><<span class="tag">/tr</span>>
+ <<span class="tag">tr</span>><<span class="tag">e:control</span> <span class="attr">column</span>="<span class="el">#{db.EMPLOYEES.UPDATE_TIMESTAMP}</span>"/><<span class="tag">/tr</span>>
+ <<span class="tag">/table</span>>
+<<span class="tag">/h:panelGroup</span>>
+<<span class="tag">/e:record</span>>
+</pre>
+ </div>
+
+ <!-- col end -->
+ </div>
+ </div>
+
+ </div>
+ </section>
+
+ <section id="sec4" class="band odd">
+ <div class="content">
+ <h1>Final word</h1>
+ <p>In software development things get complicated. Sometimes sooner, sometimes later, but they inevitably do.<br/>
+ What was once meant to be a "Simple Database", may well turn into a Monster over the years, with ever more tables, views, columns, data.</p>
+
+ <p>So how do you tame the Monster?</p>
+
+ <p>We believe the key to that are Simplicity and Flexibility.</p>
+ <ul class="bullets">
+ <li>By Simplicity we mean that there should be a strong correlation between your code and the SQL statements that are generated. This improves code readability and maintainability.
+ And it makes coding easier. With Empire-db it is often easier to write a statement in code than typing it directly in SQL.</li>
+ <li>By Flexibility we mean that one can easily modify and extend the underlying functionality, even without deep knowledge of the underlying framework.
+ In Empire-db you achieve this by simply subclassing the database, table, record, utils, context, dbms-handler, etc.
+ Using only pure OO functionality, allows you to change or add application specific behaviour or add more user-defined metadata to your model.</li>
+ </ul>
+
+ <h3>One more Use-Case</h3>
+
+ <p>In practice you almost never have "the one query" on a set of data. Rather your query often depends on the context and certain conditions for which you might need to dynamically select columns, add constraints and joins or set the row order.</p>
+
+ <p>Suppose your application provides a view of car dealers. A user might decide to list them all, or choose to set any of three possible filters: Country, Brand and/or the Minimum Annual Turnover.
+ Depending on which of those filters the user chooses, different constraints and joins need to be added to the statement and even the columns that should be displayed (i.e. selected) differ. So how do you dynamically build such a query statement?</p>
+ <!-- example -->
+ <div><a class="showMe">OK, show me how to do it</a></div>
+
+ <div class="content-box optional">
+ <div class="codebox">
+ <div class="title">Java</div>
+<pre>
+<span class="type">void</span> dealerQuery(<span class="type">String</span> country, <span class="type">String</span> brand, <span class="type">BigDecimal</span> minTurnover) {
+ <span class="comment">// create a command</span>
+ <span class="type">DBCommand</span> <span class="var">cmd</span> = <span class="var">context</span>.<span class="func">createCommand</span>();
+ <span class="comment">// select car dealer info</span>
+ <span class="var">cmd</span>.<span class="func">select</span>(<span class="obj">DEALER</span>.<span class="field">COMPANY_NAME</span>, <span class="obj">DEALER</span>.<span class="field">CITY</span>, <span class="obj">DEALER</span>.<span class="field">COUNTRY</span>);
+ <span class="comment">// Constrain to country?</span>
+ if (country!=<span class="keyword">null</span>)
+ <span class="var">cmd</span>.<span class="func">where</span> (<span class="obj">DEALER</span>.<span class="field">COUNTRY</span>.<span class="func">likeUpper</span>(country));
+ <span class="comment">// Constrain to brand?</span>
+ if (brand!=<span class="keyword">null</span>) {
+ <span class="comment">// Single brand</span>
+ <span class="var">cmd</span>.<span class="func">join</span>(<span class="obj">DEALER</span>.<span class="field">ID</span>, <span class="obj">DEALER_BRANDS</span>.<span class="field">DEALER_ID</span>)
+ .<span class="func">where</span>(<span class="obj">BRAND</span>.<span class="field">NAME</span>.<span class="func">likeUpper</span>(brand+<span class="literal">"%"</span>));
+ <span class="comment">// select the BRAND NAME</span>
+ <span class="var">cmd</span>.<span class="func">select</span>(<span class="obj">BRAND</span>.<span class="field">NAME</span>);
+ } else {
+ <span class="comment">// Show all brands a dealer distributes, separated by comma</span>
+ <span class="type">DBCommand</span> qryCmd = <span class="var">context</span>.<span class="func">createCommand</span>()
+ .<span class="func">select</span> (<span class="obj">DEALER_BRANDS</span>.<span class="field">DEALER_ID</span>, <span class="obj">BRAND</span>.<span class="field">NAME</span>.<span class="func">strAgg</span>(<span class="literal">", "</span>).<span class="func">qualified</span>())
+ .<span class="func">join</span> (<span class="obj">DEALER_BRANDS</span>.<span class="field">WMI</span>, <span class="obj">BRAND</span>.<span class="field">WMI</span>)
+ .<span class="func">groupBy</span>(<span class="obj">DEALER_BRANDS</span>.<span class="field">DEALER_ID</span>);
+ <span class="type">DBQuery</span> qry = <span class="keyword">new</span> <span class="type">DBQuery</span>(qryCmd, <span class="literal">"qbrands"</span>);
+ <span class="comment">// join with dealer query</span>
+ <span class="var">cmd</span>.<span class="func">join</span>(<span class="obj">DEALER</span>.<span class="field">ID</span>, qry.<span class="func">column</span>(<span class="obj">DEALER_BRANDS</span>.<span class="field">DEALER_ID</span>));
+ <span class="comment">// select all brands as a list</span>
+ <span class="var">cmd</span>.<span class="func">select</span>(qry.<span class="func">column</span>(<span class="obj">BRAND</span>.<span class="field">NAME</span>.<span class="func">strAgg</span>(<span class="literal">", "</span>)).<span class="func">as</span>(<span class="literal">"ALL_BRANDS"</span>));
+ }
+ <span class="comment">// Constrain to min turnover? If so, select turnover</span>
+ if (minTurnover!=<span class="keyword">null</span>) {
+ <span class="comment">// create subquery for sales</span>
+ <span class="type">DBCommand</span> qryCmd = <span class="var">context</span>.<span class="func">createCommand</span>()
+ .<span class="func">select</span>(<span class="obj">SALES</span>.<span class="field">DEALER_ID</span>, <span class="obj">SALES</span>.<span class="field">PRICE</span>.<span class="func">sum</span>().<span class="func">qualified</span>())
+ .<span class="func">where</span>(<span class="obj">SALES</span>.<span class="field">YEAR</span>.<span class="func">is</span>(<span class="type">LocalDate</span>.<span class="func">now</span>().<span class="func">getYear</span>()-1))
+ .<span class="func">groupBy</span>(<span class="obj">SALES</span>.<span class="field">DEALER_ID</span>)
+ .<span class="func">having</span>(<span class="obj">SALES</span>.<span class="field">PRICE</span>.<span class="func">sum</span>().<span class="func">isMoreOrEqual</span>(minTurnover));
+ <span class="type">DBQuery</span> qry = <span class="keyword">new</span> <span class="type">DBQuery</span>(qryCmd, <span class="literal">"qsales"</span>);
+ <span class="type">DBColumn</span> <span class="obj">PRICE_SUM</span> = qry.<span class="func">column</span>(<span class="obj">SALES</span>.<span class="field">PRICE</span>.<span class="func">sum</span>());
+ <span class="comment">// join with dealer query</span>
+ <span class="var">cmd</span>.<span class="func">join</span>(<span class="obj">DEALER</span>.<span class="field">ID</span>, qry.<span class="func">column</span>(<span class="obj">SALES</span>.<span class="field">DEALER_ID</span>));
+ <span class="comment">// select the turnover</span>
+ <span class="var">cmd</span>.<span class="func">select</span>(<span class="obj">PRICE_SUM</span>.<span class="func">as</span>(<span class="literal">"TURNOVER"</span>));
+ <span class="comment">// order by turnover descending</span>
+ <span class="var">cmd</span>.<span class="func">orderBy</span>(<span class="obj">PRICE_SUM</span>.<span class="func">desc</span>());
+ }
+ <span class="comment">// order by</span>
+ <span class="var">cmd</span>.<span class="func">orderBy</span>(<span class="obj">DEALER</span>.<span class="field">COMPANY_NAME</span>);
+
+ <span class="comment">// Done. For curiosity, get the row count</span>
+ <span class="type">int</span> dealerCount = <span class="var">context</span>.<span class="func">getUtils</span>().<span class="func">queryRowCount</span>(<span class="var">cmd</span>);
+ log.<span class="func">info</span>(<span class="literal">"Query will return {} dealers"</span>, dealerCount);
+
+ <span class="comment">// Finally, execute the query and print the result</span>
+ <span class="type">List</span><<span class="type">DataListEntry</span>> <span class="var">list</span> = <span class="var">context</span>.<span class="func">getUtils</span>().<span class="func">queryDataList</span>(<span class="var">cmd</span>);
+ for (<span class="type">DataListEntry</span> item : <span class="var">list</span>)
+ <span class="type">System</span>.out.<span class="func">println</span>(item);
+}
+</pre>
+ </div>
+ <!-- sql -->
+ <div class="sqlbox">
+ <div class="title">SQL (just one possiblity)</div>
+<pre>
+<span class="word">SELECT</span> <span class="alias">t3</span>.COMPANY_NAME, <span class="alias">t3</span>.CITY, <span class="alias">t3</span>.COUNTRY,
+ <span class="alias">qbrands</span>.NAME_STRAGG <span class="word">AS</span> ALL_BRANDS, <span class="alias">qsales</span>.PRICE_SUM <span class="word">AS</span> TURNOVER
+<span class="word">FROM</span> DEALER <span class="alias">t3</span>
+ <span class="word">INNER JOIN</span> (<span class="word">SELECT</span> <span class="alias">t4</span>.DEALER_ID, string_agg(<span class="alias">t1</span>.NAME,<span class="literal">'|'</span>) <span class="word">AS</span> NAME_STRAGG
+ <span class="word">FROM</span> DEALER_BRANDS <span class="alias">t4</span> <span class="word">INNER JOIN</span> BRAND <span class="alias">t1</span> <span class="word">ON</span> <span class="alias">t1</span>.WMI = <span class="alias">t4</span>.WMI
+ <span class="word">GROUP BY</span> <span class="alias">t4</span>.DEALER_ID
+ ) <span class="alias">qbrands</span> <span class="word">ON</span> <span class="alias">qbrands</span>.DEALER_ID = <span class="alias">t3</span>.ID
+ <span class="word">INNER JOIN</span> (<span class="word">SELECT</span> <span class="alias">t5</span>.DEALER_ID, sum(<span class="alias">t5</span>.PRICE) <span class="word">AS</span> PRICE_SUM
+ <span class="word">FROM</span> SALES <span class="alias">t5</span>
+ <span class="word">WHERE</span> <span class="alias">t5</span>.YEAR=<span class="literal">2021</span>
+ <span class="word">GROUP BY</span> <span class="alias">t5</span>.DEALER_ID
+ <span class="word">HAVING</span> sum(<span class="alias">t5</span>.PRICE)>=<span class="literal">100000</span>
+ ) <span class="alias">qsales</span> <span class="word">ON</span> <span class="alias">qsales</span>.DEALER_ID = <span class="alias">t3</span>.ID
+<span class="word">WHERE</span> ucase(<span class="alias">t3</span>.COUNTRY) <span class="word">LIKE</span> ucase(<span class="literal">'USA'</span>)
+<span class="word">ORDER BY</span> <span class="alias">qsales</span>.PRICE_SUM DESC, <span class="alias">t3</span>.COMPANY_NAME
+</pre>
+ </div>
+ </div>
+ <!-- end content-box -->
+ <div><a class="hideMe">OK, that's cool.</a></div>
+
+ </div>
+ </section>
+<!-- #EndEditable -->
+</article>
+
+<!-- footer -->
+<footer id="footer">
+ <!--
+ <div id="footer-nav">
+ <div class="container">
+ </div>
+ </div>
+ -->
+ <div id="footer-bar">
+ <div class="container">
+ <div class="footer-info">
+ <table class="inline">
+ <tr>
+ <td class="logo"><img alt="Empire-db" src="../res/empire-db-sg.png" /></td>
+ <td class="info">
+ <span>Copyright © The Apache Software Foundation. All Rights Reserved.</span>
+ <br/>
+ <span>Apache Empire-db, Apache, the Apache feather logo, and the Apache Empire-db logos are trademarks of The Apache Software Foundation.</span>
+ </td>
+ </tr>
+ </table>
+ </div>
+ </div>
+ </div>
+</footer>
+<!-- goto-top -->
+<a id="goto-top" href="#top"><span class="icon"></span></a>
+</div>
+<!-- #BeginEditable "page setup" -->
+<script type="text/javascript">
+ window.onload = function () {
+ Page.init("mi.project");
+ }
+</script>
+<!-- #EndEditable -->
+</body>
+<!-- #EndTemplate -->
+</html>
Propchange: empire-db/site/pages/project.html
------------------------------------------------------------------------------
svn:mime-type = text/plain
Added: empire-db/site/res/apache-small.png
URL: http://svn.apache.org/viewvc/empire-db/site/res/apache-small.png?rev=1898936&view=auto
==============================================================================
Binary file - no diff available.
Propchange: empire-db/site/res/apache-small.png
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
Added: empire-db/site/res/empire-db-lg.png
URL: http://svn.apache.org/viewvc/empire-db/site/res/empire-db-lg.png?rev=1898936&view=auto
==============================================================================
Binary file - no diff available.
Propchange: empire-db/site/res/empire-db-lg.png
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
Added: empire-db/site/res/empire-db-logo-b.png
URL: http://svn.apache.org/viewvc/empire-db/site/res/empire-db-logo-b.png?rev=1898936&view=auto
==============================================================================
Binary file - no diff available.
Propchange: empire-db/site/res/empire-db-logo-b.png
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
Added: empire-db/site/res/empire-db-sg.png
URL: http://svn.apache.org/viewvc/empire-db/site/res/empire-db-sg.png?rev=1898936&view=auto
==============================================================================
Binary file - no diff available.
Propchange: empire-db/site/res/empire-db-sg.png
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
Added: empire-db/site/res/feather.png
URL: http://svn.apache.org/viewvc/empire-db/site/res/feather.png?rev=1898936&view=auto
==============================================================================
Binary file - no diff available.
Propchange: empire-db/site/res/feather.png
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
Added: empire-db/site/res/gototop.png
URL: http://svn.apache.org/viewvc/empire-db/site/res/gototop.png?rev=1898936&view=auto
==============================================================================
Binary file - no diff available.
Propchange: empire-db/site/res/gototop.png
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream
Added: empire-db/site/res/key.gif
URL: http://svn.apache.org/viewvc/empire-db/site/res/key.gif?rev=1898936&view=auto
==============================================================================
Binary file - no diff available.
Propchange: empire-db/site/res/key.gif
------------------------------------------------------------------------------
svn:mime-type = application/octet-stream