Home Jsp Accessing database from JSP
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

Accessing database from JSP

Advertisement
In This article I am going to discuss the connectivity from MYSQL database with JSP.we take a example of Books database. This database contains a table named books_details.

Accessing database from JSP

        

Introduction

In This article I am going to discuss the connectivity from MYSQL database with JSP.we take a example of Books database. This database contains a table named books_details. This table contains three fields- id, book_name& author. we starts from very beginning. First we learn how to create tables in MySQl database after that we write a html page for inserting the values in 'books_details' table in database. After submitting values a table will be showed that contains the book name and author name.

Database

The database in example consists of a single table of three columns or fields. The database name is "books" and it contains information about books names & authors.


Table:books_details

  ID   Book Name   Author
   1.  Java I/O  Tim Ritchey
   2.

 Java & XML,2 Edition   

 Brett McLaughlin
   3.  Java Swing, 2nd Edition

 Dave Wood, Marc Loy,

Start MYSQL prompt and type this SQL statement & press Enter-

    MYSQL>CREATE DATABASE `books` ;

This will create "books" database.
Now we create table a table "books
_details" in database "books".
   
  
   MYSQL>CREATE TABLE `books_details` (
    `id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
    `book_name` VARCHAR( 100 ) NOT NULL ,
   `author` VARCHAR( 100 ) NOT NULL ,
    PRIMARY KEY ( `id` ) 
    ) TYPE = MYISAM ;

This will create a table "books_details" in database "books"

JSP Code

The following code contains  html for user interface & the JSP backend-

<%@ page language="java" import="java.sql.*" %>
<%
	String driver = "org.gjt.mm.mysql.Driver";
	Class.forName(driver).newInstance();
	
	Connection con=null;
	ResultSet rst=null;
	Statement stmt=null;
	
	try{
		String url="jdbc:mysql://localhost/books?user=
<user>&password=<password>";
		con=DriverManager.getConnection(url);
		stmt=con.createStatement();
	}
	catch(Exception e){
		System.out.println(e.getMessage());
	}
	if(request.getParameter("action") != null){ 
		String bookname=request.getParameter("bookname");
		String author=request.getParameter("author");
		stmt.executeUpdate("insert into books_details(book_name,
author) values('"+bookname+"','"+author+"')");
		rst=stmt.executeQuery("select * from books_details");
		%>
		<html>
		<body>
		<center>
			<h2>Books List</h2>
			<table border="1" cellspacing="0" cellpadding
="0">
			<tr>
				<td><b>S.No</b></td>
				<td><b>Book Name</b></td>
				<td><b>Author</.b></td>
			</tr>
			 	<%
				int no=1;
				while(rst.next()){
				%>
				<tr>
				  <td><%=no%></td>
				  <td><%=rst.getString("
book_name")%></td>
				  <td> <%=rst.getString("author")
%> </td>
				</tr>
				<%
				no++;
	}
	rst.close();
	stmt.close();
	con.close();
%>
			</table>
			</center>
		</body>
	</html>
<%}else{%>
	<html>
	<head>
		<title>Book Entry FormDocument</title>
		<script language="javascript">
		    function validate(objForm){
			if(objForm.bookname.value.length==0){
			alert("Please enter Book Name!");
			objForm.bookname.focus();
			return false;
			}
			if(objForm.author.value.length==0){
			alert("Please enter Author name!");
			objForm.author.focus();
			return false;
			}
			return true;
				}
			</script>
		</head>
		
		<body>
			<center>
<form action="BookEntryForm.jsp" method="post" 
name="entry" onSubmit="return
 validate(this)">
	<input type="hidden" value="list" name="action">
	<table border="1" cellpadding="0" cellspacing="0">
	<tr>
		<td>
			<table>
				<tr>
				<td colspan="2" align="center">
<h2>Book Entry Form</h2></td>
				</tr>
				<tr>
				<td colspan="2">&nbsp;</td>
				</tr>
				<tr>
				<td>Book Name:</td>
				<td><input name="bookname" type=
"text" size="50"></td>
				</tr>
				<tr>
				<td>Author:</td><td><input name=
"author" type="text" size="50"></td>
				</tr>
				<tr>
					<td colspan="2" align="center">
<input type="submit" value="Submit"></td>
					</tr>
				</table>
			</td>
		</tr>
	</table>
</form>
			</center>
		</body>
	</html>
<%}%>

Now we explain the above  codes.

Declaring Variables: Java is a strongly typed language which means, that variables must be explicitly declared before use and must be declared with the correct data types. In the above example code we declare some variables for making connection. Theses variables are- 

Connection con=null;
ResultSet rst=null;
Statement stmt=null;


The objects of type Connection, ResultSet and Statement are associated with the Java sql. "con" is a Connection type object variable that will hold Connection type object. "rst" is a ResultSet type object variable that will hold a result set returned by a database query. "stmt" is a object variable of Statement .Statement Class methods allow to execute any query.  

Connection to database: The first task of this programmer is to load database driver. This is achieved using the single line of code :-

String driver = "org.gjt.mm.mysql.Driver";
Class.forName(driver).newInstance();

The next task is to make a connection. This is done using the single line of code :-

String url="jdbc:mysql://localhost/books?user=<userName>&password=<password>";
con=DriverManager.getConnection(url);

When url is passed into getConnection() method of DriverManager class it  returns connection object. 

Executing Query or Accessing data from database:

This is done using following code :-

stmt=con.createStatement(); //create a Statement object 
rst=stmt.executeQuery("select * from books_details");

stmt is the Statement type variable name and rst is the RecordSet type variable. A query is always executed on a Statement object.
A Statement object is created by calling createStatement() method on connection object con. 

The two most important methods of this Statement interface are executeQuery() and executeUpdate(). The executeQuery() method executes an SQL statement that returns a single ResultSet object. The executeUpdate() method executes an insert, update, and delete SQL statement. The method returns the number of records affected by the SQL statement execution.

After creating a Statement ,a method executeQuery() or  executeUpdate() is called on Statement object stmt and a SQL query string is passed in method executeQuery() or  executeUpdate().
This will return a ResultSet rst related to the query string.

Reading values from a ResultSet:

while(rst.next()){

   %>

   <tr><td><%=no%></td><td><%=rst.getString("book_name")%></td><td><%=rst.getString("author")%></td></tr>

  <% 

}

The ResultSet  represents a table-like database result set. A ResultSet object maintains a cursor pointing to its current row of data. Initially, the cursor is positioned before the first row. Therefore, to access the first row in the ResultSet, you use the next() method. This method moves the cursor to the next record and returns true if the next row is valid, and false if there are no more records in the ResultSet object.

Other important methods are getXXX() methods, where XXX is the data type returned by the method at the specified index, including String, long, and int. The indexing used is 1-based. For example, to obtain the second column of type String, you use the following code:

resultSet.getString(2);

You can also use the getXXX() methods that accept a column name instead of a column index. For instance, the following code retrieves the value of the column LastName of type String.

resultSet.getString("book_name");

The above example shows how you can use the next() method as well as the getString() method. Here you retrieve the 'book_name' and 'author' columns from a table called 'books_details'. You then iterate through the returned ResultSet and print all the book name and author name in the format " book name | author " to the web page.

Summary:

This article presents JDBC and shows how you can manipulate data in a relational database from your  JSP page. To do this, you need to use  the java.sql package: DriverManager, Connection, Statement, and ResultSet. Keep in mind, however, that this is only an introduction. To create a Web application, you need  JDBC to use more features such as prepared statements and connection pooling.

To Download Example click here

When you click on the above link a Book Entry Form will open

 

Fill the book name and author fields and press Submit button. A page will open and show  a table of book name and authors like...

Advertisements

Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: March 13, 2008

Ask Questions?    Discuss: Accessing database from JSP   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
shema
March 30, 2011
love u

Ilove u. ur my best teacher ever!!!
aslam
June 2, 2011
not able to execute above code

org.apache.jasper.JasperException: An exception occurred processing JSP page /Examples/BookEntryForm.jsp at line 5
LAKSHMIKANTH VALLAMPATI
September 4, 2011
Request about the tutorials

Dear sir/madam, Please provide the jsp tutorials for ORACLE database connectivity along with MySQL. Because in many universities' academic curriculum ORACLE is mandatory. So those tutorials are very much beneficila to beginner level students.
Aniket
November 3, 2011
help require

can any1 explain the complete code on that page to me , as i am getting some problems 1) the id of the table while inserting new values( not in a order). 2)use of hidden fields 3) juz refresh the output page...(nd u got the problem
Ratikanta Sahoo
November 15, 2011
jsp

i think that it is not correct
Anil
January 12, 2012
JSP

very helpful
Ashish
January 26, 2012
accesing data from database

I am seeing error at this line stmt.executeUpdate("insert into books_details values('"+bookname+"', '"+author+"')"); pls help me
Goutham Avireddy
March 13, 2012
query

can anyone give me how to access the db2 from jsp page ? ? ?
kanchan pal
March 18, 2012
accessing database from jsp page

plz get this question's answer
rajendra shaw
March 25, 2012
nice content

nice content here
wale
November 29, 2012
how to create the BookEntryForm and codes

am learning this tutorial and i need your help
vasavi
March 31, 2013
unit 7@8

plz send 3-2 materials this 2 units.
jitendra
May 11, 2013
jsp

how i can access oracle database from JSP
Manish Joshi
September 3, 2013
jsp related

may i want to retrieve value from oracle database and display on html page using jsp
DMCA.com