JAVA DATABASE CONNECTIVITY(JDBC)

Now let we see about how to connect java with database:

A java database connectivity is an application which will behave as an interface between the java program and the database.

Database driver are 2 types:-
1)thick driver(java->driver->odbc->database)
2)thin driver(java->driver->database)

java have 4 driver:-
1)type 1 driver acts as a bridge between jdbc and other database connectivity mechanisms such as odbc.

java prg->jdbc api->jdbc driver->odbc driver->native api ->db

2)type 2 driver converts jdbc call into database vendor specific native call in a client machine.

java prg->jdbc api->jdbc driver->native api ->db

3)type 3 driver translates jdbc calls into database server independent and middleware server specific net protocol calls which are then translated into database server specific calls by the middleware server.

java prg->jdbc api->middleware server ->db

4)type 4 driver is a pure java driver which implements the database protocol to interact directly with a database.

java prg->jdbc api->jdbc driver->db

To interact with database the necessary steps are :-
----------------------------------------------------------------
1)Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
load the driver class in the memory

2)Connection con=DriverManager.getConnection("jdbc:odbc:san","scott","tiger");
get connection from DriverManager

3)Statement st=con.createStatement();
get a statement from connection

4)ResultSet rs=st.executeQuery("select * from emp");
ResultSet interface refer memory Buffer
using statement executeQuery

Sql package has 2 classes and 8 interfaces:-
--------------------------------------------------------
classes :-DriverManager,Types
interface :-Driver,Connection,Statement,PreparedStatement,CallableStatement,
ResultSet,ResultSetMetaData,DatabaseMetaData

there are 3 methods in statement interface
1)executeQuery()->select
2)executeUpdate()->update(return 0,1)
3)execute()->insert,delete,create,drop(return true,false)

java datatype:-String,int,double,JavaObject
Jdbc datatype:-varchar,char,number,number(7,2),clob,blob

to obtain data:-
getInt(),getString(),getDouble,getFloat(),getByte(),getShort(),getBoolean(),
getLong(),getObject(),getChar()

Statement ->
It can handle one statement at one time.

PreparedStatement->
It can handle multiple sql queries.
It also used for putting the values for the sql queries at the runtime.
It is faster than the statement

Callable Statement->
It is used to call stored functions and procedures of database.

Type 4 driver:-
-------------------------

import java.sql.*;
class SqlTest
{
public static void main(String arg[])throws Exception
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","tiger");
jdbc->protocol
oracle->database
thin->type4
localhost->local machine
1521->port no
orcl->service id of oracle
scott->username
tiger->password
CallableStatement st=con.prepareCall("{call addition(?,?,?)}");
st.setInt(1,10);
st.setInt(2,20);
st.registerOutParameter(3,Types.INTEGER);
st.execute();
int n=st.getInt(3);
System.out.println(n);
}
}
-----------------



ResultSetMetaData:-

ResultSetMetaData md=rs.getMetaData();
System.out.println(md.getColumnCount());
System.out.println(md.getColumnName());

DatabaseMetaData:-

DatabaseMetaData md=rs.getMetaData();
System.out.println(md.getDriverVersion());
System.out.println(md.getDriverName());