JDBC Steps ? Basic steps in writing a JDBC Application

This section gives you brief description of JDBC Steps for making connection with the database, executing the query and showing the data to the user. In this application we have connected to the MySQL database and retrieved the employee names from the dat


 

Ads

Tutorials   
JDBC Video Tutorial: Create, Read, Update and Delete (CRUD) Operation examples JDBC Video tutorial - How to read Data from MySQL Database? JDBC Video tutorial - How to delete Data in a MySQL Database? Solving java.lang.ClassNotFoundException: com.mysql.jdbc.Driver exception JDBC Video tutorial - How to update Data in a MySQL Database? JDBC Video Tutorial- How to insert data into MySQL database table? JDBC Video Tutorial: How to download JDBC Driver for MySQL? JDBC Tutorial - Writing first JDBC example and running in Eclipse Java Cross Join MySQL Database Connectivity Example In Java Change Column Name in MySQL Java Mysql Connection Example JDBC ODBC Connection In Java ResultSet In Java Prepared Statement Example JdbcRowSet interface Java JdbcRowSet Example First Step towards JDBC! JDBC Training, Learn JDBC yourself Jdbc Get Column Names JDBC Example with MySQL JDBC Prepared Statement Update JDBC Prepared Statement Insert JDBC Prepared Statement Example JDBC Prepared statement Close JDBC Prepared Statement Addbatch JDBC Nested Resultset JDBC-Odbc Connection JDBC-Odbc Connectivity JDBC Insert Preparedstatement JDBC Insert Null JDBC Get Metadata JDBC Insert Record Jdbc Insert Statement JDBC Meta Data Get tables Jdbc Mysql Connection String Jdbc Mysql Connection Url JDBC Next Jdbc Get Connection JDBC Get Int JDBC Exception JDBC Get Row Count JDBC Functions JDBC Fetch JDBC Execute Update Example JDBC Execute Statement JDBC Execute Query JDBC Drive For Mysql JDBC Connectivity Code In Java JDBC connection timeout
Ads

JDBC Steps – Basic steps in writing a JDBC Application

     

This section gives you brief description of JDBC Steps for making connection with the database, executing the query and showing the data to the user. In this application we have connected to the MySQL database and retrieved the employee names from the database. Here are the JDBC Steps to be followed while writing JDBC program:

  • Loading Driver
  • Establishing Connection
  • Executing Statements
  • Getting Results
  • Closing Database Connection

Before explaining you the JDBC Steps for making connection to the database and retrieving the employee from the tables, we will provide you the structure of the database and sample data.

Here is the sql script to create table and populate the table with data:

-- Table structure for table `employee`

CREATE TABLE `employee` (

`employee_name` varchar(50) NOT NULL,

PRIMARY KEY (`employee_name`)

);

INSERT INTO `employee` (`employee_name`) VALUES

('Deepak Kumar'),

('Harish Joshi'),

('Rinku roy'),

('Vinod Kumar');

Data inserting in MySQL database table:

mysql> insert into employee values('Deepak Kumar');
Query OK, 1 row affected (0.24 sec)

mysql> insert into employee values('Harish Joshi');
Query OK, 1 row affected (0.05 sec)

mysql> insert into employee values('Harish Joshi');
ERROR 1062 (23000): Duplicate entry 'Harish Joshi' for key 1
mysql> insert into employee values('Rinku roy');
Query OK, 1 row affected (0.03 sec)

mysql> insert into employee values('Vinod Kumar');
Query OK, 1 row affected (0.04 sec)

mysql> select *from employee;
+---------------+
| employee_name |
+---------------+
| Deepak Kumar |
| Harish Joshi |
| Rinku roy |
| Vinod Kumar |
+---------------+
4 rows in set (0.04 sec)

Here is the code of java program that retrieves all the employee data from database and displays on the console:

/*
Import JDBC core packages. 
Following statement imports the java.sql package, 
which contains the JDBC core API. 
*/ 
import java.sql.*;

