Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

JSP Database Access: How to access database from a JSP Page

In this article, we will talk about the database access from JSP pages and the other related features.

Introduction

All the web applications usually interact with different types of databases. Databases are basically used to store various types of information for different purposes. There are also different types of databases used in the industry now days. But this is very important that the relational databases are by far the most widely used.

Generally the relational database uses the tables to represent the information which it handles. A table consists of rows and columns; row is identified with record whereas column is identified with field name, so each column holds a single value of a predefined data type. Generally these data types can be text data, numeric data, dates, and binary data such as images and sound as per our requirement. A dedicated language is called Structured Query Language (SQL) that is used to access the data. The Structured Query Language is an ANSI (American National Standards Institute) standard and is maintained by all the major database sellers. The Relational database engines come in all the shapes and sizes. Structured Query Language API (Application Programming Language) is used to execute structured queries in different database engine. The standard Java libraries include an API (Application Programming Language) called the Java Database Connectivity (JDBC) API. Java Database Connectivity defines a set of classes that can execute SQL statements, although the complexity of databases can vary extensively.

Accessing a database from a JSP Page

Java Server Pages has Standard Tag Library which includes the number of actions for the database access to improve the simple database-driven Java Server Page applications. Basically these actions are used to provide the following features:

  • Using a connection pool for better performance and scalability.
  • The features are to support the queries, updates, and insertion process.
  • To handle the most common data-type conversions.
  • To Support a combination of databases.

The Data Source Interface and JDBC Drivers

Applications get access to the database through an instance of the Java Database Connectivity interface named javax.sql.DataSource. The DataSource interface is the part of Java 2 Standard Edition (J2SE) 1.4, and for prior versions of the Java2 Standard Edition. Opening a new database connection is very time-consuming. A nice thing with a DataSource is that it can represent something which is called a connection pool. Using the connection pool, a connection to the database is opened once and uses it again and again. Basically a database action needs a connection; it gets it from the pool through the DataSource object and uses it to perform one or extra (Structure Query Language) SQL statements. In that position when the action closes the connection, the connection is returned to the pool where it can be used by the next action that needs it.

The DataSource, the Java Database Connectivity Application Programming Interface contains other classes and interfaces used by Java applications to process SQL statements in a database-independent way. For every database engine, an implementation of the interfaces defined by the Java Database Connectivity Application Programming Interface translates the generic calls to a format specific to the engine. This implementation is also called a JDBC driver. Through different drivers which provides same interface allows us to develop our application on one platform (for illustration, in the following program a Personal Computer with an Access database), and then install the application on another platform (for illustration, a Solaris or Linux server with an Oracle database).

Illustrations shows how to handle the embedded quotes in a string value, and how to deal with the input and output of date and time values, semantics for certain data types, and creation of unique numbers. The Java Server Pages Standard Tag Library actions take care of some of these, like as the string quoting and the date/time string format, so if we use these actions and stick to American National Standard Institute SQL, we should be able to voyage from one database to alternative database without too much modification. Basically we should always read our database and documentation very judiciously and attempt to stay away from the proprietary things. So to be prepared, spend at least some time to read the prerequisite to interchange the application from one DB to another.

The context parameter of the value that contains four pieces of information is separated by commas: a JDBC URL, a JDBC driver class name, a database account name, and the account password.

The Java Database Connectivity URL identifies a specific database. The different JDBC drivers use different Uniform Resource Locator syntax as per requirement. All the JDBC URLs starts with jdbc: and followed by a JDBC driver identifier [In JSP program we are discussing this], such as (Open database connectivity) odbc: for the JDBC-ODBC bridge driver and the mysql for the most ordinarily used MySQL driver. The URL identifies the database instance in the driver-dependent way. If we use an Access database, we need to create a system DSN for the database using the Open database connectivity. In the Windows Control Panel we must create a system DSN as opposed to a user DSN.

Point to be remembered that the web server that executes our JSP Pages usually runs as a different user account than the account you use for development. If you specify a user DSN with your development account, the web container will not be able to find it.

System Data Source Name definition window and working process is given below step by step:

  1. At first go to Control Panel and Open Administrative Tool.
  2. After that Open ODBC driver by using double click on it.
  3. Then click on add button.
Add data source window

Figure 1: Add data source window

1. Then give a name like “sgc” as shown below.

Provide data source name

Figure 2: Provide data source name

2. Then select the database that is present in the specific folder or path.

Enter database name and path selection

Figure 3: Enter database name and path selection

4. After selecting the database file, set the DSN as shown below.

Set data source

Figure 4: Set data source

Following the above process user can easily setup the DSN and this DSN is implemented at the program segment like the following

 Connection con=DriverManager.getConnection("jdbc:odbc:sgc","",""); 

Example of Database program using JSP

Here we will now discuss three sample examples like registerinsert.jsp , updateprogram.jsp and registrationsearch.html. These three programs are used for inserting record in the database, updating record in the database, searching record in the database. Here we are using DSN process which we have already discussed above. Here we have also used MS Access database through which we set DSN name “sgc”.

