Chapter 4. Starting from an Existing Schema

In many enterprise environments, applications will be based on an existing database schema, not the other way around. Hibernate, in conjunction with a tool called Middlegen, supports this development model. In this chapter, persistent objects will be derived from an existing MySQL schema, using Middlegen to generate the Hibernate mapping files; these will in turn be used to generate Java source files.

The example in this chapter should be familiar to anyone who has ever at tended school. The database schema models a set of courses and students, tracking exams and exam results for each student. Many books on relational database design include this example—especially some of the more academic texts.

Initial Schema

In this scenario, development is based on an existing schema. Application designers are often not allowed to significantly update or change a schema, perhaps because it is in use by other applications, or because the database is a legacy system, or for any number of other possible reasons. The point is that the database design in this scenario drives the application development. A schema is given, and may occasionally be embroidered (for example, adding a column), but the overall structure is fixed prior to application development.

Listing 4.1 shows the script used to generate the schema for the database in this chapter. Note that this script is written to automatically drop and recreate the tables used in the chapter each time it is run, destroying any data present in the tables (obviously this would not be used in a “real” system).

The schema in this example includes several MySQL specific commands. For example, the TYPE=InnoDB command tells MySQL to convert the tables to the InnoDB format, a more modern format for storing database data than the default MySQL format. Similarly, the foreign-key checks are disabled to avoid errors when dropping the tables. The schema described in this chapter is based on MySQL 4.0.18. Depending on the database you are using, you may need to modify this schema script to be able to execute it. Alternatively, you may wish to use the graphical administrative tool included with your database (if any) to create this schema.

Pay close attention to the commands to create indexes and foreign key constraints at the end of the script. Middlegen (the tool used later in this chapter) reads these constraints and uses them to generate relationship mappings. In other words, Middlegen reads these key relationships and uses them to establish relationships in the generated Hibernate mapping files (and from there in the generated Java source).

If you wish to see a graphical representation of the tables generated by this script, you may wish to skip ahead to glance at the tables in Figure 4.2.

Example 4.1. Schema Generation Script

USE hibernate;

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS Course;
DROP TABLE IF EXISTS Student;
DROP TABLE IF EXISTS Exam;
DROP TABLE IF EXISTS ExamResult;
SET FOREIGN_KEY_CHECKS=1;
CREATE TABLE Course
(
      ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
);
ALTER TABLE Course ADD title CHAR(100);
ALTER TABLE Course ADD quarter CHAR(4);

CREATE TABLE Student
(
      ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
);
ALTER TABLE Student ADD firstName CHAR(100);
ALTER TABLE Student ADD lastName CHAR(100);
ALTER TABLE Student ADD idString CHAR(20);

CREATE TABLE Exam
(
      ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
);
ALTER TABLE Exam ADD courseID BIGINT UNSIGNED;
ALTER TABLE Exam ADD date TIMESTAMP;
ALTER TABLE Exam ADD comment CHAR(255);

CREATE TABLE ExamResult
(
      ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY
);
ALTER TABLE ExamResult ADD score INT;
ALTER TABLE ExamResult ADD studentID BIGINT UNSIGNED;
ALTER TABLE ExamResult ADD examID BIGINT UNSIGNED;


ALTER TABLE Course TYPE=InnoDB;
ALTER TABLE Student TYPE=InnoDB;
ALTER TABLE Exam TYPE=InnoDB;
ALTER TABLE ExamResult TYPE=InnoDB;

ALTER TABLE Exam ADD INDEX (courseID);
ALTER TABLE ExamResult ADD INDEX (studentID);
ALTER TABLE ExamResult ADD INDEX (examID);

ALTER TABLE Exam ADD CONSTRAINT FK_exam_courseID FOREIGN KEY (courseID) REFERENCES Course 
Schema Generation Script(ID);
ALTER TABLE ExamResult ADD CONSTRAINT FK_examresult_studentID FOREIGN KEY (studentID)
Schema Generation Script REFERENCES Student (ID);
ALTER TABLE ExamResult ADD CONSTRAINT FK_examresult_examID FOREIGN KEY (examID) REFERENCES
Schema Generation Script Exam (ID);

SHOW TABLES;
Middlegen Graphical Interface

Figure 4.2. Middlegen Graphical Interface

Using Middlegen

Assuming that the script has been used to generate the schema in the database, the next step is to set up and configure Middlegen. Middlegen is a tool for generating mapping files and other artifacts from an existing database schema. It can be used to generate bindings for a variety of other technologies (including EJB 2.X) in addition to Hibernate. That's what Middlegen does—generate developer artifacts (for the “middle tier”) from a relational database.

