Scenario 1. DTO class is generated using meta-data of of data table or data view:

<dto-class name="Product" ref="products" />

Scenario 2. DTO class is generated using meta-data of SQL statement:

<dto-class name="Order" ref="get_orders.sql" />

SQL statement must return ResultSet. For the example above, it may be something like:

SELECT o.*, (SELECT COUNT(*) FROM order_positions WHERE o_id=o.o_id) as positions_count
FROM orders o
WHERE o.o_date BETWEEN ? AND ?

Scenario 3. The value of 'ref' is empty. You describe class fields in XML:

<dto-class name="Pair" ref="" >
	<field column="id" java-type="int" />
	<field column="name" java-type="String" />

In Scenarios 1 and 2, you can (re-)define the types of some/all fields:

<dto-class name="Order" ref="get_orders.sql" >
	<field column="positions_count" java-type="Integer" />


There are two types of instructions to define functionality of DAO classes: 'CRUD' and 'Free-SQL'.

For 'CRUD' instructions, code generator creates 1. data access code 2. SQL statements:

	<crud-auto dto="Product" table="products" />

'Free-SQL' means that code generator creates data access code for external SQL statements:

	<query-dto-list method="getOrders(date1, date2, userId)" ref="get_orders.sql" dto="Order" />
	<exec-dml method="updateOrderState(orderId)" ref="update_order_state.sql" />

There are no restrictions like 'one DAO, one DTO' or 'one DAO, one data table'. 'CRUD' and 'Free-SQL' can be mixed:

	<crud-auto dto="Order" table="orders" />
	<crud-auto dto="OrderPosition" table="order_positions" />
	<query-dto-list method="getOrders(date1, date2)" ref="get_orders.sql" dto="Order" />
	<query-dto-list method="getOrderPositions(orderId)" ref="get_order_positions.sql" dto="OrderPosition" />
	<exec-dml method="updateOrderState(orderId)" ref="update_order_state.sql" />


There are two XML tags to generate CRUD code: 'crud-auto' and 'crud'. This is how they look with completely defined attributes and internal elements:

<crud(-auto) dto="Order" table="ORDERS" generated="*" fetch-generated="true" >
	<create method="createOrder" />
	<read-all method="readOrderList" />
	<read method="readOrder" />
	<update method="updateOrder" />
	<delete method="deleteOrder" />

For 'crud-auto', you define internal elements (like '<create...') only if you need to change default names of methods. With all defaults, 'crud-auto' is very simple:

<crud-auto dto="Order" table="ORDERS" />

For 'crud', the method is generated only if defined. To enable only CREATE, you need something like this:

<crud dto="Order" table="orders" >
	<create method="createOrder" />

Common rules for 'crud-auto' and 'crud' are described below.

Value '*' for 'generated' instructs the code generator to detect auto-incremented columns with JDBC. If JDBC driver doesn't support this feature, you specify the list of auto-incremented columns explicitly, like generate="o_id, o_serial_id".

Auto-incremented columns are not included in SQL statement INSERT. For example, if the table contains columns 'id', 'no', 'name' and 'id' is auto-incremented, then SQL will be like 'INSERT (no, name) INTO (?, ?)'

Empty value for 'generated' instructs the code generator to ignore auto-incremented columns while building SQL. For the example above, 'INSERT (id, no, name) INTO (?, ?, ?)' will be generated.

When 'fetch-generated' is "true", the code responsible for fetching of auto-incremented values after INSERT is generated. If you don't need this functionality, specify "false".

Defaults are: "*" for 'generated' and "true" for 'fetch-generated'.

CRUD-UPDATE method is not generated if all columns of the table belong to the primary key. Also, CRUD method is not generated if the primary key is required, but it cannot be detected.

DAO, 'Free-SQL'

XML-tag Generated method returns Comment
query Single scalar value For SQL statements that return ResultSet, such as SELECT. ResultSet should contain single column. Only first record is fetched.
query-list List of scalar values For SQL statements that return ResultSet, such as SELECT. ResultSet should contain single column. All resulting records are fetched.
query-dto Single DTO For SQL statements that return ResultSet, such as SELECT. ResultSet should contain 1..n columns. Only first record is fetched and represented as DTO. Class of DTO is specified with attribute 'dto'.
query-dto-list List of DTO For SQL statements that return ResultSet, such as SELECT. ResultSet should contain 1..n columns. All resulting records are fetched and copied to DTO. Class of DTO is specified with attribute 'dto'.
exec-dml Row count for DML statement or 0 for SQL statement that returns nothing. For SQL Data Manipulation Language (DML) statements, such as INSERT, UPDATE or DELETE; or an SQL statements that return nothing, such as a DDL statements.

Attribute 'method' specifies the name of generated method and the list of parameters, if needed:

Example Comments
method="getAll" For SQL without parameters.
method="getAll(o_id, o_no, o_date, c_id)" For SQL with parameters. The names of parameters are listed in brackets.
method="getAll(Order(o_id, o_no, o_date, c_id))" For SQL with parameters. Parameter values are passed to method through DTO.

Code generator detects the types of parameters through JDBC. Alternatively, you can define these types in XML:

<exec-dml method="updateSound(byte[] t_file, t_id)" ref="updateSound.sql" />

For <query> and <query-list>, you can (re)define the types of return values:

<query method="getSound(Long t_id)" ref="get_sound.sql" return-type="byte []" />

Attribute 'external-sql' is used when you need to build the text of SQL statements dynamically (in external code):

<query method="getTotalOrdersCount" ref="order/get_total_orders_count.sql" external-sql="true"/>

For simple SQL statements like 'SELECT * FROM customers WHERE CompanyName=? AND Region=?', use shortcuts instead of SQL files:

<query-dto-list method="findCustomers(companyName, region)" ref="customers(CompanyName, Region)" dto="Customer"/>