JDBC – Java DataBase Connectivity

1. JDBC –Java DataBase Connectivity

2. Introduction • Database • Collection of data • DBMS • Database management system • Storing and organizing data • SQL • Relational database • Structured Query Language • JDBC • Java Database Connectivity • JDBC driver

3. JDBC • Programs developed with Java/JDBC are platform and vendor independent. • “write once, compile once, run anywhere” • Write apps in java to access any DB, using standard SQL statements – while still following Java conventions. • JDBC driver manager and JDBC drivers provide the bridge between the database and java worlds.

4. General Architecture • What design pattern is implied in this architecture? • What does it buy for us? • Why is this architecture also multi-tiered?

5. ODBC • JDBC heavily influenced by ODBC • ODBC provides a C interface for database access on Windows environment. • ODBC has a few commands with lots of complex options. Java prefers simple methods but lots of them.

6. Type 1 Type 3 Database 3rd Party API Type 2 Native C/C++ API Local API Network API Type 4 • Type 1: Uses a bridging technology to access a database. JDBC-ODBC bridge is an example. It provides a gateway to the ODBC. • Type 2: Native API drivers. Driver contains Java code that calls native C/C++ methods provided by the database vendors. • Type 3: Generic network API that is then translated into database-specific access at the server level. The JDBC driver on the client uses sockets to call a middleware application on the server that translates the client requests into an API specific to the desired driver. Extremely flexible. • Type 4: Using network protocols built into the database engine talk directly to the database using Java sockets. Almost always comes only from database vendors.

7. Basic steps to use a database in Java • 1.Establish a connection • 2.Create JDBC Statements • 3.Execute SQL Statements • 4.GET ResultSet • 5.Close connections

8. 1. Establish a connection • import java.sql.*; • Load the vendor specific driver • Class.forName("oracle.jdbc.driver.OracleDriver"); • What do you think this statement does, and how? • Dynamically loads a driver class, for Oracle database • Make the connection • Connection con = DriverManager.getConnection( "jdbc:oracle:thin:@oracle-prod:1521:OPROD", username, passwd); • What do you think this statement does? • Establishes connection to database by obtaining a Connection object

9. 2. Create JDBC statement(s) • Statement stmt = con.createStatement() ; • Creates a Statement object for sending SQL statements to the database

10. Executing SQL Statements • String createLehigh = "Create table Lehigh " + "(SSN Integer not null, Name VARCHAR(32), " + "Marks Integer)"; stmt.executeUpdate(createLehigh); //What does this statement do? • String insertLehigh = "Insert into Lehigh values“ + "(123456789,abc,100)"; stmt.executeUpdate(insertLehigh);

11. Get ResultSet String queryLehigh = "select * from Lehigh"; ResultSet rs = Stmt.executeQuery(queryLehigh); //What does this statement do? while (rs.next()) { int ssn = rs.getInt("SSN"); String name = rs.getString("NAME"); int marks = rs.getInt("MARKS"); }

12. Close connection • stmt.close(); • con.close();