Simple Database query always empty (newbie)

quaidbrown · **Joined:** Tue May 20, 2008 1:09 pm **Posts:** 5

Hello, I'm developing an application using Seam, which integrates Hibernate.

I've created an Entity Bean named EmailGather, which holds a simple string (email address) and a boolean.

I have a JSF/Seam xhtml page that gathers the info from the user, and puts it into the database.

This works great, so I know that my bean is mapped to my table correctly.

My problem is that the query never returns results. I'm not sure what it's querying, exactly, and if it ever actually looks at the tables in my database.

I want to query before I add a new entry, to see if there is already an entry with the same email address. I am basing my query off of this:

Code:

http://www.hibernate.org/hib_docs/entitymanager/reference/en/html/objectstate.html

List cats = em.createQuery(
    "select cat from Cat as cat where cat.birthdate < ?1")
    .setParameter(1, date, TemporalType.DATE)
    .getResultList();

I am not sure how much case matters, or exactly which fields I am referring to in this query. Am I referencing the Entity bean's fields when I say "select emailAddress" or is that referring to the table?

And here is my query:

Code:

Query q = em.createQuery("select emailAddress from EmailGather as e where e.emailAddress=?1").setParameter(1, gatheredEmail.getEmailAddress());
      List existing = q.getResultList();
      
      if (existing.size() != 0) {
         em.remove(gatheredEmail);
      }

List existing is always of size 0, even though there is an entry in the database with the same email address. I used the debugger and verified that the query was receiving the argument correctly... the email address that comes from the entity bean is buried down in the hash table it uses to store arguments.

It appears to execute "correctly" because the JBoss Console says this:

Code:

12:06:03,682 INFO  [STDOUT] Hibernate: 
    select
        emailgathe0_.emailaddress as col_0_0_ 
    from
        emailgather emailgathe0_ 
    where
        emailgathe0_.emailaddress=?

My entity bean:

Code:

package org.domain.EmailGather2.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.validator.NotNull;
import org.jboss.seam.annotations.Name;

@Entity
@Name("emailgatherer")
@Table(name="emailgather")
public class EmailGather {

   private long id;
   private String emailAddress;
   private Boolean allUpdates;
   
   public EmailGather(){
      System.out.println("gothere1");
   }
   
   public EmailGather(String emailAddress, Boolean allUpdates){
      this.emailAddress = emailAddress;
      this.allUpdates = allUpdates;
      System.out.println("gothere2");
   }
   
   
   @Id @GeneratedValue(strategy=GenerationType.AUTO)
   @NotNull
   public long getId() {System.out.println("gothere3");return id;}
   public void setId(long id) {System.out.println("gothere4");this.id = id;}
   
   
   @Column(name="emailaddress")
   @NotNull
   public String getEmailAddress() {
      return emailAddress;
   }
   public void setEmailAddress(String emailAddress) {
      this.emailAddress = emailAddress;
   }
   
   @Column(name="allupdates")
   @NotNull
   public Boolean getAllUpdates() {
      return allUpdates;
   }
   public void setAllUpdates(Boolean allUpdates) {
      this.allUpdates = allUpdates;
   }
}

Full stateless session bean:

Code:

package org.domain.EmailGather2.session;

import java.util.List;

import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.domain.EmailGather2.entity.EmailGather;
import org.jboss.seam.annotations.Name;
import org.jboss.seam.annotations.In;
import org.jboss.seam.annotations.Logger;
import org.jboss.seam.log.Log;
import org.jboss.seam.faces.FacesMessages;

@Stateless
@Name("emailGatherAction")
public class EmailGatherActionBean implements EmailGatherAction {
   
    @Logger private Log log;
   
    @In
    FacesMessages facesMessages;
    
    @In(value="emailgatherer", required=true)
    private EmailGather gatheredEmail;
    
    
    @PersistenceContext
    private EntityManager em;
    public String emailGatherAction() {
       System.out.println(gatheredEmail.getEmailAddress());
       Query q = em.createQuery("select emailAddress from EmailGather as e where e.emailAddress=?1").setParameter(1, gatheredEmail.getEmailAddress());
      List existing = q.getResultList();
      
      if (existing.size() != 0) {
         em.remove(gatheredEmail); // never gets called
      }

      em.persist(gatheredEmail);
      log.info("Gathered email address:#{gatheredEmail.emailaddress}. Add to mailing list:#{gatheredEmail.allupdates}");
      if (gatheredEmail.getAllUpdates() == true) {
         FacesMessages.instance().add("Thanks! blah blah1.");
      } else {
         FacesMessages.instance().add("Thanks! blah blah2");
      }
      return "/home.xhtml";
   }
}

