JDBC

JDBC stands for Java Data Base Connectivity, which is a standard Java API for database-independent connectivity between the Java programming language and a wide range of databases.JDBC is a Java API to connect and execute the query with the database. It is a part of JavaSE (Java Standard Edition). JDBC API uses JDBC drivers to connect with the database. This is Service Technology. Any Java program connecting any data base in the standard. Any kind of Java Programme in order to communicate with any kind of data base in a standard manner, JDBC Technology is used. Java is mostly used in business applications. Business data (Enterprise Data] is stored in a data base. To offer business services to the customers, Java Applications should contact the data bases. Java Programs can make only method calls. These method calls are not understandable to the data bases. Data bases (DBMS) understand only SQL. This is nothing but Hetrogeneous Environment.

JDBC Architecture And Explaination :

1. Client Java Programme : Whatever may be the kind of Java Program it should be requesting database server to perform database operations. To do this, java program has the following responsibilities.

2. JDBC API (Set of Library Functions) : whatever may be the kind of Java program it should be requesting database server to perform database operations. To do this, Java program has the following responsibilities.

Establishing connection with the database.
Build the appropriate SQL statement as a string for example String s = “Insert into ……….. “;
Submit the SQL statement to the database.
Process the result.
Deal with the errors, when ever required.
Closing the connection.

3. Driver : To develop data base aware (knowing) Java application, we have two packages known as JDBC API. Java programs used JDBC methods from the packages in order to communicate with the data bases.

Java.sql
Javax.sql

4. Driver Manager : A driver is a translation software. It implements JDBC AP. A Driver software is developed strictly according to JDBC specification. Driver has the following responsibilities.

Establishes the database connection.
Receiving the database operational request from the Java program.
Translating JDBC method calls into database, server understandable SQL calls.
Submitting the SQL calls to the DBMS.
Receiving the response from the DBMS.
Translating the results into either Java or JDBC format and giving to the Java program.

5. Database Server : Java program for every data base operation sake talks to the driver. But, for the connection sake it will to the driver manager only once. Driver Manager initiates connect process on the driver. Driver will create the connection with the data base and provides to the driver manager. Driver Manager provides that connection to the Java program. Once, connection establishment is ever driver manager role ends.

Types of Drivers : We have four levels of (Type of) JDBC Drivers.

JDBC – ODBC Bridge Driver \ Type 1 Driver
Native API Pastly Java Driver \ Type 2 Driver \ Native Driver
Net Protocol all Java Driver \ Type 3 Driver \ Network Protocol Driver
Native Protocol all Java Driver \ Type 4 Driver \ Thin Driver

All four levels of drivers are designed in the increasing order of performance and decreasing order of client side maintenance. All these drivers are Java Program (Object Oriented System by themselves). All implements JDBC API. All are manufactured according to JDBC specification. All make the Java Program data base interaction data base independent. Different in four drivers is basically in translating the JDBC methods into something.

Type 1 Driver : It translates JDBC method calls into ODBC funcation calls. This driver can not directly communicate with the database server.
Advantages : Using this single driver we can get connected to any kind of database that is when data base changes. We need not change this driver. More ever for some DBMS, pure JDBC drivers are not available. In such cases, this is the only option.
Disadvantages : Performance problem of two layers that JDBC and ODBC layers. Clients Side Maintenance.

Type 2 Driver : It translates JDBC method calls into DBMS natives funcation calls.
Advantages: Better performance then that of Type 1 Driver because of elimination of ODBC layer. Less client side maintenance than that of Type 1 Driver because of no DSN configuration.
Disadvantages: Driver has to be changed when ever DBMS changes. Client side maintenance still exists loading that DBMS native library.

Type 3 Drivers: This driver translates JDBC method calls into a middle tier (Proxy Server) Server understandable network protocol calls.
Advantages: Zero client side maintenance. Better performance than Type 1 Driver and Type 2 Driver. Optimizations, performance tunings are done in the centralized network server.
Disadvantages: Three tier architecture is mandatory can not be used in client server computing. As JDBC client does not communicate directly with the DBMS less performance than that of Type 4 Driver.

Type 4 Driver : It translates JDBC method calls into DBMS specific native protocol calls for example SQL, Dot NET for Oracle.
Advantages : Zero client side maintenance. Highest performance.
Disadvantages : Driver should change when ever DBMS changes. For every kinds of DBMS, these drivers are not available.

