How to execute raw SQL and pass results to a variable

[msanitariusz]

Hi, in my app i have orders model/table and i would like to retrieve
average order cost (SELECT AVG(cost) FROM orders;), how can i get this
done in Play?

[Alexander Reelsen]

Hi

> Hi, in my app i have orders model/table and i would like to retrieve
> average order cost (SELECT AVG(cost) FROM orders;), how can i get this
> done in Play?

This looks like a standard JPA query
Order.find("select avg(o.cost) from Order o").fetch()

Should do what you need.

Regards, Alexander

--
http://www.emplify.de

[Philippe Gauthier]

If you want to avoid JPQL, you also have JPA.em().createNativeQuery
("SELECT AVG(cost) FROM orders;").getResultList();

Regards,
Philippe

[msanitariusz]

But what type of variable should the results be assigned to?

[Alexander Reelsen]

Hi

> But what type of variable should the results be assigned to?

java.lang.Number for example

[msanitariusz]

Could you please help me with another SQL:
SELECT AVG(cost), MAX(cost), MIN(cost) FROM orders;
How could i assign max, min and avg to 3 variables? (I know how to do
this in 3 separate queries but i want to probe the database just once)
I can assign the result of the query to an object variable, but i
don't know where to go further.

[Chris Reeves]

Have you tried:

SELECT AVG(cost) as average, MAX(cost) as maximum, MIN(cost) as
minimum FROM orders;

If you are using raw SQL, that should work and give you average,
maximum, and minimum in the resulting row.

Thanks, Chris

> --
> You received this message because you are subscribed to the Google Groups "play-framework" group.
> To post to this group, send email to play-fr...@googlegroups.com.
> To unsubscribe from this group, send email to play-framewor...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/play-framework?hl=en.
>
>

[Guillaume Bort]

It will return you an Object[] instance with 3 elements. You can also
use a Map as return type, like in the Yabe tutorial.

[nile black]

Help! Help!
when i use
Query query = JPA.em().createNativeQuery();
query.getResultList();

it throw execption:
 No Dialect mapping for JDBC type -1

following is my conf

# If you need a full JDBC configuration use the following :
db.url=jdbc:mysql://localhost:3306/db?useUnicode=true&characterEncoding=utf-8
db.driver=com.mysql.jdbc.Driver
db.user=root
db.pass=
#
# Connections pool configuration :
# db.pool.timeout=1000
# db.pool.maxSize=30
# db.pool.minSize=10
#
# If you want to reuse an existing Datasource from your application server, use:
# db=java:/comp/env/jdbc/myDatasource

# JPA Configuration (Hibernate)
# ~~~~~
#
# Specify the custom JPA dialect to use here (default to guess):
# jpa.dialect=play.db.jpa.MySQLDialect
jpa.dialect=org.hibernate.dialect.MySQLDialect
#


Nile Black
-----------------
http://www.jsxnc.com

[Guillaume Bort]

So what is the query you run exactly?