This post is for all those who wants to get quick command on JDBC.
Like before going for interviews you surely want to go through the concept of JDBC.This will surely help those guys to learn more in short span of TIME.
JDBC(JAVA DATA BASE CONNECTIVITY), that is an interface to get connected to DB with the help of DriverManager property , and after getting connected you can enquire the various properties of DB such as rows , column and their types . However the use of JDBC is not to properties but enquire the data objects and do the further processing required for various applications.
For example you hit DB with JDBCDrivers to determine some field and suppose if the column flag found there by you is same as required by your application , you can go for processings of your systems with same set of data objects.By data objects i mean the set of row or rows that gets returned when you query the DB. Today JDBC is an essential component using in every field of JAVA as it is one of the easiest way to retrieve information from DB. Now i will explain the various steps from getting connected to DB to retrieve information from DB by writing queries.
Steps involved are as follows::
1) The first basic step while getting connected to DB is to register the DriverManager class.
How we can do this is as follows::
-----------------------------------------------------------------------------------------------
DriverManager.registerDriver("driver name");
-----------------------------------------------------------------------------------------------
Here in driver name you need to mention the driver that you are using for getting connected to DB.Every DB is having a separate set of Drivers , so we need to mention the driver name .
There is an alternative for registering and loading the driver ,that can be done as follows::
-----------------------------------------------------------------------------------------------
Class.getName("driver name ").getInsatnce();
-----------------------------------------------------------------------------------------------
2) After driver has been loaded and registered , its time to get connection with DB.
Here we need to set connection properties such as credentials that we need to get connected to DB as follows::
-----------------------------------------------------------------------------------------------
Connection conn = DriverManager.getConnection("CONN","USRNAME","PASSWORD");
-----------------------------------------------------------------------------------------------
Here USRNAME and PASSWORD are the properties for getting connected to DB and CONN represents the DB you are going to hit .So in this way you can initialize the connection object for getting connected to DB.
Now you are successfully connected to DB , so the time has come to hit DB with queries to retrieve data objects from DB.
3)For enquiring DB we have a set of STATEMENTS that helps us in retrieveing data objects.
These are basically of three types:
1)Statement
2)PreparedStatement
3)CallableStatement
These three terms are perfect with their own set of features , however first one is getting outdated now as compared to second one but last one is completely differenet from the above two.
Let us work on how these statemenst helps us in retrieveing data objects.
Lets start with Statement
It is one of the most simplest way of enquiring DB , following is the illustration for this:
-----------------------------------------------------------------------------------------------
Statement statement = connection.createStatement();
statement.executeQuery("select * from table");
-----------------------------------------------------------------------------------------------
Here firstly we created instance of Statement object now its time to execute query by passing query directly or as a string in executeQuery() method. Now the first question that will come to your mind is we are hitting DB with query but where we are storing DATA OBJECTS , we can store that in ResultSet as shown but i will discuss the concept of ResultSet later in detail:
-----------------------------------------------------------------------------------------------
ResultSet res =statement.executeQuery("select * from table");
-----------------------------------------------------------------------------------------------
Now "res" object of ResultSet is holding all the dataObjects. So in this way Statement helps in hitting DB and retrieving data objects.
Lets switch to PreparedStatement ::
PreparedStatement is great going when it comes with handling complex queries and those queries where we need to pass some values.Thats why PreparedStatement has overcome the concept of Statement but later has its own use when we need to handle simple queries.
Following is the way how to retrieve data objects with PreparedStatement
-----------------------------------------------------------------------------------------------
PreparedStatement pstmt = connection.preparedStatement("select * from tables");
ResultSet res = pstmt.executeQuery();
-----------------------------------------------------------------------------------------------
So you must have noticed the difference syntactically while using both but where is the advantage of preparedStatement in this , it can be seen as ::
-----------------------------------------------------------------------------------------------
PreparedStatement pstmt = connection.preparedStatement("select * from tables where emp_no = ?");
pstmt.setLong(1,1000);
ResultSet res = pstmt.executeQuery();
-----------------------------------------------------------------------------------------------
So in this way PreparedStatement appears to be more frequent when it comes with filtering of data from DB tables.Here in setLong(1,1000) we are passing value of emp_no as 1000 and it will retrieve all data objects with emp_no as 1000 and in a similar manner we can pass any number of paramatere using PreparedStatement.
So i am sure by now you are ready to play with DB and getting dataObjects.
Lets go further to discuss our last Statement that is CallableStatement.
As i mentioned earlier it is completely differenet from statement and PreparedStatement.
Callable statement is used when we need to call a StoredProcedure on ORACLE schema that will do similar processing for number of records.Syntactically it can be shown as:
-----------------------------------------------------------------------------------------------
CallableStatement cstmt = connection.callableStatement(" { call ORA.PAC_KAGE.PROC}");
-----------------------------------------------------------------------------------------------
Here by using callable statement we are calling StoredProcedure PROC with package PAC_KAGE under ORA SCHEMA with no inputand output parameters (under ideal case).
In this way CallableStatement comes into existence with StoredProcedure.
Till now we have discussed LOADING DRIVERS AND REGISTERING THEM , GETTING CONNECTION OBJECTS and HITTING DB to retrieve information , lets go ahead and determine how the retrieved information present in ResultSet can be put in various objects.
Concept of ResultSet::
ResultSet contains the data object that we get after hitting the DB with queries ,now the point comes how we can retrieve the information from ResultSet.
Before going on lets us get little deeper into the concept of ResultSet.
ResultSet is like a set of ROWS and a cursor that always point to the first row and this row contains the whole data object retrieved from DB.So whenever we are going to retrieve information fro ResultSet , its the first row we are accessing everytime.
Following is the way that shows how to retrieve data from ResultSet:
Conside we are using preparedStatement so the process will be like this
-----------------------------------------------------------------------------------------------
PreparedStatement pstmt = connection.preparedStatement("select * from tables where emp_no = ?");
pstmt.setLong(1,1000);
ResultSet res = pstmt.executeQuery();
-----------------------------------------------------------------------------------------------
Now we have ResultSet with the huge hell of data, so lets use the cursor of ResultSet to get desired information.
-----------------------------------------------------------------------------------------------
while(res.next()){
String emp_name = res.getString("EMP_NAME");
String emp_no = res.getLong("EMP_NO");
}
-----------------------------------------------------------------------------------------------
Here EMP_NAME and EMP_NO are the columns in table "table' and we are getting EMP_NAME and EMP_NO for every record that is present in ResultSet object as we used
next() function which will go on iterating the ResultSet until it gets empty or untill cursor comes at last+1 row where there is no data.So in this way ResultSet helps in retrieving information
what we gathered from DB.
So in this way we hit DB and retrieve the information.
Hope i was able to make you revise your all JDBC concepts byputting this BLOG as a quick reference.
But still there is lots more i need to put in this BLOG and ofcourse I do.
This was just to get a short term intro on JDBC otherwise you may find books consisting of 1000 pages for JDBC only
Posts
