Servlets and Data Bases

Transcript Servlets and Data Bases

DataBases and
SQL
INFSY 547
Spring 2007
Course Wrap Up
• April 12: Complete Work on Servlets
Review of Team Projects
Close of Portfolio Work
• April 19: Review Form ideas, Team
Meetings and presentation
guidelines
• April 26: Project Due Date
Develop presentations
• May 3: Final Presentation and Party
Servlet Processing
 Process a database (similar to an


.xml file)
Output an .html file
Interact with Oracle via a servlet
In SQL Plus
Lab 10: Start with Eclipse
 Create a project in Eclipse –
(Infsy547OracleCustomerLab.java)
 External .jar files
• classes12.jar
• servlet-api.jar
Lab 10: Start with Eclipse
 Create a package – infsy547oraclecustomerlab
 Create a class – by the same name as the project
 Add import statements
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
Servlet Assuming
Names Table
public class Infsy547OracleCustomer extends HttpServlet
{
private Connection connection;
private PreparedStatement insertRecord;
private int acctNumber;
<add string name, address, city, state>
private PrintWriter out;
Names
acctNumber*
name address city
state
public void init( ServletConfig config ) throws
ServletException
{
<call getConnected method>
}
private void getConnected ()
{
Loads the driver for Oracle
try
{
Class.forName( "oracle.jdbc.driver.OracleDriver" );
connection = DriverManager.getConnection
("jdbc:oracle:thin: @146.186.84.66:1521:CLDB", “<userid>",“<psword>");
// PreparedStatement to add a customer record to names table
insertRecord = connection.prepareStatement (
"INSERT INTO names (name, address, city, state, accountnumber) " +
"VALUES(?, ?, ?, ?, ?)");
}
catch(ClassNotFoundException cnf) {
System.err.println(cnf.getMessage());
cnf.printStackTrace(); }
catch (SQLException sqlex) {
System.err.println(sqlex.getMessage());
sqlex.printStackTrace(); }
}
protected void doPost( HttpServletRequest request, HttpServletResponse response
throws ServletException, IOException {
// set up response to client
response.setContentType( "text/html" );
out = response.getWriter();
// start XHTML document
out.println( "<?xml version = \"1.0\"?>" );
out.println( "<!DOCTYPE html PUBLIC \"-//W3C//DTD " +
"XHTML 1.0 Strict//EN\" \"http://www.w3.org" +
"/TR/xhtml1/DTD/xhtml1-strict.dtd\">" );
out.println("<html xmlns = \"http://www.w3.org/1999/xhtml\">" );
// head section of document
out.println( "<head>" );
acctNumber = Integer.parseInt(request.getParameter("accountNumber"));
name = request.getParameter("Name");
<write code to request the other necessary parameters>
<call a method, insertRecord>
out.println( "<title>Thank you!</title>" );
out.println( "</head>" );
out.println( "<body>" );
out.println( "<p>Thank you for adding your record." );
out.println( "</pre></body></html>" );
out.close(); }
public void insertRecord ()
{
try
{
// update total for current survey response
insertRecord.setString( 1, name );
insertRecord.setString( 2, address );
<insert the city and state>
Note: Will not
insertRecord.setInt( 5, acctNumber);
complete the
insertRecord.executeUpdate();
html on previous
}
page if this
catch ( SQLException sqlException )
occurs.
{
sqlException.printStackTrace();
out.println( "<title>Error</title>" );
out.println( "</head>" );
out.println( "<body><p>Database error occurred. " );
out.println( "Try again later.</p></body></html>" );
out.close();
}
}
@Override
public void destroy()
{
// attempt to close statements and database connection
try
{
insertRecord.close();
connection.close();
}
catch( SQLException sqlException )
{
sqlException.printStackTrace();
}
} // end of destroy method
Exception Terminology
● Exceptions represent error conditions
● Exceptions isolate the code that deals with error
situations
● Throwing: the act of detecting an abnormal condition
Searches for a handler
catches the exception
● Try Block: Encloses one or more java statements
HTML
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Insert Customer</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<?xml version="1.0" encoding="iso-8859-1"?>
<form method = "post" action = "servlet/Infsy547OracleCustomerLab">
<label>Account Number</label>
<input name="accountNumber" type="text"><br><br>
<do the same for the remainder of the variables>
<br/><br/>
<input name="Save" type="submit" value="Save">
</form>
</body>
</html>
Complete
• Place files appropriately in TomCat
structure
• Add to web.xml
• Start TomCat
• Start you servlet
• .java file from this lab
– uploaded to the DataBase
Connectivity Drop Box
– In ANGEL under the Lessons Tab
• .html file
– Upload into HTML Drop Box

Servlets and Data Bases

Transcript Servlets and Data Bases

Directory