NetBeans Forums

 FAQFAQ   SearchSearch   MemberlistMemberlist   RegisterRegister   ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
  

Configuring Connection Pool, DataSource, with MySQL, Tomcat 6

 
Post new topic   Reply to topic    NetBeans Forums -> Java EE Users
View previous topic :: View next topic  
Author Message
eboraks



Joined: 09 Jul 2011
Posts: 1
PostPosted: Sun Jul 10, 2011 5:26 pm    Post subject: Configuring Connection Pool, DataSource, with MySQL, Tomcat 6 Reply with quote
Hello everyone,
I am new here, I am doing the switch from LAMP to Java and I have being following some examples from the web and Deitel book.

Here is my problem. I am trying to build a simple dictionary Web App, but I can’t get the dataSource to work with the connection pool. I keep getting null DataSource, and off course my page doesn't load.

I need help figuring out what I am missing in my configuration?

Here are the configuration details:
Application Folder
- Web Pages
- Index.xhtml
- META-INF -> context.xml
- WEB-INF -> web.xml
- Source Packages
- webtime
- DictionaryBean.java
- Libraries
- JSF…. (from NetBeans)
-JDK 1.6 (defaults)
- MySQL JDBC (I added it)

Configuring the Connection Pool:

Context.xml:
<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/WebApp_JSF">
<Resource name="jdbc/WebApp_JSF" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="root" password="22142214" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/dictionary"></Resource>
</Context>

Web.xml:
<resource-ref>
<description>Dictionary App database</description>
<res-ref-name>jdbc/WebApp_JSF</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>

Initiating the dataSource in DictionaryBean.java:
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.annotation.Resource;
import javax.faces.bean.ManagedBean;
import javax.sql.DataSource;
import javax.sql.rowset.CachedRowSet;

@ManagedBean( name="dictionaryBean")
public class DictionaryBean {

…. (variables)

// allow the server to inject the DataSource
@Resource(name="jdbc/WebApp_JSF")
DataSource dataSource;

….(getters and setters)

// return a results set of entries
public ResultSet getDictionaryDefinitions() throws SQLException
{


// Check is data source inject into the server
if(dataSource == null){
throw new SQLException ("Unable to obtain DataSource");
}
……..}

(I STOP HERE BECAUSE THE APPLICATION BREAK IN THIS EXCEPTION)

index.xhtml:
<html xmlns="http://www.w3.org/1999/xhtml"
xmlns:h="http://java.sun.com/jsf/html"
xmlns:f="http://java.sun.com/jsf/core">
<h:head>
</h:head>
<h:body>
<h1>Dictionary Definitions</h1>
<h:form>
<p><h:commandButton value="Add Definition" action="addDefinition"></h:commandButton></p>
</h:form>
<h:dataTable value="#{dictionaryBean.dictionaryDefinitions}" var="definiion"
cellpadding="5" cellspacing="0">
<h:column>
<f:facet name="header">Word Entry</f:facet>
#{definiion.ENTRY}
</h:column>
<h:column>
<f:facet name="header">Word Definition</f:facet>
#{definiion.DEFINITION}
</h:column>
</h:dataTable>
</h:body>
</html>




Environment: Windown 7, MySQL 5.5.8, Tomcat 6 (both part of Xampp package) and NetBeans 7.0.
Back to top
mdeggers



Joined: 28 Jan 2009
Posts: 208
PostPosted: Mon Jul 11, 2011 7:53 pm    Post subject: Reply with quote
Short answer:

Tomcat does not support managed beans. It is a servlet container / JSP
server, and not a full blown application server (like
Glassfish). Also, if you are going to use servlet 3.0 specifications
to write your web application, then you will need to move to Tomcat 7.

If you want to use managed beans and the servlet 3.0 specification,
they you'll want to use Glassfish 3.1. Otherwise, see below on how to
work with Tomcat 6.

Longer answer:

To Read:

There is a lot of documentation available on getting Tomcat 6 to
provide a pooled database application to your web application. I
suggest the following at a minimum:

http://tomcat.apache.org/tomcat-6.0-doc/jndi-resources-howto.html#JDBC_Data_Sources

If you use Hibernate, then this is also helpful:

http://wiki.apache.org/tomcat/TomcatHibernate

Configuragtion:

Next, do not add the JDBC jar to your application if you want Tomcat
to manage the database connection / pooling. The explanation is rather
long (has to do with class loaders), but the end result is that Tomcat
will not 'see' the JDBC class and will not be able to connect to the
database.

Instead, add the jar with the JDBC class to %CATALINA_HOME%\lib. This
will let Tomcat find the JDBC class and create the database pool.

Once you do this, DO NOT also add this jar to your project. NetBeans
will find the required classes since the project is associated with
the Tomcat server. However, it shouldn't have to (see below).

Code:

Making sure you close all of your database connections when you're
done with them can be a bit tricky. In fact what you're doing is
releasing a database connection to go back to the pool. If you don't
do this properly, you'll find out that you run out of database
connections after a while.

What I do (when I hand code) is to create a base class with all of the
plumbing required. I then inherit from this base class to do the
actual SQL operations. Both of these classes are POJOs, so you can use
them anywhere you get your database connections via JNDI.

You use a servlet to get information from a page, and then send it off
to a validator. Once the input is validated, then you send the
information off to the POJO to do the work. Finally, you take the
results and send it off to another page to be rendered.

JSF (or Spring, or Struts, or Wicket, or . . .) make this a lot easier
to manage for large applications. However, I would recommend coding at
least one small application by hand so that you understand what's
going on under the covers.

I have included some code below that I've used to develop resource
leak tests. Use at your own risk, but it should get you an idea.

Here's an example base class for a database request that does not
require transactions:

Code:

/*
 *  Copyright 2011 Mark Eggers.
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 *  under the License.
 */
package org.mdeggers.leakrs.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 *
 * @author mdeggers
 */
public class BaseDAO {

