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


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


• 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

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


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)


• 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


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


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

Education