GeeCON Prague 2014*
The moose likes Tomcat and the fly likes how integrate MySQL and Tomcat Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Products » Tomcat
Bookmark "how integrate MySQL and Tomcat" Watch "how integrate MySQL and Tomcat" New topic
Author

how integrate MySQL and Tomcat

mike hold
Greenhorn

Joined: Feb 25, 2005
Posts: 7
what i need for integrate tomcat 5.5 with MySQL ???

only connector ???
for example i have
mysql-connector-java-3.2.0-alpha

and now i need write it in server.xml of tomcat ???
Alan Shiers
Ranch Hand

Joined: Sep 24, 2003
Posts: 237
Hi there,

The way you connect to MySQL via Tomcat is dependant on the version of Tomcat you're using. Recently I did a small project using Tomcat 5.5. Finally, they are making it easier to encapsulate the appropriate instructions into your application itself. It used to be that one had to place "Context" information in with the server.xml file. It is the "Context" information that instructed the servlet container about connection pooling and what drivers to use for a given application. That meant extra work for your internet service provider to set up your web application properly for you. This is no longer the case. As of Tomcat version 5 you can now add a new directory to your web application naming it: META-INF. Place inside it a file named "context.xml".

So, your application directory structure should look something like this:

school
|__ index.htm
|__ login.jsp
|__ META-INF
| |__ context.xml
|__ WEB-INF
| |__ web.xml
|__ classes
|__ StudentModel.class

Here is an example context.xml file that I used to connect to a MySQL Database named "School":

<!-- New Context for MySQL Database Driver -->
<Context path="/school" docBase="school" debug="0"
reloadable="true" crossContext="true">

<Logger className="org.apache.catalina.logger.FileLogger"
prefix="localhost_School_log." suffix=".txt"
timestamp="true"/>

<Resource name="jdbc/School" auth="Container" type="javax.sql.DataSource"
username="sqlapp" password="aplus" driverClassName="org.gjt.mm.mysql.Driver"
factory="org.apache.commons.dbcp.BasicDataSourceFactory"
url="jdbc:mysql://192.168.2.75:3306/school_db?autoReconnect=true"
maxActive="3" maxIdle="1" maxWait="10000"/>

</Context>

I'm not going to explain every attribute as you can obtain that information from Tomcat documentation.

I was making use of the commons connection pooling libraries named:
commons-collections-3.1.jar
commons-collections-testframework-3.1.jar
commons-dbcp-1.2.1.jar
commons-el.jar
commons-pool-1.2.jar
and
mysql-connector-java-3.0.16-ga-bin.jar

All these files need to be in your Tomcat 5.5\common\bin directory.

Finally, you need a servlet that is going to invoke a connection to your database. The best way I can show you that is to provide a sample of what I did:


You'll notice I make use of a ResourceBundle object. I have a separate file containing name=value pairs that look like this:

findAllQuery=SELECT * FROM STUDENTS ORDER BY LASTNAME;
findQuery=SELECT * FROM STUDENTS WHERE STUDENT_ID = ?;
updateQuery=UPDATE STUDENTS SET FIRSTNAME=?, LASTNAME=?,EMAIL_ADDRESS=? WHERE STUDENT_ID=?;
createQuery=INSERT INTO STUDENTS (FIRSTNAME, LASTNAME, EMAIL_ADDRESS) VALUES (?,?,?);
deleteQuery=DELETE FROM STUDENTS WHERE STUDENT_ID=?;

This is just a properties file that contains SQL queries.

Basically, all you do is use the Context and DataSource objects to obtain a connection from the connection pool by calling the getConnection() method found in the DataSource object. You then obtain a PreparedStatement object from the Connection object and pass it the SQL query obtained from the properties file:

statement = connection.prepareStatement
sql_bundle.getString "findAllQuery"));

Then call the executeQuery() method on the PreparedStatement object. From that you'll get your ResultSet object which you can iterate over to obtain the data you're looking for. Make sure you always close your RecordSet, PreparedStatement, and Connection objects when you're through.

You'll note that most of the code is encased in a try/catch block. This is extremely important.

In a Nutshell, this is one way to do it. There are other ways using other third party connection libraries, namely Turbine.

I realize this is short and sweet and doesn't go into any great detail, but its a place to start and you can learn the rest from Tomcat documentation.

Hope this helps,

Alan Shiers
Surasak Leenapongpanit
Ranch Hand

Joined: May 10, 2002
Posts: 341
Look at this document.
JNDI Datasource HOW-TO.
Matthew Amacker
Greenhorn

Joined: Aug 10, 2004
Posts: 5
One other piece of interest that has served me greatly:

If you deploy your application remotely using Ant, remotely - meaning from a directory other than the Tomcat subdirs - you can actually specify the context file you'd like the servlet to use in the install task.

Then to make sure that your context file has actually been used you can look in $CATALINA_HOME/conf/localhost/<your config file>.xml
 
GeeCON Prague 2014
 
subject: how integrate MySQL and Tomcat