# Db

**DbOom** provides an efficient, powerful and thin layer over JDBC that significantly simplifies writing of database code. Using pure JDBC api correctly requires writing the same code snippets over and over again, what easily leads to unmaintainable application. Often there are run-time problems just because of incorrect database handling. *DbOom* introduces several smart façades that helps in writing smaller, cleaner and maintainable code.

Moreover, *DbOom* is the mapper between object and relational world using plain SQL queries. It defines object-table mappings using annotations or naming convention. However, relationships are not pre-defined, they are set on very place where used. The best way how to think of *DbOom* is from the JDBC perspective: it is not a full-blown complex ORM library; instead, it is just a nice tool built over *Db* for efficient database mapping.

### Values

* Significantly simplified JDBC.
* Enhanced statements, named parameters.
* Throws unchecked exceptions.
* Fast, no performance loss, no \*QL parsing.
* Debugging mode where all statement '?' are replaced with values.
* Plain-old SQL is used, not proprietary \*QL.
* Easy to learn and understand.
* Annotation-based mapping (optional).
* Mapping to types
* Mapping 1-1 and 1-many relations (on-the-fly)
* Template-SQL for queries aware of entities
* Database auto-detection


# DbQuery

`DbQuery` is an enhanced wrapper for prepared and regular JDBC statements. In the base scenario, it can be used anywhere where JDBC statements would be used. Nevertheless, `DbQuery` provides some additional very convenient features.

### Basic usage

The basic way how `DbQuery` can be created is by providing database connection. Once created, it can be used similarly as JDBC statement is used:

```java
    DbQuery query = new DbQuery(connection, "create table ...");
    query.executeUpdate();
    query.close();              // or just: query.autoClose().executeUpdate();
    ...
    query = new DbQuery(connection, "select * from ....");
    query.setString(1, "param1");
    ResultSet rs = query.execute();
    ...
    query.closeResultSet(rs);   // not needed, but still nice to have
    query.close();
```

Method `autoClose()` enables 'auto-mode' when the very first next action closes the query. Besides displayed methods, there is method: `executeCount()` that is made for executing `select count` database queries, or any query that returns a `long` number in the first result row and column.

Closing queries is important. Fortunately, *DbOom* allows user to invoke `close()` method, and all the dirty work is done in the behind. When a query created some `ResultSet`, it is possible to explicitly close it using `closeResultSet()` method. However, this is not mandatory! User may just simply close a query, and the `DbQuery` will close all results set that were created by it! As will be shown later, it is even possible to have automatic query closing:)

### Named parameters

Prepared JDBC statement has only ordinal parameters. For long and dynamic SQL queries, setting ordinal parameters may be tricky, and user has to be unnecessary careful. Besides ordinal parameters, `DbQuery` offers named parameters as well.

```java
    DbQuery query = new DbQuery(connection,
        "select * from FOO where id=:id and name=:name");
    query.setLong("id", id);
    query.setString("name", "john");
    ResultSet rs = query.execute();
    ...
    query.close();
```

Named and ordinal parameters may mix in one query, although that is is not a good practice.

### Debug mode

When printing JDBC prepared statements, all parameters are represented with a question mark and not real values. This makes things difficult for debugging. `DbQuery` offers the *debug mode* that will return the same query string, but populated with real values. Such debug query is just a quick-and-dirty preview and not always 100% syntaxly correct (e.g. strings are not escaped, etc), but sufficient for debugging purposes.

```java
    DbQuery query = new DbQuery(connection,
        "select * from FOO where id=:id and name=:name");
    query.setDebugMode();          // must be called before setting parameters
    query.setLong("id", id);
    query.setLong("name", "jodd");

    System.out.println(query);
```

Here is the difference that debug mode makes:

```sql
    select * from FOO where id=? and name=?
    select * from FOO where id=173 and name='jodd'     -- debug mode
```

### Configuration & Lazy initialization

`DbQuery` initializes lazy. Creating an object still doesn't do anything with the database, therefore it can be configured as needed. `DbQuery` initializes on first concrete database-related method. Therefore, setting the debug mode (and other configuration) must be done immediately after the `DbQuery`object creation.

### Parameters setters

All prepared statement setting methods are implemented in `DbQuery`. As said, each method now has two versions: one that works with ordinal parameters and one for named parameters. Moreover, during setting of a parameter, value will be checked, and if it is `null`, the `setNull`() method will be invoked instead.

There are some new methods for setting parameter values, such as: `setBean()`, `setMap()`, `setObject()`, `setObjects()`...

With `setBean()` it is possible to populate query string where parameters are named as bean properties:

```java
    DbQuery query = new DbQuery(connection,
        "select * from FOO f where f.ID=:foo.id and f.NAME=:foo.names[0]");
    query.setBean("foo", Foo);
```

### SqlTypeManager and setObject()

`DbQuery` provides new method `setObject()` for setting objects of unknown type as parameters. For that purpose, `DbQuery` must resolve the way how to handle provided type and to invoke correct setter method.

*DbOom* has one central point for resolving SQL types from object types: `SqlTypeManager`, manager for all kind of different `SqlType`s. Each `SqlType` defines how a type is set and get from the database. There is a large amount of already defined types, however, it is easy to add new and more complex ones.

### Auto-generated columns

`DbQuery` supports auto-generated columns:

```java
    // Example #1:
    DbOomQuery q = new DbOomQuery(connection,
            "insert into FOO(Data) values('data')");
    q.setGeneratedColumns();            // indicate some auto-generated columns
    q.executeUpdate();

    // get the first auto-genereted column, i.e. usually ID
    long key = q.getGeneratedKey();
    q.close();
```

```java
    // Example #2:
    DbOomQuery q = new DbOomQuery(connection,
            "insert into FOO(Data) values('data')");
    q.setGeneratedColumns("ID");        // indicate auto-generated column
    q.executeUpdate();
    ResultSet rs = q.getGeneratedColumns();
    ...
    q.closeResultSet(rs);
    q.close();
```

You could also use `q.setGeneratedKey()` instead of `q.setGeneratedColumns()` in the first example, if that sounds better to you :) Please note that some old database drivers does not support this feature (like HSQLDB 1.x).

### Stored Procedures

`DbQuery` supports calling stored procedures. The result of the stored procedure is encapsulated in `DbCallResult`.

```java
    DbQuery query = new DbQuery(connection, "{ :upp = call upper( :str ) }");
    query.setDebugMode();
    query.setString("str", "some lowercase value");
    query.outString("upp");

    DbCallResult callResult = query.executeCall();
    // now work with result from stored procedure via DbCallResult
    String str = callResult.getString("upp")

    query.close();
```


# DbSession

`DbSession` encapsulates a database connection. It also plays nicely with `DbQuery`-ies and has some convenient features.

### Connection providers

Connection provider is an object that provides connection to database when requested; and release one when not needed anymore. It encapsulates real mechanism how the database connection is actually retrieved and released.

*DbOom* offers several `ConnectionProvider` implementations: using `DataSource`, `DriverManager`, `XADataSource` or `ConnectionPoolDataSource`. More, *DbOom* has its own connection pool implementation, `CoreConnectionPool`, that works quite nicely.

### Basic usage

