Using Java to Write to a Database – Part 2

Continuing from Part 1

In part 1 we created the simplest of databases, nothing more than a table with 2 columns.

There are two main ways to write an application in Java, in my opinion of course… the first is for native applications, those applications that run directly on your desktop or mobile device. The second is an application that runs on top of an application server, such as Tomcat. Tomcat would then, normally, provide a way to interface with the application through web pages or APIs.

Of course it is possible to run an application on an application server with no interfaces. This could be for tasks such as generating reports as a scheduled service and sending them via email. Therefore, no interaction and no interface.

So, in this part we will first focus on the most common use, Web Applications.

In this part we will use a JDBC connection so the data can be accessed in our applications.

For Connecting to a database with Tomcat <- use this link as it is the foundation for the rest of this post.

Now, hopefully, you have a database and a simple web application to pull data from that database. The next step is the purpose of this post, writing data back to the database.

In the same way you retrieved data, to write data back just takes a couple more steps.

public void getEntityTableName() {
EntityManagerFactory emf = Persistence.createEntityManagerFactory(“jdbc/myDatabase”);
EntityManager em = emf.createEntityManager();
try {
PetTable petTable = new PetTable();
petTable.setName(“Rex”);
petTable.setKind(“Dog”);
EntityTransaction transaction = em.getTransaction();
transaction.begin();
em.merge(petTable);
transaction.commit();
} catch (Exception e) {
System.out.println(“e = ” + e);
} finally {
em.close();
emf.close();
}
}

The difference is the need to start and end a transaction. When writing data to a transactional database you need to obtain exclusive rights to the table where you are writing data to, just so no one else can write or delete data that you are using. What this also allows is the rolling back of changes if something goes wrong.

EntityTransaction transaction = em.getTransaction();
transaction.begin();

Make some changes to the database

transaction.commit();

That’s it, lastly you need to close the connections to the database, otherwise you could spawn multiple connections and cause a denial of service.

em.close();
emf.close();

Using Java to Write to a Database – Part 1

Writing to a database is one of the most important actions of any programme. Being able to store data independently of the application is useful for many reasons;

  • The data can be accessed by other applications, such as mobile
  • The application can be updated without affecting the data
  • Database server performance can be upgraded without affecting the application

And many more…

If you have read my other blogs you will know that I use Tomcat for my application servers, however an application server is not required for an application to run. Mobile application, for example, run (mostly) natively on the device. I also use NetBeans as my IDE.

What is needed? The main Library you will need is the vendor specific database driver. These are also known as JDBC (Java Database Connectivity) drivers when coding in Java.

In this example I will use MySQL which is a free and open source database. You can download it from https://www.mysql.com/downloads/  I will not go into the installation and setup of the database server in this blog. The packages are very easy to use, with a next, next, next type installer.

Once you have your server installed, from a command line, log into the database server.

mysql -u root -p

After you have logged into the database server, you will need to create a database.

CREATE DATABASE petstore;

Now tell the system you want to use the new database.

USE petstore;

A database is a collection of tables, views and other functions. You are going to need somewhere to put the data, a table.

CREATE TABLE pet (name VARCHAR(20), kind VARCHAR(20));

Have a look at your newly created table.

SHOW TABLES;
DESCRIBE pet;

OK, you have a database, so let’s connect to it from your application! In part 2…

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.