Middlegen uses JDBC to connect to a database and then generate application resources and code from that database. Although this may seem counter-intuitive, Ant is used as the engine for launching and configuring Middlegen. The advantage of using Ant is that the graphical user interface and the command-line build process can share a single configuration.

Obtaining Middlegen

You can download the complete Middlegen release from SourceForge.net at http://sourceforge.net/projects/middlegen. Unfortunately, the default download includes a large suite of files unnecessary for use with Hibernate (for example, extensive support for EJB CMP 2.0 and JDO). A great many complex build files are included, as well as a large suite of libraries. To make it easier to understand, this chapter will use a somewhat stripped down version of the Middlegen 2.0 VO files, available for download at http://www.cascadetg.com/hibernate/.

Figure 4.1 shows the files contained in the archive. The lib directory contains the *.jar files needed by the project. The Commons, log4j, Middlegen, and Velocity JAR files are included with the Middlegen 2.0 VO project, and the MySQL driver is available for download from http://www.mysql.com/.

Middlegen Directories and Files

Figure 4.1. Middlegen Directories and Files

As you will have noticed, there are several other folders in the distribution. The prefs directory contains the preferences generated by the Middlegen GUI. The hbm directory contains the *.hbm.xml files generated by Middlegen. Similarly, the src directory contains Java source files generated from the *.hbm.xml files by an Ant task.

The build.xml file is an Ant script used to launch the Middlegen GUI (see Chapter 2 for more information on Ant). The schema.sql file contains the MySQL SQL statements, as shown in Listing 4.1. The various log files are generated during the run of the Ant script, and may be useful if you encounter problems.

Configuring Middlegen

The most important file in the distribution is the build.xml file. If you are accustomed to working with Ant, you may expect build.xml to serve primarily as a tool for performing automated builds. Middlegen, however, uses Ant and build.xml as a configuration tool for the graphical user interface and the automated build process. When you are first building your application, you will use the Middlegen user interface, but later you may wish to merely use Middlegen as an automated build tool.

Listing 4.2 show the header for the build.xml file, containing the various configuration options for the build script. Of particular interest are the package settings (allowing you to control the package for the generated source) and the JDBC connectivity settings.

Example 4.2. Ant Build and Run Script

<project default="all">

      <!-- project name -->
      <property name="name" value="chapter4" />

      <!-- installation configuration -->
      <property name="hibernate_path"
            value="C:devenvhibernate-2.1.2"/>
      <property name="hibernate_tool_path"
            value="C:devenvhibernate-extensions-2.0.2	ools"/>
      <property name="project.dir"
           value=
"C:devenveclipsev3_M4workspacehibernatecomcascadetgch04" />

      <!-- the generated package info -->
      <property name="package" value="com.cascadetg.ch04" />
      <property name="package.dir" value="comcascadetgch04" />

      <!-- various sub directories -->
      <property name="lib.dir" value=".lib" />
      <property name="prefs.dir" value=".prefs" />
      <property name="build.hbm.dir" value=".hbm" />
      <property name="build.src.dir" value=".src" />

      <!-- name of the database script -->
      <property name="database.script.file" value="schema.sql" />
      <!-- JDBC connectivity information -->
      <property name="database.driver.file"
value="${lib.dir}mysql-connector-java-3.0.10-stable-bin.jar"/>
      <property name="database.driver.classpath"
            value="${database.driver.file}"/>
      <property name="database.driver"
            value="com.mysql.jdbc.Driver"/>
      <property name="database.url"
            value="jdbc:mysql://localhost/hibernate"/>
      <property name="database.userid"   value="root"/>
      <property name="database.password" value=""/>
      <property name="database.schema"   value=""/>
      <property name="database.catalog"  value=""/>

As shown in Listing 4.3, the default all target will build the tables in the database by executing a script containing the commands, as shown in Listing 4.1, then invoke Middlegen (generating the *.hbm.xml files), create the Java sources from the *.hbm.xml files, and finally copy the files into the project development tree.

Example 4.3. Building Tables

<!-- default target -->
<target name="all"
      depends="build_tables,middlegen,build_hbm,copy_files" />

<!-- calls and executes the schema script -->
<target name="build_tables">
      <sql
      classpath="${database.driver.classpath}"
      driver="${database.driver}"
      url="${database.url}"
      userid="${database.userid}"
      password="${database.password}"
      print="true"
      output="build_tables_result.log">
           <transaction src="${database.script.file}" />
      </sql>
</target>

Listing 4.4 shows the target to launch and configure Middlegen. First, a Middlegen Ant task definition is specified, and then the Middlegen task is configured.

