Once you have successfully installed and deployed the PDK-Java samples, you may want to run the Feedback Portlet that comes with the Sample Provider. This JSP portlet accepts feedback from the user and inserts into the database. Before you can execute this portlet, you need to run the script that sets up the database user and table required to make this portlet functional. You also need to modify the JSP code to point to your Host Name, Port Number and Oracle SID for the JDBC connection to work.
This article describes how to run the Feedback Portlet by specifying JDBC connection details in this JSP implementation. This could be useful when you need to build other portlets that need to talk to the database through JDBC connections.
You have installed the samples downloaded with PDK-Java and understand the steps required to display a Web portlet on an Oracle Portal page. For more information on installing the sample, please review the article Installing the PDK-Java Framework and Samples.
You are using the Oracle HTTP Server to execute and display your servlets used by the JPDK.
This section describes how to run the SQL script provided, to create the user and table required by the Feedback Portlet.
Change to the \feedback directory located under your JPDK installation directory (e.g. C:\jpdk\htdocs\feedback\). Find the script named insfbtab.sql -- this script creates the user and table required by the Feedback Portlet.
create user feedback identified by feedback;
alter user feedback default tablespace users;
alter user feedback temporary tablespace temp;
grant connect, resource to feedback;
connect feedback/feedback;
show user;
create table feedback_details (
CUSTOMER_NAME VARCHAR2(30),
COMPANY_NAME VARCHAR2(30),
FEEDBACK_TEXT VARCHAR2(500)
);
Login to SQLPLUS, enter: sqlplus sys/change_on_install
Run insfbtab.sql, enter: @insfbtab.sql
Exit SQLPLUS.
Change to the \feedback directory located under \htdocs of the Oracle HTTP Server where files were copied during the JPDK installation process (e.g. D:\9iAS\Apache\Apache\htdocs\jpdk\feedback\). Find the file named feedback.jsp.
<%@ page import="oracle.portal.provider.v1.*,oracle.portal.provider.v1.http.*,java.sql.*" %>
<%
// The form submit URL refers to the current Portal page. All portlets
// on this page share this URL. This means that the per portlet parameters
// are in the same request. Portlets must ensure that its parameters don't
// collide either with other portlets or other instances of itself. This
// is generally accomplished by using "fully-qualified" parameter names. A
// fully-qualified parameter name prepends the (unique) portlet reference to
// the parameter. The JPDK provides a utility to accomplish this.
String custName = HttpPortletRendererUtil.portletParameter(request, "myCustName");
String companyName = HttpPortletRendererUtil.portletParameter(request, "myCompanyName");
String feedbackText = HttpPortletRendererUtil.portletParameter(request, "myFeedbackText");
String submitFeedback = HttpPortletRendererUtil.portletParameter(request, "mySubmitFeedback");
%>
This Feedback portlet accepts your valuable feedback. <p>
<p>
<form name="feedback" method="POST" action="<%= HttpPortletRendererUtil.htmlFormActionLink(request,PortletRendererUtil.PAGE_LINK) %>" >
<%= HttpPortletRendererUtil.htmlFormHiddenFields(request,PortletRendererUtil.PAGE_LINK) %>
<b>
<%
if (request.getParameter(submitFeedback) != null )
{
%>
<%= insertFeedback(request.getParameter(custName), request.getParameter(companyName), request.getParameter(feedbackText)) %>
<%
}
%>
</b>
<label> Customer Name: <input type="text" size= "30"name="<%= custName %>" value="" > </label> <br>
<label> Company Name: <input type="text" size= "30"name="<%= companyName %>" value="" > </label> <Br>
<b>Provide your feedback below:</b> <Br>
<textarea cols="42" rows=4 name="<%= feedbackText %>" value="" wrap=virtual > </textarea> <Br>
<input type= submitname="<%= submitFeedback %>" value="Submit your feedback" >
</form>
<%! private String insertFeedback(String custName, String compName, String fbText) throws SQLException {
...
}
%>
Scroll to the insertFeedback procedure where the DriverManager.getConnection method is called.
<%@ Page import="oracle.portal.provider.v1.*,oracle.portal.provider.v1.http.*,java.sql.*" %>
...
</form>
<%! private String insertFeedback(String custName, String compName, String fbText) throws SQLException {
Connection conn = null;
PreparedStatement stmt = null;
String retStr = "<P> Feedback not saved! </P> \n";
try {
// Load the Driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// Get a Thin JDBC Connection. You need to change the code to point
// to your host name, port number, and Oracle SID. The
// username/password created by the SQL script is feedback/feedback.
conn = DriverManager.getConnection("jdbc:oracle:thin:@ncnc-sun8.us.oracle.com:1521:ORA816", "feedback", "feedback");
// To improve performance, don't commit automatically
conn.setAutoCommit(false);
// Prepare the INSERT statement for the FEEDBACK_DETAILS table
stmt = conn.prepareStatement("INSERT INTO FEEDBACK_DETAILS( CUSTOMER_NAME, COMPANY_NAME, FEEDBACK_TEXT) values (?, ?, ? ) " );
stmt.setString(1, custName );
stmt.setString(2, compName );
stmt.setString(3, fbText );
// Execute the Prepared statement to check number of rows inserted
if (stmt.executeUpdate() == 1) {
retStr = "<P> Feedback saved! </P> \n";
}
} catch (SQLException e ) {
retStr = "<P> SQL Error: <PRE> " + e + " </PRE> </P> \n";
} finally {
if (stmt != null) stmt.close();
if (conn != null) conn.close();
return(retStr);
}
}
%>
Change the JDBC connection specification to point to your Host Name, Port Number and Oracle SID. For example:
conn = DriverManager.getConnection("jdbc:oracle:thin:@myHost.us.oracle.com:1521:mySID", "feedback", "feedback")
Save the file feedback.jsp.
Verify that this saved file is under the htdocs area of the Oracle HTTP or iAS Server you're using. (e.g. D:\9iAS\Apache\Apache\htdocs\jpdk\feedback\)
Login to Oracle9iAS Portal and verify that the Feedback Portlet shows up along with the other portlets provided by the Sample Provider you installed with the PDK-Java in the Portlet Repository.
Create a page with the Feedback Portlet.
Enter details in the portlet and click "Submit your feedback".
Check that the values are correctly inserted in the database.
Login to the database, enter: sqlplus feedback/feedback
Check the data in the table FEEDBACK_DETAILS, enter: select * from feedback_details
You should see the data you entered into this table. If you do not see the data, check the JDBC connection details you provided earlier. You will also see the SQL error message show up on the portlet itself. Fix the problem and try again until you can see the data in the table.
You have now successfully run the Feedback Portlet.
Now that you have successfully executed this Feedback Portlet, you can use a similar approach when coding and running other Java portlets that interact with the database. To learn more about how Java programmers can conveniently access relational data, please refer to further documentation on JDBC and SQLJ.
Revision History:
July, 2001