The database layer is intended to isolate the rest of the application from the details of the database -- how to make a connection, the syntax used to talk to the db engine, etc.

The .Net Entity Framework version of your code would be:

var id = customer.id;
var customerOrdersTotal = db.Orders.
                          Where(o => o.CustomerId == id && o.Active).
                          Sum(o => o.Total);

Note that this would get translated into your sql version, but the exact same thing could be done without involving sql at all.

If you have a Customer that has Orders, then it would be:

var customerOrdersTotal = customer.Orders.
                          Where(o => o.Active).Sum(o => o.Total);

And this version could be written regardless of whether Orders is a virtual list or an actual list of orders.

The business doesn't care where the data is kept or how it is retrieved. The point of a DAL is to abstract away the "how is this bit of data retrieved" and turn it into just the necessary (i.e. unavoidable) "how to request the data I want". Without a DAL you have to mix the two functions together, which means you can't change one without changing the other.

Note that about half the time, you don't ever end up making any changes...but when you do it's a real pain if they are mixed together.

Edit: I'll add that your implication that "where" clauses in the data access code is a business concern is incorrect. The data layer exists to serve business needs. The data can be carved up and served in an infinite number of ways, some more useful than others. Moving a condition from the business layer to the data layer simply means that the request is common enough to be included in the ways that usefully provided out of the box. Preventing new orders when existing active orders exceeds $X is a business decision.

The database layer is intended to isolate the rest of the application from the details of the database -- how to make a connection, the syntax used to talk to the db engine, etc.

The .Net Entity Framework version of your code would be:

var id = customer.id;
var customerOrdersTotal = db.Orders.
                          Where(o => o.CustomerId == id && o.Active).
                          Sum(o => o.Total);

Note that this would get translated into the sql your JPA version would use, but the exact same thing could be done without involving sql at all.

If you have a Customer that has Orders, then it would be:

var customerOrdersTotal = customer.Orders.
                          Where(o => o.Active).Sum(o => o.Total);

And this version could be written regardless of whether Orders is a virtual list or an actual list of orders.

The business doesn't care where the data is kept or how it is retrieved. The point of a DAL is to abstract away the "how is this bit of data retrieved" and turn it into just the necessary (i.e. unavoidable) "how to request the data I want". Without a DAL you have to mix the two functions together, which means you can't change one without changing the other.

Note that about half the time, you don't ever end up making any changes...but when you do it's a real pain if they are mixed together.

Edit: I'll add that your implication that "where" clauses in the data access code is a business concern is incorrect. The data layer exists to serve business needs. The data can be carved up and served in an infinite number of ways, some more useful than others. Moving a condition from the business layer to the data layer simply means that the request is common enough to be included in the ways that are usefully provided out of the box. Preventing new orders when existing active orders exceeds $X is a business decision.

The database layer is intended to isolate the rest of the application from the details of the database -- how to make a connection, the syntax used to talk to the db engine, etc.

The .Net Entity Framework version of your code would be:

var id = customer.id;
var customerOrdersTotal = db.Orders.
                          Where(o => o.CustomerId == id && o.Active).
                          Sum(o => o.Total);

Note that this would get translated into your sql version, but the exact same thing could be done without involving sql at all.

If you have a Customer that has Orders, then it would be:

var customerOrdersTotal = customer.Orders.
                          Where(o => o.Active).Sum(o => o.Total);

And this version could be written regardless of whether Orders is a virtual list or an actual list of orders.

The business doesn't care where the data is kept or how it is retrieved. The point of a DAL is to abstract away the "how is this bit of data retrieved" and turn it into just the necessary (i.e. unavoidable) "how to request the data I want". Without a DAL you have to mix the two functions together, which means you can't change one without changing the other.

Note that about half the time, you don't ever end up making any changes...but when you do it's a real pain if they are mixed together.

The database layer is intended to isolate the rest of the application from the details of the database -- how to make a connection, the syntax used to talk to the db engine, etc.

The .Net Entity Framework version of your code would be:

var id = customer.id;
var customerOrdersTotal = db.Orders.
                          Where(o => o.CustomerId == id && o.Active).
                          Sum(o => o.Total);

Note that this would get translated into your sql version, but the exact same thing could be done without involving sql at all.

If you have a Customer that has Orders, then it would be:

var customerOrdersTotal = customer.Orders.
                          Where(o => o.Active).Sum(o => o.Total);

And this version could be written regardless of whether Orders is a virtual list or an actual list of orders.

The business doesn't care where the data is kept or how it is retrieved. The point of a DAL is to abstract away the "how is this bit of data retrieved" and turn it into just the necessary (i.e. unavoidable) "how to request the data I want". Without a DAL you have to mix the two functions together, which means you can't change one without changing the other.

Note that about half the time, you don't ever end up making any changes...but when you do it's a real pain if they are mixed together.

Edit: I'll add that your implication that "where" clauses in the data access code is a business concern is incorrect. The data layer exists to serve business needs. The data can be carved up and served in an infinite number of ways, some more useful than others. Moving a condition from the business layer to the data layer simply means that the request is common enough to be included in the ways that usefully provided out of the box. Preventing new orders when existing active orders exceeds $X is a business decision.