JDBC Java online Training By Garudatrainings Register for

JDBC Java online Training By Garudatrainings Register for a Free DEMO www. garudatrainings. com garudatrainings@gmail. com Phone: +1 508 -841 -6144

JDBC is an alternative to ODBC and ADO that provides database access to programs written in Java. JDBC drivers are available for most DBMS products: http: //java. sun. com/products/jdbc

JDBC Java API for connecting programs written in Java to the data in relational databases The standard defined by Sun Microsystems, allowing individual providers to implement and extend the standard with their own JDBC drivers. Tasks of JDBC: 1) establishes a connection with a database 2) sends SQL statements 3) processes the results

JDBC The JDBC API supports both two-tier and three-tier models for database access. Two-tier model - a Java applet or application interacts directly with the database. Three-tier model - introduces a middle-level server for execution of business logic: The middle tier to maintain control over data access. The user can employ an easy-to-use higher-level API which is translated by the middle tier into the appropriate lowlevel calls.

JDBC provides API or Protocol to interact with different databases. With the help of JDBC driver we can connect with different types of databases. Driver is must needed for connection establishment with any database. A driver works as an interface between the client and a database server.

JDBC have so many classes and interfaces that allow a Java application to send request made by user to any specific DBMS(Data Base Management System). JDBC supports a wide level of portability. JDBC provides interfaces that are compatible with java application

JDBC Components

Database Programming Steps Establish a connection Begin transaction Create a statement object Associate SQL with the statement object Provide values for statement parameters Execute the statement object Process the results End transaction Release resources

Using JDBC (Continued) Load the driver: The driver class libraries need to be in the CLASSPATH for the Java compiler and for the Java virtual machine. The most reliable way to load the driver into the program is: Class. for. Name(string). new. Instance();

Using JDBC (Continued) Establish a connection to the database: A connection URL string includes the literal jdbc: , followed by the name of the driver and a URL to the database String url = "jdbc: oracle: thin: @localhost: 1521: csodb"; jdbc “subprotocol”“subname” host database port Create a Connection object: Connection con = Driver. Manager. get. Connection(url, db. User, db. Passwor d);

Using JDBC (Continued) Begin the transaction con. set. Transaction. Isolation( Connection. TRANSACTION_SERIALIZABLE ); con. set. Auto. Commit( false ); Create a statement object Statement stmt = conn. create. Statement(); Associate SQL with the statement object String query. String = "create table students " + "(name varchar(30), id int, phone char(9))";

Using JDBC (Continued) Process the statement: Example statements: Result. Set rs = stmt. execute. Query(querystring); Int result = stmt. execute. Update(updatestring); Result. Set. Meta. Data rs. Meta = rs. get. Meta. Data(); Compiled queries can be processed via a Prepared. Statement object Stored procedures can be processed via a Callable. Statement object

Using JDBC (Continued) End transaction con. commit(); con. rollback(); Release resources con. close();

JDBC Component Interaction Driver Manager Creates Connection Creates Statement Creates Result. Set SQL Establish Link to DB Driver Result (tuples) Database

Two-Tier Database Access Model Java Application talks directly to the database Accomplished through the JDBC driver which sends commands directly to the database Results sent back directly to the application Application Space Java Application JDBC Driver SQL Command Result Set Database

Three-Tier Database Access Model JDBC driver sends commands to a middle tier, which in turn sends commands to database. Results are sent back to the middle tier, which communicates them back to the application Application Space Java Application JDBC Driver SQL Command Result Set Application Server (middle-tier) Proprietary Protocol Database

Type 1 Driver: JDBC-ODBC Bridge The JDBC type 1 driver which is also known as a JDBC-ODBC Bridge is a convert JDBC methods into ODBC function calls. Sun provides a JDBC-ODBC Bridge driver by "sun. jdbc. odbc. Jdbc. Odbc. Driver".

Type 1 Driver: JDBC-ODBC Bridge The driver is a platform dependent because it uses ODBC which is depends on native libraries of the operating system and also the driver needs other installation for example, ODBC must be installed on the computer and the database must support ODBC Driver

