JSP Tutorial: Display Data (and Lab)
Contents
1. Lab Overview 2
2. Add MySql Data Driver to your Netbeans Project 3
3. Create a JSP that Displays Data (using just JSP code) 3
4. Study the Classes used from java.sql: Connection, PreparedStatement, & ResultSet 5
5. Execute the JSP Page 6
6. Learn How to Recognize Errors 7
7. Create Java Packages (“SQL” and “view”) 8
8. Add Java Class (“DbConn”) to Package (“SQL”) 8
9. Add Java Class (FormatUtils) to Package (SQL) 11
10. Add Java Class (“WebUserView”) to Package (“view”) 13
11. Create a JSP that Uses Classes to Displays Data 15
12. Learn How Database Connections are Handled and Why 16
13. Execute the JSP that Displays Data (using Classes) 16
14. Debugging When Using Classes 16
15. Your Homework for This Week 18
16. Homework Submission 19
17. Grading 19
18. Studying for the Weekly Quiz 19
1. Lab Overview
In this lab, you will learn
· How to add a MySql database driver to your Netbeans web application.
· How to add java packages and classes to your Netbeans web app.
· How to make a JSP page reference java code.
· How to use these classes from the java.sql package: Connection, PreparedStatement, ResultSet.
· How database connections should be handled in web applications (and how that is different from windows applications).
· How to recognize common errors both from simple JSP pages (that do not use classes) as well as from normal JSP pages (that do use classes): Missing Database Driver, Database unavailable, Syntax error in Sql Statement, Error Extracting Data from Result Set (bad column name), Error Extracting Data from Result Set (wrong data type).
· How to publish your web application (this is covered in a separate document).
Before you start, you should have completed in previous labs:
· Installed the Netbeans development environment on your personal computer.
o If you are unable to install Netbeans at home (that can create and execute a “Java -- Web Application”), then you will have to work at the computer labs, but I highly recommend that you get a development environment set up at home or, at some point, you will probably have trouble keeping up with the lab homework.
· Created a home page (index.html) that describes your proposed web app.
· Installed MySqlWorkbench at home, created a database that supports your proposed web app, and entered test data.
o If you are unable to install MySqlWorkbench at home, then you will have to connect to your mySql database that was created for you on cis-linux2.temple.edu. The cis lab computers cannot create local databases. And you will have to do all of your NetBeans development from the computer labs (unless you can figure out a way to tunnel into cis-linux2 while working in NetBeans at home).
2. Add MySql Data Driver to your Netbeans Project
In order to read from your MySql database, you need to add a MySql database driver to your NetBeans project.
· Download the jar file named “mysql-connector-java-xxx-bin.jar”. You can either get the one I have posted in blackboard or you can google and download your own.
o I recommend that you place this jar file under the “NetBeans Project” folder (under mydocs on your home PC), but you can put it anywhere (as long as it stays there because if you move/delete it from that location, your NetBeans project will stop working).
· Right click on the “Libraries” folder (in the NetBeans Project pane) and select “Add JAR/Folder”. Browse to wherever you stored your jar file. When you have done so, your project pane should look like this:
3. Create a JSP that Displays Data (using just JSP code)
· Add a new JSP file to your project. You can name it something like “display_no_classes” and copy/paste the following code into the JSP page.
<%@page contentType="text/html" pageEncoding="UTF-8"%><%@ page language="java" import="java.sql.DriverManager"%>
<%@ page language="java" import="java.sql.Connection"%>
<%@ page language="java" import="java.sql.PreparedStatement"%>
<%@ page language="java" import="java.sql.ResultSet"%>
<!DOCTYPE html>
<html>
<head>
<style>
body {background-color: khaki;}
table, th, td {background-color: beige;
border:thin solid maroon;
padding: 3px;
text-align:left;}
</style>
<title>List All Users</title>
</head>
<body>
<%
Connection con = null; // "telephone call" between web app and the db mgt system
PreparedStatement stmt = null; // holds the SQL statement the web app wants to run
ResultSet results = null; // holds the data that results from executing the SQL SELECT statement.
out.println("<br/>ready to get the driver... <br/>");
try { // to find the driver
String DRIVER = "com.mysql.jdbc.Driver";
Class.forName(DRIVER).newInstance();
out.println("got the driver... <br/>");
try { // to get the connection
String url = "jdbc:mysql://localhost/cis2308?user=root"; //cis2308 is the name of my local database/schema
//String url = "jdbc:mysql://CIS-Linux2.temple.edu:3306/SP11_2308_sallyk?user=sallyk&password=loh3Nito";
con = DriverManager.getConnection(url);
out.println("got the connection with the db..." + "<br/>");
try {
String sql="select user_email, user_password from web_user order by user_email";
stmt = con.prepareStatement(sql);
results = stmt.executeQuery();
out.println("executed the query <br/<br/>");
out.println("<table>");
out.println("<tr<th>User Email</th<th>User Password</th</tr>");
while (results.next()) {
out.print("<tr>");
out.print("<td>" + results.getInt("user_email")+ "</td>");
out.print("<td>" + results.getString("user_password") + "</td>");
out.println("</tr>");
}
out.println("</table>");
out.println("All Done !!");
results.close(); // close the result set
stmt.close(); // close the statement
con.close(); // close the db connection (THIS ONE IMPORTANT-no leaks!)
} catch (Exception e) {
out.println("problem creating statement & running query:" + e.getMessage() + "<br/>");
results.close(); // close the result set
stmt.close(); // close the statement
con.close(); // close the db connection (THIS ONE IMPORTANT-no leaks!)
}
} catch (Exception e) {
out.println("problem getting connection:" + e.getMessage() + "<br/>");
}
} catch (Exception e) {
out.println("problem getting driver:" + e.getMessage() + "<br/>");
}
%>
</body>
</html>
4. Study the Classes used from java.sql: Connection, PreparedStatement, & ResultSet
The sample code imports classes from the java.sql.package (see line two of the sample code). The three classes used are Connection, PreparedStatement, and ResultSet:
· The DriverManager is the basic service for managing a set of JDBC drivers.
http://www.webopedia.com/TERM/J/JDBC.html
o JDBC stands for JavaDatabaseConnectivity,aJavaAPIthat enables Javaprograms to executeSQLstatements on any SQL-compliantdatabase. Since nearly allrelational database management systems (DBMSs)support SQL, and because Java itself runs on most platforms, JDBC makes it possible to write a singledatabaseapplication that can run on different platforms and interact with differentDBMSs.
o JDBC is similar toODBC (open database connectivity), but is designed specifically for Java programs, whereas ODBC is language-independent.
· The Connection is like a telephone call between your application and the MySql database management system.
o All connections that are opened must be closed (in EVERY code path) – or else your code has what is known as a “database connection leak”. It is very easy to create a database connection leak and web apps that have database connection leaks stop working properly with heavy usage.
· The PreparedStatement is the SQL to be executed (e.g., “SELECT * FROM WEB_USER”)
· The ResultSet is the data returned by the SQL SELECT statement. Using the “next()” method, you can only access one row of data at a time, sequentially (first row, next row, etc).
Whenever you are working with a database, there is always the possibility of an exception being thrown. For example, you attempt to connect to database that is down or unreachable, or you attempt to execute a SQL statement that has a syntax error. That’s why you’ll see a lot of try/catch code and exception handling in web applications.
5. Execute the JSP Page
· Run your JSP file (right click the JSP file from the Netbeans project pane and select “Run File”).
· If all went well, you should see your JSP page in the browser showing the data from your local database.
· Right click (in the browser) and select “View – Source” to see the HTML code that you JSP page created (something like what is shown below). Notice that NO JSP code remains. The browser only sees HTML !
· Can you improve upon the JSP code and make the “View Source” have better indentation within it’s <table> tag? Hint: use spacing in your out.print() or out.println() methods. (This is not required for your lab HW.)
<!DOCTYPE html><html>
<head>
<style>
body {background-color: khaki;}
table, th, td {background-color: beige;
border:thin solid maroon;
padding: 3px;
text-align:left;}
</style>
<title>List All Users</title>
</head>
<body>
<br/>ready to get the driver... <br/>
got the driver... <br/>
got the connection with the db...<br/>
executed the query <br/<br/>
<table>
<tr<th>User Email</th<th>User Password</th</tr>
<tr<td>2</td<td>2</td</tr>
<tr<td></td<td>pebblesx</td</tr>
<tr<td></td<td>superx2</td</tr>
<tr<td></td<td>hjhjh</td</tr>
<tr<td></td<td>bee8</td</tr>
<tr<td></td<td>yo8</td</tr>
<tr<td></td<td>amste7</td</tr>
<tr<td></td<td>yesyes5</td</tr>
<tr<td></td<td>sosilly</td</tr>
<tr<td>user99</td<td>pw99</td</tr>
</table>
All Done !!
</body>
</html>
· If you do not see a well formed HTML page in your browser, here are the things that might have gone wrong:
o You didn’t add the driver properly to your NetBeans project.
o You didn’t name your mySql schema “cis2308” in a previous lab and/or you changed your database username from the default “root” and/or you added a password.
o Your MySql database does not have a table named “web_user” with columns “user_email” and “user_password”.
· Take a moment to try to understand the code. Make some changes to the code so that you get an idea of what works and what doesn’t. Try changing variable names (in the JSP code), changing input names (in the HTML tags/attributes). With all your changes, keep testing it to make sure it still works.
6. Learn How to Recognize Errors
Throughout the semester, especially as each lab builds upon the previous labs, you will certainly encounter errors. The sample code places much importance upon trapping, handling, and displaying all possible errors. Take a moment to learn how to recognize some common errors.
· Missing Database Driver. To simulate this error, remove the mysql driver. (Right click on the Jar file and select “remove”).
o Copy/paste the error message into the word document you are using for this lab HW. In the document, label the error “Missing Database Driver-JSP page”. Then, add the driver back in.
· Database unavailable. To simulate this error, change something in your connect string (perhaps change the schema name from “cis2308” or username from “root”):
String url = "jdbc:mysql://localhost/cis2308?user=root";
o Copy/paste the error message into the word document you are using for this lab HW. In the document, label the error “Database Unavailable-JSP page”. Then, fix the connection string back to what it was originally.
· Syntax error in Sql Statement. To simulate this error, change something in your sql statement (perhaps misspell a keyword or table name or column name):
String sql="select user_email, user_password from web_user order by user_email";
o Copy/paste the error message into the word document you are using for this lab HW. In the document, label the error “ Syntax error in Sql statement-JSP page”. Then, correct the sql statement.
· Error Extracting Data from Result Set (bad column name). To simulate this error, try to extract a column name which does not exist in your result set (did not exist in your SQL select statement):
while (results.next()) {
out.print("<tr>");
out.print("<td>" + results.getString("user_email")+ "</td>");
out.print("<td>" + results.getString("user_password") + "</td>");
out.println("</tr>");
}
o Copy/paste the error message into the word document you are using for this lab HW. In the document, label the error “Bad Column Name Extracting from Result Set-JSP page”. Then, correct the column name.
· Error Extracting Data from Result Set (wrong data type). To simulate this error, change the method from getString to getInt as you attempt to extract the “user_email” column from your result set:
o Copy/paste the error message into the word document you are using for this lab HW. In the document, label the error “Wrong Data Type Extracting from Result Set-JSP page”. Then, correct the method name.
7. Create Java Packages (“SQL” and “view”)
Our next JSP page will reference java classes that are internal to the project. The java classes reside in packages. Create a package named “SQL” as follows:
· Right click on Source Packages (under your project folder in the NetBeans project pane”) and select “new package”. Name your package “SQL”.
· Since the sample code also references code in a package named “view”, create that package as well.
8. Add Java Class (“DbConn”) to Package (“SQL”)
Our first java class will be “DbConn”, a database connection “wrapper class” (a “wrapper class” is another name for the “adapter” design pattern). If the DbConn class is used EVERY time the web app needs access the database, then if our database changed (e.g., from dev to prodn), we only have to change the connection string in one class. This is very good for maintainability of the web app.
The functionality of the DbConn class is similar to what you saw in the simple display data JSP above. The important methods are to open the connection (handled in the constructor) and close the connection, but the class also needs to be able to report what error was experienced (if any) so that this information can be provided to the JSP page that requested the connection. Also, to ease the common transition between development environment and production environment, this class provides functionality to check the hostname, determine if it is running on a local machine or at temple and adjust the connection string accordingly.
Add the DbConn class to your SQL package as follows:
· Right click on the “SQL” package and select “New – Java Class”. Name the class “DbConn”.
· Paste this code into the editor and save the file.
package SQL;import java.sql.DriverManager;
import java.sql.Connection;
/** Wrapper class for database connection.
* Constructor opens connection. Close method closes connection. */
public class DbConn {
private String errMsg = ""; // will remain "" unless error getting connection
private String connectionMsg = "Connection Error-Uninitialized."; // log of getting connection
private Connection conn = null;
/** Constructor - opens database connection to database,
This version determines if the app is running locally or not */
public DbConn() {
this.connectionMsg = "";
try {
this.connectionMsg += "ready to get driver... <br/>";
String DRIVER = "com.mysql.jdbc.Driver";