public class RetriveAllEmployees{
  public static void main(String[] args) {
  System.out.println("Getting All Rows from employee table!");
  Connection con = null;
  String url = "jdbc:mysql://localhost:3306/";
  String db = "jdbc";
  String driver = "com.mysql.jdbc.Driver";
  String user = "root";
  String pass = "root";
  try{
  Class.forName(driver);
  con = DriverManager.getConnection(url+db, user, pass);
  Statement st = con.createStatement();
  ResultSet res = st.executeQuery("SELECT * FROM  employee");
  System.out.println("Employee Name: " );
  while (res.next()) {
  String employeeName = res.getString("employee_name");
  System.out.println(employeeName );
  }
  con.close();
  }
  catch (ClassNotFoundException e){
  System.err.println("Could not load JDBC driver");
  System.out.println("Exception: " + e);
  e.printStackTrace();
  }
  catch(SQLException ex){
  System.err.println("SQLException information");
  while(ex!=null) {
  System.err.println ("Error msg: " + ex.getMessage());
  System.err.println ("SQLSTATE: " + ex.getSQLState());
  System.err.println ("Error code: " + ex.getErrorCode());
  ex.printStackTrace();
  ex = ex.getNextException(); 
// For drivers that support chained exceptions
  }
  }
  }
} 

Explanation of JDBC Steps:

  • Loading Driver
    Loading Database driver is very first step towards making JDBC connectivity with the database. It is necessary to load the JDBC drivers before attempting to connect to the database.
    The JDBC drivers automatically register themselves with the JDBC system when loaded. Here is the code for loading the JDBC driver:
    Class.forName(driver).newInstance();
     
  • Establishing Connection
    In the above step we have loaded the database driver to be used. Now its time to make the connection with the database server. In the Establishing Connection step we will logon to the database with user name and password. Following code we have used to make the connection with the database:
    con = DriverManager.getConnection(url+db, user, pass);
     
  • Executing Statements
    In the previous step we established the connection with the database, now its time to execute query against database. You can run any type of query against database to perform database operations. In this example we will select all the rows from employee table. Here is the code that actually execute the statements against database:
    ResultSet res = st.executeQuery( "SELECT * FROM  employee" );
     
  • Getting Results
    In this step we receives the result of execute statement. In this case we will fetch the employees records from the recordset object and show on the console. Here is the code:
       while  (res.next()) {
       String employeeName  = res.getInt( " employee_name " );
       System.out.println( employeeName  );
       }
      
  • Closing Database Connection
    Finally it is necessary to disconnect from the database and release resources being used. If you don’t close the connection then in the production environment your application will fail due to hanging database connections. Here is the code for disconnecting the application from database:
      con.close();
     

In this section you learnt about the JDBC Steps necessary for performing database operations.

Output of program:

C:\vinod>javac RetriveAllEmployees.java

C:\vinod>java RetriveAllEmployees
Getting All Rows from employee table!
Employee Name:
Deepak Kumar
Harish Joshi
Rinku roy
Vinod Kumar

Download full code along with JDBC driver

Advertisements

Share on Google+Share on Google+

JDBC Steps ? Basic steps in writing a JDBC Application

Posted on: February 23, 2008 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Advertisements

 

Discuss: JDBC Steps ? Basic steps in writing a JDBC Application   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:3
jp
September 6, 2011
error

run: Getting All Rows from employee table! Could not load JDBC driver Exception: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver java.lang.ClassNotFoundException: com.mysql.jdbc.Driver at java.net.URLClassLoader$1.run(URLClassLoader.java:202) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader.java:190) at java.lang.ClassLoader.loadClass(ClassLoader.java:306) at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301) at java.lang.ClassLoader.loadClass(ClassLoader.java:247) at java.lang.Class.forName0(Native Method) at java.lang.Class.forName(Class.java:169) at RetriveAllEmployees.main(RetriveAllEmployees.java:18) BUILD SUCCESSFUL (total time: 0 seconds)
krishna
September 7, 2011
site looks perfect

gives complete knowledge.very good site..thsnka for the user who created it...
netjocky
November 17, 2011
i got the following error

SQLException information Error msg: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.". SQLSTATE: 08S01 Error code: 0 com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused: connect. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.". at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1049) at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:833) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:716) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:841) at java.sql.DriverManager.getConnection(DriverManager.java:582) at java.sql.DriverManager.getConnection(DriverManager.java:207) at JDBC.main(JDBC.java:18)

Ads

 

Ads