`DbSession` uses `ConnectionProvider` for getting the actual database connections. Once created, `DbSession` are used for creating `DbQuery`-ies. `DbSession` takes care of created `DbQuery` instances during its session and closes all resources at the end: all queries and therefore all created result sets. At the end, `DbSession`returns connection back to `ConnectionProvider`. Here is an example of basic `DbSession` usage:

```java
    DbSession session = new DbSession(connectionProvider);
    ...
    session.beginTransaction();
    DbQuery query = new DbQuery(session, "insert into...");
    query.executeUpdate(true);      // 'true' -> query closes after execution
    session.commitTransaction();
    ....
    query2 = new DbQuery(session, "select * from... ");
    ResultSet rs = query2.execute();
    ....
    session.close();                // only session is explicitly closed :)
```

In above example only `DbSession` is explicitly closed. As said, `DbSession` keeps track of all created `DbQuery`-ies. On session closing, all open queries will be implicitly closed; therefore all created and still open ResultSet's will be closed. Even this is nice feature, some may like more to explicitly close each resource - with *Db* this is just matter of couple of lines anyway.

### DbThreadSession

`DbSession` is open for extension. One such extension already exists: `DbThreadSession`. Upon creation, it assigns created session to the current thread. From there, it is possible to retrieve the current session in any other part or layer of the application, without the need to carry it on through method arguments or any other way. This might be useful when one session (i.e. connection) is used per single thread, through application layers.

```java
    // create session and assign it to the thread
    DbSession session = new DbThreadSession(connectionProvider);
    ...
    ...// some layers in between
    ...
    // retrieve session from thread
    DbSession session = DbThreadSession.getCurrentSession();
    DbQuery query = new DbQuery(session, "select...");
    ...
    ...// going back
    ...
    session.close();        // close the session and remove it from thread storage
```

### DbSessionProvider

Above code that works with `DbQuery` suffers from following issue: it has strong dependency on concrete `DbSession` implementation! The goal would be to loose coupling between `DbQuery` and `DbThreadSession` on the place where `DbQuery` is used. *DbOom* has solution for this problem, too.

`DbSessionProvider` implementation is responsible for returning `DbSession` inside some context (thread, request...). This may be a new session or existing one. It is possible to register default `DbSessionProvider` implementation, so no `DbSession` has to be specified when creating new `DbQuery`. `ThreadDbSessionProvider` is default session provider and it manages sessions inside a thread. Above code may be re-written like this:

```java
    // create session and assign it to the thread
    DbSession session = new DbThreadSession(connectionProvider);
    ...
    ...// some layers in between
    ...
    DbQuery query = new DbQuery("select...");    // no session reference is needed
    ...
    ...// going back
    ...
    session.close();        // close the session and remove it from thread storage
```

When `DbQuery` is created without provided session or connection argument, it uses default session provider, which is, by default, `ThreadDbSessionProvider`. This provider returns assigned session from current thread. If no session is assigned, exception is thrown.

`DbSessionProvider` implementation does not control `DbSession` lifecycle! {: .attn}

It is very important to understand that `DbSessionProvider` does not controls the `DbSession` - it does not open or close one. So database session should be created manually before usage; and then assigned or connect somehow to the `DbSessionProvider` instance; also it has to be closed manually after the usage.

### Transactions

`DbSession` works with transactions in expected way.

```java
    session.beginTransaction(
        new DbTransactionMode().isolationNone().setReadOnly(true));
    try {
        DbQuery query = new DbQuery(session, "insert into...");
        // 'true' means that query will be closed after execution
        query.executeUpdate(true);
        session.commitTransaction();
    } catch(DbSqlException dbsex) {
        session.rollbackTransaction();
    }
    System.out.println(session.isTransactionActive());
```

The last row prints `false`, since transaction is not active anymore. When a session is not under transaction, it is in the auto-commit mode.

This is just basic transaction usage, *Jodd* offers more complex transaction management, using also propagations.


# DbOom

Up to now, *DbOom* was used as a convenient replacement for JDBC. *DbOom* has much more to offer!

The goal behind *DbOom* is *not* to have an ORM tool - there are plenty solutions like that out there. Instead, *DbOom* is a thin Object-Mapping layer. Relations are not pre-defined, but defined when actually used: before the query execution; or not defined at all and set manually. Moreover, there is *no* generic QL language that works across databases; instead you use the full power of native SQL of specific database that is in use. That does not mean that you can't address *entities* in your queries! With *DbOom* you can use entity names and properties, and they will be converted into the database tables and columns.

### DbOom per database

*DbOom* works with as many databases as you need. Since using a single relational database is a common for projects (at least for projects that uses *Jodd* :) - *DbOom* considers this as a special case and offers many helpful shortcuts for his single-database usage (more later).

The central place in *DbOom* is... a `DbOom` instance (who would guess :). You will have one instance per database. `DbOom` recognizes the single-database usage, making available various shortcuts; mainly preventing you to carry on the instance of `DbOom`.

`DbOom` offers fluent builder to construct itself.

```java
    DbOom dbOom = DbOom.create().get();
    dbOom.connect();
```

or:

```java
    DbOom.create()
        .withSessionProvider(mySessionProvier)
        .withConnectionProvider(connectionPool)
        .get()
        .connect();
```

Once connected to database, `DbOom` detects the database vendor and applies some default naming convention.

### DbOom components

`DbOom` provides access to following components:

* `DbOomConfig` - configuration, mainly naming conventions,
* `DbQueryConfig` - query-related configuration,
* `DbEntityManager` - manager of entity mappings,
* `DbSessionProvider` - session provider,
* `ConnectionProvider` - connection provider
* `QueryMap` - map of named queries.

### DbOom factories

`DbOom` also is a factory for all *DbOom* working tools:

* `entities()` - returns `DbEntitySql` factory,
* `sql()` - creates new `DbSqlBuilder`,
* `query()` - creates new `DbOomQuery()`.

In single-database mode, most of these can be used without referencing `DbOom`. {: .attn}

The following pages describes all *DbOom* components and tools assuming the single-database mode; for the sake of simplicity.

### Single-database mode

As said, having a single database is a special use case for `DbOom`. Once when created, the instance of `DbOom` can be accessed using the following:

```java
    DbOom dbOom = DbOom.get();
```

The `get()` method will throw exception if multiple databases are in use, or none.

Furthermore, all *DbOom* working tools (classes that are used for quering, mapping etc) have a constructor with `DbOom` as an argument. In single-database mode, you can use static constructor methods, that does not take `DbOom` instance. For example, this usage:

```java
    DbOomQuery q = new DbOomQuery(dbOom, dbSession, "select * from ...");
```

can be replaced with:

```java
    DbOomQuery q = DbOomQuery.query(dbSession, "select * from ...");
```

Finally, you can use the `DbOom` instance directly instead, no matter which mode is in use:

```java
    dbOom.query("select * from ...");
```

The following documentation will assume the single-database mode, just for the sake of simplicity.


# DbOomQuery

**DbOomQuery** is all about convenient mapping of result set to target classes. `DbOomQuery` extends `DbQuery` by adding methods for mapping result sets to objects.

There are two ways how object is mapped to database table. The first way is by following naming *conventions*. `DbOomQuery` will try to map result set columns to objects as best as possible. Second way is by using *annotations* on domain objects, i.e. explicit markup, where no specific naming convention has to be followed. It is possible to mix both and perform mappings in both ways. Anyhow usage of `DbOomQuery` is absolutely identical in both cases.

### find()

