170 likes | 311 Views
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.
E N D
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 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.
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
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.*" %>
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
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.");
- 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
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
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
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 !
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
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
Insert into Managers ( etc ) request.getParameter("mName") mName HTML form String name Java Program name age Managers' Table name attribute database
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.
User-1 User-2 server Question-for-1 Question-for-2
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