Java Database Connectivity with JDBC: A Handout for CS-506 Students, Lecture notes of Web Design and Development

Download Java Database Connectivity with JDBC: A Handout for CS-506 Students and more Lecture notes Web Design and Development in PDF only on Docsity!

HAND OUTS

WEB DESIGN AND DEVELOPMENT

Lecture 14

Java Database Connectivity

Introduction

Java Database Connectivity (JDBC) provides a standard library for accessing databases. The JDBC API contains number of interfaces and classes that are extensively helpful while communicating with a database.

The java.sql package

The java.sql package contains basic & most of the interfaces and classes. You automatically get this package when you download the J2SE™. You have to import this package whenever you want to interact with a relational database.

Connecting With Microsoft Access

In this handout, we will learn how to connect & communicate with Microsoft Access Database. We chooses Access because most of you are familiar with it and if not than it is very easy to learn.

Create Database

In start create a database “PersonInfo” using Microsoft Access. Create one table named “Person”. The schema of the table is shown in the picture.

ƒ Press Add… button and choose Microsoft Access Driver (*.mdb) from Create New Data Source window and press Finish button as shown in diagram.

ƒ After that, ODBC Microsoft Access Setup window would be opened as shown in following diagram

ƒ Enter the Data Source Name personDSN and select the database by pressing Select button. The browsing window would be opened, select the desired folder that contains the database (The database .mdb file you have created in the first step) Press Ok button.

Basic Steps in Using JDBC

There are eight (8) basic steps that must be followed in order to successfully communicate with a database. Let’s take a detail overview of all these one by one.

1. Import Required Package

ƒ Import the package java.sql.* that contains useful classes and interfaces to access & work with database. import java.sql.*;

2. Load Driver

ƒ Need to load suitable driver for underlying database. ƒ Different drivers & types for different databases are available. ƒ For MS Access, load following driver available with j2se. Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”); ƒ For Oracle, load the following driver. You have to download it explicitly. Class.forName(“oracle.jdbc.driver.OracleDriver”);

3. Define Connection URL

ƒ To get a connection, we need to specify the URL of a database (Actually we need to specify the address of the database which is in the form of URL) ƒ As we are using Microsoft Access database and we have loaded a JDBC-ODBC driver. Using JDBC-ODBC driver requires a DSN which we have created earlier and named it personDSN. So the URL of the database will be String conURL = “jdbc:odbc:personDSN”;

4. Establish Connection With DataBase

ƒ Use DriverManager to get the connection object.

ƒ The URL of the database is passed to the getConnection method. Connection con = DriverManager.getConnection(conURL);

ƒ If DataBase requires username & password, you can use the overloaded version of getConnection method as shown below:

String usr = “umair”; String pwd = “vu”;

ƒ Always remember to call next() method at-least once

ƒ To retrieve the data of the column of the current row you need to use the various getters provided by the ResultSet. ƒ For example, the following code snippet will iterate over the whole ResultSet and illustrates the usage of getters methods while ( rs.next() ){ //by using column name String name = rs.getString(“columnName”); // or by using column index String name = rs.getString(1); }

8. Close the Connection

ƒ An opening connection is expensive, postpone this step if additional database operations are expected con.close();

Example Code 14.1: Retrieving Data from ResultSet

The JdbcEx.java demonstrates the usage of all above explained steps. In this code example, we connect with the PersonInfo database, the one we have created earlier, and then execute the simple SQL SELECT query on Person table, and then process the query results.

// File JdbcEx.java

//step 1: import package import java.sql.*;

public class JdbcEx {

public static void main (String args[ ]) {

try {

//Step 2: load driver Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

//Step 3: define the connection URL String url = “jdbc:odbc:personDSN”; //Step 4: establish the connection Connection con = DriverManager.getConnection(url); //Step 5: create Statement Statement st = con.createStatement();

//Step 6: preapare & execute the query String sql = “SELECT * FROM Person”; ResultSet rs = st.executeQuery(sql); //Step 7: process the results while(rs.next()){

// The row name is “name” in database “ PersonInfo , // hence specified in the getString() method. String name = rs.getString(“name”); String add = rs.getString(“address”); String pNum = rs.getString(“phoneNum”); System.out.println(name + “ ” + add + ” ” + pNum); }

//Step 8: close the connection con.close();

}catch(Exception sqlEx){ System.out.println(sqlEx); } } // end main } // end class