`DbOomQuery.find()` is used to find single set of objects from database, i.e. to find exactly one row and to map it to some set of objects.

```java
    DbOomQuery q = DbOomQuery.query(session,
            "select * from GIRL join BOY on... where...");
    Object[] girlAndBoy = (Object[]) q.find(Girl.class, Boy.class);
    Girl girl = (Girl) girlAndBoy[0];
    Boy boy = (Boy) girlAndBoy[1];
    girl.setBoy(boy);        // if there is such dependency
```

The join between two tables is mapped to two, explicitly specified, classes. Since `DbOomQuery` is not aware of relationships, `boy` instance would be not injected into the `girl`. Here this is done manually (line #5).

### How mapping works

Mapping process is the core of *DbOom* and it is important to understand how *result-set-to-object* mapper works. It matches both table and column names with provided array of bean classes and its properties. Mapper reads meta-data from result set and then maps columns of same table to its properties of corresponding class. This is repeated for all remaining columns (and tables).

Some JDBC drivers doesn't provide table name within result set meta-data. When table name is not available, mapper tries to to best possible job: it tries to map columns to one class by matching just column names and bean properties. While everything is OK (i.e. while there is a bean property that matches result set columns), mapper continues using the same bean object. If mapping fails i.e. if some column name is not founded among bean properties, mapper takes the next bean class and repeats the procedure. This approach has some sharp usage edge, but they can be easily avoided.

To summarize: *DbOom* mapping uses a type a long as it could. {: .attn}

*DbOom* offers a solution when table names are not available in JDBC meta-data, that will be explained later.

As said, mapping works with bean classes, i.e. domain objects. Moreover, mapper recognizes Java and *Jodd* number classes (configurable) as well and they are mapped to one single column:

```java
    q.find(
        Integer.class, Girl.class, Long.class,
        Boy.class, Float.class, String.class);
```

Mapping functionality of *DbOom* is modular and may be easily replaced with custom implementation.

### Find single type

When result set is mapped to a single type, `find()` returns an `Object` and not `Object` array. The following example has no casting at all:

```java
    DbOomQuery q = DbOomQuery.query(session, "select * from GIRL ... where...");
    Girl girl = q.find(Girl.class);
```

### list(), listSet()...

`DbOomQuery` has also methods for retrieving all records from the result set. They are returned as list or set of object arrays.

```java
    DbOomQuery q = DbOomQuery.query(session,
            "select * from GIRL join BOY on... where...");
    List<Object[]> girlsAndBoys = q.list(Girl.class, Boy.class);
    Set<Object[]> girlsAndBoysSet = q.listSet(Girl.class, Boy.class);
    List<Girl> girls = q.list(Girl.class);
```

### Iterator

Sometimes fetching and instantiating of whole result set may be time and memory consuming. In such cases it is possible to iterate over result set using `iterate()` method.

### Annotations

It is possible to annotate entity objects to specify table and column names that will be used during mapping process. This is done using two annotations, `@DbTable` and `@DbColumn`:

```java
    @DbTable("BOY")
    public class BadBoy {

        @DbColumn("ID")
        Integer ajdi;

        @DbColumn
        String name;
        ...
    }
```

This example breaks naming convention on some properties, therefore class and fields are marked to specify the table and column names. If value element of an annotation is not given, name will be generated from the class/field name.

Annotations `@DbTable` and `@DbColumn` just define table (or view) and column names. Think of `@DbTable` like a set of ResultSet columns that applies to one bean.

Using annotations is the preferred way for working with *DbOom*, but not the only one. {: .attn}

### Join hints: 1-1 relations

When selecting a join of entities, `DbOomQuery` by default maps the result into the array of objects that are not connected anyhow. For example, the query `"select * from GIRL join BOY..."` will return for each row an array of two elements: `Girl` and `Boy` instances. In order to put `Girl` instance into a `Boy`, user has to do that manually in the code.

`DbOomQuery` offers a simple way how to join resulting instances. To specify what to join, user has to provide so-called *join hints*. Join hint is a simple name of entities and their properties in the context of the query. Here is an example:

```java
    DbOomQuery q = DbOomQuery.query(session,
        "select girl.*, boy.* from GIRL girl join BOY boy on girl.ID=boy.GIRL_ID");
    List<Boy> boys = q.withHints("boy.girl", "boy").list(Girl.class, Boy.class);
```

Every row of the query results will be mapped to two beans: `Girl` and `Boy`. Using provided hints, we define that `Girl` instances should be injected into the `Boy` instances, for every row. Here, `Boy` entity instance is named as `boy`. `Girl` entity instance is named as `boy.girl`, indicating that `Girl` instance should be injected into the `boy.girl` property.

Hint is the name/path of bean in the context of query result. With hints you can organize resulting entities inject one into another. Hints order is important! {: .attn}

With hints it is possible to solve 1-1 relationships. Obviously, it works for uni-directional relationships; bidirectional support should be developed in Java, in setter method.


# Mapping

When it comes to mapping, *DbOom* tries its best to match database types with Java types of POJO properties (i.e. mapped columns). *DbOom* knows how to convert between various SQL types and common Java types, including `enums`. SQL types in *DbOom* are actually implementations of `SqlType`, that defines how to convert values between SQL and Java types.

### Custom Mapping

It is possible to define custom SQL types, i.e. custom type mappings. They can be defined in two ways:

* *globally*: custom `SqlType` implementation is registered in `SqlTypeManager`. Such SQL types are available all across the application.
* *locally*: defined in `@DbColumn` annotation by setting `sqlType` element, this custom type applies only on annotated property.

SQL types defined in annotation are always used, even if java type of a property has its own SQL type already registered.

### Naming strategies

For successful mapping and *DbOom* functionality, table and column naming strategies must match how used database works.

This is very important to understand! *DbOom* has table and column name naming strategies that define how entity/column names are converted *to* and *from* the mapped class/property names. Notice that these naming strategies are used in **both** directions: when converting from table/column name to class/property and vice-versa (i.e. *mapping*); and when converting from class/property to table/column name (i.e. *resolving*).

For example, if you have a table `JJ_FOO_BAR` (prefix and uppercase) and column `value_data` (lowercase), it may be mapped to class `FooBar` (camel-case strategy) and property `valueData` (again, camel-case). The opposite mapping also has to match: class `UserData` may be resolved to e.g. table `EX_USER_DATA_N` (uppercase with both prefix and suffix); property `valueData` may be resolved to column `value_date` (lowercase).

Here are the possible naming strategies options (defined in `DbOomManager`):

* `splitCamelCase` - if camel case words should be split with `separatorChar`.
* `separatorChar` - simple char used when `splitCamelCase` is `true`, by default its `_`
* `changeCase` - when `true` (default) table or column names will be changed to upper or lowercase.
* `uppercase` or `lowercase` - defines it table or column name should be converted to upper case or lowercase.
* `prefix` and `suffix` - table names may have prefix and/or a suffix.

Why naming is important? Naming strategies must match how target database works. Wrong naming strategy is the most common configuration mistake when using *DbOom*! {: .attn}

Therefore, when working with *DbOom*, please use uniform naming convention across the whole database and please match it with how JDBC drivers work! One thing that can help is to enable logging. If you see WARN message like this:

```
 [WARN] Column SQL type not available: DbEntity: TESTER2.TIME
```

then it is a sign that mapping or naming conventions might be wrong.

### Database auto-detection

Upon start, `DbOom` connects to the database and detects the vendor. Depending of the used database, `DbOom` should set correct naming conventions.

### Mapping Example

| \~\~\~\~\~ java @DbTable public class Foo { @DbId public long id; @DbColumn public MutableInteger number; @DbColumn( sqlType = IntegerSqlType.class) public String string; @DbColumn public String string2; @DbColumn public Boo boo; @DbColumn public FooColor color; @DbColumn( sqlType = FooWeigthSqlType.class) public FooWeight weight; @DbColumn public Timestamp timestamp; @DbColumn public Clob clob; @DbColumn public Blob blob; @DbColumn public BigDecimal decimal; @DbColumn public BigDecimal decimal2; @DbColumn public LocalDateTime jdt1; @DbColumn public LocalDateTime jdt2; } \~\~\~\~\~ | \~\~\~\~\~ sql create table FOO ( ID integer not null, NUMBER integer not null, STRING integer not null, STRING2 integer not null, BOO integer not null, COLOR varchar not null, WEIGHT integer not null, TIMESTAMP timestamp not null, CLOB longvarchar not null, BLOB longvarbinary not null, DECIMAL decimal not null, DECIMAL2 varchar not null, JDT1 bigint not null, JDT2 varchar not null, primary key (ID) ) \~\~\~\~\~ |
| ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |

Most of above mappings are straightforward: number fields are mapped to number java types, varchars to strings, etc. There are some useful additional mappings, like mapping `String` values to integer columns - of course, it is assumed that string contains only digits. In this example you can see two explicit local mapping, when SQL type is defined in `@DbColumn` annotation.

#### Custom mappings

Now something interesting: property `boo` has a custom type `Boo`, and it is also mapped to database. Of course, this mapping can't be done automatically. We must provide custom `SqlType` that explains how to convert database value to and from `Boo` type. Since we want to use this mapping everywhere, we might register it globally:

```java
    SqlTypeManager.get().register(Boo.class, BooSqlType.class);
```

and `BooSqlType` may look like:

```java
    public class BooSqlType extends SqlType<Boo> {

        @Override
        public void set(PreparedStatement st, int index, Boo value)
                throws SQLException {
            st.setInt(index, value.value);
        }

        @Override
        public Boo get(ResultSet rs, int index) throws SQLException {
            Boo boo = new Boo();
            boo.value = rs.getInt(index);
            return boo;
        }
    }
```

In this simple example, `Boo` is stored as an integer in database; however, you can create a more complex SQL type and conversion.

#### Enum mappings

Lets see how simple enumeration (`FooColor`) can be stored to database. Enumerations, by default, are stored as strings (varchars...).

Now, enumeration may be stored as other SQL type, but it is necessary to define custom `SqlType` for mapping conversion. One such implementation may look like:

```java
    public class FooWeigthSqlType extends SqlType<FooWeight> {

        @Override
        public void set(PreparedStatement st, int index, FooWeight value)
                throws SQLException {
            st.setInt(index, value.getValue());
        }

        @Override
        public FooWeight get(ResultSet rs, int index) throws SQLException {
            return FooWeight.valueOf(rs.getInt(index));
        }
    }
```

If you have enumerations that are mapped to an integer, you don't even have to write custom SQL types! So above `SqlType` is NOT needed if you design your enumeration like this:

```java
    public enum Status {
        PENDING(0),
        ACTIVE(1),
        COMPLETED(99);

        final int status;
        final String statusString;

        private Status(int status) {
            this.status = status;
            this.statusString = String.valueOf(status);
        }

        public int value() {
            return status;
        }

        @Override
        public String toString() {
            return statusString;
        }
    }
```

The key thing here is `toString()` that returns int value as a `String`. When you map such enum to a column of some int type, everything will work out of box! This is because of behavior of *BeanUtil* tool. Note that we have cached int value for better performances, to avoid string conversion on every access.

#### Other mappings

Other mappings from the example are also straightforward. It is interesting to notice that `LocalDateTime` is stored as number of milliseconds (compatible with `System.currentTimeMillis()`).


# DbOomManager

### Types registration

Anytime when some types are specified in `DbEntityManager` methods, they are examined and parsed and these results are stored internally in `DbEntityManager`. This kind of registration is called *types registration* since only class information is registered. This means that more then one entity types may be mapped to single database table. In other words, this is one way mapping:

**Entity → Table, Entity name**

### Entity registration

To use the full potential of *DbOom*, all entity classes should be registered as *entities* in the `DbEntityManager` before the usage. Entity registration supersede the type registration: types and tables now becomes mapped in both ways:

**Entity ↔ Table, Entity name**

Now each table name is also uniquely registered with one and only one entity. While it is still possible to register more types to tables, it is not possible to register one table name to more then one entities. `DbOomQuery` will throw exception if that happens.

### Automatic registration

Since entities must be registered manually, there is nice way how all annotated classes on class path can be registered automatically. This is done by using `AutomagicDbOomConfigurator`. It scans the class path and jar files (or part of it, as specified by user) and finds all classes annotated with `@DbTable`. No class is loaded in class loader unless it contains correct bytecode.

`AutomagicDbOomConfigurator` offers both way of registration.

### Resolving entities

One of the benefits of entity registration is the feature of resolving the entities just from the result set meta data. In all `DbOomQuery` examples, each method accepts explicit list of (entity) classes to which the result set will be mapped. Now it is possible to omit the list and let `DbOomQuery` resolve classes by itself:

```java
    DbOomQuery q = DbOomQuery.query(session,
        "select * from GIRL join BOY on... where...");
    Girl girl = q.findOne(Girl.class);  // ok
    Girl girl = (Girl) q.findOne();     // throws an exception

    dbOom.entityManager().registerEntity(Girl.class);
    Girl girl = (Girl) q.findOne();     // now it works
```

To some this feature is useful, others doesn't use since it is not so visible what is the target entity.


# Template SQL (T-SQL)

Native SQL contains table and column names. As *DbOom* is an object mapper, it would be more natural to use entity and property names instead.

Template SQL is a SQL-alike query string with an add-on: *the macros*. Macros allow usage of entity and property names instead of tables and columns names.

Example:

```sql
    select $C{bb.*}, $C{bg.+}
        from $T{BadGirl bg} join $T{Boy bb} on $bg.+=bb.girlId
```

Result:

```sql
    select bb.GIRL_ID, bb.ID, bb.NAME, bg.ID
        from GIRL bg join BOY bb on bg.ID=bb.GIRL_ID
```

### Table macro $T

Table macro converts entity names into table names. Optionally it may define table alias for further reference, otherwise entity name is used instead. One table macro may define more tables definitions separated by comma.

Usages:

* `$T{<entity> [<alias>]}`

Example:

```sql
    select * from $T{Foo f}
```

Result:

```sql
    select * from FOO f
```

### Columns macro $C

Columns macro renders property name into single column, all columns or id column of a table (usually used for `select` queries). It also support generation of column aliases that are later used as hints in mapping result sets into objects.

Usages:

* `$C{<entity>.<property>}` - renders single column
* `$C{<entry>}` or `$C{<entry>.*}` - renders all table columns, ordered

  by name.
* `$C{<entry>.+}` - renders id column of a table.
* `$C{<hint>:<entity>...}` - defines a hint.
* `$C{.<columnName>}` - defines a column name as is (not a property).

Examples:

```sql
    select $C{f.bar} from $T{Foo f}
```

Result:

```sql
    select f.BAR from FOO f
```

Example that renders all columns using a joker sign `*`:

```sql
    select $C{f.*} from $T{Foo f}
```

Result:

```sql
    select f.BAR, f.ID, f.ZAP from FOO f
```

Example that renders id column using joker sign `+`':

```sql
    select $C{f.+} from $T{Foo f}
```

Result:

```sql
    select f.ID from FOO f
```

Column aliases can be generated in several ways. First, column alias may contain table name (`TABLE_NAME`).

```sql
    select $C{f.bar} from $T{Foo f}
```

Result:

```sql
    select f.BAR as FOO$BAR from FOO f
```

Example for generating column names using table references (i.e. entity names, `TABLE_REFERENCE`)

```sql
    select $C{f.bar} from $T{Foo f}
```

Result:

```sql
    select f.BAR as Foo$BAR from FOO f
```

Finally, there is a third variant, the most safe one, using column code, a generated name (`COLUMN_CODE`). Useful when table names are big and together with column name exceed max allowed column alias name.

```sql
    select $C{f.bar} from $T{Foo f}
```

Result:

```sql
    select f.BAR as col_0 from FOO f
```

#### Special case

When entity is not a table reference, then `$C` macro renders just alias name.

### Reference macro $

Reference macro renders simply columns names from properties. Used in `where` part of the sql query.

Usages:

* `$<entity>.<property>` - renders mapped column
* `$<entity>.+` - renders id column
* `$<entity>` - renders table name
* `$.<property>` - renders just column name

Examples:

```sql
    select $C{f.bar} from $T{Foo f} where $f.zap=173
```

Result:

```sql
    select f.BAR from FOO f where f.ZIP=173
```

If table alias is not used, reference will render column using table name:

```sql
    select $C{Foo.bar} from $T{Foo} where $Foo.zap=173
```

Result:

```sql
    select FOO.BAR from FOO where FOO.ZIP=173
```

### Match macro $M

When using templates it is often needed to provide some additional data, e.g. values that are references in the templated query. For example, for `$M` macro, reference value must be assigned to the template. This is done using method `use()`.

```java
    Boy boy = new Boy();
    boy.id = 1;
    boy.girlId = 3;
    DbSqlBuilder s =
        sql("select * from $T{boy} where $M{boy=boy}").use("boy", boy);
```

Result:

```sql
    select * from BOY boy where (boy.GIRL_ID=:boy.girlId and boy.ID=:boy.id)
```

Here a value reference `boy` is named as table reference. This is not a good practice, and here is done line that to show the difference between table references and value references (added with `use()`)

### Simple join hints

With sql templates it is even more easier to specify joining hints:

```java
    // Standard way:
    q = DbOomQuery.query(session, sql(
        "select $C{girl.*}, $C{boy.*} from $T{Girl girl} " +
        "join $T{Boy boy} on girl.id=$boy.girlId"));
    boy = (Boy) q.withHints("boy.girl, boy").find(Girl.class, Boy.class);
```

```java
    // Inline way:
    q = DbOomQuery.query(sql(
        // hint inside column name
        "select $C{boy.girl.*}, $C{boy.*} from $T{Girl girl} " +
        "join $T{Boy boy} on girl.id=$boy.girlId"));
    boy = (Boy) q.find(Girl.class, Boy.class);
```

This is clean and visible way for specifying hints, without extra method call.

When using join hints in T-SQL, simple convention has to be followed: table reference name (`girl` in above example) used in the hint should be equal to property name of a target entity (e.g. property `boy.girl` must exist). {: .attn}

If target property name is not the same as reference name, you can specify it like this: `$C{boy.girlAlt:girl.*}`.

For more powerful hints configuration, use `withHints()` method.

### ParsedSql

You can gain some performance by parsing the template query only once.

```java
    ParsedSql q1 = sql("select ....").parse();
```

If you store the value of `ParsedSql`, you can easily re-use it in your methods, by passing it to the constructor of `DbOomQuery`. Just be sure that parsing is done *after* the *DbOom* initialization!

### DbEntitySql

With `DbSqlBuilder` engine it is possible to create high-level factories that can simplify database usage. One such factory already exist: `DbEntitySql`. Here are some usage examples:

```java
    DbOomQuery.query(session,
        dbOom.entities().insert(new Girl(...)).executeUpdate();
    // more fluent
    dbOom.entities().insert(new Girl(...)).
        query(session).executeUpdate();

    Girl girl = ...
    dbOom.entities()..find(girl);
    dbOom.entities()..findById(girl);    // find entity only by id,
                                        // other properties are ignored
    dbOom.entities()..deleteById(girl);  // deletes by id
```

Of course, it is possible to create even higher level of encapsulation, but this not something what library such this should provide.


# Relations & Hints

We have already seen how to use *hints* to inject values into resulting objects. Lets analyze this topic more and see how to deal with the **one-to-one** and **one-to-many** relations efficiently.

### Problem

Here is the real-life problem. Lets say that we have a list of some telecommunication centers in database. Each center has one or more associated prefixes. Also, each center belongs to one country (i.e. region). We need to fetch all telecom data, as they do not change during the application runtime.

### Model

Model is no-brainer.

```java
    @DbTable
    public class Telecom extends Entity {
        @DbId
        private long telecomId;
        @DbColumn
        private String name;
        @DbColumn
        private long countryId;
        ...
    }
```

```java
    @DbTable
    public class TelecomPrefix extends Entity {
        @DbId
        protected long prefix;
        @DbColumn
        protected long telecomId;
        ...
    }
```

```java
    @DbTable
    public class Country extends Entity {
        @DbId
        long countryId;
        @DbColumn
        String name;
        ...
    }
```

The rest of fields just hold various entity data. All methods are POJO setters and getters. Model objects, for now, are independent.

Note that each model object extends `Entity` class. This is not mandatory, but is considered as a good practice. `Entity` class usually contains implementation of `hashCode()` and `equals()` methods, based on the **primary key** of the class. Again, this is also not mandatory and some would consider the whole instance state then just primary keys. This is fine; using just primary keys is somewhat more pragmatic - and faster - if code is written with having that in mind.

### Lazy approach

Lazy approach is fetching data without joins, firing several database queries. In our example we would need to execute the following queries:

* fetch all `Telecom`s.
* For each `Telecom`, fetch list of its `TelecomPrefix`
* For each `Telecom`, fetch the belonging `Country`.

There is nothing special to mention here, each query and *DbOom* usage is simple.

### Entity relationships

Up to now, there was no dependencies between entities. Obviously, it make sense to have list of `TelecomPrefix`es inside of `Telecom`, as well as the single `Country`. Therefore, we can write something like this:

```java
    @DbTable
    public class Telecom extends Entity {
        ...
        protected TelecomPrefix[] prefixes;
        protected Country country;
        // get/set methods for above fields
        ...
    }
```

Instead of an array of `TelecomPrefix`es, we could use any `Collection`. {: .attn}

In the lazy approach, the whole *wiring* would be done manually, by developer. Once when he get list of `TelecomPrefix` entities for some `Telecom` he would need to manually convert it to an array and set it to target.

### Join

This is nice case when we can use a *join* of three tables to fetch all data in one call. So the code may look like this:

```java
    DbOomQuery q = query(sql(
            "select $C{t.*}, $C{tp.*}, $C{c.*} " +
            "from $T{Telecom t} join $T{TelecomPrefix tp} using ($.telecomId) " +
            "join $T{Country c} using ($.countryId)"));

    telecoms = q.list(Telecom.class, TelecomPrefix.class, Country.class);
```

Here we create join of three tables. Each result set row is mapped to an object array with three elements: telecom, telecom prefix and country. And finally, each such object array is stored in resulting list.

On the first sight, there are no improvements here: for each row, we still need to manually wire results.

### Hints

Hints to the rescue! As we explained, *hints* defines how to wire objects within the single row. We want to use hints to inject e.g. `Country` instance into the `Telecom` instance, in the single row. Here is how to do so:

```java
    DbOomQuery q = query(sql(
            "select $C{t.*}, $C{t.prefixes:tp.*}, $C{t.country:c.*} " +
            "from $T{Telecom t} join $T{TelecomPrefix tp} using ($.telecomId) " +
            "join $T{Country c} using ($.countryId)"));

    telecoms = q.list(Telecom.class, TelecomPrefix.class, Country.class);
```

The change is small, yet powerful! With hints we instruct to append all `TelecomPrefix` into the `Telecom.prefixes` property, as well to inject `Country` into the `Telecom.country`! Resulting list elements would have just **one** element, a `Telecom`, since all other mapped elements are injected into this instance.

While this perfectly works with **one-to-one** relationships, like with `Country`; there is a failure with **one-to-many** relationships, like with `TelecomPrefix`. This is because for the each row of result set, *DbOom* will create a **new** instance of `Telecome`. Hence, if a telecom contain two prefixes, it will be listed twice and each time telecom will link just one, different, telecom prefix!

### Cache entities

Fortunately, the problem is easy to solve: by enabling caching on query level (i.e. on result-set level). So this code:

```java
    DbOomQuery q = query(sql(
            "select $C{t.*}, $C{t.prefixes:tp.*}, $C{t.country:c.*} " +
            "from $T{Telecom t} join $T{TelecomPrefix tp} using ($.telecomId) " +
            "join $T{Country c} using ($.countryId)"));

    q.cacheEntities(true);
    telecoms = q.list(Telecom.class, TelecomPrefix.class, Country.class);
```

*DbOoom* now caches **all entities** during the execution of a query and re-uses existing instances if already exist! In our case this means that instead of creating several instances of `Telecom` for each its prefix, there will be just one instance, with many prefixes injected into it.

Using query cache increases memory usage. {: .attn}

There is just one thing to be aware of - the resulting list will still contain duplicated records (hey, it's the same with Hibernate:) The trivial way to fix this is to use a `Set` instead of `List`\\:

```java
    DbOomQuery q = query(sql(
            "select $C{t.*}, $C{t.prefixes:tp.*}, $C{t.country:c.*} " +
            "from $T{Telecom t} join $T{TelecomPrefix tp} using ($.telecomId) " +
            "join $T{Country c} using ($.countryId)"));

    q.cacheEntities(true);
    telecoms = q.listSet(Telecom.class, TelecomPrefix.class, Country.class);
```

What we have now is the set of unique entities, properly injected with related content.

### EntityAware mode

But using `Set` is not always an option. Can we have a `List`, but **without** duplicate entries? Sure! `DbOomQuery` supports so called "entity mode". It goes one step further from `cacheEntities`, so enabling the entity mode will also enable cached entities.

In entity aware mode, not only that objects are cached, but also they are compared to the previous result! The very same example from above:

```java
    DbOomQuery q = query(sql(
            "select $C{t.*}, $C{t.prefixes:tp.*}, $C{t.country:c.*} " +
            "from $T{Telecom t} join $T{TelecomPrefix tp} using ($.telecomId) " +
            "join $T{Country c} using ($.countryId)"));

    q.entityAwareMode(true);
    telecoms = q.list(Telecom.class, TelecomPrefix.class, Country.class);
```

will now return `List` **without** the duplicates! Just a nice object tree, ready to be used :)


# Configuration

Configuration of *Db* and *DbOom* frameworks is set in `DbOom`.

There are two things to remember when talking about *Db* configuration.

1. **Configure first!** Try to configure before use or register

   entities. That would significantly reduce number of errors.
2. **Be aware of JDBC driver varieties**! JDBC drivers behave

   differently. Some have implemented most of the specified methods and

   provides enough meta-data, others omit some informations. Therefore,

   be patient and learn what your database driver can do; and configure

   *DbOom* accordingly.

Configurations are located mostly in the following classes:

* `DbQueryConfig` - configuration related to queries.
* `DbOomConfig` - everything related mapping, naming conventions etc.

### Best practices

As said, every JDBC driver and database behaves differently. Here are some best practice you can use in your projects:

* Establish convention between entity names and tables names.
* Use strict matching, i.e. set correct letter case for table and column

  names.
* Bidirectional mapping between table and entity may not work because of

  missing meta-data in JDBC driver. Try using explicit conversion to

  types.
* Use code `columnAliasType` if needed.
* Use `$C{}` just for selected columns, and nothing else.
* Don't forget that letter case of tables is different when created

  from the code.

Very often (at the beginning of the project), *DbOom* is not working because of wrong letter case and mismatched conventions. Just experiment a bit until you set it right:) Once set, everything goes smoothly:)