Database Server : In Industries strength business applications RDBMS is used as database server almost all the times.
Example of Types of Drivers :

Oracle Server – Oracle Corporation
Microsoft SQL Server – Microsoft Corporation
Infomix – IBM Corporation
DB2 – IBM Corporation
Sybase – Sybase Corporation

Data Base Connectivity Approaches :

A Java program can get connected to the database in two ways.

JDBC – ODBC Approach
Pure JDBC Approach

JDBC – ODBC Approach : In this approach the following things are required in the client side.

JDBC Driver
JDBC API
ODBC Driver
ODBC API (Library)

In this approach Sun Micro System developed Bridge Driver (Type 1) is used. This driver translates JDBC method calls into ODBC function calls. That is, Type 1 Driver can not directly communicate with the database. ODBC procedure oriented, low level implementation. ODBC is windows oriented platform dependencies. Steps to connect to the Data Base using JDBC – ODBC Connection.

Loading The Driver : In LANG Package, we have a library class “Class”. This class has one static method forName().
Class.forName();
This method is used to load the driver class into memory.

For Example : Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
sun.jdbc.odbc is a Package Name and JdbcOdbcDriver is a Class Name

Once, the above method is executed, three things happen in the back ground.

Driver Class is loaded into memory
Driver Class Object is created.
Driver Object is registered with the Driver Manager.

Preparing the Connection String : Preparing the connection String is nothing but creating a string object which is the URL to the data base. In JDBC – ODBC approach connection string will be in the following format : "jdbc-odbc:dsn"

Requesting for the Connection : Java Program asks or requests java.sql.DriverManager class for the sake of data base connection. In this class, we have overloaded static methods to provide connectivity.

getConnection(String cs, "User Name", "Password");
getConnection(String cs);

Closing the Connection : After performing data base operations to release the networking resources. We have to close the connection by using the close().

The java.sql package contains classes and interfaces for JDBC API. A list of interfaces of JDBC API are given below :

Driver Interface
Connection Interface
Statement Interface
PreparedStatement Interface
CallableStatement Interface
ResultSet Interface
ResultSetMetaData Interface
DatabaseMetaData Interface
RowSet Interface

A list of Classes of JDBC API are given below :

DriverManager Class
BLOB Class
CLOB Class
Types Class

Q) Write a Program that get connected oracle data base server using JDBC - ODBC approach ?

import java.sql.*;
public class HybridConnection {
public static void main(String [ ] args) throws Exception {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
System.out.println("Driver Loaded, Instantiated and registered with the Driver Manager");
String s = "jdbc.odbc:india";
Connection con = DriverManager.getConnection(s, "scott","tiger");
System.out.println("Client Process Connected to the Server Process");
con.close();
System.out.println("After performing data base operations connection is closed");
}
}

Creating the Statement Object : By calling create statement method on the connection object, we create the java.sql.Statement object. Statement st = con.createStatement(); Statement is the designatory JDBC Object in order to submit SQL statement.

Submitting the SQL Statement : Statement object has three important methods to submit SQL statement to the data base.
1. executeUpdate(String dml) :- Using this method we submit insert, update and delete SQL statements to the data base. This method returns and integer that represents the number of rows effected in the data bases.
2. executeQuery(String [] drl)
3. execute(String sql)

Q) What is DSN ?

It is a name given to a configuration in which odbc driver, underline data base are involved. Using odbc database source administrator, we configure the DSN external to the Java Programme. In order to link odbc driver to sun driver in the program we use the DSN. DSN creation is not required in pure jdbc approach.

Q) Why to close the Statement Object ?

To release the jdbc resources.

Q) Why to close the Connection ?

To release the networking resources.

Q) Write a program to store one employee information into the data base tables ?

import java.sql.*;
public class InsertRecord {
public static void main(String [] args) throws Exception {
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
System.out.println(“Driver Loaded”);
String cs = “jdbc:odbc:ourdsn”;
Connection cs = DriverManager.getConnection(cs,”scott”,”tiger”);
System.out.println(“Connection is Established…..”);
String sql = “INSERT INTO EMPLOYEE VALUES (1000, ‘RAMA’ , 7000)”;
Statement st = con.createStatement();
int n = st.executeUpdate(sql);
System.out.println( n + “ Row Created.”);
st.close();
con.close();
System.out.println(“Connection is Closed.”);
}
}