And my JSF code:

Code:

<!DOCTYPE composition PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
                      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<ui:composition xmlns="http://www.w3.org/1999/xhtml"
                xmlns:s="http://jboss.com/products/seam/taglib"
                xmlns:ui="http://java.sun.com/jsf/facelets"
                xmlns:f="http://java.sun.com/jsf/core"
                xmlns:h="http://java.sun.com/jsf/html"
                xmlns:rich="http://richfaces.org/rich"
                template="layout/template.xhtml">
     <ui:define name="body">
     
     
    <h:form id="emailgather" styleClass="edit">
    
        <rich:panel>
            <f:facet name="header"></f:facet>
         <s:decorate id="emailaddressDecoration" template="layout/edit.xhtml">
                <ui:define name="label">Email Address</ui:define>
                <h:inputText id="emailaddress"
                            required="true"
                             value="#{emailgatherer.emailAddress}"/>
            </s:decorate>

            <s:decorate id="allupdatesDecoration" template="layout/edit.xhtml">
                <ui:define name="label">Add me to your mailing list</ui:define>
                 <h:selectBooleanCheckbox id="allupdates"
                                    required="true"
                                   value="#{emailgatherer.allUpdates}"/>
                 <h:outputLabel for="allupdates">
                    <h:outputText id="allupdatesLabel" value="(You'll receive all our interesting updates, not just our launch announcement)" />
                 </h:outputLabel>
            </s:decorate>

            <div style="clear:both">
                <span class="required">*</span> 
                required fields
            </div>
           <div class="actionButtons">
              <h:commandButton type="submit" value="Sign me up!" action="#{emailGatherAction.emailGatherAction}"/>
           </div>
            <h:messages globalOnly="true" styleClass="message"/>
        </rich:panel>
                 


                    
        
        
    </h:form>
 </ui:define>    
</ui:composition>

Table description in Postgres:

Code:

Table name: emailgather
Column         |Type                   |Modifiers
emailaddress   character(256)      not null
allupdates        boolean                not null
id                    integer                  not null default nextval('emailgather_id_seq'::regclass)
Indexes:
    "emailgather_pkey" PRIMARY KEY, btree (id)

Any advice is appreciated!

ninju.bohra · **Joined:** Mon May 12, 2008 8:42 pm **Posts:** 2

I am not familiar with the

Code:

.getResultList()

method that you used.

For HQL queries you should be referening the attributes in the objects...the goal of Hibernate to isolate (i.e. centralize) all of the database columns references to the mapping files.

For our code we would have done

Code:

public EmailGather getEmailGatherByAddress(String addressToFind) {
        String searchCondtion = "(emailAddress = '" + addressToFind + "')"); 
        String hqlString = "from EmailGather where " + searchCondition;
        Query q = session.createQuery(hqlString);
        List objectsFound = q.list();
        if (objectsFound.size() > 0) {
             return objectsFound.get(0);
        } else {
             return null;
        }
}

quaidbrown · **Joined:** Tue May 20, 2008 1:09 pm **Posts:** 5

I'm using the Query class from javax.persistence.Query; It doesn't have a list() method. The code I was writing was EJB-QL rather than HQL. Do you use different packages for these classes (Query, List, etc) when using different QLs?

quaidbrown · **Joined:** Tue May 20, 2008 1:09 pm **Posts:** 5

I got it to work with this code:

Code:

     String searchCondition = "emailAddress = '" + gatheredEmail.getEmailAddress() + "'";
        String hqlString = "from EmailGather where " + searchCondition;
        Query q = em.createQuery(hqlString);
        List objectsFound = q.getResultList();
        
        
        if (objectsFound.size() > 0) {
             System.out.println("found something");
             for(Object o : objectsFound){
                em.remove(o);
             }
        } else {
            System.out.println("didn't find anything");
        } 

Thanks a ton for your advice!

I'd still like to know why the EJB-QL wasn't working. This hql stuff confuses me because there is no "select" at the beginning :P

sanne.grinovero · **Posted:** Tue May 20, 2008 8:32 pm

Hi,
glad you have it working, still you should really use a parameter for you query,
something like

Code:

Query q = em.createQuery("from EmailGather as e where e.emailAddress='?1'").setParameter(1, gatheredEmail.getEmailAddress()); 

Also you may like the Hibernate Validators:

Code:

@Column(name="emailaddress")
   @NotNull
   @Email
   public String getEmailAddress() {
      return emailAddress;
   } 

(Adding @Email to the property)