Java and Databases: An Overview

Java and Databases: An Overview

Objectives • After completing this lesson, you should be able to do the following: • Describe how to use JDBC to access a database from Java • Describe how to use JBCL data components to access a database • Describe how to use SQLJ to access a database

Overview • This lesson introduces the technologies involved in accessing databases from a Java program JDBC Database Applet or application

Accessing Databases in the Non-Java World • Database vendors provide APIs that programs can call to access a database • Known as a Call Level Interface (CLI) • For example, Oracle Call Interface (OCI) • Vendor provides a driver to receive CLI calls and forward to database Program Calls OCI functions Oracle OCI driver Oracleserver

Dealing with Different Databases • Different databases have their own CLI to access different databases • Need to use different CLIs • Need a driver for each CLI Program 1, calls OCI functions Program 2, calls other CLI functions Oracle OCI driver Other driver Other db Oracle

ODBC: A Standard CLI • ODBC provides a standard interface to any database Program 1, calls ODBC functions Program 2, calls ODBC functions ODBC Driver Manager ODBC driver for Oracle ODBC driver for other db Other db Oracle

From ODBC to JDBC • JDBC plays a similar role in Java • JDBC defines standard interfaces and classes that you can call from Java Java Program 1, uses JDBC classes/interfaces Java Program 2, uses JDBC classes/interfaces JDBC Driver Manager JDBC driver for Oracle JDBC driver for other db Other db Oracle

What Is JDBC? • JDBC defines standard interfaces • Import java.sql package in a Java app • Interfaces implemented by JDBC drivers Example JDBC interfaces JDBC driver, such as Oracle interface Driver{…} class AAA implements Driver{…} class BBB implements Connection{…} etc… interface Connection{…} interface Statement{…} interface ResultSet{…}

Using JDBC • A simplified example: import java.sql.*; public class MyClass { public void MyMethod() { Connection con = DriverManager.getConnection(…); Statement st = con.createStatement(); ResultSet res; res = st.executeQuery("select * from emp"); // … Loop through result set, one row at a time

Oracle JDBC Drivers • Oracle provides two different types of JDBC driver • “JDBC Thin” driver • “JDBC OCI” driver • Use one or the other, depending on which type of Java program you are building

Oracle JDBC Thin Driver • The Oracle JDBC Thin driver is written in 100% pure Java • Can be downloaded over the network, with a Java applet • Use this driver for Java applets or applications Java applet or application Oracle Oracle JDBC Thin driver

Oracle JDBC OCI Driver • The Oracle JDBC OCI driver makes OCI calls to a preinstalled “native” driver on the client • Use for Java applications only Java application Oracle JDBC OCI driver Oracle oci803jdbc.dll

AppBuilder Support for JDBC • AppBuilder for Java includes: • JDBC classes / interfaces in java.sql • Sun JDBC-ODBC Bridge driver • The installation also provides: • Oracle Thin drivers for Oracle7 and Oracle8 • Oracle JDBC-OCI drivers for Oracle7 and Oracle8

AppBuilder Data Components • The JavaBeans Component Library (JBCL) provides many data components • Some are visual “data-aware” controls • Some are nonvisual “worker” controls • Allows a Java program to manipulate a database with the minimum of code • Hides much of the complexity

JBCL Data Components Example • This applet connects to an Oracle8 database, and performs a simple query • Results of query are displayed in a grid • No code neededin this example

Dynamic SQL in Java • The JBCL data components use JDBC to achieve database connectivity • The JDBC calls use “dynamic SQL” • SQL strings are parsed at run time • Not precompiled Statement stmt; stmt = conn.execute("select * from EMP " + "where SAL > 1000 " + "order by HIREDATE");

Static SQL in Java • Oracle also provides the capability to provide “static SQL” in a Java program • SQL statements are precompiled, and converted to equivalent Java code • Allows SQL statements to be verified against database at compile time • Fewer run time errors • This ability is provided by “SQLJ”

SQL What Is SQLJ? • SQLJ is a way to embed static SQL in Java programs • Looks like standard Java, with a small number of localized extensions • Translates embedded SQL into calls to JDBC

SQLJ Example • A simplified example of how to use SQLJ • Inserts a new row into emp table: import java.sql.*; import sqlj.runtime.ref.*; #sql { insert into EMP values('Thomas' , 1200) }; afile.sqlj

Precompiling SQLJ Code • SQLJ code must be precompiled to Java, using the SQLJ translator • Checks the SQL code at compile time • Converts afile.sqlj into afile.java • Translates embedded SQL into appropriate calls to JDBC afile.java afile.sqlj SQLJ translator JDBC calls

Summary • JDBC defines a standard database interface for Java programs • Two primary types of Oracle JDBC driver: JDBC Thin, and JDBC OCI • AppBuilder for Java provides many data-aware components • SQLJ can be used to embed static SQL code in Java programs

Java and Databases: An Overview