    protected Connection conn = null;
    protected ResultSet rs = null;
    protected Statement stmt = null;
    protected PreparedStatement ps = null;

    protected String dsname = SQLStatements.DBJNDI; // ugly hack for now

    private Log log = LogFactory.getLog(this.getClass());

    public BaseDAO() {
    }
   
    protected void getConnection() {
        DataSource ds = null;
        try {
            Context icontext = new InitialContext();
            Context envCtx = (Context) icontext.lookup("java:comp/env");
            ds = (DataSource) envCtx.lookup(dsname);
        } catch (NamingException ex) {
            log.fatal("Failed to get JNDI name: " + dsname);
            log.fatal(ex);
            cleanup();
        }
        if (ds != null) {
            try {
                conn = ds.getConnection();
            } catch (SQLException ex) {
                log.fatal("Failed to get database connection");
                log.fatal(ex);
            }
        }
    }

    protected void getConnection(String dsname) {
        this.dsname = dsname;
        getConnection();
    }
   
    protected void cleanup() {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                log.fatal("Failed to close nonnull ResultSet");
                log.fatal(ex);
            } finally {
                rs = null;
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException ex) {
                log.fatal("Failed to close nonnull PreparedStatement");
                log.fatal(ex);
            } finally {
                ps = null;
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException ex) {
                log.fatal("Failed to close nonnull Statement");
                log.fatal(ex);
            } finally {
                stmt = null;
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException ex) {
                log.fatal("Failed to close nonnull Connection");
                log.fatal(ex);
            } finally {
                conn = null;
            }
        }
    }
}


I've got a lot of logging built in, which you'll probably want to
ignore while starting out. I have a helper class for the JNDI name
(dsname), but you may want to just hard code it :p.

Here's an example of how it's used:

Code:
/*
 *  Copyright 2011 Mark Eggers.
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 *  under the License.
 */
package org.mdeggers.leakrs.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.mdeggers.leakrs.model.Months;

/**
 *
 * @author mdeggers
 */
public class ViewMonths extends BaseDAO {
    private ArrayList<Months> months;
    private Log log = LogFactory.getLog(this.getClass());
    private int year;

    public ViewMonths() {
    }

    public void setYear(int year) {
        this.year = year;
    }

    public int getYear() {
        return this.year;
    }

