Database Processing with JSP ISYS 350 Database Applications

Database Processing with JSP ISYS 350

Database Applications Queries/Updates Applications Database Server Results

Database: Customer table Fields: CID, Cname, City, Rating Example: Enter CID in a box and retrieve the customer record

Create a CID List. Box

SQL Select Command 1. Select all records from a table: SELECT * FROM table. Name; Ex. SELECT * FROM customer; 2. Select records meeting criteria: SELECT * FROM table. Name WHERE criteria; Ex. SELECT * FROM customer WHERE city=‘SF’; 3. Select one field from a table: Ex, CID of the Customer table SELECT CID FROM CUSTOMER; 3. Select a few fields from a table: Ex, CID, Cname of the Customer table Ex. SELECT CID, Cname FROM customer;

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 my. CIDS, str. SQL; my. Cid=request. get. Parameter("cid"); str. SQL="select * from customer where cid='" + my. Cid + "'";

Creating a Java DB (Derby) Database with Net. Beans • Tutorial: – http: //netbeans. org/kb/docs/ide/javadb. html#starting

1. Creating a Database: Click Services tab; open databases node; 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 table 2. Enter Table name 3. 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: Note: Click Add Row to continue adding new row; click OK after entering the last row.

Database Processing with JSP • Java Database Connectivity (JDBC) is an application program interface (API) specification for connecting programs written in Java to the data in popular databases. • Must import java. sql using page directive: – <%@page import="java. sql. *" %> • Need a database driver: – Java Derby database: • jdbc: derby

Define a connection string • A connection string is a string that specifies information about a data source and the means of connecting to it. It is passed in code to an underlying driver or provider in order to initiate the connection. • Example: – DBUrl="jdbc: derby: //localhost: 1527/CRM";

Define Database Objects to Run SQL Select Command • Define a connection object using the connection string: – No password: • connection = Driver. Manager. get. Connection(DBUrl); – With password: • connection = Driver. Manager. get. Connection(DBUrl, "dchao"); • Define a SQL Statement object: – Statement SQLStatement = connection. create. Statement(); • SQL Statement object’s execute. Query method: – execute. Query: This method executes SQL Select statement and create a resultset object: – Example: • str. SQL="select * from customer where cid='" + my. Cid + "'"; • Result. Set rs = SQLStatement. execute. Query(str. SQL);

JDBC Result. Set • 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.

Result. Set 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()

Example 1: Result set contains at most one record or no record (selecting record based on primary key) <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859 -1"> <title>Insert title here</title> </head> <body> <form action="get. Customer. jsp" method="post"> <p>Enter CID: <input name="mycid" type="text" size="20" maxlength="20" ></p> <input type="submit" value="Submit"> </form></body> </html>

Jdbc: derby demo: Accessing database CRMxx with no username and password <% Connection connection = null; String DBUrl="jdbc: derby: //localhost: 1527/CRMxx"; try { String my. Cid, str. SQL, Cname, City, Rating; connection = Driver. Manager. get. Connection(DBUrl); Statement SQLStatement = connection. create. Statement(); my. Cid=request. get. Parameter("mycid"); str. SQL="select * from customer where cid='" + my. Cid + "'"; Result. Set rs = SQLStatement. execute. Query(str. SQL); if (rs. next()) { Cname=rs. get. String("CNAME"); City=rs. get. String("CITY"); Rating=rs. get. String("Rating"); rs. close(); out. println("Cname: <input type='text' name='name' size='20' value='" + Cname + "' /> "); out. println("City: <input type='text' name='city' size='20' value='" + City+ "' /> "); out. println("Rating: <input type='text' name='rating' size='3' value='" + Rating + "' /> "); } else { out. println("Customer not exist!"); } rs. close(); } catch(SQLException e) { out. println("Something wrong"); out. println(e. get. Message()); } %>

