0

I have 2 tables Sponsors and Study. The columns in Sponsor and Study tables are:

Sponsors table structure:

SponsorId: int primary key auto_increment,
SponsorName: varchar(30) unique not null,
Address: varchar(255)

Study table structure:

StudyId int primary key auto_increment,
StudyName varchar(30) unique not null,
SponsorId int not null foreign key to Sponsors table.

Study to Sponsor has a ManyToOne relationship.

I am trying to display the fields in Sponsors and Study in a datatable and perform create, update and delete operations on it.

This is the method I have used for populating the datatable.

public List retrieveStudy() {
  Query query=getEntityManager().createNativeQuery("select"+    
              "s.studyId,s.studyName,s.sponsorId ,sp.sponsorName as"+    
              "sponsorName,sp.address from Study s left join Sponsors sp", 
               Study.class);
    return query.getResultList();
}

While displaying the datatable for Study I want to display the SponsorName instead of SponsorId so, I have used the join query for getting the SponsorName.

Now I am able to display the SponsorName in the datatable but when I add a record I am getting the following exception.

[EclipseLink-4002] (Eclipse Persistence Services - 2.2.0.v20110202-r8913):    orgException .eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'SponsorName' in 'field list'
Error Code: 1054

This is my entity class for Study

public class Study implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "StudyId")
private Integer studyId;
@Basic(optional = false)
@NotNull
@Size(min = 1, max = 50)
@Column(name = "StudyName")
private String studyName;
@JoinColumn(name = "SponsorId", referencedColumnName = "SponsorId")
@ManyToOne(optional = false)
private Sponsors sponsorId;

@Column(name="SponsorName")
private String sponsorName;

// Constructors getters,setters
}

Jsf page

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"   
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml"
  xmlns:h="http://java.sun.com/jsf/html"
  xmlns:f="http://java.sun.com/jsf/core"
  xmlns:ui="http://java.sun.com/jsf/facelets"
  xmlns:p="http://primefaces.org/ui">

    <ui:composition template="/SRAtemplate.xhtml">
        <ui:define name="head">
            <title> Study </title>            
        </ui:define>
    <ui:define name="heading">
        Study 
    </ui:define>
    <ui:define name="body">
        <br/>
        <p:growl id="msg" autoUpdate="true"/>
        <br/>
        <h:form id="addstudyform">
            <p:panelGrid columns="2" styleClass="panelgridstyle">

                <f:facet name="header">
                    Add Study
                </f:facet>

                <h:outputLabel for="StudyName" value="StudyName*:"/>
                <p:inputText id="StudyName" value="#studyController.selected.studyName}" maxlength="20" 
                     required="true" requiredMessage="StudyName must be   entered"
                         validatorMessage="StudyName must be an alphanumeric value">
                    <f:validateRegex pattern="[a-zA-Z0-9\s]+"/>
                </p:inputText>

                <h:outputLabel for="Sponsor" value="Sponsor*:"/>
                <p:selectOneMenu id="Sponsor" value="#{studyController.selected.sponsorId}" effect="fade" effectSpeed="0" 
                       required="true" requiredMessage="Sponsor must be selected">  
                    <f:selectItems value="#{sponsorsController.itemsAvailableSelectOne}" var="sponsor" 
                           itemLabel="#{sponsor.sponsorId}" itemValue="#{sponsor}"/>  
                </p:selectOneMenu>

                <p:commandButton id="btnSaveStudy" value="Save" actionListener="#{studyController.createStudy()}"
                                 update=":studyform:studydt"/>
                <p:commandButton id="btnCancelStudy" value="Clear" type="reset" update=":addstudyform"/>

            </p:panelGrid>
        </h:form>
        <br/>
        <br/>
        <h:form id="studyform" style="alignment-adjust:middle">
            <p:dataTable id="studydt" value="#{studyController.retrieveStudy()}" var="item" 
                         paginator="true" rows="15" emptyMessage="No Records Found">

                <p:column filterStyleClass="filterstyle" filterBy="#{item.studyName}"
                          filterMatchMode="startsWith" style="text-align: left;">
                    <f:facet name="header"> StudyName </f:facet>
                    <h:outputText value="#{item.studyName}"/>
                </p:column>

                <p:column filterStyleClass="filterstyle" filterBy="#{item.sponsor}"
                              filterMatchMode="startsWith" style="text-align: center;">
                        <f:facet name="header"> Sponsor </f:facet>
                        <h:outputText value="#{item.sponsorName}"/>
                </p:column>                 

            </p:dataTable>
            </h:form>
    </ui:define>
    </ui:composition>
</html>

This is the perisistence.xml file

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.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_2_0.xsd">
   <persistence-unit name="ABCPU" transaction-type="JTA">
       <jta-data-source>jdbc/ABC</jta-data-source>
       <exclude-unlisted-classes>false</exclude-unlisted-classes>           
   </persistence-unit>
 <properties>
        <property name="eclipselink.ddl-generation.output-mode" value="database"/>
        <property name="eclipselink.jdbc.batch-writing" value="Buffered"/>
        <property name="eclipselink.logging.level" value="INFO"/>
        <property name="eclipselink.ddl-generation" value="create-or-extend-tables"/>
    </properties>
</persistence>

Update

I think I am getting the error because I have added the SponsorName column in Study entity.

Can we map the columns from one entity in another?

If yes, Can some one suggest me how to do it.

