What is odbc ?

Open database connectivity. Introduced by Microsoft. Limitations are only works for windows
applications. But works for any database any language.

But it is not recommended for java since the main advantage of java is plat form independent and
also these odbc drivers are developed native c and c++ languages. So there is lot of performance loss
while converting calls from java to native language and native language calls to java.

Jdbc API

Jdbc API is the standards defined by java which database vendors will implement these interfaces to
develop their driver softwares.

Jdbc API contains java.sql and javax.sql packages. Java.sql contains basic classes and interfaces to
communicate with database and javax.sql package contains advanced jdbc classes like
ConnectionPool, rowSet etc.

JDBC Drivers:

Type1 – jdbc odbc bridge driver

This driver converts jdbc calls to odbc specific calls and odbc converts driver calls to database specific
calls. This is compatible with any language , any database but only works for windows.

Type2 – driver

This driver converts the jdbc calls to database specific native library calls. Performace is good.but this
is database dependent and platform dependent.

Type 3 driver:

Middle ware driver. Driver converts jdbc calls to middleware calls. Platform independent, database
independent. Only limitation is performace is little slow.

Type4 driver:

Thin driver. Drirect communication with database. Only limitation is database dependent.

What is the difference between thin driver and thick driver ?

If there is any extra component to communicate with the database that is called thick driver. Else it
is called thin driver. Type4 driver is the thin driver.

Section 2: Develop jdbc application:

Class.forName(“sun.jdbc.odbc.jdbcodbcDriver”)

This line will automatically load and register driver class. In jdbcOdbcDriver class there is a static
block which registers driverClass. As soon as we load the class the static blocks will execute and
drivers will be registered. So the above single statement do both loading and registering the class.

From jdbc 4.0 and java 1.6 onwards there is no need to write this line. As soon as the jvm finds the
class in classpath it will automatically loads and registers the driver.

Current version of jdbc is 4.2

25:steps to connect to oracle database with type1 driver

For type 1 driver we need to configure DSN(data source name). And the DSN service is only available
for windows XP. Also support for type1 driver is stopped since java 8

To create the connection object after loading and registering driver

