Creating JDBC Connection in web application

There are following are six steps involved in creating a JDBC application −

·        Import Require packages: we need to import JDBC packages that are Requires while creating connection with mysql. JDBC classes needed for database connectoin. Most often, using import java.sql.* will suffice.

·        Register the JDBC driver: Driver manager class will attempt to load driver referenced in the jdbc.drivers. This makes User to customize the JDBC drivers Used by their application.

·         Open a connection : it need the DriverManager.getConnection() method to create a Connection object, which represents a physical connection with the database.

·        Execute a query: For executing a query we will call execute methods from different statements

1.  Execute : Return true if the first object that the query returns is a ResultSet   object.Use this method if the query could return one or more ResultSet Objects. Retrieve the ResultSet objects returned from the query by repeatedly calling statement.getResultSet.

2.  ExecuteQuery :Returns one ResultSetObject.

3.   Execute Update : Returns an  integer representing the number of rows affected         by the SQL statement. User this method if you are using INSERT,DELETE, or Update SQL statements

             Extract data from result set: Requires that you use the appropriate ResultSet.getXXX() method to retrieve the data from the result set.

Clean up the environment: Requires explicitly closing all database resources versus relying on the JVM's garbage collection.

Sample Code

//STEP 1. Import packages

import java.sql.*;

public class JDBCExample {

   // JDBC driver name and database URL

   static final String JDBCDRIVER = "com.mysql.jdbc.Driver"; 

   static final String DBURL = "jdbc:mysql://localhost/EMP";

   //  Database credentials

   static final String USER = "username";

   static final String PASS = "password";

  

   public static void main(String[] args) {

   Connection conn = null;

   Statement stmt = null;

   try{

      //STEP 2: Register JDBC driver

      Class.forName("com.mysql.jdbc.Driver");

      //STEP 3: Open a connection

      System.out.println("Connecting to database...");

      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      //STEP 4: Execute a query

      System.out.println("Creating statement...");

      stmt = conn.createStatement();

      String sql;

      sql = "SELECT id, first, last, age FROM UserDetails";

      ResultSet rs = stmt.executeQuery(sql);

      //STEP 5: Extract data from result set

      while(rs.next()){

         //Retrieve by column name

         int id  = rs.getInt("id");

         int age = rs.getInt("age");

         String first = rs.getString("first");

         String last = rs.getString("last");

         //Display values

         System.out.print("ID: " + id);

         System.out.print(", Age: " + age);

         System.out.print(", First: " + first);

         System.out.println(", Last: " + last);

      }

      //STEP 6: Clean-up environment

      rs.close();

      stmt.close();

      conn.close();

   }catch(SQLException se){

      //Handle errors for JDBC

      se.printStackTrace();

   }catch(Exception e){

      //Handle errors for Class.forName

      e.printStackTrace();

   }finally{

      //finally block used to close resources

      try{

         if(stmt!=null)

            stmt.close();

      }catch(SQLException se2){

      }// nothing we can do

      try{

         if(conn!=null)

            conn.close();

      }catch(SQLException se){

         se.printStackTrace();

      }//end finally try

   }//end try

}//end main

}//end FirstExample