jahnavi
  • 29
  • 2
  • 10
  • Well apparently the study database table does not contain a column 'SponsorName'. Which makes sense since I would expect that to be found in the sponsors table and entity. – Gimby Apr 08 '13 at 13:04
  • Enable logging and include the SQL, check that your mappings are correct. – James Apr 08 '13 at 14:14
  • Gimby, Yes please. the Study table doesn't contain SponsorName. But I want to retrieve 'SponsorName' based on SponsorID.(I am facing the same problem as jahnavi posted) – Sree Rama Apr 08 '13 at 15:28
  • James, would you please elaborate. The SQL query works good in MySQL client; and it works good with or without using key word LEFT JOIN. But the JPA native query used with "LEFT JOIN" fails while using with POJOS. – Sree Rama Apr 08 '13 at 15:32
  • @Gimby, I have included the SponsorName in the Study entity to display it in the Study datatable using native query for joining Study and Sponsor tables. Study table has a foreign key SponsorId to the Sponsors table. Is there any other way to display the SponsorName in Study datatable. – jahnavi Apr 09 '13 at 06:39
  • @James, Can you please elaborate your answer. – jahnavi Apr 09 '13 at 06:41
  • You mention 2 tables, but there are more than that or your native query is wrong. The native query SQL uses Sponsors and Studymaster, and the Study entity would default to use a Study table. So its hard to figure out what else you are missing with the incomplete mappings and information. James asked you to turn logging on, which is described here http://wiki.eclipse.org/EclipseLink/Examples/JPA/Logging – Chris Apr 12 '13 at 15:07
  • my guess is that the problem is not with the native query itself, but with the Studymaster entity mappings and its relationship to Study. Is there any reason why you are using a native query instead of JPQL? Something like createQuery("select s from Studymaster s").getResultList();. At the very least, you should execute the JPQL and compare the generated query to what you are using with the native query to see what might be going wrong. – Chris Apr 12 '13 at 15:09
  • @Chris, I am getting an IllegalArgumentException when i gave jpql query like this: getEntityManager().createQuery("select s from Study s JOIN Sponsors sp where s.SponsorId=sp.SponsorId",Study.class).getResultList(); – jahnavi Apr 16 '13 at 08:45
  • @jahnavi that is because that is SQL not JPQL. Is there a relationship between Sponsors and study in your entities? If you would use that "select s from Study s join s.sponsor sponsor". – Chris Apr 16 '13 at 12:17
  • @Chris, The Study and Sponsors have manytoone relationship between them. I have tried the query as you suggested but still I am getting the same exception. I have updated my question. I am using native query. Can you tell me where I might be doing wrong. – jahnavi Apr 17 '13 at 09:36
  • @jahnavi I don't really understand your many problems, as you jump around between them. The database you've shown does not match your entities - Study maps a sponsorName field, which the table doesn't seem to have. So JPA will get an exception when ever it inserts or queries for this entity. And you are still using a native query when you do not need to - you should fix the exceptions you get with JPQL before you can hope to get a native query that builds entities to work. Once you have that working, you can get the sponsorName field when querying the Study entity through the query. – Chris Apr 17 '13 at 13:15
  • @Chris, Sorry for confusing you. I have mapped the sponsorName in Study because I want to display the sponsorName instead of sponsorId in the datatable for Study. I have updated my question can you please look at it and give me some suggestion. – jahnavi Apr 23 '13 at 12:03
  • 1
    You really should start over with a new question, starting with what you are trying to accomplish and why. From the looks of it, you are erronously using JPA just to issue native SQL statements. its supported, but not really using JPA the way it was meant to be used. – Chris Apr 23 '13 at 12:46

2 Answers2

2

It seems what you are trying to do is work how you want things displayed into the entity design, which seems like a bad idea. When you want to display things differently, you will be stuck changing the entire app.

You cannot map a field from one table/entity in another in the way you are trying. You have mapped your Sponsor entity to the sponsor table - StudyName exists in a different table so you will always get an exception reading or writing this entity. Create the entity the way you would expect to use it in the application - I don't think it makes sense to have a studyName in a Sponsor java object, or a sponsorName in a study object. Wouldn't it make more sense to have a getSponsorName that checked if there were an associated sponsor and called getName on it, rather than try to store the name in two spots?

There are many options to get fields and or entity combinations back if that is what you want. JPQL can return anything, for instance 

"SELECT s.studyId, s.studyName, sp.sponsorName, sp.address FROM Study s left join sp.sponsorId sp"

will return you a list of Object[]s containing the field values. Or 

"SELECT s, sp.sponsorName FROM Study s left join sp.sponsorId sp"

returns the list of Object[]s containing Study objects and the referenced sponsorName.

You can also use native queries and map the results using ResultSetMappings to get the same results.

Chris
  • 20,138
  • 2
  • 29
  • 43
0

My guess is it is a case issue. Try defining your @Column annotations as all uppercase.

If you really want to use camel case, then you need to quote the columns names, i.e. @Column(name="'StudyName'").

There is also the persistence unit property, "eclipselink.jpa.uppercase-column-names"="true" that will force all column names to uppercase.

James
  • 17,965
  • 11
  • 91
  • 146
  • Can you provide snippet of table design with PK, FK and all not-null fields and its data types? It is understood that Study and Sponsors tables have many to one relation on SponsorID. – Sree Rama Apr 22 '13 at 06:07