This is an old revision of the document!

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.

<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>