    public ArrayList<Months> getMonths() {
        this.months = new ArrayList<Months>();
        getConnection();
        if (conn != null) {
            try {
                ps = conn.prepareStatement(SQLStatements.VMONTHS);
                if (ps != null) {
                    ps.setInt(1, this.year);
                    rs = ps.executeQuery();
                    while(rs.next()) {
                        Months amonth = new Months();
                        amonth.setYear(this.year);
                        amonth.setMonthnum(rs.getInt("monthnum"));
                        this.months.add(amonth);
                    }
                } else {
                    log.error("Failed to create prepared statement");
                }
            } catch (SQLException ex) {
                log.error(ex);
                log.error("Error Code: " + ((SQLException) ex).getErrorCode());
                log.error("Message: " + ((SQLException) ex).getMessage());
                cleanup();
                return this.months;
            }
        }

        if (log.isInfoEnabled()) {
            log.info("Number of rows returned: " + this.months.size());
        }
        cleanup();
        return this.months;
    }
}


Here is the portion of the SQLStatements class:

Code:
    // List of months
    public static final String VMONTHS =
      "select distinct month(date_sold) as monthnum"
       + " from sales_hist"
       + " where year(date_sold) = ?"
       + " order by monthnum";


And here's the model class.

Code:

public class Months {
    private int monthnum;
    private int year;
    private Date month;

    public Months() {
        this.year = 2010;
    }

    public int getMonthnum() {
        return monthnum;
    }

    public void setMonthnum(int monthnum) {
        this.monthnum = monthnum;
    }

    public int getYear() {
        return year;
    }

    public void setYear(int year) {
        this.year = year;
    }

    public Date getMonth() {
        // should be a better way
        GregorianCalendar cal = new GregorianCalendar(this.year,monthnum-1,1);
        this.month = cal.getTime();
        return this.month;
    }
}


I tend to put SQL statements in a separate class to keep code more
organized.

This entire mess is used to handle a selection list with JSTL fmt so
the results are internationalized based on locale.

Finally:

Before plunging into servlet specification 3.0 (which requires Tomcat
7 BTW), you might want to work with servlet specification 2.5 (which
runs on Tomcat 6). It's a bit more work, but you'll end up
understanding how the plumbing is laced together. This will help you
both appreciate what servlet spec 3.0 does, and understand what it's
doing under the covers.

A good book for this is:

Head First Servlets and JSP, 2nd Edition. The first edition covers
servlet spec 2.4.

Welcome to Java.

. . . . just my two cents.

/mde/
Back to top
hikarimay10



Joined: 18 Jul 2011
Posts: 6
PostPosted: Fri Jul 22, 2011 12:51 pm    Post subject: Reply with quote
Hi:

I am also trying to connect to a JDBC driver using MySQL however, I am not using a connection pool. I have a method that sets up the connection as shown below:
Code:
Class.forName("com.mysql.jdbc.Driver").newInstance();
                setConnectionUrl("jdbc:mysql://localhost:3306/database?" +
                        "user=user&password=password");


It had been working but now I am not sure what went wrong and it gives error message shown below:
Code:
Cannot get connection, Details: java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/database?user=user&password=password


Then I added the jar to the Tomcat lib directory like u suggested and removed the library from my project and now I get:
Code:
Cannot find driver class, Details: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver



Do you have any suggestions? Do I need a connection pool?

I also had the similiar JDBC driver error when trying to connect to Microsoft SQL server 2008 R2 using Microsoft driver and when I used Glassfish. Is the problem with NetBeans?

I am not sure what else to try since I had already tried suggestions from http://social.msdn.microsoft.com/forums/en-US/sqldataaccess/thread/1c1a1313-057d-46d4-9ca7-82fc4f23a150/

Thanks.
Back to top
mdeggers



Joined: 28 Jan 2009
Posts: 208
PostPosted: Fri Jul 22, 2011 5:52 pm    Post subject: Reply with quote
I've never programmed a Java web application without using a connection
pool. I can't think of a use case off the top of my head where not using a
connection pool is desirable.

That said, when you write a web application that runs under Tomcat, the
JAR file should be accessible from only one place. So:

1. Put the JAR file in $CATALINA_HOME/lib and NOT in app/WEB-INF/lib

