1 / 28

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

kay-jensen
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. Creating a Database: Right click Java DB and select Create Database Note: Username and password.

  10. Connect or Delete a database: Right click database name

  11. Connected Database

  12. Creating a New Table: 1.Right click Tables and select create table2. Enter Table name3. Click Add column to define field

  13. Database Processing with JSP • Must import java.sql using page directive: • <%@page import="java.sql.*" %> • Need a driver: • JDBC-ODBC Bridge: The JDBC-ODBC Bridge allows applications written in the Java programming language to use with many existing ODBC drivers. • Note: JDBC-ODBC does not work if you have a Windows 7 64-bit system and 32-bit MS Access. • Example: • String DBUrl="jdbc:odbc:MySalesDB"; • Java Derby database: • Example: • String DBUrl="jdbc:derby://localhost:1527/SalesDBJava";

  14. Define Database Objects • Define connection object: • connection = DriverManager.getConnection(DBUrl); • 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);

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

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

  17. 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="cid" type="text" size="20" maxlength="20" ></p> <input type="submit" value="Submit"> </form> </body> </html>

  18. Jdbc:odbc demo: A DSN MyCustomer is defined for a SQL Server database. <% Connection connection = null; String DBUrl="jdbc:odbc:MyCustomer"; try { String myCid, strSQL, Cname, City, Rating; connection = DriverManager.getConnection(DBUrl); Statement SQLStatement = connection.createStatement(); myCid=request.getParameter("cid"); 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(SQLExceptione) { out.println("Something wrong"); out.println(e.getMessage()); } %>

  19. Populate a Listbox using a ResultSet <form name="cidForm" method ="post" action="getCustomer.jsp"> <p>Select CID:<select name="cid"></p> <% Connection connection = null; String DBUrl="jdbc:odbc:MyCustomer"; try { String myCid, strSQL; connection = DriverManager.getConnection(DBUrl); 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>

  20. Show Records in a Table <% Connection connection = null; String DBUrl="jdbc:odbc:MyCustomer"; try { String Cid, strSQL, Cname, City, Rating; connection = DriverManager.getConnection(DBUrl); 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> <tbody> <% 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()); } %>

  21. Insert a New Record <% Connection connection = null; String DBUrl="jdbc:odbc:MyCustomer"; try { String myCid, strSQL, myCname, myCity, myRating; connection = DriverManager.getConnection(DBUrl); Statement SQLStatement = connection.createStatement(); 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 + "')"; intCount; Count=SQLStatement.executeUpdate(strSQL); if (Count==1) out.println("Insertion sucessful"); } catch(SQLException e) { out.println(e.getMessage()); } %>

  22. Jdbc:derby demo: Accessing a Derby database SalesDBJava with username and password. Note: Using Derby, SQL stastement cannot end with “;” <% Connection connection = null; String DBUrl="jdbc:derby://localhost:1527/SalesDBJava"; try { String myCid, strSQL, Cname, City, Rating; connection = DriverManager.getConnection(DBUrl,"dchao","dchao"); Statement SQLStatement = connection.createStatement(); myCid=request.getParameter("cid"); 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(SQLExceptione) { out.println("Something wrong"); out.println(e.getMessage()); } %>

  23. Derby demo:Populate a Listbox using a ResultSet <form name="cidForm" method ="post" action="getCustomer.jsp"> <p>Select CID:<select name="cid"></p> <% Connection connection = null; String DBUrl="jdbc:derby://localhost:1527/SalesDBJava"; 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>

  24. Derby demo:Show Records in a Table <% Connection connection = null; String DBUrl="jdbc:derby://localhost:1527/SalesDBJava"; 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> <tbody> <% 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()); } %>

  25. Jdbc:odbc: 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:odbc:MyCustomer"; connection = DriverManager.getConnection(DBUrl); 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()); } %>

  26. Show Customer/Orders • 1. A HTML form with CID listbox • 2. Show selected customer’s record and related records.

  27. <% Connection connection = null; String DBUrl="jdbc:odbc:MyCustomer"; try { String myCid, strSQL, Cname, City, Rating; String OID, SalesPerson; Date OrderDate; connection = DriverManager.getConnection(DBUrl); Statement SQLStatement = connection.createStatement(); myCid=request.getParameter("cid"); 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><br>

  28. <table border='1' width='400' cellspacing=1> <thead> <tr> <th>OID</th> <th>ODate</th> <th>SalesPerson</th> </thead> <tbody> <% Statement SQLStatementOrder = connection.createStatement(); strSQL="select * from orders where cid='" + myCid + "'"; ResultSetrsOrder = SQLStatementOrder.executeQuery(strSQL); while (rsOrder.next()) { OID=rsOrder.getString("OID"); OrderDate= rsOrder.getDate("ODate"); SalesPerson=rsOrder.getString("SalesPerson"); %> <tr> <td width='25%'><%=OID%></td> <td width='25%'><%=OrderDate.toString() %></td> <td width='25%'><%=SalesPerson%></td> </tr> <% } rsOrder.close(); } else { out.println("Customer not exist!"); rs.close(); } } catch(SQLException e) { out.println(e.getMessage()); } %>

More Related