Skip to main content
UploadLanguage (EN)Support
BusinessMobileSocial MediaMarketingTechnologyArt & PhotosCareerDesignEducationPresentations & Public SpeakingGovernment & NonprofitHealthcareInternetLawLeadership & ManagementAutomotiveEngineeringSoftwareRecruiting & HRRetailSalesServicesScienceSmall Business & EntrepreneurshipFoodEnvironmentEconomy & FinanceData & AnalyticsInvestor RelationsSportsSpiritualNews & PoliticsTravelSelf ImprovementReal EstateEntertainment & HumorHealth & MedicineDevices & HardwareLifestyle
Mindfire Solutions, profile picture
Uploaded byMindfire Solutions
PDF, PPTX2,219 views

Overview Of JDBC

The document provides an overview of JDBC (Java Database Connectivity), outlining its architecture, types of drivers, and how to establish connections with databases using DriverManager and DataSource. It also discusses the advantages of using DataSource, various statement methods, result set characteristics, and performance tips for optimizing JDBC usage. The content emphasizes the simplicity of database access through JDBC and includes insights on transaction management and best practices for developers.

Technology◦Education◦
Download as PDF, PPTX
Overview of JDBC(Java Database Connectivity) By Madhusmita Pradhan
Agenda What is JDBC(Java Database Connectivity)  Architecture Of JDBC  Walk you through some JDBC basics  Few JDBC performance tips 
What is JDBC ?     JDBC keep things simple and makes database access easy. Standardized SQL lets you talk to databases from different vendors in a uniform way. The java.sql calls go through a JDBC driver. DB vendors are creating native JDBC drivers.
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 Ideal: can change database engines without changing any application code
JDBC Drivers     Type I: “Bridge” Type II: “Native” Type III: “Middleware” Type IV: “Pure”
JDBC Drivers Type I “Bridge” Type II “Native” ODBC ODBC Driver CLI (.lib) JDBC Type III “Middleware” Type IV “Pure” Middleware Server
Transaction Transaction = more than one statement which must all succeed (or all fail) together If one fails, the system must reverse all previous actions Also can’t leave DB in inconsistent state halfway through a transaction COMMIT = complete transaction ROLLBACK = abort
Establish Connection JDBC application connects to a target data source using one of two classes:  DriverManager (Fully implemented class): Automatically loads any JDBC 4.0 drivers found within the class path, when this class first attempts to establish a connection using database URL. Application must manually load any JDBC drivers prior to version 4.0.  DataSource (Interface): A DataSource object represents a particular DBMS. Objects instantiated by classes that implement the DataSource represent a particular DBMS.
JDBC Class Usage Loads, chooses drivers DriverManager / DataSource Connects to actual database Driver Connection a single SQL statement connection session Statement ResultSet the records returned from a Statement
Database Connection URLs Syntax: jdbc:subprotocol:source  Each driver has its own subprotocol  Each subprotocol has its own syntax for the source For MySQL: jdbc:mysql://localhost:3306/TestDB
Connection using DriverManager Class.forName("com.mysql.jdbc.Driver"); // Only for JDBC version prior to 4.0 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/ test","root", "mindfire");
Connection using DataSource Context context = new InitialContext(); DataSource dataSource = (DataSource) context.lookup("jdbc/DataSource"); Connection connection = dataSource.getConnection(); // Here JNDI has been used for register a DataSource
Advantages of Using DataSource Object Programmers no longer have to hard code the driver name or JDBC URL in their applications, which makes them more portable.  DataSource properties make maintaining code much simpler in terms of maintenance  Includes additional implementations like ConnectionPooling and Distributed Transactions. 
Statement Methods ResultSet executeQuery(String) Execute a SQL statement that returns a single ResultSet.  int executeUpdate(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. Statement.getMoreResults(), used for fetch next available ResultSet, if any return true otherwise return false.  Statement stmt = con.createStatement(); ResultSet rs = stmt.executeQuery(query);
Statement Methods (Cont.) If you want to execute a Statement object many times, it usually reduces execution time to use a PreparedStatement object instead. When the PreparedStatement is executed, the DBMS can just run the PreparedStatement SQL statement without having to compile it first. PreparedStatement stmt = con.prepareStatement(SQLQueryString); ResultSet rs = stmt.executeQuery();
ResultSet A ResultSet object is a table of data representing a database result set, which is usually generated by executing a statement that queries the database.  Access data in ResultSet object through cursor(Not database cursor). This cursor is a pointer that points to one row in a resultset.  Only one ResultSet per Statement can be open at once.  A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed,or used to retrieve the next result from a sequence of multiple results. 
ResultSet Characteristics ResultSet Type:  TYPE_FORWARD_ONLY(Default): cannot be scrolled, its cursor moves forward only.  TYPE_SCROLL_INSENSITIVE: result can be scrolled, its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. The result set is insensitive to changes made to the underlying data source while it is open.  TYPE_SCROLL_SENSITIVE: result can be scrolled, its cursor can move both forward and backward relative to the current position, and it can move to an absolute position. The result set reflects changes made to the underlying data source while the result set remains open.
ResultSet Characteristics (Cont.) ResultSet Concurrency:  CONCUR_READ_ONLY(Default): The ResultSet object cannot be updated.  CONCUR_UPDATABLE: The ResultSet object can be updated. NOTE: Not all JDBC drivers and databases support concurrency. DatabaseMetaData.supportsResultSetConcurrency(), method can be used for this purpose.
ResultSet Characteristics (Cont.) ResultSet Holdability: ResultSet property holdability gives the application control over whether ResultSet objects (cursors) are closed when commit is called.  HOLD_CURSORS_OVER_COMMIT: ResultSet cursors are not closed; they are holdable: they are held open when the method commit is called. Holdable cursors might be ideal if your application uses mostly read-only ResultSet objects.  CLOSE_CURSORS_AT_COMMIT: ResultSet objects (cursors) are closed when the commit method is called. Closing cursors when this method is called can result in better performance for some applications. 
Batch Update For execute list of associate commands that may contain statements for update, insert, or delete a row; and it may also contain DDL statements such as CREATE TABLE and DROP TABLE.  con.setAutoCommit(false); PreparedStatement pstmt = con.prepareStatement("INSERT INTO products VALUES( ?)"); pstmt.setString(1, "Amaretto"); pstmt.addBatch(); pstmt.setString(1, "Hazelnut"); pstmt.addBatch(); // ... and so on for each new int [] updateCounts = pstmt.executeBatch(); con.commit();
Performance Tips Use PreparedStatement  Use Batch Update  Disable auto-commit  Always close Statement, PreparedStatement and Connection  Choose suitable(latest) JDBC driver because it directly effects the performance at DAO layer. 
Thank You