This week's book giveaway is in the OCAJP 8 forum. We're giving away four copies of OCA Java SE 8 Programmer I Study Guide and have Edward Finegan & Robert Liguori on-line! See this thread for details.
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:
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:
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.
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