Listing 1: Sample showing sample registerinsert.jsp

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%@page import="java.sql.*"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Example of Java Server Page with JDBC</title>
    </head>
    <body>
<% 
String u=request.getParameter("userid");
String p=request.getParameter("password");
String n=request.getParameter("sname");
String e=request.getParameter("eid");
String a=request.getParameter("addr");
try
{
    	Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    	Connection con=DriverManager.getConnection("jdbc:odbc:sgc","","");
/* Passing argument through the question mark */
    	PreparedStatement ps=con.prepareStatement("insert into login values(?,?,?,?,?)") ;    	ps.setString(1,u);
    	ps.setString(2,p);
    	ps.setString(3,n);
    	ps.setString(4,a);
    	ps.setString(5,e);
    	int i=ps.executeUpdate(); /*Set the Update query command */
    	if(i!=0)
     	{                      
        		response.sendRedirect("index.jsp?msg=Thank You for registering with us in Mrbool !");
    	 }
     	else
    	 {
           		response.sendRedirect("registerinsert.jsp?msg=Insertion Failed!! Please try again!!!   ");
     	}
         con.close();
    }
    catch(Exception ex)
    {
        out.println(ex);
     }
%>
  </body>
</html>

Code explanation:

  1. The above code is used to insert record in to the database. Here we are accessing data from the entry form. After entering the data, user can easily access the record through the get parameter method.
  2. After that, we set odbc driver and through the DSN (Which is already discussed above) we link to the database.
  3. Now the prepareStatement is use d to pass the DML insert command. This command is used to store the record in the database.
  4. Now we set the string at the particular database filed.

This is the update program that is used to update existing records in the database.

Listing 2: Sample showing updateprogram.php

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>JSP Page</title>
    </head>
    <body>
  <%
        String uid=(String)session.getAttribute("userid");
        String p=request.getParameter("password1");
        String n=request.getParameter("name1");
        String e=request.getParameter("eid");
        String a=request.getParameter("address");
        try{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection con=DriverManager.getConnection("jdbc:odbc:sgs","","");
            PreparedStatement ps=con.prepareStatement("update login set password1=?,name1=?,eid=?,address=? where userid=?");
        ps.setString(1,p);
        ps.setString(2,n);
        ps.setString(3,e);
        ps.setString(4,a);
        ps.setString(5,uid);
        int i=ps.executeUpdate();
            if(i!=0)
            {
                response.sendRedirect("index.jsp?msg=Mrbool Profile Updated Successfully!!!!!!!");
            }
            else
            {
                response.sendRedirect("pUpdate.jsp?msg=Mrbool  Profile Updation Failed Please Try again!!!!");
            }
     con.close();
        }
    catch(Exception ex){
        out.println(ex);
        }
    %>
    </body>
</html>

Code explanation:

  • This program mostly works as per the previous system procedure model. Through this program model we can develop a data uploading structure.
  • Here we use update command. This update command passes data through a unique value.
  • After that if the updating is executed successfully, and then it will redirect the page.

This is the searching program. Through this program model we can search all the data that is already present in the database.

Listing 3: Sample showing registrationsearch.jsp

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ page import="java.sql.*,java.util.*" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252" />
<title>Photo gallery</title>
<link rel="stylesheet" type="text/css" href="style.css" />

</head>
<body>
<div id="Layer9">
<% 
  String msg=request.getParameter("msg");  
  
  if(msg!=null)
  {
     out.print("<h1>"+"Welcome "+(String)session.getAttribute("u")+"</h1>");
      out.print(msg);
	} 
  %>
</div>
<div id="wrap">
<% 
     String uid=(String)session.getAttribute("u");
             if(uid==null) 
              {   
    %> <div class="header">
       		<div class="logo"><a href="index.jsp"><img class='imagem_artigo' src="images/pg.gif" width="294" height="73" /></a></div>            
        <div id="menu">
            <ul>                                                                       
            <li class="selected"><a href="index.jsp" class="style12">home</a></li>
            <li><a href="aboutus.jsp" class="style12">about us</a></li>
            <li><a href="gallery.jsp" class="style12">Gallery</a></li>
            
            <li><a href="myaccount.jsp" class="style12">log in</a></li>
            <li><a href="register.jsp" class="style12">register</a></li>
            <li></li>
            <li></li>
           
            </ul>
        </div>     
        <%}
      else
          {
     %>    <div class="header">
       		<div class="logo"><a href="index.jsp"><img class='imagem_artigo' src="images/pg.gif" alt="" title="" border="0" /></a></div>
        <div id="menu">
            <ul>                                                                       
            <li class="selected"><a href="index.jsp" class="style12">home</a></li>
            <li><a href="aboutus.jsp" class="style12">about us</a></li>
            <li><a href=" gallery.jsp" class="style12">Gallery</a></li>
            <li><a href="photoFormupdate.jsp" class="style12"> Upload iamges </a></li>
            <li><a href="pUpdate.jsp"><span class="style12">Update Profil</span>e</a></li>
            <li><a href="logOut.jsp" class="style12">logout</a></li>
            <li></li>
            </ul>
        </div>
<%
   }