A single output plugin is used to indicate that the application uses Hibernate (Middlegen can also create EJB or JDO mappings, using other plugins).

Note that the four tables described by the schema are explicitly named in the file. Middlegen will load these tables and attempt to deduce the relationships between them from the JDBC driver metadata. If the relationship is sufficiently complex, it can specify additional configuration data.

Example 4.4. Middlegen Task

<!-- Target to run Middlegen -->
<target name="middlegen"
      description="Run Middlegen"
      unless="middlegen.skip">

      <mkdir dir="${prefs.dir}"/>
      <echo message="Class path = ${basedir}"/>

      <taskdef name="middlegen"
           classname="middlegen.MiddlegenTask">
            <classpath>
                  <fileset dir="${lib.dir}">
                      <include name="*.jar"/>
                      <include name="*.properties"/>
                  </fileset>
            </classpath>
      </taskdef>

      <!-- Note the gui=true value. Set this to false to use in
      a command-line fashion -->
      <middlegen
            appname="${name}"
            prefsdir="${prefs.dir}"
            gui="true"
            databaseurl="${database.url}"
            driver="${database.driver}"
            username="${database.userid}"
            password="${database.password}"
            chema="${database.schema}"
            catalog="${database.catalog}"
      >

            <!-- The hibernate plugin-->
            <hibernate
                 destination="${build.hbm.dir}"
                 package="${package}"
                 javaTypeMapper=
          "middlegen.plugins.hibernate.HibernateJavaTypeMapper"
            />

            <table name="student" />
            <table name="course" />
            <table name="exam" />
            <table name="examresult" />

            <!-- You can declare additional tables and
            relationships here. You can use this to
            override or add to the JDBC derived information
            -->
            <!-- For example...
            <many2many>
            <tablea name="student"/>
            <jointable name="examresult" generate="false"/>
            <tableb name="class"/>
            </many2many>
            -->
      </middlegen>
</target>

Listing 4.5 shows the target that generates the Java sources from the *.hbm.xml files (similar to the files shown in Chapter 2) after Middlegen exits from the GUI.

Example 4.5. Building Java Source Files

<!-- creates the Java sources from the HBM files.
<target name="build_hbm" description="builds the Java sources">
     <mkdir dir="${build.src.dir}"/>
     <echo message="src dir = ${build.src.dir}"/>
     <echo message="target hbm
           files = ${build.hbm.dir}${package.dir}" />

     <taskdef name="hbm2java"
         classname="net.sf.hibernate.tool.hbm2java.Hbm2JavaTask">
           <classpath>
             <pathelement
     location="${hibernate_path}hibernate2.jar"/>
             <pathelement
     location="${hibernate_tool_path}hibernate-tools.jar"/>
             <pathelement
     location=
     "${hibernate_path}libcommons-collections-2.1.jar" />
             <pathelement
     location="${hibernate_path}libcommons-logging-1.0.3.jar" />
             <pathelement
     location="${hibernate_path}libcommons-lang-1.0.1.jar" />
             <pathelement
     location="${hibernate_path}libxerces-2.4.0.jar" />
             <pathelement
     location="${hibernate_tool_path}libjdom.jar"/>
           </classpath>
     </taskdef>

     <hbm2java config="hbm2java_config.xml"
          output="${build.src.dir}">
  <fileset
   dir="${build.hbm.dir}${package.dir}" includes="*.hbm.xml"/>
     </hbm2java>
</target>

Finally, as shown in Listing 4.6 the project provides a task to copy the resulting source and mapping files from the generated projects into the development tree.

Example 4.6. Building Java Source Files

<!-- copies the results to your project source tree.
Note that you need both the Java source and the
HBM files. -->
<target name="copy_files" description="copies results to project">
      <copy todir="${project.dir}">
             <fileset dir="hbm${package.dir}" />
             <fileset dir="${build.src.dir}${package.dir}" />
             <fileset file="${database.script.file}" />
      </copy>
</target>

</project>

Finally, as shown in Listing 4.6, the project provides a task to copy the resulting source and mapping files from the generated projects into the development tree.

Running Middlegen

Before running Middlegen as shown, you must have first successfully installed MySQL, created a database named hibernate, and installed Ant. Once you have done all this, simple open a terminal, change to the directory containing the build file, type ant, and press return.

The terminal will begin by generating output, as shown in Listing 4.7, and then will launch the Middlegen graphical interface.

Example 4.7. Initial Middlegen Output

C:devenvcascadetg-middlegen-2.0-vo>ant
Buildfile: build.xml

build_tables:
      [sql] Executing file: C:devenvcascadetg-middlegen-2.0-voschema.sql
      [sql] 33 of 33 SQL statements executed successfully

