Dear readers, this time I would like to share about how to create a JSP’s and MySQL web application. This time we will create it using easy way, means that we wouldn’t create this web application by coding java classes that we might needed to support this application. We only create JSP, yes only JSP, and of course few configuration that I hope easy enough too.
Before we start, let’s prepare a database and a table that will be one of our material of this creation. Let’s create a database and name it as “test_jsp” and for the table, the structure of the table is looked like this sketch.
or we can copy and paste sql query below:
create database test_jsp;
use test_jsp;
CREATE TABLE `identitas` (
`NO` int(10) NOT NULL AUTO_INCREMENT,
`NAMA` varchar(100) NOT NULL,
`ALAMAT` varchar(255) DEFAULT NULL,
`NO_TELP` varchar(20) DEFAULT NULL,
PRIMARY KEY (`NO`));
The above Sql Query is a query to create a database named as test_jsp, and creating a table named as identitas with the following field described above on the image. The please fill up the table with few necessary data, in order to make the table containing data like the picture below. 
By that, one of our material for this application is created.
To do this development, actually we need or we will use a library, this library has an ability to support the creation of a web application only using jsp. The needed library is Jakarta Taglibs library.
Jakarta Taglibs is one of the open source project of Jakarta Tag Libraries which supporting a creation of a web application using JSP and without coding java classes. With the Taglibs concept means that will use this library supported API by calling it using the JSP’s tags. Since this web application using a database, so we need a library called DBTags (database tags), so please download the library here http://jakarta.apache.org/site/downloads/downloads_taglibs.html, and we will need other library which have a function as a MySQL database driver which is called as mysql-connector-java.jar, please also download this library here http://dev.mysql.com/downloads/connector/j/
After finished downloading those libraries, then please extract the both files. And the next step is preparing a web application on an Application Server or Servlet Container, for this exercise we using Tomcat as a Servlet Container.
- Create a folder on a WebApps folder on tomcat installation directory. Let’s give a name “MySimpleJSP”. This folder will be a folder path on our web application.
- Inside the folder is MySimpleJSP again, we make two more folders named WEB-INF and libs.
- Copy mysql-connector-java.5.1.6.jar file (I use the connector 5.1.6 here) from the mysql connector library, and taglibs-dbtags.jar files from libraries Jakarta Tablibs which we downloaded earlier into the libs folder.
- And also copy the taglibs-dbtags.tld file from Jakarta Taglibs library that we have extracted onto WEB-INF folder.
- Create a file called web.xml and then save the file also in the WEB-INF folder, while the contents of the web.xml file write the following directions:
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
version="2.5">
<jsp-config>
<taglib>
<taglib-uri>http://jakarta.apache.org/taglibs/dbtags</taglib-uri>
<taglib-location>/WEB-INF/taglibs-dbtags.tld</taglib-location>
</taglib>
</jsp-config>
</web-app>
Until here, we have completed preparations for web-applications for our experiment this time. And the next step is to simply make jsp files that we need to be able to access the MySQL database we have made earlier, all jsp files which later we will create will be placed in MySimpleJSP folder or parallel with WEB-INF folder and the libs folder. Let’s create a jsp file.
The first JSP file we will create is get_identity.jsp, this file is a jsp file in charge of taking the data in the tables located on the identitas table on test_jsp database. Let’s look at the codes below get_identity.jsp file:
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib uri="http://jakarta.apache.org/taglibs/dbtags" prefix="sql" %>
<html>
<head><title>My Simple JSP MYSQL Page</title></head>
<body>
<%-- Step 1) create a database connection --%>
<sql:connection id="connect">
<sql:url>jdbc:mysql://localhost/test_jsp</sql:url>
<sql:driver>com.mysql.jdbc.Driver</sql:driver>
<sql:userId>root</sql:userId>
<sql:password>admin99</sql:password>
</sql:connection>
<%--End Creating database Connection--%>
<%-- Step 2) Create a SQL query --%>
<sql:statement id="statement" conn="connect">
<sql:query>
SELECT * FROM identitas
</sql:query>
<table border="1">
<tr>
<th>NO</th>
<th>NAMA</th>
<th>ALAMAT</th>
<th>NO TELP</th>
</tr>
<%--Step 2.1) Loop the result--%>
<sql:resultSet id="data">
<tr>
<td><sql:getColumn position="1"/> </td>
<td><sql:getColumn position="2"/> </td>
<td><sql:getColumn position="3"/> </td>
<td><sql:getColumn position="4"/> </td>
</tr>
</sql:resultSet>
</table>
</sql:statement>
<%--Step 3) Close Database Connection--%>
<sql:closeConnection conn="connect"/>
</body>
</html>
Let’s make an attention the JSP’s code above:
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@taglib uri="http://jakarta.apache.org/taglibs/dbtags" prefix="sql" %>
On the second line we make a declaration tag that we shall use in this jsp file. Uri must be in line with taglib-uri which we declared in the web.xml file before. And if this web application run, the web.xml file have a responsibilities to find tags reference with sql prefix on taglibs-dbtags.tld file.
<%-- Step 1) create a database connection --%>
<sql:connection id="connect">
<sql:url>jdbc:mysql://localhost/test_jsp</sql:url>
<sql:driver>com.mysql.jdbc.Driver</sql:driver>
<sql:userId>root</sql:userId>
<sql:password>admin99</sql:password>
</sql:connection>
<%--End Creating database Connection--%>
While the above code block, we make declarations tags to connect to the mysql database that we created earlier.Please make attention each line above, the database is test_jsp then the driver is com.mysql.jdbc.Driver, and userid to connect to the mysql database is the root, and password to connect to the mysql database is admin99. My mysql database username is root and password is admin99, please adjust your database with the settings of each. Please make more attention in the code above, all of this configuration should be in block sql: connection
<%-- Step 2) Create a SQL query --%>
<sql:statement id="statement" conn="connect">
<sql:query>
SELECT * FROM identitas
</sql:query>
<table border="1">
<tr>
<th>NO</th>
<th>NAMA</th>
<th>ALAMAT</th>
<th>NO TELP</th>
</tr>
<%--Step 2.1) Loop the result--%>
<sql:resultSet id="data">
<tr>
<td><sql:getColumn position="1"/> </td>
<td><sql:getColumn position="2"/> </td>
<td><sql:getColumn position="3"/> </td>
<td><sql:getColumn position="4"/> </td>
</tr>
</sql:resultSet>
</table>
</sql:statement>
Then look at the rows of the following code, we create a block of tag sql:statement. Where in this block we write the block tag sql:query and tag sql:ResultSet, and in the block sql:ResultSet, we extract the data from the database to be displayed to the web page using tag sql:getColumn. If we try to understand the tags, the description may look like this: tag sql:query is a tag to write sql query which will be executed by the application. Then tag sql:ResultSet is a tag where the captured data from the database placed in the form to an array collection. And the last is the tag sql:getColumn is a way to extract the collection array and then sorted based on the basic view of our application web pages.
<%--Step 3) Close Database Connection--%>
<sql:closeConnection conn="connect"/>
And finally the last in this code is, that we then terminate the connection to our MySQL database.
Then after that, we can test this MySimpleJSP application by running our Tomcat and then call the get_identity.jsp files on your favorite browser pointed to the address: http://localhost:8000/MySimpleJSP/get_identity.jsp (I have configured tomcat to port 8000, please replace with the appropriate port in your tomcat configuration). If successful, it should display the web page is like this
With the appearance of the data into a web page of our JSP application, then we can describe that this exercise is successful done. For further step you can create more jsp pages, such as a jsp file to insert the data into the table, etc.
I Hope this usefull.
Thanks
Josescalia
6. And then run squid as a windows service by typing this command on the console: squid.exe -i. And then check if the squid are installed as a window service like picture below:
7. And then start the Squid Service.
Another Josescalia's Web Blog 
You must be logged in to post a comment.