Q) Write a program to update one employee information into the data base tables ?

import java.sql.*;
public class UpdateRecord {
public static void main(String [] args) throws Exception {
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
System.out.println(“Driver Loaded……”);
String cs = “jdbc:odbc:ourdsn”;
Connection cs = DriverManager.getConnection(cs,”scott”,”tiger”);
System.out.println(“Connection is Established…..”);
String sql = “UPDATE ACCOUNT SET BALANCE = BALANCE + 8000 WHERE BALANCE > 5000”;
Statement st = con.createStatement();
int n = st.executeUpdate(sql);
System.out.println( n + “ Row Updated.”);
st.close();
con.close();
System.out.println(“Connection is Closed.”);
}
}

Q) Write a program to store one employee information into the data base tables ?

import java.sql.*;
public class InsertRecord {
public static void main(String [] args) throws Exception {
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
System.out.println(“Driver Loaded”);
String cs = “jdbc:odbc:ourdsn”;
Connection cs = DriverManager.getConnection(cs,”scott”,”tiger”);
System.out.println(“Connection is Established…..”);
String sql = “INSERT INTO ACCOUNT VALUES (1000, ‘RAMA’ , 7000)”;
Statement st = con.createStatement();
int n = st.executeUpdate(sql);
System.out.println( n + “ Row Created.”);
st.close();
con.close();
System.out.println(“Connection is Closed.”);
}
}

Q) Write a program to delete one employee information into the data base tables ?

import java.sql.*;
public class DeleteRecord {
public static void main(String [] args) throws Exception {
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
System.out.println(“Driver Loaded………….”);
String cs = “jdbc:odbc:ourdsn”;
Connection cs = DriverManager.getConnection(cs,”scott”,”tiger”);
System.out.println(“Connection is Established…..”);
String sql = “DELETE FROM ACCOUNT WHERE BALANCE < 4000”;
Statement st = con.createStatement();
int n = st.executeUpdate(sql);
System.out.println( n + “ Row Deleted.”);
st.close();
con.close();
System.out.println(“Connection is Closed.”);
}
}

Retrieving Data from the Data Base : java.sql.Statement has one method that is executeQuery() method. In order to submit DRL (Select) statement to the databases.
ResultSet : ResultSet Object oriented representation of a table of records returned from the database up on the submission of SELECT Statement is nothing but ResultSet Object.
Note : OMR means One More Record.
Result object has two kinds of methods :
1. Cursor Movement Methods.
2. Column Values retrieval Methods.
When the ResultSet is open. The cursor points to zero record area of the ResultSet object. When the cursor does not point to records holding area if we try to records holding area if we try to fetch the column values we get SQL Exception (Errors). In the ResultSet we have around 15 methods to deal with cursor (movement). One important methods is next().
This method does two things :
1. Move the cursor in the forward directions by one record (Area) in the ResultSet.
2. Returning a boolean value indicating whether record exists or not.
ResultSet object has getXXX() methods to retrieve column values.

Q) Write a program to supply on Employee number to the data base, retrieve the details, if employee does not exists display the same ?

import java.sql.*;
public class Retrieval {
public static void main(String [] args) throws Exception {
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
System.out.println(“Driver is Loaded……….”);
String cs = “jdbc:odbc:india”;
Connection con = DriverManager.getConnection(cs, “scott”, “tiger”);
String sql = “SELECT * FROM EMPLOYEE WHERE EMPNO = 888”;
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
if(rs.next()) {
String nm = rs.getString(2);
float s = rs.getFloat(3);
System.out.println(“Employee Name : ” + nm);
System.out.println(“Employee Salary : ” + s);
} else {
System.out.println(“Employee does not exist. ”);
}
rs.close();
st.close();
con.close();
}
}

Q) Write a program to retrieve Employee Name and Salary of a particular Employee ?