# Inject Values Using Hints

Sometimes an entity contains a property that is not mapped to a simple column, but instead holds some value fetched from another (joined) table or a value generated by query. For example, such property can holds some count or the result of some math operation, or value from other table.

We will use example where property `SentMessage.content` should be populated with value of `Message.ctx` (string value). Here is the T-SQL query that joins two tables and selects everything what is needed:

```sql
    select $C{sm.*}, $C{msg.ctx} from $T{SentMessage sm}
    join ($T{Message msg}) on ($msg.id=$sm.refId) where ...
```

Since `msg.ctx` string is not part of `SentMessage`, we can only call `DbOomQuery#list(SentMessage.class, String.class)` to receive data. But, this would be painful! The returned result for above T-SQL query is `List<Object[]>`, and in order to populate each `SentMessage.content` we would need to:

1. create a new `List<SentMessage>`.
2. iterate returned list of `Object[]`.
3. for each list element, cast array elements to `SentMessage` and

   `String`.
4. manually set property `content` of iterated `SentMessage` instance

   with string value.
5. add such prepared `SentMessage` to the new list.

Not only that it is unnecessary complicated, but also some additional memory is allocated to hold both lists in the same time.

### Using hints

Fortunately, *DbOom* framework comes with *hints*;) As explained elsewhere, hints can be used to instruct how returned values should be injected one into another.