This makes the JAR file available to both Tomcat and any web application
that is loaded in Tomcat. This is useful when you use Tomcat to create
database connection pools and access the database connection via JNDI.

This is the recommended way of working with database connections.
Changing database names, drivers, user names, passwords, etc. is then
a matter of configuration, not programming / compilation.

This is NOT the recommended way of working with general web
application jar files. Placing application JAR files in $CATALINA_HOME/lib
will create problems when you want to upgrade your application. You'll
basically have to stop Tomcat, replace the JAR files in
$CATALINA_HOME/lib, and then restart Tomcat if you want to upgrade that
part of your application.

In certain instances, placing application JAR files in $CATALINA_HOME/lib
can also lead to PERMGEN memory leaks. See this for example:

http://blogs.oracle.com/fkieviet/entry/classloader_leaks_the_dreaded_java

2. Put the JAR file in app/WEB-INF/lib and NOT in $CATALINA_HOME/lib

This makes the JAR file available only to your application. Tomcat will
not be able to see it, and Tomcat's connection pooling mechanism will
not be available.

In order to do this, make sure that the appropriate JAR file is included
in your project, and that the Package checkbox is checked.

Again, if you do this make sure that the JAR file DOES NOT EXIST in
$CATALINA_HOME/lib.

When you select a server for your web application (when creating th
project), NetBeans adds all of the JAR files found in the appropriate
spots to your CLASSPATH for building the project. For Tomcat 6, this
means that JAR files in $CATALINA_HOME/lib are included during
the building of the project.

If the database JAR is present in $CATALINA_HOME/lib, you do not need
to add the library to your project as well. To confirm that the library
is indeed available, do the following:

1. Go to the Libraries node of your project and expand it
2. Select the server node and expand it

You will see the list of JARS that NetBeans will use when building your
project. This is why (for example) you don't need to include the servlet
api in your project. NetBeans gets this information from the Tomcat
server.

Summary:

1. Use database connection pools
2. Put your driver library in $CATALINA_HOME/lib
3. Do NOT add the driver anywhere else

OR

1. Do not use database connection pools
2. Add your driver to your project and select the Package checkbox
3. Make SURE the JAR file is not in $CATALINA_HOME/lib

Connection pools are preferable.

For MS SQL, stay away from drivers that use JNI components (DLLs or
.so files). Managing JNI in an application server is pretty tricky.

Instead, take a look at the jTDS driver available from:

http://jtds.sourceforge.net/

. . . . just my two cents.

/mde/
Back to top
hikarimay10



Joined: 18 Jul 2011
Posts: 6
PostPosted: Mon Jul 25, 2011 12:01 pm    Post subject: Reply with quote
Hi mdeggers:

I am using MySQL db and Apache Tomcat server. I have made the changes according to Apache's docs and used a connection pool however, I get the message:
Code:
org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class 'com.mysql.jdbc.Driver' for connect URL 'jdbc:mysql://localhost:3306/myDB'


So far I have the jar file in the $CATALINA_HOME/lib directory and followed the steps at http://tomcat.apache.org/tomcat-6.0-doc/jndi-resources-howto.html#JDBC_Data_Sources. However I am not sure what else is wrong.

In the web application folders I expanded and added the code as follows:

web.xml
Code:
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>
    <resource-ref>
        <description>
    Resource reference to a factory for java.sql.Connection
    instances that may be used for talking to a particular
    database that is configured in the Context
    configurartion for the web application.
        </description>
        <res-ref-name>
    jdbc/myDB
        </res-ref-name>
        <res-type>
    javax.sql.DataSource
        </res-type>
        <res-auth>
    Container
        </res-auth>
    </resource-ref>

</web-app>


Then in context.xml
Code:
<?xml version="1.0" encoding="UTF-8"?>
<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/TweetCollector">
  <Resource name="jdbc/myDB"
            auth="Container"
            type="javax.sql.DataSource"
            username="root"
            password="administrator"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/myDB"
            maxActive="8"
            maxIdle="4"/>
</Context>


Then the code to connect:
Code:
 Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource ds = (DataSource)
  envCtx.lookup("jdbc/myDB");