Type 1 Driver: JDBC-ODBC Bridge Type 1 is the simplest compare to all other driver but it's a platform specific i. e. only on Microsoft platform. The JDBC-ODBC Bridge is use only when there is no PURE-JAVA driver available for a particular database.

Architecture Diagram

Type 1 Driver: JDBC-ODBC Bridge Process: Java Application -> JDBC APIs JDBC Driver Manager —» Type 1 Driver —» ODBC Driver —> Database library APIs —> Database Advantage: (1) Connect to almost any database on any system, for which ODBC driver is installed. (2) It's an easy for installation as well as easy(simplest) to use as compare the all other driver.

Type 1 Driver: JDBC-ODBC Bridge Disadvantage: (1) The ODBC Driver needs to be installed on the client machine. (2) It's a not a purely platform independent because its use ODBC which is depends on native libraries of the operating system on client machine. (3) Not suitable for applets because the ODBC driver needs to be installed on the client machine.

Type 1 Driver: JDBC-ODBC Bridge JDBC: ODBC ( mainly for Desktop Applications) Use bridging technology Requires installation/configuration on client machines Not good for Web

Type 2 Driver: Native-API Driver (Partly Java driver) The JDBC type 2 driver is uses the libraries of the database which is available at client side and this driver converts the JDBC method calls into native calls of the database so this driver is also known as a Native-API driver.

Architecture Diagram

Type 2 Driver: Native-API Driver (Partly Java driver) Process: Java Application -> JDBC APIs JDBC Driver Manager —» Type 2 Driver —» Vendor Client Database library APIs —> Database Advantage: (1) There is no implantation of JDBC-ODBC Bridge so it's faster than a type 1 driver; hence the performance is better as compare the type 1 driver (JDBC-ODBC Bridge).

Type 2 Driver: Native-API Driver (Partly Java driver) Disadvantage (1) On the client machine require the extra installation because this driver uses the vendor client libraries. (2) The Client side software needed so cannot use such type of driver in the web-based application. (3) Not all databases have the client side library. (4) This driver supports all JAVA applications e. XCept applets.

Type 2 Driver: Native-API Driver (Partly Java driver) Native API Drivers (Vendor Specific drivers) Requires installation/configuration on client machines Used to leverage existing CLI libraries Usually not thread-safe Mostly obsolete now

Type 3 Driver: Network-Protocol Driver (Pure Java driver for database Middleware) The JDBC type 3 driver uses the middle tier(application server) between the calling program and the database and this middle tier converts JDBC method calls into the vendor specific database protocol and the same driver can be used for multiple databases also so it's also known as a Network. Protocol driver as well as a JAVA driver for database middleware.

Architecture Diagram

Type 3 Driver: Network-Protocol Driver (Pure Java driver for database Middleware) Process Java Application —> JDBC APIs -> JDBC Driver Manager —> Type 3 Driver —> Middleware (Server)—> any Database Advantage: (1) There is no need for the vendor database library on the client machine because the middleware is database independent and it communicates with client.

Type 3 Driver: Network-Protocol Driver (Pure Java driver for database Middleware) (2) Type 3 driver can be used in any web application as well as on internet also because there is no anv software require at client side. (3) A single driver can handle any database at client side so there is no need a Separate driver for each database. (4) The middleware server can also provide the typical services such as connections, auditing, load balancing, logging etc.

Type 3 Driver: Network-Protocol Driver (Pure Java driver for database Middleware) Disadvantage: (1) An Extra layer added, may be time consuming. (2) At the middleware develop the database specific coding, may be increase complexity.

Type 3 Driver: Network-Protocol Driver (Pure Java driver for database Middleware) Network API Calls middleware server, usually on database host Very flexible & allows access to multiple databases using one driver Only need to download one driver But it's another server application to install and maintain.