import java.sql.*;
public class Retrieval {
public static void main(String [] args) throws Exception {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
System.out.println("Driver is Loaded ........ ");
String cs = "jdbc:odbc:india";
Connection con = DriverManager.getConnection(cs, "scott", "tiger");
String sql = "SELECT EMPNAME, SALARY FROM EMPLOYEE WHERE EMPNO = 1001";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
rs.next();
String nm = rs.getString(1);
float s = rs.getFloat(2);
System.out.println(nm);
System.out.println(f);
rs.close();
st.close();
con.close();
}
}

Q) Write a program to display all the Employee’s Information ?

import java.sql.*;
public class Retrieval {
public static void main(String [] args) throws Exception {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver")
; System.out.println("Driver is Loaded ....... ");
String cs = "jdbc:odbc:india";
Connection con = DriverManager.getConnection(cs, "scott", "tiger");
String sql = "SELECT * FROM EMPLOYEE";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
while(rs.next()) {
System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getFloat(3));
}
rs.close();
st.close();
con.close();
}
}

Q) Write a program to get connected to the data base using JDBC Approach (Pure JDBC Approach) ?

In Pure JDBC Approach, Driver Class changes connection string changes and there is no need of DSN creation. In order to get connected to the Oracle Data Base the driver class is Oracle.jdbc.driver.OracleDriver.
1. Connection string in this case will be as followings jdbc:oracle:thin:@localhost:1521:server;
2. Port Number as well as data base Service Name, we have to get from the following line.
File : D\Oracle\ora81\network\admin\tnsnames.ora

import java.sql.*;
public class InsertMultipleTimes {
public static void main(String [] args) throws Exception {
Class.forName("Oracle.jdbc.driver.OracleDriver");
String cs = "jdbc:oracle:thin:@localhost:1521:server";
Connection cs = DriverManager.getConnection(cs,"scott","tiger");
System.out.println("Connected ....... ");
con.close();
}
}

Note : Oracle Driver class is available in classes 111/112.zip or jar and classes111/classes112.zip or jar.
We need to set the class path for that location as follows : Set class path = D:\Oracle\Ora81\jdbc\lib\classes12.jar % classpath %

Q) When we submit SQL statement to the data base engine ? What happens in the back ground ?

1. Data Base engine passes the SQL Statement to examine the Syntactial errors.
2. Query plan Generation.
3. Executing the Query.

Q) What is the limitation of statement object ?

When ever an SQL statement is required to be submitted repeatedly statement object causes performance problems for the data base. java.sal.PreparedStatement which is a child of statement over comes this problem.
Steps to implement the using of Prepared Statement and Building the SQL Statement with special syntax :
1. String sql = "INSERT INTO EMPLOYEE VALUES (?, ?, ?) ";
2. String sql = "SELECT * FROM ACCOUNT WHERE ACCNO = ? ";
Note : OMS means One More Statement

Building the Prepared Statement :
PreparedStatement ps = con.preparedStatement(sql);
Binding the parameters ---> Supply the values to the in parameters is known as Bindings of parameters.

Submit/Executing the SQL Statement :
ps.executeUpdate();
ResultSet rs = ps.executeUpdate();

Note : "in" parameters are those parameters whose values are not supplied at query compilation time instead, place holders (?) are kept. To bind the parameters. " ps " has setXXX() methods that are mirror of getXXX() method of ResultSet.

Q) What are the differences between Statement and PreparedStatement ?

Statement is empty where as PrepareStatement object holds a pre compiled/already prepared SQL Statement. Statement object is used for one time submission of any SQL Statement. PreparedStement is used for submitting the same SQL statement repeatedly. In such case, it gives more performance than statement. Prepared Statement will have at least one “in” parameter.

Q) Example Program to store employee information into the data base ?

import java.sql.*;
public class InsertMultipleTimes {
public static void main(String [] args) throws Exception {
Class.forName(“Oracle.jdbc.driver.OracleDriver”);
String cs = “jdbc:oracle:thin:@localhost:1521:server”;
Connection cs = DriverManager.getConnection(cs,”scott”,”tiger”);
String sql = “INSERT INTO EMPLOYEE VALUES (?, ?, ?)”;
PreparedStatement ps = con.preparedStatement(sql);
ps.setInt(1,101);
ps.setString(2,”Rajesh”);
ps.setFloat(3,”20000”);
ps.executeUpdate();
ps.setInt(1,102);
ps.setString(2,”Suresh”);
ps.setFloat(3,”40000”);
ps.executeUpdate();
ps.close();
con.close();
}
}

