How to confing multiple DB connections FOR TOMCAT 5.0?

hi guys,
i try to use multiple db connections. but it seems not working. how to do that in Context.xml file? and how to map the connection to Tomcat 5?

i am new to this stuff, pls help me!!!

thanks a lot
viola
//this is the way we connect to DB. I think it is JNDI 
public WealthFrontService() {
        try {
            Context initContext = new InitialContext();
            ds = (DataSource)initContext.lookup("java:/comp/env/jdbc/OmlWebDS");
        }
        catch (Throwable e) {
            // Make sure you log the exception, as it might be swallowed
            logger.error("Cannot create service", e);
            throw new ExceptionInInitializerError(e);
        }
    }
 
public List getClientsByPartialName(String partialName) throws Exception {
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Connection conn = ds.getConnection(); //this is the connection
        List clients = new ArrayList();
       
        try {
            statement = conn.prepareStatement(sqlstring  );
            statement.setString(1, "%" + partialName + "%");
            statement.setString(2, "%" + partialName + "%");
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Client client = new Client();
               ............
                clients.add(client);
            }
        }
        finally {
            if (resultSet != null) resultSet.close();
            if (statement != null) statement.close();
            conn.close();
        }
       
        return clients;
    }
***************************************
in our context.xml
<Context>
//can i create multiple <Realm> under this <context>? i tried, but it does not work. could you guys give me an example?
  <Realm className="com.gbst.security.auth.GbstOmlWebTomcatLoginModule"
         dataSourceName="jdbc/OmlWebDS"
         digest="SHA-1"
         localDataSource="true"
         userTable="webuser"
         userRoleTable="allroles"
         userNameCol="username"
         roleNameCol="userrole"
         userCredCol="password"/>
 
  <Resource name="mail/Session" auth="Container" type="javax.mail.Session"/>
  <ResourceParams name="mail/Session">
    <parameter><name>mail.smtp.host</name><value>mail</value></parameter>
    </ResourceParams>
 
  <Resource name="jdbc/OmlWebDS" auth="Container" type="javax.sql.DataSource"/>
  <ResourceParams name="jdbc/OmlWebDS">
    <parameter><name>factory</name><value>org.apache.commons.dbcp.BasicDataSourceFactory</value></parameter>
    <parameter><name>maxActive</name><value>10</value></parameter>
    <parameter><name>maxIdle</name><value>10</value></parameter>
    <parameter><name>maxWait</name><value>20000</value></parameter>
    <parameter><name>username</name><value>ourusername</value></parameter>
    <parameter><name>password</name><value>pwd</value></parameter>
    <parameter><name>driverClassName</name><value>oracle.jdbc.driver.OracleDriver</value></parameter>
    <parameter><name>url</name><value>jdbc:oracle:thin:@........</value></parameter>
    <parameter><name>removeAbandoned</name><value>true</value></parameter>
    <parameter><name>removeAbandonedTimeout</name><value>60</value></parameter>
      <parameter><name>logAbandoned</name><value>true</value></parameter>
    <parameter><name>testOnBorrow</name><value>true</value></parameter>
    </ResourceParams>
 
 
  </Context>
 
**********************************
this is the DB connection i want to do in the context.xml file:
i have put the mapping in our web.xml like the following:
 
<resource-ref>
    <description>DB Connection</description>
    <res-ref-name>jdbc/CMTWebDS</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>
 
in context.xml:
<Context>
 
  <Realm className="com.gbst.security.auth.GbstOmlWebTomcatLoginModule"
         dataSourceName="jdbc/OmlWebDS"
         digest="SHA-1"
         localDataSource="true"
         userTable="webuser"
         userRoleTable="allroles"
         userNameCol="username"
         roleNameCol="userrole"
         userCredCol="password"/>
 
  <Resource name="mail/Session" auth="Container" type="javax.mail.Session"/>
  <ResourceParams name="mail/Session">
    <parameter><name>mail.smtp.host</name><value>mail</value></parameter>
    </ResourceParams>
 
  <Resource name="jdbc/OmlWebDS" auth="Container" type="javax.sql.DataSource"/>
  <ResourceParams name="jdbc/OmlWebDS">
    <parameter><name>factory</name><value>org.apache.commons.dbcp.BasicDataSourceFactory</value></parameter>
    <parameter><name>maxActive</name><value>10</value></parameter>
    <parameter><name>maxIdle</name><value>10</value></parameter>
    <parameter><name>maxWait</name><value>20000</value></parameter>
    <parameter><name>username</name><value>XXX</value></parameter>
    <parameter><name>password</name><value>XXXX</value></parameter>
    <parameter><name>driverClassName</name><value>oracle.jdbc.driver.OracleDriver</value></parameter>
    <parameter><name>url</name><value>jdbc:oracle:thin:@XXXXX</value></parameter>
    <parameter><name>removeAbandoned</name><value>true</value></parameter>
    <parameter><name>removeAbandonedTimeout</name><value>60</value></parameter>
      <parameter><name>logAbandoned</name><value>true</value></parameter>
    <parameter><name>testOnBorrow</name><value>true</value></parameter>
    </ResourceParams>
 
 // new DB CONNECTIONS:
 
  <Realm className="com.gbst.security.auth.GbstOmlWebTomcatLoginModule"
         dataSourceName="jdbc/CMTWebDS"
         digest="SHA-1"
         localDataSource="true"
         userTable="webuser"
         userRoleTable="allroles"
         userNameCol="username"
         roleNameCol="userrole"
         userCredCol="password"/>
        
  <Resource name="jdbc/CMTWebDS" auth="Container" type="javax.sql.DataSource"/>
  <ResourceParams name="jdbc/CMTWebDS">
    <parameter><name>factory</name><value>org.apache.commons.dbcp.BasicDataSourceFactory</value></parameter>
    <parameter><name>maxActive</name><value>10</value></parameter>
    <parameter><name>maxIdle</name><value>10</value></parameter>
    <parameter><name>maxWait</name><value>20000</value></parameter>
    <parameter><name>username</name><value>XXXXX</value></parameter>
    <parameter><name>password</name><value>XXXXX</value></parameter>
    <parameter><name>driverClassName</name><value>com.microsoft.sqlserver.jdbc.SQLServerDriver</value></parameter>
    <parameter><name>url</name><value>jdbc:sqlserver://XXXX</value></parameter>
    <parameter><name>removeAbandoned</name><value>true</value></parameter>
    <parameter><name>removeAbandonedTimeout</name><value>60</value></parameter>
    <parameter><name>logAbandoned</name><value>true</value></parameter>
    <parameter><name>testOnBorrow</name><value>true</value></parameter>
    </ResourceParams>
   
  </Context>