So what would be the injection hint in our example? Its easy: for each row, please inject `msg.ctx` into `sm.content`. In the other words, we can say the following: map first columns of returned result into `sm` (`SentMessage`) and the last column put into `sm.content`.

Here is how to specify this hint in Java:

```java
    DbOomQuery q = query(sql(
            "select $C{sm.*}, $C{msg.ctx} as content from $T{SentMessage sm} " +
            "join ($T{Message msg}) on ($msg.id=$sm.refId) where..."));
    // set parameters
    List<SentMessage> list =
        q.withHints("sm", "sm.content").
            list(SentMessage.class, String.class);
```

And that is all! Here is how to read this in English: each record from resultset map into `SentMessage` and `String`, and name the first instance as "`sm`", while the second value, the string value, inject into `SentMessage`instance.

There is just one caveat - column name (specified with \\'`AS`\\' keyword) must match the property name.

Also note that returned value is `List<SentMessage>`, where each `SentMessage` has populated `content` property; so everything is ready for you :)


# Mapping to a bean

There are cases when you have to write complex queries with some calculations, string manipulation, etc. when some result set columns are calculated and not simple table columns values. The question is how to map such columns using *DbOom* and template SQL.

One answer is simple: you can map such result set into simple types, like `Integer`, `String`, etc. classes. However, it would be more convenient if you can map such result into a bean.

