Hi, I wonder whether Should getConnection() be placed in Init() of a servlet in the situation that multiple users concurrently access DB using service method. My though is, if we place getConnection() in Init() and dbConn is member variable instead of local variable then multiple users have to share a single connection to database. This is resource saving but user will have to wait till other finish using the connection if there're more than one users concurrently accessing DB through the service method. My thought is lookup DB is placed in init() but getConnection is placed in service method, your thought?
My thought is that servlets should be use stricktly for communication with the HttpServletResponse and HttpServletRequest and that there should be 0 database calls inside a servlet. The database calls belong in the data model of your web application. In addition, you should use a Connection Pool for your database as estalishing a connection to a database is the most time consuming event of of the whole process. And last but not least, if you just absolutly have to have your database calls inside a servlet, then it really doesn't matter where you put it. Web applications are multithreaded. If you tell us what Database you are using and what Application server you are using (Tomcat, Resin, JBoss, etc) then we can direct you to the appropriate information.
Yes, my application server is WAS 4.05 and I do not implement DB logic in servlet, actually I maintain a static datasource variable and getconnection is done in a DataAccess object. But the thing I concern is Do I make use of connection aggressively so that each connection is used at maximum. For each DB operation I always generate a connection for it even I know that that connection comes from a pool but I still wonder whether it's expensive operation. You know an operation from user's perspective may involve several DB operations. In my opinion,in order to make use of DB connection, there should be a business class that group together these DB operations into a single business operation. Then this business operation should use one connection to DB. It's the method in business component that should generate DB connection not every single DB operations at DB access object. My failure is that I couldn't define well basic DB operations so that I can reuse them for different busines operation. What I've done now is I use a single class for DB Access and embed some business logic in it: for example I use the function transferMoney(Account accounta, Account account b, long sum) instead of 3 functions: 2 in DB access object: credit(Acount acc, long money, Connection conn), debit(Account acc, long money, Connection conn) and one in business object: transferMoney(Account accounta, Account account b, long sum). Does any one know the rule to separate these kinds of stuff. The purpose is to re-use of basic DB operations while make use of db-connection by grouping DB-connections according to business logic and they all share a connection object ?