Object Oriented Programming in Java (95-707) Java Language Basics

1
Lecture 8Object Oriented Programming in Java

• Advanced Topics
• Java Database Connectivity (JDBC)

2
Todays Lecture

• Trail JDBC(TM) Database Access
• Lesson JDBC Basics
• Other resource JDBC Short Course
• http//developer.java.sun.com/developer/onlineTrai
  ning/Database/JDBCShortCourse/index.html

3
History

• Structured Query Language (SQL)
• ODBC
• JDBC

4
Data Definition Language

• creating databases
• creating tables and columns
• creating indexes
• examples
• CREATE TABLE tablename .
• CREATE INDEX anindexname .

5
Data Manipulation Language

• inserting records
• deleting records
• updating records
• examples
• SELECT FROM atable WHERE fieldname avalue
• INSERT INTO atable VALUES(field1,field2,field
  3)
• DELETE FROM atable WHERE fieldname avalue

6
Common Programming Steps

• With JDBC
• load drivers
• connect to a database
• create SQL statement
• send SQL to database
• disconnect from database

7
Step 1 Loading Drivers

• Suns provided driver
• JDBC-ODBC bridge driver
• good for prototyping
• not to be used for real production applications
• Register the driver
• DriverManager.registerDriver(new
  sun.jdbc.odbc.JdbcOdbcDriver())

8
Design Point

• Guess what is the type of the parameter to the
  DriverManagers registerDriver method?
• What is the design significance?

9
Step 2 Connecting

• DriverManagers getConnection method
• several ways to specify the required parameters
• DriverManager.getConnection(URL,userid,password)
• Connection getConnection(URL,properties)
• Connection getConnection(URL)
• getConnection returns an object of type
  Connection

10
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

11
Steps 3 4 Create and Send a SQL Statement

• use the connection to create the statement
• Statement sql con.createStatement()
• create a String with the SQL statement
• String s INSERT INTO tablename
  VALUES(\field1\, \field2\)
• send the statement to the driver/database
• sql.execute(s)

12
Step 5 Disconnect

• close the connection once you are done with it
• example
• Connection con
• .
• con.close()

13
Getting data from a database

• SQL
• SELECT field1, field2, field3 FROM atablename
• use Statement executeQuery method
• ResultSet resultSet sql.executeQuery()

14
Handling Result Sets

• the ResultSet class allows you to iterate through
  the rows returned by the SQL statement
• String s
• while(resultSet.next())
• s resultSet.getString(field1)

15
Getting Metadata

• ResultSetMetadata meta resultSet.getMetaData()
• int numOfColumns meta.getColumnCount()
• String s meta.getColumnLabel(1)
• String s meta.getColumnTypeName()

16
Multiple execute methods

• for INSERT, UPDATE, DELETE
• stmt.executeUpdate(...)
• for obtaining result sets
• ResultSet rs stmt.executeQuery(...)

17
Prepared Statement

• used for efficiency when the same statement will
  be used multiple times
• PreparedStatement prepared connection.prepareSta
  tement(INSERT )

18
synchronizing updates

• connection.commit()

19
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
• FirstName VARCHAR
• LastName 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
  database-table

20
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.

21
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

22
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
• DriverManager.registerDriver(new
  sun.jdbc.odbc.JdbcOdbcDriver())
• Connection con
• DriverManager.getConnection("jdbcodbcdsnname"
  )
• Statement s con.createStatement()
• s.execute("INSERT INTO file.txt VALUES(...")
• s.execute("INSERT INTO file.txt VALUES(...)
• .
• con.close()