Hello,
I have created a oracle view by combaining 2 tables. (i had hard time to join these 2 tables in hibernate to create a map. I must be doing something wrong). Anyway, i got the view, i got the map created out of that view and i got the java object created.

Here is my map

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping>
<!--
Created by Middlegen Hibernate plugin

http://boss.bekk.no/boss/middlegen/
http://hibernate.sourceforge.net/
-->

<class
name="com.xerox.hc.model.common.device.DevInfoProxyDevListView"
table="DEV_INFO_PROXY_DEV_LIST_VIEW"
>

<composite-id>
<key-property
name="serialNumber"
column="SERIAL_NUMBER"
type="java.lang.String"
length="20"
/>
<key-property
name="connectionType"
column="CONNECTION_TYPE"
type="java.lang.String"
length="1"
/>
<key-property
name="proxyId"
column="PROXY_ID"
type="java.lang.String"
length="64"
/>
</composite-id>


<!-- associations -->

</class>
</hibernate-mapping>

Here is my query:

Query query = session1.createQuery("from com.xerox.hc.model.common.device.DevInfoProxyDevListView as d where d.serialNumber in ("+sb.toString()+")");

Here is my view that i have created.

create or replace view dev_info_proxy_dev_list_view as select d.serial_number, d.connection_type, p.proxy_id from device_info d, proxy_device_list p where p.serial_number (+) = d.serial_number;

Here are the 2 tables

Table Name : Proxy_device_list



Name Null? Type

----------------------------------------- -------- ----------------------------

PROXY_ID NOT NULL VARCHAR2(64)

SERIAL_NUMBER NOT NULL VARCHAR2(15)



Table Name : device_info



Name Null? Type

----------------------------------------- -------- ----------------------------

SERIAL_NUMBER NOT NULL VARCHAR2(20)

PRODUCT_CODE VARCHAR2(10)

SITE_ID NUMBER

CONNECTION_TYPE CHAR(1)


basically I have to get serial_number, connection_type,(from device_info table) proxy_id (from proxy_device_list) table even if the serial number in device_info table is not in proxy_device_list table, i want to return a null proxy_id. I had failed in all my attempts to do joins and that's the reason i have careted a view.

It works fine if the proxy_id value is not null. It throws errors only when the proxy_id is null.

Here is the stack trace:

net.sf.hibernate.LazyInitializationException: Hibernate lazy instantiation problem
at net.sf.hibernate.impl.IteratorImpl.next(IteratorImpl.java:98)
at com.xerox.hc.reg2.configuration.session.ConfigurationManagerSessionBean.getDeviceInfo(ConfigurationManagerSessionBean.java:248)
at com.xerox.hc.reg2.configuration.session.ConfigurationManagerSessionBean.isDeviceDCSRegistered(ConfigurationManagerSessionBean.java:160)
at com.xerox.hc.reg2.configuration.session.ConfigurationManagerSessionBean_n2y8kg_EOImpl.isDeviceDCSRegistered(ConfigurationManagerSessionBean_n2y8kg_EOImpl.java:534)
at com.xerox.hc.reg2.configuration.session.ConfigurationManagerSessionBean_n2y8kg_EOImpl_WLSkel.invoke(Unknown Source)
at weblogic.rmi.internal.ServerRequest.sendReceive(ServerRequest.java:166)
at weblogic.rmi.internal.BasicRemoteRef.invoke(BasicRemoteRef.java:138)
at com.xerox.hc.reg2.configuration.session.ConfigurationManagerSessionBean_n2y8kg_EOImpl_812_WLStub.isDeviceDCSRegistered(Unknown Source)
at com.xerox.hc.webservices.provisionrequest.session.ProvisionRequestSessionBean.isDeviceDCSRegistered(ProvisionRequestSessionBean.java:183)
at com.xerox.hc.webservices.provisionrequest.session.ProvisionRequestSessionBean_ja994w_EOImpl.isDeviceDCSRegistered(ProvisionRequestSessionBean_ja994w_EOImpl.java:998)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at weblogic.webservice.component.slsb.SLSBInvocationHandler.invoke(SLSBInvocationHandler.java:88)
at weblogic.webservice.core.handler.InvokeHandler.handleRequest(InvokeHandler.java:104)
at weblogic.webservice.core.HandlerChainImpl.handleRequest(HandlerChainImpl.java:143)
at weblogic.webservice.core.DefaultOperation.process(DefaultOperation.java:531)
at weblogic.webservice.server.Dispatcher.process(Dispatcher.java:204)
at weblogic.webservice.server.Dispatcher.doDispatch(Dispatcher.java:176)
at weblogic.webservice.server.Dispatcher.dispatch(Dispatcher.java:96)
at weblogic.webservice.server.WebServiceManager.dispatch(WebServiceManager.java:98)
at weblogic.webservice.server.servlet.WebServiceServlet.serverSideInvoke(WebServiceServlet.java:298)
at weblogic.webservice.server.servlet.ServletBase.doPost(ServletBase.java:485)
at weblogic.webservice.server.servlet.WebServiceServlet.doPost(WebServiceServlet.java:268)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:971)
at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:402)
at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:28)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:27)
at com.xerox.hc.weblogic.LogFilter.doFilter(LogFilter.java:70)
at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:27)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:6356)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:317)
at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:118)
at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:3635)
at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:2585)
at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:197)
at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:170)
Caused by: net.sf.hibernate.UnresolvableObjectException: No row with the given identifier exists: com.xerox.hc.model.common.device.DevInfoProxyDevListView@1f1b072[serialNumber=2UP000001,connectionType=D,proxyId=<null>], of class: com.xerox.hc.model.common.device.DevInfoProxyDevListView
at net.sf.hibernate.UnresolvableObjectException.throwIfNull(UnresolvableObjectException.java:37)
at net.sf.hibernate.impl.SessionImpl.internalLoad(SessionImpl.java:1876)
at net.sf.hibernate.type.ManyToOneType.resolveIdentifier(ManyToOneType.java:68)
at net.sf.hibernate.type.EntityType.resolveIdentifier(EntityType.java:215)
at net.sf.hibernate.type.EntityType.nullSafeGet(EntityType.java:154)
at net.sf.hibernate.impl.IteratorImpl.postNext(IteratorImpl.java:72)
at net.sf.hibernate.impl.IteratorImpl.next(IteratorImpl.java:92)
... 39 more

