21 - JSP Database Access

21.1 Overview of JSP Database Access

As discussed in earlier chapter, JSTL provides SQL tags to interact with database. In this chapter we will discuss how to use those tags.

Since this chapter is about interaction with database, we need to have a Database installed on system so we can use in our examples.

There are several database like MySQL, Oracle, DB2 etc available but in our chapter , we will use MySQL.

MySQL is a freely available open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). SQL is the most popular language for adding, accessing and managing content in a database. It is most noted for its quick processing, proven reliability, ease and flexibility of use. MySQL ships with no GUI tools to administer MySQL databases or manage data contained within the databases. MySQL Server is very fast, reliable, scalable, and easy to use.

MySQL is easy to install and light weight database as compared to Oracle.

As MySQL server does not ship with any GUI tool, we will use MySQLWorkbench as a tool to work with MySQL server

MySQL Workbench is a visual database design tool that integrates SQL development, administration, database design, creation and maintenance into a single integrated development environment for the MySQL database system.

21.2 MySQL installation

If you do not have MySQL server installed, follow below instructions to download and install MySQL server.

a) MySQL- Download MySQL version 5.5 from http://dev.mysql.com/downloads/windows/installer/5.5.html and install it on your machine.

b) MySQL Workbench- Download MySQL Workbench from http://dev.mysql.com/downloads/workbench/ and install it.

21.3 Database Creation

Lets create schema using MySQL Workbench with name ‘LIBRARY’

        

        

Create Table with name ‘Books’ using below create query

CREATE TABLE `library`.`books` ( `isbn` VARCHAR(45) NOT NULL , `name` VARCHAR(45) NOT NULL , `price` DOUBLE NOT NULL , `author` VARCHAR(45) NOT NULL , PRIMARY KEY (`isbn`) );

21.4 SQL Tags

JSTL SQL tag library can be used to interact with database. Tab library provides tag to perform several operations on database.

· URI for format tag library is http://java.sun.com/jsp/jstl/sql

· To use SQL tag library on JSP page we need to import its tag library using

   <%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%>
 

21.4.1 <sql:setDataSource>

In any application where we need to interact with database, we need to first create a datasource to provide all the required information about database so our application is aware about the database to connect.

This tag is used to configure all the required information about the database (in our case it will be LIBRARY ) and save it in a scoped variable.

Following attributes are supported by this tag-

· driver- this attribute tells the driver class to be loaded and registered to interact with database.

· url- URL for the database connection

· userusername of Database

· passwordpassword of Database

· var- variable to store datasource information

· scope- scope in which variable will be saved. Possible values are Page, request, session and application. Default value is Page.

Syntax of this tag looks like below –

<sql:setDataSource var="libraryDataSource" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost:3306/LIBRARY"
     user="user_id"  password="password"
     scope=”page | application | session | request”
/>

21.4.2 <sql:query>

This tag is used to fire the select query and saves the result in scoped variable.

Following attributes are supported by this tag-

· sql- to provide the select query. Alternatively we can add the select statement in body of tag

· datasource- database connection . This attribute should not be used with <sql:query> tag is used within <sql:transaction> tag

· var- variable to store result of query

· scope- scope in which variable will be saved. Possible values are Page ,request, session and application . Default value is Page.

Syntax of this tag looks like below –

<sql:query var="booksQuery" datasource=”libraryDatasource”
    scope=”page | application | session | request”>
     select * from books;
</sql:query>

21.4.3 <sql:update>

This tag is used to fire the insert, delete or update select query and saves the result(affected rows) in scoped variable.

Following attributes are supported by this tag-

· sql- to provide the select query. Alternatively we can add the insert, update or delete statement in body of tag

· datasource- database connection ,. This attribute should not be used with <sql:update> tag is used within <sql:transaction> tag

· var- variable to store result of query (affected records)

· scope- scope in which variable will be saved. Possible values are Page ,request, session and application . Default value is Page.

Syntax of this tag looks like below –

<sql:update var="booksQuery" datasource=”libraryDatasource”
    scope=”page | application | session | request”>
    delete  from books;
</sql:update>

21.4.4 <sql:param>

This tag is used with <sql:query> and <sql:update> to supply a value for a value placeholder

Syntax of this tag looks like below –

    <sql:param value="scoped_variable" /> >

21.4.5 <sql:transaction>

This tag is used to provide transactions functionality and used with <sql:query> and <sql:update> . All the statements added in this tag becomes the part of same transactions (which means either all changes are performed or none will be performed)

Syntax of this tag is

    <sql:transaction dataSource="libraryDatasource"> ">

21.5 Driver Download.

We need to download MySQL driver jar file from http://dev.mysql.com/downloads/file.php?id=13598 and place it in WEB-INF\lib directory of web application.

21.6 JSP Datase Connection Example

21.6.1 Create sqlInsert.jsp to insert rows in database.

<html>
  <head>
    <title> sql:insert tag example </title>
  </head>
  <%@ taglib prefix="c"   uri="http://java.sun.com/jsp/jstl/core" %>
  <%@ taglib prefix="sql"  uri="http://java.sun.com/jsp/jstl/sql" %>
  <body>
    <sql:setDataSource var="libraryDataSource" driver="com.mysql.jdbc.Driver"
       url="jdbc:mysql://localhost:3306/LIBRARY"
       user="root"  password="password"/>
    <sql:update dataSource="${libraryDataSource}" var="affectedRows">
       insert into books values
       ('ISBN1234', 'JSP Tutorial', '124.3','Joe Bloggs');
    </sql:update>
    Number of Rows Inserted are :: <c:out value="${affectedRows }"/>
  </body>