middlegen:
     [echo] Class path = C:devenvcascadetg-middlegen-2.0-vo
[middlegen] log4j:WARN No appenders could be found for logger (middlegen.Middlegen).
[middlegen] log4j:WARN Please initialize the log4j system properly.
[middlegen] Database URL:jdbc:mysql://localhost/hibernate
[middlegen]
********************************************************
[middlegen] * CTRL-Click relations to modify their cardinality     *
[middlegen] * SHIFT-Click relations to modify their directionality *
[middlegen] ********************************************************
<GUI launches>

Figure 4.2 shows the Middlegen user interface in action. The boxes can be organized to fit aesthetic requirements. Clicking on a table will let you set table-specific configuration options; clicking on a column name will allow you to set column-specific configuration options.

By interacting with the Middlegen interface, you configure the *.hbm.xml files that will be generated by Middlegen. While the interface allows you to modify the cardinality and direction of the bindings, you can't create or delete associations—this must be done in the database via foreign keys or via the Middlegen build.xml file.

The following modifications were made in the Middlegen interface for this application.

  • The key generator for all four tables is changed from assigned to native. For more information on these options, see the generator tag in Chapter 5.

  • Change the score Java type from java.lang.Long to int.

After these changes are made, the Generate button should be clicked to actually generate the *.hbm.xml files. The close box is used to exit. After Middlegen exits, the Ant build script will continue, as shown in Listing 4.8.

Example 4.8. Concluding Middlegen Output

<GUI terminates>
[middlegen] Updated preferences in C:devenvcascadetg-middlegen-2.0-voprefs
Concluding Middlegen Outputchapter4-prefs.pr

build_hbm:

copy_files:
     [copy] Copying 4 files to C:devenveclipsev3_M4workspacehibernatecomcascadetgch04
all:

BUILD SUCCESSFUL
Total time: 11 minutes 38 seconds
C:devenvcascadetg-middlegen-2.0-vo>

Generated Mapping Files

As seen in Figure 4.2, the examresult table is a many-to-many join with an additional data attribute. Because of the additional data (the score value), we can't model this using Hibernate's built-in many-to-many functionality. Instead it must be modeled as a separate class.

Listing 4.9 shows the generated *.hbm.xml file for the Examresult class (the white space has been reformatted slightly to make it more readable). Note the two many-to-one associations; these are used in lieu of a Hibernate many-to-many relationship.

Example 4.9. Generated Many-to-Many Examresult Class

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping>
<!--
    Created by the Middlegen Hibernate plugin

    http://boss.bekk.no/boss/middlegen/
    http://hibernate.sourceforge.net/
-->

<class name="com.cascadetg.ch04.Examresult" table="examresult">

    <id name="id" type="java.lang.Long" column="ID">
        <generator class="native" />
    </id>

    <property name="score" type="int" column="score"
         length="11" />

    <!-- associations —>
    <!-- bi-directional many-to-one association to Student -->
    <many-to-one name="student"
        class="com.cascadetg.ch04.Student"
        not-null="true">
        <column name="studentID" />
    </many-to-one>

    <!-- bi-directional many-to-one association to Exam -->
    <many-to-one name="exam" class="com.cascadetg.ch04.Exam"
        not-null="true" >
        <column name="examID" />
    </many-to-one>

</class>
</hibernate-mapping>

The flip side of the association with Examresult is shown in Listing 4.10 (again, the white space has been reformatted to make it more readable). The Exam mapping uses a set to manage the relationship. For more information on the set tag and the other nested portions of the mapping, see Chapter 5.

Example 4.10. Generated Exam Mapping

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "--//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping>
<!--
    Created by the Middlegen Hibernate plugin

    http://boss.bekk.no/boss/middlegen/
    http://hibernate.sourceforge.net/
-->

<class name="com.cascadetg.ch04.Exam" table="exam">

    <id name="id" type="java.lang.Long" column="ID" >
        <generator class="native" />
    </id>

    <property name="date" type="java.sql.Timestamp"
        column="date" length="14" />
    <property name="comment" type="java.lang.String"
        column="comment" length="255" />

    <!-- associations -->
    <!-- bi-directional many-to-one association to Course -->
    <many-to-one name="course"
        class="com.cascadetg.ch04.Course"
        not-null="true" >
        <column name="courseID" />
    </many-to-one>

    <!-- bi-directional one-to-many association to Examresult -->
    <set name="examresults" lazy="true" inverse="true" >
        <key>
            <column name="examID" />
        </key>
        <one-to-many class="com.cascadetg.ch04.Examresult" />
    </set>

</class>
</hibernate-mapping>

