Lecture 8 Object Oriented Programming in Java Advanced

Lecture 8 Object Oriented Programming in Java Advanced Topics Java Database Connectivity (JDBC) June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 1

Today’s Lecture • Trail: JDBC(TM) Database Access • Lesson: JDBC Basics • Other resource: JDBC Short Course – http: //developer. java. sun. com/developer/online. Training/Database/JDBCShort. Course/index. html June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 2

History • Structured Query Language (SQL) • ODBC • JDBC June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 3

Data Definition Language • creating databases • creating tables and columns • creating indexes • examples – CREATE TABLE tablename …. – CREATE INDEX anindexname …. June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 4

Data Manipulation Language • inserting records • deleting records • updating records • examples: – SELECT * FROM atable WHERE fieldname = ‘avalue’ – INSERT INTO atable VALUES(‘field 1’, ’field 2’, ’field 3’) – DELETE FROM atable WHERE fieldname = ‘avalue’ June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 5

Common Programming Steps • With JDBC: – – – load drivers connect to a database create SQL statement send SQL to database disconnect from database June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 6

Step 1: Loading Drivers • Sun’s provided driver – JDBC-ODBC bridge driver – good for prototyping – not to be used for real production applications • Register the driver: – Driver. Manager. register. Driver(new sun. jdbc. odbc. Jdbc. Odbc. Driver()) June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 7

Design Point – Guess what is the type of the parameter to the Driver. Manager’s register. Driver method? – What is the designificance? June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 8

Step 2: Connecting • Driver. Manager’s get. Connection method – several ways to specify the required parameters • Driver. Manager. get. Connection(URL, userid, password) • Connection get. Connection(URL, properties) • Connection get. Connection(URL) • get. Connection returns an object of type “Connection” June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 9

Design Point – multiple ways to connect – Use of public static methods to create a Connection object: factory design pattern – private constructor to prevent from instantiating the class – Connection is an interface June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 10

Steps 3 & 4: Create and Send a SQL Statement • use the connection to create the statement – Statement sql = con. create. Statement(); • create a String with the SQL statement – String s = “INSERT INTO tablename VALUES(’field 1’, ’field 2’)”; • send the statement to the driver/database – sql. execute(s); June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 11

Step 5: Disconnect • close the connection once you are done with it • example: – Connection con; – …. – con. close(); June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 12

Getting data from a database • SQL – SELECT field 1, field 2, field 3 FROM atablename • use Statement execute. Query method – Result. Set result. Set = sql. execute. Query(“…”) June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 13

Handling Result Sets • the Result. Set class allows you to iterate through the rows returned by the SQL statement • String s • while(result. Set. next()){ – s = result. Set. get. String(“field 1”); • } June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 14

Getting Metadata • Result. Set. Metadata meta = result. Set. get. Meta. Data(); • int num. Of. Columns = meta. get. Column. Count(); • String s = meta. get. Column. Label(1); • String s = meta. get. Column. Type. Name(); June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 15

Multiple execute methods • for INSERT, UPDATE, DELETE: – stmt. execute. Update(“. . . ”); • for obtaining result sets – Result. Set rs = stmt. execute. Query(“. . . ”); June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 16

Prepared Statement • used for efficiency when the same statement will be used multiple times • Prepared. Statement prepared = connection. prepare. Statement(“INSERT …”); June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 17

synchronizing updates • connection. commit(); June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 18

JDBC Hands-on - I • Problem statement; – first, we will create a small database using either the text file or Access ODBC drivers. • The database-table will have the following fields; – First. Name VARCHAR – Last. Name VARCHAR – Grade INTEGER – second, we will create a Java program that inserts and reads records into/from the database-table – third, we will look at the metadata of the databasetable June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 19

JDBC Hands-on - II • configure a Data Source Name – Bring up Control Panel » Select the Start button » Select the Settings menu item » Select the Control Panel menu item – Find and double-click on the ODBC Icon (32 -bit/with 32 on it). This brings up the 'Data Sources' window. » Select Add. This brings up the Add Data Source window. – Select the driver for the type of driver you want. » » If you selected Text, the ODBC Text Setup window appears. Name the data source mage. Fill in a description. Select the directory in which you placed files from the initial task. – Select OK to accept new driver. June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 20

JDBC Hands-on - III • Create an empty text file which will later contain your data • make sure the text file is in the directory specified when configuring the DSN June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 21

JDBC Hands-on - IV • Edit a Java program to start using the defined DSN Sample program import java. sql. *; public class JDBCTest{ public static void main(String arg[]) throws Exception{ Driver. Manager. register. Driver(new sun. jdbc. odbc. Jdbc. Odbc. Driver()); Connection con = Driver. Manager. get. Connection("jdbc: odbc: dsnname"); Statement s = con. create. Statement(); s. execute("INSERT INTO file. txt VALUES(“. . . "); s. execute("INSERT INTO file. txt VALUES(“. . . ”); …. con. close(); } } June 1, 2000 Object Oriented Programming in Java (95 -707) Java Language Basics 22