1 / 23

Basics of JDBC

Basics of JDBC. Session 14. Objectives. Describe ODBC Discuss what is JDBC Discuss why we need JDBC Describe the java.sql package in brief Discuss types of drivers Explain the anatomy of a JDBC program. Explain the concept of database connectivity. Database.

paiget
Download Presentation

Basics of JDBC

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Basics of JDBC Session 14

  2. Objectives • Describe ODBC • Discuss what is JDBC • Discuss why we need JDBC • Describe the java.sql package in brief • Discuss types of drivers • Explain the anatomy of a JDBC program • Explain the concept of database connectivity Basics of JDBC / 2 of 23

  3. Database • A database contains data that is in an organized form • Client/Server applications make extensive use of database programming • Activities may involve opening a connection, communicating with a database, executing SQL statements and retrieving query results • Standardized APIs are available that simplify database programming. Examples of these are ODBC and JDBC Basics of JDBC / 3 of 23

  4. ODBC • Provides functions to insert, modify and delete data and obtain information from the database • Open DataBase Connectivity (ODBC) is an Application Programming Interface (API) provided by Microsoft for accessing databases Basics of JDBC / 4 of 23

  5. How ODBC connection takes place? • Application could be a GUI program • Makes use of ODBC to interact with databases • Driver Manager is part of Microsoft ODBC and is used to manage various drivers in the system Access Driver Access Database Oracle Database Oracle Driver ODBC Interface Driver Manager Application … … Basics of JDBC / 5 of 23

  6. Need for JDBC • A literal translation of the ODBC C interface into a Java API would not be desirable • ODBC mixes simple and advanced features together and has complex options even for simple queries • A Java API like JDBC is needed in order to enable a “pure Java” solution • JDBC is portable • JDBC is a standard interface for Java programmers to access relational databases • ODBC uses a C interface that has lot of drawbacks Basics of JDBC / 6 of 23

  7. JDBC • Defines a set of API objects and methods to interact with databases • JDBC is a must for all Java applications that access data stored in external data providers like SQL Server, Oracle or Access • JDBC is a Java Database Connectivity API that is a part of the Java Enterprise API Basics of JDBC / 7 of 23

  8. JDBC Driver Types (1) • ODBC is not readily convertible to Java • Sun provides a bridge driver to access ODBC data sources from JDBC • This is called the JDBC-ODBC Bridge plus ODBC driver • JDBC-ODBC Bridge plus ODBC Driver Database Server Oracle ODBC Driver JDBC - ODBC bridge Database Server SQL Server ODBC Driver DB-client Basics of JDBC / 8 of 23

  9. JDBC Driver Types (2) • Native API partly-Java Driver • JDBC calls are converted into calls on the client API for DBMS • This driver uses JavaNativeInterface(JNI) that calls the local database APIs • The Native APIs partly-Java driver calls the Native Database Library that accesses the database • This driver like the ODBC driver needs binary code on the client machine Vendor Specific Protocol JDBC Driver (Java and Binary Code) Database Server Basics of JDBC / 9 of 23 DB-client

  10. JDBC Driver Types (3) • JDBC-Net pure Java driver • Uses a networking protocol and middleware to communicate with the server • Server then translates the messages communicated to DBMS specific function calls • Specific protocol used depends on the vendor • No need for client installation • Allows access to multiple back-end databases Database Server Networking protocol & middleware JDBC Driver (Pure Java Driver) Database Server SQL Server Basics of JDBC / 10 of 23 DB-client

  11. JDBC Driver Types (4) • Native-protocol pure Java driver • 100% Java enabled and does not use CLI libraries • Capable of communicating directly with the database • Converts JDBC calls into network protocols such as TCP/IP and other proprietary protocols used by DBMS directly • Since many of these protocols are proprietary, the database vendors themselves will be the primary source of usage Vendor Specific Protocol JDBC Driver (Pure Java Driver) Database Server Basics of JDBC / 11 of 23 DB-client

  12. JDBC architecture (1) Java Program JDBC Driver SQL command Results Application Server Database Basics of JDBC / 12 of 23

  13. JDBC architecture (2) • Application Layer – developer makes calls to database through SQL and retrieves results • Driver layer – handles all communication with a specific driver implementation • Four main Java interfaces that every Driver layer must implement are : • Driver, Connection, Statement and ResultSet • The JDBC API interface comprises of two layers: Basics of JDBC / 13 of 23

  14. JDBC architecture (3) Application layer Driver layer Implements Interfaces Driver Connection Statement ResultSet Basics of JDBC / 14 of 23

  15. JDBC components • Driver Manager – to load specific drivers for an application • Driver • Data Source – User application interacts with this to get results • Application – here the JDBC methods are used to execute SQL and get results Basics of JDBC / 15 of 23

  16. The java.sql package • JDBC API defines a set of interfaces and classes used for communicating with the database • These are contained in the java.sql package • Classes included in this package are : • Date, DriverManager, DriverPropertyInfo, Time, TimeStamp, Types • Interfaces included are : • Callable Statement, Connection, DatabaseMetaData, Driver, PreparedStatement, ResultSet, ResultSetMetaData, Statement Basics of JDBC / 16 of 23

  17. Creating a JDBC application 1 Begin Import the java.sql package 2 Load and Register the driver 3 Create a Connection object 4 5 Create a Statement object 6 Execute the statement 7 Close Connection 8 Basics of JDBC / 17 of 23 End

  18. Example Output Basics of JDBC / 18 of 23

  19. Using SQL (1) • To find out the number of employees who joined together on the same day and whose job_id is equal to 5: SELECT count(*) FROM Employee WHERE job_id=5 GROUPBY hire_date • To retrieve the name, phone, email and phone number from the table colleagues: SELECT name, email, phone FROM colleagues Basics of JDBC / 19 of 23

  20. Using SQL (2) • Instead they return an integer representing the number of rows affected • Assume we need to add a new record – • INSERT into COFFEE VALUES(‘French_Roast’,00049,8.99,0,0) • SQL DML statements do not return the results as ResultSets Basics of JDBC / 20 of 23

  21. Using SQL (3) • Data Definition Language statements are used to create tables, add columns to the existing tables; delete tables and so on • CREATE TABLE Emp(emp_name VARCHAR(25),emp_no VARCHAR(4),emp_age number); • The LIKE operator is used with SQL statements to compare two strings • SELECT * FROM employee WHERE emp_id LIKE ‘A%’ Basics of JDBC / 21 of 23

  22. Example (1) Basics of JDBC / 22 of 23

  23. Example (2) Output Basics of JDBC / 23 of 23

More Related