1 / 19

JDBC - Java Database Connectivity

JDBC - Java Database Connectivity. What is JDBC?. JDBC provides Java applications with access to most database systems via SQL The architecture and API closely resemble Microsoft's ODBC (Open DataBase Connectivity) JDBC 1.0 was originally introduced into Java 1.1

jwhipkey
Download Presentation

JDBC - Java Database Connectivity

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

  2. What is JDBC? • JDBC provides Java applications with access to most database systems via SQL • The architecture and API closely resemble Microsoft's ODBC (Open DataBase Connectivity) • JDBC 1.0 was originally introduced into Java 1.1 • JDBC 2.0 was added to Java 1.2 and so on.. • JDBC 4.0 is the latest version. • JDBC classes are contained within the java.sql & javax.sql packages. • There are few classes • There are several interfaces

  3. JDBC Driver JDBC Driver JDBC Architecture • With JDBC, the application programmer uses the JDBC API • The developer never uses any proprietary APIs • Any proprietary APIs are implemented by a JDBC driver • There are 4 types of JDBC Drivers Java Application JDBC API JDBC DriverManager

  4. JDBC Architecture Application JDBC Driver • Java code calls JDBC library • JDBC loads a driver • Driver talks to a particular database • Can have more than one driver -> more than one database.

  5. JDBC Classes • DriverManager • Manages JDBC Drivers • Used to Obtain a connection to a Database • Date • Used to Map between java.util.Date and the SQL DATE type • Time • Used to Map between java.util.Date and the SQL TIME type

  6. JDBC Interfaces • Driver • All JDBC Drivers must implement the Driver interface. Used to obtain a connection to a specific database type • Connection • Represents a connection to a specific database • Used for creating statements • Used for managing database transactions • Used for accessing stored procedures • Used for creating callable statements • ResultSet • Represents the result of an SQL statement • Provides methods for navigating through the resulting data

  7. JDBC Interfaces • Statement • Used for executing SQL statements against the database • PreparedStatement • Similar to a stored procedure • An SQL statement (which can contain parameters) is compiled and stored in the database • CallableStatement • Used for executing stored procedures

  8. JDBC Drivers • There are 4 types of JDBC Drivers • Type 1 - JDBC-ODBC Bridge • Type 2 - JDBC-Native Bridge • Type 3 - JDBC-Net Bridge • Type 4 - Direct JDBC Driver • Type 1 only runs on platforms where ODBC is available • ODBC must be configured separately • Type 2 Drivers map between a proprietary Database API and the JDBC API • Type 3 Drivers are used with middleware products • Type 4 Drivers are written in Java • In most cases, type 4 drivers are preferred

  9. Client Database Server ApplicationGUI JDBC API Network Interface SQL Results SQL Requests SQL Results SQL Requests Network Connection JDBC API defines a set of interfaces and classes to be used for communicating with a database.

  10. Client Application Database Server JDBC Driver Database Libraries Network Interface SQL Results SQL Requests SQL Results SQL Requests Network Connection JDBC Driver: translates java into SQL. JDBC drivers are implemented by database vendors. JDBC supports the ANSI SQL92 Entry Level standard.

  11. JDBC Driver Types:

  12. Type I: JDBC-ODBC Bridge Driver Database Client Application JDBC Driver Local Disk ODBC Driver Network Interface SQL Requests SQL Results Proprietary Database Protocol Network Interface Stand-Alone Applications run on Local Machine JDBC Driver: translates java calls into ODBC calls. Harder to debug, slower, not work for applets. Inexpensive, readily available. Server

  13. Type II: Native-API-Partly Java Driver Database Client Application JDBC Driver Local Disk Native Database Libraries (Call Level Interface) Network Interface SQL Requests SQL Results Proprietary Database Protocol Network Interface JDBC Driver: use local libraries to communicate with database server. Java calls are translated into local CLI calls. Faster than the ODBC bridge, not work for applets. Server

  14. Type III: JDBC-Net-All-Java Driver Database Client Application JDBC Driver (Client) Local Disk Network Interface SQL Requests SQL Results JDBC Driver Network Protocol Network Interface Server • JDBC Driver: client calls are translated into driver-specific network protocol. • JDBC Driver: server listener translates the requests into CLI calls at server site. • All database-specific code resides on the server. • JDBC driver network protocol is not standardized. JDBC Driver (Server Listener) Native Database Libraries (Call Level Interface)

  15. Type IV: Native-Protocol-All-Java Driver Database Client Application JDBC Driver Local Disk Network Interface SQL Requests SQL Results Proprietary DB Protocol (in Java) Network Interface Server • JDBC Driver: 100% java and use no CLI native libraries. • Support applets containing the driver to be downloaded over the network, I.e., applets can communicate directly with the database.

  16. JDBC JDBC Drivers (Fig.) Type I “Bridge” ODBC ODBC Driver Type II “Native” CLI (.lib) Middleware Server Type III “Middleware” Type IV “Pure”

  17. SQL Types/Java Types Mapping SQL Type Java Type CHAR String VARCHAR String LONGVARCHAR String NUMERIC java.Math.BigDecimal DECIMAL java.Math.BigDecimal BIT boolean TINYINT int SMALLINT int INTEGER int BIGINT long REAL float FLOAT double DOUBLE double BINARY byte[] VARBINARY byte[] DATE java.sql.Date TIME java.sql.Time

  18. Steps • To execute a statement against a database, the following flow is observed • Load the driver (Only performed once) • Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); • Obtain a Connection to the database (Save for later use) • Connection con = DriverManager.getConnection("jdbc:odbc:DSN"); • Obtain a Statement object from the Connection Statement st=con.createStatement() PreparedStatementpst=con.prepareStatement(String sql) • Use the Statement object to execute SQL. Updates, inserts and deletes return Boolean. Selects return a ResultSet • ResultSetrs=st.executeQuery(String sql) • Navigate ResultSet, using data as required • public intgetInt(intcolumnNumber) • public String getString(String columnName) • Close Connection. • con.close()

  19. import java.sql.*; class Conn1 { public static void main(String[] args) { ResultSet rs; try { Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); Connection c=DriverManager.getConnection("jdbc:odbc:mydsn"); Statement st=c.createStatement(); rs=st.executeQuery("select * from stud1"); System.out.println("Name" +"\t"+"Roll"); while(rs.next()) { System.out.println(rs.getString("Name")+"\t"+rs.getInt("Roll")); } c.close(); } catch(Exception e) { System.out.println(e); } } }

More Related