Getting JSP to connect to a MySQL database

Introduction

In this tutorial, we will demonstrate how to connect to a MySQL database using JSP, an extremely useful resource in server side technologies We are going to assume in this tutorial that you already have a database created that you wish to connect to. JSP stands for JavaServer Pages and can used to create dynamically created web pages using the Java programming language. Like php, JSP is a server-side language, and because it is a server-side language, like, php, it requires a server to run. While PHP often uses an Apache server, and Node.JS often creates its own server, JSP usually requires a server with a servlet container, such as Tomcat or Jetty. JSP is a powerful tool because you can execute java code right within the same jsp file that is generating the html, and it executes quickly. Connecting to a database in JSP is easy and fast, resulting in a better user experience. In this tutorial, we will learn how to set up your development environment, and use that to connect to the MySQL database for performing queries. By the end of this tutorial, you will be well on your way to becoming a JSP developer!

Preparing your environment

In order for JSP to run, you need a server. For our purposes, we will be using Tomcat on a Linux machine. In order to run Tomcat, you need to install the server, and make sure you have JDK installed. What is JDK you might ask? JDK stands for Java Development Kit. Essentially, this package allows you to code in the Java programming language. Install Tomcat on your Linux machine by executing the following commands in a terminal:

sudo apt-get update sudo apt-get install tomcat6 sudo apt-get install tomcat6-admin sudo apt-get install tomcat6-user

These commands tell your Linux machine to update, to install the tomcat server, and to install the roles of “admin” and “user,” which we will address later in this tutorial. Next, check to make sure you have the most updated openjdk

sudo apt-get update sudo apt-ge install openjdk-6-jdk

In order for Tomcat to be able to run the JDK, Tombcat needs to be told where the JDK is. For this, edit the Tomcat configuration file by using the vim editorsudo vim /etc/default/tomcat6. Each machine’s file organization is different, of course, but for my machine, I uncommented the following line:

#JAVA_HOME=/usr/lib/jvm/openjdk-6-jdk

And changed it to

JAVA_HOME=/usr/lib/jvm/java-6-openjdk-amd64

You are also going to need to edit your Tomcat policy file to allow connections from Tomcat to your MySQL database by adding the following lines to the end of the policy file:

grant { permission java.net.AllPermission; permission java.net.SocketPermission "localhost:3306", "connect"; permission java.net.SocketPermission "(your live server address here):3306", "connect"; };

One final thing, for the server to work, you also have to download a .tar file that will provide the connector for your JSP. You can find the download file HERE. Once downloaded, copy the file into the directory /usr/share/tomcat6/lib/ At this point, you may restart your newly installed Tomcat server (sudo /etc/init.d/tomcat6 restart) and check to see that your server is working by opening a browser and visiting localhost:8080. If all goes well, you should see the following on your browser:Screenshot from 2015-04-08 09_52_01

Creating a JSP page

Congratulations, you have successfully configured an environment for programming in JSP! Now , the times comes to create your first jsp page. It’s as simple as creating a generic html page, but first we must establish the correct permissions on our Tomcat server so that we can write files to it.

sudo chown [username] /var/lib/tomcat6/webapps/ROOT -R sudo chmod 775 /var/lib/tomcat6/webapps/ROOT -R

Restart Tomcat, open up a text editor and we are ready to get started! In the text editor, create a basic html page. After all, JSP is simply a page that combines html and java code that is executed by the server. The html we shall use is as follows:

<!doctype html> <head> <title>Our First JSP!</title> </head> <body> <h1>Hello World! This is our first JSP!</h1> </body> </html>

This should output the following: html hello world Now that we have the basic skeleton for our html page, we can begin the magic by adding in snippets of Java code. This is what differentiates JSP from other client-side technologies like JavaScript, which executes only in the browser. With JSP, all the code can be executed in Java on the server before displaying to the user’s browser. Like Node.JS, Java code is distinguished from the surrounding html by <% %> brackets. Inside these magical brackets, you may implement all the Java your heart desires. For demonstration purposes, let us execute some Java code in our html page.

<% String hello = "Hello World! JSP is my favorite!"; out.println(hello); %>

You’re going to want to put this code somewhere within the html <body></body>tags. At this point we have a functioning JSP, so let’s connect to the database!

MySQL

First, a refresher on MySQL basics. MySQL Relational Database Managements System. It communicates to the database using SQL, which stands for Structured Query Language. Essentially, you submit a command to your database using a SQL statement, and it executes on your database. With SQL, you can read from your database, create entries, update them, and delete them. SQL can be used in virtually any programming language or framework to access a database. MySQL is just one of many RDBMS’s out there. Others include Oracle, Microsoft SQL Server, IBM DB2, SAP, and others.

Connect to the Database

In order to connect a database in JSP, you have to include the right libraries and use the correct drivers. Insert the following code snippet at the top of your JSP page (before any of the html stuff):

