Monday, November 24, 2014

Differnet steps required to Connect Java Applicattions with Database Servers using JDBC Driver


JDBC Tutorial, steps for database connection using JDBC Driver. custom database driven application development, how to connect java applications with database DBMS using JDBC, JDBC Connection cloud database, cloud database hosting using jdbc, jdbc connection diagram, java web development, free java software, free training tutorial, free learn jdbc, database administration
 In this java learning tutorial, we shall learn how to use JDBC (Java Database Connectivity) Technology to connect java applications with different database DBMS like MS Access, MS SQL Server, MySQL Server, Oracle SQL Server and Cloud database etc. There are many ways to use the JDBC driver for connection and access the database. We shall discuss only basic steps which are required to accomplish database connection. There are eight basic steps which are necessary to connect a application with database. These steps are as under 

Step 1:- Import Required Package
First step is to import the required package "java.sql.*" . This package has useful classes and interfaces to communicate with database.

import java.sql.*;

Step 2:- Load Driver
In this step load suitable driver for underlying database DBMS. As we learn, in my previous post about JDBC there are different JDBC Driver types for different DBMS.
For example,

• For MS Access, JDBC -ODBC Driver is used which is available with j2se.
       Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

• For Oracle SQL Database Server, load the following driver. This driver is available as free on Oracle Website for downloading.

        Class.forName(“oracle.jdbc.driver.OracleDriver”);

Step 3:- Define Connection URL
For creating connection with Database, we need to specify the URL / address of a database.
 
For Microsoft Access use DSN of Database with jdbc - odbc driver.

String conURL = “jdbc:odbc:personDSN”;

For Oracle Database Server:
String  conURL ="jdbc:oracle:thin:@myhost:1521:orcl";


Step 4:- Establish Connection With DataBase
Use DriverManager() method to get the connection object. The URL of the database is passed to the getConnection method.

Connection con = DriverManager.getConnection(conURL);

If DataBase requires username & password, you can use the overloaded version of getConnection method as shown below:

con = DriverManager.getConnection(conURL, usr, pwd);


For example to connect Oracle database scott with password tiger use below 

Connection conn = DriverManager.getConnection
                 ("jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger");


Step 5:- Create Statement
As connection established, a Statement object is obtained from Connection object. Once you get a statement object, you can use it for various kinds of SQL queries like SELECT, INSERT, DELETE, UPDATE etc.

Statement stmt = con.createStatement( );

Step 6:- Execute a Query
After getting statement object, the next step is to pass the SQL statements and to execute them. Generally below mentioned two methods are used for executing SQL queries

o executeQuery(sql) method:  This method is used for SQL SELECT queries. It returns the ResultSET object that contains the results of the query which will be further processed as per requirement.

String sql = “SELECT * from tablename”;
ResultSet rs = stmt.executeQuery(sql);

o executeUpdate(sql) method: This method is used for executing an update statement like INSERT,
UPDATE or DELETE. It returns an Integer value which represent the number of rows updated

String sql = “INSERT INTO tablename ” + “(columnNames) Values (values)” ;
int count = stmt.executeUpdate(sql);

Step 7:- Process Results of the Query
The obtained ResultSet provides various get methods which takes column name or column index to access data. The ResultSet maintains the data in the form of tables (rows & columns)
Below are some requirement / features of ResultSet
  • Table first row has index 1, not 0.
  • ResultSet next method returns true or false depending upon whether the next row is available or not available and if next row is available then it moves next
  • Always remember to call next() method at-least once
  • Various getter methods of  ResultSet are used to get data. For example, below mentioned code will iterate over the whole ResultSet.
         while ( rs.next() ){
         String name = rs.getString(“columnName”);            //Retrieved data by using column name
         String name = rs.getString(1); // or Retrieved data by using column index
                                   }

Step 8:- Close the Connection
After retrieving data, close the database connection because an opening connection is expensive which may cause issue for other database requests, postpone this step if additional database operations are required.

con.close();

Example Codes with Steps:
Below is the example Code with steps, it will show clearly the steps and codes required to connect the java applications with required Database DBMS and how to use Getter methods to retrieve the data from database.

// File JdbcEx.java
//Step 1: Import required package which is java.sql.

import java.sql.*;

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

//Step 2: load driver in try clause

Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);

//Step 3: define the database connection URL

String url = “jdbc:odbc:personDSN”;

//Step 4: establish the connection using DriverManager.getConnection() method.

Connection con = DriverManager.getConnection(url);

//Step 5: create Statement

Statement st = con.createStatement();

//Step 6: preapare & execute the query

String sql = “SELECT * FROM Person”;    // Retrieve data from Person table in database
ResultSet rs = st.executeQuery(sql);

//Step 7: process the results
while(rs.next()){

// For example, the database table Column name are “name, address and phoneNum”, specified these column in the getString() method.

String name = rs.getString(“name”);
String add = rs.getString(“address”);
String pNum = rs.getString(“phoneNum”);
System.out.println(name + “ ” + add + ” ” + pNum);    // show output result on console.
}

