The Cayenne framework provides a way of working with objects whose values are stored in a database. It makes life easier for you the developer by abstracting away database operations and instead allowing you to work with Java objects. It allows you to do pretty much anything SQL can do and provides a much better mechanism for working with straight JDBC. Connection pooling, caching, transaction management and object lifecycles are all either taken care of or configurable through the framework (and usually with the modeller). Besides this it can "talk" with a large number of databases, some of which you've probably not even heard of before, thus allowing you to change the target database type or even use a number of databases in the same project (I've not tried cross-db queries yet but don't see why it wouldn't work).
I will touch on a few Cayenne things here, however there is a lot of documentation that covers the same things on the Cayenne site:
Download and install the modeller for you environment. There are now 3 choices depending on your environment.
Run the modeller. The first thing you'll need to do is set a path to your JDBC driver. Yes its still JDBC under the hood.
Create a datasource for you convenience while using the modeller. Remember to select the correct adapter for you database type.
Create a new Domain and then a domain node
Select a Local Datasource and then click "Sync with Local"
Create a new Map. This will now allow you to add entities.
Alternatively if you have an existing DB then you can get Cayenne to "re-engineer" existing tables, views and stored procedures into the Cayenne model.
Select Tools - Reengineer Database Schema
Select the Datasource
Select table and/or stored procedure names. These can be wildcarded with the '%' operator. eg 'test%' will select all tables that start with 'test'
DBEntities map directly to a database table. These are easy enough to create just give it a name, add some attributes and any relationships. Anything you add to the entity will be created when you create the corresponding ObjEntity.
One thing that is a standard in the Object relational world is to use generated keys instead of "natural" keys. It can't always be guaranteed that natural keys will be unique, often natural keys must be made composite leading to further complexity, you acheive a design consistency, joins are simplified, indexes based on PK are simplified and faster.
Cayenne can take care of the generation of the PK or can get your DB to do the job. If you decide to follow this approach then always create a primary key field that has the type int...
Right after you've finished building up your DBEntity create the ObjEntity and the modeller will create the ObjEntity based on the DBEntity. You'll notice that there is no attribute created for the primary key. Cayenne encourages you to not play around with the PK. There are ways to get to the PK if you really need to. You could manually expose it as an attribute or use the DataObjectUtils class to help.
Everything you've seen is straightforward enough and in a perfect world/project that is all you'll see. Sometimes though you'll need to model data from an existing stored procedure either because of security policies or you're hooking into an existing DB. I've seen some horrible models (data that is) before and sometimes a stored procedure can be the best way to get to that data.
2 choices again:
Give the stored procedure name to call. Check the returns value only if the stored procedure does return a value, this does not include what result set values it returns.
Add the parameters it requires, exactly as it expects them:
At this point we have a pointer to the actual stored procedure, whether we have defined it manually or let the modeller pull it from the DB. If the stored procedure does NOT return a resultset then you need not do anything else; ie it only inserts, updates or deletes data. However, if it does then we have a few more steps to do...what we need to do is get the resultset into ObjEntities, here is how:
Create a DbEntity that mirrors the exact names and types of the resultset. Ensure that the primary key of the DbEntity is exactly that of the resultset or this will not work. It is ok to not include all the other fields just make sure their name and type are correct.
Create an ObjEntity from the DBEntity.
In the modeller there is no direct linkage between the Stored procedure and the DBEntity (the DB and Obj entites are linked). The mapping for this is done in code, and here is how:
// Get a query object that will call our stored proc
ProcedureQuery query = new ProcedureQuery("my_stored_proc");
List list = null;
try {
// Set "IN" parameter values
query.addParameter("@start_date", getStartDate());
query.addParameter("@end_date", getEndDate());
log.info("Running report SP from: " + getStartDate() + " to: " + getEndDate());
// Run it
List rows = getDataContext().performQuery(query);
if (ObjectUtil.isEmpty(rows)) {
return null;
}
// Map the "raw" data rows returned to a list of our object type
list = getDataContext().objectsFromDataRows(MyObjEntity.class, rows, false, false);
} catch (UtilException e) {
log.error("Could not run stored procedure", e);
}
Of course if you don't want a list of nice ObjEntites you can just call the stored procedure directly without all the creating of DB and Obj entities and work with the data returned more directly.
There is an ant task for this called "cgen". To my knowledge there is not yet a Maven plugin equivalent but it would be very nice.
However you can easily call the cgen ant task...
In the pom.xml:
<plugin>
<artifactId>maven-antrun-plugin</artifactId>
<executions>
<execution>
<phase>generate-sources</phase>
<goals><goal>run</goal></goals>
<configuration>
<tasks>
<ant target="generate-classes" inheritrefs="true"/>
</tasks>
</configuration>
</execution>
</executions>
</plugin>
...and in the ant build.xml
<project>
<target name="generate-classes">
<typedef resource="org/objectstyle/cayenne/tools/antlib.xml"
classpathref="maven.compile.classpath"/>
<cgen map="src/main/resources/sitedb.map.xml" destDir="src/main/java"
superpkg="com.xyz.auto"/>
</target>
</project>
Up until now its all been about creating ObjEntities definitions but what kind of things can you do with them?
Create a new object/insert into database:
// Create a new data object in the context
DataObject dataObject = getDataContext().createAndRegisterNewObject(getVisitObject().getCrudMetaInfo().getJavaClass());
// Set values and commit
dataObject.setStringValue("Value1");
dataObject.setIntValue(1000);
dataObject.setDateValue(new Date());
getDataContext().commitChanges();
Change values/update statement into database:
dataObject = // Get the dataobject in some way...
/// Then set fields and commit
dataObject.setStringValue("Value1");
dataObject.setIntValue(1000);
dataObject.setDateValue(new Date());
getDataContext().commitChanges();
Delete
dataObject = // Get the dataobject in some way... getDataContext().deleteObject(dataObject); getDataContext().commitChanges();
Now if things got messy and you don't want to commit you can do a:
getDataContext().rollbackChanges();
I wanted to show how to create, update and delete objects in the database because it is so simple. Selecting or finding data can be as simple or a bit harder. I'll only lay down some of the basics here to kick off but I encourage looking at the Cayenne docs for a lot more info.
Say we want to find users in our (pretend) database that match a certain username and are active in the system...
List result = null;
String userName = "steve";
try {
// Add the criteria that the user name field must match userName
Expression expression = ExpressionFactory.matchExp(User.USER_NAME_PROPERTY, userName);
// AND that the user is active
expression = expression.andExp(ExpressionFactory.matchExp(User.IS_ACTIVE_PROPERTY, new Boolean(true)));
// Create a query
SelectQuery query = new SelectQuery(User.class, expression);
// Run the query
result = dataContext.performQuery(query);
} catch (Exception e) {
// Throw a custom exception...
throw new DBException("Could not get profile list for user: " + userName, e);
}
// the resultset in this case should have either 0 or 1 value even though our Cayenne query will always
// return a result.
User user = ObjectUtil.isEmpty(result) ? null : (User) result.get(0);
Cayenne has a lot more tricks up its sleave to solve many common problems with working with data in databases. I've only demonstrated the simple ways here.
Cayenne has a neat way of helping you find objects that are related (in db terms by foreign keys). Again it is a simple matter of walking through your object graph.
Lets take 2 examples:
A User has a number of Profiles (a profile is their customised view of the system). So there is a foreign key relationship of profile.user_id = user.id, a user can have many profiles but a profile can belong to only one user.
In the modeller to create a new relationship"
Finally click save to have the modeller validate and save your model.
If your validation fails you'll get a dialog box looking like:
If this happens click on each row in turn and fix the problem. You can leave the validation dialog up until you've fixed each one.
OK! Great we've got a relationship set and I will assume we've generated the DB and Object code. Now our runtime code to select the profiles for a user will look something like:
List<Profile> profileList = user.getToProfiles();
There are many times when we have a many-to-many relationship. The common appraoch to solve this is to have an intermediate or join table between the 2 entities where each of the outer entities has a one-to-many relationship with the join table. This would be defined similarly to the above example. So say a user may have many roles and a role may be assigned to many users, in the middle we'll have a join table called user_roles.
Then we can make use of this in Java like:
user.getToUserRoles().getRoles();
Use enough of these type of expressions and your code becomes harder to read and maintain. Cayenne to the rescue with flattened relationships.
Be nice if you could just do this:
user.getRoles();
this is what we'd be after anyway and you can do this from the Cayenne modeller.
Steps:
In your Java code you should now be able directly map from a user to get all their roles:
List<Role> roles = user.getToRoles();