Intro to JDBC To effectively use Java Data

Intro to JDBC To effectively use Java Data Base Connectivity we must understand: 1. Relational Database Management Systems (RDBMS) 2. JDBC Drivers 3. SQL (Structured Query Language) 4. Methods in packages java. sql & javax. sql

Intro to JDBC RDBMS (p. 1) Popular Relational Databases: • Oracle • Microsoft SQL Server • Sybase • Informix • Microsoft Access • my. SQL • Cloud. Scape … and many, many more!

Intro to JDBC RDBMS (p. 2) A Database consists of: Tables, which store… records, which contain fields of information. Relational Databases: store data in multiple tables that can be related (or linked) to one another via key fields.

Intro to JDBC RDBMS (p. 3) Row Number Name Department Salary Location 23603 Jones 413 1100 New Jersey 24568 Kerwin 413 2000 New Jersey 34589 Larson 642 1800 Los Angeles 35761 Myers 611 1400 Orlando 47132 Neumann 413 9000 New Jersey 78321 Stephens 611 8500 Orlando Primary key Column An example: let’s look at the Microsoft Access database we just used.

Intro to JDBC RDBMS (p. 4) A Sample Relational Database:

Intro to JDBC RDBMS (p. 5) In this example, a one-to-many relationship exists between the Contacts table and the Calls table. Contacts. Contact. ID is the primary key; Calls. Contact. ID is the foreign key. Primary and foreign keys need not have the same name, but must be of the same data type.

Intro to JDBC Drivers (p. 1)

Intro to JDBC Drivers (p. 2) Sun’s JDBC-ODBC Bridge – a type 1 driver Tips: - Always use System DSN, so DSN is available to all - ODBC Data Sources are only available on LAN/WAN (Not Internet accessible) - Use a Java/Native. API driver to access databases via internet - More info: JDBC-ODBC Bridge Driver

Intro to JDBC Drivers (p. 3) More drivers available at: http: //industry. java. sun. com/products/jdbc/drivers Lab: Find a type 2, 3 or 4 driver to use with Microsoft Access Your driver documentation will give you the class name to use. For instance, if the class name is jdbc. Driver. XYZ , you would load the driver with the following line of code: Class. for. Name("jdbc. Driver. XYZ"); Another driver lab: Set up distributed DB at \B 122 -1

Intro to JDBC SQL (Structured Query Language) (p. 1) SQL is an industry standard for querying relational databases. Although SQL, like everything else in the software industry, exists in several versions and flavors, basic syntax is consistent and reliable across all systems and software. Database languages, such as SQL, ordinarily include two sublanguages: • Data Definition Language (DDL) • Data Manipulation Language (DML)

Intro to JDBC SQL (p. 2) • Data Definition Language (DDL): Enables developer to create and modify database architecture (tables, records, fields). Query keywords: CREATE TABLE ALTER TABLE DROP TABLE

Intro to JDBC SQL (p. 3) • Data Manipulation Language (DML): Enables developer to add, edit and delete records and retrieve data. Query keywords: SELECT – retrieves data from table(s) INSERT – populates table with data UPDATE – changes existing data in table DELETE – removes data from table

Intro to JDBC SQL (p. 4) SELECT statement clauses: • SELECT – Select and retrieve records • FROM – Tables from which to get fields • WHERE – Criteria for filtering selections • GROUP BY – How to group found records • HAVING – Used with GROUP BY to specify criteria • ORDER BY – Criteria for ordering found records

Intro to JDBC SQL (p. 5) SELECT statement syntax: • Basic SELECT Query : SELECT Field. Name, … FROM Table. Name, … • Examples: SELECT * FROM Authors SELECT Author. ID, Last. Name FROM Authors

Intro to JDBC SQL (p. 6) SELECT statement syntax: • SELECT Query with WHERE clause: SELECT Field. Name, … FROM Table. Name, … WHERE criteria • Examples: SELECT * FROM Authors WHERE Year. Born > 1960 SELECT * FROM Authors WHERE Last. Name = ‘Dietel’ (WHERE operators: < , > , <= , >= , < > , LIKE ( [? , *] )

Intro to JDBC SQL (p. 7) SELECT statement syntax: • ORDER BY Clause: SELECT Field. Name, … FROM Table. Name, … WHERE criteria ORDER BY Field. Name, … ASC | DESC • Example: SELECT * FROM Authors ORDER BY Last. Name, First. Name DESC

Intro to JDBC SQL (p. 8) SELECT statement syntax: • • WHERE with LIKE clause: SELECT * FROM Authors WHERE last. Name LIKE ‘D*’ [Or, use ‘D%’ other DBs] LIKE clause wildcard operators: ? * [Or, _ % other DBs]

Intro to JDBC SQL (p. 9) SELECT statement syntax: • WHERE with LIKE clause: SELECT author. ID, first. Name, last. Name FROM Authors WHERE last. Name LIKE ‘? i*’ [Or, use ‘_i%’ other DBs] • LIKE clause wildcard operators: ? * [Or, _ % other DBs]

Intro to JDBC SQL (p. 10) SELECT statement syntax: • A More Complex Example Using an Implied Join: SELECT Publisher. Name, Title FROM Publishers, Titles WHERE (Titles. Publisher. ID = Publishers. Publisher. ID) ORDER BY Publisher. Name, Title ASC OR SELECT Publishers. Publisher. Name, Titles. Title FROM Publishers, Titles WHERE (Titles. Publisher. ID = Publishers. Publisher. ID) ORDER BY Publishers. Publisher. Name, Titles. Title ASC

Intro to JDBC SQL (p. 11) INSERT statement syntax: • Basic INSERT Query : INSERT INTO table. Name ( column. Name 1, . . , column. Name. N ) VALUES ( value 1, … , value. N ) • Example: INSERT INTO authors ( first. Name, last. Name ) VALUES ( ‘Sue’, ‘Smith’ )

Intro to JDBC SQL (p. 12) UPDATE statement syntax: • Basic UPDATE Query : UPDATE table. Name SET column. Name 1 = value 1, … , column. Name. N = value. N WHERE criteria • Example: UPDATE authors SET last. Name = ‘Jones’ WHERE last. Name = ‘Smith’ AND first. Name = ‘Sue’

Intro to JDBC SQL (p. 13) DELETE statement syntax: • Basic DELETE Query : DELETE FROM table. Name WHERE criteria • Example: DELETE FROM authors WHERE last. Name = ‘Jones’ AND first. Name = ‘Sue’

Intro to JDBC Package java. sql (p. 1) Result Sets • Result. Set (Recordset in ASP): JDBC returns query results in a Result. Set Object • Result. Sets are retrieved by executing queries: result. Set = statement. execute. Query( query );

Intro to JDBC Package java. sql (p. 2) Traversing Result Sets • A Result. Set can be traversed forward and backward (depending on cursor type) using: result. Set. first(); result. Set. next(); result. Set. previous(); result. Set. last(); … and more!

Intro to JDBC Package java. sql (p. 3) Data types for Result Sets • Data can be retrieved from a Result. Set by using various get. XXX methods: result. Set. get. String(Field. Name); // returns data as type result. Set. get. Float(Field. Name); result. Set. get. Date(Field. Name); result. Set. get. Int(Field. Name); result. Set. get. Meta. Data(); // returns Result. Set. Meta. Data obj. … and more!

Intro to JDBC Package java. sql (p. 4) Result Set Cursors