FFL2:FFL2 Main/Hibernate Reverse Engineering/Hibernate Reverse Engineering Example

In this example we are adding views for real league positions to the database and creating the associated classes and mappings.

When we run a Hibernate reverse engineer it will reverse in both tables and views. However our life will be made easier by creating dummy tables first, then reverse engineering, then replacing them with views of the same name. The reason for this is that we can then define the primary keys and foreign key associations, without these the reverse engineer wouldn't give us exactly what we wanted and we'd be left with manual editing to do. In addition the types of the number would be incorrect (we get things like BigDecimal(45, 0) ).

Define the table

We will add a table as follows

 create table RealLeagueStanding (
	idRealClub        int(11) unsigned not null,
	seasonStartYear   int(11) unsigned not null,
	idRealDivision    int(11) unsigned not null,
	played            int(3) unsigned not null,
	won               int(3) unsigned not null,
	drawn             int(3) unsigned not null,
	lost              int(3) unsigned not null,
	goalsFor          int(3) unsigned not null,
	goalsAgainst      int(3) unsigned not null,
	homeWon           int(3) unsigned not null,
	homeDrawn         int(3) unsigned not null,
	homeLost          int(3) unsigned not null,
	homeGoalsFor      int(3) unsigned not null,
	homeGoalsAgainst  int(3) unsigned not null,
	awayWon           int(3) unsigned not null,
	awayDrawn         int(3) unsigned not null,
	awayLost          int(3) unsigned not null,
	awayGoalsFor      int(3) unsigned not null,
	awayGoalsAgainst  int(3) unsigned not null,
	goalDifference    int(3) not null,
	points            int(3) not null,
	primary key (idRealClub, seasonStartYear),
	CONSTRAINT `temp1rls` FOREIGN KEY (`idRealClub`) REFERENCES `RealClub` (`idRealClub`),
	CONSTRAINT `temp2rls` FOREIGN KEY (`seasonStartYear`) REFERENCES `FantacySeason` (`seasonStartYear`),
	CONSTRAINT `temp3rls` FOREIGN KEY (`idRealDivision`) REFERENCES `RealDivision` (`idRealDivision`)
) ENGINE=InnoDB;

Some points to note:

• By specifying the columns as not null we will get int primitives in the generated Java. If we did not specify 'not null' we would get Integer objects as variables of this type can be null.
• The constraints will give cause associations to the relevant classes to be generated.

Run the Reverse Engineer

Run it using 'Run | Hibernate Code Generation... | Reverse Engineer FFL Model from DB'. It will take a minute or two and then you should find all the generated code under the fantacycore/hibernate_gen_src folder.

Merge in the Changes

We have some new files to copy into our main source tree and also some updated files to merge. In the Eclipse 'Java' perspective open up the 'Navigator' view (use 'Window | Show View' if required). We will use the navigator view as it just deals with files and doesn't try to do anything clever.

Copy over new files

Copy the following files over directly from the hibernate_gen_src/uk/org/fantacy/model/ directory to the src/uk/org/fantacy/model/ directory as they are new:

• RealLeagueStanding.java
• RealLeagueStandingId.java
• RealLeagueStanding.hbm.xml

Update hibernate.cfg.xml

We need to add the following line to hibernate.cfg.xml to make hibernate aware of our new mapping file

        <mapping resource="uk/org/fantacy/model/RealLeagueStanding.hbm.xml" />

Merge updated files

We will also need to merge the following files which will be updated as the RealClub and FantacySeason classes will now link to RealLeagueStanding:

• FantacySeason.java
• FantacySeason.hbm.xml
• RealDivision.java
• RealDivision.hbm.xml
• RealClub.java
• RealClub.hbm.xml

We will merge these rather than copy them straight across to make sure we don't overwrite any changes we may have made previously. Fortunately merging is simple in Eclipse, click on both files in the navigator while holding Ctrl then right-click and choose 'Compare with | Each other'.

Merge FantacySeason.java

