database processing with jsp
DESCRIPTION
Database Processing with JSP. ISYS 350. Example: Enter CID in a box and retrieve the customer record. Create a CID ListBox. SQL Select Command. Select all records from a table: SELECT * FROM tableName ; Select records meeting criteria: SELECT * FROM tableName WHERE criteria;. - PowerPoint PPT PresentationTRANSCRIPT
Database Processing with JSP
ISYS 350
Example: Enter CID in a box and retrieve the customer record
Create a CID ListBox
SQL Select Command
Select all records from a table:
SELECT * FROM tableName;
Select records meeting criteria:
SELECT * FROM tableName WHERE criteria;
Creating a String Containing SQL Select Command
•Assuming the CID is entered in a HTML textbox named “cid” or selected from a HTML listbox named “cid”:
String myCIDS, strSQL; myCid=request.getParameter("cid"); strSQL="select * from customer where cid='" + myCid + "'";
SQL Insert CommandINSERT INTO tableName VALUES (field values separated by commas);Ex 1. Customer table with CID, CNAME, CITY, RATING.INSERT INTO CUSTOMER VALUES (‘C1’, ‘SMITH’, ‘SF’, ‘A’);
Ex 2. Orders table with OID, OrderDate, CID, SalesPersonINSERT INTO ORDERS VALUES (‘O11’, ‘9/28/02’, ‘C1’, ‘Peter’);
Creating A String Containing SQL Insert Command
myCid=request.getParameter("cid");myCname=request.getParameter("cname");myCity=request.getParameter("city");myRating=request.getParameter("rating");strSQL = "Insert into Customer values ('";strSQL += myCid + "','" + myCname + "','";strSQL += myCity + "','" + myRating + "')";
Creating a Java DB (Derby) Database with NetBeans
• Tutorial:– http://
netbeans.org/kb/docs/ide/java-db.html#starting
1. Creating a Database: Click Services tab; Right click Java DB and select Start Server; then select Create Database
Note: You may leave username and password blank or assign a name and password.
Difference between with Username and No username
• No username: Create table in the App folder• With username: create table in the default
username’s folder
2. Connect to the database: Right click database name
Connected Database
3. Creating a New Table: Open the App folder, and:1. Right click Tables and select create table2. Enter Table name3. Click Add column to define field
Create a New Table
• Table name: Customer• Fields:
– CID: Type – CHAR with size 5; primary key– Cname: Type – CHAR with size 20– City:Type – CHAR with size 20– Rating: Type – CHAR with size 1
To insert records to a table:
• Right-click the table name and select View Data• From the record view window, click the Insert
Record button:
Database Processing with JSP
• Must import java.sql using page directive:– <%@page import="java.sql.*" %>
• Need a driver:– Java Derby database:– Example:
• String DBUrl="jdbc:derby://localhost:1527/CRM";
Define Database Objects• Define connection object:
– No password: connection = DriverManager.getConnection(DBUrl);– With password:
• connection = DriverManager.getConnection(DBUrl,"dchao","dchao");
• Define SQL Statement object:– Statement SQLStatement = connection.createStatement();
• SQL Statement methods:– 1. executeQuery: This method executes SQL Select statement and create a
resultset object:– Example:
• strSQL="select * from customer where cid='" + myCid + "'";• ResultSet rs = SQLStatement.executeQuery(strSQL);
– 2. executeUpdate: This method executes SQL Insert, Delete and Update statement and returns the number of records affected by the statement.
– Example:strSQL = "Insert into Customer values ('";strSQL += myCid + "','" + myCname + "','";strSQL += myCity + "','" + myRating + "')";int Count;Count=SQLStatement.executeUpdate(strSQL);
JDBC ResultSet
• The rows that satisfy a particular query are called the result set. The number of rows returned in a result set can be zero or more. A user can access the data in a result set using a cursor one row at a time from top to bottom. A cursor can be thought of as a pointer to the rows of the result set that has the ability to keep track of which row is currently being accessed.
ResultSet Methods
• Next() : Returns true if the cursor is now positioned on a row and false if the cursor is positioned after the last row.
• previous() • first()• last()
HTML FormEnter CID:
<html><head><meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><title>Insert title here</title></head><body>
<form action="getCustomer.jsp" method="post"><p>Enter CID: <input name="mycid" type="text" size="20" maxlength="20" ></p>
<input type="submit" value="Submit"></form></body></html>
<%Connection connection = null;String DBUrl="jdbc:derby://localhost:1527/CRM5";try{String myCid, strSQL, Cname, City, Rating;connection = DriverManager.getConnection(DBUrl,"dchao","dchao");Statement SQLStatement = connection.createStatement();myCid=request.getParameter("mycid");strSQL="select * from customer where cid='" + myCid + "'";ResultSet rs = SQLStatement.executeQuery(strSQL);if (rs.next())
{Cname=rs.getString("CNAME");City=rs.getString("CITY");Rating=rs.getString("Rating");rs.close();
%> Cname: <input type="text" name="name" size="20" value="<%=Cname%>"><br> City: <input type="text" name="city" size="20" value="<%=City%>"><br> Rating: <input type="text" name="rating" size="3" value="<%=Rating%>"><br> <%
}else
{out.println("Customer not exist!");rs.close();
}}catch(SQLException e)
{out.println("Something wrong");
out.println(e.getMessage());}
%>
Jdbc:derby demo: Accessing a Derby database CRM5 with username and password. Note: Using Derby, SQL statement cannot end with “;”
<form name="cidForm" method ="post" action="getCustomer.jsp"> Select CID:<select name="mycid"> <% Connection connection = null;
String DBUrl="jdbc:derby://localhost:1527/CRM5";try{
String myCid, strSQL; connection = DriverManager.getConnection(DBUrl,"dchao","dchao"); Statement SQLStatement = connection.createStatement(); strSQL="select cid from customer"; ResultSet rs = SQLStatement.executeQuery(strSQL); while (rs.next()) { myCid=rs.getString("cid"); out.println("<option value='" +myCid + "'>" + myCid + "</option>"); } } catch(SQLException e)
{out.println(e.getMessage());
} %> </select> <input type="submit" value="GetCustomerData" name="btnSubmit" /> </form>
Derby demo:Populate a Listbox using a ResultSet
Derby demo:Show Records in a Table
<%Connection connection = null;String DBUrl="jdbc:derby://localhost:1527/CRM5";try{
String Cid, strSQL, Cname, City, Rating; connection = DriverManager.getConnection(DBUrl,"dchao","dchao");Statement SQLStatement = connection.createStatement();strSQL="select * from customer";ResultSet rs = SQLStatement.executeQuery(strSQL);
%> <table border='1' width='400' cellspacing=1> <thead> <tr> <th>CID</th> <th>Cname</th> <th>City</th> <th>Rating</th> </tr> </thead> <%
while (rs.next()) {
Cid=rs.getString("CID"); Cname=rs.getString("CNAME");
City=rs.getString("CITY");Rating=rs.getString("Rating");
%> <tr> <td width='25%'><%=Cid%></td> <td width='25%'><%=Cname%></td> <td width='25%'><%=City%></td> <td width='25%'><%=Rating%></td> </tr><% } rs.close();}catch(SQLException e)
{out.println(e.getMessage());
} %> </table>
Show Records in a Table using Out.print
<%Connection connection = null;String DBUrl="jdbc:derby://localhost:1527/CRM5";try{
String Cid, strSQL, Cname, City, Rating; connection = DriverManager.getConnection(DBUrl,"dchao","dchao");Statement SQLStatement = connection.createStatement();strSQL="select * from customer";ResultSet rs = SQLStatement.executeQuery(strSQL);
out.println("<table border='1' width='400' cellspacing=1>"); out.println(" <thead><tr>"); out.println("<th>CID</th> <th>Cname</th> <th>City</th> <th>Rating</th>"); out.println("</tr></thead>"); while (rs.next())
{Cid=rs.getString("CID");
Cname=rs.getString("CNAME");City=rs.getString("CITY");Rating=rs.getString("Rating");
out.println("<tr>"); out.println("<td width='25%'>" + Cid + "</td>"); out.println("<td width='25%'>" + Cname + "</td>"); out.println("<td width='25%'>" + City + "</td>"); out.println("<td width='25%'>" + Rating + "</td>"); out.println("</tr>"); } rs.close();}catch(SQLException e)
{out.println(e.getMessage());
} out.println("</table>"); %>
New Customer Data Entry
<form name="newForm" action="addNewCustomer.jsp" method="POST"> Customer Data Entry Form<br><br> Enter CID: <input type="text" name="mycid" value="" /><br><br> Enter Name: <input type="text" name="cname" value="" /><br><br> Enter City: <input type="text" name="city" value="" /><br><br> Enter rating: <input type="text" name="rating" value="" /><br><br> <input type="submit" value="Add New Customer" name="btnSubmit" /> </form>
Insert a New Record<% Connection connection = null; String DBUrl="jdbc:derby://localhost:1527/CRM5";try{ String myCid, strSQL, myCname, myCity, myRating;
connection = DriverManager.getConnection(DBUrl,"dchao","dchao");
Statement SQLStatement = connection.createStatement(); myCid=request.getParameter("mycid"); myCname=request.getParameter("cname"); myCity=request.getParameter("city"); myRating=request.getParameter("rating"); strSQL = "Insert into Customer values ('"; strSQL += myCid + "','" + myCname + "','"; strSQL += myCity + "','" + myRating + "')";
int Count; Count=SQLStatement.executeUpdate(strSQL); if (Count==1) out.println("Insertion sucessful");
}catch(SQLException e)
{out.println(e.getMessage());
} %>
Cookie
• Cookie is a small data file added by a website to reside in user’s system.
• Define a cookie:– new Cookie(“Key”, “value”);– Ex. Cookie cookieCID = new Cookie ("cookieCID",CID);
• Write a cookie: – response.addCookie(cookieCID);
Example: <% String CID="C1"; Cookie cookieCID = new Cookie ("cookieCID",CID); response.addCookie(cookieCID); out.println("CID cookie= " + CID + "added"); %>
Reading Cookies
<% Cookie[] cookies = request.getCookies(); out.println(cookies[0].getName() + cookies[0].getValue() + "<br>");%>
Use request.getCookies() method.This method retrieve cookies and return them in an array.
Reading Cookie and Customer Record <% try{ String myCid, strSQL, Cname, City, Rating; Cookie[] cookies = request.getCookies(); myCid=cookies[0].getValue(); Connection connection = null; String DBUrl="jdbc:derby://localhost:1527/CRM5"; connection = DriverManager.getConnection(DBUrl,"dchao","dchao"); Statement SQLStatement = connection.createStatement();
strSQL="select * from customer where cid='" + myCid + "'"; ResultSet rs = SQLStatement.executeQuery(strSQL); if (rs.next())
{Cname=rs.getString("CNAME");City=rs.getString("CITY");Rating=rs.getString("Rating");rs.close();
%>Cname: <input type="text" name="name" size="20" value="<%=Cname%>"><br>City: <input type="text" name="city" size="20" value="<%=City%>"><br>Rating: <input type="text" name="rating" size="3" value="<%=Rating%>"><br><%
}else
{out.println("Customer not exist!");rs.close();
} }catch(SQLException e)
{out.println(e.getMessage());
} %>