JSP Tutorial
By Emmanuel MASABO
Three-Tier Architecture
Located @ Any PC HTTP Requests Located @ Your PC Microsoft Internet Explorer HTML
Glassfish Server Java Server Pages (JSPs) JDBC Requests Tuples
Located @ DBLab
Oracle DB Server
Data Entry Forms
JDBC
import java.sql.*; class JdbcTest { public static void main (String args []) throws SQLException { try{ // Connect to the local database Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/student s_db","root",""); // Query the student first names Statement stmt = conn.createStatement(); ResultSet rset = stmt.executeQuery ("SELECT * FROM Students");
// Print the first name out //firstname is the 3rd attribute of Student while (rset.next ()) System.out.println(rset.getString(3)); //close the result set, statement, and the connection rset.close(); stmt.close(); conn.close(); } catch(SQLException err){ System.out.println(err.getMessage()); } } }
PreparedStatement Object If you want to execute a Statement object many times, it will normally reduce execution time to use a PreparedStatement object instead. PreparedStatement updateStud = conn.prepareStatement( "UPDATE Students SET first_name = ? WHERE last_name LIKE ?"); updateStud.setString(1, John); updateStud.setString(2, Smith); updateStud.executeUpdate();
PreparedStatement Object the following two code fragments accomplish the same thing: Code Fragment 1: String updateString = "UPDATE COFFEES SET SALES = 75 " + "WHERE COF_NAME LIKE 'Colombian'"; stmt.executeUpdate(updateString); Code Fragment 2: PreparedStatement updateSales = con.prepareStatement( "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? "); updateSales.setInt(1, 75); updateSales.setString(2, "Colombian"); updateSales.executeUpdate():
int getInt(int columnIndex) Retrieves the value of the designated column in the current row of this ResultSet object as an int in the Java programming language. int getInt(String columnName)
String getString(int columnIndex)
String getString(String columnName)
Using Transactions
When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and will be automatically committed right after it is executed.
conn.setAutoCommit(false); .... transaction ... con.commit(); con.setAutoCommit(true);
Using Transactions
example
con.setAutoCommit(false); PreparedStatement updateSales = con.prepareStatement( "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?"); updateSales.setInt(1, 50); updateSales.setString(2, "Colombian"); updateSales.executeUpdate(); PreparedStatement updateTotal = con.prepareStatement( "UPDATE COFFEES SET TOTAL = TOTAL + ? WHERE COF_NAME LIKE ?"); updateTotal.setInt(1, 50); updateTotal.setString(2, "Colombian"); updateTotal.executeUpdate(); con.commit(); con.setAutoCommit(true);
Retrieving Exceptions
JDBC lets you see the warnings and exceptions generated by your DBMS and by the Java compiler. To see exceptions, you can have a catch block print them out. For example, the following two catch blocks from the sample code print out a message explaining the exception:
try { // Code that could generate an exception goes here. // If an exception is generated, the catch block below // will print out information about it. } catch(SQLException ex) { System.err.println("SQLException: " + ex.getMessage()); }
JSP Syntax
Comment
<%-- Comment --%>
Expression
<%= java expression %>
Scriplet
<% java code fragment %>
Include
<jsp:include page="relativeURL" />
Entry Form - First Attempt
Entry Form - First Attempt
Menu HTML Code
<b>Data Entry Menu</b> <ul> <li> <a href=Courses.jsp">Courses<a> </li> <li> <a href=Classes.jsp">Classes<a> </li> <li> <a href=Students.jsp">Students<a> </li> </ul>
Entry Form - First Attempt
JSP Code
<html> <body> <table> <tr> <td> <jsp:include page="menu.html" /> </td> <td> Open connection code Statement code Presentation code Close connection code </td> </tr> </table> </body> </html>
Entry Form - First Attempt
Open Connectivity Code
<%-- Set the scripting language to java and --%> <%-- import the java.sql package --%> <%@ page language="java" import="java.sql.*" %> <%
try {
// Make a connection to the MySql datasource Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3 306/students_db","root",""); %>
NB:In the case we get no suitable driver message, we can use Class.forName("com.mysql.jdbc.Driver").newInstance(); before connection or we can go to services, drivers and add mysql driver. Dont forget to add the mysql connect driver also in the library folder.
Entry Form - First Attempt
Statement Code
<% // Create the statement Statement statement = conn.createStatement(); // Use the statement to SELECT the student attributes // FROM the Student table. ResultSet rs = statement.executeQuery ("SELECT * FROM Students"); %>
Entry Form - First Attempt
Presentation Code
<table> <tr> <th>SSN</th> <th>First</th> <th>Last</th> <th>College</th> </tr> <% // Iterate over the ResultSet while ( rs.next() ) { %> Iteration Code <% } %> </table>
Entry Form - First Attempt
Entry Form - First Attempt
Iteration Code
<tr> <%-- Get the SSN, which is a number --%> <td><%= rs.getInt("SSN") %></td> <%-- Get the ID --%> <td><%= rs.getString("ID") %></td> <%-- Get the FIRSTNAME --%> <td><%= rs.getString("FIRSTNAME") %></td> <%-- Get the LASTNAME --%> <td><%= rs.getString("LASTNAME") %></td> <%-- Get the COLLEGE --%> <td><%= rs.getString("COLLEGE") %></td> </tr>
Entry Form - First Attempt
Close Connectivity Code
<% // Close the ResultSet rs.close(); // Close the Statement statement.close(); // Close the Connection conn.close();
} catch (SQLException sqle) { out.println(sqle.getMessage()); } catch (Exception e) { out.println(e.getMessage()); } %>
Entry Form - Second Attempt
Entry Form - Second Attempt
JSP Code
<html> <body> <table> <tr> <td> Open connection code Insertion Code Statement code Presentation code Close connection code </td> </tr> </table> </body> </html>
Entry Form - Second Attempt
Insertion Code
// Check if an insertion is requested String action = request.getParameter("action"); if (action != null && action.equals("insert")) { conn.setAutoCommit(false); // Create the prepared statement and use it to // INSERT the student attrs INTO the Students table. PreparedStatement pstmt = conn.prepareStatement( ("INSERT INTO Students VALUES (?, ?, ?, ?, ?)")); pstmt.setInt(1,Integer.parseInt(request.getParameter("SSN"))); pstmt.setString(2, request.getParameter("ID")); pstmt.setString(3,request.getParameter("FIRSTNAME")); pstmt.setString(4,request.getParameter("LASTNAME")); pstmt.setString(5,request.getParameter("COLLEGE")); pstmt.executeUpdate(); conn.commit(); conn.setAutoCommit(true); }
Entry Form - Second Attempt
Presentation Code
<table> <tr> <th>SSN</th> <th>First</th> <th>Last</th> <th>College</th> </tr> Insert Form Code <% // Iterate over the ResultSet while ( rs.next() ) { %> Iteration Code <% } %> </table>
Entry Form - Second Attempt
Insert Form Code
<tr> <form action="students.jsp" method="get"> <input type="hidden" value="insert" name="action"> <th><input value="" name="SSN" size="10"></th> <th><input value="" name="ID" size="10"></th> <th><input value="" name="FIRSTNAME" size="15"></th> <th><input value="" name="LASTNAME" size="15"></th> <th><input value="" name="COLLEGE" size="15"></th> <th><input type="submit" value="Insert"></th> </form> </tr>
Entry Form - Third Attempt
Entry Form - Third Attempt
JSP Code
<html> <body> <table> <tr> <td> Open connection code Insertion Code Update Code Delete Code Statement code Presentation code Close connection code </td> </tr> </table> </body> </html>
Entry Form - Third Attempt
Update Code
// Check if an update is requested if (action != null && action.equals("update")) { conn.setAutoCommit(false); // Create the prepared statement and use it to // UPDATE the student attributes in the Student table. PreparedStatement pstatement = conn.prepareStatement( "UPDATE Students SET ID = ?, FIRSTNAME = ?, " + "LASTNAME = ?, COLLEGE = ? WHERE SSN = ?"); pstatement.setString(1, request.getParameter("ID")); pstatement.setString(2, request.getParameter("FIRSTNAME")); pstatement.setString(3,request.getParameter("LASTNAME")); pstatement.setString(4,request.getParameter("COLLEGE")); pstatement.setString(5,request.getParameter("SSN")); int rowCount = pstatement.executeUpdate(); //conn.setAutoCommit(false); conn.setAutoCommit(true); }
Entry Form - Third Attempt
Delete Code
// Check if a delete is requested if (action != null && action.equals("delete")) { conn.setAutoCommit(false); // Create the prepared statement and use it to // DELETE the student FROM the Student table. PreparedStatement pstmt = conn.prepareStatement( "DELETE FROM Student WHERE SSN = ?"); pstmt.setInt(1, Integer.parseInt(request.getParameter("SSN"))); int rowCount = pstmt.executeUpdate(); //conn.setAutoCommit(false); conn.setAutoCommit(true); }
Entry Form - Third Attempt
Presentation Code
<table> <tr> <th>SSN</th> <th>First</th> <th>Last</th> <th>College</th> </tr> Insert Form Code <% // Iterate over the ResultSet while ( rs.next() ) { %> Iteration Code <% } %> </table>
Entry Form - Third Attempt
Iteration Code changed
<tr> <form action="students.jsp" method="get"> <input type="hidden" value="update" name="action"> <td><input value="<%= rs.getInt("SSN") %>" name="SSN"></td> <td><input value="<%= rs.getString("ID") %>" name="ID"></td> <td><input value=" <%= rs.getString("first_name") %>" name="FIRSTNAME"></td> <td><input value="<%= rs.getString("last_name") %>" name="LASTNAME"></td> <td><input value=" <%= rs.getString("college") %>" name="COLLEGE"></td> <td><input type="submit" value="Update"></td> </form> <form action="students2.jsp" method="get"> <input type="hidden" value="delete" name="action"> <input type="hidden" value="<%= rs.getInt("SSN") %>" name="SSN"> <td><input type="submit" value="Delete"></td> </form> </tr>
