31 JSP with JDBC

Dr M. Vijayalakshmi

JDBC

JDBC is an application programming interface between Java programs and database management systems. JDBC is a core part of the Java platform and is included in the standard JDK distribution. The purpose of JDBC is to connect database and manipulate the data in database from a Servlet page or from a JSP page.

Basic Steps to Connect Database

1. Establish a connection.

2. Create JDBC Statements.

3. Execute SQL Statements.

4. Get ResultSet.

5. Close connections.

Step Description

1. Establish a connection

A connection to MySQL is established by using the package,

import java.sql.*;

Then load the vendor specific driver,

Class.forName(“oracle.jdbc.driver.OracleDriver”);

This code dynamically loads a driver class, for Oracle database.

Make the connection,

Connection conn = DriverManager.getConnection(DB_URL,USER,PASS);

This code establishes connection to database by obtaining a Connection object.

2. Create JDBC statement(s)

JDBC statements are created using the following code,

Statement stmt = con.createStatement() ;

This creates a Statement object for sending SQL statements to the database.

3. Executing SQL Statements

SQL statements are executed using the query,

sql = “SELECT attributes FROM Table”;

Get ResultSet

The Resultset object is obtained by the executeQuery() function,

ResultSet rs = Stmt.executeQuery(sql);

Close Connection

The JDBC connection and SQL query execution is closed by calling the close() function,

con.close();

Install Mysql

Install Mysql Database from MYSQL official website dev.mysql.com

Select the Platform type (i.e Windows or Mac)and download the Installer.

Steps to run JSP using Tomcat server

STEP 1: To run JSP pages, Download TOMCAT SERVER from Website.

URL: https://tomcat.apache.org/download-60.cgi

STEP 2: The tomcat folder has the following files as bin, conf, lib, logs, temp, webapps and work.

STEP 3: Create directory “myjsp” under the webapps directory.

STEP 4: Save this file in shown in the given path,

C:/ Program Files/…/…/webapps/myjsp/Filename.

STEP 5: Download Mysql connector jar file from the following url.

URL:https://dev.mysql.com/downloads/connector/j/3.1.html

STEP 6:Copy the jar file to apache-tomcat\webapps\ROOT\WEB-INF\lib directory.

STEP 7: To run JSP program

Start the tomcat service,

Start -> run -> services.msc -> Select Apache Tomcat -> start

Open new tab in browser or open new window,

http://localhost:8080/myjsp/Filename.jsp

Creating a Table using JSP

Example Code:

The following code is for creating a table using a JSP page. First establish a connection to the database from the JSP page. When connection is successful, execute the SQl statement “CREATE table test (testid mediumint(8), name varchar(100))” using the executeUpdate() function.

<%@page contentType=”text/html” pageEncoding=”UTF-8″%> <!DOCTYPE html><%@ page import=”java.sql.*” %> <%@ page import=”com.mysql.jdbc.Driver” %>

<%!String driver = “com.mysql.jdbc.Driver”;

String url = “jdbc:mysql://localhost/PUB”;

String name = “root”; String pass=”password”; %>

<html>

<head>

<title>testJSP</title>

</head>

<body>

<p>Attempting to open JDBC connection to:… </p><%=url%>

try{

String tableStr = “CREATE table test (testid mediumint(8), name varchar(100))”; Class.forName( driver );

Connection con = DriverManager.getConnection( url, name, pass ); Statement stat = con.createStatement(); %><p> executing: <%=tableStr%></p>%stat.executeUpdate( tableStr );%><p> success…. </p><%con.close();

}

catch (SQLException sqle)

{

out.println(“<p> Error opening JDBC, cause:</p> <b> ” + sqle + “</b>”);

}

catch(ClassNotFoundException cnfe)

{

out.println(“<p> Error opening JDBC, cause:</p> <b>” + cnfe + “</b>”);

}

</body>

</html>

Creating a Table: Database

OUTPUT:

The output of the above code is shown below.

DATABASE:

SELECT Operation

Example Code:

The following code is for selecting records from a table using a JSP page and display the records in a table format. First establish a connection to the database from the JSP page. When connection is successful, execute the SQL statement “select * from Publisher” using the executeQuery() function.

<%@page contentType=”text/html” pageEncoding=”UTF-8″%> <!DOCTYPE html>

<%@ page import=”java.sql.*” %>

<% Class.forName(“com.mysql.jdbc.Driver”); %>

