java jdbc connection

Topic 15: Database Connectivity
JDBC
Reading: Chapter 4, Volume 2
Advanced Programming Techniques

Objective & Outline
• Objective:
– Introduction: How to connect to and
communicate with databases
• Outline:
– Database basics
– Connecting to a database with JDBC
– Querying and manipulating databases with JDBC

Database Basics
• A (relational) database:
– A collection of tables, each containing information
about certain aspects of some objects.
– Attributes: names of columns
– Records or entries: rows
• Example:
testdb.mdb
– 4 tables

Database Basics
• Database management systems (DBMS):
allow user to manipulate data
• Many DBMS available. We will use
Microsoft Access, which comes with
Microsoft Office.

Database Basics
• Task:
– Find all books by “Fox, David”, list author name, title, publisher name and price
• The query involves all four tables and

Database Basics
• The query: Select2 in testmd.db

Database Basics
• Task: Find all books published by McGraw-Hill
– Select3 in testdb

Database Basics
• Update:
– Modify records
– Insert new records
– Delete records
– Create new Table
– ….

Database Basics
• So far, interact with database manually
– Usually interact with databases via application programs
• The structured query language (SQL):
– Standard language for accessing databases:.
• Query database using SELECT statements
– Select all records in the Books table:
SELECT * FROM Books
– Select only some columns
SELECT ISBN, Price, Title
FROM Books

Database Basics
• Select from multiple tables
– Find all books by “Fox, David”, list author name, title, publisher name and
price
SELECT Authors.Name, Books.Title, Publishers.Name,
Books.Price
FROM Books, Authors, Publishers, BooksAuthors
WHERE Publishers.Publisher_Id = Books.Publisher_Id
AND Books.ISBN = BooksAuthors.ISBN
AND BooksAuthors.Author_Id = Authors.Author_Id
AND Authors.Name= 'Fox, David'
– When we select from multiple tables, a joint table is formed. The joint table
consists of ALL possible combinations of rows from all the tables.
– The first three conditions in the WHERE clause constrain the query to legal
combinations.

