Database Access through Java CSE 470 Software Engineering

Database Access through Java CSE 470 Software Engineering Fall 2000 1

DBMS Overview • A Database Management System (DBMS) is a system that provides a convenient and efficient way to store and retrieve data and manages issues like security, concurrent access, etc. Today, Relational Database Systems (RDBMS) are the most extensively used DBMS. • An RDBMS represents data as tables. Each table has a specified set of fields. Each set of values for the fields represents a record in the table. A collection of related tables are grouped together as a ‘database’ in the RDBMS. CSE 470 Software Engineering Fall 2000 2

DBMS Overview… • The de-facto standard for communicating with a DBMS is through the use of Structured Query Language (SQL) Application SQL Results (e. g. , records) DBMS CSE 470 Software Engineering Fall 2000 3

SQL Overview • SQL provides the user a set of statements to access the database. These statements can be classified into two main types: – Statements that specify/modify the structure of the database, known as DDL (Data Definition) statements (e. g. , CREATE TABLE) – Statements that retrieve, add or modify data in the database, known as DML (Data Manipulation) statements (e. g. , SELECT) CSE 470 Software Engineering Fall 2000 4

SQL Statements • Some commonly used SQL statements are: – CREATE TABLE : To define the structure of a table – INSERT: To insert record(s) into a table – DELETE: To delete records from a table – UPDATE: To modify existing data in a table – SELECT: To retrieve records from a table [ Simple syntax for the CREATE TABLE and SELECT statements are provided on the following slides. Examples for these statements are also provided later in the presentation. For more detailed syntax, refer to the MSDN Library ] CSE 470 Software Engineering Fall 2000 5

SQL – CREATE TABLE • Syntax: CREATE TABLE table-name ( field-name 1 field-type 1[, field-name 2 field-type 2] … ) • Field types are: INTEGER, CHAR, DATETIME, etc. CSE 470 Software Engineering Fall 2000 6

SQL – SELECT • Syntax: SELECT {* | field-name 1 [, field-name 2]…} FROM table-name [WHERE ( condition )] • ‘*’ selects all fields in the table • The WHERE clause is used to retrieve only those records that meet a specific condition. Absence of the WHERE clause retrieves all records in the table CSE 470 Software Engineering Fall 2000 7

JDBC™ • An API for Java applications, to access databases and other tabular data • Used to build all-Java database applications • Can interact with multiple database management systems • Often thought of as an acronym for ‘Java Database Connectivity’ CSE 470 Software Engineering Fall 2000 8

JDBC Architecture Java Application JDBC API JDBC Driver Manager JDBC Driver API JDBC Driver Implementation DBMS CSE 470 Software Engineering Database Server Fall 2000 9

The JDBC Driver • Key component that enables communication between the Java application and the data source • The driver makes it possible for the application to: – Establish a connection with the data source – Send queries and update statements to the data source – Process results obtained from the data source CSE 470 Software Engineering Fall 2000 10

How to Start using JDBC • Check that your Java installation includes JDBC API. The Java 2 SDK Standard Edition includes the JDBC 2. 0 core API – the java. sql package. The javax. sql package provides the JDBC Optional Package API. [We will use the Java SDK installed on the Windows 2000 system]. • Check that a JDBC driver is installed. [We will use a JDBC-ODBC bridge driver]. • Check that the DBMS you want to connect to, is setup. [We will use the Microsoft SQL server “ALBORZ”]. • Check that the database you want to connect to has been setup on the DBMS. [The system administrators have setup the required databases]. CSE 470 Software Engineering Fall 2000 11

Using JDBC… • If not already registered, register your database as an (ODBC) data source [instructions are on the web page] • Establish a connection • Create Tables • Enter data into Tables • Process Data from Tables • Close statements/connections when no longer required SQL statements are used to create tables, enter/update data in tables and to query the data. CSE 470 Software Engineering Fall 2000 12

Establishing a Connection • Load the JDBC driver Example: String driver = “sun. jdbc. odbc. Jdbc. Odbc. Driver”; Class. for. Name(driver); • Make the connection Example: String url = “jdbc: odbc: alborz”; String user = “sparty”; String pswd = “xyz”; Connection con = Driver. Manager. get. Connection(url, user, pswd); This statement creates a Connection object, con. [Note: In the above example, “alborz” is the name of the data source (DSN) as registered with the ODBC driver] CSE 470 Software Engineering Fall 2000 13

Creating Tables • Build the SQL statement to create a table – SQL statement to be coded in the application: CREATE TABLE PERSON ( FIRSTNAME CHAR(20), LASTNAME CHAR(20) NOT NULL, DOB DATETIME, TELE INTEGER, CONSTRAINT MYKEY PRIMARY KEY(FIRSTNAME, LASTNAME)) – Corresponding Java code: String createsql = “CREATE TABLE PERSON” + “(FIRSTNAME CHAR(20), LASTNAME CHAR(20), ” + “DOB DATETIME, TELE INTEGER, CONSTRAINT” + “MYKEY PRIMARY KEY (FIRSTNAME, LASTNAME))”; CSE 470 Software Engineering Fall 2000 14

