jpa 2.0 - Exception [EclipseLink-4002] while using native query for joining two tables -
i have 2 tables sponsors , study. columns in sponsor , 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 sponsors table. study sponsor has manytoone relationship.
i trying display fields in sponsors , study in datatable , perform create, update , delete operations on it.
this method have used populating datatable.
public list retrievestudy() { query query=getentitymanager().createnativequery("select"+ "s.studyid,s.studyname,s.sponsorid ,sp.sponsorname as"+ "sponsorname,sp.address study s left join sponsors sp", study.class); return query.getresultlist(); } while displaying datatable study want display sponsorname instead of sponsorid so, have used join query getting sponsorname.
now able display sponsorname in datatable when add record getting 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 entity class 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 entered" validatormessage="studyname must 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 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 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 getting error because have added sponsorname column in study entity.
can map columns 1 entity in another?
if yes, can 1 suggest me how it.
it seems trying work how want things displayed entity design, seems bad idea. when want display things differently, stuck changing entire app.
you cannot map field 1 table/entity in in way trying. have mapped sponsor entity sponsor table - studyname exists in different table exception reading or writing entity. create entity way expect use in application - don't think makes sense have studyname in sponsor java object, or sponsorname in study object. wouldn't make more sense have getsponsorname checked if there associated sponsor , called getname on it, rather try store name in 2 spots?
there many options fields , or entity combinations if want. jpql can return anything, instance
"select s.studyid, s.studyname, sp.sponsorname, sp.address study s left join sp.sponsorid sp" will return list of object[]s containing field values. or
"select s, sp.sponsorname study s left join sp.sponsorid sp" returns list of object[]s containing study objects , referenced sponsorname.
you can use native queries , map results using resultsetmappings same results.
Comments
Post a Comment