NDSU CS Department Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Connecting to the database through JSP ======

There are two distinct methods for connecting to the database using JSP. The JSP will be compiled into a servlet, and that happens automatically each time you change the code. The first method makes database connections in the .jsp page itself, while the second one sets up the database connection in a separate class (called a bean in Java server programming).

===== Connecting within JSP page =====
----
Here is an example for connecting to the Oracle database through JSP. This approach mixes the presentation and logic.  Using a bean allows separating those.

This is very similar to accessing the database through a java application, except that it is not run by the user who writes the code.  As a result, the classpath settings are different.  As long as you use the directory structure discussed below, you should not have problems with the class path.

This method is **not recommended** as it may expose your username and password if you are using a shared environment such as the department lab. This is because the public_html directory cannot be read protected and still serve its purpose.  Using a bean eliminates this problem and is much more commonly used in real-world/business situations.

<code>
<html>
<head>
</head>
<body>

<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>

<%
        //
        Class.forName("oracle.jdbc.driver.OracleDriver");

        // Initialize connection to database
        //      Remember to change username and password
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@db1.chpc.ndsu.nodak.edu:1521:cs", "username", "password");

        // Create a statement and a query, execute the query
        Statement stmt = conn.createStatement();

        String query = ""; //Remember to change this to match your database

        ResultSet rset = stmt.executeQuery(query);

        // Iterate through all result rows
        while(rset.next())
        {
                // Print the first item of the result
                out.println(rset.getString(1));

                // Print a line break
                out.println("<br/>");
        }

        // Clean up
        stmt.close();
        rset.close();
        conn.close();

%>

</body>
</html>
</code>

===== Connecting using a Bean =====
----
If you want to split presentation and database access to make a 3-tier application (presentation, logic, database), you will need two pieces:

   - A class (called a 'bean') to handle all of the database access
   - A JSP page to call functions of the class and display query results

The logic layer (database queries and other logic) will be implemented in a class, the presentation layer (your JSP/HTML) will call functions of the class, and the class will do the work of getting data from the database. 

==== The Class (Bean) ====

You will need a class for your logic layer. This class needs to be part of a package otherwise the server won't be able to access it. 

Here is an example class:
<code>
package beans;

import java.io.*;
import java.sql.*;
import java.util.*;

public class DBCon
{
        public DBCon()
        {
                // empty constructor
        }

        public ArrayList getRecords()
        throws SQLException, ClassNotFoundException
        {
                ArrayList records = new ArrayList();
                // Load Oracle driver
                Class.forName("oracle.jdbc.driver.OracleDriver");

                // Initialize connection to database
                //      Remember to change username and password
                Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@db1.chpc.ndsu.nodak.edu:1521:cs", "username", "password");

                // Create a statement and a query, execute the query
                Statement stmt = conn.createStatement();

                String query = ""; //Remember to change this to match your database

                ResultSet rset = stmt.executeQuery(query);

                // Iterate through all result rows
                while(rset.next())
                {
                        records.add(rset.getString(1));
                }

                // Clean up
                stmt.close();
                rset.close();
                conn.close();

                return records;
        }
}
</code>

This class is part of the package 'beans'. There is a single function that returns the first item of each query result. You will want to add functions or change this function to match your database and project. These functions can be called by the JSP page.

Once compiled, this class needs to be placed in the folder: /home/username/public_html/WEB-INF/classes/<packagename>/ where '<packagename>' is the name of the package your class belongs to. In this case the package name is 'beans'. 

==== The JSP page ====

The JSP page will be placed in the public_html directory, and can be reached through the URL http://students.cs.ndsu.nodak.edu/~uuid/mypage.jsp

To use the database functions defined in your bean, you need to use a special JSP directive. 

You can import packages using the syntax:
<code>
<%@page import="java.util.*"%>
</code>
This imports java.util.*

To import your bean, use the syntax:
<code>
<jsp:useBean id="dbcon" scope="request" class="beans.DBCon" />
</code>

This defines an object called 'dbcon'. It has a scope of 'request', meaning that the dbcon object is the same throughout the request (options for scope include 'page', 'request', 'session'). The attribute 'class' tells the server which class to use for this object.

This is similar to the java:
<code>
DBCon dbcon = new DBCon();
</code>

in that it makes a new object for code the use. The difference is that jsp:useBean is used for JSP pages to load Beans.

Here is a small JSP page that loads the DBCon bean and calls the function getRecords(). Note that we do not specify ANY database connection information here, that's taken care of in the DBCon bean. 

<code>
<html>
<head></head>
<body>

<%@page import="java.util.*"%>
<jsp:useBean id="dbcon" scope="request" class="beans.DBCon" />

<%
        // Get the first item of each record by calling a function of the DBCon class
        ArrayList records = dbcon.getRecords();

        // Go through each record and print
        for(int i = 0; i < records.size(); i++)
        {
                // Print out each item in the 'records' array
                out.println(" item: " + (String)records.get(i));
                out.println("<br/>");
        }

%>
</body>
</html>
</code>