Ajuda com Inner Join no hibernate

26 de agosto de 2010 3 respostas

supertchuco 26 de agosto de 2010

Boa tarde a todos…estou com a seguinte situação e não consigo resolver…
Possuo uma aplicação web que utiliza hibernate e nela tenho duas entidades…

A Aplicação

@SuppressWarnings("serial")
@Table(name = "T_APP")
@SequenceGenerator(name="SEQ", sequenceName="Q_APP")
@FilterDef(name="filterByApp", parameters=@ParamDef( name="filteredApplications", type="int" ))
@Filter(name="filterByApp", condition="APP_ID in (:filteredApplications)")

@Entity
public class Application extends BaseEntity {

	public static final String SORT_BY_NAME = "name";
	public static final String SORT_BY_DESCRIPTION = "description";
	
	public static final String NAME_HOMEPAGE_PORTAL = "HomepagePortal";
	public static final String NAME_NAS_PORTAL = "NASPortal";

	@Id
	@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SEQ")
	@Column(name = "APP_ID")
	private Integer id;

	@Column(name = "DESC_TX")
	private String description;

	@Column(name = "APP_NM")
	@OrderBy
	private String name;
	
	@ManyToOne(fetch=FetchType.LAZY)
	@JoinColumn(name="INPUT_Q_ID", nullable = false)
	private InputQueue inputQueue;
  
