[EJB 3.0] - A classic EJB Left Join and Order By clause problem
by app4you＠yahoo.com
Good day,
I am using Oracle 9i and I would like to apply the ORDER BY on the Customer.state when querying the Order class.
1. Here are the entities:
@Entity
@Table(name="ORDERS")
public class Order implements Serializable{
...
@ManyToOne
@JoinColumn(name="USERID")
public Customer getCustomer() {
return customer;
}
public void setCustomer(Customer customer) {
this.customer = customer;
}
@ManyToOne
@JoinColumn(name="PRODUCTID")
public Customer getProduct() {
return product;
}
public void setProduct(Product product) {
this.product = product;
}
...
}
@Entity
public class Customer implements Serializable{
String address1;
String address2;
String city;
String state;
String zip;
...
}
@Entity
public class Product implements Serializable{
int productid;
String productName;
...
}
2. Here is the query that I would like to get it working so that the result will be a list of the Order entity bean where I can get all the
products and the customers for a report listing. Please notice the DISTINCT and the ORDER BY clause on the Order's customer state property.
.createQuery("select distinct order
from Order order
left join order.customer customer
order by customer.state");
3. All worked fine on the MySQL DB server, but Oracle threw an error saying something like " not SELECTed statement " which
means that the order by column must be a part of the select statement.
4. Question: how can we go about to make the above statement work so that I can get all the results?
Your help is greatly appreciated.
Thank you,
John
View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3972116#3972116
Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3972116
19 years, 7 months
[EJB 3.0] - Simple EJB3.0 App deploys with HypersonicSQL but no Postgres
by forumer
To learn, I have a very simple app with only one Session Bean and one Entity Bean, User and I am deploying it iin JBoss-4.0.4.CR2. It deploys fine when I use DefaultDS in persistence.xml. Table User is created fine too. But it is --NOT-- working when I point persistence.xml to PostgresqlDS, another datasource used in our application. It fails when trying to create the table, User! I'd appreciate any help to get this working. I am including persistence.xml, postgresql-ds.xml and console output...
BTW, I don't know why hibernate.hbm2ddl.auto = create-drop, as part of persistence.xml is not showing when I preview this post!
postgresql-ds.xml:
<?xml version="1.0" encoding="utf-8" ?>
<local-tx-datasource>
<jndi-name>PostgresqlDS</jndi-name>
<connection-url>jdbc:postgresql://localhost:5432/begss
</connection-url>
<driver-class>org.postgresql.Driver</driver-class>
<user-name>postgres</user-name>
password
<type-mapping>PostgreSQL 8.1</type-mapping>
</local-tx-datasource>
---
persistence.xml:
<persistence-unit name="myejb30">
<jta-data-source>java:/PostgresqlDS</jta-data-source>
</persistence-unit>
---
Console output:
08:42:04,044 INFO [Ejb3Configuration] Processing PersistenceUnitInfo [
name: myejb30
...]
08:42:04,044 INFO [Ejb3Configuration] found EJB3 Entity bean: test.User
08:42:04,044 INFO [AnnotationBinder] Binding entity from annotated class: test.User
08:42:04,044 INFO [EntityBinder] Bind entity test.User on table user
08:42:04,054 INFO [ConnectionProviderFactory] Initializing connection provider: org.hiber
nate.ejb.connection.InjectedDataSourceConnectionProvider
08:42:04,054 INFO [InjectedDataSourceConnectionProvider] Using provided datasource
08:42:04,054 INFO [SettingsFactory] RDBMS: PostgreSQL, version: 8.1.3
08:42:04,054 INFO [SettingsFactory] JDBC driver: PostgreSQL Native Driver, version: Postg
reSQL 8.1 JDBC3 with SSL (build 404)
08:42:04,054 INFO [Dialect] Using dialect: org.hibernate.dialect.PostgreSQLDialect
08:42:04,054 INFO [TransactionFactoryFactory] Transaction strategy: org.hibernate.ejb.transaction.JoinableCMTTransactionFactory
08:42:04,054 INFO [TransactionManagerLookupFactory] instantiating TransactionManagerLookup: org.hibernate.transaction.JBossTransactionManagerLookup
08:42:04,054 INFO [TransactionManagerLookupFactory] instantiated TransactionManagerLookup
08:42:04,054 INFO [SettingsFactory] Automatic flush during beforeCompletion(): disabled
08:42:04,054 INFO [SettingsFactory] Automatic session close at end of transaction: disabl
ed
08:42:04,054 INFO [SettingsFactory] JDBC batch size: 15
08:42:04,054 INFO [SettingsFactory] JDBC batch updates for versioned data: disabled
08:42:04,054 INFO [SettingsFactory] Scrollable result sets: enabled
08:42:04,054 INFO [SettingsFactory] JDBC3 getGeneratedKeys(): disabled
08:42:04,064 INFO [SettingsFactory] Connection release mode: auto
08:42:04,064 INFO [SettingsFactory] Default batch fetch size: 1
08:42:04,064 INFO [SettingsFactory] Generate SQL with comments: disabled
08:42:04,064 INFO [SettingsFactory] Order SQL updates by primary key: disabled
08:42:04,064 INFO [SettingsFactory] Query translator: org.hibernate.hql.ast.ASTQueryTrans
latorFactory
08:42:04,064 INFO [ASTQueryTranslatorFactory] Using ASTQueryTranslatorFactory
08:42:04,064 INFO [SettingsFactory] Query language substitutions: {}
08:42:04,064 INFO [SettingsFactory] Second-level cache: enabled
08:42:04,064 INFO [SettingsFactory] Query cache: disabled
08:42:04,064 INFO [SettingsFactory] Cache provider: org.hibernate.cache.HashtableCachePro
vider
08:42:04,064 INFO [SettingsFactory] Optimize cache for minimal puts: disabled
08:42:04,064 INFO [SettingsFactory] Structured second-level cache entries: disabled
08:42:04,064 INFO [SettingsFactory] Statistics: disabled
08:42:04,064 INFO [SettingsFactory] Deleted entity synthetic identifier rollback: disable
d
08:42:04,064 INFO [SettingsFactory] Default entity-mode: pojo
08:42:04,064 INFO [SessionFactoryImpl] building session factory
08:42:04,074 INFO [SessionFactoryObjectFactory] Not binding factory to JNDI, no JNDI name configured
08:42:04,074 INFO [SchemaExport] Running hbm2ddl schema export
08:42:04,074 INFO [SchemaExport] exporting generated schema to database
08:42:04,084 ERROR [SchemaExport] Unsuccessful: create table user (id int4 not null, password varchar(255), username varchar(255), primary key (id))
08:42:04,084 ERROR [SchemaExport] ERROR: syntax error at or near "user"
08:42:04,104 INFO [SchemaExport] schema export complete
08:42:04,104 INFO [NamingHelper] JNDI InitialContext properties:{java.naming.factory.init
ial=org.jnp.interfaces.NamingContextFactory, java.naming.factory.url.pkgs=org.jboss.naming:org.jnp.interfaces}
08:42:04,104 INFO [JmxKernelAbstraction] installing MBean: jboss.j2ee:ear=test.ear,jar=beans.jar,name=UserSessionBean,service=EJB3 with dependencies:
08:42:04,104 INFO [JmxKernelAbstraction] persistence.units:ear=test.ear,jar=beans.jar,unitName=myejb30
08:42:04,234 INFO [EJBContainer] STARTED EJB: test.UserSessionBean ejbName: UserSessionBean
08:42:04,254 INFO [EJB3Deployer] Deployed: file:/C:/jboss-4.0.4.CR2/server/all/tmp/deploy/tmp2592test.ear-contents/beans.jar
08:42:04,264 INFO [TomcatDeployer] deploy, ctxPath=/MyEJB30, warUrl=.../tmp/deploy/tmp2592test.ear-contents/web-exp.war/
08:42:04,334 INFO [EARDeployer] Started J2EE application: file:/C:/jboss-4.0.4.CR2/server/all/deploy/test.ear
08:42:04,334 ERROR [URLDeploymentScanner] Incomplete Deployment listing:
View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3972111#3972111
Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3972111
19 years, 7 months