setConnectionDB(ds.getConnection());



I am getting the same message I was getting when I was not using connection pool. The jar file is only in Tomcat and not elsewhere. If I expand the libraries folder I can only see it in the Tomcat lib folder too and not the other libraries. What should I do??
Back to top
mdeggers



Joined: 28 Jan 2009
Posts: 208
PostPosted: Mon Jul 25, 2011 5:03 pm    Post subject: Reply with quote
Hmm.

This is getting to be more of a Tomcat question than a NetBeans question,
but I'll give it a shot.

Code:
<?xml version="1.0" encoding="UTF-8"?>
<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" path="/TweetCollector">
  <Resource name="jdbc/myDB"
            auth="Container"
            type="javax.sql.DataSource"
            username="root"
            password="administrator"
            driverClassName="com.mysql.jdbc.Driver"
            url="jdbc:mysql://localhost:3306/myDB"
            maxActive="8"
            maxIdle="4"/>
</Context>


I'll assume the above has a copy / paste error. Otherwise you have two
XML document declarations which will create problems. Remove one of
them.

Also, since MySQL normally runs on port 3306, you don't need to declare
it. It doesn't cause any harm, but there's no reason for it.

Another line that you might add is a validation query. You can then check
the Tomcat logs and see what errors your get. Change your context.xml
file to include the following in the Resource element.

Code:
validationQuery="SELECT 1"


The logs will tell you why it cannot connect.

The next question I have is can you connect to the database using the
credentials given in context.xml? MySQL has some interesting permissions,
including being able to connect to 127.0.0.1 while not being able to
connect to localhost.

It's a good idea to check to see if you can get a database connection. If
you are running Linux, you can do the following from the command line:

mysql -u <username> -p <database-name>

The program will prompt for a password, and then you should get:

Code:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Type quit; (with the semicolon) and press enter to leave.

If you don't get a database connection with the command line, then you
have a MySQL or firewall problem.

Finally, let's take a look at your code. First of all, a lot of the methods
called throw exceptions that you should catch and log. This will tell you
a lot about what's not working. For example, here's a excerpt from the
code I posted earlier in the thread:

Code:
protected void getConnection() {
    DataSource ds = null;
    try {
        Context icontext = new InitialContext();
        Context envCtx = (Context) icontext.lookup("java:comp/env");
        ds = (DataSource) envCtx.lookup(dsname);
    } catch (NamingException ex) {
       log.fatal("Failed to get JNDI name: " + dsname);
       log.fatal(ex);
       cleanup();
    }
    if (ds != null) {
        try {
            conn = ds.getConnection();
        } catch (SQLException ex) {
           log.fatal("Failed to get database connection");
           log.fatal(ex);
           cleanup();
        }
    }
}


And here's the cleanup() routine in the same class:

Code:
protected void cleanup() {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException ex) {
           log.fatal("Failed to close nonnull ResultSet");
           log.fatal(ex);
        } finally {
           rs = null;
        }
    }
    if (ps != null) {
        try {
             ps.close();
        } catch (SQLException ex) {
           log.fatal("Failed to close nonnull PreparedStatement");
           log.fatal(ex);
        } finally {
            ps = null;
        }
    }
    if (stmt != null) {
        try {
            stmt.close();
        } catch (SQLException ex) {
           log.fatal("Failed to close nonnull Statement");
           log.fatal(ex);
        } finally {
            stmt = null;
        }
    }
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException ex) {
           log.fatal("Failed to close nonnull Connection");
           log.fatal(ex);
        } finally {
            conn = null;
        }
    }
}


You must clean up after exceptions. If you do not, the database connection
in the pool will still be marked as used, and you will eventually run out of
database connections.

You must properly clean up and close after every connection. If you do
not, the database connection in the pool will still be marked as used, and
you will eventually run out of database connections.

The logging is also important. When things go wrong, it will tell you what
to look for. If you don't want to implement logging (commons-logging plus
log4j is what I use), then at least do a System.out.println(). Tomcat should
log these in $CATALINA_HOME/logs/catalina.out, so you can see what's
going on.

Finally, I have no idea what the following line does:

setConnectionDB(ds.getConnection());