            /* getter/setters

e a InputQueue

@SuppressWarnings("serial")
@Table(name = "T_INPUT_Q")
@SequenceGenerator(name="SEQ", sequenceName="INPUT_Q_ID")
@FilterDef(name="filterByInputQueue", parameters=@ParamDef( name="filteredInputQueue", type="int" ))
@Filter(name="filterByInputQueue", condition="INPUT_Q_ID in (:filteredInputQueue)")
@Entity
public class InputQueue extends BaseEntity {

	public static final String SORT_BY_NAME = "name";
	public static final String SORT_BY_DESCRIPTION = "description";
	
	public static final String NAME_HOMEPAGE_PORTAL = "HomepagePortal";
	public static final String NAME_NAS_PORTAL = "NASPortal";

	@Id
	@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SEQ")
	@Column(name = "INPUT_Q_ID")
	private Integer id;

	@Column(name = "INPUT_Q_NM")
	private String name;
  /* getter/setters

Nesse sistema o qual trabalho eles utiliza a hql para trazer os dados e a consulta está desse jeito :

String hql = "select distinct app ";
		hql += buildHql(application);
		if (sortBy != null && !("".equals(sortBy))) {
			if ("ASC".equalsIgnoreCase(order)) {
				hql += "order by upper(app." + sortBy + ") asc";
			} else {
				hql += "order by upper(app." + sortBy + ") desc";
			}
		}

		Query query = getSession().createQuery(hql);
		if (application.getName() != null) {
			query.setString("appNameOrAlias", application.getName().toUpperCase());
		}

Portanto resumindo, na tabela de application está o id da InputQueue que é uma Fk para a tabela InputQueue que possui o nome da InputQueue…
So que a maldita consulta não está trazendo a entidade Application com o objeto inputQueue com o id e sem o nome, então fica a dúvida preciso implementar algo no hql ou somente a anotação na entidade já faria esse serviço?
E o que faltaria pra isso funcionar?
Valeu…

3 Respostas

MarceloNeo 26 de ago. de 2010

Você esta passando o objeto correto, ele dá algum Exception?

Eu estava com um problema semelhante a pouco instantes, eu passava o Id do objeto, equando eu deveria passar o próprio objeto
Da uma olhada na propriedade do objeto e vê se o parametro que você esta passando é compativel…
não sei certo se entendi teu problema, qualquer coisa explica mais um pouco ai!

supertchuco 27 de ago. de 2010

Olá a todos, bom um dos problemas consegui resolver…ficou assim as duas classes

a classe application que possui um join com a tabela InputQueue através do objeto “inputQueue”

@SuppressWarnings("serial")
@Table(name = "T_APP")
@SequenceGenerator(name="SEQ", sequenceName="Q_APP")
@FilterDef(name="filterByApp", parameters=@ParamDef( name="filteredApplications", type="int" ))
@Filter(name="filterByApp", condition="APP_ID in (:filteredApplications)")

@Entity
public class Application extends BaseEntity {

	public static final String SORT_BY_NAME = "name";
	public static final String SORT_BY_DESCRIPTION = "description";
	
	public static final String NAME_HOMEPAGE_PORTAL = "HomepagePortal";
	public static final String NAME_NAS_PORTAL = "NASPortal";

	@Id
	@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SEQ")
	@Column(name = "APP_ID")
	private Integer id;

	@Column(name = "DESC_TX")
	private String description;

	@Column(name = "APP_NM")
	@OrderBy
	private String name;
	
	
	@OneToOne(fetch=FetchType.LAZY,cascade=javax.persistence.CascadeType.ALL)
	@Cascade(CascadeType.SAVE_UPDATE)
	@JoinColumn(name="INPUT_Q_ID")
	private InputQueue inputQueue;



///**** getters and setters
}

a classe InputQueue em si só normal…também

@SuppressWarnings("serial")
@Table(name = "T_INPUT_Q")
@SequenceGenerator(name="SEQ", sequenceName="Q_INPUT_Q")
@Entity
public class InputQueue extends BaseEntity {
	
	public static final String ALL_OTHERS = "All Others";
	
	
	
	@Id
	@GeneratedValue(strategy=GenerationType.SEQUENCE, generator="SEQ")
	@Column(name = "INPUT_Q_ID")
	private Integer id;
	
	@Column(name = "INPUT_Q_NM")
	private String name;

	//** getter and setters //////

}

Bom na hora de salvar a application com um Inputqueue que esteja com o seu id zerado e só name preenchido beleza, ele salva o id da Inputqueue (pois ele é gerado no hibernate pode ver na classe InputQueue) na tabela application e na tabela InputQueue com o cascateamento perfeito, mas agora a história triste…

Quando tento salvar uma application com com sua Inputqueue com um id j e name já preenchidos no banco (no caso só teria que salvar o id da Inputqueue na tabela application do banco) ta dando o seguinte erro

[27/08/10 15:45:26:919 BRT] 0000002f SystemOut     O 2010-08-27 15:45:26,888 ERROR [WebContainer : 2] [AbstractFlushingEventListener.performExecutions()]: Could not synchronize database state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:94)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
	at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:114)
	at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:109)
	at org.hibernate.jdbc.AbstractBatcher.prepareBatchStatement(AbstractBatcher.java:244)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2252)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2688)
	at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:79)
	at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:279)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
	at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
	at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
	at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1027)
	at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:365)
	at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
	at org.springframework.orm.hibernate3.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:655)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:732)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:701)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:321)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:116)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at $Proxy362.save(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:618)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
	at org.springframework.aop.framework.adapter.ThrowsAdviceInterceptor.invoke(ThrowsAdviceInterceptor.java:126)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at com.adp.decs.common.logging.LoggingAdvice.invoke(LoggingAdvice.java:62)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
	at $Proxy363.save(Unknown Source)
	at com.adp.decs.view.action.ApplicationAction.saveAsNew(ApplicationAction.java:169)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:618)
	at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:440)
	at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:279)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:242)
	at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:163)
	at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:138)
	at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:249)
	at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)
	at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)
	at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)
	at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:148)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:93)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:235)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:89)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:128)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at org.apache.struts2.interceptor.ProfilingActivationInterceptor.intercept(ProfilingActivationInterceptor.java:104)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:267)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:126)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:148)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:128)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:176)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at com.adp.decs.view.interceptor.AuthorizationInterceptor.intercept(AuthorizationInterceptor.java:32)
	at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:236)
	at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:52)
	at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:468)
	at org.apache.struts2.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:395)
	at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:190)
	at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:130)
	at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:378)
	at com.adp.decs.security.AuthenticationFilter.doFilter(AuthenticationFilter.java:89)
	at org.springframework.security.util.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:390)
	at org.springframework.security.util.FilterChainProxy.doFilter(FilterChainProxy.java:175)
	at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:236)
	at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:167)
	at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:190)
	at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:130)
	at org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:198)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
	at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:190)
	at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:130)
	at com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilterChain.java:87)
	at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:832)
	at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:742)
	at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:695)
	at com.ibm.ws.wswebcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:103)
	at com.ibm.ws.webcontainer.extension.DefaultExtensionProcessor.invokeFilters(DefaultExtensionProcessor.java:833)
	at com.ibm.ws.webcontainer.extension.DefaultExtensionProcessor.handleRequest(DefaultExtensionProcessor.java:771)
	at com.ibm.ws.wswebcontainer.extension.DefaultExtensionProcessor.handleRequest(DefaultExtensionProcessor.java:113)
	at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:3453)
	at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:267)
	at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:815)
	at com.ibm.ws.wswebcontainer.WebContainer.handleRequest(WebContainer.java:1466)
	at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:119)
	at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:458)
	at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewInformation(HttpInboundLink.java:387)
	at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:267)
	at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.sendToDiscriminators(NewConnectionInitialReadCallback.java:214)
	at com.ibm.ws.tcp.channel.impl.NewConnectionInitialReadCallback.complete(NewConnectionInitialReadCallback.java:113)
	at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:165)
	at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
	at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
	at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:136)
	at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:196)
	at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:751)
	at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:881)
	at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1473)
Caused by: java.sql.BatchUpdateException: ORA-00001: unique constraint (DECSADM.IU_INPQ_INPUT_Q_NM) violated

	at oracle.jdbc.driver.DatabaseError.throwBatchUpdateException(DatabaseError.java:343)
	at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:10656)
	at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteBatch(WSJdbcPreparedStatement.java:941)
	at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeBatch(WSJdbcStatement.java:705)
	at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
	at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
	... 128 more

Bom agradeço a qualquer ajuda…

MarceloNeo 27 de ago. de 2010

ConstraintViolationException você não esta respeitando alguma fk ou pk, só isso que ele está falando

Criado 26 de agosto de 2010

Ultima resposta 27 de ago. de 2010

Respostas 3

Participantes 2

3 Respostas

Topicos relacionados