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 columnsSELECT 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.5WHERE Books.Publisher_Id = ‘00791’

– A more intuitive wayUPDATE Books SET Price = Price -0.5WHERE 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, ….

Connecting to Databases with JDBC

• 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

Connecting to Databases with JDBC

• 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

Connecting to Databases with JDBC

Next:– Register testdb.mdb with

the ODBC Data Source Administrator on your PC.

– Register JDBC driver (sun.jdbc.odbc.JdbcOdbcDriver ) with DriverManager

– Making connection

Java Application

JDBC Driver Manager

JDBC/ODBC bridge

Vendor supplied JDBC driversODBC

Database

Connecting to Databases with JDBC

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

Connecting to Databases with JDBC

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

Connecting to Databases with JDBC

– 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

Connecting to Databases with JDBC

• 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 timeClass.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

Connecting to Databases with JDBC

• 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

Connecting to Databases with JDBC

• 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

Connecting to Databases with JDBC• 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)

Objective & Outline

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

Querying and Manipulating Databases with JDBC

• getConnection returns an object of java.sql.ConnectionConnection 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)

Querying and Manipulating Databases with JDBC

• Form query as a stringString query = "SELECT ISBN, Title, Price " +

"FROM Books";

• Call the executeQuery method of the Statement interfaceResultSet 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

Database column number starts at 1

Querying and Manipulating Databases with JDBC

• 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' ";

Querying and Manipulating Databases with JDBC

Commands: modify records, insert new records, delete records, ….

Form command as a stringString 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 interfacestmt.executeUpdate( command );

Querying and Manipulating Databases with JDBC

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