Presentation for java data base connectivity

Jdbc

1. Web based Java programming
(4350708)
Paresh M Tank
Lecturer in Computer Engineering Dept.
B & B Institute of Technology
Vallabh Vidyanagar-388120

2. Sub: Web based Java Programming
Code: 4350708
Unit –I
Java Data Base Connectivity (JDBC)

3. Some useful definitions
◼Package
◼In Java, a package is a grouping of related classes and interfaces
◼JAR File also known as Java archive (.jar)
◼It is file format (just like a zip format) which combines many files
and folder into one file
◼It is excellent way to distribute and deploy Java application
◼API (Application Programming Interface)
◼API is a collection of prewritten packages, classes, and interfaces
◼API is part of JDK and used an interface between two or more
applications
◼Framework
◼A framework is a group of prewritten packages, classes and
interfaces
◼Framework provides set of tools and components which
programmer can use directly to build application

4. IDE
◼IDE stands for Integrated Development Environment
◼AnIDEis a software tool that provides comprehensive facilities
to computer programmers for software development
◼AnIDEnormally consists of a source code editor, build
automation tools, debugger and helps to integrate other tools
like web server
◼List of Popular IDE for Java Programming
◼Eclipse
◼NetBeans
◼IntelliJ Idea

5. Eclipse
◼We will use Eclipse as an IDE in this subject
◼Eclipse is popular IDE for computer programmer
◼Eclipse is used in development of software using Java,
PHP, C/C++, Java Script etc
◼Eclipse is plug-inbased
◼Eclipse is open source and maintained by Eclipse.org
community

6. EclipseInstallation and
configuration
◼Before installing eclipse, you must have JDK installed in system
◼We can download latest Eclipse IDE from eclipse.org in zip file
◼To install Eclipse just unzip downloaded file. That’s it
◼To start Eclipse, double click eclipse application file from unzipped
folder
◼Once eclipse starts, it will ask for workspace folder
◼Eclipse workspaceis folder in hard drive where eclipse stores all
the projects
◼Select required perspective from top right corner of eclipse
window
◼Select Java perspective (for JavaSEproject) or
◼Select Java EE perspective (for JavaEEProject)
◼Now we can create new project from file menu

7. What is DB, DBMS and SQL?
◼What is database?
◼Database is organized collection of data stored in relational tables
◼What is DBMS?
◼DBMS is tool which provides mechanism for storing, updating,
deleting and retrieving Data from table.
◼What is SQL?
◼It is structured query language used by DBMS to manipulate
databases
◼Why database access from Java program requires?
◼Data always processed by program written in programming
language
◼DBMS does not have processing mechanism and Graphical user
interface to access data.
◼Data processing and GUI are created in programming language
like Java

8. What is JDBC?
◼JDBC stands for Java Database connectivity
◼It is javaAPI which defines standard way to access any
relational database from Java program
◼It allows you to connect to a DBMS, execute SQL queries and
retrieve results
◼JDBC is Java API (set of classes and interface) stored in following
two packages and it is part of JavaSE
◼java.sql.*;
◼javax.sql.*;
◼Database vendor implements set of interfaces from JDBC API ,
which is known as JDBC drivers
◼Driver translates the JDBC API calls into database specific call to
perform the operation
◼JDBC API is independent of RDBMS and used to connect with any
RDBMS. “Write once and run any where”

9. JDBC Architecture

10. JDBC driver
◼Database vendor (ex. Oracle corporation, MySQL community)
implements set of interfaces from JDBC API , which is known as
JDBC drivers
◼Java program connects to DBMS server to access database
using JDBC API via JDBC drivers
◼JDBC driver act as the bridge between Java application and the
DBMS
◼.jar file containing drivers need to be attached and imported. it
can be downloaded from DB vendor’s website
◼Following class, interfaces and its implementation are used to
access database
◼DriverManager(it is class)
◼Connection
◼Statement or PreparedStatementor CallableStatement
◼ResultSet, ResultSetMetaData
◼SQLException

11. JDBC API’s important classes and
interfaces
◼Folliwingare important JDBC classes and interfaces
◼DriverManager
◼DriverManagerclasscommunicates with vendor-specific drivers which
perform the real communication with the database
◼Responsible for managing the JDBC drivers available to an application
◼Hands out connection to the client code
◼Connection
◼Represents session between java application and database server
◼Statement
◼Represents SQL query statement
◼ResultSet
◼when select query executed, Table data is received in ResultSet
object
◼ResultSetMetaData
◼Provides metadata about ResultSetlike number of columns, type etc
◼SQLException→Represents runtime SQL errors in Java Program

12. JDBC Database Access steps

