CODEWITHSUNDEEP
springhibernatespring-data-jpa
Maglethong Spirr
Maglethong Spirr

Reputation: 443

Select and count in Spring

I have a relationship like States with cities (where cities get added from time to time). I wish to make a query specification to retrieve all states and the current number of cities it has. Ideally in the same query and as efficient as possible.

I Say specification because I wish to combine it with other specifications in different parts of the project.

Normally I would make a derived attribute with triggers in my database, but portability is a concern. What are my options for doing that in my Spring application without compromising efficiency to much?

Since I em pretty new to Spring I thought of doing a query for states and then count cities in a foreach loop, but there surely is a better alternative.

Upvotes: 0

Views: 1460

Answers (2)

StanislavL
StanislavL

Reputation: 57381

In your StateEntity you can use @Formula plus subquery to get the cities number.

@Entity
@Table(name="state")
public class StateEntity

    @Id
    @Column(name="state_id")
    private int id;

    ....
    @Formula(" (select count(*) from city c where c.state_id=state_id ")
    public int citiesCount;

Here I supposed that you have a table city with column state_id referencing state table primary key state_id

UPDATE: Actually it's converted in SQl like this

select
  state_id,
  ... some more columns
  (select count(*) from city c where c.state_id=state_id) as cities_count
from
... all the conditions and joins here...

So I think there will be just one big select with a subselects for each state you got in the results.

If you sometimes need and sometimes don't need the count you can define two classes StateEntity and StateEntityWithCount extends StateEntity. The count column with formula can be added to the StateEntityWithCount . Depending on whether you need the count you can use corresponding entity class.

About portability you are right. In common case it's true. The SQL is not portable but the SQL is standard and the expression is supported in most RDBMS as far as I know. Second, do you really need to change DB? What is probability of the change?

Finally. You can write a separate query (HQL based) to retrieve the city counts for each state you pass. That's also possible solution. See here an example

Upvotes: 1

Moshe Arad
Moshe Arad

Reputation: 3733

You want to build an HQL query, something like this:

select state_id, state_name, count(city_id)
from states
group by state_id, state_name

Then you can extract the results by writing something like this: 

SessionFactory sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();
  Session session = sessionFactory.openSession();
  session.beginTransaction();

  String SQL_QUERY = "select state_id, state_name, count(city_id)
                      from states
                      group by state_id, state_name ";

  Query query = session.createQuery(SQL_QUERY);

  for(Iterator it=query.iterate();it.hasNext();)
  {
   Object[] row = (Object[]) it.next();
   System.out.print("State Name: " + row[1]);
   System.out.println(" | Number of Cities: " + row[2]);
  }
  session.getTransaction().commit();
  session.close();

Upvotes: 1

Related Questions