<HTML>

<HEAD>
<TITLE>The Publishers Database Table </TITLE>

</HEAD>

<BODY>

<H1>The Publishers Database Table </H1>

<%Connection connection = DriverManager.getConnection(“jdbc:mysql://localhost/PUB”, “root”, “password”);Statement statement = connection.createStatement() ;ResultSet resultset = statement.executeQuery(“select * from Publisher”) ; %>

<TR>

<TH>State</TH>

<TH>Country</TH>

</TR>

<% while(resultset.next()){ %>

<TR>

</TR>

<% } %>

</TABLE>

</BODY>

</HTML>

OUTPUT:

The output of the above code is shown below.

INSERT Operation

Example Code:

The following code is for inserting records into a table using a JSP page. First establish a connection to the database from the JSP page. When connection is successful, execute the SQl statement “INSERT

INTO Employees VALUES (105, 22, ‘Jeeva’, ‘Kumar’)”.

<%@ page import=”java.io.*,java.util.*,java.sql.*”%>

<%@ page import=”javax.servlet.http.*,javax.servlet.*” %>

<%@ taglib uri=”http://java.sun.com/jsp/jstl/core” prefix=”c”%>

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

<html>

<head>

<title>JINSERT Operation</title>

</head>

<body>

<sql:setDataSource var=”snapshot” driver=”com.mysql.jdbc.Driver” url=”jdbc:mysql://localhost/TEST” user=”root” password=”password”/>

<sql:update dataSource=”${snapshot}” var=”result”>

INSERT INTO Employees VALUES (105, 22, ‘Jeeva’, ‘Kumar’); </sql:update>

<sql:query dataSource=”${snapshot}” var=”result”> SELECT * from Employees; </sql:query>

Output:

The output of the above code is shown below

Database (Before Executing):

DELETE Operation

The following code is for deleting records from a table using a JSP page. First establish a connection to the database from the JSP page. When connection is successful, execute the SQL statement “DELETE FROM Employees WHERE Id = ?”.

Example

<%@ page import=”java.io.*,java.util.*,java.sql.*”%>

<%@ page import=”javax.servlet.http.*,javax.servlet.*” %>

<%@ taglib uri=”http://java.sun.com/jsp/jstl/core” prefix=”c”%>

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

<html>

<head>

<title>DELETE Operation</title>

</head>

<body>

<sql:setDataSource var=”snapshot” driver=”com.mysql.jdbc.Driver”url=”jdbc:mysql://localhost/TEST”user=”root” password=”password”/>

<c:set var=”empId” value=”102″/>

<sql:update dataSource=”${snapshot}” var=”count”> DELETE FROM Employees WHERE Id = ? <sql:param value=”${empId}” /> </sql:update>

<sql:query dataSource=”${snapshot}” var=”result”> SELECT * from Employees;

</sql:query>

<tr>

<th>First Name</th>

</tr>

<c:forEach var=”row” items=”${result.rows}”>

<tr>

</tr>

</c:forEach>

</table>

</body>

</html>

Output:

The output of the above code is shown below.

Login Application

Example

//Index.html

<body>

User name :<input type=”text” name=”userid” /> password :<input type=”password” name=”password” /> <input type=”submit” />

</form>

</body>

<%@ page import =”java.sql.*” %>

<%@ page import =”javax.servlet.http.*” %>

<%String userid=request.getParameter(“userid”);

session.putValue(“userid”,userid);

String password=request.getParameter(“password”); Class.forName(“com.mysql.jdbc.Driver”); java.sql.Connection con =DriverManager.getConnection(“jdbc:mysql://localhost/test”,”root”,”password”);

Statement st= con.createStatement();

ResultSet rs=st.executeQuery(“select * from users where user_id='”+userid+”‘”);

if(rs.next())

{

if(rs.getString(2).equals(password))

{

out.println(“welcome”+userid);

}

else

{

out.println(“Invalid password try again”);

}

else%>

Output:

The output of the above code is shown below.

Database:

Summary

In this section we discussed about how to install MySQL and run JSP pages in Tomcat. The module also explains about the operational views of how to Create table, Insert and Delete data in JDBC from JSP page illustrated with needed examples. Finally, this module also demonstrates a simple login Application created using JSP with JDBC.

Web Links

Herbert Schildt, ” Java: The Complete Reference”, 9th Edition, Mcgraw-Hill, 2014.
https://www.tutorialspoint.com