AuDAO and building my website

Created: 2013/03/16 12:01:30+0000

I use a tool called AuDAO to generate the Data Access Objects, Data Transfer Object and SQL. These are generated from an XML description of the tables. I found it very useful and it is a convenient way to access a database from Java. That said it isn't suited for high volume websites, I was unable to find out any information on optimizing performance. The data objects map directly onto the tables generated by the SQL. Much of the functionality provided by databases and SQL is made unavailable because of this.

I use ant to generate the Java classes and SQL used by my website. This allows it to fit smoothly into my build process. I compile and package the generated Java classes and use them from by code. The SQL I use in creating a tearing down my database, which because of my automated process I do a lot.

Accessing the database made easy. I use the generated DaoFactory class to create a Data Access Object for a table. This is a call to a static method that takes a database Connection as the single argument. This DAO object is used to access and update a table. In the XML file I define a set of find methods that are provided by the table's DAO. These methods return one or more Data Transfer Objects. Each of these DTOs represents a single row in the table and has getters for each database column. The simplest is the auto-find which generates a method for getting a DTO based on the primary key. This returns a single DTO, other methods which don't work on the primary key are less constrained. By default the other methods return an array of DTOs but AuDAO can be easily configured to return the DTOs as a List.

Updating the database made easy. To insert an entry into the database a you first construct a DTO. The DTO has setters for each of the database columns, you call these methods to set the value of the row's columns. Then you get a DAO for the table and call the insert method passing in the DTO you just made. If you did everything right you are done. To update a row all you need is the DAO. In the XML file any column you want to update must be marked as editable. The DAO has an update method for each editable column it takes two arguments, the first is the primary key of the row you want to update and the second is the value you want to update to.

Adding a new column or table becomes easy. I update the XML file that defines the database, adding the new table or column. When I make a change to my database the first thing I do is unload it. With all the values written to a file I have a backup and a way to populate my database which is good because the second thing I do is drop my database. I modify the files to allow them to be loaded into the new database schema. I run AuDAO using ant which generates the SQL file that creates the database. Once the new database has been created I load the files back in and I have a working database.

This only works because I have a small database. I expect as it grows larger I will need to rely on alter table statements. Unloading, dropping, recreating and loading is something I do for more than just altering my DB schema. I use it to maintain sync between two separate databases. I develop and write articles on my laptop and then copy my code, site and data to my server where I extract and deploy everything.

Before finding AuDAO I was writing the DAOs and DTOs myself by hand. A slow and slightly buggy process that dragged down my productivity. The AuDAO generated classes are type checked and allow database access and modification without writing any SQL. It's focus on tables and rows prevent selecting part of a table's columns and there are no joins. I had to write custom find methods for getting rows out of a table based on foreign keys. Performing joins can only be done in the code using multiple DAOs and classes of DTOs. This means that large scale, complex databases will not get the power from AuDAO that they need. For small, simple databases like mine it makes everything much easier.