MySQLDBCPExample_MySQL

0. Introduction

Versions of MySQL and JDBC drivers that have been reported to work:

MySQL 3.23.47, MySQL 3.23.47 using InnoDB,, MySQL 3.23.58, MySQL 4.0.1alphaConnector/J 3.0.11-stable (the official JDBC Driver)mm.mysql 2.0.14 (an old 3rd party JDBC Driver)

Before you proceed, don't forget to copy the JDBC Driver's jar into $CATALINA_HOME/lib.

1. MySQL configuration

Ensure that you follow these instructions as variations can cause problems.

Create a new test user, a new database and a single test table. Your MySQL user must have a password assigned. The driver will fail if you try to connect with an empty password.

	mysql> GRANT ALL PRIVILEGES ON *.* TO javauser@localhost -> IDENTIFIED BY 'javadude' WITH GRANT OPTION; mysql> create database javatest; mysql> use javatest; mysql> create table testdata ( -> id int not null auto_increment primary key, -> foo varchar(25), -> bar int);

Note: the above user should be removed once testing is complete!

Next insert some test data into the testdata table.

	mysql> insert into testdata values(null, 'hello', 12345); Query OK, 1 row affected (0.00 sec) mysql> select * from testdata; &＃43;----&＃43;-------&＃43;-------&＃43; | ID | FOO | BAR | &＃43;----&＃43;-------&＃43;-------&＃43; | 1 | hello | 12345 | &＃43;----&＃43;-------&＃43;-------&＃43; 1 row in set (0.00 sec) mysql>

2. Context configuration

Configure the JNDI DataSource in Tomcat by adding a declaration for your resource to your Context.

For example:

3. web.xml configuration

Now create a WEB-INF/web.xml for this test application.

	MySQL Test App DB Connection jdbc/TestDB javax.sql.DataSource Container

4. Test code

Now create a simple test.jsp page for use later.

	<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> select id, foo, bar from testdata Results Foo ${row.foo} Bar ${row.bar}

That JSP page makes use of JSTL's SQL and Core taglibs. You can get it from Apache Tomcat Taglibs - Standard Tag Library project — just make sure you get a 1.1.x release. Once you have JSTL, copy jstl.jar andstandard.jar to your web app's WEB-INF/lib directory.

Finally deploy your web app into $CATALINA_BASE/webapps either as a warfile called DBTest.war or into a sub-directory called DBTest

Once deployed, point a browser at http://localhost:8080/DBTest/test.jsp to view the fruits of your hard work.