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"> </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...
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Current Comments
39 comments so far (post your own) View All Comments Latest 10 Comments:Hi,This is Pradeep,
I want to know how to write custom tags in jsp,pls give me a simple example with explanation
Posted by M.V.V.Pradeep on Thursday, 05.8.08 @ 16:35pm | #58911
i want edit&delete program in jsp using jdeveloper.
please sebd the code.
Posted by ashok on Friday, 05.2.08 @ 09:40am | #58352
I have made one jsp page which is having 11 buttons. if i will click any of button it should fetch the data from oracle database corresponding to that table. what is the code which i can use for this. or if u can give any link or code for this.
Posted by Ankur on Tuesday, 03.25.08 @ 11:51am | #54233
hi please send the full codings it helps to create a new document.
Posted by subhashini on Wednesday, 03.5.08 @ 11:24am | #51393
hi,
Please send me full code.......
i will try to help for ur problem..
Regards,
G.Krishna
Posted by krishna on Thursday, 02.28.08 @ 17:23pm | #50457
plz help me.......
exception
org.apache.jasper.JasperException: Exception in JSP: /in.jsp:22
19: if(request.getParameter("action") != null){
20: String bookname=request.getParameter("bookname");
21: String author=request.getParameter("author");
22: stmt.executeUpdate("insert into books_details(book_name,author) values('"+bookname+"','"+author+"')");
23: rst=stmt.executeQuery("select * from books_details");
24: %>
25: <html>
Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:504)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:393)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
root cause
java.lang.NullPointerException
org.apache.jsp.in_jsp._jspService(in_jsp.java:65)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:332)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
note The full stack trace of the root cause is available in the Apache Tomcat/5.5.17 logs.
Posted by sreekanth on Thursday, 02.28.08 @ 11:41am | #50383
org.apache.jasper.JasperException: Unable to compile class for JSP
An error occurred at line: 2 in the jsp file: /in.jsp
Generated servlet error:
String literal is not properly closed by a double-quote
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:510)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:375)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
root cause
Posted by sreekanth on Wednesday, 02.27.08 @ 11:38am | #50280
Hi
This is Raghavendra
I have doubt how to extablish the connection between JAVA progam and Database i.e sql,
pls give me example also.
Posted by Raghavendra on Monday, 02.25.08 @ 14:57pm | #49943
Hi,
I think your error is related to the incorrect installation of the mysql.
Please reinstall MySQL on fresh machine and then try.
Hopefully on new machine your problem will be solved.
Thanks
Posted by Deepak Kumar on Thursday, 02.21.08 @ 17:06pm | #49316
i cant create table in mysql because of this error message"access violation at address 005cfc42 in module "heidisql.exe" read the address 00000054 " kindly help me to fix this problem..coz i need it badly..Thanks!!!
Posted by marky on Thursday, 02.21.08 @ 16:36pm | #49312