The mapping generated for the student table is shown in Listing 4.11 (extraneous white space removed).

Example 4.11. Generated Student Mapping

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping>
<!--
    Created by the Middlegen Hibernate plugin

    http://boss.bekk.no/boss/middlegen/
    http://hibernate.sourceforge.net/
-->

<class name="com.cascadetg.ch04.Student" table="student">

    <id name="id" type="java.lang.Long" column="ID" >
        <generator class="native" />
    </id>

    <property name="firstName" type="java.lang.String"
        column="firstName" length="100" />
    <property name="lastName" type="java.lang.String"
        column="lastName" length="100" />
    <property name="idString" type="java.lang.String"
        column="idString" length="20" />

    <!-- associations -->
    <!-- bi-directional one-to-many association to Examresult -->
    <set name="examresults" lazy="true" inverse="true">
        <key>
            <column name="studentID" />
        </key>
        <one-to-many class="com.cascadetg.ch04.Examresult" />
    </set>

</class>
</hibernate-mapping>

The mapping generated for the final table, course, is shown in Listing 4.12 (white space edited).

Example 4.12. Generated Course Mapping

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "--//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping>
<!--
    Created by the Middlegen Hibernate plugin

    http://boss.bekk.no/boss/middlegen/
    http://hibernate.sourceforge.net/
-->

<class name="com.cascadetg.ch04.Course" table="course" >

    <id name="id" type="java.lang.Long" column="ID" >
        <generator class="native" />
    </id>

    <property name="title" type="java.lang.String"
        column="title" length="100" />
    <property name="quarter" type="java.lang.String"
        column="quarter" length="4" />

    <!-- associations -->
    <!-- bi-directional one-to-many association to Exam -->
    <set name="exams" lazy="true" inverse="true" >
        <key>
            <column name="courseID" />
        </key>
        <one-to-many class="com.cascadetg.ch04.Exam"/>
    </set>

</class>
</hibernate-mapping>

Generated Java

Given the mapping files, Ant and Hibernate automatically generate the corresponding Java source from the *.hbm.xml files. Figure 4.3 shows an overview of the generated source.

Generated Java Source

Figure 4.3. Generated Java Source

Working with the Database

The application now has both *.hbm.xml files and corresponding Java source files. Given these files, it's a simple matter to use Hibernate to perform database operations. The sample code shown in the remainder of this chapter will use the generated files to populate the database with some data and then produce a report based on that data.

From the perspective of the classes manipulating the database via Hibernate, these are just additional examples of manipulating data with Hibernate, independent of the build system that generated the Java and mapping files.

Two classes, as shown in Figure 4.4, are used to manipulate the data. The first, GenerateData, populates the database with some test data. The second, GenerateReports, generates a tab-delimited text file, suitable for use with a spreadsheet.

Data Generation and Reporting Classes

Figure 4.4. Data Generation and Reporting Classes

Listing 4.13 demonstrates the insertion of data. Note the use of java.util.HashSet as a concrete implementation of java.util.Set. The latter is only an interface, whereas the former is a concrete implementation. For more information on sets and relationships, see Chapter 7.

Example 4.13. Generating Data

package com.cascadetg.ch04;

import java.util.HashSet;

/** Various Hibernate-related imports */
import net.sf.hibernate.*;
import net.sf.hibernate.cfg.*;

public class GenerateData
{
    /** We use this session factory to create our sessions */
    public static SessionFactory sessionFactory;

    static String[] students =
        {
            "Smith",
            "Bob",
            "123-45-6789",
            "Stevens",
            "John",
            "456-78-9012",
            "Almara",
            "Betty",
            "098-76-5432" };

    static Student[] createdStudents = new Student[3];
    static Exam midterm;
    static Exam coursefinal;

    /** Loads the Hibernate configuration information,
     * sets up the database and the Hibernate session factory.
     */
    public static void initialization()
    {
        //System.setErr(System.out);
        System.out.println("initialization");
        try
        {
            Configuration myConfiguration = new
                 Configuration();

            myConfiguration.addClass(Course.class);
            myConfiguration.addClass(Exam.class);
            myConfiguration.addClass(Examresult.class);
            myConfiguration.addClass(Student.class);

            // Sets up the session factory (used in the rest
            // of the application).
            sessionFactory =
                 myConfiguration.buildSessionFactory();

        } catch (Exception e)
        {
            e.printStackTrace();
        }
    }

    public static void main(String[] args)
    {
        initialization();
        createClass();
        createExamResults();
    }