Comparing the FantacySeason.java files we can see that some of the diffs relate to adding the link to RealLeagueStanding and we can copy them all across. However we don't want to lose the code that was added previously to link to FantacyPlayerSeasonSummary. This isn't in the newly generated files because the constraints don't actually exist in the database as this class is based on a view (or more accurately a table with '_Table' at the end of it's name built from a view). Anyway, we just highlight each diff which we want to bring across and use the 'Copy current change from left to right' button to copy it over. The diffs we want are

• New member variable for the association

	private Set<RealLeagueStanding> realLeagueStandings = new HashSet<RealLeagueStanding>(
			0);

• Constructor (don't think this is really important, we don't use this constructor)

			Set<RealLeagueStanding> realLeagueStandings,

and

		this.realLeagueStandings = realLeagueStandings;

• Getter and Setter

	public Set<RealLeagueStanding> getRealLeagueStandings() {
		return this.realLeagueStandings;
	}

	public void setRealLeagueStandings(
			Set<RealLeagueStanding> realLeagueStandings) {
		this.realLeagueStandings = realLeagueStandings;
	}

Having done all these merges, save the file under fantacycore/src while still in the diff editor. At this point the diffs will update and you should see that the only diffs now are extra code under fantacycore/src which has been added in the past.

Merge FantacySeason.hbm.xml

The diff here is a bit more complicated. That is because the new version of Hibernate tools seems to be add extra information to the <set> element. None of this is required and I'm not really sure why it is adding it. For example

lazy="true"

is the default

table="FantacyCompetition"

is just repeating information in the existing FantacyCompetition.hbm.xml file and

fetch="select"

is, I'm pretty sure, the default as well. So we'll just copy the association we want across and delete all the unnecessary attributes of the <set> tag.

