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

Java SE Technologies provides Database Integrity through

Java DB

• 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.

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

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.

JDBC Connection is just 2 Steps far from You.

1. Register Driver

Programmatically either

• thought forName() or
• through registerDriver()

Examples

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());

Manually

By Setting System Property

Examples

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>:<subprotocol>:<subname>

• <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:

Driver Name
	com.mysql.jdbc.Driver
Database URL format :
	jdbc:mysql://hostname/databaseName
Eg.
Class.forName("com.mysql.jdbc.Driver");
Connection c1=DriverManager.getConnection("jdbc:mysql:
//localhost:3306/abcDB","root","admin");

Driver Name
	oracle.jdbc.driver.OracleDriver
Database URL format :
	jdbc:oracle:thin@hostname:portnumber:databaseName
Eg.
Driver d1=new oracle.jdbc.driver.OracleDriver();
DriverManager.registerDriver(d1);
Connection c1=DriverManager.getConnection("jdbc:oracle:
thin:@192.168.1.10:1521:orcl","scott","tiger");

Driver Name
	com.jdbc.odbc.JdbcOdbcDriver
Database URL format
	jdbc:odbc:databaseName
Eg.
Class.forName("com.jdbc.odbc.JdbcOdbcDriver");
Connection c1=DriverManager.getConnection("jdbc:odbc:databaseName");

Driver Name
	COM.ibm.db2.jdbc.net.DB2Driver
Database URL format :
	jdbc:db2:hostname:portnumber/databaseName

Driver Name
	org.apache.derby.jdbc.EmbeddedDriver
Database URL format :
	jdbc:derby://localhost:1527/sample
Eg.
Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
Connection conn= DriverManager.getConnection("jdbc:derby:C:\\Program Files
\\Sun\\JavaDB\\demo\\databases\\toursdb");

Driver Name
	com.microsoft.jdbc.sqlserver.SQLServerDriver
Database URL format :
	jdbc:sqlserver://localhost;user=MyUserName;password=*****;
	jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks;integratedSecurity=true;
Eg.
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
Connection conn= DriverManager.getConnection("jdbc:sqlserver:
//localhost:1433;databaseName=AdventureWorks;integratedSecurity=true;");

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

Selected Methods in java.sql.Connection

JDBC API provides three classes for sending SQL Statements to Database.

• Statement Object
• PreparedStatement Object
• CallableStatement Object

Statement :

• 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.

PreparedStatement :

• 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

CallableStatement :

• 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.

ResultSet:

• 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()