To do that, you can create a view in database. However, there are some cases when you can't use views. Do not worry, *DbOom* can help you anyway. Just use `$C` template-sql macro as alias column name.

Let's focus on the following sql:

```sql
    select g.ID + 10, UCASE(g.NAME), g.* from GIRL g where g.ID=1
```

Two first columns are calculated ones. As said, we can map this result to: `Long.class, String.class, Girl.class`. But lets see how we can map first two columns into the some `Bean1` class, a pure POJO.

First thing is to annotate `Bean1` class with `@DbTable` and `@DbColumn` annotations, as it is a mapped bean.

Then, you can write above query using the following template-sql:

```sql
    select $g.id + 10 as $C{Bean1.sum}, UCASE($g.name) as $C{Bean1.bigName}, $C{g.*}
    from $T{Girl g}
    where $g.id=1
```

The key point here is to map columns using `$C` macro and the bean name.


# JTX overview

*Jodd* provides great, little, stand-alone transaction manager, *JTX*. It is a significant change in traditional thinking, since no (j2ee) application server is required; *JTX* may be used in any Java code.

*JTX* is built to be roughly similar to **JTA** up to certain point; but without complexity. It's goal is to works well with every-day web/desktop applications; not to manage heavy-weight requirements such as transactions across multiple domains, etc.

