Persisting lecture instances

exercise No. 41

Create comment in forum


Read the mi-maven-archetype-jpa-hibernate archetype's documentation to derive a Maven project from it using the MI archetype catalog. For the sake of a simplified discussion regarding package names choose the following groupId and artifactId values:

<project xmlns=...>



  1. Get used to JPA using the existing data model first. Execute and watch the generated SQL statements.


    Configure log4j2.xml using log level debug.

  2. Analyze the generated database schema.


    SHOW CREATE TABLE Airline is your friend.

  3. Execute Visualize the JPA generated SQL statements and explain the JDBC layer messages including search parameter values.


    Configure log4j2.xml using log level trace.

  4. Replace the existing data model in favour of an application persisting Lecture instances. Each lecture will be described by its title e.g. Structured Data and Applications. Persist the following three lectures:

    • Structured data and applications

    • Software development

    • Database systems

  5. Modify to retrieve all lectures from your database having a title starting with the letter S.


  6. A best practices advice recommends supplying a business key in addition to a surrogate primary key. Read Annotation Type Column and define a unique, non-null integer property courseNumber in your Lecture class.

    Check your generated Mysql schema.

  7. Query for all lectures starting with the letter S and having a courseNumber greater than 2000.


  1. Execution of class yields:

    DEBUG [main] util.LogHelper ( - PersistenceUnitInfo [
    name: strategy_drop-and-create 
    INFO  [main] cfg.Environment ( - HHH000206: not found 
    INFO  [main] ...  Connection properties: {user=hdmuser, password=XYZ} 
    INFO  [main] ...  Autocommit mode: false 
    INFO  [main] dialect.Dialect ... Using dialect: org.hibernate.dialect.MySQL5InnoDBDialect 
        drop table if exists Airline 
        create table Airline ( 
            id bigint not null auto_increment,
            icaoCode varchar(255),
            name varchar(255),
            primary key (id)
        ) ENGINE=InnoDB
    insert into Airline (icaoCode, name) values (?, ?) 

    Using <persistence-unit name="jpa-recreate" ...> from file persistence.xml.

    Hibernate may as well use a configuration file in favour of JPA persistence.xml.

    Opening a JDBC connection as being defined in persistence.xml:

    <property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost/hdm" />
    <property name="javax.persistence.jdbc.user" value="hdmuser" />
    <property name="javax.persistence.jdbc.password" value="XYZ" />
    <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />

    Yeah, buddy: Log files may reveal clear text passwords!

    Allowing grouping of statements into transactions.

    Choosing the desired Mysql backend.

    Drop table Airline if exists. This is due to the <property name="" value="create-drop" /> definition in persistence.xml.

    Creating the database table.

    Inserting data using PreparedStatements. Notice the two question marks (?, ?) representing JDBC PreparedStatement place holders.

    CREATE TABLE Airline (
      id bigint(20) NOT NULL AUTO_INCREMENT,
      icaoCode varchar(255) DEFAULT NULL,
      name varchar(255) DEFAULT NULL,
      PRIMARY KEY (id)
  3. The log output contains:

    select as id1_0_,
           airline0_.icaoCode as icaoCode2_0_,
  as name3_0_
    from Airline airline0_
    where airline0_.icaoCode=?
    TRACE [main] sql.BasicBinder ( - binding parameter [1] as [VARCHAR] - [DLH]

    Removing the SQL table alias this may be simplified:

    select id, icaoCode, name
    from Airline
    where airline0_.icaoCode=?
    TRACE [main] sql.BasicBinder ( - binding parameter [1] as [VARCHAR] - [DLH]

    This is an expected statement again using a placeholder representing the icaoCode search value.