Andrew Eells dot com

Agile Database dot com

Have added some new features to my agile database framework and decided to release as a slightly more professional undertaking – say hello to Agile Database dot com.

The code’s hosted on GitHub so please feel free to take a look and let me know if you have any feedback or issues.

I’m going to add a section on Usage shortly, and probably delete the old code hosted on this blog, but aside from that I don’t have much of a plan for a roadmap. Certainly multi-database / OS support without having to refactor the scripts would be beneficial. Please feel free to let me know if you have any other ideas!

Hibernate foreign-key collections and database best practice

I came across the following situation having noticed we were not enforcing database NOT NULL column constraints, even though all column entries were non-null. This is not best-practice from a database perspective and so I thought I’d look into it.

So, I have 2 tables, ENQUIRY and ELEMENT with a One-to-Many relationship such that an Enquiry has many Elements. And I wanted to enforce the NOT NULL constraint on foreign key column ELEMENT.ENQUIRY_ID. This relationship looks like so when modeling the Enquiry object with Hibernate:

@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name = "ENQUIRY_ID", referencedColumnName = "ID")
private Set elements = new HashSet();


When I enforced the NOT NULL constraint at the database level I received the following stacktrace however:

Caused by: java.sql.BatchUpdateException: ORA-01400: cannot insert NULL into ("ELEMENT"."ENQUIRY_ID")


So Hibernate is obviously persisting the collection of elements before the parent enquiry and then going back and doing an UPDATE on the foreign key field afterwards (so INSERT collection items with NULL foreign key, INSERT parent, UPDATE collection item foreign keys) as it doesn’t realise the foreign key column will always be non-null.

You must explicitly tell Hibernate that the foreign key column is NOT NULL. It can then persist the parent enquiry first followed by the collection items. To do this, add the following clause to the @JoinColumn annotation:

@JoinColumn(name = "ENQUIRY_ID", referencedColumnName = "ID", nullable = false)


This way, not only are you ensuring database best-practice, but Hibernate will also have to issue less statements (so INSERT parent, INSERT collection items) which should be a more efficient transaction statement.

The javax.persistence.JoinColumn API only states that the nullable clause indicates whether the foreign key column is nullable but nowhere could I find how this nuance affects Hibernate and the queries it issues – it’s probably out there someplace but I hope this post can help anyone with a similar problem.

Thanks to axtavt on stackoverflow for pointing out the obvious!

Oracle 11g UCP with Tomcat

Ensure you have the below tnsnames.ora file in TNS_ADMIN so as to connect to the server from client using connect string: sqlplus <username>@xyz

xyz =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)
(PORT = <port>)
(HOST = <server_name>)
)
(CONNECT_DATA = (SID = <SID>))
)

Hibernate’s @FilterJoinTable

Couldn’t find a completely articulate example or full documentation of how to add a filter to a Hibernate Many-to-Many collection association mapping, so thought I’d would post a complete example here.

It’s possible to apply the filter to the target entity table which is reasonably well documented, or at least you can have an educated guess, but also to the association table of the Many-to-Many relationship where the docs were a little less clear.

I have the following relational association table:

CREATE TABLE departure_point_resort_association (
  departure_point_id      VARCHAR(36) NOT NULL,
  resort_id               VARCHAR(36) NOT NULL,
  site_id                 ENUM('30', '31'...) NOT NULL,
  PRIMARY KEY (departure_point_id, resort_id),
  CONSTRAINT assoc_departure_point_fk FOREIGN KEY (departure_point_id)
    REFERENCES departure_point (id),
  CONSTRAINT assoc_resort_fk FOREIGN KEY (resort_id) REFERENCES resort (id)
);

which is mapping a ski resort to potential departure points or airports. There was no real requirement to persist an actual SITE table (which would be entirely static data) and to map to it’s foreign key value, so instead am using a simple MySQL ENUM column above, and wanted to apply a filter at the application level to the collection of Resorts based on this site_id.