%>            
       </div> 
            <div class="center_content">
         <div id="Layer2">         </div>
         <div id="Layer8">
		 <div class="footer"></div>
       </div>       
         <div class="new_products"></div>
           <div class="clear"></div>
  </div>
  
<div class="clear"></div>
</div>
<div id="Layer4">
 <h1>Photo Gallery</h1>
</div>
<div id="Layer5">
 
   <span class="style4"><font face="Algerian" size="5">Images By Category</font></span>  
    ::<br />
<font size="3" face="Courier" color="#CC0033" align="center">
</font><font size="3" color="#CC0033" align="center"><span class="style12"><a href=" registrationsearch.jsp?Category=Nature">Nature</a><br />
<a href=" registrationsearch.jsp?Category=Sports">Sports</a><br />
<a href=" registrationsearch.jsp?Category=Baby">Baby</a><br />
<a href="search.jsp?Category=Animals">Animals</a></span></font><font size="3" face="Courier" color="#CC0033" align="center"><br />
 </font></div>
<div id="Layer7">
<%  
      String cat=request.getParameter("Category");
   int i=0;
    ArrayList ar=new ArrayList();  // This part is used for creating new array list.
    ArrayList pi=new ArrayList();            
                try{
                    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                    Connection cn=DriverManager.getConnection("jdbc:odbc:sgc","","");
     
                  PreparedStatement ps=cn.prepareStatement("select * from photoDetails where pct=?");
                    ps.setString(1,cat);
                    ResultSet rs=ps.executeQuery();
                    while(rs.next())
{
                      i++;
                        String pUrl02=rs.getString(5).trim();
                        String pUrl2=pUrl02.substring(pUrl02.lastIndexOf("\\")+1);
                        ar.add(pUrl2);
                       pi.add(rs.getString(1)); 
                     }
    out.print(i+cat);
                        cn.close(); /*Close the connection*/
                }
                catch(Exception e){
                    out.println(e);
                    } 
 %> 
     <table bgcolor="orange" border="5" width="550" height="220"> 
               <%  int j=0,k=0,t=0;
                while(k<4)
                {  %>
                       <tr>
                        <% 
                        j=0;
                        while(j<3 && j+t<i)
                        { %> 
                        <td> <a href="index.jsp?msg=<%=pi.get(j+t)%>"><img class='imagem_artigo' src="<%=(String)ar.get(j+t)%>" alt=""  height="200" width="200"/></a>
                                               <% 
                       
                        j++;
                        }
                        %>
                        
                    </tr>
             <%
                  t+=3; 
                 
                  k++;
                   }
                  %> 
  </table> 
</div>
</body>
</html>

 

And the css of the page above is:

Listing 4: CSS

#Layer1 {
	position:absolute;
	width:200px;
	height:115px;
	z-index:1;
	left: 751px;
	top: 186px;
	background-color: #993399;
}
#Layer2 {
	position:absolute;
	width:897px;
	height:1174px;
	z-index:1;
	left: 261px;
	top: 181px;
	background-color: #9900CC;
}

#Layer4 {
	position:absolute;
	width:891px;
	height:132px;
	z-index:3;
	left: 264px;
	top: 182px;
	background-color: #009900;
}
#Layer5 {
	position:absolute;
	width:217px;
	height:266px;
	z-index:4;
	left: 939px;
	top: 318px;
	background-color:  #FC3;
}
#Layer6 {
	position:absolute;
	width:678px;
	height:508px;
	z-index:5;
	left: 145px;
	top: 334px;
}
#Layer7 {
	position:absolute;
	width:673px;
	height:1038px;
	z-index:5;
	left: 263px;
	top: 316px;
	background-color: #CCCCCC;
}
#Layer8 {
	position:absolute;
	width:898px;
	height:107px;
	z-index:6;
	left: 262px;
	top: 1240px;
	background-color: #EFEFEF;
}
#Layer9 {
	position:absolute;
	width:429px;
	height:79px;
	z-index:7;
	left: 421px;
	top: 232px;
}

.style2 {
	font-family: Algerian;
	color: #000000;
}
.style4 {color: #000000}
.style12 {font-family: algerian}

Code explanation:

  1. This is the searching program.
  2. Select query is used here for searching the record. Here we are searching picture wise.
  3. rs is the ResultSet in the program segment. Through the result set process we can easily search the record.

This JSP page can work with all possible Databases like MS Access, MySql, Oracle, and Microsoft SQL Server. In this Article it is very much clear that JSP technology can work with JDBC ODBC driver. Through this driver process we must set up DSN connection. Software developers and designers use it widely for more portable and reliable database connection process. We have also discussed the DNS set up process. Developers can follow the process easily and set up their own database connection from JSP page.



Website: www.techalpine.com Have 16 years of experience as a technical architect and software consultant in enterprise application and product development. Have interest in new technology and innovation area along with technical...

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login