Win a copy of Modern JavaScript for the Impatient this week in the Server-Side JavaScript and NodeJS forum!

rajasekhar kannamaneni

Greenhorn
+ Follow
since Nov 05, 2010
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by rajasekhar kannamaneni

Hi,

can any one please help me how to redesign this mysql query into jpql(jpa query).

SELECT delivered.ls_fk_artikel, delivered.SUM_Amount_Delivered, sold.SUM_Amount_Sold, (coalesce(delivered.SUM_Amount_Delivered,0) - coalesce(sold.SUM_Amount_Sold,0)) AS Amount_Stock
FROM
(
SELECT
Sum(ls_main.ls_menge) AS SUM_Amount_Delivered,
ls_main.ls_fk_artikel,
ls_main.ls_ean,
ls_main.ls_artikelnr
FROM
ls_head
Inner Join ls_main ON ls_head.ls_head_id = ls_main.fk_ls_head
WHERE
ls_head.fk_filialen = '2410' AND
ls_head.storno IS NULL
GROUP BY
ls_main.ls_fk_artikel
) AS delivered
left JOIN
(
SELECT
coalesce(Sum(sales.anzahl), 0) AS SUM_Amount_Sold,
sales.fk_artikel,
sales.ean,
sales.artikel_nr
FROM
sales
WHERE
sales.fk_filialen = '2410'
GROUP BY
sales.fk_artikel
) AS sold ON delivered.ls_fk_artikel = sold.fk_artikel;

lsmain is one table, where I have to get delivered information, sales is sold data and I have to caliculate the difference between the delivered and sold stock.

Currently I am using the below query

Query query = this.entityManager.createQuery("select l, sum(l.ls_menge), s, sum(s.anzahl) from LsMainImpl l, SalesImpl s inner join l.fk_ls_head h where h.fk_filialen.filial_id = " + filial_id + " and h.storno is null and l.ls_fk_artikel.artikel_id = s.fk_artikel.artikel_id and s.fk_filialen.filial_id = " + filial_id + " group by l.ls_fk_artikel.artikel_id");

but it taking 250 seconds..where as the above query takes only 3 seconds.

can any one please help me how to do that.

Thanks in Advance,


Best Regards,
Raja.

Imran Perwez wrote:This bit, end of line...



hi, this is my actual xml code and above is due to my mistake while pasting it here.

<?xml version="1.0" encoding="UTF-8"?>
<persistence 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_1_0.xsd";
version="1.0">
<persistence-unit name="LagerstandnewDS" transaction-type="JTA">

<jta-data-source>java:/LagerstandnewDS</jta-data-source>

<class>com.ernst.persistenceImpl.beanImpl.NrKreiseBean</class>

<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
<property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
<property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/lagerstandnew"/>
<property name="hibernate.connection.username" value="root"/>
<property name="hibernate.connection.password" value="sekhar"/>
<property name="hibernate.archive.autodetection" value="class, hbm"/>
<property name="hibernate.hbm2ddl.auto" value="update"/>

<property name="hibernate.id.new_generator_mappings" value="true"/>
<property name="hibernate.generate_statistics" value="true"/>
<property name="hibernate.cache.use_structured_entries" value="true"/>

<property name="hibernate.format_sql" value="true"/>
<property name="hibernate.show_sql" value="true"/>
<property name="use_sql_comments" value="true"/>
</properties>

</persistence-unit>
</persistence>

Paul Sturrock wrote:Is that valid XML? You have a trailing comment.



I hope its a valid xml..because if there is any error in xml normally parser will throw the error...what do you think.

Paul Sturrock wrote:My guess is you are either using an old version of persistence.xml. Hibernate seems to be reporting it creates a schema without any exceptions occurring, which makes me think your annotated class is not being found.



I am using this version:


<?xml version="1.0" encoding="UTF-8"?>
<persistence 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_1_0.xsd";
version="1.0">
<persistence-unit name="LagerstandnewDS" transaction-type="JTA">

<jta-data-source>java:/LagerstandnewDS</jta-data-source>

<class>com.ernst.persistenceImpl.beanImpl.NrKreiseBean</class>

<properties>
<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver"/>
<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/lagerstandnew;create=true"/>
<property name="javax.persistence.jdbc.user" value="raja"/>
<property name="javax.persistence.jdbc.password" value="sekhar"/> -->
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
<property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
<property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/lagerstandnew"/>
<property name="hibernate.connection.username" value="root"/>
<property name="hibernate.connection.password" value="sekhar"/>
<property name="hibernate.archive.autodetection" value="class, hbm"/>
<property name="hibernate.hbm2ddl.auto" value="update"/>

</properties>

</persistence-unit>
</persistence>


Paul Sturrock wrote:Transactions are unlikely to be the problem I think - DDL is (usually) not transactional.