Q) Piece of code to reterive employee information repeatedly ?

String sql = "SELECT * FROM EMPLOYEE WHERE EMPNO = ?";
PreparedStatement ps = con.preparedStatement(sql);
ps.setInt(1, 101);
ResultSet rs = ps.executeQuery();
rs.next();
System.out.println(" First Employee Details ........ ");
System.out.println(" Employee Name : " + Employee);
System.out.println(" Salary : Rs. " + salary);
rs.close();
ps.close();
Note : Once ResultSet is ready retrieval is as usual.

Q) Write a Program to display all the records of Employee Table along with Column names ?

ResultSet object holds only column values of the rows but not the column details.
ResultSetMetaData is the designatory JDBC object that holds the column details of the Resultset. This object has some important methods that gives column details.
1. getColumnCount ( );
2. getColumnName(int no);
3. getColumnType( );
Retrieve Example using ResultSet and ResultSetMetaData :
import java.sql.*;
public class Retrieve {
public static void main(String [] args) throws Exception {
Class.forName(“Oracle.jdbc.driver.OracleDriver”);
String cs = “jdbc:oracle:thin:@localhost:1521:server”;
Connection cs = DriverManager.getConnection(cs,”scott”,”tiger”);
String sql = “SELECT * FROM EMPLOYEE”;
Statement st = con.createStatement();
ResultSet rs = rs.executeQuery(sql);
ResultSetMetaData rmd = rs.getMetaData();
int noc = rmd.getColumnCount();
for(int i = 1; i < noc; i++) {
System.out.println(rmd.getColumnCount(i) + “ II ”);
System.out.println( );
}
while(rs.next()) {
System.out.println(rs.getInt(1) + “ ” + rs.getString(2) + “ ” + rs.getFloat(3));
}
rs.close();
st.close();
con.close();
}
}

JDBC - Create Database Programming

import java.sql.*;
public class JdbcCreateDataBase {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Creating database...");
stmt = conn.createStatement();
String sql = "CREATE DATABASE STUDENTS";
stmt.executeUpdate(sql);
System.out.println("Database created successfully...");
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} finally {
try{
if(stmt!=null)
stmt.close();
} catch(SQLException se2) {
}
try{
if(conn!=null)
conn.close();
} catch(SQLException se){
se.printStackTrace();
}
}
}
System.out.println("Goodbye!");
}
}

Output :

Connecting to database...
Creating database...
Database created successfully...
Goodbye!

JDBC - Select Database Programming

import java.sql.*;
public class JdbcSelectDataBase {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} finally {
try{
if(conn!=null)
conn.close();
} catch(SQLException se){
se.printStackTrace();
}
}
}
System.out.println("Goodbye!");
}
}

Output :

Connecting to a selected database...
Connected database successfully...
Goodbye!

JDBC - Drop Database Programming

import java.sql.*;
public class JdbcDropDataBase {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
System.out.println("Deleting database...");
stmt = conn.createStatement();
String sql = "DROP DATABASE STUDENTS";
stmt.executeUpdate(sql);
System.out.println("Database deleted successfully...");
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} finally {
try{
if(stmt!=null)
stmt.close();
} catch(SQLException se2) {
}
try{
if(conn!=null)
conn.close();
} catch(SQLException se){
se.printStackTrace();
}
}
}
System.out.println("Goodbye!");
}
}

Output :

Connecting to a selected database...
Connected database successfully...
Deleting database...
Database deleted successfully...
Goodbye!

JDBC - Create Table in Database Programming

import java.sql.*;
public class JdbcCreateDataBaseTable {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
System.out.println("Creating table in given database...");
stmt = conn.createStatement();
String sql = "CREATE TABLE REGISTRATION (ID INTEGER NOT NULL, FIRST VARCHAR(255), LAST VARCHAR(255), AGE INTEGER, PRIMARY KEY ( id ))";
stmt.executeUpdate(sql);
System.out.println("Created table in given database...");
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} finally {
try{
if(stmt!=null)
stmt.close();
} catch(SQLException se2) {
}
try{
if(conn!=null)
conn.close();
} catch(SQLException se){
se.printStackTrace();
}
}
}
System.out.println("Goodbye!");
}
}

Output :