I assume that this is a method somewhere in your class that sets the
connection from the DataSource? If so, hopefully you catch the exceptions
there as well.

Summary:

1. Make sure you can connect to your database - case is important
2. Add a validation query to your context.xml
3. Catch and log exceptions - it will tell you what's going wrong
4. Read the logs

My guess is that you cannot connect to the database. It's one of the
following:

1. Firewall (but then NetBeans could not connect)
2. Permissions (try 127.0.0.1 in your URL)
3. Case or other error in URL
4. You really do have two XML doc declarations in context.xml

. . . . just my two cents.

/mde/
Back to top
hikarimay10



Joined: 18 Jul 2011
Posts: 6
PostPosted: Mon Jul 25, 2011 9:14 pm    Post subject: Reply with quote
Hello again mdeggers:

Sorry, for posting so much but I'm not sure if the problem is NetBeans or Apache or MySQL.

    The code I posted HAS a copy / paste error in my XML document .
    I added a cleanup() method in the same class although its a bit different.
    I added a log and error methods which uses the System.out.println() so it shows up in the tomcat tab in NetBeans
    I removed the port 3306 and tried the 127.0.0.1 instead.
    I added the validationQuery="SELECT 1" to the resource element
    I am not sure how to test if I get a database connection from Windows command line. How would I do this??


I also noticed something, my main program sends across the SQL statement but it is done in a for loop because I have an array of data so the method calls the db connection and the method to insert more than once. I noticed that the first command is executed but then the remanining commands are ignored and finaly when I call to close the resouces at the end then it gives an error. Therefore, I have a method partially shown:

Code:
.....
.....
for (Status status : statuses) {

.....
.....
try {
                getDbConnector().setupDatabaseConnection();
//first SQL
                getDbConnector().updateData(queryStatus);
//second SQL
                getDbConnector().updateData(queryUser);
               
            } catch (SQLException sqlException) {
                getDbConnector().errorMessageHandlerDB("storeRawTweets: DB error " + sqlException.toString());
            }
        } // end for

        // close db resources
        getDbConnector().closeConnectionDB();



getDbConnector() is the class with all the database methods. So I setup the connection then send the SQL statement then close the connection. I am not sure if the problem is the for loop.

Then inside the db class I have:

Code:
public void setupDatabaseConnection() {

        // Set boolean to indicate that database connection is not yet valid
        this.setIsDBValid(false);

        try {

            // creates a connection if none already exists
            if (getConnectionDB() == null) {

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

                // Store connection
                setConnectionDB(ds.getConnection());

                // No errors but display log
                this.logMessageHandlerDB("setupDatabaseConnection: Successful");
                this.setIsDBValid(true);
            }

        } catch (NamingException e) {
            this.errorMessageHandlerDB("Failed to get JNDI name:, Details: " + e.toString());
            cleanup();

        } catch (java.lang.NullPointerException e) {

            this.errorMessageHandlerDB("setupDatabaseConnection: Cannot find driver class, Details: " + e.toString());
            cleanup();

        } catch (java.sql.SQLException e) {

            this.errorMessageHandlerDB("setupDatabaseConnection: Cannot get connection, Details: " + e.toString());
            cleanup();

        } catch (java.lang.Exception e) {

            this.errorMessageHandlerDB("setupDatabaseConnection: Unknown error");
            cleanup();

        }
    } // end function




public void updateData(String queryString) throws SQLException {

        // sets up connection
        //this.setupDatabaseConnection();

        Statement statement = null;

        this.logMessageHandlerDB("CONN=" + getConnectionDB());

        // if DB valid then update table in database with Tweets and User details
        if (this.isIsDBValid() == true) {

            try {
                statement = getConnectionDB().createStatement();
                numberOfRowsQueryResult = statement.executeUpdate(queryString);
                this.logMessageHandlerDB("updateData: Total rows Inserted " + numberOfRowsQueryResult);


            } catch (java.lang.NullPointerException e) {

                this.errorMessageHandlerDB("updateData: Cannot get connection, Details: " + e.toString());

            } catch (java.sql.SQLException e) {

                this.errorMessageHandlerDB("updateData: Cannot create SQL statement, Details: " + e.toString());

            } catch (java.lang.Exception e) {

                this.errorMessageHandlerDB("updateData: Unknown error, Details: " + e.toString());

            } finally {

//cleanup
                this.closeStatementDB(statement);
            }
        }
    } // end function




