Databases
StackOverflow2013
Postsdbo
POHow to use inner join statement with Hibernate
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to use inner join statement with Hibernate
    primarykey
    data
    text
    <p>I have two tables in a MySQL database: 1. Device table with some device specific values. 2. Vendor table with only a id value and name value</p> <p>Now, I would like to get two values, model name and the vendor_id, from the device table. But instead to show the vendor_id I would like to display the corresponding vendor name. So in the MySQL Workbench this statement is working fine and shows me the expected result like Model = N95 and Vendor = Nokia</p> <pre><code>SELECT device.model, vendor.vendor from device inner join vendor on device.vendor_id = vendor.id </code></pre> <p>Now I'm using Hibernate to get the values from tables.</p> <pre><code>public List&lt;Device&gt; listDevices() { session = HibernateUtil.getSessionFactory().openSession(); Transaction tx = null; try{ tx = session.beginTransaction(); String sql = "SELECT device.model, vendor.vendor from device inner join vendor on device.vendor_id = vendor.id"; SQLQuery query = session.createSQLQuery(sql); query.addEntity(Device.class); List list = query.list(); allDevices = new ArrayList(); for (Iterator iterator = list.iterator(); iterator.hasNext();) { device = (Device) iterator.next(); allDevices.add(device); } tx.commit(); } catch (HibernateException e) { if(tx!=null) tx.rollback(); e.printStackTrace(); } finally { session.close(); return allDevices; } } </code></pre> <p>But this is not working I got this exception:</p> <pre><code>SEVERE: Column 'id' not found. SEVERE: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.loader.Loader.doList(Loader.java:2223) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104) at org.hibernate.loader.Loader.list(Loader.java:2099) at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:289) at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1695) at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142) at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152) at com.vodafone.omisuite.model.DeviceData.listDevices(DeviceData.java:154) at com.vodafone.omisuite.model.DeviceData.&lt;init&gt;(DeviceData.java:50) at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:526) at java.lang.Class.newInstance(Class.java:374) at com.sun.faces.mgbean.BeanBuilder.newBeanInstance(BeanBuilder.java:186) at com.sun.faces.mgbean.BeanBuilder.build(BeanBuilder.java:100) at com.sun.faces.mgbean.BeanManager.createAndPush(BeanManager.java:409) at com.sun.faces.mgbean.BeanManager.create(BeanManager.java:269) at com.sun.faces.el.ManagedBeanELResolver.resolveBean(ManagedBeanELResolver.java:244) at com.sun.faces.el.ManagedBeanELResolver.getValue(ManagedBeanELResolver.java:116) at com.sun.faces.el.DemuxCompositeELResolver._getValue(DemuxCompositeELResolver.java:176) at com.sun.faces.el.DemuxCompositeELResolver.getValue(DemuxCompositeELResolver.java:203) at com.sun.el.parser.AstIdentifier.getValue(AstIdentifier.java:116) at com.sun.el.parser.AstValue.getBase(AstValue.java:151) at com.sun.el.parser.AstValue.getValue(AstValue.java:200) at com.sun.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:226) at org.jboss.weld.el.WeldValueExpression.getValue(WeldValueExpression.java:50) at com.sun.faces.facelets.el.TagValueExpression.getValue(TagValueExpression.java:109) at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:194) at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:182) at javax.faces.component.UIData.getValue(UIData.java:732) at javax.faces.component.UIData.getDataModel(UIData.java:1811) at javax.faces.component.UIData.setRowIndexWithoutRowStatePreserved(UIData.java:484) at javax.faces.component.UIData.setRowIndex(UIData.java:473) at com.sun.faces.renderkit.html_basic.TableRenderer.encodeBegin(TableRenderer.java:82) at javax.faces.component.UIComponentBase.encodeBegin(UIComponentBase.java:869) at javax.faces.component.UIData.encodeBegin(UIData.java:1133) at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1854) at javax.faces.render.Renderer.encodeChildren(Renderer.java:176) at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:894) at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1856) at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1859) at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1859) at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(FaceletViewHandlingStrategy.java:443) at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:131) at javax.faces.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:337) at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:120) at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101) at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:219) at javax.faces.webapp.FacesServlet.service(FacesServlet.java:647) at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1682) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:318) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:160) at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:734) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:673) at com.sun.enterprise.web.WebPipeline.invoke(WebPipeline.java:99) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:174) at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:357) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:260) at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:188) at org.glassfish.grizzly.http.server.HttpHandler.runService(HttpHandler.java:191) at org.glassfish.grizzly.http.server.HttpHandler.doHandle(HttpHandler.java:168) at org.glassfish.grizzly.http.server.HttpServerFilter.handleRead(HttpServerFilter.java:189) at org.glassfish.grizzly.filterchain.ExecutorResolver$9.execute(ExecutorResolver.java:119) at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeFilter(DefaultFilterChain.java:288) at org.glassfish.grizzly.filterchain.DefaultFilterChain.executeChainPart(DefaultFilterChain.java:206) at org.glassfish.grizzly.filterchain.DefaultFilterChain.execute(DefaultFilterChain.java:136) at org.glassfish.grizzly.filterchain.DefaultFilterChain.process(DefaultFilterChain.java:114) at org.glassfish.grizzly.ProcessorExecutor.execute(ProcessorExecutor.java:77) at org.glassfish.grizzly.nio.transport.TCPNIOTransport.fireIOEvent(TCPNIOTransport.java:838) at org.glassfish.grizzly.strategies.AbstractIOStrategy.fireIOEvent(AbstractIOStrategy.java:113) at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.run0(WorkerThreadIOStrategy.java:115) at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy.access$100(WorkerThreadIOStrategy.java:55) at org.glassfish.grizzly.strategies.WorkerThreadIOStrategy$WorkerThreadRunnable.run(WorkerThreadIOStrategy.java:135) at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:564) at org.glassfish.grizzly.threadpool.AbstractThreadPool$Worker.run(AbstractThreadPool.java:544) at java.lang.Thread.run(Thread.java:724) Caused by: java.sql.SQLException: Column 'id' not found. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919) at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1167) at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2851) at org.hibernate.type.IntegerType.get(IntegerType.java:28) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:163) at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:154) at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:1097) at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:565) at org.hibernate.loader.Loader.doQuery(Loader.java:701) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236) at org.hibernate.loader.Loader.doList(Loader.java:2220) ... 76 more </code></pre> <p>If I use the following statement instead its working, but it shows me the ID values for the vendor_id and not the corresponding names.</p> <pre><code>SELECT * from device inner join vendor on device.vendor_id = vendor.id </code></pre> <p>For both tables I have created java classes and two *.hbm.xml files. In the hibernate.cfg.xml file both mappings are configured.</p> <p>Can anyone help and explain me what I did wrong and how can I fix this? If you have an advice how to create a corresponding HQL statement please feel free to post ;-)</p> <p>This is the device class created through Hibernate!</p> <pre><code>/** * Device generated by hbm2java */ @Entity @Table(name="device" ,catalog="myDB" ) public class Device implements java.io.Serializable { private Integer id; private String version; private String shortname; private int vendorId; private String model; private String platform; private String appIconSize; private String description; private String uaProfile; private byte[] image; private String imageMimetype; private int internetonly; private int creator; private int approvable; private int approved; private Date changed; public Device() { } public Device(String shortname, int vendorId, String model, String platform, int internetonly, int creator, int approvable, int approved, Date changed) { this.shortname = shortname; this.vendorId = vendorId; this.model = model; this.platform = platform; this.internetonly = internetonly; this.creator = creator; this.approvable = approvable; this.approved = approved; this.changed = changed; } public Device(String shortname, int vendorId, String model, String platform, String appIconSize, String description, String uaProfile, byte[] image, String imageMimetype, int internetonly, int creator, int approvable, int approved, Date changed) { this.shortname = shortname; this.vendorId = vendorId; this.model = model; this.platform = platform; this.appIconSize = appIconSize; this.description = description; this.uaProfile = uaProfile; this.image = image; this.imageMimetype = imageMimetype; this.internetonly = internetonly; this.creator = creator; this.approvable = approvable; this.approved = approved; this.changed = changed; } @Id @GeneratedValue(strategy=IDENTITY) @Column(name="id", unique=true, nullable=false) public Integer getId() { return this.id; } public void setId(Integer id) { this.id = id; } @Column(name="version") public String getVersion() { return this.version; } public void setVersion(String version) { this.version = version; } @Column(name="shortname", nullable=false) public String getShortname() { return this.shortname; } public void setShortname(String shortname) { this.shortname = shortname; } @Column(name="vendor_id", nullable=false) public int getVendorId() { return this.vendorId; } public void setVendorId(int vendorId) { this.vendorId = vendorId; } @Column(name="model", nullable=false) public String getModel() { return this.model; } public void setModel(String model) { this.model = model; } @Column(name="platform", nullable=false, length=7) public String getPlatform() { return this.platform; } public void setPlatform(String platform) { this.platform = platform; } @Column(name="app_icon_size", length=10) public String getAppIconSize() { return this.appIconSize; } public void setAppIconSize(String appIconSize) { this.appIconSize = appIconSize; } @Column(name="description") public String getDescription() { return this.description; } public void setDescription(String description) { this.description = description; } @Column(name="ua_profile") public String getUaProfile() { return this.uaProfile; } public void setUaProfile(String uaProfile) { this.uaProfile = uaProfile; } @Column(name="image") public byte[] getImage() { return this.image; } public void setImage(byte[] image) { this.image = image; } @Column(name="image_mimetype", length=16) public String getImageMimetype() { return this.imageMimetype; } public void setImageMimetype(String imageMimetype) { this.imageMimetype = imageMimetype; } @Column(name="internetonly", nullable=false) public int getInternetonly() { return this.internetonly; } public void setInternetonly(int internetonly) { this.internetonly = internetonly; } @Column(name="creator", nullable=false) public int getCreator() { return this.creator; } public void setCreator(int creator) { this.creator = creator; } @Column(name="approvable", nullable=false) public int getApprovable() { return this.approvable; } public void setApprovable(int approvable) { this.approvable = approvable; } @Column(name="approved", nullable=false) public int getApproved() { return this.approved; } public void setApproved(int approved) { this.approved = approved; } @Temporal(TemporalType.TIMESTAMP) @Column(name="changed", nullable=false, length=19) public Date getChanged() { return this.changed; } public void setChanged(Date changed) { this.changed = changed; } } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser2992380
    1. USuser2992380
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to use inner join statement with Hibernate
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to use inner join statement with Hibernate
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COYou are us sing hibernate, use HQL. This will be super simple. The H docs are good on these matters.
      singulars
      1. POHow to use inner join statement with Hibernate
      1. USRob
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload