Database Access through Java

1. Database Access through Java CSE470 Software Engineering Fall 2000

2. 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. CSE470 Software Engineering Fall 2000

3. 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 CSE470 Software Engineering Fall 2000

4. 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) CSE470 Software Engineering Fall 2000

5. 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 ] CSE470 Software Engineering Fall 2000

6. SQL – CREATE TABLE • Syntax: CREATE TABLE table-name ( field-name1 field-type1[, field-name2 field-type2] … ) • Field types are: INTEGER, CHAR, DATETIME, etc. CSE470 Software Engineering Fall 2000

7. SQL – SELECT • Syntax: SELECT {* | field-name1 [, field-name2]…} 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 CSE470 Software Engineering Fall 2000

8. 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’ CSE470 Software Engineering Fall 2000

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

10. 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 CSE470 Software Engineering Fall 2000

11. 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]. CSE470 Software Engineering Fall 2000

12. 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. CSE470 Software Engineering Fall 2000

13. Establishing a Connection • Load the JDBC driver Example: String driver = “sun.jdbc.odbc.JdbcOdbcDriver”; Class.forName(driver); • Make the connection Example: String url = “jdbc:odbc:alborz”; String user = “sparty”; String pswd = “xyz”; Connection con = DriverManager.getConnection(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] CSE470 Software Engineering Fall 2000

14. 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))”; CSE470 Software Engineering Fall 2000

15. Creating Tables … • Create a JDBC Statement Example: Statement stmt = con.createStatement(); This statement creates a Statement object, stmt that can pass SQL statements to the DBMS using connection, con. • Execute the JDBC Statement Example: stmt.executeUpdate(createsql); [Note: The method executeUpdate() 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 executeQuery() is used] CSE470 Software Engineering Fall 2000

16. 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 executeUpdate() CSE470 Software Engineering Fall 2000

17. 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 executeQuery() ResultSet rs = stmt.executeQuery(querysql); CSE470 Software Engineering Fall 2000

18. Processing Data (Result Sets) • Use the first(), next() and last() methods of ResultSets to scroll through the result set. • Use the getXXX() 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.getString(1) + rs.getString(2); Timestamp birthday = rs.getTimestamp(“DOB”); Integer tele = rs.getInt(“TELE”); System.out.println(name + “ “ + birthday + “ “ + tele); } CSE470 Software Engineering Fall 2000

19. Closing Statements and Connections • Statement objects that have been created, (for eg., using the createStatement() 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() CSE470 Software Engineering Fall 2000

20. STUDENTS More SQL – Specifying Foreign Keys Consider the following tables, STUDENTS & GRADES GRADES CSE470 Software Engineering Fall 2000

21. 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)) CSE470 Software Engineering Fall 2000

22. 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) CSE470 Software Engineering Fall 2000

23. 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. CSE470 Software Engineering Fall 2000

24. 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 CSE470 Software Engineering Fall 2000