Using Teneo, EMF and Hibernate to update and query your data

Last week I showed you how to use Teneo, EMF and Hibernate to store your data in a database.

This week, we're going to have a look at how to update the data, add more records and how to query the database for information using HQL, the Hibernate Query Language.

If you haven't done so, you might consider taking last week's tutorial in order to get your environment set up and understand the basic concepts. If you're lazy, you can as well download the code of the tutorial to get started more quickly. It is available here.

As it turns out, in order to change anything in the library database, we must first fetch the to-be-changed data, so we need to have a look at querying first.

Retrieving and updating data
Let's assume we'd want to the update the number of pages in a book, because the new edition has an additional chapter.

1. So, first of all we need to open a session and begin a transaction:

       {
           Session session = sessionFactory.openSession();
           session.beginTransaction();

2. Next, let's create an HQL query. We want to find any books that are written by an author by the name "A. K. Dewdney" that contain "Omnibus" in their title.

           Query query = session.createQuery(
               "SELECT book from " +
               "    Book book, " +
               "    Writer writer " +
               "WHERE " +
               "    book.title like '%Turing Omnibus%' " +
               "AND " +
               "    writer.name = 'A. K. Dewdney'");

3. We can now execute the query and display the results:

           List books = query.list();
           Book book = books.get(0);
           System.out.println(book.getTitle());

4. Updating the page count is pretty obvious:

           book.setPages(520);

5. Finally, don't forget to commit the transaction and close the session:

           session.getTransaction().commit();
           session.close();
       }

Adding data
Let's now assume we want to add more books (and authors) to the library.

1. By now, you should be pretty familiar with the pattern of opening a new session:

       {
           Session session = sessionFactory.openSession();
           session.beginTransaction();

2. As we want to add new items to the library, we need to retrieve the library instance first of all:

           Query query = session.createQuery("from Library");
           List libraries = query.list();
           Library library = libraries.get(0);

3. Creating a new book and its author is easy, as we just have to use the API EMF so kindly generated for us:

           Writer writer = LibraryFactory.eINSTANCE.createWriter();
           writer.setName("J.R.R. Tolkien");
           Book book = LibraryFactory.eINSTANCE.createBook();
           book.setTitle("The Hobbit");
           book.setPages(320);
           book.setAuthor(writer);
           book.setCategory(BookCategory.MYSTERY);
           library.getBooks().add(book);
           library.getWriters().add(writer);

4. Finally, commit the transaction and close the session:

           session.getTransaction().commit();
           session.close();
       }

Using Teneo and EMF to store your data

Most of you know that I am working as a committer for various Eclipse-related projects (such as Xtext, Xpand and the Modeling Workflow Engine). You might not know, however, that I also work as a consultant for itemis. On one of my recent consulting assignments in Ottawa, Canada, I was asked "How can we use EMF to store our data in a database?"

Well, it turns out EMF can help a long way to store data in a database. Here is how.
Prepare your development environment

1. Get Eclipse 3.5 M5 (click here to download)
2. Unpack and start Eclipse
3. Bring up the "Install New Software" dialog (Help -> Install New Software)
4. Select Teneo EMF Hibernate Runtime and Teneo EMF Hibernate SDK, version 1.0.3
5. Cick Finish
6. You most probably will be asked to restart Eclipse.

Create a target definition that includes Hibernate and HSQLDB
In order to keep things simple, we will store the data in an HSQLDB database. We will use the Hibernate OR Mapper to perform the mapping between your data objects and the database. As you might guess, quite a number of libraries will be involved to get the task accomplished. Instead of creating a bunch of plug-in projects containing the respective libraries, or - even worse - copying all libraries into our project, we'll set up a target definition. Target definitions help to maintain a common set of dependencies for all developers on a team, which is a Good Thing.

1. Create a new project library.target (File -> New -> Project... -> General -> Project)
2. Create three folders in this project: hibernate, dependencies, hsqldb
3. Go to the SpringSource Bundle Repository and download the following OSGi bundles:
   

   file	save in folder
   com.springsource.org.hibernate-3.2.6.ga.jar	library.target/hibernate
   com.springsource.org.apache.commons.logging-1.1.1.jar	library.target/dependencies
   com.springsource.org.dom4j-1.6.1.jar	library.target/dependencies
   com.springsource.org.apache.commons.collections-3.2.0.jar	library.target/dependencies
   com.springsource.javax.transaction-1.1.0.jar	library.target/dependencies
   com.springsource.antlr-2.7.7.jar	library.target/dependencies
   com.springsource.org.hsqldb-1.8.0.9.jar	library.target/hsqldb

4. Create a new target definition library.target in this project (File -> New -> Other... -> Plug-in Development -> Target Definition)
5. Open the target definition and add the three directories to it's contents. As the GUI does not allow you to work with relative paths, you might consider to use a text editor to paste the following text:

   <?xml version="1.0" encoding="UTF-8"?>
   <?pde version="3.5"?>
   <target description="Teneo-related stuff, mostly Hibernate" name="Library Target Definition">
       <locations>
           <location path="${eclipse_home}" type="Profile"/>
           <location path="${workspace_loc}/library.target/hibernate" type="Directory"/>
           <location path="${workspace_loc}/library.target/dependencies" type="Directory"/>
           <location path="${workspace_loc}/library.target/hsqldb" type="Directory"/>
       </locations>
   </target>

6. Open the target definition in the Target Definition Editor and click on the Set as Target Platform hyperlink in the upper right area. This will activate the target definition. All contained bundles are now available and can be referenced as dependencies.

Create a model for your data
The data model will be based on the well-known library tutorial that ships with EMF. If you are interested in a more in-depth look, I recommend taking this tutorial. However, to shortcut things, here is the ultra-slim version of the EMF-Tutorial:

