JSP Database

Back to: JSP Tutorials for Beginners and Professionals

How to interact with a Database in JSP

In this article, I am going to discuss How to interact with a Database in JSP Application. Please read our previous article, where we discussed JSP Standard Tag Library (JSTL) with Examples. As part of this article, we are going to discuss the following pointers.

JSP Database Access

In JSP, we are using databases for storing huge types of data. We can easily connect with databases to create and manage records. JSP has a number of actions for database access to improve the simple database-driven JAP applications. These actions provide the following features:

1. Better performance and scalability.
2. Supports SQL queries like INSERT, UPDATE, DELETE and UPDATE.
3. Handles most common data type conversions.
4. Supports a combination of databases.

What is JDBC?

The process of interacting with the database from Java Applications is called JDBC. JDBC is a Java API which enables java programs to execute SQL statements. It is an application programming interface that defines how a Java programmer can access the database in tabular format from Java code using a set of standard interfaces and classes written in the Java programming language.

JDBC has been developed under the Java Community Process that allows multiple implementations to exist and be used by the same application. JDBC is an API, which will provide a very good predefined library to connect with a database from JAVA applications in order to perform the basic database operations. In the case of JDBC applications, we will define the database logic and Java applications and we will send a Java-represented database logic to Database Engine. But database engine is unable to execute the Java-represented database logic, it should require the database logic in Query Language Representations. To execute JDBC applications we should require a conversion mechanism to convert the database logic from Java representations to Query Language representations and from Query Language representations to Java representations. In this situation, the required conversion mechanisms are available in the form of software called Driver.

Why do we need JDBC?

JDBC helps the programmers to write java applications that manage these three programming activities:

1. It helps us to connect to a data source, like a database.
2. It helps us in sending queries and updating statements to the database, and
3. Retrieving and processing the results received from the database in terms of answering your query.

Steps to Design JDBC Applications

Step1: Load and Register the Driver

First, you need to load the driver or register it before using it in the program. Registration is to be done once in your program. The forName() method of the Class class is used to register the driver class.

Syntax: public static void forName(String className)throws ClassNotFoundException 

You can register the Driver by following two ways:

1. Class.forName(): Here we load the driver’s class file into memory at the runtime. No need of using new or creation of objects.
2. DriverManager.registerDriver(): DriverManager is a Java inbuilt class with a static member register. Here we call the constructor of the driver class at compile time.

Step2: Establish a Database Connection

In order to establish communication with the database, you must first open a JDBC connection to the database. After loading the driver, establish connections using the DriverManager.getConnection() method. Following are three methods of DriverManager.getConnection():

1. getConnection(String url)
2. getConnection(String url, Properties prop)
3. getConnection(String url, String user, String password)

Syntax: public static Connection getConnection(String url,String name,String password) throws SQLException

Step3: Create the Statement

Once a connection is established you can interact with the database. The createStatement() method of the Connection interface is used to create a statement. The object of the statement is responsible to execute queries with the database.

Syntax: public Statement createStatement()throws SQLException  

Step4: Execute the Query

Now it’s time to process the result by executing the query. The executeQuery() method of Statement interface is used to execute queries to the database.  This method returns the object of ResultSet that can be used to get all the records of a table. The executeUpdate(sql query) method of the Statement interface is used to execute queries of updating/inserting.

Syntax: public ResultSet executeQuery(String sql)throws SQLException

Step5: Close the Connection

So finally, we have sent the data to the specified location. By closing the connection object statement and ResultSet will be closed automatically. The close() method of the Connection interface is used to close the connection.

Syntax: public void close()throws SQLException 

Database Creation using MySQL Database

Install MySQL in your device using the following link:

https://dev.mysql.com/downloads/windows/installer/5.5.html

Now, Create a Database using MySQL Workbench with the name “Employee”.

Below is the Script

You will get the following successful message once the database is created successfully.

Creating Table in Employee Database

Right-click on the created database and click on “Create Table”

Give appropriate Table name as “EmployeeDetails” and use the below script to create a table:

The following table is successfully created:

Create Data Records

After creating a table add records into your table.

Entered records are:

Database Operations in JSP Application

We can do multiple operations like insert the records, delete the records, edit the records and update the records into the database using JSP. To do so first we will create a “LIBRARY” database which is as follows:

Now use the “LIBRARY” database and create a “books” table with columns: ISBN Number, Name of Book, Price in $, and Author. Below is the query:

Database “LIBRARY” and the table “books” created successfully.

Now let’s perform different operations as follows:

INSERT Operation in JSP

The insert operation is used to insert the records into the database. In the below example we are inserting the records in the table. We are importing the core library of JSTL and giving its prefix which will help to get output. Connect JDBC driver and add appropriate username and password. We are using the SQL query of the insert query. If the records are inserted successfully you will get the appropriate message in the output.

<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="root" />
 <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>

Output

UPDATE Operation in JSP

In this example, we are updating the records in the table. We are importing the core library of JSTL and giving its prefix which will help to get output. Connect JDBC driver and add appropriate username and password. We are using SQL query of the update query. Here we are updating the “JSP Tutorial” value to “Advanced JSP Tutorial”. If the records are updated successfully you will get the appropriate message in the output.

<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="root" />
 <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>

Output

SELECT Operation in JSP

The select operation is used to select the records from the table. In this example, we are fetching records from the books table. We are importing the core library of JSTL and giving its prefix which will help to get output. Connect JDBC driver and add appropriate username and password. We are using the SQL query of the select query. At last, we are printing the output which is fetched from the results of the query using for each loop.

<html>
<head>
<title>SQL Select 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="876745" />
 <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>

Output

DELETE Operation in JSP

In this example, we are deleting the records in the table. We are importing the core library of JSTL and giving its prefix which will help to get output. Connect JDBC driver and add appropriate username and password. We are using the SQL query of the delete query. If the records are deleted successfully you will get an appropriate message in the output.

<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="root" />
 <%
  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>

Output

CRUD Operation in JSP Application

In this example, we are performing CRUD operating which is equivalent to INSERT, SELECT, UPDATE and DELETE statements in SQL. We will connect to the database by giving the appropriate username and password. Then execute INSERT statements to insert the data. Then we are using the UPDATE statement to update the “JSP Tutorial” value into “Advanced JSP Tutorial”. Then we are deleting the records using the DELETE statement in SQL. Then we are using SELECT operations to fetch all the available records.

<html>
<head>
<title>sql:CRUD 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="root" />
 <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>

Output

In the next article, I am going to discuss JSP XML Tags with Examples. Here, in this article, I try to explain How to interact with a Database in JSP with Examples. I hope you enjoy this JSP Database Access with Examples article.