Connecting to a selected database...
Connected database successfully...
Creating table in given database...
Created table in given database...
Goodbye!

JDBC - Drop Table in Database Programming

import java.sql.*;
public class JdbcDropDataBaseTable {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
System.out.println("Deleting table in given database...");
stmt = conn.createStatement();
String sql = "DROP TABLE REGISTRATION";
stmt.executeUpdate(sql);
System.out.println("Table deleted in given database...");
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} finally {
try{
if(stmt!=null)
stmt.close();
} catch(SQLException se2) {
}
try{
if(conn!=null)
conn.close();
} catch(SQLException se){
se.printStackTrace();
}
}
}
System.out.println("Goodbye!");
}
}

Output :

Connecting to a selected database...
Connected database successfully...
Deleting table in given database...
Table deleted in given database...
Goodbye!

JDBC - Insert Records in Table Programming

import java.sql.*;
public class JdbcInsertRecordsTable {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
System.out.println("Inserting records into the table...");
stmt = conn.createStatement();
String sql = "INSERT INTO REGISTRATION VALUES (100, 'Zara', 'Ali', 18)";
stmt.executeUpdate(sql);
sql = "INSERT INTO REGISTRATION VALUES (101, 'Mahnaz', 'Fatma', 25)";
stmt.executeUpdate(sql);
sql = "INSERT INTO REGISTRATION VALUES (102, 'Zaid', 'Khan', 30)";
stmt.executeUpdate(sql);
sql = "INSERT INTO REGISTRATION VALUES (103, 'Sumit', 'Mittal', 28)";
stmt.executeUpdate(sql);
System.out.println("Inserted records into the table...");
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} finally {
try{
if(stmt!=null)
stmt.close();
} catch(SQLException se2) {
}
try{
if(conn!=null)
conn.close();
} catch(SQLException se){
se.printStackTrace();
}
}
}
System.out.println("Goodbye!");
}
}

Output :

Connecting to a selected database...
Connected database successfully...
Inserting records into the table...
Inserted records into the table...
Goodbye!

JDBC - Select Records in Table Programming

import java.sql.*;
public class JdbcSelectRecordsTable {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
System.out.println("Creating statement...");
stmt = conn.createStatement();
String sql = "SELECT ID, FIRST, LAST, AGE FROM REGISTRATION";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
System.out.print("ID: " + id);
System.out.print(", AGE : " + age);
System.out.print(", FIRST : " + first);
System.out.println(", LAST : " + last);
}
rs.close();
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} finally {
try{
if(stmt!=null)
stmt.close();
} catch(SQLException se2) {
}
try{
if(conn!=null)
conn.close();
} catch(SQLException se){
se.printStackTrace();
}
}
}
System.out.println("Goodbye!");
}
}

Output :

Connecting to a selected database...
Connected database successfully...
Creating statement...
ID: 100, AGE : 18, FIRST : Zara, LAST : Ali
ID: 101, AGE : 25, FIRST : Mahnaz, LAST : Fatma
ID: 102, AGE : 30, FIRST : Zaid, LAST : Khan
ID: 103, AGE : 28, FIRST : Sumit, LAST : Mittal
Goodbye!

JDBC - Update Records in Table Programming

import java.sql.*;
public class JdbcUpdateRecordsTable {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
System.out.println("Creating statement...");
stmt = conn.createStatement();
String sql = "UPDATE REGISTRATION SET AGE = 30 WHERE ID IN (100, 101)";
stmt.executeUpdate(sql);
sql = "SELECT ID, FIRST, LAST, AGE FROM REGISTRATION";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
System.out.print("ID: " + id);
System.out.print(", AGE : " + age);
System.out.print(", FIRST : " + first);
System.out.println(", LAST : " + last);
}
rs.close();
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} finally {
try{
if(stmt!=null)
stmt.close();
} catch(SQLException se2) {
}
try{
if(conn!=null)
conn.close();
} catch(SQLException se){
se.printStackTrace();
}
}
}
System.out.println("Goodbye!");
}
}

Output :

Connecting to a selected database...
Connected database successfully...
Creating statement...
ID: 100, AGE : 30, FIRST : Zara, LAST : Ali
ID: 101, AGE : 30, FIRST : Mahnaz, LAST : Fatma
ID: 102, AGE : 30, FIRST : Zaid, LAST : Khan
ID: 103, AGE : 28, FIRST : Sumit, LAST : Mittal
Goodbye!