Type 4 Driver: Native-Protocol Driver (Pure Java driver directly connected to database) The JDBC type 4 driver converts JDBC method calls directly into the vendor specific database protocol and in between do not need to be converted any other formatted system so this is the fastest way to communicate quires to DBMS and it is completely written in JAVA because of that this is also known as the "direct to database Pure JAVA driver".

Architecture Diagram

Type 4 Driver: Native-Protocol Driver (Pure Java driver directly connected to database) Disadvantage: (1) There is a separate driver needed for each database at the client side. (2) Drivers are Database dependent, as different database vendors use different network protocols.

Type 4 Driver: Native-Protocol Driver (Pure Java driver directly connected to database) Network Protocol Driver (used for Network based Applications) Pure Java Drivers Use Java networking libraries to talk directly to database engines need to download a new driver for each database engine.

All JDBC Types Diagram Type I “Bridge” JDBC Type II “Native” Type III “Middleware” ODBC Driver CLI (. lib) Middleware Server Type IV “Pure” Alex Chaffee

JDBC Steps Driver. Manager Driver Connection Statement Result. Set Garudatrainings. com +1 508 -841 -6144

JDBC URLs jdbc: subprotocol: source each driver has its own subprotocol each subprotocol has its own syntax for the source jdbc: odbc: Data. Source e. g. jdbc: odbc: Northwind jdbc: msql: //host[: port]/database e. g. jdbc: msql: //foo. nowhere. com: 4333/accounting Garudatrainings. com +1 508 -841 -6144

Driver. Manager Loads database drivers, and manages the connection between the application and the driver Connection get. Connection (String url, String user, String password) Connects to given JDBC URL with given user name and password Throws java. sql. SQLException returns a Connection object Garudatrainings. com +1 508 -841 -6144

Driver Translates API calls into operations for a specific data source Garudatrainings. com +1 508 -841 -6144

Connection A Connection represents a session with a specific database or a session between an application and a database Within the context of a Connection, SQL statements are executed and results are returned. Can have multiple connections to a database Also provides “metadata” -- information about the database, tables, and fields Also methods to deal with transactions

Obtaining a Connection String url = "jdbc: odbc: Northwind"; try { Class. for. Name ("sun. jdbc. odbc. Jdbc. Odbc. Driver"); Connection con = Driver. Manager. get. Connection(url); } catch (Class. Not. Found. Exception e) { e. print. Stack. Trace(); } catch (SQLException e) { e. print. Stack. Trace(); } Garudatrainings. com +1 508 -841 -6144

Connection Methods Statement create. Statement() returns a new Statement object Prepared. Statement prepare. Statement(String sql) returns a new Prepared. Statement object Callable. Statement prepare. Call(String sql) returns a new Callable. Statement object Why all these different kinds of statements? Optimization. Garudatrainings. com +1 508 -841 -6144

Statement A Statement object is used for executing a static SQL statement and obtaining the results produced by it. An SQL Statement to perform a query or update operation. Garudatrainings. com +1 508 -841 -6144

Statement Methods Result. Set execute. Query(String) Execute a SQL statement that returns a single Result. Set. int execute. Update(String) Execute a SQL INSERT, UPDATE or DELETE statement. Returns the number of rows changed. boolean execute(String) Execute a SQL statement that may return multiple results. Why all these different kinds of queries? Optimization. Garudatrainings. com +1 508 -841 -6144

Result. Set A Result. Set provides access to a table of data generated by executing a Statement. Logical set of columns and rows returned by executing an SQL statement Only one Result. Set per Statement can be open at once. The table rows are retrieved in sequence. A Result. Set maintains a cursor pointing to its current row of data. The 'next' method moves the cursor to the next row. you can’t rewind

Result. Set Methods boolean next() activates the next row the first call to next() activates the first row returns false if there are no more rows void close() disposes of the Result. Set allows you to re-use the Statement that created it automatically called by most Statement methods Garudatrainings. com +1 508 -841 -6144

Result. Set Methods Type get. Type(int column. Index) returns the given field as the given type fields indexed starting at 1 (not 0) Type get. Type(String column. Name) same, but uses name of field less efficient int find. Column(String column. Name) looks up column index given column name Garudatrainings. com +1 508 -841 -6144

