Java/JDBCSome Database TerminolgyDatabase TerminologySample TablePowerPoint PresentationSlide 6SQLSelect StatementFrom the broker table, select the contents of the last name attributeFrom the broker table, select all attributesFrom the broker table, select all attributes where the last name is SmithUse AND or OR to connect multiple where clausesExample with two TablesCartesian ProductSlide 15Slide 16ODBCCreating an ODBC ConnectionSlide 19Slide 20Java’s JDBCSQL Query as a Java StringSlide 23A Simple Standalone JDBC ApplicationSlide 25Slide 26It WorksJSP and ScopingPage ScopePage Scope ExampleUnder TomcatSlide 32Slide 33One Page May Call AnotherCallee.jspAfter Visiting Caller.jspRequest ScopeRequest Scope Caller.jspRequest Scope Callee.jspSlide 40Session ScopeSession Scope ExampleSlide 43Application BeansApplication Bean Example 1Application Bean Example 2Slide 47Slide 48A Simple JSP/JDBC ExampleRegister w/ODBCSlide 51Slide 52Slide 53Slide 54Slide 55It Works!An Example Using Connection PoolingPooledConnection.javaSlide 59Slide 60ConnectionPool.javaSlide 62Slide 63Slide 64Slide 65Slide 66Slide 67Slide 68Slide 69Slide 70Slide 71Slide 72Slide 73Slide 74JDBCPooledExample.jspSlide 76Slide 77Slide 78Slide 79Slide 80It works too!SummaryJava/JDBC • Some Database terminology (brief)• A simple stand alone JDBC Application• Java Server Pages and Scoping• A simple JSP and JDBC example• JSP and JDBC Connection Pooling• SummarySome Database Terminolgy Gary Alperson helped developed these slides and the JDBC example.Database Terminology•Database: A shared collection of logically related data (and a description of this data) designed to meet the information needs of an organization•Relation: A table with columns and rows•Attribute: A named column of a relation•Tuple: A row in a relationDefinitions from Database Systemsby Connolly, Begg, and StrachanSample Tablebrokerb_id lname fname1 Smith John2 Jones Hannah3 Reynolds Leon4 Chang Donna5 Smith Deborah6 Thompson Daniel7 Frendun Laurabrokerb_id lname fname1 Smith John2 Jones Hannah3 Reynolds Leon4 Chang Donna5 Smith Deborah6 Thompson Daniel7 Frendun LauraAttributebrokerb_id lname fname1 Smith John2 Jones Hannah3 Reynolds Leon4 Chang Donna5 Smith Deborah6 Thompson Daniel7 Frendun LauraTupleSQL•Data Definition Language (DDL)–Create tables–Modify tables–Delete (drop) tables•Data Manipulation Language (DML)–Insert data–Update data–Select dataSelect Statementbrokerb_id lname fname1 Smith John2 Jones Hannah3 Reynolds Leon4 Chang Donna5 Smith Deborah6 Thompson Daniel7 Frendun LauraWe will use this data for our examplesFrom the broker table, select the contents of the last name attributeQuerySELECT lnameFROM broker;ResultslnameSmithJonesReynoldsChangSmithThompsonFrendunSQL is not case sensitive. Key SQL words are capitalized and line breaks are inserted by convention.From the broker table, select all attributesQuerySELECT *FROM broker;Results* Acts as a wildcardbrokerb_id lname fname1 Smith John2 Jones Hannah3 Reynolds Leon4 Chang Donna5 Smith Deborah6 Thompson Daniel7 Frendun LauraFrom the broker table, select all attributes where the last name is SmithQuerySELECT *FROM brokerWHERE lname = ‘Smith’;Results•Note that the string is enclosed by single quotes•The contents of a string are case sensitivebrokerb_id lname fname1 Smith John5 Smith DeborahUse AND or OR to connect multiple where clausesQuerySELECT *FROM brokerWHERE lname = ‘Smith’AND fname = ‘John’;Resultsb_id lname fname1 Smith JohnExample with two TablesOne-to-many relationship•Each broker may have many customers•Each customer is only affiliated with one broker•The b_id joins both tables by identifying the unique broker that each customer is associated withbroker customerb_id lname fname1 Smith John2 Jones Hannah3 Reynolds Leon4 Chang Donna5 Smith Deborah6 Thompson Daniel7 Frendun Lauracustomerid b_id lname fname1 1 LeParc Wilson2 1AnstinceDevon3 2 Tabor Mark4 2 Lenks Sandy5 2PhillipsonRichard6 3 Kini Raghu7 4 Kim DavidCartesian Productbroker.b_idbroker.lnamebroker.fnameidcustomer.b_idbroker.lnamebroker.fname1 Smith John 1 1 LeParc Wilson1 Smith John 2 1 Anstince Devon1 Smith John 3 2 Tabor Mark1 Smith John 4 2 Lenks Sandy1 Smith John 5 2 Phillipson Richard1 Smith John 6 3 Kini Raghu1 Smith John 7 4 Kim David2 Jones Hannah 1 1 LeParc Wilson2 Jones Hannah 2 1 Anstince Devon2 Jones Hannah 3 2 Tabor Mark2 Jones Hannah 4 2 Lenks Sandy2 Jones Hannah 5 2 Phillipson Richard2 Jones Hannah 6 3 Kini Raghu2 Jones Hannah 7 4 Kim David3 Reynolds Leon 1 1 LeParc Wilson3 Reynolds Leon 2 1 Anstince Devon3 Reynolds Leon 3 2 Tabor Mark3 Reynolds Leon 4 2 Lenks Sandy3 Reynolds Leon 5 2 Phillipson Richard3 Reynolds Leon 6 3 Kini Raghu3 Reynolds Leon 7 4 Kim David4 Chang Donna 1 1 LeParc Wilson4 Chang Donna 2 1 Anstince Devon4 Chang Donna 3 2 Tabor Mark4 Chang Donna 4 2 Lenks Sandy4 Chang Donna 5 2 Phillipson Richard4 Chang Donna 6 3 Kini Raghu4 Chang Donna 7 4 Kim DavidWhen you do a query on multiple tables, SQL begins by creating the Cartesian product, which combines each tuple from one relation from every tuple of the other relation.(Actual SQL implementationsare free to compute the resulting table efficiently,i.e., the actual Cartesian product may not be generated at all.)QuerySELECT *FROM customer, brokerWHERE broker.b_id = 1;SQL does not realize that the b_id in the customer table is the same as the b_id in the broker table unless you join them in the where clause.broker.b_idbroker.lnamebroker.fnameidcustomer.b_idbroker.lnamebroker.fname1 Smith John 1 1 LeParc Wilson1 Smith John 2 1 Anstince Devon1 Smith John 3 2 Tabor Mark1 Smith John 4 2 Lenks Sandy1 Smith John 5 2 Phillipson Richard1 Smith John 6 3 Kini Raghu1 Smith John 7 4 Kim DavidResultsCartesian ProductQuerySELECT *FROM customer, brokerWHERE broker.b_id = 1AND broker.b_id = customer.b_id;Resultsbroker.b_idbroker.lnamebroker.fnameidcustomer.b_idbroker.lnamebroker.fname1 Smith John 1 1 LeParc Wilson1 Smith John 2 1 Anstince DevonODBCODBC is a programming interface that enables applications to access data in database systems that use Structured Query Language (SQL) as a data standard.Creating an ODBC Connection•Click on the Start button.•Choose Settings, Control Panel•Double-click on ODBC Data Sources•Choose the System DSN tab•Click Add•Click on the desired driver (MSAccess)•Click on the Finish button•Enter a Data Source Name•Click on the Select button•Locate the desired