In a nutshell *JTX* provides a transaction model that supports transaction demarcation over any number of resources - of any kind. Of course, the emphasis is put on database transactions; there is a layer built on top of *JTX* to integrate it well with *Db* and *Proxetta* frameworks. *JTX* may be used programmatically through simple API or declaratively using annotations.

### JTX in action

A picture is worth a thousand words, a good code example even more;) Here is one real-life example how *JTX* is used declaratively.

```java
    ...
    @Transaction
    public String view() {
        // read data db
        return result;
    }

    @ReadWriteTransaction
    public void store(int id) {
        // save data to db
    }

    @Transaction(propagation=PROPAGATION_REQUIRED, readOnly=false, timeout=100)
    public void update(int id) {
        // save data to db
    }
    ...
```

Cool, isn't it:) This example already shows several *JTX* features, like using custom annotations, but its definitely not the only way how *JTX* can be used.

Following *JTX* pages explains the concept behind the framework and its usage.


# JTX Concepts

*JTX* is all about: resources, resource managers, transactions and transaction managers.

### Resources and Resource managers

Resource in *JTX* is anything that have transactions. Speaking programatically, resource is any class than encapsulates some transactional entity, for example: database session (for databases), messages manager (for message queues). Resource knows how to **maintain** a transaction.

Each resource (i.e. resource type) has its own resource manager. In *JTX*, resource manager is responsible for managing transactions of the resources of the same type. As seen in the code, `JtxResourceManager` interface is fairly simple, two most important methods are `beginTransaction()` and `rollbackTransaction()`.

We can say that `JtxResourceManager` serves as an **adapter** between *JTX* framework and some transactional resource. For e.g. database that would be an implementation that takes database session (or connection) and creates a new transaction on it. Since it is an adapter, beginning and rolling back the transactions can be now done through resource manager, without touching the resource itself.

### Transaction manager

Transaction manager goes one step further. It's purpose is to control all registered resource managers and to create/close transactions on all acquired resources. *JTX* transaction manager also provides out-of-box transaction propagation handling. Let's see more details of `JtxTransactionManager`.

As said, *JTX* transaction manager is used to start transactions. Actually, when needed transactions are **requested** from the *JTX* manager. Depending on transaction **propagation**, manager will return an existing transaction or a new one.

Requested transactions may be optionally scoped by context in which they exists. Only one transaction may be opened in the context. For example, context can be a class within the transactions are created, so only the first method of that class will create the transaction; if that method internally invoke other (transactional) methods, their requests will be ignored.

### Transaction

Transaction is an unit of work that is performed by one or more resources. Great definition, huh:)? In *JTX*, transactions are encapsulated by `JtxTransaction` class. The most important thing to remember about it is that:

`JtxTransaction` is a '**transactional request**'. Its existence doesn't mean that real transaction is started on the resources(s). {: .attn}

We said that `JtxTransactions` are **requested** from the `JtxTransactionManager`. But only when a **resource** is requested from the jtx transaction, a real transaction is started on the resource!

*JTX* supports several different resource types, i.e. a transaction can be started over several resources. When committed, a real transaction is committed on each resource, one by one. We are aware this is not the ideal scenario for transactions over multiple resources, but it is pragmatic; on the other hand *JTX* is usually used in one-resource-type environment, where the only resource is database.

#### Transaction status

*JTX* transaction goes through several statuses during it's usage; they are very similar to JTA. On the very beginning, status is either `ACTIVE` or `NO_TRANSACTION`. As said, *JTX* transaction is an actual transaction **request**, therefore even if a real transaction is not required - e.g. as defined by propagation, there will be a `JtxTransaction` object in state `NO_TRANSACTION`.

Other statuses are self-explanatory and can be seen in javadoc or source.

### Transaction mode

Following attributes defines transaction mode.

#### Propagation behavior

Propagation behavior is used to define the transaction boundaries. It defines behavior if a transactional method is executed when a transaction context already exists.

*JTX* provides its own propagation management.

#### Isolation level

Isolation level has something to do with the concurrency control. When multiple transactions are running they may cause dirty reads, non repeatable reads and phantom reads. It is the degree of isolation one transaction has from the work of other transactions. For example, can this transaction see uncommitted writes from other transactions?

Isolation level is currently not supported by *JTX*. It is expected to be managed by transactional resource itself.

#### Timeout

Defines how long this transaction may run before timing out. Currently, *JTX* only checks the length of the transaction; longer transaction will not be canceled after timeout period.

#### readOnlyMode

Read-only transaction does not modify any data. Transaction should fail on write attempt. *JTX* does not treat this flag, it just passes it to the resource manager i.e. resource.

### Propagation behavior management

One very important feature provided by `JtxTransactionManager` is propagation behavior management. This means that manager will handle transaction propagation as defined by transaction mode attribute.

Following propagations are supported by *JTX*\\:

* `PROPAGATION_REQUIRED` - Support a current transaction, create a new

  one if none exists;
* `PROPAGATION_SUPPORTS` - Support a current transaction, execute

  non-transactionally if none exists;
* `PROPAGATION_MANDATORY` - Support a current transaction, throw an

  exception if none exists;
* `PROPAGATION_REQUIRES_NEW` - Create a new transaction, suspend the

  current transaction if one exists;
* `PROPAGATION_NOT_SUPPORTED` - Execute non-transactionally, suspend the

  current transaction if one exists;
