Link to home
Start Free TrialLog in
Avatar of viola123
viola123

asked on

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

Avatar of Mick Barry
Mick Barry
Flag of Australia image

what exactly isn't working?

Avatar of viola123
viola123

ASKER

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
if you remove the part i add into the context.xml file, it will work.
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

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

SOLUTION
Avatar of Mick Barry
Mick Barry
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Didn't my direction help get you to the solution?
split between me and viola123