I created a method just to ensure that I only close the connection after the for loop is complete but for some reasong the first time the for loops executes the first SQL statement is inserted. Then my logs shows the other SQL statement being printed but no inserts in the database so the for loop is ok but not the db connection or insert method.

So the logs have
Code:
setupDatabaseConnection: Successful
updateData: Total rows Inserted 1


I displayed the connection value which was stored during the setup connection and it gives:
Code:
jdbc:mysql://127.0.0.1/myDB?autoReconnect=true, UserName=root@localhost, MySQL-AB JDBC Driver


Then the log from the close connection method gives the error below which means after it gets the connection and inserts the first sql statements from the first method call but not the second or remaining then it will not close:
Code:
Cannot close connection, Details: java.sql.SQLException: No suitable driver


Any more suggestions? Thanks
Back to top
mdeggers



Joined: 28 Jan 2009
Posts: 208
PostPosted: Wed Jul 27, 2011 5:34 pm    Post subject: Reply with quote
I had a nice reply all typed up, and then I timed out.

Anyway, you're leaking connections like crazy in your code. What's
happening is that you're running out of database connections in the pool.

Things to ponder in the following code:

Code:
for (Status status : statuses) {
    try {
    getDbConnector().setupDatabaseConnection();
//first SQL
        getDbConnector().updateData(queryStatus);
//second SQL
        getDbConnector().updateData(queryUser);
        } catch (SQLException sqlException) {
            getDbConnector().errorMessageHandlerDB("storeRawTweets: DB error " + sqlException.toString()); }
} // end for
// close db resources
getDbConnector().closeConnectionDB();


How many objects do you create with getDbConnector() ? BTW, that
name is really ugly. It looks like a method name, and not a class name.

A good explanation on how to manage this can be found here:

http://blog.christopherschultz.net/index.php/2009/03/16/properly-handling-pooled-jdbc-connections/

Basically, your code is just borrowing connections that Tomcat has
created between it and the database. When you've borrowed a
connection, Tomcat marks that as used. When you close and null out the
parts of the connection, Tomcat marks that as available.

If you never properly close and null out all the parts, Tomcat thinks the
connection is still in use (and you won't be able to garbage collect all the
objects). Two things happen: you run out of connections, and you leak
memory.

To fix this, you can either duplicate the connection borrowing and closing
code for each class you issue SQL statements or you can write a base
class with all of the boilerplate in it and inherit the code.

I like the base class mechanism, because I reduce copy-paste errors. Also
if I make a mistake in my base class, I can fix it in one place and all
of my other classes inherit the fix.

So, here's what I suggest:

1. Write a base class (call it BaseDAO)

In that class have some methods:

protected void getConnection() // gets connection from Tomcat JNDI pool
protected void cleanup() // calls a bunch of other methods to clean up
protected void cleanRS() // cleans up result sets
protected void cleanStatement() // cleans up statements
protected void cleanConnection() // cleans up connections

You'll probably want separate methods since you seem to use the
same connection to issue multiple SQL statements in your loop. That way
you can clean up result sets and statements without closing the actual
connection.

2. Write a class that does the actual query or insert

Inherit from BaseDAO so you get the advantage of all of your work

Now you should only have to write one or two methods, focusing on your
actual SQL statements. You get to use (for free) all of the methods in your
base class.

public class SomeSQL extends BaseDAO

That should keep you (and Tomcat) happy.

. . . . just my two cents.
/mde/
Back to top
hikarimay10



Joined: 18 Jul 2011
Posts: 6
PostPosted: Thu Jul 28, 2011 8:34 pm    Post subject: [SOLVED] Reply with quote
Hi again:

I used the BaseDAO class then extended it into another class which calls the methods to update or retrieve data and it worked Smile. I customized the examples that you showed on your post Mon Jul 11, 2011 7:53 pm. My latest details are:

Using:
java version "1.6.0_26"
Apache Tomcat 6.0.26