@Entity @Table
@FilterDef(name = "siteFilter", parameters = {
    @ParamDef(name = "siteId", type = "string")
})
public final class Resort extends BasePersistentObject {
    @ManyToMany(fetch = FetchType.EAGER)
    @FilterJoinTable(name = "siteFilter", condition = "site_id = :siteId")
    @JoinTable(name = "DEPARTURE_POINT_RESORT_ASSOCIATION",
            joinColumns = {
                    @JoinColumn(name = "RESORT_ID")
            },
            inverseJoinColumns = {
                    @JoinColumn(name = "DEPARTURE_POINT_ID")
            }
    )
    private Set departurePoints;

Then it’s simply a case of enabling the filter in the client (DAO or whatever your framework dictates):

session.enableFilter("siteFilter").setParameter("siteId",  String.valueOf(siteId));

Tags: Hibernate

Hibernate StatelessSession with Transactional Annotation

I have a bi-directional collection mapping in Java which I thought was causing me problems when persisting using Hibernate’s StatelessSession API.

However, it looks as though the issue was being caused by the fact I was using the @Transactional annotation instead. The API states:

“Operations performed via a stateless session bypass Hibernate’s event model and interceptors.”

I didn’t pick up on the importance of the above statement as it’s not particularly clear at first glance, but it looks as though using Annotations via AOP just isn’t supported when streaming directly to the database using the StatelessSession.

Commenting out the annotation and using a programmatic Transaction instead corrected the problem:

//    @Transactional
public List<Package> insertAvailability(final List<Package> packages) {
    final StatelessSession session = sessionFactory.openStatelessSession();
    final Transaction tx = session.beginTransaction();

    session.insert(aPackage);
    // insert collections manually etc...

    tx.commit();
    session.close();

    return packages;
}

Tags: Hibernate

Primary Keys vs GUIDs

Just thought I would post some code having read Savvy Duck’s comments about the above. Although the project I refer to below was greenfield and didn’t require any kind of database migration, I never encountered any of the performance problems mentioned in some of the related articles.

We were using an Oracle 10g database however, and I’ll report an update if I see any issues with my latest project which is on MySQL and seems to have a bit of a history in this department. But it really was never an issue in any way that we recognized.

The likelihood of a primary key clash was calculated as 1 in 2.27 x 10exp32 for every one million records.

Anyway, the code. We were mapping to Java using Hibernate (avoiding the primary key problem mentioned here) like so:

public abstract class BasePersistentObject implements Serializable
{
    // will be overrriden with data read from database
    @Id
    private String id = UUID.createUUID();
}

I can provide the above UUID code if anyone wants to have a play with this implementation.

Instead of using the standard auto-increment Sequence then, the primary key was implemented with the following function:

/**
 * Returns a UID string.
 *
 * This is string based on a randomly generated bit sequence just like in the app server.
 */
CREATE OR REPLACE FUNCTION f_get_uid RETURN VARCHAR2 IS
BEGIN
	RETURN UTL_RAW.CAST_TO_VARCHAR2(
	           UTL_RAW.TRANSLATE( DBMS_CRYPTO.RANDOMBYTES(24),
	                              uid_util.uid_from_set,
	                              uid_util.uid_to_set ) );
END;
/

-- defines two constants used by f_get_uid() to generate UID strings;
CREATE OR REPLACE PACKAGE uid_util
IS
	uid_from_set CONSTANT RAW(256) := UTL_RAW.XRANGE(hextoraw('00'), hextoraw('FF'));
	uid_to_set   CONSTANT RAW(256) := UTL_RAW.CONCAT(
		UTL_RAW.CONCAT(
			UTL_RAW.XRANGE(UTL_RAW.CAST_TO_RAW('A'), UTL_RAW.CAST_TO_RAW('Z')),
			UTL_RAW.XRANGE(UTL_RAW.CAST_TO_RAW('a'), UTL_RAW.CAST_TO_RAW('z')),
			UTL_RAW.XRANGE(UTL_RAW.CAST_TO_RAW('0'), UTL_RAW.CAST_TO_RAW('9')),
			UTL_RAW.CAST_TO_RAW('-'),
			UTL_RAW.CAST_TO_RAW('_')
		),
		UTL_RAW.CONCAT(
			UTL_RAW.XRANGE(UTL_RAW.CAST_TO_RAW('A'), UTL_RAW.CAST_TO_RAW('Z')),
			UTL_RAW.XRANGE(UTL_RAW.CAST_TO_RAW('a'), UTL_RAW.CAST_TO_RAW('z')),
			UTL_RAW.XRANGE(UTL_RAW.CAST_TO_RAW('0'), UTL_RAW.CAST_TO_RAW('9')),
			UTL_RAW.CAST_TO_RAW('-'),
			UTL_RAW.CAST_TO_RAW('_')
		),
		UTL_RAW.CONCAT(
			UTL_RAW.XRANGE(UTL_RAW.CAST_TO_RAW('A'), UTL_RAW.CAST_TO_RAW('Z')),
			UTL_RAW.XRANGE(UTL_RAW.CAST_TO_RAW('a'), UTL_RAW.CAST_TO_RAW('z')),
			UTL_RAW.XRANGE(UTL_RAW.CAST_TO_RAW('0'), UTL_RAW.CAST_TO_RAW('9')),
			UTL_RAW.CAST_TO_RAW('-'),
			UTL_RAW.CAST_TO_RAW('_')
		),
		UTL_RAW.CONCAT(
			UTL_RAW.XRANGE(UTL_RAW.CAST_TO_RAW('A'), UTL_RAW.CAST_TO_RAW('Z')),
			UTL_RAW.XRANGE(UTL_RAW.CAST_TO_RAW('a'), UTL_RAW.CAST_TO_RAW('z')),
			UTL_RAW.XRANGE(UTL_RAW.CAST_TO_RAW('0'), UTL_RAW.CAST_TO_RAW('9')),
			UTL_RAW.CAST_TO_RAW('-'),
			UTL_RAW.CAST_TO_RAW('_')
		)
	);
END;
/

To GPL or not…

As the following post describes in more detail, I have created and released under the GPL a versioning framework for database release patching. This has been tested and used in an Agile environment with multiple users throughout all stages of the development lifecycle. Please feel free to download and use it within your project, or comment on any improvements you think could be made.

I thought it worth explaining the reason why I have released it under the Ordinary GPL and not the Lesser version. Firstly, I doubt this framework is ever likely to appear as part of a software release bundle – it’s more likely to be used to support the software development process and releases themselves, as opposed to actually being part of an application. And secondly, I would rather this were not used for direct commercial gain but to give users of freeware a benefit over commercial applications or software houses. There’s a very good explanation of the pros and cons on the GNU site.

Tags: agile database framework, gpl