should include or ..

you can do a <jsp:include file="myfile.jsp">
and myfile.jsp can have

Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection connection = DriverManager.getConnection...

Or any other better way ? Like Struts ? or Using Property ??
Bcoz I dont see people doing includes for this, becuase it has DB IP address or details and User/pass  etc., you know what I mean

Can anyone tell me step by step to use Connection POOL.

what database you are connecting to??
depending upon that some of the settings will change but in short this is what you need to do..

1. assuming you are on tomcat5 or above and your application name is MyTest..

this is how your MyTest.xml shd be looking its in c:\tomcat5.0\conf\catalina\localhost\MyTest.xml

<Context path="/MyTest" docBase="C:\Tomcat5.0\webapps\MyTest" debug="0" reloadable="true" workDir="C:\Tomcat5.0\webapps\MyTest\work" crossContext="true">
  <Resource name="jdbc/TESTDB" auth="Container" type="javax.sql.DataSource" />
            <ResourceParams name="jdbc/TESTDB">
               <parameter>
                 <name>username</name>
                 <value>user</value>
               </parameter>
               <parameter>
                 <name>password</name>
                 <value>Pwd</value>
               </parameter>
               <parameter>
                 <name>driverClassName</name>
                 <value>Driver for your database</value>
               </parameter>
<parameter>
<name>url</name>
<value>Url for your database</value>
</parameter>
<parameter>
  <name>max-connections</name>
  <value>10</value>
</parameter>
 </ResourceParams>
</Context>


then in your MyTest/WEB-INF/web.xml do this
<resource-ref >
         <res-ref-name>jdbc/TESTDB</res-ref-name>
         <res-type>javax.sql.DataSource</res-type>
         <res-auth>Container</res-auth>
      </resource-ref>

these lines should be the very last ones just before the ending tag of  </web-app>

now in your jsp or a bean that you will create this code needs to go..

try
{
     Context initCtx = new InitialContext();
     Context envCtx = (Context)initCtx.lookup("java:comp/env");

     DataSource ds = (DataSource)envCtx.lookup("jdbc/TESTDB");

     Connection con = ds.getConnection();

     Statement s = con.createStatement();
     String sql = "select * from person";
     ResultSet rs = s.executeQuery(sql);
}

Once this gets going we can think of JNDI caching and all other good stuff....:-)

If you're going to use a DataSource and use the connection in a JSP that you should look at JSTL and the sql library which make it very easy to connect.

Eg  If you just want a quick access to database...

You set the datasource if you haven't done so already:
<sql:setDataSource   var="dataSrc"  driver="oracle.jdbc.driver.OracleDriver"  url="jdbc:oracle:thin:@localhost:1521:ORCL"  user="username"  password="password" />

Then query like so:
 <sql:query var="queryResultsSet" dataSource="${dataSrc}"> <!-- here is the datasource from earlier -->
  select * from blog group by created desc limit ?
  <sql:param value="${6}"/>
</sql:query>

well Kuldeep I'm using MS-SQL server.

Infact you are increaing my code lines....

 Context initCtx = new InitialContext();
  Context envCtx = (Context)initCtx.lookup("java:comp/env");
   DataSource ds = (DataSource)envCtx.lookup("jdbc/TESTDB");

Instread of this 3 lines I would do what I'm doing currently...
ClassforName....
Connection Con....

Whats purpose of setting xmls and using 3 line code, why not go with my above 2 line.  Explain please if I'm approaching wrong. Also note in each jsp I'm closing the connection.


Bloodredsun !! I;m using all storeced procedures no need for query.

two things...
first.. you will not be putting this code in every jsp of yours..
you will just create a bean in which you will have all this code..
package Test;
public class DbBean
{
private boolean connected;
private Connection dbCon = null;

private Connection doConnect()
            throws SQLException
      {
if(connected)
return dbCon;            
                              String url=null;
            String initialContext = null;
            Context ctx = null;


                  try {      
      ctx = (Context)new InitialContext();
      DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/TESTDB");
                        dbCon = (Connection)ds.getConnection();
                        connected = true;
      }catch(SQLException sqlEx){
                        
                              throw sqlEx;
                                                                connected = false;                                                                                
                                                                return null;
                        }
                              
                  catch(Exception ex){
                        throw ex;
                                                               connected = false;
                                                                return null;  
                  }
              return dbCon;
    }


and in your jsp pages..
<jsp:useBean id="db" class="test.DbBean" scope="page" />

<%Connection con =db.connect();
con.executeQuery("whatever");
con.close();
%>

ideally speaking dbBean shd have a execSQL and close method in it and your shd be using it from the class only but for now this will do..

Is this called Connection POOL or its just other way of using it.

Also where am I including user/pass for DB
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/TESTDB");
u mean:
DataSource ds = (DataSource)ctx.lookup("19.19.19.19");

nopes...
your user id and passwords are stored in the xml that we modified earlier...
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/TESTDB"); literally means
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/TESTDB");
"jdbc/TESTDB" is the name that we have given to our connection pool in MyTest.xml

Okay I getting now.

I can change what u said: c:\tomcat5.0\conf\catalina\localhost\MyTest.xml
in my local machine as I'm using Tomcat5.0

But what abt my test server which is using Tomcat4.0. Which file do I need to change on the test server ?

tomcat4.0 used to have it in conf\web.xml...all the context were stored in that xml only.. so you will have to update it there