    static void createClass()
    {
        System.out.println();
        System.out.println("createClass");

        Session hibernateSession = null;
        Transaction myTransaction = null;
        try
        {
            hibernateSession = sessionFactory.openSession();
            myTransaction =
                 hibernateSession.beginTransaction();

            Course myCourse = new Course();
            myCourse.setQuarter("Q12005");
            myCourse.setTitle("Introduction To Hibernate");
            hibernateSession.save(myCourse);

            midterm = new Exam();
            midterm.setComment("First midterm");
            midterm.setCourse(myCourse);
            hibernateSession.save(midterm);

            coursefinal = new Exam();
            coursefinal.setComment("Course final");
            coursefinal.setCourse(myCourse);
            hibernateSession.save(coursefinal);

            HashSet myExams = new HashSet();
            myExams.add(midterm);
            myExams.add(coursefinal);
            myCourse.setExams(myExams);
            hibernateSession.save(myCourse);

            for (int i = 0; i < students.length; i = i + 3)
            {
                Student newStudent = new Student();
                newStudent.setLastName(students[i]);
                newStudent.setFirstName(students[i + 1]);
                newStudent.setIdString(students[i + 2]);
                hibernateSession.save(newStudent);

                createdStudents[i / 3] = newStudent;
            }

            hibernateSession.flush();
            myTransaction.commit();

        } catch (Exception e)
        {
            e.printStackTrace();
            try
            {
                myTransaction.rollback();
            } catch (Exception e2)
            {
                // Silent failure of transaction rollback
            }
        } finally
        {
            try
            {
                hibernateSession.close();
            } catch (Exception e2)
            {
                // Silent failure of session close
            }
        }

    }

    static void createExamResults()
    {
        System.out.println();
        System.out.println("createExamResults");

        Session hibernateSession = null;
        Transaction myTransaction = null;
        try
        {
            hibernateSession = sessionFactory.openSession();
            myTransaction =
                 hibernateSession.beginTransaction();

            for (int i = 0; i < createdStudents.length; i++)
            {
                Student myStudent = createdStudents[i];
                Examresult myExamResult = new Examresult();
                myExamResult.setScore(85 + i * 4);
                myExamResult.setStudent(myStudent);
                myExamResult.setExam(midterm);
                hibernateSession.save(myExamResult);
            }

            for (int i = 0; i < createdStudents.length; i++)
            {
                Student myStudent = createdStudents[i];

                Examresult myExamResult = new Examresult();
                myExamResult.setScore(75 + i * 8);
                myExamResult.setStudent(myStudent);
                myExamResult.setExam(coursefinal);
                hibernateSession.save(myExamResult);
            }

            hibernateSession.flush();
            myTransaction.commit();

        } catch (Exception e)
        {
            e.printStackTrace();
            try
            {
                myTransaction.rollback();
            } catch (Exception e2)
            {
                // Silent failure of transaction rollback
            }
        } finally
        {
            try
            {
                hibernateSession.close();
            } catch (Exception e2)
            {
                // Silent failure of session close
            }
        }
    }
}

Once data is loaded into the database, generating a report is only a matter of retrieving data from the database. Listing 4.14 shows how the report is generated, including the use of HQL to obtain statistical information. For more information on HQL, see Chapter 8.

Example 4.14. Generating the Report

package com.cascadetg.ch04;

/** Various Hibernate-related imports */
import java.io.FileOutputStream;
import java.util.Iterator;

import net.sf.hibernate.*;
import net.sf.hibernate.cfg.*;
import net.sf.hibernate.expression.*;

public class GenerateReports
{
    /** This is used to store the resulting report */
    static StringBuffer report = new StringBuffer();

    // System constants for the new line and directory tokens
    static String newLine =
         System.getProperty("line.separator");
    static String fileSep =
         System.getProperty("file.separator");

    /** We use this session factory to create our sessions */
    static SessionFactory sessionFactory;

    static public String highScore(Exam inExam)
    {
        String hql =
            "select max(result.score) from Examresult "
                + "as result where result.exam=?";
        return getExamStats(hql, inExam);
    }

    static public String lowScore(Exam inExam)
    {
        String hql =
            "select min(result.score) from Examresult "
                + "as result where result.exam=?";
        return getExamStats(hql, inExam);
    }

    static public String averageScore(Exam inExam)
    {
        String hql =
            "select avg(result.score) from Examresult "
                + "as result where result.exam=?";
        return getExamStats(hql, inExam);
    }

