I’m writing in Spring in conjunction with Hibernate and every time I need to write access DB I face the same question:
where to place business logic?
Of course it is recommended to put it to services and keep DAO as simple as possible (the best to keep DAO only for CRUD). But what in the case when I would like to retrieve, let’s say Bookings. But only those with status ACTIVE (it means, that field status equals ACTIVE and additionally validFrom is LT today and validTo is GT today).
It will give me more complex query which I place in DAO method: List<Bookings> findActiveBookings()
So far so good.
Now let's assume that ACTIVE is also that booking which has status RESERVED and validTo date equal today.
I have two possibilities:
1) extends my DAO method and make SQL more complex at the same time putting some business conditions into DAO (if ACTIVE and date=..., if RESERVED and date=...)
2) create another method List<Booking>findReservedBookingForDate() and in services call them both and aggregate the results.
(of course it is worth to consider creating more generic method for example List<Booking> findBookingForState(BookingState state))
Which approach is better: keep simple DAO and move logic to services but have more DB access OR have one DB access but more complex query in DAO
It's better to have complex queries in DAO because generally speaking, the database is better at processing complex queries than code that you can write. Plus, doing aggregation/filtering in the service tier requires you to send a lot of data over the network from database to the app server, which would reduce throughput.
Remember that the main reason you want to put all your business logic in the service layer is to make it maintainable and easier to understand. So, as far as I'm concerned, all the if conditions and the for loops related to business logic should be in the service layer. It's fine if your DAO is doing filtering, as lo as the DAO methods are very concise. You should be able to look at the service implementation and know what the application is doing. It's fine if you DAO has more than basic crud methods.