moving "logic" out of dataaccess layer would lead to lots of database connections

I have a method in my dataaccess layer that processes a file import. Such a file can have many records in it that need to be inserted into the database. This method already contains certain "logic or business rules" and more need to be added. Problem is if I move this out of the dataaccess layer each record would have to be inserted separately with a new connection. Obviously that is not very good when inserting thousands of records. (there is no guarantee that connections are pooled). So I'm kind of lost 开发者_如何学运维on how to solve this. The dataaccess layer class is getting bigger and bigger and in term of good design os probably violating several rules already.

Only thing that comes to my mind is producer-consumer -> business logic imports file and performs validation and puts validated records into a queue. A separate thread (the dataaccess layer) reads from that queue.

Any other ideas? is there a pattern for such problems?

You have a design problem. The business logic layer should be the one demarcating transactions, and the data access layer should always use the same connection for a given transaction.

You could be using Spring, for example, which would let you use declarative transactions and a connection pool, and would make sure the same connection is used for the whole transaction.

1. Have a separte business layer separate from dataaccess layer - here deal with your business rules , and when you are ready to call into data-access , create collection of business objects - pass then into the DA layer - the DA layer will process the objects - either as individual rows or as a single bulk operation -
2. Use Design patterns in your business layer that will keep the evolution simple.