Tuesday, July 3, 2012

Steps for building a simple JDBC application?

1. Import Packages
2. Register JDBC Driver
3. Open a Connection
4. Execute a Query
5. Extract data from Result Set
6. Clean-Up Environment

A Simple Program to demonstrate the JDBC application working:

This is a simple program that retrieves  and displays the values of particular columns from the Employees table stored in the database.

//STEP 1. Import packages
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.Date;
import java.sql.SQLException;

public class FirstQuery {
public static void main(String[] args) {
//Define Connection variable
Connection conn = null;
//Begin standard error handling
//STEP 2: Register JDBC driver
String driver = "oracle.jdbc.driver.OracleDriver";  //for oracle database
//STEP 3: Open a connection
System.out.println("Connecting to database...");
String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:XE";
 /* this url will be different for the different databases */
String user = "username"; // username and password for the database authentication
String password = "password";
conn = DriverManager.getConnection(jdbcUrl,user,password);
//STEP 4: Execute a query
Statement stmt = conn.createStatement();
String sql;
sql = "SELECT SSN, Name, Salary, Hiredate FROM Employees";
ResultSet rs = stmt.executeQuery(sql);
//STEP 5: Extract data from result set
//Retrieve by column name
int ssn= rs.getInt("ssn");
String name = rs.getString("name");
//Retrieve by column index as an example
double salary = rs.getDouble(3);
Date date = rs.getDate(4);
//Display values
System.out.print("SSN: " + ssn);
System.out.print(", Name: " + name);
System.out.print(", Salary: $" + salary);
System.out.println(", HireDate: " + date);
//STEP 6: Clean−up environment
}catch(SQLException se){
//Handle errors for JDBC
}catch(Exception e){
//Handle errors for Class.forName
//finally block used to close resources
    }catch(SQLException se){
}//end main

The Expected Output after you have created the databse with some values of these columns:

Connecting to database...
SSN: 111111111, Name: Harish, Salary: $5000.55, HireDate: 2010−09−16
SSN: 419876541, Name: Vikas, Salary: $1500.75, HireDate: 2011−03−05
SSN: 312654987, Name: Aman, Salary: $2000.95, HireDate: 2012−01−11
SSN: 123456789, Name: Sumit, Salary: $3080.05, HireDate: 2009−09−07
SSN: 987654321, Name: John, Salary: $4351.27, HireDate: 2011−12−31
The values that you will store in the databse will be shown.

This was just a simple demonstration program to just give some idea about the JDBC connectivity.
In the later posts, the more detailed and programs for entering the data in the databse, updating and creating the database using either Statement or preparedStatement objects.

No comments:

Post a Comment