Result. Set Methods String get. String(int column. Index) boolean get. Boolean(int column. Index) byte get. Byte(int column. Index) short get. Short(int column. Index) int get. Int(int column. Index) long get. Long(int column. Index) float get. Float(int column. Index) double get. Double(int column. Index) Date get. Date(int column. Index) Time get. Time(int column. Index) Timestamp get. Timestamp(int column. Index) Garudatrainings. com +1 508 -841 -6144

Result. Set Methods String get. String(String column. Name) boolean get. Boolean(String column. Name) byte get. Byte(String column. Name) short get. Short(String column. Name) int get. Int(String column. Name) long get. Long(String column. Name) float get. Float(String column. Name) double get. Double(String column. Name) Date get. Date(String column. Name) Time get. Time(String column. Name) Timestamp get. Timestamp(String column. Name) Garudatrainings. com +1 508 -841 -6144

is. Null In SQL, NULL means the field is empty Not the same as 0 or “” In JDBC, you must explicitly ask if a field is null by calling Result. Set. is. Null(column) Garudatrainings. com +1 508 -841 -6144

Sample Database Employee ID 1 2 3 4 5 Last Name Davolio Fuller Leverling Peacock Buchanan Garudatrainings. com +1 508 -841 -6144 First Name Nancy Andrew Janet Margaret Steven

SELECT Example Connection con = Driver. Manager. get. Connection(url, "alex", "8675309"); Statement st = con. create. Statement(); Result. Set results = st. execute. Query("SELECT Employee. ID, Last. Name, First. Name FROM Employees"); Garudatrainings. com +1 508 -841 -6144

SELECT Example (Cont. ) while (results. next()) { int id = results. get. Int(1); String last = results. get. String(2); String first = results. get. String(3); System. out. println("" + id + ": " + first + " " + last); } st. close(); con. close(); Garudatrainings. com +1 508 -841 -6144

Mapping Java Types to SQL Types SQL type Java Type CHAR, VARCHAR, LONGVARCHAR NUMERIC, DECIMAL BIT TINYINT SMALLINT INTEGER BIGINT REAL FLOAT, DOUBLE BINARY, VARBINARY, LONGVARBINARY DATE TIMESTAMP String java. math. Big. Decimal boolean byte short int long float double byte[] java. sql. Date java. sql. Timestamp Garudatrainings. com +1 508 -841 -6144

Database Times in SQL are notoriously unstandard Java defines three classes to help java. sql. Date year, month, day java. sql. Time hours, minutes, seconds java. sql. Timestamp year, month, day, hours, minutes, seconds, nanoseconds usually use this one Garudatrainings. com +1 508 -841 -6144

Modifying the Database use execute. Update if the SQL contains “INSERT” or “UPDATE” Why isn’t it smart enough to parse the SQL? Optimization. execute. Update returns the number of rows modified execute. Update also used for “CREATE TABLE” etc. (DDL) Garudatrainings. com +1 508 -841 -6144

Transaction Management Transactions are not explicitly opened and closed Instead, the connection has a state called Auto. Commit mode if Auto. Commit is true, then every statement is automatically committed default case: true Garudatrainings. com +1 508 -841 -6144

set. Auto. Commit Connection. set. Auto. Commit(boolean) if Auto. Commit is false, then every statement is added to an ongoing transaction you must explicitly commit or rollback the transaction using Connection. commit() and Connection. rollback() Garudatrainings. com +1 508 -841 -6144

Connection Managers Hint: for a large threaded database server, create a Connection Manager object It is responsible for maintaining a certain number of open connections to the database When your applications need a connection, they ask for one from the CM’s pool Why? Because opening and closing connections takes a long time Warning: the CM should always set. Auto. Commit(false) when a connection is returned Garudatrainings. com +1 508 -841 -6144