Database Basics
• Update:
– Slash prices for McGraw-Hill books by 0.5
UPDATE Books
SET Price = Price -0.5
WHERE Books.Publisher_Id = ‘00791’
– A more intuitive way
UPDATE Books
SET Price = Price -0.5
WHERE Books.Publisher_Id =
( SELECT Publisher_Id
FROM Publishers
WHERE Name = 'McGraw-Hill‘)

Objective & Outline
• Outline:
– Database basics
– Connecting to a database with JDBC
– Querying and manipulating databases with JDBC

Connecting to Databases with JDBC
• JDBC: Java Database Connectivity kit
• Package: java.sql
– DriverManager: class for making connection to
databases
– Connection: interface for communicating with
databases once connection has been established.
– Other interfaces: Statement, ResultSet,
DatabaseMetaData, ResultSetMetaData,
SQLException, ….

• To establish connection, DBMS must provide JDBC
drivers
– Many DBMS venders (222) provide JDBC drivers
• PointBase Mobile Edition: DBMS in java by PointBase
Inc.
– JDBC driver: com.pointbase.jdbc.jdbcDriver
• See http://servlet.java.sun.com/products/jdbc/drivers for DBMS
with JDBC drivers
– Microsoft Access & many other DBMS
• Has a C interface called ODBC (Open DataBase Connectivity)
• The JDBC/ODBC bridge sun.jdbc.odbc.JdbcOdbcDriver
provided by Sun

• JDBC-to-database communication path
Will discuss this path
using testdb.mdb
Textbook discusses this
path using testdb.mdb
Java Application
JDBC Driver Manager
JDBC/ODBC
bridge
Vendor
supplied
JDBC
driversODBC drivers
Database

Next:
– Register testdb.mdb with
the ODBC Data Source
Administrator on your
PC.
– Register JDBC driver
(sun.jdbc.odbc.JdbcO
dbcDriver ) with
DriverManager
– Making connection
Java Application
JDBC Driver Manager
JDBC/ODBC
bridge
Vendor
supplied
JDBC
driversODBC
Database

• Register testdb.mdb with the ODBC Datasource Administer on
your PC. Instructions:
– Open Control Panel and select ODBC Data Sources [32bit]
(on XP under Administrative Tools). You will get the window
shown on the right. Click on Add.

– Select Microsoft Access Driver and then
click on Finish. You will get the window shown
on the right.
– Provide a name for the data source, say TestDB.
– Click and Select and select testdb.mdb
from file system (picture on the next page)

– Then you see the picture on the right. Simply click
on OK and the registration is complete.
– Thereafter the database testdb.mdb is known
as TestDB to the ODBC Datasource
Adminstrator.
– For JDBC, URL for the database is:
jdbc:odbc:TestDB

• Register JDBC driver (sun.jdbc.odbc.JdbcOdbcDriver )
with DriverManager
– Method 1:
• set system property jdbc.drivers
System.setProperty("jdbc.drivers",
"sun.jdbc.odbc.JdbcOdbcDriver");
• At initialization, DriverManager loads all driver classes
referenced in jdbc.drivers
– Method 2: Load driver explicitely at any time
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
A call to forName("X") causes the class named X to be initialized.
Java Application
JDBC Driver Manager
JDBC/ODBC
bridge
Vendor
supplied
JDBC
drivers
ODBC
Database

• Making connection
Connection con=
DriverManager.getConnection("jdbc:odbc:TestDB", "", "");
java.sql.Connection: represents a connection to a DB.
• Several drivers might have been registered with the DriverManager. The
DriverManager will find one that can use the protocol on the database URL.
URL for database
DB passwordDB user name

• Connection examples
– ConnectMe.java: register JDBC driver using the
second method
– ConnectMe1.java: register JDBC driver using the
first method
• Note. Examples (including testdb.mdb) in this
topic are packaged in the zip. See the code page

• An easier way to connect to DB
– Interface javax.sql.DataSource
– DBMS Vendor provides An object DataSource object,
• which will typically be registered with a naming service
based on the JavaTM Naming and Directory (JNDI) API for
easy retrieval
– Programmer get connection using the following
method of DataSource interface
Connection getConnection(String username, String password)

Querying and Manipulating Databases with JDBC
• getConnection returns an object of java.sql.Connection
Connection con =
DriverManager.getConnection("jdbc:odbc:TestDB","", "");
• Creates a Statement object for sending SQL statements to the database
Statement stmt =
con.createStatement();
• Next:
– Queries
– Commands (updates)

• Form query as a string
String query = "SELECT ISBN, Title, Price " +
"FROM Books";
• Call the executeQuery method of the Statement interface
ResultSet rs = stmt.executeQuery( query );
– Which returns an object of the java.sql.ResultSet interface.
• A table of data representing a database result set, which is usually generated by
executing a statement that queries the database.
• Analysis of result:
while (rs.next())
{ for (int i = 1; i <= 3; i++)
System.out.print( rs.getString(i) + " | ");
System.out.println("");
}
Select1.java
Databasecolumn numberstartsat1

• More complex queries
– Select2.java: Find all books by David Fox and
print out author name, title, publisher name, and
price.
String query =
"SELECT Authors.Name, Books.Title, Publishers.Name,
Books.Price " +
"FROM Books, Authors, Publishers, BooksAuthors " +
"WHERE Publishers.Publisher_Id = Books.Publisher_Id " +
"AND Books.ISBN = BooksAuthors.ISBN " +
"AND BooksAuthors.Author_Id = Authors.Author_Id " +
"AND Authors.Name= 'Fox, David' ";

 More complex queries
 Select3.java: Find all books published by McGraw-Hill and print
out author name, title, publisher name, and price
String query =
"SELECT Authors.Name, Books.Title, Publishers.Name,
Books.Price " +
"FROM Books, Authors, Publishers, BooksAuthors " +
"WHERE Publishers.Publisher_Id = Books.Publisher_Id " +
"AND Books.ISBN = BooksAuthors.ISBN " +
"AND BooksAuthors.Author_Id = Authors.Author_Id " +
"AND Publishers.Name= 'McGraw-Hill' ";

 Commands: modify records, insert new records, delete records, ….
 Form command as a string
String command =
"UPDATE Books " +
"SET Price = Price + " + "-0.5" +
" WHERE Books.Publisher_Id =
(SELECT Publisher_Id FROM Publishers WHERE Name = 'McGraw-
Hill')";
 Call the executeUpdate method of the Statement interface
stmt.executeUpdate( command );
Update.java

Assignment 3
• Topics involved:
– GUI: Topic 9, 10
– Applets, Topic 11
– Multithreading: Topic 12
– Java Networking: Topic 13
– Servlets: Topic 14
– JDBC: Topic 15
Applet
Swing (GUI)
Servlet
DB
Java Networking
Client Browser ServerApplet
User
JDBC

java jdbc connection

More Related Content

What's hot

Viewers also liked

Similar to java jdbc connection

More from Waheed Warraich

Recently uploaded

java jdbc connection