HOWTO: Connecting to MySQL from Tomcat
This is how I finally got Connector/J to work with Tomcat (and assuming you already installed Tomcat and MySQL):
MySQL Connector/J (formerly MM.MySQL) is a native Java driver that converts JDBC (Java Database Connectivity) calls into the network protocol used by the MySQL database. Simply put, it allows JSP pages in Tomcat to connect to the MySQL Database Server.
Installing Connector/J:
Note: This HOWTO is for version 3.0, but the same basically applies to 2.0.
Download Connector/J 3.0 and extract the zip. From there, get the only file you really need (the rest is mostly just source files): mysql-connector-java-3.0.6-stable-bin.jar.
Copy mysql-connector-java-3.0.6-stable-bin.jar to {Tomcat Install Dir}/common/lib/ (this is where you place all the JAR files that you want accessible to all webapps).
Restart Tomcat (Control Panel > Administrative Tools > Services, right-click on Apache Tomcat and select Restart) so that it picks up the JAR file.
Connector/J is now installed and available to all JSP files.
Connecting to MySQL with JSP:
Here's a sample page on how to connect to MySQL, it assumes that you already have the appropriate database, table and fields setup in MySQL and added some sample data to the user table. Save this file to {Tomcat Install Dir}/webapps/ROOT/jdbctest.jsp and run it in your browser by navigating to http://localhost:8080/jdbctest.jsp.
<!doctype html public "-//w3c//dtd html 4.0 transitional//en" "http://www.w3.org/TR/REC-html40/strict.dtd"> <!-- These import statements are needed to run the SQL queries, they are part of the JDK. --> <%@ page import="java.sql.DriverManager" %> <%@ page import="java.sql.Statement" %> <%@ page import="java.sql.ResultSet" %> <html> <head> <title>JDBC test with Connector/J</title> </head> <body> <%! /* My source for most of this code: http://www.webmasterbase.com/article/770/565 */ // Define variables String uId; String fName; String lName; %> <% // This is needed to use Connector/J. It basically creates a new instance // of the Connector/J jdbc driver. Class.forName("com.mysql.jdbc.Driver").newInstance(); // Open new connection. java.sql.Connection conn; /* To connect to the database, you need to use a JDBC url with the following format ([xxx] denotes optional url components): jdbc:mysql://[hostname][:port]/[dbname][?param1=value1][¶m2=value2]... By default MySQL's hostname is "localhost." The database used here is called "mydb" and MySQL's default user is "root". If we had a database password we would add "&password=xxx" to the end of the url. */ conn = DriverManager.getConnection("jdbc:mysql://localhost/mydb?user=root"); Statement sqlStatement = conn.createStatement(); // Generate the SQL query. String query = "SELECT UserId, FirstName, LastName FROM user"; // Get the query results and display them. ResultSet sqlResult = sqlStatement.executeQuery(query); while(sqlResult.next()) { uId = sqlResult.getString("UserId"); fName = sqlResult.getString("FirstName"); lName = sqlResult.getString("LastName"); out.println("<b>" + uId + "</b>, " + fName + ", " + lName + "<br />"); } // Close the connection. sqlResult.close(); sqlStatement.close(); conn.close(); %> </body> </html>
Related links: