Hibernate – Getting Data out of the Database — setup — HQL

So I have populated my database using manual JSON parsing and database access methods that I wrote manually. I want to get the songs and shows out of the database now using Hibernate. I’m looking at the 5.2.8.Final release of Hibernate for Documentation on how to do this. The example in my older post was based on a much older version of Hibernate.

My database access methods are crazy. They don’t use JavaBean stlyle which entails using a no argument constructor to make the object and then using setters to populate the objects data. I was trying to make a bunch of constructors to fit the number of data pieces I had. That’s terrible style.

I’ve also been having problems getting the resource files in the correct locations. Here is a great tutorial on getting the files in their proper places.

There are two options for setting up the mappings. You can generate them all automatically using a tool I described in a previous post OR you can use annotations. The auto generated ones make an XML file that I am struggling to link my hibernate.cfg.xml file to. It is looking for Songs.hbm.xml and is unable to find it. Put all of the XML files into the resources folder.

Resources folder with xml files.

I will explore using annotations later but for now I don’t see a purpose. I don’t plan on having a changing schema for the tables. I will create the database and then generate the XML files just once. There isn’t any reason to use annotations for such a small project. Then again, there isn’t much reason to use Hibernate at all for such a small project other than to learn it.

Definitely DO NOT use the following setting in your xml file as it deletes your database table every time you start your program.

 <!-- Drop and re-create the database schema on startup -->
 <property name="hbm2ddl.auto">create</property>

You can easily rollback your database using AWS’s “Restore to a point in time” feature on the instance options pane. Every time you make a new database, you have to edit the security settings again which is a huge pain.

HQL

It is extremely similar to SQL syntax. Here’s a resource to read. And what follows are the best tricks I found.

You may supply extra join conditions using the HQL with keyword.

The With keyword restricts joins

from Cat as cat
    left join cat.kittens as kitten
        with kitten.bodyWeight > 10.0

The Fetch construct will allow me to get entire collections of objects with a single SELECT. This is the real useful piece of this puzzle. I want to get a List of songs included with my Show object.

A “fetch” join allows associations or collections of values to be initialized along with their parent objects using a single select. This is particularly useful in the case of a collection. It effectively overrides the outer join and lazy declarations of the mapping file for associations and collections. See Section 19.1, “Fetching strategies” for more information.

from Cat as cat
    inner join fetch cat.mate
    left join fetch cat.kittens

So you would get a CAT object which has a mate property and a kittens property. All of those are Cat objects on their own too.

Looking into collections now to see how I can make a mapping from a show to a collection of songs.

  • order-by=”column_name asc|desc”
    • There is an order by for the mapping section
    • only works with JDK 1.4//??
  • optimistic-lock=”true|false
    • default is true – meaning the p
    • This assumes that if a child is changed, the parent object will also be changed.
    • If the parent isn’t changed, the child will fail to change due to dirty updates
    • For many to one associations this should be false so children are updated
<map-key 
        column="column_name"                (1)
        formula="any SQL expression"        (2)
        type="type_name"                    (3)
        node="@attribute-name"
        length="N"/>
1 column (optional): the name of the column holding the collection index values.
2 formula (optional): a SQL formula used to evaluate the key of the map.
3 type (required): the type of the map keys.

Here’s an example of how to make a one to many relationship with annotations. I want to do it directly in the mapping file.

Here’s one with an XML mapping file.

<set 
 name="setList" 
 table="songs" 
 inverse="false" 
 lazy="true" 
 fetch="select"
 order-by="mtime asc">
 <key>
 <column name="show_id" not-null="true" />
 </key>
 <one-to-many
 class="com.milesgwood.moe.hbm.Songs"
 not-found="exception"
 />
 </set>

You can limit the number of results you get.

query.setMaxResults(maxRows);

Sorting Collections How TO