Advanced Java Programming - Old Questions

8. How do you execute SQL statement in JDBC? (5)

Java Database Connectivity (JDBC) is an Application Programming Interface (API) used to connect Java application with Database. JDBC is used to interact with various type of Database such as Oracle, MS Access, My SQL and SQL Server. It allows java program to execute SQL statement and retrieve result from database.

The following steps are involved in executing SQL statements in JDBC:

1.      Load the JDBC driver.

2.      Specify the name and location (given as a URL) of the database being used.

3.      Connect to the database with a Connection object.

4.      Execute a SQL query using a Statement object.

5.      Get the results in a ResultSet object.

6.      Finish by closing the ResultSet, Statement and Connection objects.

Example:

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class Simplejdbc{

public static void main( String args[] ) {

try{

          //Load the JDBC driver

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

           // Establish a connection

            String url = "jdbc:mysql//localhost/test";

            Connection conn = DriverManager.getConnection(url);

           //Create a statement

            Statement st = conn.createStatement();

           //Execute a statement

            ResultSet rs = st.executeQuery("SELECT * FROM Employee");

            while(rs.next()){

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

                               String name = rs.getString("E_Name");

                               String address = rs.getString("E_Address");

                               Date d = rs.getDate("DOB");

                               System.out.println(id+"\\t"+name+"\\t"+address+"\\t"+d);

                               }

           st.close();

           conn.close();

    }catch (SQLException sqlExcep){

               System.out.println("Error: " + sqlExcep.getMessage());

               }

      }

}