</html>

Access sqlInsert.jsp with Url http://localhost:8080/jsp-tutorial/sqlInsert.jsp

21.6.2 Create sqlUpdate.jsp to update rows in database.

<html>
  <head>
    <title> sql:update tag example </title>
  </head>
  <%@ taglib prefix="c"   uri="http://java.sun.com/jsp/jstl/core" %>
  <%@ taglib prefix="sql"  uri="http://java.sun.com/jsp/jstl/sql" %>
  <body>
    <sql:setDataSource var="libraryDataSource" driver="com.mysql.jdbc.Driver"
       url="jdbc:mysql://localhost:3306/LIBRARY"
       user="root"  password="password"/>
    <sql:update dataSource="${libraryDataSource}" var="affectedRows">
      update books set name='Advanced JSP Tutorial' where isbn = 'ISBN1234'
    </sql:update>
    Number of Rows Updated are :: <c:out value="${affectedRows }"/>
  </body>
</html>

Access sqlUpdate.jsp with Url http://localhost:8080/jsp-tutorial/sqlUpdate.jsp

21.6.3 Create sqlQuery.jsp to get the data.

<html>
  <head>
    <title> sql:query tag example </title>
  </head>
    <%@ taglib prefix="c"   uri="http://java.sun.com/jsp/jstl/core" %>
    <%@ taglib prefix="sql"  uri="http://java.sun.com/jsp/jstl/sql" %>
  <body>
    <sql:setDataSource var="libraryDataSource" driver="com.mysql.jdbc.Driver"
       url="jdbc:mysql://localhost:3306/LIBRARY"
       user="root"  password="password"/>
    <sql:query dataSource="${libraryDataSource}" var="records">
       select * from books;
    </sql:query>
    <table border="1" >
      <tr>
        <th>ISBN Number</th>
        <th>Name of Book</th>
        <th>Price in $ </th>
        <th>Author</th>
      </tr>
      <c:forEach var="row" items="${records.rows}">
      <tr>
        <td><c:out value="${row.isbn}"/></td>
        <td><c:out value="${row.name}"/></td>
        <td><c:out value="${row.price}"/></td>
        <td><c:out value="${row.author}"/></td>
      </tr>
      </c:forEach>
     </table>
   </body>
</html>

Access sqlQuery.jsp with Url http://localhost:8080/jsp-tutorial/sqlQuery.jsp

21.6.4 Create sqlDelete.jsp to delete the data. Also use <sql:param> tag to provide placeholder value

<html>
  <head>
    <title> sql:delete and sql:param tag example </title>
  </head>
    <%@ taglib prefix="c"   uri="http://java.sun.com/jsp/jstl/core" %>
    <%@ taglib prefix="sql"  uri="http://java.sun.com/jsp/jstl/sql" %>
  <body>
     <sql:setDataSource var="libraryDataSource" driver="com.mysql.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/LIBRARY"
        user="root"  password="password"/>
     <%
        String isbnNo="ISBN1234" ;
     %>
     <sql:update dataSource="${libraryDataSource}" var="affectedRows">
       delete from books   where isbn = ?;
       <sql:param value="<%= isbnNo %>"/>
     </sql:update>
     Number of Rows Deleted are :: <c:out value="${affectedRows }"/>
  </body>
</html>

Access sqlDelete.jsp with Url http://localhost:8080/jsp-tutorial/sqlDelete.jsp

21.6.5 Create sqlTransaction.jsp to insert, update and delete the data in a transaction.

<html>
  <head>
    <title> sql:transaction tag example </title>
  </head>
  <%@ taglib prefix="c"   uri="http://java.sun.com/jsp/jstl/core" %>
  <%@ taglib prefix="sql"  uri="http://java.sun.com/jsp/jstl/sql" %>
  <body>
     <sql:setDataSource var="libraryDataSource" driver="com.mysql.jdbc.Driver"
     url="jdbc:mysql://localhost:3306/LIBRARY"
     user="root"  password="password"/>
     <sql:transaction dataSource="${libraryDataSource}">
       <sql:update>
         insert into books values
         ('ISBN1234', 'JSP Tutorial', '124.3','Joe Bloggs');
       </sql:update>
       <sql:update >
         insert into books values
         ('ISBN4567', 'Servlets Tutorial', '224.3','Joe Bloggs');
       </sql:update>
       <sql:update >
         insert into books values
         ('ABCD4567', 'Java Tutorial', '129.3','Joe Bloggs');
       </sql:update>
       <sql:update>
          update books set name='Advanced JSP Tutorial' where isbn = 'ISBN1234';
       </sql:update>
       <sql:update >
         delete from  books where  isbn = 'ISBN4567';
       </sql:update>
     </sql:transaction>
     <sql:query dataSource="${libraryDataSource}" var="records">
       select * from books;
     </sql:query>
     <table border="1" >
       <tr>
         <th>ISBN Number</th>
         <th>Name of Book</th>
         <th>Price in $ </th>
         <th>Author</th>
       </tr>
       <c:forEach var="row" items="${records.rows}">
       <tr>
         <td><c:out value="${row.isbn}"/></td>
         <td><c:out value="${row.name}"/></td>
         <td><c:out value="${row.price}"/></td>
         <td><c:out value="${row.author}"/></td>
       </tr>
       </c:forEach>
     </table>
  </body>
</html>

Access sqlTransaction.jsp with Url http://localhost:8080/jsp-tutorial/sqlTransaction.jsp

Like us on Facebook