JDBC - Delete Records in Table Programming

import java.sql.*;
public class JdbcDeleteRecordsTable {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
System.out.println("Creating statement...");
stmt = conn.createStatement();
String sql = "DELETE FROM REGISTRATION WHERE ID = 101";
stmt.executeUpdate(sql);
sql = "SELECT ID, FIRST, LAST, AGE FROM REGISTRATION";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
System.out.print("ID: " + id);
System.out.print(", AGE : " + age);
System.out.print(", FIRST : " + first);
System.out.println(", LAST : " + last);
}
rs.close();
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} finally {
try{
if(stmt!=null)
stmt.close();
} catch(SQLException se2) {
}
try{
if(conn!=null)
conn.close();
} catch(SQLException se){
se.printStackTrace();
}
}
}
System.out.println("Goodbye!");
}
}

Output :

Connecting to a selected database...
Connected database successfully...
Creating statement...
ID: 100, AGE : 30, FIRST : Zara, LAST : Ali
ID: 102, AGE : 30, FIRST : Zaid, LAST : Khan
ID: 103, AGE : 28, FIRST : Sumit, LAST : Mittal
Goodbye!

JDBC - Select Records in Table using Where Clause Programming

import java.sql.*;
public class JdbcWhereClauseTable {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
System.out.println("Creating statement...");
stmt = conn.createStatement();
System.out.println("Fetching records without condition....");
String sql = "SELECT ID, FIRST, LAST, AGE FROM REGISTRATION";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
System.out.print("ID: " + id);
System.out.print(", AGE : " + age);
System.out.print(", FIRST : " + first);
System.out.println(", LAST : " + last);
}
System.out.println("Fetching records with condition...");
sql = "SELECT ID, FIRST, LAST, AGE FROM REGISTRATION WHERE ID >= 101";
rs = stmt.executeQuery(sql);
while(rs.next()) {
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
System.out.print("ID: " + id);
System.out.print(", AGE : " + age);
System.out.print(", FIRST : " + first);
System.out.println(", LAST : " + last);
}
rs.close();
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} finally {
try{
if(stmt!=null)
stmt.close();
} catch(SQLException se2) {
}
try{
if(conn!=null)
conn.close();
} catch(SQLException se){
se.printStackTrace();
}
}
}
System.out.println("Goodbye!");
}
}

Output :

Connecting to a selected database...
Connected database successfully...
Creating statement...
Fetching records without condition...
ID : 100, AGE : 30, FIRST : Zara, LAST : Ali
ID : 102, AGE : 30, FIRST : Zaid, LAST : Khan
ID : 103, AGE : 28, FIRST : Sumit, LAST : Mittal
Fetching records with condition...
ID : 102, AGE : 30, FIRST : Zaid, LAST : Khan
ID : 103, AGE : 28, FIRST : Sumit, LAST : Mittal
Goodbye!

JDBC - Select Records in Table using Like Programming

import java.sql.*;
public class JdbcLikeTable {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
System.out.println("Creating statement...");
stmt = conn.createStatement();
System.out.println("Fetching records without condition....");
String sql = "SELECT ID, FIRST, LAST, AGE FROM REGISTRATION";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
System.out.print("ID: " + id);
System.out.print(", AGE : " + age);
System.out.print(", FIRST : " + first);
System.out.println(", LAST : " + last);
}
System.out.println("Fetching records with condition...");
sql = "SELECT ID, FIRST, LAST, AGE FROM REGISTRATION WHERE FIRST LIKE '%za%'";
rs = stmt.executeQuery(sql);
while(rs.next()) {
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
System.out.print("ID: " + id);
System.out.print(", AGE : " + age);
System.out.print(", FIRST : " + first);
System.out.println(", LAST : " + last);
}
rs.close();
} catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} finally {
try{
if(stmt!=null)
stmt.close();
} catch(SQLException se2) {
}
try{
if(conn!=null)
conn.close();
} catch(SQLException se){
se.printStackTrace();
}
}
}
System.out.println("Goodbye!");
}
}

Output :

JDBC - Select Records in Table using Sorting Order Programming