    static public String getExamStats(String hql, Exam inExam)
    {

        Session hibernateSession = null;
        Transaction myTransaction = null;

        String returnVal = "0";
        try
        {
            hibernateSession = sessionFactory.openSession();
            myTransaction =
                 hibernateSession.beginTransaction();

            Query myQuery = hibernateSession.createQuery(hql);
            myQuery.setLong(0, inExam.getId().longValue());

            returnVal = myQuery.iterate().next().toString();

            myTransaction.commit();

        } catch (Exception e)
        {

            e.printStackTrace();
            try
            {
                myTransaction.rollback();
            } catch (Exception e2)
            {
                // Silent failure of transaction rollback
            }
        } finally
        {
            try
            {
                if (hibernateSession != null)
                    hibernateSession.close();
            } catch (Exception e)
            {
                // Silent failure of session close
            }
        }
        return returnVal;
    }

    static public String getExamReport(Exam inExam)
    {

        Session hibernateSession = null;
        Transaction myTransaction = null;

        java.util.Iterator result = null;
        String returnVal = "0";
        try
        {
            hibernateSession = sessionFactory.openSession();
            myTransaction =
                 hibernateSession.beginTransaction();

            // In this example, we use the Criteria API.  We
            // could also have used the HQL, but the
            // Criteria API allows us to express this query
            // more easily.

            Criteria query =
            hibernateSession.createCriteria(Examresult.class);
            query.add(Expression.like("exam", inExam));
            // Now, specify sorting by the score
            query.addOrder(Order.desc("score"));

            // Indicate that we want to grab all of the
            // associated student IDs as well.  This
            // lets us pull all of the data in a single
            // SQL statement!
            query.setFetchMode("student.idString",
                 FetchMode.EAGER);

            // This actually performs the database request,
            // based on the query we've built.
            result = query.list().iterator();

            while (result.hasNext())
            {
                Examresult myExamResult =
                     (Examresult)result.next();
                report.append(myExamResult.
                     getStudent().getIdString());
                report.append("	");
                report.append(myExamResult.getScore());
                report.append(newLine);
            }

            myTransaction.commit();

        } catch (Exception e)
        {
            e.printStackTrace();
            try
            {
                myTransaction.rollback();
            } catch (Exception e2)
            {
                // Silent failure of transaction rollback
            }
        } finally
        {
            try
            {
                if (hibernateSession != null)
                    hibernateSession.close();

            } catch (Exception e)
            {
                // Silent failure of session close
            }
        }
        return returnVal;
    }

    static public void generateReport()
    {
        Session hibernateSession = null;
        Transaction myTransaction = null;

        java.util.Iterator result = null;
        try
        {
            hibernateSession = sessionFactory.openSession();
            myTransaction =
                 hibernateSession.beginTransaction();

            // In this example, we use the Criteria API.  We
            // could also have used the HQL, but the
            // Criteria API allows us to express this query
            // more easily.

            Criteria myQuery =
                hibernateSession.createCriteria(Course.class);

            result = myQuery.list().iterator();

            while (result.hasNext())
            {
                Course myCourse = (Course)result.next();
                Iterator exams =
                     myCourse.getExams().iterator();

                report.append(myCourse.getTitle());
                report.append(newLine);
                report.append(myCourse.getQuarter());
                report.append(newLine);
                report.append(newLine);

                while (exams.hasNext())
                {
                    Exam myExam = (Exam)exams.next();
                    report.append(myExam.getComment());
                    report.append(newLine);

                    report.append("High Score: 	");
                    report.append(highScore(myExam));
                    report.append(newLine);

                    report.append("Low Score: 	");
                    report.append(lowScore(myExam));
                    report.append(newLine);

                    report.append("Average Score: 	");
                    report.append(averageScore(myExam));
                    report.append(newLine);
                    report.append(newLine);

                    getExamReport(myExam);
                    report.append(newLine);
                }

            }

            myTransaction.commit();

        } catch (Exception e)
        {
            e.printStackTrace();
            try
            {
                myTransaction.rollback();
            } catch (Exception e2)
            {
                // Silent failure of transaction rollback
            }
        } finally
        {
            try
            {
                if (hibernateSession != null)
                    hibernateSession.close();
            } catch (Exception e)
            {
                // Silent failure of session close
            }
        }
    }

    static public void initialization()
    {
        try
        {
            Configuration myConfiguration = new
                 Configuration();

            myConfiguration.addClass(Course.class);
            myConfiguration.addClass(Exam.class);
            myConfiguration.addClass(Examresult.class);
            myConfiguration.addClass(Student.class);

            // Sets up the session factory (used in the rest
            // of the application).
            sessionFactory =
                 myConfiguration.buildSessionFactory();

        } catch (Exception e)
        {
            e.printStackTrace();
        }
    }

