DIARY OF A JAVA ADDICT

Quick JDBC

The JDBC is an acronym for Java Database Connectivity, it is a standard Java API, used in several paradigms like Java Applications,Java Applets, Java Servlets,Java Server Pages and Enterprise JavaBeans. An application can use JDBC by including following two packages:

java.sql
javax.sql

The two tier JDBC architecture consist of two APIs:

JDBC API: Connection from Application to JDBC Manager
JDBC Driver API: Connection from JDBC Manager to Driver

Glossary of terms:

Driver Manager: Match connection requests with proper database driver.
Driver: Handles communications with database server.
Connection: All communication with database is through connection object only.
Statement: Used to submit SQL commands to database.
ResultsSet: Holds data retrieved as a result of statement execution.

General Flow of Action

A JDBC application involves following six steps:

Step 1: Importing packages

//Importing the packages.
import java.sql.*;
//Variable for driver name
private static final String JDRVR = "com.mysql.jdbc.Driver";
//Variable name for database
private static final String JURL = "jdbc:mysql://localhost/";
//User Identification
static final String JUSR = "root";
static final String JPWD = "******";

Step 2: Registering JDBC Driver

//STEP 2: Registering JDBC driver.
Class.forName ( JDRVR);

Step 3: Connecting with DBMS

Connection myCon = null;
Statement myStmt = null;
//STEP 3: Connecting to database.
myCon = DriverManager.getConnection ( JURL,JUSR,JPWD);

Step 4: Creating a database: An update

Connection myCon = null;
//STEP 3: Executing a Query
myStmt = myCon.createStatement ( );
myStmt.executeUpdate ( "CREATE DATABASE PATRONS");

Step 4: Creating a table: An update

//Create a table called PATRON in PATRONS
myStmt.executeUpdate (
"CREATE TABLE PATRON "+
" ( "+
" id INTEGER not NULL,"+
" fName VARCHAR ( 255),"+
" lName VARCHAR ( 255),"+
" title VARCHAR ( 10),"+
" addrLine1 VARCHAR ( 255),"+
" addrLine2 VARCHAR ( 255),"+
" city VARCHAR ( 255),"+
" state VARCHAR ( 2),"+
" ZIP VARCHAR ( 10), "+
" PRIMARY KEY ( id)"+
")"
);

Step 4: Inserting into table: An update

//Insert Some Entries into the table.
//Insert Some Entries into the table.
myStmt.executeUpdate (
"INSERT INTO PATRON VALUES "+
" ( "+
" 1, "+
"\"Anil\","+
"\"Singh\","+
"\"Dr.\","+
"\"2258 Oakridge Dr\","+
"\"Apt 16\","+
"\"Aurora\","+
"\"IL\","+
"\"60503\""+
")"
);

Step 4: Updating tables: Update

//Updating a record in the table.
myStmt.executeUpdate ( "update PATRON set title=\"Prof.\" where id=2");

Step 5: Analysing result set

//Time to make data qry, and see the changes.
ResultSet rs = myStmt.executeQuery (
                       "SELECT title,"+
                       " lName, city "+
                       " FROM PATRON "+
                       " order by fName"
                       );
while ( rs.next ( )) {
//Analyse the result line by line.
String title = rs.getString ( "title");
String lName = rs.getString ( "lName");
String city = rs.getString ( "city");
System.out.println ( title+" "+lName+", from: "+city+".");
}

Step 6: Cleanup

//Close the statement.
try {
if ( myStmt!=null)
myStmt.close ( );
}
catch ( SQLException e) {
}
//Close the connection.
try {
if ( myCon!=null)
myCon.close ( );
}
catch ( SQLException se) {
se.printStackTrace ( );
}

Simple as JDBC is, it nevertheless provides a rich functionality for handling RDBM systems. There is a rich support for advanced features like triggers, stored procedure and transactions. We will explore these aspects in future posts.

DIARY OF A JAVA ADDICT

Friday, November 21, 2014

TEN MINUTE INTRODUCTION TO JDBC

Quick JDBC