<% Connection connection = null; String DBUrl="jdbc: derby: //localhost: 1527/CRM 5"; try { String my. Cid, str. SQL, Cname, City, Rating; connection = Driver. Manager. get. Connection(DBUrl, "dchao"); Statement SQLStatement = connection. create. Statement(); my. Cid=request. get. Parameter("mycid"); str. SQL="select * from customer where cid='" + my. Cid + "'"; Result. Set rs = SQLStatement. execute. Query(str. SQL); if (rs. next()) { Cname=rs. get. String("CNAME"); City=rs. get. String("CITY"); Rating=rs. get. String("Rating"); rs. close(); out. println("Cname: <input type='text' name='name' size='20' value='" + Cname + "' /> "); out. println("City: <input type='text' name='city' size='20' value='" + City+ "' /> "); out. println("Rating: <input type='text' name='rating' size='3' value='" + Rating + "' /> "); } else { out. println("Customer not exist!"); } rs. close(); } catch(SQLException e) { out. println("Something wrong"); out. println(e. get. Message()); } %>

Jdbc: derby demo. Using JSPexpression <% Connection connection = null; String DBUrl="jdbc: derby: //localhost: 1527/CRM 5"; try { String my. Cid, str. SQL, Cname, City, Rating; connection = Driver. Manager. get. Connection(DBUrl, "dchao"); Statement SQLStatement = connection. create. Statement(); my. Cid=request. get. Parameter("mycid"); str. SQL="select * from customer where cid='" + my. Cid + "'"; Result. Set rs = SQLStatement. execute. Query(str. SQL); if (rs. next()) { Cname=rs. get. String("CNAME"); City=rs. get. String("CITY"); Rating=rs. get. String("Rating"); rs. close(); %> Cname: <input type="text" name="name" size="20" value="<%=Cname%>"> City: <input type="text" name="city" size="20" value="<%=City%>"> Rating: <input type="text" name="rating" size="3" value="<%=Rating%>"> <% } else { out. println("Customer not exist!"); rs. close(); } } catch(SQLException e) { out. println("Something wrong"); out. println(e. get. Message()); } %>

Example 2: Result set contains many keys Populate a Listbox using a Result. Set <form name="cid. Form" method ="post" action="get. Customer. jsp"> Select CID: <select name="mycid"> <% Connection connection = null; String DBUrl="jdbc: derby: //localhost: 1527/CRMxx"; try { String my. Cid, str. SQL; connection = Driver. Manager. get. Connection(DBUrl); Statement SQLStatement = connection. create. Statement(); str. SQL="select cid from customer"; Result. Set rs = SQLStatement. execute. Query(str. SQL); while (rs. next()) { my. Cid=rs. get. String("cid"); out. println("<option value='" +my. Cid + "'>" + my. Cid + "</option>"); } } catch(SQLException e) { out. println(e. get. Message()); } %> </select> <input type="submit" value="Get. Customer. Data" name="btn. Submit" /> </form>

Example 3: Result set contains many records Show Records in a Table <% Connection connection = null; String DBUrl="jdbc: derby: //localhost: 1527/CRMxx"; try { String Cid, str. SQL, Cname, City, Rating; connection = Driver. Manager. get. Connection(DBUrl); Statement SQLStatement = connection. create. Statement(); str. SQL="select * from customer"; Result. Set rs = SQLStatement. execute. Query(str. SQL); 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. get. String("CID"); Cname=rs. get. String("CNAME"); City=rs. get. String("CITY"); Rating=rs. get. String("Rating"); out. println("<tr>"); out. println("<td>" + Cid + "</td>"); out. println("<td>" + Cname + "</td>"); out. println("<td>" + City + "</td>"); out. println("<td>" + Rating + "</td>"); out. println("</tr>"); } rs. close(); } catch(SQLException e) { out. println(e. get. Message()); } out. println("</table>"); %>