13. JDBC Database Access steps ….contd
◼(1) Load Driver in RAM memory
◼Class.forName(string classname);
◼classnameis provided by DB vendor
◼classnameused for oracle DB is “oracle.jdbc.driver.OracleDriver”
◼(2) Create the Connection object using DriverManager
◼Connection DriverManager.getConnection(connectionstring, username,
password);
◼Connection string (URL) is RDBMS specific
◼Connection string for oracle is "jdbc:oracle:thin:@localhost:1521:xe"
◼(3) Create the Statement object using Connection object
◼Statement CreateStatement() //it is member of connection class
◼(4) Methods of Statement to run query
◼ResultSetexecuteQuery(String query) //To run select query
◼int executeUpdate(String query) //To run insert, update, delete query
◼Boolean execute(String query) // To run all type of query

14. JDBC Database Access steps….contd
◼(5) Methods of ResultSetto retrieve records
◼These four methods are used to manage record pointer in ResultSet
◼booleannext()
◼booleanprevious()
◼booleanfirst()
◼booleanlast()
◼Following methods are used to access data of current row
◼int getInt(String Colname) or int getInt(int ColIndex)
◼String getString(String Colname), String getString(int ColIndex)
◼ResultSetMetaDataMethods
◼ResultSetMetaDatagetMetaData() //Method is member of ResultSet
◼int getColumnCount()
◼String getColumnName(int columnindex)
◼String getColumnTypeName(int columnindex)
◼(6) Close ResultSet, Statement and Connection using
Close() method of respective class

15. SQL to Java Data type mapping
SR NOSQL Type ResultSetMethod
1 BIT booleangetBoolean(int columnindex)
2 CHAR int getString(int columnindex)
3 VARCHAR String getString(int columnindex)
4 DOUBLE double getDouble(int columnindex)
5 INTEGER int getInt(int columnindex)
6 REAL float getFloat(int columnindex)
7 DATE Date getDate(int columnindex)

16. Driver names and connectionString
for popular RDBMS
RDBMS JDBC driver name URL formats
Oracle oracle.jdbc.driver.OracleDriverjdbc:oracle:thin:@hostname:port:DBname
MySQL com.mysql.jdbc.Driver jdbc:mysql://@hostname/databasename
Sybase com.Sybase.jdbc.SybDriver jdbc:Sybase:Tds:hostname/Dbname
Ms-Accesssun.jdbc.odbc.JdbcOdbcDriverjdbc:odbc:DSName
DB2 com.ibm.db2.net.DB2Driver jdbc:db2:hostname:port/DBname

17. ResultSet scrollability, updatability
and sensitivity
◼Statement and ResultSet
◼Statement stmt=conn.createStatement();
◼ResultSet rs=stmt.executeQuery(“Select *from student”);
◼Above code creates read_onlyand forward_onlyand insensitive ResultSet
object
◼To change scrollability, updatability and sensitivity use following code
◼Statement stmt=conn.createStatement(int RSType, int RSConcurrency)
RSTypecan be one of the following
◼ResultSet.TYPE_FORWARD_ONLY
◼ResultSet.TYPE_SCROL_INSENSITIVE
◼ResultSet.TYPE_SCROL_SENSITIVE
RSConcurrencycan be one of the following
◼ResultSet.CONCUR_READ_ONLY
◼ResultSet.CONCUR_UPDATABLE

18. Types of Statement
◼There are three types of Statement in JDBC
◼(1) Statement
◼How to create it-> Statement createStatement()throw SQLException
◼Statement object are used to send SQL statements to RDBMS server
◼Statement object normally used to execute SQL query without any
parameter
◼Main disadvantage of Statement is, it is not efficient when same SQL
statement is executed many times
◼It is vulnerable to SQL Injection attack due to queries are constructed
using concatenated strings
◼Methods of Statement to run query
◼ResultSetexecuteQuery(String query)
◼intexecuteUpdate(String query)
◼booleanexecute(String query)
◼ResultSetgetResultSet()
◼intgetUpdateCount()

19. Types of Statement….Contd
◼(2) PreparedStatement
◼How to create it-> PreparedStatementprepareStatement(Query)
◼PreparedStatementobject are used to send parameterized SQL query to
database
◼Ex insert into student values(?,?)
◼PreparedStatementalso provides setter methods to set query
parameters ex. setXXX(int paraindex, XXX value)
◼PreparedStatementobject represents precompiled SQL statement
◼Since PreparedStatementobject is precompiled, it efficiently execute
same query multiple times
◼It is better choice than Statement because it automatically escapes
special character and avoid SQL Injection attack
◼Methods of PreparedStatementto run query
◼intexecuteUpdate()
◼ResultSetexecuteQuery()
◼booleanexecute()
◼void setString(1, “Hello”)