<%@ page import = "java.sql.*" %>
 <%@ page import = "java.sqlx.*" %>
 <% Class.forName("com.mysql.jdbc.Driver"); java.sql.Connection conn = DriverManager.getConnection("jdbc:mysql://databaseaddress, username,password); %>

Let me explain the above code. In order to connect to a MySQL Database in any language, you first need to import the sql libraries using the first two lines of code above. You also need a database connection. In order to create a database connection in MySQL, you need what is called jdbc (Java Database Connector). You import this connector into your code by declaring a Java Class. By using Class.forName() you are able to import the entire class within your code. As a parameter for this Class.forName(), you are calling “com.mysql.jdbc.Driver”. All this represents is the address for the java database connector Driver. At this point, you need to make the actual connection to your specific database using your individual information. conn represents the variable in which you will store your database connection information. The variable type isn’t a regular String or int, but rather it is a java.sql.Connection. While at first intimidating, all this type expression is, is a type of variable that was created in the “com.mysql.jdbc.Driver” class we imported into our code. It is saying that within the “com.mysql.jdbc.Driver” Class, we are using a Connection object, which is within “sql”, which is also within “java”. This connection object will let us connect to our database by inputting all the sensitive information. Now we assign something to the object conn. We are assigning a method to it that was imported from the “com.mysql.jdbc.Driver” Class. The method we are using is DriverManager.getConnection(). Inside the parentheses, we shall input our credentials for our MySQL database. The first parameter you put is the address of your MySQL database. If your database is on the server you are working on, then you can simply put a file path there. However, since our JSP page is on our local machine, and our MySQL database is on a live server, he have to put the entire URL. The address for my database is:

"jdbc:mysql://192.168.25.17:3306/it210b"

192.168.25.17 is the IP address of the server where my MySQL database is located. :3306 is the port number. 3306 is the port most often used for the MySQL Database system. Then you input your username in quotes like so: “johnsample”. Then finally, your password: “securepassword”. Make sure each of the parameters are separated by commas, and you should be good to go.

Doing something with the Database

This should be all that is necessary to create a connection to the database. But now what? How do you know it’s even working? Well, one way to test it is to execute a statement and see if it goes through. Let’s try to print out some rows of a table onto our JSP page Within the <body></body>tags, we will insert a scriptlet (use these brackets <% %>). Then, insert the following code onto your page:

// Store query in a String variable
 String query = "SELECT userId, userName FROM users";
 // Create the java statement Statement stmt = conn.createStatement();
 // Execute the query, and get a java resultset
 ResultSet rslt = stmt.executeQuery(query);
 //iterate through the java resultset
 while (rslt.next())
 {
 String userId = rslt.getString("userId");
 String userName = rslt.getString("userName");
 // Print the results
 String html = "<p>" + userId + ", " + userName + "</p>";
 out.println(html);
 }

Now let’s explain what all the gobbledeguck is about. First, you create a SQL statement, which we are storing in a string called query. Next we create a Java statement. Java statements are how to properly prepare query statements in Java. We are storing our statement in a variable called stmt. Next, we can execute the MySQL query using the executeQuery() method. We use the executeQuery() method on the statement object stmt, and store it into a resultset variable called rslt. At this point, the query has been executed and we have all the results stored in an accessible variable. Now the only thing that remains is to actually output the data onto the page.

To print the table data onto the page, we must loop through the rows of the table and store the data into variables. We write a while loop that iterates through the result set. Within the while loop, we store the userId into a String variable called userId, and we store the userName into a String variable called userName, using the getString method on the rslt object. We output the variables within <p></p> tags using the java out.println method.

At this point, you should be able to to view the user id’s and usernames of the rows in your MySQL table on your webpage:

mysql output

Congratulations! You have connected to a MySQL database using JSP!!

Still not working?

If you’ve had trouble connecting to the database, check the following things in your system:

  • Have you restarted your tomcat server?
  • Do ALL of your files have correct permissions?(775, make sure that you are the owner)
  • Do you have syntax errors? Check your capitalization and semicolons
  • Is your SQL statement valid? You can check if your SQL statement will prepare and execute by using the SQL feature of phpmyadmin and testing your statement there.
  • If your page printed out an error message, READ IT. It will probably help you troubleshoot the problem.

Additional Resources

The following are good resources for further learning and trying to connect to the database. I, myself, used many of these links when connecting with JSP for the first time. Enjoy!

  • Connecting JSP To Mysql Database Lesson: This link provides a good step-by-step tutorial on how to connect using excellent graphics to demonstrate the architecture of connecting to a database.
  • JSP – Database Access: This tutorial is helpful if you do not have a database created, because it takes you step-by-step through the process of creating a database a table.
  • MySQL JSP Database Connect Demo: This website provides a full demo of a database connection with MySQL, and though some of the syntax is different, accomplishes the same tasks using the same principles.
  • Stack Overflow question troubleshooting a MySQL database connection: This link illustrates the importance of the MySQL driver in JSP, because this user did not import his library correctly. If you are having problems with your Driver, check out this link.