1 / 17

Java, Access, SQL, HTML

Java, Access, SQL, HTML. Three-tier architecture involves:. Client - Browser. Server - Tomcat. - Server-side language - JSP. Database - Access. could just as well be:. Server - IIS. - Server-side language - PHP. Database - MySQL. Key Issues.

brandee
Download Presentation

Java, Access, SQL, HTML

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. Java, Access, SQL, HTML

  2. Three-tier architecture involves: Client - Browser Server - Tomcat - Server-side language - JSP Database - Access could just as well be: Server - IIS - Server-side language - PHP Database - MySQL

  3. Key Issues How to register database so Java can locate it. How to access Java SQL methods. How to connect to a database. How to use SQL to Insert a record in data base. How to retrieve records using SQL Select query. How to process and output Select results. How to access HTML Form data.

  4. How to register database so Java can locate it. Use Control panel > Admin tools > ODBC Data sources etc Data Source Name (DSN) can be whatever you want DSN does not have to be same as file name

  5. How to identify required Java SQL methods Library of java objects, methods that can handle SQL Place at top of JSP page: <%@ page import = "java.sql.*" %>

  6. How to Connect to database Tags to enclose Java simplified: Loads software drivers that can talk to database <% %> Connects to Data SourceFrank Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver"); Connection conn=null; conn = DriverManager.getConnection("jdbc:odbc:Frank", "", ""); out.println ("Database Connected."); Embedded in HTML response. Java try-catch syntax is omitted for simplicity

  7. SQL to Insert a record in database Make Statement object (once) Make SQL querystring s Statementstm = conn.createStatement(); Strings= "INSERT INTO Managers VALUES ('Charlie') "; Execute query stm.executeUpdate(s); Close database conn.close(); out.println ("Database closed.");

  8. - Java SQL Overview - These are mainly repetitious boilerplate. Loads Driver to Connect to database Class.forName Executes driver DriverManager.getConnection conn= DriverManager.getConnection Makes Connection object stm= conn.createStatement() Makes Statement object s = "Insert into ... " String s stm.executeUpdate ( s ) Execute query

  9. How to retrieve records using Select query. Make Statement object - once ! Statement stm = conn.createStatement ( ); Make SQL string s String s = "SELECT * FROM Managers"; Execute s ResultSet r= stm.executeQuery(s); Save results in ResultSetr Like a table retrieved by Select query

  10. How to process results. ...from previous slide Get next row of results ResultSet r= stm.executeQuery(s); while ( r.next( ) ) { } out.print ("<br>Name: " + r.getString ("name") ); out.println (" Age : " + r.getString ("age" ) ); Get attribute values by name

  11. Navigation thru results next row of table ResultSet r= stm.executeQuery(s); while ( r.next( ) ) { } Fails at end of table out.print ("<br>Name: " + r.getString ("name") ); Gets named column in current row !

  12. Navigation – behavior of next ( ) next marches thru table rows while ( r.next( ) ) { } out.print ("<br>Name: " + r.getString ("name") ); next ( ) is true if it points to a row next ( ) is false if no more rows to point to gets data from current row

  13. How to access HTML Form data Get input with HTML field name ! Java variable String name = request.getParameter ("mName"); name = "'" + name + "'" ; SQL needs quotes around values String s = "INSERT INTO Managers VALUES (" +name+ ")" stm.executeUpdate(s); Make SQL query Variable input data

  14. Insert into Managers ( etc ) request.getParameter("mName") mName HTML form String name Java Program name age Managers' Table name attribute database

  15. Remembering who the server is talking too...one approach When you contact a server again It does not remember its previous interaction with you Unless you make that happen. A simple approach is to use hidden html fields Where the server sends you data that you transparently send back when you complete the Form. Other approaches involve cookies or session information or background databases.

  16. User-1 User-2 server Question-for-1 Question-for-2

  17. Remembering who the server is talking too...one approach HTML-1 – form, field, button, action JSP-1 – gets data, send html with hidden fields & data! & new action HTML-2 – form, hidden field, button, action= JSP-2 JSP-2 – gets data, sends to browser

More Related