import java.sql.*;
public class JdbcSortOrderTable {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/STUDENTS";
static final String USER = "username";
static final String PASS = "password";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
System.out.println("Connecting to a selected database...");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
System.out.println("Connected database successfully...");
System.out.println("Creating statement...");
stmt = conn.createStatement();
System.out.println("Fetching records in ascending order...");
String sql = "SELECT ID, FIRST, LAST, AGE FROM REGISTRATION ORDER BY FIRST ASC";
ResultSet rs = stmt.executeQuery(sql);
while(rs.next()) {
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
System.out.print("ID: " + id);
System.out.print(", AGE : " + age);
System.out.print(", FIRST : " + first);
System.out.println(", LAST : " + last);
}
System.out.println("Fetching records with condition...");
sql = "SELECT ID, FIRST, LAST, AGE FROM REGISTRATION ORDER BY FIRST DESC";
rs = stmt.executeQuery(sql);
while(rs.next()) {
int id = rs.getInt("id");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");
System.out.print("ID: " + id);
System.out.print(", AGE : " + age);
System.out.print(", FIRST : " + first);
System.out.println(", LAST : " + last);
}
rs.close(); } catch(SQLException se) {
se.printStackTrace();
} catch(Exception e) {
e.printStackTrace();
} finally {
try{
if(stmt!=null)
stmt.close();
} catch(SQLException se2) {
}
try{
if(conn!=null)
conn.close();
} catch(SQLException se){
se.printStackTrace();
}
}
}
System.out.println("Goodbye!");
}
}

Output :

Connecting to a selected database...
Connected database successfully...
Creating statement...
Fetching records in ascending order...
ID : 100, AGE : 30, FIRST : Zara, LAST : Ali
ID : 102, AGE : 30, FIRST : Zaid, LAST : Khan
ID : 103, AGE : 28, FIRST : Sumit, LAST : Mittal
Fetching records in descending order...
ID : 100, AGE : 30, FIRST : Zara, LAST : Ali
ID : 102, AGE : 30, FIRST : Zaid, LAST : Khan
ID : 103, AGE : 28, FIRST : Sumit, LAST : Mittal
Goodbye!

JDBC Interfaces :

RowSet
Driver
ResultSet
Statement
Connection
PrepareStatement
DataBaseMetaData
CallableStatement
ResultSetMetaData

JDBC Classes :

BLOB
CLOB
Type
DriverManager

Classes in RowSet Interfaces :

WebRowSet
JdbcRowSet
JoinRowSet
CachedRowSet
FilteredRowSet

Pages

JDBC

Data Base Connectivity Approaches :

Q) Write a Program that get connected oracle data base server using JDBC - ODBC approach ?

Q) What is DSN ?

Q) Why to close the Statement Object ?

Q) Why to close the Connection ?

Q) Write a program to store one employee information into the data base tables ?

Q) Write a program to update one employee information into the data base tables ?

Q) Write a program to store one employee information into the data base tables ?

Q) Write a program to delete one employee information into the data base tables ?

Q) Write a program to supply on Employee number to the data base, retrieve the details, if employee does not exists display the same ?

Q) Write a program to retrieve Employee Name and Salary of a particular Employee ?

Q) Write a program to display all the Employee’s Information ?

Q) Write a program to get connected to the data base using JDBC Approach (Pure JDBC Approach) ?

Q) When we submit SQL statement to the data base engine ? What happens in the back ground ?

Q) What is the limitation of statement object ?

Q) What are the differences between Statement and PreparedStatement ?

Q) Example Program to store employee information into the data base ?

Q) Piece of code to reterive employee information repeatedly ?

Q) Write a Program to display all the records of Employee Table along with Column names ?

JDBC - Create Database Programming

Output :

JDBC - Select Database Programming

Output :

JDBC - Drop Database Programming

Output :

JDBC - Create Table in Database Programming

Output :

JDBC - Drop Table in Database Programming

Output :

JDBC - Insert Records in Table Programming

Output :

JDBC - Select Records in Table Programming

Output :

JDBC - Update Records in Table Programming

Output :

JDBC - Delete Records in Table Programming

Output :

JDBC - Select Records in Table using Where Clause Programming

Output :

JDBC - Select Records in Table using Like Programming

Output :

JDBC - Select Records in Table using Sorting Order Programming

Output :