JDBC Driver:
MySQL Connector/J JDBC Driver (mysql-connector-java-5.1.17-bin.jar) is in $CATALINA_HOME/lib directory

I am also posting the content of my other files as follows:

content.xml
Code:
<?xml version="1.0" encoding="UTF-8"?>
<Context antiJARLocking="true" crossContext="true" path="/TweetCollector" reloadable="true">
  <Resource auth="Container" driverClassName="com.mysql.jdbc.Driver" maxActive="8" maxIdle="4" name="jdbc/myDB" password="administrator" type="javax.sql.DataSource" url="jdbc:mysql://127.0.0.1/myDB?autoReconnect=true" username="root" validationQuery="SELECT 1"/>
</Context>


web.xml
Code:
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
    <welcome-file-list>
        <welcome-file>index.jsp</welcome-file>
    </welcome-file-list>
    <resource-ref>
        <description>
    Resource reference to a factory for java.sql.Connection
    instances that may be used for talking to a particular
    database that is configured in the Context
    configurartion for the web application.
        </description>
        <res-ref-name>
    jdbc/myDB
        </res-ref-name>
        <res-type>
    javax.sql.DataSource
        </res-type>
        <res-auth>
    Container
        </res-auth>
    </resource-ref>
</web-app>


Thank you so much, mdeggers.
Very Happy

I also noticed that you used a logging class. The common logging has a download but I am unsure where that log file is written to and how to force it to use the Log4J. I am wondering since I am creating a web application how would I use logging to store errors etc to a log file for use by an administrator? BTW should this be another post, I'm not sure?


Although now I need to figure out how to get my web app to run outside of NetBeans but I will make another post for that.

Cheers,
hikarimay10
Back to top
mdeggers



Joined: 28 Jan 2009
Posts: 208
PostPosted: Thu Jul 28, 2011 9:19 pm    Post subject: Reply with quote
Hey! Glad you got your web application working.

And yeah, using log4j / commons-logging is probably another post. It's not
really that difficult, but there are several ways you can approach the
problem. Ask here or in email.

Anyway, getting your web application to work outside of NetBeans is really
easy.

1. Build your project - right-mouse click on the project and select build

This will create a "dist" folder in your project and create a
<project-name>.war file.

2. Browse to where you've installed Tomcat and find the bin folder

3. Double-click on startup.bat (if I remember correctly you're on Windows)

Now Tomcat is happily running. You'll then use the manager application
to deploy your web application.

4. Use your browser to browse to http://localhost:8080/

This is the default Tomcat page if you've not changed server.xml

5. Select the Tomcat Manager link

6. Fill in the user name and password

This will be the same user name and password you gave to Tomcat when
you set it up under NetBeans. If you don't remember what it is, go look
at the tomcat-users.xml file found in %CATALINA_HOME%\conf.

7. Now scroll down to WAR file to deploy

8. Click on Choose File

9. Browse to where the WAR file you built in step 1 above

10. Click on the Deploy button

Tomcat will upload the WAR file you chose, and start the application.
Well, Tomcat will automatically start the application if you have AutoDeploy
set to true in %CATALINA_HOME\conf\server.xml. This is the default,
so not to worry.

If all is good, then you should see your application listed in the
Applications listing. The "running" column should have "true", and
there should be links to stop, reload, and undeploy your application in
the "commands" column.

11. Right-mouse click on your application in the "Path" column and
open it in a new tab (or window) to run it.

When you're done, you can delete the application and stop Tomcat.

You can undeploy your application by clicking on the "undeploy" link
for your application in the Tomcat Manager application.

To stop Tomcat, you can browse (using Windows Explorer) to
%CATALINA_HOME%\bin, and click on shutdown.bat.

You could also start up NetBeans, and do the operations from within
NetBeans.

. . . . just my two cents.
/mde/
Back to top
Display posts from previous:   
Post new topic   Reply to topic    NetBeans Forums -> Java EE Users All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You can attach files in this forum
You can download files in this forum


Powered by phpBB
By use of this website, you agree to the NetBeans Policies and Terms of Use. © 2012, Oracle Corporation and/or its affiliates. Sponsored by Oracle logo