20. Types of Statement….Contd
◼(3) CallableStatement
◼How to create it-> CallableStatementprepareCall(Query ) throw
SQLException
◼CallableStatementobject are used to execute database stored
procedures or functions.
◼Stored procedures offers advantages like precompilation, reduced
network traffic, increase securities due to hidden SQL from client
◼CallableStatementobject provides methods for setting up its IN and
OUT parameters and methods for executing stored procedure
◼int executeUpdate()
◼ResultSetexecuteQuery()
◼booleanexecute()
◼registerOutParameter()
◼getResultSet()

21. JDBC Architecture and it’s Model
◼JDBC architecture
◼JDBC architecture has two tier and three tier data access
model
◼Two Tier data access model
◼Three Tier data access model

22. Two Tier Access Model

23. Three Tier Access Model

24. JDBC Components
◼JDBC API
◼DriverManager, Driver, Connection
◼Statement, ResultSet, SQLException
◼JDBC Driver Manager
◼Manages different types of JDBC driver
◼DriverManagerconnects Java application to driver
◼JDBC Test Suite
◼Test suite is useful for testing new drivers
◼Test suite checks that new drivers is as per standards or not
◼JDBC-ODBC Bridge driver
◼Using this driver we can access any database on Window OS
◼Database are accessed via ODBC API provided by Window OS
◼Can be used for testing and development purpose
◼It is removed from latest Java version
◼Connection Pooling mechanism

25. JDBC Driver types
◼There are four types of JDBC drivers available
◼Type-1:JDBC-ODBC Bridge driver
◼Type-2:Native API/partly Java driver
◼Type-3:Middleware/Network protocol driver
◼Type-4:Pure Java driver/thin driver/ Native protocol driver

26. Type-1 JDBC-ODBC Bridge driver

27. Driver Type-1
advantages/Disadvantages
◼Advantages
◼Easy to install and configure ODBC driver
◼Bridge driver is part of JDK so no need to install
◼very good driver for prototyping and testing
◼Disadvantages
◼Requires ODBC driver to be installed
◼Performance is slow
◼Not suitable for production environment
◼ODBC is platform dependent

28. Type-2 Native API/Partly Java
driver

29. Driver Type-2
advantages/Disadvantages
◼Advantages
◼No ODBC dependency but depends on native library
◼Better performance and security compared to type-1
◼Disadvantages
◼Requires native libraries to be installed in client machine
◼Limited portability

30. Type-3 Middleware/Network
protocol driver

31. Driver Type-3
advantages/Disadvantages
◼Advantages
◼can be used with any DBs for which middleware server is
available
◼Allows access to remote databases
◼Platform independent
◼Disadvantages
◼Requires to install and configure middleware server
◼Network overhead
◼performance may be slower compare to type-2

32. Type-4 Pure Java driver

33. Driver Type-4
advantages/Disadvantages
◼Advantages
◼Platform independent and no extra installations
◼High performance
◼Widely used in production environment
◼Disadvantages
◼May requires specific driver version
◼Limited to DB having type-4 drivers available

34. Transaction Processing
◼Transaction processing helps to maintain data integrity and
consistency while performing DB operations
◼What is transaction?
◼Transactiongroups multiple DB operations in single unit.
◼It ensures that all operations in transaction either executed and
committed or fails and rolled back
◼Commit: Commit operation permanently save transaction
operations to the database
◼Auto-Commit: In JDBC by default, each SQL statement is
auto-committed upon execution. auto-commit can be disabled
◼Rollback: Rollback is used to undo current transaction and
restore DB to previous state
◼Savepoint: savepoint enables you to create checkpoint within
a transaction, so that transaction can be rolled back to save
point instead of rolling back whole transaction

35. Transaction Processing ….contd
◼Connection interface provides following methods to manage
transaction in JDBC
◼void setAutoCommit(booleanb);
◼void commit();
◼Savepoint setSavepoint();
◼void rollback(); or void rollback(Savepoint x);

36. Disadvantages /Limitation of JDBC
◼SQL statements are database dependent in JDBC, if it is
required to change DB then all SQL statement needs to change
◼JDBC is low level API so there is big programming overhead
◼Object relational mapping is not supported
◼Limited support for transaction processing
◼JDBC involves significant amount of boilerplate code(repeated
code)
◼JDBC programmer should know how to write query in SQL

37. Advantages of JDBC
◼JDBC is platform independent
◼JDBC offers extensive support to database vendors
◼JDBC allows efficient and high performance DB operations.
◼JDBC supports SQL standards
◼JDBC connection pooling saves time
◼JDBC is capable of reading any DB