<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
  <persistence-unit name="JPA-ScratchPU" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <class>jpascratch.dao.Library</class>
    <class>jpascratch.dao.Book</class>
    <properties>
            <property name="hibernate.connection.url" value="jdbc:mysql://10.0.64.200:3306/test"/>
            <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
            <property name="hibernate.connection.password" value="password"/>
            <property name="hibernate.connection.username" value="username"/>
            <property name="hibernate.cache.provider_class" value="org.hibernate.cache.NoCacheProvider"/>
            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLInnoDBDialect"/>
    </properties>
  </persistence-unit>
</persistence>

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/
package jpascratch;

import java.util.HashMap;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.NoResultException;
import javax.persistence.NonUniqueResultException;
import javax.persistence.Persistence;
import javax.persistence.Query;
import jpascratch.dao.Book;
import org.apache.log4j.Logger;

/**
*
* @author dhoyt
*/
public class DriverBugDemo {
    private static Logger LOG = Logger.getLogger(DriverBugDemo.class.getCanonicalName());

    public void demoTheBug() {

        LOG.debug("begin.");
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("JPA-ScratchPU", new HashMap());
        EntityManager em = emf.createEntityManager();

        // some book titles
        String titles[] = {"Frankenstein", "War and Peace", "The Inspector Ggnural", null};

        // make some books out of them.
        EntityTransaction et = em.getTransaction();
        et.begin();
        for (String title : titles) {
            Book b = new Book();
            b.setTitle(title);
            em.persist(b);
        }
        et.commit();

        // now, try to look them up.
        Query namedQ = em.createNamedQuery("Book.findByTitle");
        String title = null;

        try {
            // War and Peace should be fine using hql named query.
            title = "War and Peace";
            namedQ.setParameter("title", title);

            Book b = (Book) namedQ.getSingleResult();
            LOG.debug("Found book with id " + b.getId() + " and title " + b.getTitle());

            title = null;

            // now to test a null parameter first using a native query
            // returns the book we made earlier.
            Query nativeQuery = em.createNativeQuery("select * from book where title " + (title == null ? "is null" : "=" + title), Book.class);
            b = (Book) nativeQuery.getSingleResult();
            LOG.debug("Found book with id " + b.getId() + " and title " + b.getTitle());

            // then again using the hql named query
            // this will throw NoResultException.
            // looking at the show_query=true output, we find that
            // it's because of this sql:  select .... where title=null
            // which should read ... where title is null
            namedQ = em.createNamedQuery("Book.findByTitle");
            namedQ.setParameter("title", title);
            b = (Book) namedQ.getSingleResult();
            LOG.debug("Found book with id " + b.getId() + " and title " + b.getTitle());

        } catch (NoResultException nre) {
            LOG.debug("sorry, no book by title " + title);
        } catch (NonUniqueResultException nure) {
            LOG.debug("Too many results.  Please narrow your query.");
        }

    }
}

/*
* To change this template, choose Tools | Templates
* and open the template in the editor.
*/

package jpascratch.dao;

import java.io.Serializable;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

/**
*
* @author dhoyt
*/
@Entity
@Table(name = "book")
@NamedQueries({@NamedQuery(name = "Book.findById", query = "SELECT b FROM Book b WHERE b.id = :id"), @NamedQuery(name = "Book.findByTitle", query = "SELECT b FROM Book b WHERE b.title = :title")})
public class Book implements Serializable {
    private static final long serialVersionUID = 1L;
   
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Integer id;
   
    @Column(name = "title")
    private String title;
   
    @ManyToOne(fetch=FetchType.EAGER, cascade=CascadeType.ALL)
    @JoinColumn(name = "library_id", referencedColumnName = "id")
    private Library libraryId;

    public Book() {
    }

    public Book(Integer id) {
        this.id = id;
    }

    public Book(Integer id, String title) {
        this.id = id;
        this.title = title;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public Library getLibraryId() {
        return libraryId;
    }

    public void setLibraryId(Library libraryId) {
        this.libraryId = libraryId;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof Book)) {
            return false;
        }
        Book other = (Book) object;
        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "jpascratch.dao.Book[id=" + id + "]";
    }

}

1282 [main] DEBUG jpascratch.DriverBugDemo  - Found book with id 142 and title War and Peace
1294 [main] DEBUG jpascratch.DriverBugDemo  - Found book with id 144 and title null
1297 [main] DEBUG jpascratch.DriverBugDemo  - sorry, no book by title null

... WHERE b.title = :title OR (b.title is null AND :title is null)