News
Release Notes
  • Release 1.7
    April 14, 2012

    Support for HSQLDB engine. More

  • Release 1.6
    February 19, 2011

    Lists instead of arrays in finders. Enumerations mapped to Strings in DB. Batch inserts. More

  • Release 1.5
    June 10, 2010

    Moved to open-source. Caching for GAE implemented. Extended GQL parser. More

  • More...
XML DAO DTO SQL

AuDAO - SQL and Java DAO Generator

This is a free Java Data Access Objects (DAO), Data Transfer Objects (DTO)
and SQL script generator for MySQL, Oracle, HSQLDB and Google App Engine datastores.

The input is a source configuration xml file which describes the tables (entities) and their relations. The output is a set of SQL scripts and Java DAO layer which allows you to access your data structures without any knowledge of the underlying datastore API.

Click on the DAO and DTO icons to show the generated classes:

<database version="1.0" xmlns="http://www.spoledge.com/audao">-
  <config>+
    <dto enum-column="true"/>
    <factory>+
      <create-params default="false" connection="true"/>
    </factory>
  </config>
  <tables>-
    <auto-find/>
    <table name="seo_words">+
      <columns>+
        <column name="word_id">+
          <type>int</type>
          <auto/>
          <pk/>
        </column>
        <column name="the_word">+
          <type min-length="3" max-length="64">String</type>
          <not-null/>
        </column>
      </columns>
      <indexes>+
        <index name="inx_seo_word">+
          <unique/>
          <columns>+
            <column name="the_word"/>
          </columns>
        </index>
      </indexes>
      <methods>+
        <find name="dynamic">+
          <dynamic/>
        </find>
      </methods>
    </table>
    <table name="seo_word_count">+
      <edit-mode>column</edit-mode>
      <columns>+
        <column name="word_id">+
          <ref table="seo_words"/>
          <pk/>
        </column>
        <column name="entity_id">+
          <ref table="entities"/>
          <pk/>
        </column>
        <column name="word_count">+
          <type>int</type>
          <not-null/>
          <edit/>
        </column>
      </columns>
      <methods>+
        <find name="byPrefix" limit="10">+
          <condition>+
            <query>word_id in (select word_id from seo_words where the_word like ?) and entity_id=? and word_count != 0 order by word_count desc</query>
            <params>+
              <param name="word" type="String"/>
              <column name="entity_id"/>
            </params>
          </condition>
        </find>
      </methods>
    </table>
    <table name="seo_word_index">+
      <columns>+
        <column name="word_id">+
          <ref table="seo_words"/>
          <pk/>
        </column>
        <column name="entity_id">+
          <ref table="entities"/>
          <pk/>
        </column>
        <column name="record_id">+
          <type>int</type>
          <pk/>
        </column>
        <column name="word_priority">+
          <type>int</type>
          <not-null/>
        </column>
      </columns>
      <indexes>+
        <index name="inx_seo_winx_rec">+
          <columns>+
            <column name="entity_id"/>
            <column name="record_id"/>
          </columns>
        </index>
      </indexes>
      <methods>+
        <delete>+
          <index name="inx_seo_winx_rec"/>
        </delete>
      </methods>
    </table>
    <table name="entities" java="Entity">+
      <read-only/>
      <columns>+
        <column name="entity_id">+
          <type>int</type>
          <enum>+
            <value id="100">SEGMENT</value>
            <value id="110">SEGMENT_RATING</value>
            <value id="120">SEGMENT_CONFIDENCE</value>
            <value id="190">COMPANY_ACTIVITY_TYPE</value>
            <value id="200">PARTY</value>
            <value id="201">PARTY_ICO</value>
            <value id="202">PARTY_NAME</value>
            <value id="203">PARTY_ADDRESS</value>
            <value id="204">PARTY_PHONE</value>
            <value id="300">PAYMENT_RATING</value>
            <value id="310">FINANCIAL_STATE</value>
            <value id="390">PARTY_RATING</value>
            <value id="400">USER_COMMENT</value>
            <value id="410">BATCH</value>
            <value id="980">LOG_HISTORY</value>
            <value id="990">USER</value>
            <value id="991">ROLE</value>
            <value id="992">USER_ROLE</value>
            <value id="995">APPSEC_MODULE</value>
            <value id="996">APPSEC_ACTION</value>
            <value id="997">APPSEC_ACCESS</value>
          </enum>
          <pk/>
        </column>
        <column name="entity_name">+
          <type max-length="32">String</type>
          <not-null/>
        </column>
      </columns>
      <indexes>+
        <index name="inx_entity_name">+
          <unique/>
          <columns>+
            <column name="entity_name"/>
          </columns>
        </index>
      </indexes>
      <data>+
        <row>+<c>100</c><c>SEGMENT</c></row>
        <row>+<c>110</c><c>SEGMENT_RATING</c></row>
        <row>+<c>120</c><c>SEGMENT_CONFIDENCE</c></row>
        <row>+<c>190</c><c>COMPANY_ACTIVITY_TYPE</c></row>
        <row>+<c>200</c><c>PARTY</c></row>
        <row>+<c>201</c><c>PARTY_ICO</c></row>
        <row>+<c>202</c><c>PARTY_NAME</c></row>
        <row>+<c>203</c><c>PARTY_ADDRESS</c></row>
        <row>+<c>204</c><c>PARTY_PHONE</c></row>
        <row>+<c>300</c><c>PAYMENT_RATING</c></row>
        <row>+<c>310</c><c>FINANCIAL_STATE</c></row>
        <row>+<c>390</c><c>PARTY_RATING</c></row>
        <row>+<c>400</c><c>USER_COMMENT</c></row>
        <row>+<c>410</c><c>BATCH</c></row>
        <row>+<c>980</c><c>LOG_HISTORY</c></row>
        <row>+<c>990</c><c>USER</c></row>
        <row>+<c>991</c><c>ROLE</c></row>
        <row>+<c>992</c><c>USER_ROLE</c></row>
        <row>+<c>995</c><c>APPSEC_MODULE</c></row>
        <row>+<c>996</c><c>APPSEC_ACTION</c></row>
        <row>+<c>997</c><c>APPSEC_ACCESS</c></row>
      </data>
    </table>
  </tables>
