1 / 30

Database Processing with JSP

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;.

zody
Download Presentation

Database Processing with JSP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Processing with JSP ISYS 350

  2. Example: Enter CID in a box and retrieve the customer record

  3. Create a CID ListBox

  4. SQL Select Command Select all records from a table: SELECT * FROM tableName; Select records meeting criteria: SELECT * FROM tableName WHERE criteria;

  5. 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 + "'";

  6. SQL Insert Command INSERT 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, SalesPerson INSERT INTO ORDERS VALUES (‘O11’, ‘9/28/02’, ‘C1’, ‘Peter’);

  7. 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 + "')";

  8. Creating a Java DB (Derby) Database with NetBeans • Tutorial: • http://netbeans.org/kb/docs/ide/java-db.html#starting

  9. 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.

  10. 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

  11. 2. Connect to the database: Right click database name

  12. Connected Database

  13. 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

  14. 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

  15. 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:

  16. 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";

  17. 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 + "'"; • ResultSetrs = 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 + "')"; intCount; Count=SQLStatement.executeUpdate(strSQL);

  18. 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.

  19. 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()

  20. Enter CID: HTML Form <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>

  21. Jdbc:derby demo: Accessing a Derby database CRM5 with username and password. Note: Using Derby, SQL statement cannot end with “;” <% 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 + "'"; ResultSetrs = 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()); } %>

  22. Derby demo:Populate a Listbox using a ResultSet <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"; ResultSetrs = 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>

  23. 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"; ResultSetrs = 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>

  24. 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"; ResultSetrs = 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>"); %>

  25. 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>

  26. 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()); } %>

  27. 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);

  28. Example: <% String CID="C1"; Cookie cookieCID = new Cookie ("cookieCID",CID); response.addCookie(cookieCID); out.println("CID cookie= " + CID + "added"); %>

  29. Reading Cookies Use request.getCookies() method. This method retrieve cookies and return them in an array. <% Cookie[] cookies = request.getCookies(); out.println(cookies[0].getName() + cookies[0].getValue() + "<br>"); %>

  30. 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 + "'"; ResultSetrs = 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()); } %>

More Related