[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

should include or ..

Posted on 2005-04-25
15
Medium Priority
?
214 Views
Last Modified: 2013-11-18
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
0
Comment
Question by:princehyderabad
  • 6
  • 5
  • 2
13 Comments
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13861603
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
 

Author Comment

by:princehyderabad
ID: 13861640
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
 
LVL 19

Accepted Solution

by:
Kuldeepchaturvedi earned 200 total points
ID: 13861674
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Assisted Solution

by:bloodredsun
bloodredsun earned 200 total points
ID: 13861937
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
 

Author Comment

by:princehyderabad
ID: 13862430
Can anyone tell me step by step to use Connection POOL.
0
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13862529
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
 
LVL 29

Expert Comment

by:bloodredsun
ID: 13862568
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
 

Author Comment

by:princehyderabad
ID: 13862870
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
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13863110
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
 

Author Comment

by:princehyderabad
ID: 13863132
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
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13863140
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
 

Author Comment

by:princehyderabad
ID: 13863190
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
 
LVL 19

Expert Comment

by:Kuldeepchaturvedi
ID: 13863213
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are two main kinds of selectors in CSS: One is base selector like h1, h2, body, table or any existing HTML tags.  For instance, the following rule sets all paragraphs (<p> elements) to red: (CODE) CSS also allows us to define our own custom …
Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question