BTW, i am using hibernate 2.0 and my IDE is Intellij with jdk 1.4.5_05 and oracle 8.1.7.4

Anyhelp would be really appreciated.

The object doesn't seem to exist in the database. Have you checked?

Hello, thanks for your response. I knew that the object is empty. If the connectionType is "D" then the object proxyId will be null. If the connectionType is "P" then the object proxyId will have a value and in the latter case it works fine. But how can i filter that out. I don't want to do 2 queries first to see if the connectionType is "D" or "P" and then do another query to get the proxyId only if the connection type is "P".
Thats why i want the proxyId no matter what the connection type is. Hibernate should handle back the object even if it is null.

Any help would be appreciated.

Elango

OK

I've had another look at this. AFAIK you cannot have an identifier that is nullable - but perhaps someone can confirm this for composite keys. If this is the case then you should consider either
1) Changing the identifier to the appropriate columns
2) Changing your mappings (e.g. to a one-to-one or on-to-many relationship)

Perhaps we can help you out:

You haven't provided me with enough info on your table structure to enable me to say excactly how the tables are related. I need to know firstly what the primary keys of the repective tables are and secondly how the tables are related (fk's, type of relationship etc). Once we know this we can then decide if a view is appropriate or whether a simple association is going to meet your needs.

Thanks

Justin

actually other db than oracle does not allow null value for a composite PK element

Sorry for not being clear on DB structure.
Here are my tables

In here, device_info is the master table and serial_number is the primary key.

In proxy_device_list table, both proxy_id and serial_number are primary keys (composite keys). Serial_number is also FK to device_info table.
Proxy_id is also an FK to another table. But we don't want that table.

When i say that the proxy_id could be null for connection_type = "D", i meant to say that only if the connection type is "P" a record will put into the proxy_device_list table. But no matter what the connection_type (either D or P) all records will be in device_info (obviously it is the master).

My query should bring back serial_number, connection_type from device_info table and proxy_id from proxy_device_list table. If the connection_type is "D", i knew that there won't be any record in the proxy_device_list table. But still i want to have "null" proxy_id returns to me.

As you might already know that you can do this in oracle query(regular sql query)

here is the regular sql query for what i want


select d.serial_number, d.connection_type, p.proxy_id from device_info d, proxy_device_list p where d.serial_number in ('ELA000028','2UP000001') and p.serial_number (+) = d.serial_number;

By doing this, i can avoid 2 queries ( 2 database hits) to the DB.

Table Name : device_info

Name Null? Type
----------------------------------------- -------- ----------------------------
SERIAL_NUMBER NOT NULL VARCHAR2(20)
PRODUCT_CODE VARCHAR2(10)
SITE_ID NUMBER
CONNECTION_TYPE CHAR(1)


Table Name : Proxy_device_list
Name Null? Type
----------------------------------------- -------- ----------------------------
PROXY_ID NOT NULL VARCHAR2(64)
SERIAL_NUMBER NOT NULL VARCHAR2(15)

Thanks
Elango

I would map the tables using a one-to-many relationship:

i.e:
device_info 1----* proxy_device_list

Then you can modify the HQL that you create to outer join on the proxy_device_list table using something like:

Could you please change my map to show how one-to-many setup can be done (if you can)? Do i need any <Set> or some kind of collection attribute in my map to join the proxy_device_list table?

Also, can i include some class in my query if i do not have a map for that class? In you example you are saying di.proxyDeviceList. does it means that i have to have a map for ProxyDeviceList? If so, do i need any relation from this table to device_info table?

if you have a query that queries/joins 2 tables, what object you cast the result back. In my case, i think i will be having device_info object that will be having one-to-many relationship to proxy_device_list object.

do you cast the resulting object to device_info object or to proxy_device_list object?

Because i tried once and i get class cast exception.

Thanks a lot for your help.

This is really covered in the parent-child part of the documentation (simple one to many mappings are very common examples) - please check this section out, change your mappings and post if you have a problem.

Not a HQL query. You need to map a class that you refer to. If you need to resort to plain old JDBC use session.connection()... but I would think that this is not the route that you wish to go.


Check out the docs - there are many examples of one-to-many relationships. Pay particular attention to the parent-child section.


A common mistake is to cast the results from query.list() back to your object. You actually get back a list of Object arrays.


Post you mappings and code

Not a HQL query. You need to map a class that you refer to. If you need to resort to plain old JDBC use session.connection()... but I would think that this is not the route that you wish to go.