Open in new window

LVL 6
viola123Asked:
Who is Participating?
 
viola123Connect With a Mentor Author Commented:
hi, i found how to do it:

<parameter><name>url</name><value>jdbc:oracle:thin:@XXXXX;databaseName=xxx;</value></parameter>
it works.

thanks a lot
0
 
objectsCommented:
what exactly isn't working?

0
 
viola123Author Commented:
hi,
when go to the first page to login, it does not do anything when you click the submit button. i mean it stays on the same page after you click the button.

thanks
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
viola123Author Commented:
if you remove the part i add into the context.xml file, it will work.
0
 
objectsCommented:
do you need to add access to an additional db?

check the logs, you mind find some error messages in there that will help point at the cause

0
 
viola123Author Commented:
hi, i found the problem: i have to add sqljdbc.jar to Tomcat lib folder, otherwise, the connection won't work. but one more question: how to specify the database name in <Realm>?
SOLUTION:
in web.xml file, add the following:
<resource-ref>
    <description>DB Connection</description>
    <res-ref-name>jdbc/CMTWebDS</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>
 **************************
in context.xml:
<Context>
 
  <Realm className="com.gbst.security.auth.GbstOmlWebTomcatLoginModule"
         dataSourceName="jdbc/OmlWebDS"
         digest="SHA-1"
         localDataSource="true"
         userTable="webuser"
         userRoleTable="allroles"
         userNameCol="username"
         roleNameCol="userrole"
         userCredCol="password"/>
 
  <Resource name="mail/Session" auth="Container" type="javax.mail.Session"/>
  <ResourceParams name="mail/Session">
    <parameter><name>mail.smtp.host</name><value>mail</value></parameter>
    </ResourceParams>
 
  <Resource name="jdbc/OmlWebDS" auth="Container" type="javax.sql.DataSource"/>
  <ResourceParams name="jdbc/OmlWebDS">
    <parameter><name>factory</name><value>org.apache.commons.dbcp.BasicDataSourceFactory</value></parameter>
    <parameter><name>maxActive</name><value>10</value></parameter>
    <parameter><name>maxIdle</name><value>10</value></parameter>
    <parameter><name>maxWait</name><value>20000</value></parameter>
    <parameter><name>username</name><value>XXX</value></parameter>
    <parameter><name>password</name><value>XXXX</value></parameter>
    <parameter><name>driverClassName</name><value>oracle.jdbc.driver.OracleDriver</value></parameter>
    <parameter><name>url</name><value>jdbc:oracle:thin:@XXXXX</value></parameter>
    <parameter><name>removeAbandoned</name><value>true</value></parameter>
    <parameter><name>removeAbandonedTimeout</name><value>60</value></parameter>
      <parameter><name>logAbandoned</name><value>true</value></parameter>
    <parameter><name>testOnBorrow</name><value>true</value></parameter>
    </ResourceParams>
 
 // new DB CONNECTIONS:
 //one more question: how to set the database name?????
    <Resource name="jdbc/CMTWebDS" auth="Container" type="javax.sql.DataSource"/>
  <ResourceParams name="jdbc/CMTWebDS">
    <parameter><name>factory</name><value>org.apache.commons.dbcp.BasicDataSourceFactory</value></parameter>
    <parameter><name>maxActive</name><value>10</value></parameter>
    <parameter><name>maxIdle</name><value>10</value></parameter>
    <parameter><name>maxWait</name><value>20000</value></parameter>
    <parameter><name>username</name><value>XXXXX</value></parameter>
    <parameter><name>password</name><value>XXXXX</value></parameter>
    <parameter><name>driverClassName</name><value>com.microsoft.sqlserver.jdbc.SQLServerDriver</value></parameter>
    <parameter><name>url</name><value>jdbc:sqlserver://XXXX</value></parameter>
    <parameter><name>removeAbandoned</name><value>true</value></parameter>
    <parameter><name>removeAbandonedTimeout</name><value>60</value></parameter>
    <parameter><name>logAbandoned</name><value>true</value></parameter>
    <parameter><name>testOnBorrow</name><value>true</value></parameter>
    </ResourceParams>
   
  </Context>

Open in new window

0
 
objectsConnect With a Mentor Commented:
you don't , the database is specified in the datasource (in the url)

0
 
objectsCommented:
Didn't my direction help get you to the solution?
0
 
objectsCommented:
split between me and viola123

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.