//Step 8: After activity done, close the database connection

con.close();
}catch(Exception sqlEx){
System.out.println(sqlEx);        // this code will show any exception or error if any occur.
}
} // end main
} // end class

Related Topic:

What is JDBC and different JDBC Driver Types and JDBC Benefits

70 comments:

  1. All MCA and B.Tech final year students participated in this campus recruitment drive. One student has been selected from that campus drive.PCS Global Pvt Ltd - A Platform for Freshers BE /B-Tech/MCA PERENNATION COMPUTER SOLUTIONS GLOBAL PRIVATE LIMITED (PCS Global) has been providing WEB DEVELOPMENT service for over 3+ years.
    http://www.pcsglobal.in/

    ReplyDelete

  2. This is really an awesome article. Thank you for sharing this.It is worth reading for everyone.
    Visit us:
    Web Designers London
    Website Design London

    ReplyDelete
  3. As a web development and design service provider Webzin InfoTech provides optimum solutions to customer by helping them to achive their goals and make available their services and products in the online market.

    ReplyDelete
  4. It is very useful information ... Thanks for sharing.


    ReplyDelete
  5. Managing the database and share with an active server is essential for their safe, easy and effective sharing. You just need to create an MS access folder for storing the data and later on simply upload it on the server for easy usage. For more information please click here

    ReplyDelete
  6. Very efficiently written information. It will be helpful to everyone who will use it, including me. Almost certainly I’m likely to bookmark your blog.

    ReplyDelete
  7. Excellent! Thanks for this - I've been looking at this feature for ages. Followed your instructions and it works a treat!

    ReplyDelete
  8. Thanks for sharing sir. I have followed all given instructions. Your all described techniques and tips help me to achieve my desired task. Now a days Java Applications with Database Servers and many other Web Related Professional Stuff is rampant on highest scale. No doubt every professional man is going towards IT to have proper solutions for the sake of spreading his or her business. Thank you for this post.

    ReplyDelete
  9. Thanks for sharing sir. I have followed all given instructions. Your all described techniques and tips help me to achieve my desired task. Now a days Java Applications with Database Servers and many other Web Related Professional Stuff is rampant on highest scale. No doubt every professional man is going towards IT to have proper solutions for the sake of spreading his or her business. Thank you for this post.

    ReplyDelete
  10. I was working and suddenly I visits your site frequently and recommended it to me to read also. The writing style is superior and the content is relevant. Thanks for the insight you provide the readers!
    facebook entrar
    facebook entrar iniciar sesion
    facebook en español descargar
    facebook entrar direto agora

    ReplyDelete
  11. Great information provided. I appreciate your work. I like the way you write. Awesome, keep it up.

    Website Design Company in Ilford
    Web Development in Ilford

    ReplyDelete
  12. Thanks for sharing excellent information. Your site is very cool . I am impressed by the details that you have on this site.SEO Companies | Digital Marketing Company Bangalore

    ReplyDelete
  13. Web development is a broad term for the work involved in developing custom-built web applications and websites for the Internet.

    ReplyDelete
    Replies
    1. Great and useful information .. thanks for sharing with us .. I love to visit your blog.

      Delete
  14. Its really very Best message...Its should be provide the lot information..thank you for giving the best information,......Website Designing Company Bangalore | Website Development Companies Bangalore

    ReplyDelete
  15. Great informational resource as always!I really love the way of explaining this post.
    App Development company | website design melbourne

    ReplyDelete
  16. Nice post, thanks for sharing. We are one of the web application development company in India.

    ReplyDelete
  17. I can set up my new idea from this post. It gives in depth Thanks for this valuable information for all,..
    SEO services pakistan

    ReplyDelete
  18. Great information but guys yuo need more information then you can read from here: http://flymediatech.blogspot.in/

    ReplyDelete
  19. Nice Information you have written here. Really Great Stuff. I keep it bookmark for our future purpose.

    We are also Web development Company in India who provide the services in Android App Development in Nagpur , SEO Company in Nagpur , Ecommerce Website Development in Nagpur. Visit Us today

    AceZed IT Solution

    ReplyDelete
  20. Web design development company which is not only specialized in the field of website development and designing but also specialized in the field of e-commerce development and Web promotions.
    web development or mobile app development

    ReplyDelete
  21. We are a honest and committed team of Marketing, Advertising and IT Consultants. Our specialization is in Business Development, Advertising, Web Designing, Web development, Graphic Designing, Mobile Apps Development, Digital Marketing Expert for more technically complex projects. We're a passionate group of digital marketing and advertising experts.
    Ecommerce

    ReplyDelete
  22. Nice artilce. I learned a lot from this.
    Keep up the good work :)

    offshore magento development

    ReplyDelete
  23. We are a honest and committed team of Marketing, Advertising and IT Consultants. Our specialization is in Business Development, Advertising, Web Designing, Web development, Graphic Designing, Mobile Apps Development, Digital Marketing Expert for more technically complex projects. We're a passionate group of digital marketing and advertising experts.
    Ecommerce

    ReplyDelete
  24. Our Java Application Development solution service is there to help the clients to receive better and more accomplished java solutions upwards right from the grassroots level. Our experienced team of professional programmers has ensured that our client's receive excellent java applications solutions. Our accomplished team has forever been highly knowledgeable with highest skill-sets to create products and software application with Java Technology Development while mastering its nuances and its very own specialties.

    ReplyDelete
  25. Thank you for share your nice information, Plz visit us: web-development

    ReplyDelete
  26. Thank you so much for sharing this one very helpful and peaceful info share by you, Thanks again.
    - PSD to HTML

    ReplyDelete
  27. Thanks for taking time for sharing this article, it was excellent and very informative. Its really very useful of all of users. I found a lot of informative stuff in your article. Keep it up.Web Designing Company Bangalore | Website Development Bangalore

    ReplyDelete
  28. I really appreciate your efforts, knowledge, the way you explained this connect java application with database topic.

    Calgary web design

    ReplyDelete
  29. Well it was very good to see your article... Thanks a lot for providing Information Regarding Java.. Bangalore Web Design Companies | Web Developers Bangalore

    ReplyDelete
  30. The necessary as well as important fact about SEO South Florida web developer is that they will work and help your online business to get noticed in a very short span of time. The most important point as well as necessary things about web developers are the way they can help your Web Design South Florida business to grow and manage all important things.

    ReplyDelete
  31. I am glad to find your impressive way of writing the post.Thanks for sharing the post.Also see my website. IT

    consultant company


    ReplyDelete
  32. I hope everyone like this information they shared as I do. Really great information.
    Web Development

    ReplyDelete
  33. Hi,

    This is similar to your article.

    Our Team of experienced Web Developers offer maintenance procedures, irrespective of the nature and the location factor of the PHP Software Development to ensure smooth functioning of the activities within your organization and giving support projects.

    Ecommerce Portal Development
    Online Portal Web Development

    ReplyDelete
  34. Really nice information which you share here. Thanks for sharing your post.

    ReplyDelete
  35. Good efforts. All the best for future posts. I have bookmarked you. Well done. I read and like this post. Thanks. best mba colleges in punjab

    ReplyDelete
  36. Nice Information you have written here. Really Great Stuff. I keep it bookmark for our future purpose.
    Digital Marketing Agency Thailand
    Web Development Bangkok

    ReplyDelete
  37. Valuable for information if there is any other regarding this kindly revert me back on this


    Website Development

    ReplyDelete
  38. Superb explanation & it's too clear to understand the concept as well, keep sharing admin with some updated information with right examples.Keep update more posts.

    Manpower Consultancy in Chennai

    ReplyDelete
  39. Very Nice...Thanks for sharing this great post.If you want to develop any app then you can visit :http://www.quuick.in/android-app-development-hyderabad.php

    ReplyDelete
  40. Hi, I read your blog this is very informative. Thanks for share this blog. You can find dedicated Web designers, Mobile Application Developers, SEO and marketing specialists will tailor make the solution you want for your business. Know more about click here – www.hvantagetechnologies.com

    ReplyDelete
  41. really nice article !!! I had learned so many things reading this article, thanks a lot for sharing the information. Our site also provides an innovative and interactive Mobile App Services and if you want more ideas to refer our site: www.hvantagetechnologies.com

    ReplyDelete
  42. Thank you for sharing the information. Yberry Infosystem is a Mobile and Web Solutions Technology Services company. We specialize in Mobile Apps and Web Devlopment, Ecommerce Solutions. Visit: www.yberryinfosystem.com

    ReplyDelete
  43. "Thanks for this blog. provided great information. All the details are explained clearly with the great explanation. Thanks for this wonderful blog. Step by step processes execution are given clearly.Know the details about different thing."!!!
    ios app development company

    ReplyDelete
  44. ERPTREE Offering Oracle fusion HCM online training, Oracle Fusion SCM Online Training, Oracle fusion financials online training, Oracle fusion hcm training, Oracle fusion scm training, Oracle fusion financials training, Oracle fusion dba online training in Hyderabad, Bangalore,Gurgon, Noida, India, Dubai, UAE, USA, Kuwait, UK, Singapore, Saudi Arabia, Canada, Delhi, Chennai, Kolkata, Pune, Mumbai, Ahmedabad.


    Oracle Fusion HCM Training

    ReplyDelete
  45. New web site is looking good. Thanks for the great effort.Pakistani Bridal Dresses

    ReplyDelete
  46. Helpful blog great Information...

    Mobile Application Development Company

    We offer mobile app developers for hire at hourly or project basis very affordable rate Android application development company in India. Android ios app Developers in India and USA.

    ReplyDelete

Please enter relevant question and information.