</database>
up

For more information about DAO concept, please see Wikipedia or DAO Pattern.

The generated Java DAO layer uses plain JDBC to access database (MySQL, Oracle, HSQLDB). No other frameworks or technologies are needed to run ! The code is fully under your control.

The generated Java DAO layer for Google App Engine (GAE) uses directly the GAE Datastore API now (using DatastoreService). It is also possible to generate DAO layer over JDO (using PersistenceManager), but it is not recommended to use it for production environments due to performance issues.

The generated Java code requires Java 1.5 or higher.

AuDAO is an open-source project hosted at http://code.google.com/p/audao/.

Learn how to begin in Getting Started with AuDAO or list all DAO and SQL Generator Features or order and download AuDAO immediatelly.

Why DAO ?

Look at the following code snippets - in the left you can see one JDBC and one JDO code example. In the right you can see the corresponding code using generated DAO layer.
Which side do you like more ?

JDBC DAO Layer
Connection conn = ...;
User dto = ...;
PreparedStatement stmt = null;
ResultSet rs = null;

try {
  stmt = conn.prepareStatement( SQL_INSERT, PreparedStatement.RETURN_GENERATED_KEYS );

  if (dto.getUserName() == null) {
      throw new DaoException("Username cannot be null");
  }

  if (dto.getUserName().length() > 32) {
      throw new DaoException("Username's max length is 32");
  }

  stmt.setString( 1, dto.getUserName() );

  int n = stmt.executeUpdate();

  rs = stmt.getGeneratedKeys();
  rs.next();

  dto.setUserId( rs.getInt( 1 ));

  return dto.getUserId();
}
catch (SQLException e) {
  ... // handle exception
}
finally {
  if (rs != null) try { rs.close(); } catch (SQLException e) {}
  if (stmt != null) try { stmt.close(); } catch (SQLException e) {}
}
    
User dto = ...;

UserDao dao = DaoFactory.createUserDao();

return dao.insert( dto );
    
JDO DAO Layer
PersistenceManager pm = ...;
long userId = ...;
String newUserName = ...;

if (newUserName == null) {
  throw new DaoException("Username cannot be null");
}

if (newUserName.length() > 32) {
  throw new DaoException("Username's max length is 32");
}

User user = null;

try {
  user = pm.getObjectById( User.class, userId);
}
catch (JDOObjectNotFoundException e) {}

if (user != null) {
  user.setUserName( newUserName );
}
    
long userId = ...;
String newUserName = ...;

UserDao dao = DaoFactory.createUserDao();

dao.updateUserName( userId, newUserName );
    

As shown above the generated DAO layer can also perform some validations or even automatically assigns values to empty fields - depending how you configure the source.

And moreover the generated DAO layer can be independent of the underlying database. For example you do not care about the way how autoincrement fields are fetched from the database - it is the responsibility of the DAO layer to use autoincrement fields in MySQL, sequences in Oracle / HSQLDB and automatically generated fields in Google App Engine.

Free Online Generator

We provide you our online generator as a free service. You do not need to register. If you want to try it, then just try it!

Of course, this online generator service is limited by the size of the source configuration file. You can increase that limit by signing-in with your Google Account.

Or you can use the standalone version for free now, go to the download page.

AuDAO Main Features

Write only one configuration file and let AuDAO generate all the following files:

  • SQL create script which creates all DB structures like tables, views, indexes, foreign key constraints and even data inserting clauses ! Learn more...
  • SQL drop script which deletes all structures created by the create script - this is useful for testing - just execute both scripts - drop and create - and your test DB is fresh again !
  • Java DTOs (Data Transfer Objects) - which allow you to represent one DB record as a Java object
    • automatic mapping of DB types to Java types
    • mapping of allowed values in DB to Java enumerations - learn more...
    • column references - learn more...
    • inheritance is supported
    • can be used as DTOs for client applications developed using Google Web Toolkit
  • Java DAOs (Data Access Objects) - which allow you to write applications independent of target DB type
  • Java DAO Implementations - which implement the DAO layer for selected DB type
    • automatic primary keys implemented by native DB features (sequences, autoincrements)
    • mapping and casting Java types to native DB types

For more information please contact us at

Free Online Tool

Try our free online DAO generator.

Upload the configuration XML file and get the generated SQL scripts and Java classes for free !

Free GQL Parser

Download source or binary of our open-source GQL parser.

Query q =
 new GqlDynamic()
  .parseQuery(
   "SELECT * FROM Ent");
All GQL features are supported. More...