should include or ..

Hi,

I'm using this below code in almost every JSP page. I was wondering is there a include where I can put this and recall instead of  this 2 line code in each and every page of jsp. I'm using Apache-Tomcat(4.0). Please guide me !!!

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

Thanks
princehyderabadAsked:
Who is Participating?
 
KuldeepchaturvediConnect With a Mentor Commented:
well for that ideally you should be using a connection pool where you specify the connection..
then create a bean which can do a context lookup for you and can return a connection back to the jsp... that what is done most of the time when it comes to database acess..
0
 
KuldeepchaturvediCommented:
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...
0
 
princehyderabadAuthor Commented:
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
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
bloodredsunConnect With a Mentor Commented:
Ideally, you should, as Kulli says, use a DataSource and a DataBase Connection Pool (DBCP)

see here how to with Tomcat 4
http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html

Faster, scales better and a lot less code in your pages, especially for somehting like SQLServer.
0
 
princehyderabadAuthor Commented:
Can anyone tell me step by step to use Connection POOL.
0
 
KuldeepchaturvediCommented:
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....:-)
0
 
bloodredsunCommented:
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>
0
 
princehyderabadAuthor Commented:
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.
0
 
KuldeepchaturvediCommented:
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..
0
 
princehyderabadAuthor Commented:
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");
0
 
KuldeepchaturvediCommented:
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
0
 
princehyderabadAuthor Commented:
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 ?
0
 
KuldeepchaturvediCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.