Derby demo: Show Records in a Table using JSP Expression <% Connection connection = null; String DBUrl="jdbc: derby: //localhost: 1527/CRM 5"; try { String Cid, str. SQL, Cname, City, Rating; connection = Driver. Manager. get. Connection(DBUrl, "dchao"); Statement SQLStatement = connection. create. Statement(); str. SQL="select * from customer"; Result. Set rs = SQLStatement. execute. Query(str. SQL); %> <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. get. String("CID"); Cname=rs. get. String("CNAME"); City=rs. get. String("CITY"); Rating=rs. get. String("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. get. Message()); } %> </table>

New Customer Data Entry <form name="new. Form" action="add. New. Customer. jsp" method="POST"> Customer Data Entry Form Enter CID: <input type="text" name="mycid" value="" /> Enter Name: <input type="text" name="cname" value="" /> Enter City: <input type="text" name="city" value="" /> Enter rating: <input type="text" name="rating" value="" /> <input type="submit" value="Add New Customer" name="btn. Submit" /> </form>

SQL Insert Command INSERT INTO table. Name VALUES (field values separated by commas); Ex 1. Customer table with CID, CNAME, CITY, RATING. INSERT INTO CUSTOMER VALUES (‘C 1’, ‘SMITH’, ‘SF’, ‘A’); Ex 2. Orders table with OID, Order. Date, CID, Sales. Person INSERT INTO ORDERS VALUES (‘O 11’, ‘ 9/28/02’, ‘C 1’, ‘Peter’);

Creating A String Containing SQL Insert Command my. Cid=request. get. Parameter("cid"); my. Cname=request. get. Parameter("cname"); my. City=request. get. Parameter("city"); my. Rating=request. get. Parameter("rating"); str. SQL = "Insert into Customer values ('"; str. SQL += my. Cid + "', '" + my. Cname + "', '"; str. SQL += my. City + "', '" + my. Rating + "')";

SQL Statement Object’s execute. Update Method to Run SQL Insert Statement • execute. Update: This method executes SQL Insert, Delete and Update statement and returns the number of records affected by the statement. • Example: • str. SQL = "Insert into Customer values ('"; • str. SQL += my. Cid + "', '" + my. Cname + "', '"; • str. SQL += my. City + "', '" + my. Rating + "')"; • int Count; • Count=SQLStatement. execute. Update(str. SQL);

<% try { Insert a New Record Connection connection = null; String DBUrl="jdbc: derby: //localhost: 1527/CRMxx"; String my. Cid, str. SQL, my. Cname, my. City, my. Rating; connection = Driver. Manager. get. Connection(DBUrl); %> Statement SQLStatement = connection. create. Statement(); my. Cid=request. get. Parameter("mycid"); my. Cname=request. get. Parameter("cname"); my. City=request. get. Parameter("city"); my. Rating=request. get. Parameter("rating"); str. SQL = "Insert into Customer values ('"; str. SQL += my. Cid + "', '" + my. Cname + "', '"; str. SQL += my. City + "', '" + my. Rating + "')"; int Count; Count=SQLStatement. execute. Update(str. SQL); if (Count==1) out. println("Insertion sucessful"); } catch(SQLException e) { out. println(e. get. Message()); }

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 cookie. CID = new Cookie ("cookie. CID", CID); • Write a cookie: – response. add. Cookie(cookie. CID);

Example: <% String CID="C 1"; Cookie cookie. CID = new Cookie ("cookie. CID", CID); response. add. Cookie(cookie. CID); out. println("CID cookie= " + CID + "added"); %>

Reading Cookies Use request. get. Cookies() method. This method retrieve cookies and return them in an array. <% Cookie[] cookies = request. get. Cookies(); out. println(cookies[0]. get. Name() + cookies[0]. get. Value() + " "); %>

Reading Cookie and Customer Record <% try{ String my. Cid, str. SQL, Cname, City, Rating; Cookie[] cookies = request. get. Cookies(); my. Cid=cookies[0]. get. Value(); Connection connection = null; String DBUrl="jdbc: derby: //localhost: 1527/CRMxx"; connection = Driver. Manager. get. Connection(DBUrl); Statement SQLStatement = connection. create. Statement(); str. SQL="select * from customer where cid='" + my. Cid + "'"; Result. Set rs = SQLStatement. execute. Query(str. SQL); if (rs. next()) { Cname=rs. get. String("CNAME"); City=rs. get. String("CITY"); Rating=rs. get. String("Rating"); rs. close(); %> Cname: <input type="text" name="name" size="20" value="<%=Cname%>"> City: <input type="text" name="city" size="20" value="<%=City%>"> Rating: <input type="text" name="rating" size="3" value="<%=Rating%>"> <% } else { out. println("Customer not exist!"); rs. close(); } } catch(SQLException e) { out. println(e. get. Message()); } %>