Creating Tables … • Create a JDBC Statement Example: Statement stmt = con. create. Statement(); This statement creates a Statement object, stmt that can pass SQL statements to the DBMS using connection, con. • Execute the JDBC Statement Example: stmt. execute. Update(createsql); [Note: The method execute. Update() is used with statements like CREATE, INSERT, UPDATE, etc. , that affect either the data or the structure of data stored in the database. For queries (SELECT statements) the method execute. Query() is used] CSE 470 Software Engineering Fall 2000 15

Entering Data into a Table • Build the SQL INSERT statement – SQL statement to be coded in the application: INSERT INTO PERSON VALUES (“Sparty”, “Spartan”, “ 1/1/1855”, 3531855) – Corresponding Java code: String insertsql = “INSERT INTO PERSON” + “VALUES (‘Sparty’, ‘Spartan’, ‘ 1/1/1855’, 3531855)”; • Create a JDBC Statement, if not already created (as with CREATE TABLE) • Execute the JDBC Statement with execute. Update() CSE 470 Software Engineering Fall 2000 16

Retrieving Data from a Table • Build the SQL SELECT statement – SQL statement to be coded in the application: SELECT * FROM PERSON WHERE (DATEPART(YY, DOB) < 1900) – Corresponding Java code: String querysql = “SELECT * FROM PERSON” + “WHERE (DATEPART(YY, DOB) < 1900)”; • Create a JDBC Statement, if not already created • Execute the JDBC Statement with execute. Query() Result. Set rs = stmt. execute. Query(querysql); CSE 470 Software Engineering Fall 2000 17

Processing Data (Result Sets) • Use the first(), next() and last() methods of Result. Sets to scroll through the result set. • Use the get. XXX() methods to extract data from the fields. • The fields can be specified either by their names or their position in the record. • Example: while (rs. next()) { String name = rs. get. String(1) + rs. get. String(2); Timestamp birthday = rs. get. Timestamp(“DOB”); Integer tele = rs. get. Int(“TELE”); System. out. println(name + “ “ + birthday + “ “ + tele); } CSE 470 Software Engineering Fall 2000 18

Closing Statements and Connections • Statement objects that have been created, (for eg. , using the create. Statement() method), have to be closed when they are no longer needed. The close() method is used to close the statement. E. g. : stmt. close(); • All open connections also have to be closed, either when they are no longer required, or before the program terminates. E. g. : con. close() CSE 470 Software Engineering Fall 2000 19

More SQL – Specifying Foreign Keys Consider the following tables, STUDENTS & GRADES • STUDENTS ID 10001 … NAME Sparty … DOJ 1/1/1855 … EMAIL sparty@msu. edu … • GRADES STU_ID 10001 … COURSE MTH 101 CEM 101 … GRADE 4. 0 3. 5 … CSE 470 Software Engineering Fall 2000 20

Specifying Foreign Keys… • The table GRADES has a foreign-key relationship with table STUDENTS, i. e. , for every value of STU_ID in GRADES there must be a record with a matching ID in STUDENTS • The SQL statements to create the two tables will be as follows: – SQL to create table STUDENTS: CREATE TABLE STUDENTS ( ID INTEGER, NAME CHAR(30), DOJ DATETIME, EMAIL CHAR(30), CONSTRAINT PK_STUDENTS PRIMARY KEY (ID)) CSE 470 Software Engineering Fall 2000 21

Specifying Foreign Keys… – SQL to create table GRADES, with foreign key CREATE TABLE GRADES ( STU_ID INTEGER, COURSE CHAR(6), GRADE DECIMAL(2, 1), CONSTRAINT PK_GRADES PRIMARY KEY (STU_ID, COURSE), CONSTRAINT FK_GRADES FOREIGN KEY (STU_ID) REFERENCES STUDENTS) CSE 470 Software Engineering Fall 2000 22

References • JDBC API Tutorial and Reference, Second Edition – White, Fisher, Cattell, Hamilton, Hapner; Addison Wesley • Understanding SQL and Java Together – Melton, Eisenberg; Morgan Kauffman Publishers • Deitel & Deitel, “Java How To Program” Third Edition, Prentice Hall Inc. CSE 470 Software Engineering Fall 2000 23

Additional Notes: JDBC Driver Implementation • Various driver implementations are possible. Most commonly used types are: – Type 1 – JDBC-ODBC bridge: Implements JDBC by invoking ODBC on the client machine – Type 2 – Native-API Partly Java Driver: Implements JDBC by making calls to a DBMS native-API on the client machine – Type 3 – Net-Protocol All-Java Driver: Converts JDBC calls to DBMS-independent net protocol messages. The server then translates these messages to DBMS-specific calls – Type 4 – Native-Protocol All-Java Driver: Converts JDBC calls directly into the network protocol used by the DBMS CSE 470 Software Engineering Fall 2000 24