Optimized Statements Prepared Statements SQL calls you make again and again allows driver to optimize (compile) queries created with Connection. prepare. Statement() Stored Procedures written in DB-specific language stored inside database accesed with Connection. prepare. Call() Garudatrainings. com +1 508 -841 -6144

JDBC Class Diagram

Examples

Check Connection

Print Result. Set

Update Records

Execute Example

Metadata Connection: Database. Meta. Data get. Meta. Data() Result. Set: Result. Set. Meta. Data get. Meta. Data() Garudatrainings. com +1 508 -841 -6144

Result. Set. Meta. Data What's the number of columns in the Result. Set? What's a column's name? What's a column's SQL type? What's the column's normal max width in chars? What's the suggested column title for use in printouts and displays? What's a column's number of decimal digits? Garudatrainings. com +1 508 -841 -6144

Result. Set. Meta. Data Does a column's case matter? Is the column a cash value? Will a write on the column definitely succeed? Can you put a NULL in this column? Is a column definitely not writable? Can the column be used in a where clause? Is the column a signed number? Is it possible for a write on the column to succeed? and so on. . . Garudatrainings. com +1 508 -841 -6144

Database. Meta. Data What tables are available? What's our user name as known to the database? Is the database in read-only mode? If table correlation names are supported, are they restricted to be different from the names of the tables? and so on… Garudatrainings. com +1 508 -841 -6144

JDBC 2. 0 Scrollable result set Batch updates Advanced data types Blobs, objects, structured types Rowsets Persistent Java. Beans JNDI Connection Pooling Distributed transactions via JTS

Scrollable Result Sets In JDBC 1. 0, result sets could be navigated in only one direction (forward) and starting at only one point (first row) Since JDBC 2. 0, the cursor can be manipulated as if it were a array index Methods exist for reading both forward and backward, for starting from any row, and for testing the current cursor location.

JDBC 2. 0 Navigation Methods for Scrollable Result Sets boolean next ( ) Advances the cursor to the next row. boolean previous ( ) Moves the cursor back one row. boolean first ( ) Moves the cursor to the first row. boolean last ( ) Moves the cursor to the last row. void before. First ( ) Moves the cursor before the first row, usually in anticipation of calling next ( ) void after. Last ( ) Moves the cursor after the last row, usually in anticipation of calling previous ( ) boolean Moves the cursor to the specified absolute (int row) row. Specifying a negative number moves the cursor relative to the end of the result set;

JDBC 2. 0 Navigation Methods for Scrollable Result Sets (contd. ) boolean Moves the cursor forward or relative (int row) backward the number of rows specified. boolean is. Before. First ( ) True if the cursor is before the first row. boolean is. After. Last ( ) True if the cursor is after the last row. boolean is. First ( ) True if the cursor is positioned on the first row. boolean is. Last ( ) True if the cursor is positioned on the last row.

Creating Scrollable Result Sets Statement object created with parameters to indicate specific capabilities Connection. create. Statement() method can have up to three parameters: result. Set. Type – type of scrolling to be used result. Set. Concurrency – indicates whether the result set can be updated result. Set. Holdability – specifies whether to close cursors when a commit is done Example stmt = con. create. Statement( Result. Set. TYPE_SCROLL_INSENSITIVE, Result. Set. CONCUR_READ_ONLY);

Constants in Result Sets Cursor Related Constants TYPE_FORWARD_ONLY JDBC 1. 0 -style navigation in which the cursor starts at the first row and can only move forward. TYPE_SCROLL_INSENSITIVE All cursor positioning methods are enabled; the result set doesn’t reflect changes made by others in the underlying table. TYPE_SCROLL_SENSITIVE All cursor positioning methods are enabled the result set reflects changes made by others in the underlying table.

Constants in Result Sets (contd. ) Updating Record Sets CONCUR_READ_ONLY The results set won’t be updatable CONCUR_UPDATABLE Rows can be added and deleted, and columns can be updated. Closing Cursors HOLD_CURSORS_OVER_COMMIT Do not close cursors after a commit is done. CLOSE_COURSORS_AT_COMMIT Close cursors when a commit is done.