Andrew Eells dot com

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