Connecting to a database with Tomcat

Browsing the internet for instructions on how to connect to a database from my Java web app using Tomcat was a minefield.

I almost gave up on Tomcat as so many sites would give overly complex methods involving changing the configuration files of the Tomcat server itself. How could that be? I don’t have access to the configuration files of hosted services like Amazon, so there must be another way… and there is.

I discovered there are easy steps to allow your web app to connect to a database.

I am using Netbeans, so the following information is for Netbeans, however, you may be able to adapt it to whatever IDE you use. I will be discussing only how to create the connection in this post, but not how to use them, that will be in other posts.

Before you can use the database connection in your app you must first register the database connection with the IDE. Under the services tab, right click on the database element and select “New connection”. Follow the instruction to connect to your database. On the last step, where you are asked to set the JNDI name, do not go with the default. Instead try to follow the preferred naming convention of jdbc/myDatabase as this will help when connecting to it later.

You will need to use the correct driver for your database. Java has a lovely translation system that allows you to write SQL queries using a standard syntax called JPA. The drive will then convert this SQL into the vendor specific syntax to work with the database you are connecting to.

You can either add the .jar file from the vendors download page to your projects library, or use maven by adding the correct tags to your projects pom.xml file if you are using a maven project(a good idea).

Here is the MySql one;

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.3</version>
</dependency>

And this one works with Microsoft Sql;

<dependency>
<groupId>com.hynnet</groupId>
<artifactId>sqljdbc4-chs</artifactId>
<version>4.0.2206.100</version>
</dependency>

Now you have the correct driver and the IDE can connect to your database you can configure your web app to use the database.

Right click on your project, select New and then “Entity classes from database”. When you select the JNDI name you created earlier Netbeans will retrieve all of the Tables and Views (that have primary keys) from your database. Select the ones you want and next, next, finish. This will create the Entity classes for you.

As I said earlier, I wont go into detail in this post on how to use the Entity classes. Search my other posts for help with that.

So, you have your Entity’s. You have connected your IDE to your database. Can I just publish my app to Tomcat? Unfortunately, No.

Unlike other Java application servers, such as Glassfish, Payara or Weblogic, the war file does not create JDBC connections in the server.

However, it is rather easy to do in Tomcat once you know how.

To tell Tomcat how you want your web app to use the database you need to edit the WEB-INF/web.xml file and add the following tags.

<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/myDatabase</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

Now you can use your database from your beans. I use the following code to do this.

public EntityManager getEntityManager() {
EntityManagerFactory emf = Persistence.createEntityManagerFactory(“jdbc/myDatabase”);
return emf.createEntityManager();
}

public List<EntityTableName> getEntityTableName() {
EntityManager em = getEntityManager();
List<EntityTableName> results = (EntityTableName) em.createNamedQuery(“EntityTableName.findAll”).getResults();
em.close();
return results;
}

That’s it! I can now use the getEntityTableName() method to pull the results from the database and use them as needed.

I hope this helped someone and I look forward to your comments.

3 thoughts on “Connecting to a database with Tomcat

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s