1. Download the Rose class model and save it on your computer
2. Create a new EMF project (File -> New -> Other... -> Eclipse Modeling Framework -> EMF Project)
   • Project name: library
   • Model importer: Rose class model
   • Browse to the Rose class model library.mdl mentioned before
   • Click on Load to load the model
   • Click Next, then Finish
3. In the library.genmodel editor, right-click on the Library node and select Generate Model Code

Create the library main application
In order to demonstrate how to use the data model and how to perform CRUD operations with your data, we will create a simple Java class. In the good spirit of encapsulation and components, we will create a new plug-in project to host this class:

1. Create a new Plug-in project library.main (File -> New -> Project... -> Plug-in Project)
2. Open the manifest and add the following dependencies:
   • library (this is the bundle which contains our data model)
   • org.eclipse.emf.teneo.hibernate
   • org.eclipse.emf.ecore.xmi
   • com.springsource.org.hibernate
   • com.springsource.org.apache.commons.logging
   • com.springsource.org.dom4j
   • com.springsource.org.apache.commons.collections
   • com.springsource.javax.transaction
   • com.springsource.antlr
   • com.springsource.org.hsqldb
3. Create a new Hibernate configuration file hibernate.properties in library.main/src and paste the following lines:

   hibernate.connection.driver_class=org.hsqldb.jdbcDriver
   # use the following line to run the embedded db:
   # hibernate.connection.url=jdbc:hsqldb:file:/some/path/on/your/computer/dbname
   # the following line will connect to a standalone (local) DB server:
   hibernate.connection.url=jdbc:hsqldb:hsql://127.0.0.1/library
   hibernate.connection.username=sa
   hibernate.connection.password=
   hibernate.dialect=org.hibernate.dialect.HSQLDialect
   hibernate.hbm2ddl.auto=true

Implement the library main application
With all the boilerplate in place, we're finally ready to write some code. We will create a new class and add some code to create an author and his book and store both in a library.

1. Create a new class LibraryDemo, making sure it has a main method
2. In order to use Teneo to persist our data, we first need to create a datastore and register our model package with it:

       // create the data store
       String dataStoreName = "LibraryDataStore";
       HbDataStore dataStore = HbHelper.INSTANCE.createRegisterDataStore(dataStoreName);
   
       // register the model package with the data store
       dataStore.setEPackages(new EPackage[] { LibraryPackage.eINSTANCE });
   
       // initialize the data store, which creates the tables
       dataStore.initialize();

3. Next, we need to get hold of a session factory and request a new session form it:

       SessionFactory sessionFactory = dataStore.getSessionFactory();
       {
           Session session = sessionFactory.openSession();
           session.beginTransaction();

4. Now, let's create a new library and save it to the session:

           // create a library
           Library library = LibraryFactory.eINSTANCE.createLibrary();
           library.setName("Developer's bookshelf");
   
           // store the library
           session.save(library);

5. In the following part, we will create an author and his book, link them to each other and add them to the library. There is no specific Teneo aspect to this part of the code, it is just straightforward usage of the API EMF generated for your datamodel:

           // create an author
           Writer writer = LibraryFactory.eINSTANCE.createWriter();
           writer.setName("A. K. Dewdney");
   
           // create a book
           Book book = LibraryFactory.eINSTANCE.createBook();
           book.setTitle("The New Turing Omnibus");
           book.setPages(480);
           book.setCategory(BookCategory.MYSTERY); // oh well, let's hope it's not mystery to most readers!
           book.setAuthor(writer);
   
           // add book and writer to library
           library.getBooks().add(book);
           library.getWriters().add(writer);

6. Finally, we need to commit our changes to the database and close the session:

           // commit changes to the database and close the session
           session.getTransaction().commit();
           session.close();
       }

Start the DB server and run the application

1. Open a command line and navigate to the directory that contains hsqldb.jar
2. Start the HSQLDB server using this command line:

   java -cp com.springsource.org.hsqldb-1.8.0.9.jar org.hsqldb.Server -database.0 file:library -dbname.0 library

3. Finally (!) go back to Eclipse and start LibraryDemo. You should get an output similar to this one:

   Mar 6, 2009 1:50:26 PM org.eclipse.emf.teneo.hibernate.HbHelper createRegisterDataStore
   INFO: Creating emf data store and registering it under name: LibraryDataStore
   ...
   Mar 6, 2009 1:50:28 PM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
   INFO: schema update complete

4. To actually see the data stored in the database, navigate to the directory containing the database and open library.log:

   CREATE USER SA PASSWORD "" ADMIN
   /*C1*/SET SCHEMA PUBLIC
   CONNECT USER SA
   ...
   INSERT INTO "library" VALUES(1,'Library',0,'Developer''s bookshelf')
   INSERT INTO "writer" VALUES(1,'Writer',0,'A. K. Dewdney',NULL,NULL,'Library','1',-2)
   INSERT INTO "book" VALUES(1,'Book',0,'The New Turing Omnibus',480,'Mystery',1,NULL,NULL,'Library','1',-3)
   DELETE FROM "writer" WHERE E_ID=1
   INSERT INTO "writer" VALUES(1,'Writer',0,'A. K. Dewdney',1,0,'Library','1',-2)
   DELETE FROM "book" WHERE E_ID=1
   INSERT INTO "book" VALUES(1,'Book',0,'The New Turing Omnibus',480,'Mystery',1,1,0,'Library','1',-3)
   INSERT INTO "writer_books" VALUES(1,1,0)
   COMMIT
   DISCONNECT

In the next installment, I will show you how to retrieve objects from the database and query the database using Hibernate Query Language (HQL).

Download the source
If you are interested in the source for the solution so far, you can download it here: (click to download).