PDA

Click to See Complete Forum and Search --> : want to create separate connection file for Servlets

mishu007
10-18-2007, 06:03 AM
I have Developed a Small application where The Employee Data read from a table.



package com.abhishek.login;


//File: Myconnection.java

/* A servlet to display the contents of the Oracle Employee database */

import java.io.*;
import java.sql.*;
import java.text.*;
import java.util.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class Myconnection extends HttpServlet
{
public String getServletInfo()
{
return "Servlet connects to Oracle database and displays result of a SELECT";
}

private Connection dbcon; // Connection for scope of ShowBedrock

// "init" sets up a database connection
public void init(ServletConfig config) throws ServletException
{
String loginUser = "scott";
String loginPasswd = "tiger";
String loginUrl = "jdbc:oracle:thin:@DBSERVER:1521:Abhishek";

// Load the PostgreSQL driver
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
dbcon = DriverManager.getConnection(loginUrl, loginUser, loginPasswd);
}
catch (ClassNotFoundException ex)
{
System.err.println("ClassNotFoundException: " + ex.getMessage());
throw new ServletException("Class not found Error");
}
catch (SQLException ex)
{
System.err.println("SQLException: " + ex.getMessage());
}
}

// Use http GET

public void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException
{
response.setContentType("text/html"); // Response mime type

// Output stream to STDOUT
PrintWriter out = response.getWriter();

out.println("<HTML><Head><Title>Employee</Title></Head>");
out.println("<Body><H1>Employee</H1>");

try
{
// Declare our statement
Statement statement = dbcon.createStatement();

String query = "SELECT empno, ename, ";
query += " job ";
query += "FROM EMP ";

// Perform the query
ResultSet rs = statement.executeQuery(query);

out.println("<table border>");

// Iterate through each row of rs
while (rs.next())
{
String m_name = rs.getString("ename");
String m_dept = rs.getString("empno");
String m_jobtitle = rs.getString("job");
out.println("<tr>" +
"<td>" + m_name + "</td>" +
"<td>" + m_dept + "</td>" +
"<td>" + m_jobtitle + "</td>" +
"</tr>");
}

out.println("</table></body></html>");
statement.close();
}
catch(Exception ex)
{
out.println("<HTML>" +
"<Head><Title>" +
"Bedrock: Error" +
"</Title></Head>\n<Body>" +
"<P>SQL error in doGet: " +
ex.getMessage() + "</P></Body></HTML>");
return;
}
out.close();
}
}





Here i have created a Connection for connecting with the Oracle server. So whenever i need that i need to put those line. Instead of wirting these line again and again can i write all those coding in a separate file and access those connection file into any particular servlets. Please help me with a sample example.
Khalid Ali
10-22-2007, 12:52 AM
yes, fairly simple (I am sure you have already figured this out),
Just create a new java class, name it to whatever suites your application naming standard. And then initialize everything in the constructor and have other methods for running a query, getting a connection obj etc.
Make sure that you make it so that it does not create a new connection every time unless needed.
mishu007
10-22-2007, 02:27 AM
any example?? please provide me a Example.

thanks in advance
mdjo
10-31-2007, 02:17 PM
I typically create a class I call "Db", and in that class I create a static function that gets a connection. Like:


public Connection getConnection()
{
Class.forName("...driver...");
... etc ...
Connection conn=DriverManager.getConnection(...whatever...);
return conn;
}


Then any time I need a connection I just write "Connection conn=Db.getConnection()".

Exactly what goes in the getConnection function depends on the database used, connection pooling, etc. But that's another advantage of putting it all in a function: If you switch from getting a new connection each time to using connection pooling, there's one place to change.

Note that on web pages you should be very careful about making connections: This takes a relatively long time, so if you write a JSP or servlet that makes and breaks twenty separate connections before returning output to the user, your performance will suck. At most, each JSP or servlet should get one connection at start up, use it for all DB access, and close it when done. Better still is to use connection pooling, but that's another subject.
slaughters
10-31-2007, 04:37 PM
....and close it when done...Just thought that this was important enough to be said twice :)