Since
2008
Since
2008
Since
2008
JDBC is an unofficial acronym for java database connectivity. JDBC helps us to connect to a database and execute SQL statements against a database. JDBC api provides set of interfaces and there are different implementations respective to different databases.
I wrote an article describing difference between abstract class and interface. Lot of people are asking for example with a nice usage scenario. JDBC is a direct and excellent example. JDBC provides a set of interfaces and it is a contract between java developers and database providers. Every database is unique in its own way and we dont want to create separate programs for each database.
Java application developers program using the jdbc interfaces and database developer provide the implementation for th jdbc interfaces. We add the respective implementation to the application and using the corresponding database. A classic use of java interfaces.
JDBC was first introduced as part of JDK 1.1. JDBC api has its own version and latest version being JDBC 4.1 part of Java SE 7. JDBC 4.1 is a maintenance release of JSR 221.
Using JDBC in our application can be segregated into three major steps as below,
//step 1 - getting connection
Connection connection = DriverManager.getConnection(
"jdbc:jdbcDriver:database",
databaseUsername, dbpassword);
//step 2 - query execution and getting result
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT * FROM employee");
//step 3 - parsing the result from ResultSet
while (resultSet.next()) {
int empid = rs.getInt("empid");
String empname = rs.getString("empname");
}
Above is a general and basic illustration of using JDBC.
Getting a database connection consists of two substeps loading driver and creating connection.
There are four types of JDBC drivers,
In JDBC 4.0 this step is not required and all the all divers found in classpath are loaded automatically. When we want to load a java class into JVM we use Class.forName and the same is followed here. We choose a type of JDBC driver and use Class.forName to load it.
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(Exception e){
System.out.println("Exception is loading the driver."+e);
}
There are two classes that allows to connect to a database DriverManager and DataSource. DriverManager is most popular and easier to use.
Key object is Connection which allows to interact with database and execute queries. DriverManager has a method named getConnection and we use this to get an instance of Connection.
For getConnection we need to pass username, password of database and a connection url. JDBC Connection URL varies based on the database used. Following are for some of the popular databases,
try{
Connection connection = DriverManager.getConnection(connectionUrl,dbUserName,dbPassword");
}
catch( SQLException sqlEx ){
System.out.println( "Exception in getting a connection"+sqlEx);
}
Using the Connection obtained we execute SQL statements. Before executing the SQL we need to compose the statements. There are three difference ways for creating a sql statement and they are by using Statement or PreparedStatement or CallableStatement.
Statement:
This is used for creating a static sql statement. Why do we call this as static? Everytime we use this Statement object the sql query is compiled, irrespective of it is used earlier or not. Generally this Statement is used in simple scenarios and when we are sure a same query will never be executed again.
PreparedStatement:
We can use PreparedStatement to create a pre-compiled SQL statement. It gives better performance compared to Statement as it is precompiled and this is applicable when the same sql will be executed multiple times.
PreparedStatement pstmt = connection.prepareStatement("UPDATE
EMPLOYEE SET name = ? WHERE empid = ?");
pstmt.setString(1, "Joe");
pstmt.setInt(2, 10829);
CallableStatement:
This is different from the above two statement. This is specially used for executing stored procedures.
Once the statement is creating using any one of the above three statements then we call their respective execute methods and get the result back from database.
ResultSet is the object returned when a select query is performed. ResultSet represents multiple records of data. We can read records from a ResultSet using a cursor. Cursor is pointer to a record. By default, after we read a record the pointer moves to the next record then we can read the following records. TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE are three types using which the cursor is controlled. This section calls for a detailed separate article :)
I am going to write a series of articles on JDBC and will cover the following JDBC Driver Types, PreparedStatement, CallableStatement, ResultSet, Cursors, Transactions, Stored Procedures, Metadata. Please add a comment to let me know if you want me to write on any other thing specific in JDBC.
package com.javapapers.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCBasicExample {
private Connection connection = null;
private Statement statement = null;
private ResultSet resultSet = null;
public static void main() throws Exception {
try {
// using mysql driver - download and add in classpath
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost/employeedb?"
+ "user=dbusername&password=dbpassword");
statement = connection.createStatement();
resultSet = statement.executeQuery("select * from employee");
while (resultSet.next()) {
String employeename = resultSet.getString("name");
int empid = resultSet.getString("empid");
Date dateofBirth = resultSet.getDate("dateofbirth");
System.out.println("Employee Name: " + employeename);
System.out.println("Employee ID: " + empid);
System.out.println("Employee Date of Birth: " + dateofBirth);
}
} catch(SqlException sqe) {
sqe.printStacktrace();
}
finally {
try {
resultSet.close();
statement.close();
connection.close();
}catch(Exception e){}
}
}
}
Comments are closed for "JDBC Introduction".
NICE ARTICLE
thank you very much. can you explain me the difference between comarable,clonable and comarator interfaces
Hi Joe,
“Database developer provide the implementation for the jdbc interfaces”. This means for every DB (oracle,Mysql,etc) we need a different driver right ? and also what is the difference between these drivers and the four types of driver u mentioned ? Pls clarify.
Nanri :)
Very Nice.. especially the diagram you explained is basically the thing we lack in interviews..:)
Was looking for a article on JDBC for my friends.. Got it.. Thanks..
Hi Joe,
it was nice article. it refreshed the JDBC knowledge. Can you please tell us when we have to choose which typw of databse drivers. which one is good in which situation.
Thanks!
Useful article, Thank you…..
very nice topic
thanks
Another six by you joe. Thanks a lot. Waiting for article on different type of jdbc connector.
hey buddy, u r an excellent techie….carry on man, u rocks….!!!
It would be so nice if you can throw some more focus on TYPE_FORWARD_ONLY, TYPE_SCROLL_INSENSITIVE and TYPE_SCROLL_SENSITIVE
with example..
thanks Joe .. useful Article
Hey Joe,
Thanks for sharing.If you don’t mind would you please categorize the DB drivers performance wise
Hey Joe,
Fantastic.nice article on jdbc…U rock man….
hi joe,
How the abstract class and interface
works in the JDBC scenario.pls explain it.
Hi Joe,
Thank you so much for your article.
Y.Jayaram Reddy.
Thanks a lot.. very nice one!!
Hi JOE, Clearly mentioned on Statements and resultset. Can you please explain difference between PreparedStatement and CallableStatement.
Thanks.
hi joe,
how to insert images and document in database.I got few information related to that (i.e)need use clob or blob datatypes in database.but i dont know how to process the image in java(i.e) serialiseable.please help me..
Thanks,
Arjun
very article..
Joe, good article. it would be good if you provide new features or feature list in version based for the jdbc release. Also mension the available Jdk version for each jdbc release. Thx
Joe, recently I came across your blog and found it very interesting.
Regarding JDBC, as explained Drivers provided by Databases contains the actual implementation regarding getting the connection, what about the driver types. Does all the databases need to provide implementation for all the driver types.
Please write an article on Rowset as well
awesome Sir…really find useful…
Easy to understand. Thank you very much.
Hi Joe
Thanks for sharing the knowledge.
Nicely explained about the jdbc stuff
JDBC is not this less… Need to cover more! You may need to write another post on this.
Performance tuning JDBC is a major topic to tocuh.
very nice article joe.thanks
Really nice blog which guided and explained me in creating DB connection using JDBC. Congrats Joe
Hi Joe,
Most of the times we see loading jdbc odbc driver. Can you give scenarios where other jdbc drivers can be used?
Hi,
Very nice..
Thanks for your explanation.
plz explain this line
DriverManager.getConnection(“jdbc:oracle:thin:@localhost:1521:xe”);
nc article.:)
[…] Update and Delete in this server side application. We know how to do a basic web application using java jdbc and deploy it in server. So just for a change, here I have used PHP […]
Joe nice article.
Explained nicely and in a very decent manner.
Thanks for your posts. :)
can anyone plz explain what a savepoint is? and how to handle dead locks in transactions?
Savepoint is a place in a transaction till where you can rollback the changes done. Without savepoint, if you rollback all the changes will be reverted. With savepoint, changes done till savepoint will be preserved.
Handling dead locks in transaction is a large topic and requires a separate article for itself :-)
Very clear and very helpful
Hi,
Is some limitations of mysql server or mysql jdbc driver?
If i work with resultset more 30 minutes, i get less records than really contain in database table. And have no errors or warrnings.
Thanks !! Greatly Explained !! Really helped me to prepare myself for the Web Technologies Exam tomorrow !!
Thanks
~Srikanth
thank you sir very clean and conceptual cover all concepts Excellent atricle
Thanks Sir ,very nice and useful article
i have one query , when i try the above example i need to create connection of static , please clarify this
i am satisfied this notes thanks…
you are d0ing a marvellous job… joe.. well done.maverick.. i haven’t seen such a chaste usage of web to spread knowledge and showcase one’s technical prowess.. keep doing .
nice site
Hi Joe,
can you explain.why we’re using Class.forName in Jdbc connection
very very great platform to learn
thank you
GG mate! Nice well-structured article.
type 4, pure native java driver are best for use ,,,, they are portable and platform independent ,, other drivers are not platform independent ,, you will need native code and that work’s with the help of Java Native Interface (JNI) ,, so for different – 2 OS there will be different drivers ,,, ,, But not sure ,, :)
Dear joe
please write article on Resultset,preparestatment,callablestatment,cursors and transactions
Can you please write about rowsets.