We will add the following after the definition of the fantacyWeeks association (or wherever else, the order of the set definitions isn't important).

        <set name="realLeagueStandings" inverse="true">
            <key>
                <column name="seasonStartYear" not-null="true" />
            </key>
            <one-to-many class="uk.org.fantacy.model.RealLeagueStanding" />
        </set>

Merge RealDivision.java

This is almost identical to the merge for FantacySeason.java above. We will add the following

• New member variable for the association

	private Set<RealLeagueStanding> realLeagueStandings = new HashSet<RealLeagueStanding>(
			0);

• Constructor (again I don't think this is really important, we don't use this constructor)

			, Set<RealLeagueStanding> realLeagueStandings

and

		this.realLeagueStandings = realLeagueStandings;

• Getter and Setter

	public Set<RealLeagueStanding> getRealLeagueStandings() {
		return this.realLeagueStandings;
	}

	public void setRealLeagueStandings(
			Set<RealLeagueStanding> realLeagueStandings) {
		this.realLeagueStandings = realLeagueStandings;
	}

Merge RealDivision.hbm.xml

Similar to the merge for FantacySeason.hbm.xml, we just add the following just before the closing <class/> tag (again we remove the unnecessary attributes of the set tag).

        <set name="realLeagueStandings" inverse="true">
            <key>
                <column name="idRealDivision" not-null="true" />
            </key>
            <one-to-many class="uk.org.fantacy.model.RealLeagueStanding" />
        </set>

Merge RealClub.java

Exactly equivalent to the two previous examples

Merge RealClub.hbm.xml

Exactly equivalent to the two previous examples, just add

        <set name="realLeagueStandings" inverse="true">
            <key>
                <column name="idRealClub" not-null="true" />
            </key>
            <one-to-many class="uk.org.fantacy.model.RealLeagueStanding" />
        </set>

Add data access methods to load the new objects

We won't want to be loading the new RealLeagueStanding objects through their primary keys, we will want to load all the objects for a given season and division at once. We add a new interface method declaration to FantacyDAO.java as follows (tip press Ctrl-Shift-O to automatically pull in the required imports)

	public List<RealLeagueStanding> getRealLeagueStandings(
			RealDivision realDivision, FantacySeason fantacySeason);

We add the implementation of this method in FantacyHibernateDAO as follows

	public List<RealLeagueStanding> getRealLeagueStandings(
			RealDivision realDivision, FantacySeason fantacySeason) {
		Query qry = this.getSession().createQuery(
				"from RealLeagueStanding rls " +
				"where rls.fantacySeason = :fantacySeason " +
				"and rls.realDivision = :realDivision " +
				"order by rls.points desc, rls.goalDifference " +
				"desc, rls.goalsFor desc, rls.goalsAgainst desc");
		
		qry.setEntity("realDivision", realDivision);
		qry.setEntity("fantacySeason", fantacySeason);
		
		List<RealLeagueStanding> ret = getQueryList(qry);
		return ret;
	}

Replace the table with a view

The table we created earlier has now served its purpose of simplifying the reverse engineering. It's now time to drop that and replace it with a view.

• drop the table as follows

drop table if exists RealLeagueStanding;

• create the view as follows (a couple of helper views also required)

CREATE OR REPLACE VIEW RealMatchWithScore AS
SELECT
	*,
	sum(CASE when (home = 1 AND idGoal is not null and not ownGoal)
		or (home = 0 AND idGoal is not null and ownGoal)
		then 1
		else 0
		END) as homeScore,
	sum(CASE when (home = 0 AND idGoal is not null and not ownGoal)
		or (home = 1 AND idGoal is not null and ownGoal)
		then 1
		else 0
		END) as awayScore
FROM
	RealMatch
	LEFT JOIN RealAppearance using (idRealMatch)
	LEFT JOIN Goal using (idRealMatch, idPlayer)
WHERE
	idRealMatchStatus = 3
GROUP BY
	idRealMatch;


CREATE OR REPLACE VIEW RealClubResults AS
SELECT
	seasonStartYear,
	idRealDivision,
	idRealMatch,
	idRealClub_home as idRealClub,
	1 as home,
	homeScore as goalsFor,
	awayScore as goalsAgainst,
	homeScore > awayScore as won,
	homeScore = awayScore as draw,
	homeScore < awayScore as loss,
	if ( homeScore > awayScore , 3 , if ( homeScore = awayScore , 1 , 0)) as pts
FROM 
	RealMatchWithScore
UNION
SELECT
	seasonStartYear,
	idRealDivision,
	idRealMatch,
	idRealClub_away as idRealClub,
	0 as home,
	awayScore as goalsFor,
	homeScore as goalsAgainst,
	awayScore > homeScore as won,
	awayScore = homeScore as draw,
	awayScore < homeScore as loss,
	if ( awayScore > homeScore , 3 , if ( homeScore = awayScore , 1 , 0)) as pts
FROM 
	RealMatchWithScore;


CREATE OR REPLACE VIEW RealLeagueStanding AS
SELECT
	idRealClub,
	seasonStartYear,
	idRealDivision,
	count(distinct idRealMatch) as played,
	sum(won) as won,
	sum(draw) as drawn,
	sum(loss) as lost,
	sum(goalsFor) as goalsFor,
	sum(goalsAgainst) as goalsAgainst,
	sum(if(home,won,0)) as homeWon,
	sum(if(home,draw,0)) as homeDrawn,
	sum(if(home,loss,0)) as homeLost,
	sum(if(home,goalsFor,0)) as homeGoalsFor,
	sum(if(home,goalsAgainst,0)) as homeGoalsAgainst,
	sum(if(not home,won,0)) as awayWon,
	sum(if(not home,draw,0)) as awayDrawn,
	sum(if(not home,loss,0)) as awayLost,
	sum(if(not home,goalsFor,0)) as awayGoalsFor,
	sum(if(not home,goalsAgainst,0)) as awayGoalsAgainst,
	sum(goalsFor) - sum(goalsAgainst) as goalDifference,
	sum(pts) as points
FROM 	
	RealClubResults results
GROUP BY 
	seasonStartYear, 
	idRealDivision, 
	idRealClub;