* `PROPAGATION_NEVER` - Execute non-transactionally, throw an exception

  if a transaction exists.


# Example

To understand the concepts of *JTX* its best to see an example. To make things simpler, our transactional resource will be a simple `String` value. Let's create one such class:

```java
    public class WorkSession {

        static String persistedValue = "jodd";
        String sessionValue;
        boolean readOnly;
        int txno;            // transaction number

        public WorkSession() {    // start session in non-tx mode
        }
        public WorkSession(int txno) {    // start tx session
            this.txno = txno;
        }

        public void writeValue(String value) {
            if (txno == 0) {    // no transaction
                persistedValue = value;
                return;
            }
            // under transaction
            if (readOnly == true) {
                throw new UncheckedException();
            }
            sessionValue = value;
        }

        public String readValue() {
            if (sessionValue != null) {
                return sessionValue;
            }
            return persistedValue;
        }

        // commit
        public void done() {
            if (sessionValue != null) {
                persistedValue = sessionValue;
            }
            sessionValue = null;
        }

        // rollback
        public void back() {
            sessionValue = null;
        }
```

Now we are ready to begin our journey:)

### ResourceManager

The first thing is to create a `JtxResourceManager` for our resource. The main method to implement is `beginTransaction()`. It starts a transaction on our resource depending on transaction mode.

Since we will use `JtxTransactionManager`, propagation behavior and timeout will be already supported! Therefore, our resource manager has only to deal with isolation and read-only attribute. We will ignore isolation to make things simpler. Here is how resource manager may look like:

```java
    public class WorkResourceManager implements JtxResourceManager<WorkSession> {

        int txno = 1;

        public Class<WorkSession> getResourceType() {
            return WorkSession.class;
        }

        public WorkSession beginTransaction(JtxTransactionMode jtxMode, boolean active) {
            if (active == false) {
                return new WorkSession();
            }
            WorkSession work = new WorkSession(txno++);
            work.readOnly = jtxMode.isReadOnly();
            return work;
        }

        public void commitTransaction(WorkSession resource) {
            resource.done();
            txno--;
        }

        public void rollbackTransaction(WorkSession resource) {
            resource.back();
            txno--;
        }

        public void close() {
        }
    }
```

Quick overview of what we have done in `beginTransaction()`\\: the `active` flag tells us if real transaction should be started or we are working in auto-commit mode. When it is set, we create a transaction-aware resource. Since isolation is ignored, we only need to pass read-only flag.

### Usage

Now we are ready to use *JTX*\\:

```java
    // [1] create jtx manager and register our resource manager
    JtxTransactionManager jtxManager = new JtxTransactionManager();
    jtxManager.registerResourceManager(new WorkResourceManager());

    // [2] request jtx
    JtxTransaction jtx = manager.requestTransaction(
            new JtxTransactionMode().propagationRequired().readOnly(true));

    // [3] requrest resource i.e. start jtx
    WorkSession work = jtx.requestResource(WorkSession.class);

    // [4] work
    work.writeValue("new value");

    // [5] done
    jtx.commit();

    // [6] cleanup
    manager.close();
```

The most important thing to remember is that in step #2 we are just **requesting** a jtx transaction. Not until the next step, #3, the real transaction will be started. Again, we are **requesting** a resource, therefore, if we call it several time in a row, the same resource instance will be returned.

### Worker

*JTX* also provides `LeanJtxWorker`, a class that utilizes `JtxTransactionManager` and makes it more convenient for use when transaction is requested over different context, i.e. with transaction nesting. Basically, everything stays the same, except `LeanJtxWorker` would return `null` when new transaction is not created on its request, meaning that current transaction matches the requested transaction attributes (mostly propagation).


# Db and Annotations

*JTX* supports multiple resource types, but its most efficient with single resource type, such as relational database. Therefore, there is whole layer build on top of generic *JTX* classes that serves just to simplify use and usage of transaction over databases.

### Declarative transactions

But that is not all. Transactions, in general, are a perfect example of scattered logic, that can be encapsulated via aspects. With help of *Proxetta*, *JTX* can be applied on methods that are annotated with `@Transaction` annotation.

#### Custom transactions

In most applications we would have only two types of transactions: one that only read and one that allows writing data. `@Transaction` annotation by default matches read-only transactions, meaning that on every place where read-write transaction is needed, user have to write this with annotation elements.

To reduce this boilerplate code, it is possible to define custom annotation that takes different default values.


# DB Relations Example

**DbOom** does not provide relations mapping out-of-box. Instead, developer is able to use relations whenever he needs them. And it is not so complicated as it sounds.

### Example

![](/files/-MUyyGyMUcgpyuzNLY0W)

`Question` may have many `Answer`s, but only one `Country`. Lets see how to model this relation in Java in order to be convenient and easy to use.

### 1-to-1 relations

Foreign keys to other entities should be mapped in parent entity. Here, `Question` has `countryId` property mapped to corresponding column in database, as any other column:

```java
    @DbTable
    public class Question extends Entity {

        @DbColumn
        Long countryId;

        ...
    }
```

Having FK id mapped to entity is often convenient: when you need to update or delete questions `Country`, you just need the id (and not the `Country` instance).

On the other hand, sometimes it is required (or just handy:) to have full `Country` object in `Question`. For those situations we usually put the following set and get methods pair:

```java
    Country country;

    public Country getCountry() {
        return country;
    }

    public void setCountry(Country country) {
        countryId = country == null ? null : country.getId();
        this.country = country;
    }
```

How to populate the country? One solution is to simply execute `AppDao#findById(Country.class, id)` when `Country` is needed. Other solution, for 1-1 relationships, is to fetch both `Question` and `Country` together, using *join hints* in template sql:

```sql
    select $C{question.*}, $C{question.country.*} from $T{Question question}
    join $T{Country country} on $country.id=$question.countryId
```

### 1-to-many relations

In our example `Question` may have some `Answers`. There is nothing to map or configure in `Question` for this relation. `Answer`s has to be loaded manually when needed. Here is what we usually put for 1-to-many relations in parent entity (here: `Question`).

```java
    public class Question extends Entity {
        ...

        List answers = new ArrayList();

        public List getAnswers() {
            return answers;
        }

        public void setAnswers(List answers) {
            this.answers = answers;
            for (Answer answer : answers) {
                answer.setQuestion(this);
            }
        }

        public void addAnswer(Answer answer) {
            answers.add(answer);
            answer.setQuestion(this);
        }

        ...
```

One note: since we wanted to have here a bi-directional relation, we had to have lines: #12-#14 and #19. Now, how to load answers? One way is to simply write a service method that returns `List<Answer>` filtered and ordered anyhow you need them, or to simply load them in provided `Question`. If you do not need to have sorted or filtered list, you can simply invoke: `AppDao#findRelated(Answer.class, question)`. See relations & hints for more details.

### many-to-1 relations

On the parent side (here: `Answer`) you can do everything as for 1-1 relation: map FK and add set/get methods in the same manner. On the child side (here: `Question`) you have to manage this relations as already said in above note.

### many-to-many relations

There is nothing special in Db for many-to-many relations. Just simply write services/dao methods that returns whatever you need. If you want to use template-sql then you have to map the 'middle' table. Of course, you can split this relation to two 1-to-many; but this is completely up to you.

In Uphea, we have this situation between `Question` and `User`, where user may have many favorite questions. We have `Favorites` entity since we use template-sql.