Persisting lecture instances

exercise No. 41

Q:

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=...>

  <groupId>jpaintro</groupId>
  <artifactId>university</artifactId>
  ...
</project>

Tasks:

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

    Tip

    Configure log4j2.xml using log level debug.

  2. Analyze the generated database schema.

    Tip

    SHOW CREATE TABLE Airline is your friend.

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

    Tip

    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 jpaintro.university.SearchByIcao to retrieve all lectures from your database having a title starting with the letter S.

    Tip

  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.

A:

  1. Execution of class jpaintro.university.CreateAirline yields:

    ...
    DEBUG [main] util.LogHelper (LogHelper.java:102) - PersistenceUnitInfo [
    name: strategy_drop-and-create 
    ...
    INFO  [main] cfg.Environment (Environment.java:213) - HHH000206: hibernate.properties 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 hibernate.properties 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="hibernate.hbm2ddl.auto" 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.

  2. SHOW CREATE TABLE Airline
      ...
    CREATE TABLE Airline (
      id bigint(20) NOT NULL AUTO_INCREMENT,
      icaoCode varchar(255) DEFAULT NULL,
      name varchar(255) DEFAULT NULL,
      PRIMARY KEY (id)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
  3. The log output contains:

    ...
    select airline0_.id as id1_0_,
           airline0_.icaoCode as icaoCode2_0_,
           airline0_.name as name3_0_
    from Airline airline0_
    where airline0_.icaoCode=?
    ...
    TRACE [main] sql.BasicBinder (BasicBinder.java:65) - 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 (BasicBinder.java:65) - binding parameter [1] as [VARCHAR] - [DLH]
    ...

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