    public static void main(String[] args)
    {
        // Places the generated report in the same directory
        // as this Java source file.
        java.io.File reportFile =
            new java.io.File(
                "com"
                    + fileSep
                    + "cascadetg"
                    + fileSep
                    + "ch04"
                    + fileSep
                    + "report.txt");

        // If the report file doesn't exist, generate the
        // default data set.
        if (!reportFile.exists())
        {
            GenerateData.main(null);
        }

        // Set up our session factory
        initialization();

        // Do all of the report generation
        generateReport();

        // Echo the results to the console
        System.out.println(report.toString());

        // Try to write the results to the report file
        try
        {
            FileOutputStream myOutputStream =
                new FileOutputStream(reportFile);
            myOutputStream.write(report.toString().getBytes());
        } catch (Exception e)
        {
            e.printStackTrace();
        }
    }
}

When this application runs, it will generate the report file. Before looking at the report, Listing 4.15 shows the SQL generated by Hibernate during the course of the run. While the SQL shown may seem complex, it's a lot easier to look at this generated SQL than to write the SQL by hand. Several statements are much more complex than what one might easily write—in particular, the single lengthy statement which fetches both the Examresults and the associated Student records with a single query. Compare that SQL statement with the generating code, as shown in the GenerateReports.getExamReport().

Example 4.15. SQL Generated by Hibernate

Hibernate: select this.ID as ID0_, this.title as title0_, this.quarter as quarter0_ from
SQL Generated by Hibernate course this where 1=1
Hibernate: select exams0_.ID as ID__, exams0_.courseID as courseID__, exams0_.ID as ID0_,
SQL Generated by Hibernate exams0_.date as date0_, exams0_.comment as comment0_, exams0_.courseID as courseID0_ from
SQL Generated by Hibernate exam exams0_ where exams0_.courseID=?
Hibernate: select max(examresult0_.score) as x0_0_ from examresult examresult0_ where 
SQL Generated by Hibernate(examresult0_.examID=? )
Hibernate: select min(examresult0_.score) as x0_0_ from examresult examresult0_ where 
SQL Generated by Hibernate(examresult0_.examID=? )
Hibernate: select avg(examresult0_.score) as x0_0_ from examresult examresult0_ where 
SQL Generated by Hibernate(examresult0_.examID=? )
Hibernate: select this.ID as ID3_, this.score as score3_, this.studentID as studentID3_,
SQL Generated by Hibernate this.examID as examID3_, student1_.ID as ID0_, student1_.firstName as firstName0_,
SQL Generated by Hibernate student1_.lastName as lastName0_, student1_.idString as idString0_, exam2_.ID as ID1_,
SQL Generated by Hibernate exam2_.date as date1_, exam2_.comment as comment1_, exam2_.courseID as courseID1_,
SQL Generated by Hibernate course3_.ID as ID2_, course3_.title as title2_, course3_.quarter as quarter2_ from
SQL Generated by Hibernate examresult this left outer
outer join exam exam2_ on this.examID=exam2_.ID left outer join join student student1_ on
SQL Generated by Hibernate this.studentID=student1_.ID left
course course3_ on exam2_.courseID=course3_.ID where this.examID like ? order by this
SQL Generated by Hibernate.score desc
Hibernate: select max(examresult0_.score) as x0_0_ from examresult examresult0_ where 
SQL Generated by Hibernate(examresult0_.examID=? )
Hibernate: select min(examresult0_.score) as x0_0_ from examresult examresult0_ where 
SQL Generated by Hibernate(examresult0_.examID=? )
Hibernate: select avg(examresult0_.score) as x0_0_ from examresult examresult0_ where 
SQL Generated by Hibernate(examresult0_.examID=? )
Hibernate: select this.ID as ID3_, this.score as score3_, this.studentID as studentID3_,
SQL Generated by Hibernate this.examID as examID3_, student1_.ID as ID0_, student1_.firstName as firstName0_,
SQL Generated by Hibernate student1_.lastName as lastName0_, student1_.idString as idString0_, exam2_.ID as ID1_,
SQL Generated by Hibernate exam2_.date as date1_, exam2_.comment as comment1_, exam2_.courseID as courseID1_,
SQL Generated by Hibernate course3_.ID as ID2_, course3_.title as title2_, course3_.quarter as quarter2_ from
SQL Generated by Hibernate examresult this left outer join student student1_ on this.studentID=student1_.ID left
SQL Generated by Hibernate outer join exam exam2_ on this.examID=exam2_.ID left outer join course course3_ on exam2_
SQL Generated by Hibernate.courseID=course3_.ID where this.examID like ? order by this.score desc

Finally, the generated report, a tab-delimited text file, can easily be opened in any popular spreadsheet software and formatted and edited as needed, as shown in Figure 4.5.

Generated Report

Figure 4.5. Generated Report

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
13.59.234.214