Database Access through Java

1
Database Access through Java
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.

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
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)

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

6
SQL CREATE TABLE

• Syntax
• CREATE TABLE table-name (
• field-name1 field-type1,
• field-name2 field-type2
• )
• Field types are INTEGER, CHAR, DATETIME, etc.

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

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

9
JDBC Architecture
Java Application
JDBC API
JDBC Driver Manager
JDBC Driver API
JDBC Driver Implementation
DBMS
Database Server
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

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.

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.

13
Establishing a Connection

• Load the JDBC driver
• Example
• String driver sun.jdbc.odbc.JdbcOdbcDriver
• Class.forName(driver)
• Make the connection
• Example
• String url jdbcodbcalborz
• 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

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))

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

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()

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) lt 1900)
• Corresponding Java code
• String querysql SELECT FROM PERSON
• WHERE (DATEPART(YY, DOB) lt 1900)
• Create a JDBC Statement, if not already created
• Execute the JDBC Statement with executeQuery()
• ResultSet rs stmt.executeQuery(querysql)

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)

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()

20
More SQL Specifying Foreign Keys
Consider the following tables, STUDENTS GRADES

• STUDENTS

ID NAME DOJ EMAIL
10001 Sparty 1/1/1855 sparty_at_msu.edu

• GRADES

STU_ID COURSE GRADE
10001 MTH101 4.0
10001 CEM101 3.5

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))

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)

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.

24
JDBC Driver Implementation
Additional Notes

• 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