Connection conn = DriverManager.getConnection(“connection

Connection string contains 3 parts

Databaseprotocol:subprotocol:subname  jdbc:odbc:dsnservice1

Database protocol is jdbc for any connection

Subprotocol is driver specific protocol

And for the connection remember that DriverManager is implemented class for the interface in jdbc
API.

Sun.jdbc.odbc.jdbcOdbcConnection is returned by the getConnection method. This Class is part of

Statement Object:

Class.forName(“sun.jdbc.odbc.jdbcOdbcDriverClass”)

Connection conn = DriverManager.getConnection(“url”,”username”,”password”);

Statement st = conn.createStatement();

Then there are three types of execute statements are there

St.executeQuery();

St.executeUpdate();

St.execute();
St.executeQuery returns ResultSet object

St.executeUpdate returns int value which represents the number of rows updated or deleted.

St.execute can be used for both select and update queries. Returns true for select queries and flase
for update queries. We can use st.getResultSet method if the st.execute method returned true, and
st.getUpdatedRowCount method if the st.execute() method returned false.

If we used st.executeQuery method for non select queries like update and delete what happens ?

If we used type1 driver it throws SQLException

If we used type4 driver it don’t throw any exception ,and returns an empty result set. When we try
to access that empty result set we will get SQLException with message fetch out of sequence

If we used st.executeUpdate method for select queries like select what happens ?

If we used type 1 driver it will throw SQLException with message no row count was produced.

If we used type4 driver we will get the number of rows got in result set.

If we used executeUpdate () method for DDL queries like create table, alter table etc what happens ?

If it is type1 driver you get -1

If it is type4 driver you get 0

Result set:

Result set is like cursor. At start it lies at before first record (BFR position) . And it ends at After Last
record.

ResultSet rs = st.executeQuery();

The result has a next() method which checks if there is next record exists or not. It returns Boolean.
If the record exists we will get the result by using rs.getXXX() methods.

We can pass String or int arguments to getXXX() method. If we pass string it matches the column
name in the record and gets that value. If we pass the int the value at that coloum number will be
returned.

It is always recommended to pass colum number instead of column names.

Also remember that one statementObject is always associated with one ResultSet. If we use the
same statementObject for different resultSet the resultSet will be closed automatically.

For example

Statement st = conn.getStatement();

ResultSet rs1 = st.excuteQuery()

ResultSet rs2 = st.executeQry();

If(rs1.next()){

Do some thing // will throw an SQLException saying result set already closed.

Closing resources:

We have to close the opened resources in the reverse order we opened.

We opened

resultSet,statementObject and connection

remember that resultSet is associated with statementObject so if there is no statementObject there

so  st.close()  will also close result set

same way conn.close() also closes st and rs.

34:

Working with type 2 driver.

Type 2 driver communicates with the database using vendor specific native libraries. Some database
provides these native libraries and some databases not. For example sql don’t provide native
libraries.

For oracle we need to add the ojdbc14,6,7 for oracle 10g,11g,12c and add to the class path.

And connection string will be <jdbcprotocol>:<databaseprotocol>:<subprotocol>

Jdbc:oracle:oci8:XE

We don’t need to download the jdbc driver , driver will be automatically available once downloaded
the oracle database. That will be in database libraries in c drive or where the database is installed.

This XE is database SID. This will be orcl, orcl1 sometimes.

We can know this by query  select * from global-name;

We can see that in tnsnames.ora file.

35:

Type4 driver:
same with type2 driver . except for connection string. Here jdbc4 directly communicates with
database so there will be no oci8

Jdbc:oracle:thin:localhost:1521:XE
Type4 driver for oracle is called thin driver.

36:

Type 3 driver:

This is middleware driver. This is the only driver that platform independent and database
independent. But it is little costly . it is also the one driver follows 3 tier architecture. It is most
commonly used in enterprise environments.

There are different middlewares available in the market. One of them are IDS. This IDS middleware
internally uses type1, type2 and type3 drivers to communicate with the database.

First we have to install the middleware.

The we have to configure the dsn since middle ware internally uses type1 driver too.

And the driver is class is  ids.sql.IDSDriver

Connection string is  jdbc:id://localhost:12/conn?dsn=sysdnstyp3

The driver class will be available in jdk13drv.jar will be available. This jar will be available in IDS
installation directory, which we have to set in class path

37:

Type 5 driver:

Data direct driver:

This is the driver developed by progress data direct. It is licenced. We have to download the setup
file first and then place the jar files in the class path which is oracle.jar for oracle database.

The dirver class will be located in

Com.ddtech.jdbc.oracle.OracleDirver

The connection string will be

Jdbc:datadirect:oracle://localhost:1521:xe

40:

How automatic loading of driver class is working in jdbc4.0 or driver ojdbc6

In ojdbc6 or later there is a META-INF/services folder is available. There is Driver.txt file. Form there
java will know where is driver class is, which free us from loading and registering the driver.

And from java7 we can use try with resources to close the connection.

41,42:

Use executeUpdate() method for create and drop quires. Because they are non-select queries
43:

Commit is true by default in jdbc

52:

To get records by acending or decending order

Select * from employee order by desc

Select * from employee order by asc

Select query will be execute with st.executeQuery. this will return ResultSet object.

By using rs.next which returns Boolean we can know if there next records exists or not

55:

Select * from employees where ename like ‘d%’  will select employees whose names starts with d

57:

Consider database have

Eno Ename EAddr Esal  coloums

In java if you execute the query in this way

Select * from employees  you will get the exact resultset from database and you can continue
processing the data as it is.

But if you execute the query in this way

Select ename eno esal eaddr from employees

The the result set will be in the order of the query you provided in the java code

rs.getString(1) will give the ename , not eno.

Another case

If you give the query  select ename esal from employees the result will contain only those two
coloums.

rs.getString(3) will give you SQLException

section 7:

select count(*) from employees  gives number records in the table

select max(esal) from employees  gives maximum salary from the employee table
select min(esal) from employees gives min salary from the employee table

These are called aggregate functions.

We use st.executeQuery for these aggreagate functions.

We will get the result set and by result set by using rs.getInt() we will get number of records of the
table. rs.getDouble() for esal

Sub queries:

How you get the employees list who have minimum salary

Select * from employee where esal in (select min(esal) from employee);

Get the Nth highest salary

Select * from (select eno,ename,eaddr,esal,rank() over(order by esal DESC) ranking from employees)
where ranking =”+n;

Section 8:

How you use st.execute() method

Execute() method will be used for both select and non-select

St.executeQuery(non-select query)

Type1 driver  SQLException with message ResultSet not produced.

Type4 driver  Empty result set. If you try to access the result set SQLException with
message fetch out of sequence

St.executeUpdate(select query)

Type1 driver – SQLException  no row count update was produced.

Type4 driver – number of rows selected will be printed.