Your configuration refers to something called ArtikelBean, which is not your annotated class. My guess is ArtikelBean does not contain annotations hbm2ddl can use. Can we see the class ArtikelBean, if that is indeed what you are trying to map? I see you have hibernate.archive.autodetection set too, could it be your mapped class is not deployed where you think it is?




hi..later I have replaced the above bean with NrKreiseBean which was pasted in above comment..may be its also a issue..can you please check the above code annotations one more time..its working with hbm.xml file but not with annotated class..can you please look it.

Imran Perwez wrote:What was the exception?...



java.lang.ClassCastException: org.hibernate.transaction.TransactionFactoryFactor
y cannot be cast to org.hibernate.transaction.TransactionFactory
at org.hibernate.transaction.TransactionFactoryFactory.buildTransactionF
actory(TransactionFactoryFactory.java:65)
at org.hibernate.cfg.SettingsFactory.createTransactionFactory(SettingsFa
ctory.java:452)
at org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:
165)
at org.hibernate.cfg.Configuration.buildSettings(Configuration.java:2101
)
at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.jav
a:1325)

but it will not throw any error by using JTATransactionFactory, but the result is same.

Imran Perwez wrote:I think it's your transaction strategy that is causing the problem



Have you tried using a different transaction strategy?



I have tried with other transaction factories also..but it will throw classcast exception for it.

Paul Sturrock wrote:


for your quick view this is my entity bean


Entity Beans are an EJB 2 technology. It confuses the issue if you use the term to refer to JPA classes.



can you please tell me how can I create the table using above class.
for your quick view this is my entity bean..can you please check it whats going wrong in it.

Imran Perwez wrote:Maybe a silly question... but I take it you are using JPA entities that are correctly annotated? And, not the old style EJB2.1 entity bean?

I don't have the issue you have. I have quite a minimalistic persistence.xml file and datasource compared to yours...

peristence.xml...



Datasource in "<jboss>\server\default\deploy"...



actually I am using almost similar specifications made by you..I will tell one more interesting thing..its creating tables automatically when I have *.hbm.xml files but when I dont have these xml files its not creating any tables directly by using entity beans..I dont want to create hibernate matching files..jboss have to create tables just by using my entity tables only..is it possible to do this?

M Marin wrote:Add also the schema name manually to de database, just leave the tables to be automatically generated.




what I found one interesting fact is..when jboss runs persistence.xml its not generating any create table queries..thats why its not creating any tables..how to generate the create table queries automatically.

Imran Perwez wrote:Try changing...

To...


See if that helps...



If I use above code also its not generating any tables..

what I found one interesting fact is..when jboss runs persistence.xml its not generating any create table queries..thats why its not creating any tables..how to generate the create table queries automatically.

Paul Sturrock wrote:


10:24:07,528 INFO [SchemaUpdate] updating schema
10:24:07,528 INFO [SchemaUpdate] schema update complete


Looks like its updating the schema.


(NB: "entity beans" are a EJB2 technology. When you say you created entity beans did you really mean entity beans? )



hi, this is my class..can you please check it..

M Marin wrote:Add also the schema name manually to de database, just leave the tables to be automatically generated.



I did what you said..but still its not creating any tables in database and its printing this on console:

10:24:07,513 INFO [SessionFactoryObjectFactory] Bound factory to JNDI name: persistence.unit:unitName=ernst-app-ear-1.0.ear/persistenceImpl-app-1.0.war#DefaultDS
10:24:07,513 WARN [SessionFactoryObjectFactory] InitialContext did not implement EventContext
10:24:07,528 INFO [SchemaUpdate] Running hbm2ddl schema update
10:24:07,528 INFO [SchemaUpdate] fetching database metadata
10:24:07,528 INFO [SchemaUpdate] updating schema
10:24:07,528 INFO [SchemaUpdate] schema update complete
10:24:07,544 INFO [NamingHelper] JNDI InitialContext properties:{java.naming.factory.initial=org.jnp.interfaces.NamingContextFactory, java.naming.factory.url.pkgs=org.jboss.naming:org.jnp.interfaces}

can you please tell me what you have done other than creating hbm.xml, persistence.xml and entitiy beans..can you please check it.

M Marin wrote:Hello,

I am developing a program with Hibernate and Oracle and I want to reproduce the same application using MySQL. I want to generate the database tables using:

<property name="hibernate.hbm2ddl.auto">create</property>

from the ("Oracle") domain code, but I can only create one table out of four. Does anyone have an idea what I am doing wrong?

Regards.

M. Marin.



Hi,

can you please post your bean class and persistence.xml and the settings what you are using to execute this program..because I am facing the same problem and struggling for one week to create tables in database automatically.

Thanks in Advance,


Best Regards,
Raja.