how to connect to database from doPost() by creating Conncetion object in another class

[Deepak Mantry]

Hi friends,

I am trying the follow code,
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
DBConnection db = null;
Connection conn = null;
PreparedStatement pst = null;

ResultSet rs = null;

try {
db = new DBConnection();
conn = db.getConnection();

pst = conn
.prepareStatement("select ename from employee_hobby where hobby=?");
pst.setString(1, request.getParameter("hobby"));
but i am getting null as value of conn every time..

public class DBConnection {

public Connection getConnection() {
try {
System.out.println("getConncetion() of DBConnection...");
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
Connection conn = DriverManager.getConnection(
"jdbcracle:thin:@localhost:1521rcl", "scott", "tiger");
return conn;
} catch (Exception e) {
// TODO: handle exception
return null;
}
}

[Ulf Dittmer]

I see there's a TODO in the code; I'd start by addressing that. At a minimum, print something to the log files, like "e.printStackTrace()". I bet that's where the problem is - ignoring an exception that you simply cannot ignore without consequences.

[Amit Singh Hora]

you can do it in this way
1)Declare a Connection Class
2)now either set the <loadonstartup> element an attribute of servlet in web.xml to some +ve value

The ContextListener Class will be called when context will be initialized and that the servlet will load on start up
now in either of them declare a method that will connect with the database and then set this connection object as your context attribute and get the attribute weherever required and procced with your
database operations

[Bear Bibeault]

Amit Singh Hora wrote:you can do it in this way

Do not do it that way. Creating a connection and holding it in application scope for the duration of the application is a very very bad idea.

Connections should be held open for as little time possible. Open one when you need it, and close it as soon as possible.

Container-managed connection pooling should be used whenever possible.

[Amit Singh Hora]

Thanks bear for pointing it out
i serached net and got some usefull stuff related to connection pooling and how to maintain it
visit this
http://pdf.coreservlets.com/first-edition/CSAJSP-Chapter18.pdf