Java Data Base Connectivity
- What is JDBC?
- JDBC Versions.
- JDBC Architecture.
- JDBC Drivers and its Type
- How to Connect with JDBC?
- Methods in java.sql.Connection
- Classes for sending SQL Statements
- JDBC Examples
|What is JDBC?|
Java SE Technologies provides Database Integrity through
- Java DB is Sun’s supported distribution of the open source Apache Derby database.
- Its ease of use, standards compliance, full feature set, and small footprint make it the ideal database for Java developers.
- Java DB is written in the Java programming language,
Java Data Objects (JDO)
- The Java Data Objects (JDO) API is a standard interface-based Java model abstraction of persistence.
- Application programmers can use JDO technology to directly store Java domain model instances into the persistent store (database).
The Java Database Connectivity (JDBC)
- The Java Database Connectivity (JDBC) API is the industry standard for database-independent connectivity between the Java programming language and
- A wide range of databases SQL databases and other tabular data sources, such as spreadsheets or flat files.
- The Java Database Connectivity (JDBC) API provides universal data access from the Java programming language.
- Using the JDBC 3.0 API, you can access virtually any data source, from relational databases to spreadsheets and flat files.
The JDBC 3.0 API is comprised of two packages:
- the java.sql package
- the javax.sql package, which adds server-side capabilities
JDBC is an API for accessing databases in an uniform way.
JDBC Versions available with different version of JDK as under.
|JDK Version||JDBC Version|
|JDK 1.1||JDBC 1.0 API|
|JDK 1.2||JDBC 2.0 API|
|JDK 1.4||JDBC 3.0|
|JDK 1.5||JDBC 4.0|
JDBC Architecture provides the mechanism to translate Java Statements into SQL statements
The JDBC API has two levels of interface:
Application Layer :
Application Layer allows the developer to make calls to the database via SQL and retrieve the results.
Driver Layer :
The Driver Layer handles communication with a specific driver implementation.
|JDBC Drivers and its Type|
It Convert SQL Statements into a form that a certain database can interpret.
It also Retrieve the result of SQL Statements and convert the result into equivalent JDBC API Object.
There are Four Types of Driver :
- JDBC-ODBC Bridge Driver
- Native API/Partly-Java Driver
- JDBC-Net Pure Java Driver
- Native-Protocol Pure Java Driver
Type 1 Driver
Requires ODBC driver to be installed in client Machine.
Type 2 Driver
Requires ODBC driver to be installed in client Machine.
it converts JDBC calls to native API Calls of the database
Type 3 Driver
Type 4 Driver
This is all Java Pure Driver implementations that do not requires client Machine configuration.
|How to Connect with JDBC|
JDBC Connection is just 2 Steps far from You.
1. Register Driver
- thought forName() or
- through registerDriver()
Class.forName("com.mysql.jdbc.Driver"); OR Class.forName("com.mysql.jdbc.Driver").newInstance(); OR Driver d1=new com.mysql.jdbc.Driver(); DriverManger.registerDriver(d1); OR DriverManger.registerDriver(new com.mysql.jdbc.Driver());
By Setting System Property
c:\> java -Djdbc.drivers=com.mysql.jdbc.Driver myFirstJDBCProg
2. Get Connection
The JDBC URL is a string that provides a way of identifying a database.
Syntax for Database URL :
- <protocol> in a JDBC URL is always jdbc
- <subprotocol> is the name of the database connectivity mechanism. if it is ODBC-JDBC bridge, the subprotocol must be odbc and it is type1 driver.
- <subname> is used to identify the database
Some Other Driver Details are as Under:
|RDBMS||JDBC Driver Name|
|MS Access with ODBC||
|Microsoft SQL Server||
Note :- For this you need to download the Jar file.
Click Here to download the mysql.jar for MYSQL Database Server
Click Hrer to download the ojdbc14.jar for Oracle Database Server
|Methods in java.sql.Connection|
Selected Methods in java.sql.Connection
|Statement createStatement()||Returns a statement object that is used to send SQL to the database.|
|PreparedStatement preparedStatement(String sql)||Returns an object that can be used for sending parameterized SQL statements.|
|CallableStatement prepareCall(String sql)||Returns an object that can be used for calling stored procedures.|
|DatabaseMetaData getMetaData()||Gets an object that supplied database configuration information.|
|boolean isClosed()||Reports whether the database is currently open or not.|
|void commit()||Makes all changes permanent since previous commit/rollback.|
|void rollback()||Undoes and discards all changes done since the previous commit/rollback.|
|void setAutoCommit (boolean yn)||Restores/removes auto-commit mode, which does an automatic commit after each statement. The default case is AutoCommit is on.|
|void close()||Closes the connection and releases the JDBC resources for the connection.|
|boolean isReadOnly()||Retrieves whether this Connection object is in read-only mode.|
|void setReadOnly(boolean yn)||Puts this connection in read-only mode as a hint to the driver to enable database optimizations.|
|Classes for sending SQL Statements|
JDBC API provides three classes for sending SQL Statements to Database.
- Statement Object
- PreparedStatement Object
- CallableStatement Object
- It is used to execute queries
- It Can be created by calling the createStatement() method from the Connection object.
Has three methods that can be used for querying a database:
- The ResultSet executeQuery() method executes a simple select query and returns a single ResultSet object
- The int executeUpdate() method executes the DML SQL INSERT, UPDATE and DELETE statements
- The boolean execute() method executes a SQL statement that may return true or false and can be use when creating table.
- Can be created by calling the preparedStatement() method from the Connection object
- Is used for sending queries with INPUT parameters
- Takes an SQL statement as a parameter
- The SQL statement contains placeholders ( ? ) that can be replaced by INPUT parameters
- Can be created by calling the prepareCall() method from the Connection object
- Contains the functionality of calling a stored procedure
- Is used for queries with INPUT as well as OUTPUT parameters of Stored Procedure.
- Is generated as a result of executing a statement
- Provides getXXX() methods to access data from the table where XXX refers to the data type of the parameter
- Maintains a cursor pointing to its current row of data
- It has the method next() to move to the next row
- It has the method previous() to move the previous row.
Select all Records from Customer Table : Using Statement and executeQuery()
Select specific Record from Customer Table : Using PreparedStatement and executeQuery()
insert Record in Customer Table : Using PreparedStatement and executeUpdate()
delete Records from Customer Table : Using PreparedStatement and executeUpdate()
update Records from Customer Table : Using PreparedStatement and executeUpdate()
Select all Records from procedure based on Customer Table : Using CallableStatement and executeQuery()
Select Column name , table name etc from Customer Table : Using ResultSetMetaData and getMetaData()
Select Database information of any Connection : Using DatabaseMetaData and getMetaData()