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
