1 / 87

Java Database Connectivity (JDBC)

Java Database Connectivity (JDBC). Introduction to JDBC. JDBC is a simple API for connecting from Java applications to multiple databases. Lets you smoothly translate between the world of the database, and the world of the Java application .

matana
Download Presentation

Java Database Connectivity (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. Java Database Connectivity (JDBC)

  2. Introduction to JDBC • JDBC is a simple API for connecting from Java applications to multiple databases. • Lets you smoothly translate between the world of the database, and the world of the Java application. • The idea of a universal database access API is not a new one. • For example, Open Database Connectivity (ODBC) was developed to create a single standard for database access in the Windows environment. • JDBC API aims to be as simple as possible while providing developers with maximum flexibility.

  3. Understanding JDBC Drivers Java Application • To connect to a database • , you first need a JDBC Driver. • JDBC Driver: • set of classes that interface with a specific database engine. JDBC Driver Manager JDBC- ODBC Bridge Vendor Specific JDBC Driver Vendor Specific ODBC Driver Database Database Diagram Source: Marty Hall, Core Web Programming (Prentice Hall.)

  4. JDBC Drivers • JDBC drivers exist for every major database including: • Oracle, SQL Server, Sybase, and MySQL. • We will use MSAccess Data base,Microsoft’s ODBC driver,Sun’s JDBC-ODBC bridge.

  5. }do in pgm Java Program uses JDBC API JDBC-ODBC bridge converts these calls to ODBC }do manually ODBC Data Source Your tables in MSAcess

  6. java.sql • The package comprising the core JDBC API is called java.sql • We use a simple MS Access database, which means that the inbuilt JDBC-ODBC bridge driver can be employed • If you wish to experiment with other databases, place the appropriate JDBC driver within folder java\jre\lib\ext.

  7. Creating an ODBC Data Source

  8. Creating an ODBC Data Source First , it is necessary to register the database as an ODBC Data Source. Once this has been done, the database can be referred to by its Data Source Name (DSN).

  9. steps to set up your own ODBC Data Source(only for ODBC databases!)

  10. Simple Database Access

  11. Using JDBC to access a database requires several steps

  12. Using JDBC to access a database requires several steps Load the Database Driver • class is used to hold methods that operate upon other classes in order to furnish details of their characteristics • forName  method of class

  13. Using JDBC to access a database requires several steps Establish a Connection to the Database • declare a Connection reference • call static method getConnection of class DriverManager to return a Connection object • Method getConnection takes three String arguments: • a URL-style address for the database; • a user name; • a password.

  14. Using JDBC to access a database requires several steps Establish a Connection to the Database • Address format: jdbc:<sub-protocol>:<data-source> • <sub-protocol> specifies a database connection service (i.e., a driver) • <data-source>  provides all the information needed by the service to locate the database

  15. Using JDBC to access a database requires several steps Establish a Connection to the Database Assuming that our Finances database is indeed local and that we did not set a user name or password for this database If this same database were remote

  16. Using JDBC to access a database requires several steps Create a Statement Object and Store its Reference • Call createStatement () method of our Connection object • Save the address of the object returned in a Statement reference.

  17. Using JDBC to access a database requires several steps Run a Query/Update and Accept the Result(s) • Class Statement has methods to execute queries: • executeQuery ()  to retrieve data from a database • executeUpdate() to change the contents of the database • The former method returns a ResultSet object, • latter returns an integer that indicates the number of database rows that have been affected by the updating operation.

  18. Using JDBC to access a database requires several steps Run a Query/Update and Accept the Result(s)

  19. Using JDBC to access a database requires several steps Run a Query/Update and Accept the Result(s)

  20. Using JDBC to access a database requires several steps Manipulate/Display/Check Result(s) • The only method of ResultSet that we need to make use of at present is next, which moves the ResultSet cursor/pointer to the next row in the set of rows referred to by that object. • We can retrieve data via either the field name or the field position using the appropriate getXXX () method

  21. Manipulate/Display/Check Result(s)

  22. Manipulate/Display/Check Result(s) Repeat Steps 4 and 5 as Required Close the Connection

  23. Modifying the Database Contents

  24. Modifying the Database Contents • Database Update Statements : • INSERT • DELETE • UPDATE • Update statements are submitted via the executeUpdate() method of statement interface

  25. Modifying the Database Contents

  26. Example:

  27. Example:

  28. Example:

  29. Example:

  30. Example:

  31. Example:

  32. Example:

  33. Transactions

  34. Transactions • one or more SQL statements that may be grouped together as a single processing entity • We want either all the statements or none of them to be executed. • Java provides the Connection interface methods • commit  used at the end of a transaction to commit/finalise the database changes • rollback  used to restore the database to the state it was in prior to the current transaction

  35. Transactions • By default, JDBC automatically commits each individual SQL statement that is applied to a database. • In order to change this default behaviour so that transaction processing may be carried out, we must first execute Connection method setAutoCommit with an argument of false • We can then use methods commit and rollback to effect transaction processing.

  36. Transactions

  37. Meta Data

  38. Meta Data • 'data about data‘ • Two categories of meta data available through the JDBC API: • data about the rows and columns returned by a query (i.e., data about ResultSet objects); • • data about the database as a whole. The first of these is provided by interface ResultSetMetaData, an object of which is returned by the ResultSet method getMetaData

  39. Meta Data • data about the rows and columns is provided by interface ResultSetMetaData, an object of which is returned by the ResultSet method getMetaData • Data about the database as a whole is provided by interface DatabaseMetaData, an object of which is returned by the Connection method getMetaData

  40. Meta Data • Information available from a ResultSetMetaData object includes the following: • the number of fields/columns in a ResultSet object; • the name of a specified field; • the data type of a field; • the maximum width of a field; • the table to which a field belongs.

  41. Meta Data • SQL types is represented in class java.sql.Types INTEGER and VARCHAR are particularly commonplace, the latter of these corresponding to string values.

  42. Meta Data • The example coming up makes use of the following ResultSetMetaData methods, which return properties of the database fields held in a ResultSetMetaData object.

  43. Meta Data

More Related